Re: PostgreSQL : error hint for LATERAL join

Hello,

Your point of view is interesting. May I discuss it ?
One answered to me on my question about " bug (ou not) in CTE Common Table Expressions or Subqueries in the FROM Clause " :
" Its doesn’t have to seem logical to you, but this is how it is defined to work and thus the observed behavior is not a bug. "
I thought about it and OK, even if regretted that the PostgreSQL language sometimes ( rarely ) doesn't really make sense for me (given my logic).

So, are standards the rule or not ?
For me, one of the two following things should be true : either the hint (in case of a lateral error) is incomplete or the possibility of " cross join lateral " should be removed.

Of course, the idea of CROSS join doesn't make think about LATERAL idea.
But, is there a difference between a CROSS join and a INNER join with the " ON TRUE " clause ?
Note : an inner join is a cross join with a clause ON (logical condition) and OK, of course, an INNER join has not always " ON TRUE " clause, but it can.

I think you are right to write " Because the lateral takes precedence ".
LATERAL " takes precedence " over CROSS in the same way as INNER JOIN ... and INNER JOIN ON TRUE
With LATERAL, joins are no more independant relations, neither CROSS or INNER.

SELECT *
FROM (
VALUES ('a'),('b')
) t (c1)
JOIN LATERAL (
VALUES ('b', c1), ('c',c1 || '*')
) u(d1) ON true
;
c1 | d1 | column2
----+----+---------
a | b | a
a | c | a*
b | b | b
b | c | b*
(4 lignes)

Regards

De: "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
À: "PALAYRET Jacques" <jacques(dot)palayret(at)meteo(dot)fr>
Cc: "pgsql-general" <pgsql-general(at)postgresql(dot)org>
Envoyé: Vendredi 8 Avril 2022 15:36:34
Objet: Re: PostgreSQL : error hint for LATERAL join

On Fri, Apr 8, 2022 at 1:29 AM PALAYRET Jacques < [ mailto:jacques(dot)palayret(at)meteo(dot)fr | 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.