On Fri, Apr 8, 2022 at 1:29 AM PALAYRET Jacques <jacques.palayret@xxxxxxxx> 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)
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.
