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.g.johns...@gmail.com>
À: "PALAYRET Jacques" <jacques.palay...@meteo.fr>
Cc: "pgsql-general" <pgsql-gene...@postgresql.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.palay...@meteo.fr | jacques.palay...@meteo.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.
