SQL: Duplicate column names in query results

[philrz]

SuperSQL does not yet support duplicate column names in query results as is supported in standard SQL.

Details

Repro is with super commit 8f4cd56. This was found via a query from a sqllogictest.

The referenced sqllogictest is actually trying to test something more subtle, but seeing it fail with super reminded me that the more fundamental ability to cope with duplicate column names in query results is still on our SuperSQL to-do list.

With input data integers2.sup:

{i:1::int32}
{i:2::int32}
{i:3::int32}
{i:null::int32}

The following query produces column names i and i_1.

$ super -version
Version: 8f4cd564b

$ super -c "SELECT i, i % 2 AS i FROM 'integers2.sup';"
{i_1:1::int32,i:1}
{i_1:2::int32,i:0}
{i_1:3::int32,i:1}
{i_1:null::int32,i:null::int64}

Whereas in Postgres, we get two columns both with name i.

$ psql postgres
psql (17.5 (Homebrew))
Type "help" for help.

# SELECT * FROM integers2;
 i 
---
 1
 2
 3
  
(4 rows)

postgres=# SELECT i, i % 2 AS i FROM integers2;
 i | i 
---+---
 1 | 1
 2 | 0
 3 | 1
   |  
(4 rows)

[philrz]

I think this is another example that would require support for duplicate column names, but since it manifests as absent columns rather than the name with _1 appended I'm logging it separately just in case it requires special treatment.

Details

Repro is with super commit e75dfda. The repro query shown above was taken from a sqllogictest.

Here's the query with test data running successfully in Postgres. Note it shows two columns named a and two named b.

$ psql postgres
psql (17.5 (Homebrew))
Type "help" for help.

postgres=# CREATE TABLE test (a INTEGER, b INTEGER);
CREATE TABLE

postgres=# INSERT INTO test VALUES (11, 1), (12, 2);
INSERT 0 2

postgres=# SELECT * FROM test t1, test t2;
 a  | b | a  | b 
----+---+----+---
 11 | 1 | 11 | 1
 11 | 1 | 12 | 2
 12 | 2 | 11 | 1
 12 | 2 | 12 | 2
(4 rows)

And here's the repro with test data in SuperDB. Note that it shows only two columns.

$ super -version
Version: e75dfdaf1

$ super -s test
{a:11::int32,b:1::int32}
{a:12::int32,b:2::int32}

$ super -c "SELECT * FROM test t1, test t2;"
{a:11::int32,b:1::int32}
{a:12::int32,b:2::int32}
{a:11::int32,b:1::int32}
{a:12::int32,b:2::int32}

[philrz]

Such as in this sqllogictest, when there's multiple AS specifying the same target column name there is an explicit error message. Using the same input data as the top repro example:

$ super -version
Version: 9292bf6fd

$ super -c "SELECT i AS i, i % 2 AS i FROM 'integers2.sup';"
duplicate column label "i" at line 1, column 25:
SELECT i AS i, i % 2 AS i FROM 'integers2.sup';
                        ~

[philrz]

In a recent community Slack thread a user reported bumping into this issue.