Re: Unexpected zero results

On Wed, Mar 23, 2022 at 10:10 AM Viliam Ďurina <viliam(dot)durina(at)gmail(dot)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.