Thread: PostgreSQL : error hint for LATERAL join
Hello,
For a LATERAL join, I think the hint (in case of error) is incomplete :
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
SELECT *
FROM (
SELECT 'a'
) t (c1)
LEFT OUTER JOIN LATERAL (
SELECT 'b', c1
) u(d1) ON true
;
c1 | d1 | c1
----+----+----
a | b | a
(1 ligne)
SELECT *
FROM (
SELECT 'a'
) t (c1)
INNER JOIN LATERAL (
SELECT 'b', c1
) u(d1) ON true
;
c1 | d1 | c1
----+----+----
a | b | a
(1 ligne)
SELECT *
FROM (
SELECT 'a'
) t (c1)
CROSS JOIN LATERAL (
SELECT 'b', c1
) u(d1)
;
c1 | d1 | c1
----+----+----
a | b | a
(1 ligne)
Regards
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
SELECT *
FROM (
SELECT 'a'
) t (c1)
LEFT OUTER JOIN LATERAL (
SELECT 'b', c1
) u(d1) ON true
;
c1 | d1 | c1
----+----+----
a | b | a
(1 ligne)
SELECT *
FROM (
SELECT 'a'
) t (c1)
INNER JOIN LATERAL (
SELECT 'b', c1
) u(d1) ON true
;
c1 | d1 | c1
----+----+----
a | b | a
(1 ligne)
SELECT *
FROM (
SELECT 'a'
) t (c1)
CROSS JOIN LATERAL (
SELECT 'b', c1
) u(d1)
;
c1 | d1 | c1
----+----+----
a | b | a
(1 ligne)
Regards
----- Météo-France -----
PALAYRET JACQUES
DCSC/GDC
jacques.palayret@meteo.fr
Fixe : +33 561078319
PALAYRET JACQUES
DCSC/GDC
jacques.palayret@meteo.fr
Fixe : +33 561078319
On Fri, Apr 8, 2022 at 1:29 AM PALAYRET Jacques <jacques.palayret@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)
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.
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.
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)
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.johnston@gmail.com>
À: "PALAYRET Jacques" <jacques.palayret@meteo.fr>
Cc: "pgsql-general" <pgsql-general@postgresql.org>
Envoyé: Vendredi 8 Avril 2022 15:36:34
Objet: Re: PostgreSQL : error hint for LATERAL join
À: "PALAYRET Jacques" <jacques.palayret@meteo.fr>
Cc: "pgsql-general" <pgsql-general@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 <jacques.palayret@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)
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.
PALAYRET Jacques <jacques.palayret@meteo.fr> writes:
> For me, one of the two following things should be true : either the hint (in case of a lateral error) is incomplete
orthe possibility of " cross join lateral " should be removed.
The reason the hint is worded the way it is is a practical one: the other
possibilities are not drop-in syntactic replacements.
Given
CREATE TABLE t (x float8);
all five of these are syntactically legal (with or without the
implied LATERAL):
SELECT * FROM t, sin(x);
SELECT * FROM t CROSS JOIN sin(x);
SELECT * FROM t JOIN sin(x) ON true;
SELECT * FROM t INNER JOIN sin(x) ON true;
SELECT * FROM t LEFT JOIN sin(x) ON true;
The only way to get to the error in question is to write something
like
SELECT * FROM t FULL JOIN sin(x) ON true;
If that's what you have, then changing FULL to INNER or LEFT will
produce a syntactically legal query; changing it to CROSS will not
unless you make additional changes (which'd be hard if the ON
condition were nontrivial).
Also, if we were trying to be completionist, we'd have to somehow
work in the comma and implicit-INNER syntaxes, which would surely
make the hint a confusing mess.
Another way to look at it is that there are only two allowed
semantic behaviors here: INNER and LEFT joins. The fact that
there's more than one way to spell an inner join is a historical
accident.
regards, tom lane
On Mon, Apr 11, 2022 at 7:20 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
PALAYRET Jacques <jacques.palayret@meteo.fr> writes:
> 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.
The reason the hint is worded the way it is is a practical one: the other
possibilities are not drop-in syntactic replacements.
[...]
SELECT * FROM t, sin(x);
SELECT * FROM t CROSS JOIN sin(x);
SELECT * FROM t JOIN sin(x) ON true;
SELECT * FROM t INNER JOIN sin(x) ON true;
SELECT * FROM t LEFT JOIN sin(x) ON true;
Another way to look at it is that there are only two allowed
semantic behaviors here: INNER and LEFT joins. The fact that
there's more than one way to spell an inner join is a historical
accident.
Agreed on both points, and a hat-tip for the former.
I did get somewhat annoyed previously that I had to write "on true" (I considered that a code smell) but decided it was a better option than both "t, six(x)" and "t cross join sin(x)" as I dislike the implicit format even more and the semantic mis-match with the cross join was unappealing as well. There really is no other option for a LEFT JOIN here so just the consistency with an INNER JOIN has now made writing "on true", at least for a lateral join, make sense to me.
David J.