On 11/17/26 18:10, Tomas Vondra wrote:
> Hi,
> 
> Thanks for the fixes. I've accidentally tripped over this issue while
> working on a nearby fix [1], dealing with pushdown to FULL JOIN, so
> kinda in the opposite direction.
> 
> I've posted a more complex fix in that thread, which also inspects the
> expressions in the PASSING/ON ERROR and ON/EMPTY clauses. Which does
> allow treating the JsonExpr as strict in more cases (well, at least I
> believe so). But when I tried to use your fix, it only really changed a
> single query in the (fairly extensive) regression tests I added:
> 
> #   Values Scan on "*VALUES*"
> # -   Output: JSON_QUERY("*VALUES*".column1, "*VALUES*".column2 PASSING
> 0 AS p RETURNING jsonb WITH UNCONDITIONAL WRAPPER KEEP QUOTES EMPTY
> ARRAY ON EMPTY EMPTY OBJECT ON ERROR), JSON_EXISTS("*VALUES*".column1,
> "*VALUES*".column2 PASSING 0 AS p TRUE ON ERROR)
> # +   Output: sqljson_query_strict("*VALUES*".column1,
> "*VALUES*".column2), sqljson_exists_strict("*VALUES*".column1,
> "*VALUES*".column2)
> #  (2 rows)
> #
> 
> There are two such failures, but both are about blocking inlining of a
> SQL function with JSON_QUERY/JSON_VALUE.
> 

To clarify, that does not mean the cases treated as non-strict by v4 is
very narrow. Expressions that stop being inlined in a STRICT SQL
function are for example:

 - plain JSON_EXISTS(js, '$.a'), because of its implicit FALSE ON ERROR

 - TRUE ON ERROR

 - a constant PASSING 1 AS v , in JSON_EXISTS (with ERROR ON ERROR) and
in JSON_VALUE

 - DEFAULT 0 ON EMPTY,  DEFAULT -1 ON ERROR, and even DEFAULT NULL 

 - EMPTY ARRAY ON EMPTY  and  EMPTY OBJECT ON ERROR 

AFAIK all of those are treated as strict with my fix. This can trigger
annoying plan changes:

- An expression index on the inlined expression is no longer used: Index
Scan becomes Seq Scan.

- A subquery pulled up from the nullable side of an outer join now gets
PlaceHolderVars.

...

But I'm still not convinced that makes it a serious issue, because how
many people are actually creating SQL functions wrapping JSON queries?


regards

-- 
Tomas Vondra



Reply via email to