Re: Row pattern recognition

Hi Tatsuo,

> > 10/12 Walk DEFINE clause in window tree traversal [new]
> > A newly discovered issue: nodeFuncs.c was not visiting the
> > DEFINE clause in expression_tree_walker, query_tree_walker,
> > and their mutator counterparts. The demonstrated case is SQL
> > function inlining: a SQL function with a parameter used in
> > DEFINE (e.g., DEFINE A AS v > $1) would fail to substitute
> > the actual argument, producing wrong results.
>
> Excellnt findings! BTW, I realized that we cannot use $1 of function
> in PATTERN clause like: A{$1}.
>
> ERROR: 42601: syntax error at or near "$1"
> LINE 10: PATTERN (A{$1})
> ^
> LOCATION: scanner_yyerror, scan.l:1211
>
> Should we document somewhere?
>

The PATTERN quantifier {n} only accepts Iconst (integer literal) in the
grammar. When a host variable or function parameter is used (e.g.,
A{$1}), the user gets a generic syntax error.

Oracle accepts broader syntax and validates later, producing an error
at a later stage rather than a syntax error at parse time.

PostgreSQL itself already has precedent for this pattern -- in fact,
within the same window clause, frame offset (ROWS/RANGE/GROUPS) accepts
a_expr in the grammar and then rejects variables in parse analysis via
transformFrameOffset() -> checkExprIsVarFree().

I'd lean against documenting this. The SQL standard already defines
the quantifier bound as <unsigned integer literal>, so there is nothing
beyond the standard to call out, and documenting what is *not* allowed
tends to raise questions that wouldn't otherwise occur to users.

Rather, I think accepting a broader grammar and validating later would
be the more appropriate response, producing a descriptive error like:

"argument of bounded quantifier must be an integer literal"

I can either include this in the current patch set or handle it as a
separate follow-up after the main series is committed. What do you
think?

Regards,
Henson