Thread: Unexpected zero results
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?
Viliam
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 |
| | |
+--------+-------+
| 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:
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.
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.
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