SQL: "ambiguous column reference" in attempted ORDER BY and/or WHERE with JOIN #5983
Description
This works in Postgres, but not in SuperDB.
$ super -c "SELECT * FROM 'integers.sup' LEFT OUTER JOIN 'integers2.sup' ON integers.i=integers2.k ORDER BY i;"
"i": ambiguous column reference at line 1, column 97:
SELECT * FROM 'integers.sup' LEFT OUTER JOIN 'integers2.sup' ON integers.i=integers2.k ORDER BY i;
~
Details
Repro is with super commit e75dfda. This was found via a query from a sqllogictest.
This works in Postgres.
$ psql postgres
psql (17.5 (Homebrew))
Type "help" for help.
postgres=# CREATE TABLE integers(i INTEGER, j INTEGER);
CREATE TABLE
postgres=# INSERT INTO integers VALUES (1, 2), (2, 3), (3, 4);
INSERT 0 3
postgres=# CREATE TABLE integers2(k INTEGER, l INTEGER);
CREATE TABLE
postgres=# INSERT INTO integers2 VALUES (1, 10), (2, 20);
INSERT 0 2
postgres=# SELECT * FROM integers LEFT OUTER JOIN integers2 ON integers.i=integers2.k ORDER BY i;
i | j | k | l
---+---+---+----
1 | 2 | 1 | 10
2 | 3 | 2 | 20
3 | 4 | |
(3 rows)
However it's not yet supported in SuperDB.
$ super -version
Version: e75dfdaf1
$ cat integers.sup
{i:1::int32,j:2::int32}
{i:2::int32,j:3::int32}
{i:3::int32,j:4::int32}
$ cat integers2.sup
{k:1::int32,l:10::int32}
{k:2::int32,l:20::int32}
$ super -c "SELECT * FROM 'integers.sup' LEFT OUTER JOIN 'integers2.sup' ON integers.i=integers2.k ORDER BY i;"
"i": ambiguous column reference at line 1, column 97:
SELECT * FROM 'integers.sup' LEFT OUTER JOIN 'integers2.sup' ON integers.i=integers2.k ORDER BY i;
~
Of course, if we drop the ORDER BY it works fine and we can see the column i is in there.
$ super -c "SELECT * FROM 'integers.sup' LEFT OUTER JOIN 'integers2.sup' ON integers.i=integers2.k;"
{i:1::int32,j:2::int32,k:1::int32,l:10::int32}
{i:2::int32,j:3::int32,k:2::int32,l:20::int32}
{i:3::int32,j:4::int32}
Also, another sqllogictest defined in the same file shows a similar complaint in the WHERE clause as well.
$ super -c "SELECT * FROM 'integers.sup' LEFT OUTER JOIN 'integers2.sup' ON integers.i=integers2.k WHERE k IS NOT NULL ORDER BY i;"
"k": ambiguous column reference at line 1, column 95:
SELECT * FROM 'integers.sup' LEFT OUTER JOIN 'integers2.sup' ON integers.i=integers2.k WHERE k IS NOT NULL ORDER BY i;
~
"i": ambiguous column reference at line 1, column 118:
SELECT * FROM 'integers.sup' LEFT OUTER JOIN 'integers2.sup' ON integers.i=integers2.k WHERE k IS NOT NULL ORDER BY i;
~