Unexpected zero results

Viliam Ďurina schrieb am 23.03.2022 um 17:56:

Hello all,

I'm experimenting with JSON-path functions, and stumbled upon this query:

  SELECT jsonb_path_query('[1,2,3]', '$[*]?(@ == 4)')

It returns 0 rows. I expected it to return one row with `null` value.
Isn't it the case that `SELECT <some expression>` should always
return 1 row?

jsonb_path_query is a set returning function, so it's actually more like this:

SELECT *
FROM jsonb_path_query('[1,2,3]', '$[*]?(@ == 4)')

Then it's obvious why no row is returned.

That's one of the reasons I never use set-returning functions in the SELECT list.

I've just realized that. I used it as an equivalent of the standard
`JSON_QUERY` that returns a JSON value. If the expression matches multiple
values, it can wrap them in a JSON array.

Now I'm surprised that a set-returning function is even allowed in SELECT
clause where the values have to be scalar. I tried another query with even
weirder result:

SELECT jsonb_path_query('[1,2,2]', '$[*]?(@ > 1)') expr1,
jsonb_path_query('[1,2,3]', '$[*]?(@ > 0)') expr2

+--------+-------+
| expr1 | expr2 |
+--------+-------+
| 2 | 1 |
| 2 | 2 |
| (null) | 3 |
| | |
+--------+-------+

Is it documented somewhere how is the set-typed result supposed to work?
Also how come a set contains two elements with the same value?

Viliam

On Wed, Mar 23, 2022 at 6:00 PM Thomas Kellerer <shammat@gmx.net> wrote:

On Wed, Mar 23, 2022 at 10:10 AM Viliam Ďurina <viliam.durina@gmail.com>
wrote:

Now I'm surprised that a set-returning function is even allowed in SELECT
clause where the values have to be scalar.

AFAIK the lateral construct, which is required to avoid doing just this, is
a relatively recent invention for SQL. I infer from that fact that the
ability to execute a set-returning function in the select clause has always
been allowed. When done, it behaves in a manner similar to an inner join
against the single input evaluation rows (i.e., the one where, typically,
the argument values come from). An inner join of one row and zero rows is
zero rows which is the behavior you are observing.

A true scalar subquery does not have this limitation - even when correlated
it gets joined to the parent relation in a left join manner and so the
single row in the parent relation will always remain and a zero record
outcome will result in null for the scalar subquery output.

FWIW this is the same behavioral dynamic that happens for Regular
Expressions. Our original regexp_matches() function eventually was
supplemented with a regexp_match() function to (mainly) allow for prettier
queries. I like having the option to choose the desired function instead
of having to write the normal single-result case always using a scalar
subquery.

I tried another query with even weirder result:

SELECT jsonb_path_query('[1,2,2]', '$[*]?(@ > 1)') expr1,
jsonb_path_query('[1,2,3]', '$[*]?(@ > 0)') expr2

+--------+-------+
| expr1 | expr2 |
+--------+-------+
| 2 | 1 |
| 2 | 2 |
| (null) | 3 |
| | |
+--------+-------+

Is it documented somewhere how is the set-typed result supposed to work?

Yep, though probably not where you would expect to find it. We just
haven't had a patch submission as yet that improved matters.

https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET

Also how come a set contains two elements with the same value?

That is just how SQL works. A result set does not have all of the
characteristics of a formal mathematical set. Every produced row has a
unique identity independent of the value(s) of the fields. There are SQL
operations that can remove all but one of these identities from a result
set based upon the comparison of the field values (DISTINCT, UNION, etc...).

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Wed, Mar 23, 2022 at 10:10 AM Viliam Ďurina <viliam.durina@gmail.com>
wrote:

Now I'm surprised that a set-returning function is even allowed in SELECT
clause where the values have to be scalar.

AFAIK the lateral construct, which is required to avoid doing just this, is
a relatively recent invention for SQL. I infer from that fact that the
ability to execute a set-returning function in the select clause has always
been allowed.

I believe that Postgres' handling of that is actually a hangover
from Berkeley's PostQUEL language. Dunno what the SQL standard has
to say on the subject --- but it wouldn't surprise me if they don't
allow it.

regards, tom lane