MIME-Version: 1.0
References: <20260306.153837.2137322608184587391.ishii@postgresql.org>
 <20260308.084722.1574081784704713701.ishii@postgresql.org>
 <CAAAe_zCvHz=tHkggP37OoQH8R0ux4j_CJdBTiPUg-L=6cVMyWg@mail.gmail.com>
 <20260309.120802.1845076903520202301.ishii@postgresql.org>
In-Reply-To: <20260309.120802.1845076903520202301.ishii@postgresql.org>
Reply-To: assam258@gmail.com
From: Henson Choi <assam258@gmail.com>
Date: Mon, 9 Mar 2026 13:02:02 +0900
Message-ID: 
 <CAAAe_zAn2nFgM_gfsEDYu+MXCArRFoP6s9bRz2bP4X5HNmnYww@mail.gmail.com>
Subject: Re: Row pattern recognition
To: Tatsuo Ishii <ishii@postgresql.org>
Cc: sjjang112233@gmail.com, vik@postgresfriends.org, er@xs4all.nl,
	jacob.champion@enterprisedb.com, david.g.johnston@gmail.com,
	peter@eisentraut.org, pgsql-hackers@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000cf9b6a064c8f77af"
Archived-At: 
 <https://www.postgresql.org/message-id/CAAAe_zAn2nFgM_gfsEDYu%2BMXCArRFoP6s9bRz2bP4X5HNmnYww%40mail.gmail.com>
Precedence: bulk

--000000000000cf9b6a064c8f77af
Content-Type: text/plain; charset="UTF-8"

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

--000000000000cf9b6a064c8f77af
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div class=3D"gmail_quote gmail_quote_container"><div>Hi T=
atsuo,</div><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"mar=
gin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1=
ex">&gt;=C2=A0 =C2=A010/12=C2=A0 Walk DEFINE clause in window tree traversa=
l=C2=A0 [new]<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 A newly discovered issue: nodeFuncs.=
c was not visiting the<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 DEFINE clause in expression_tree_wal=
ker, query_tree_walker,<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 and their mutator counterparts. The =
demonstrated case is SQL<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 function inlining: a SQL function wi=
th a parameter used in<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 DEFINE (e.g., DEFINE A AS v &gt; $1)=
 would fail to substitute<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 the actual argument, producing wrong=
 results.<br>
<br>
Excellnt findings!=C2=A0 BTW, I realized that we cannot use $1 of function<=
br>
in PATTERN clause like: A{$1}.<br>
<br>
ERROR:=C2=A0 42601: syntax error at or near &quot;$1&quot;<br>
LINE 10:=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0PATTERN (A{$1})<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 ^<br>
LOCATION:=C2=A0 scanner_yyerror, scan.l:1211<br>
<br>
Should we document somewhere?<br></blockquote><div><br></div>The PATTERN qu=
antifier {n} only accepts Iconst (integer literal) in the<br>grammar.=C2=A0=
 When a host variable or function parameter is used (e.g.,<br>A{$1}), the u=
ser gets a generic syntax error.<br><br>Oracle accepts broader syntax and v=
alidates later, producing an error</div><div class=3D"gmail_quote gmail_quo=
te_container">at a later stage rather than a syntax error at parse time.<br=
><br>PostgreSQL itself already has precedent for this pattern -- in fact,<b=
r>within the same window clause, frame offset (ROWS/RANGE/GROUPS) accepts<b=
r>a_expr in the grammar and then rejects variables in parse analysis via<br=
>transformFrameOffset() -&gt; checkExprIsVarFree().<br><br>I&#39;d lean aga=
inst documenting this.=C2=A0 The SQL standard already defines<br>the quanti=
fier bound as &lt;unsigned integer literal&gt;, so there is nothing<br>beyo=
nd the standard to call out, and documenting what is *not* allowed<br>tends=
 to raise questions that wouldn&#39;t otherwise occur to users.<br><br>Rath=
er, I think accepting a broader grammar and validating later would<br>be th=
e more appropriate response, producing a descriptive error like:<br><br>=C2=
=A0 &quot;argument of bounded quantifier must be an integer literal&quot;<b=
r><br>I can either include this in the current patch set or handle it as a<=
br>separate follow-up after the main series is committed.=C2=A0 What do you=
<br>think?<br><div><br></div><div>Regards,<br>Henson</div></div></div>

--000000000000cf9b6a064c8f77af--