Bug in parsing or formatting Extract function

[ghost]

Hi,

I have a SQL statement that contains

GROUP BY EXTRACT(MONTH FROM issue_date)

After parsing this statement and writing the AST using String() on the root node, it returns an invalid function call section:

GROUP BY extract('month', issue_date)

[ghost]

Looks like extract could become a special node type in tree pkg
https://www.postgresqltutorial.com/postgresql-date-functions/postgresql-extract/

[ghost]

as a workaround, I am patching the AST to replace the func argument with instance of this

type ExtractArg struct {
	Field *tree.StrVal
	From  tree.Expr
}

func (e *ExtractArg) Walk(v tree.Visitor) tree.Expr {
	field, fieldChanged := tree.WalkExpr(v, e.Field)
	from, fromChanged := tree.WalkExpr(v, e.From)
	if fieldChanged || fromChanged {
		exprCopy := *e
		exprCopy.Field = field.(*tree.StrVal) //assume type not changing
		exprCopy.From = from
		return &exprCopy
	}
	return e
}
func (e *ExtractArg) TypeCheck(ctx *tree.SemaContext, desired *types.T) (tree.TypedExpr, error) {
	// assume field that extracts numbers
	nr := tree.NewNumVal(constant.MakeInt64(0), strconv.Itoa(0), false)
	return nr.TypeCheck(ctx, desired)
}
func (e *ExtractArg) String() string {
	return fmt.Sprintf("extract(%v from %v)", e.Field, e.From)
}
func (e *ExtractArg) Format(ctx *tree.FmtCtx) {
	ctx.WriteString(strings.ToUpper(e.Field.RawString()))
	ctx.WriteString(" FROM ")
	ctx.FormatNode(e.From)
}

[auxten]

As the whole parser is mostly part of cockroachdb parser, cockroachdb treats EXTRACT(<part> FROM <value>) equal as function extract("<part>", <value>).

Your workaround is excellent. Could you please submit a Pull Request for this patch?

https://www.cockroachlabs.com/docs/stable/functions-and-operators.html#special-syntax-forms