Support columns having the same alias

[berkaysynnada]

Describe the bug

When we give the same aliases for multiple columns (SELECT ts as c1, inc_col as c1 FROM annotated_data_infinite), builder gives such an error:
Plan("Projections require unique expression names but the expression \"annotated_data_infinite.ts AS c1\" at position 0 and \"annotated_data_infinite.inc_col AS c1\" at position 1 have the same name. Consider aliasing (\"AS\") one of them.")

To Reproduce

        ctx.sql(
            "CREATE EXTERNAL TABLE annotated_data_infinite (
              ts INTEGER,
              inc_col INTEGER,
              desc_col INTEGER,
            )
            STORED AS CSV
            WITH HEADER ROW
            WITH ORDER (ts ASC)
            LOCATION '/Users/berkaysahin/Desktop/arrow-datafusion/datafusion/core/tests/data/window_1.csv'",
        )
        .await?;
        let sql = "SELECT ts as c1, inc_col as c1 FROM annotated_data_infinite";
        let dataframe = ctx.sql(sql).await.expect(&msg);

Expected behavior

Postgre can handle it and gives result with two columns having the same name. I don't know this is an intentional behaviour in Datafusion or a bug, but I would like to open an issue.

Additional context

No response

[comphead]

Thanks @berkaysynnada for raising. This is quite old problem,DF has unique column name check in the planner. We planned to move this check one level upper, so the query will fail if outer query references the inner query containing duplicated aliases. Fir you scenario is it real world one?

[berkaysynnada]

Thanks @berkaysynnada for raising. This is quite old problem,DF has unique column name check in the planner. We planned to move this check one level upper, so the query will fail if outer query references the inner query containing duplicated aliases. Fir you scenario is it real world one?

Not actually, it was a hypothetical trial. Your plan makes sense and thanks for letting me know about it.

[alamb]

I also filed #6758 to think about the problem with large column names.

[alamb]

I wonder if some potential solution for this issue would be to automatically add a string to make the columns unique in the arrow schema?

[comphead]

that is good idea btw, currently we got bunch of issues

• Arrow schema uniqueness violation
• DFSchema uniqueness violation
  this solution can potentially address both issues

[jackwener]

This is a legacy issue.

Generally, we won't raise an error for having columns with the same name unless an outer subquery references that column name.

In terms of this issue itself, we should fix it in the planner.

[alamb]

🤯 I didn't realize this worked

(arrow_dev) alamb@MacBook-Pro-8:~/Software/influxdb_iox2$ datafusion-cli
DataFusion CLI v26.0.0
❯ create table foo (x int) as values (1), (2), (3);
0 rows in set. Query took 0.003 seconds.
❯ select x as "my_col", x as "my col" from foo;
+--------+--------+
| my_col | my col |
+--------+--------+
| 1      | 1      |
| 2      | 2      |
| 3      | 3      |
+--------+--------+
3 rows in set. Query took 0.005 seconds.
❯ select x as "my_col", x+1 as "my col" from foo;
+--------+--------+
| my_col | my col |
+--------+--------+
| 1      | 2      |
| 2      | 3      |
| 3      | 4      |

However, using c1 as the alias for some reason fails:

❯ select x as c1, x as c1 from foo;
Error during planning: Projections require unique expression names but the expression "foo.x AS c1" at position 0 and "foo.x AS c1" at position 1 have the same name. Consider aliasing ("AS") one of them.+--------+--------+

[comphead]

🤯 I didn't realize this worked

(arrow_dev) alamb@MacBook-Pro-8:~/Software/influxdb_iox2$ datafusion-cli
DataFusion CLI v26.0.0
❯ create table foo (x int) as values (1), (2), (3);
0 rows in set. Query took 0.003 seconds.
❯ select x as "my_col", x as "my col" from foo;
+--------+--------+
| my_col | my col |
+--------+--------+
| 1      | 1      |
| 2      | 2      |
| 3      | 3      |
+--------+--------+
3 rows in set. Query took 0.005 seconds.
❯ select x as "my_col", x+1 as "my col" from foo;
+--------+--------+
| my_col | my col |
+--------+--------+
| 1      | 2      |
| 2      | 3      |
| 3      | 4      |

However, using c1 as the alias for some reason fails:

❯ select x as c1, x as c1 from foo;
Error during planning: Projections require unique expression names but the expression "foo.x AS c1" at position 0 and "foo.x AS c1" at position 1 have the same name. Consider aliasing ("AS") one of them.+--------+--------+

its failed as DF has projection uniqueness column name check.
first test didn't fail because it has different aliases

[alamb]

first test didn't fail because it has different aliases

Oh man, 🤦 -- I missed the _ and

[tv42]

An extra motivation to get this right is that sqlite's sqllogictest suite has a lot of these. You'll get better test coverage if you support this.