Re: PostgreSQL : error hint for LATERAL join

On Fri, Apr 8, 2022 at 1:29 AM PALAYRET Jacques <jacques(dot)palayret(at)meteo(dot)fr>
wrote:

> For a LATERAL join, I think the hint (in case of error) is incomplete :
>
> " DÉTAIL : The combining JOIN type must be INNER or LEFT for a LATERAL
> reference. "
> to be replaced by :
> " DÉTAIL : The combining JOIN type must be CROSS, INNER or LEFT for a
> LATERAL reference. "
>
> Note : it depends on what is needed
>
>
Regardless of whether the syntax works or not, the conceptual idea that a
lateral is also somehow a cross join is just wrong. A cross join, by
definition, takes two independent relations and performs a cartesian
product between them. A lateral join, by definition, takes a row from the
left side of the join, evaluates the right side using one or more columns
from that rows, then produces an output row for each row produced by the
right side (copying the left) - the inner/outer marker indicating what to
do when the right side produces zero rows.

If you use a non-trivial demonstration query (i.e., one that doesn't try to
multiply 1x1) this becomes more clear:

postgres=# SELECT *
FROM (
VALUES ('a'),('b')
) t (c1)
CROSS JOIN LATERAL (
VALUES ('b', c1), ('c',c1 || '*')
) u(d1)
;
c1 | d1 | column2
----+----+---------
a | b | a
a | c | a*
b | b | b
b | c | b*
(4 rows)

The presence of the cross join is misleading (if anything the error message
is sound advice and the behavior shown is wrong, but likely standard's
mandated). If it were a true cross join the relation u produced 4 unique
rows and the relation t produced 2, thus the output should have 8 rows. It
only has four. Because the lateral takes precedence here and only matches
a subset of the right-side output rows with the left side.

David J.