Thread: Jumble Query with COERCE_SQL_SYNTAX

Good day.

v14 introduced the way to get original text for some kind of expressions
using new 'funcformat' - COERCE_SQL_SYNTAX:
- EXTRACT(part from timestamp)
- (text IS [form] NORMALIZED)
and others.

Mentioned EXTRACT and NORMALIZED statements has parts, that are not
usual arguments but some kind of syntax. At least, there is no way to:

    PREPARE a(text) as select extract($1 from now());

But JumbleExpr doesn't distinguish it and marks this argument as a
variable constant, ie remembers it in 'clocations'.

I believe such "non-variable constant" should not be jumbled as
replaceable thing.

In our case (extended pg_stat_statements), we attempt to generalize
plan and then explain generalized plan. But using constant list from
JumbleState we mistakenly replace first argument in EXTRACT expression
with parameter. And then 'get_func_sql_syntax' fails on assertion "first
argument is text constant".

Sure we could workaround in our plan mutator with skipping such first
argument. But I wonder, is it correct at all to not count it as a
non-modifiable syntax part in JumbleExpr?

------

regards,

Sokolov Yura
y.sokolov@postgrespro.ru
funny.falcon@gmail.coma

Hi,

On Tue, Mar 29, 2022 at 03:52:57PM +0300, Yura Sokolov wrote:
> 
> v14 introduced the way to get original text for some kind of expressions
> using new 'funcformat' - COERCE_SQL_SYNTAX:
> - EXTRACT(part from timestamp)
> - (text IS [form] NORMALIZED)
> and others.
> 
> Mentioned EXTRACT and NORMALIZED statements has parts, that are not
> usual arguments but some kind of syntax. At least, there is no way to:
> 
>     PREPARE a(text) as select extract($1 from now());
> 
> But JumbleExpr doesn't distinguish it and marks this argument as a
> variable constant, ie remembers it in 'clocations'.
> 
> I believe such "non-variable constant" should not be jumbled as
> replaceable thing.

Yeah, the problem is really that those are some form of sublanguage inside SQL,
which is always a mess :(

It's probably an implementation detail that we treat those as syntactic sugar
for plain function calls, but since that's what we're doing I don't think it's
really sensible to change that.  For instance, for the query jumbler using this
query or "select pg_catalog.extract($1, now())" are identical, and that form
can be prepared.  Maybe it would make sense to allow a parameter for the
EXTRACT(x FROM y), since we're already allowing a non-standard form with
plain string literal?  The story is a bit different for NORMALIZED though.