#1580 General error with LATERAL and transitive join column

[Dom F]

(Might be related to #1579)

General case, using v2.5.0:

SELECT col3 FROM tableA JOIN tableB USING (col1) CROSS JOIN LATERAL(SELECT col2 FROM tableC WHERE col2 = col1) AS table (col3)

Results in a "General Error" due to col1 inside the LATERAL().
col1 shouldn't be ambiguous because it's the same colum in tableA and tableB, even though it's not qualified with a table name.

Requires tableC.col2 to be primary key:

create table tableA (col1 int);
create table tableB (col1 int);
create table tableC (col2 int, primary key (col2));

These variations don't cause the error:

When tableC.col2 is not primary key.

When you qualify col1 inside LATERAL():
SELECT col3 FROM tableA JOIN tableB USING (col1) CROSS JOIN LATERAL(SELECT col2 FROM tableC WHERE col2 = tableA.col1) AS tableC (col3)
or:
SELECT col3 FROM tableA JOIN tableB USING (col1) CROSS JOIN LATERAL(SELECT col2 FROM tableC WHERE col2 = tableB.col1) AS tableC (col3)

When you remove ambiguity from transitive join column:
SELECT col3 FROM tableA CROSS JOIN LATERAL(SELECT col2 FROM tableC WHERE col2 = col1) AS tableC (col3)

[Fred Toussi]

Thanks for reporting. Fixed and committed to SVN base/trunk.