Support columns having the same alias

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

@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?

@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.

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

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?

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

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.

🤯 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.+--------+--------+

🤯 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

first test didn't fail because it has different aliases

Oh man, 🤦 -- I missed the _ and

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.

I wonder if someone has time to file a ticket with the idea to re(use) sqlite's sqllogictest suite?

Another case is when selecting same value literals

DataFusion CLI v37.0.0
❯ select 1, 1;
Error during planning: Projections require unique expression names but the expression "Int64(1)" at position 0 and "Int64(1)" at position 1 have the same name. Consider aliasing ("AS") one of them.
❯

Yes,I was thinking the other day to allow query like that and check uniqueness from outer queries only

@alamb

@alamb I don't think you can directly run SQLite's test suite against just datafusion, there's a lot of CREATE INDEX etc going on.

I have an early-stage OLTP database project in the works using datafusion and that survives a decent fraction of sqllogictest. This issue is one of the remaining big limitations, along with AVG(DISTINCT) #2408 -- I've been filing bugs on test cases that failed due to datafusion, and they've largely been fixed.

would it be correct to use statement visitors here to add unique aliases?

I think that is actually a pretty neat idea -- specifically add the aliases in the SQL planner 👍

I would be happy to review such a PR

What is the best place to create this visitor?
Create new visitors/select_expr_aliases.rs near https://github.com/apache/datafusion-sqlparser-rs/blob/main/src/ast/visitor.rs? Or somewhere in datafustion repo?

What is the best place to create this visitor?

I am not 100% sure about an alias

In general I think code that would create unique aliases would belong in the planner somewhere (SqlToRel): https://github.com/apache/datafusion/blob/main/datafusion/sql/src/planner.rs

Support duplicate column names in SQL projections (fix apache#6543)

I ran into this while running TPC-DS benchmarks against a SQL engine we're building on DataFusion. Opened a PR that adds auto-aliasing in the SQL planner: #21126

Fix/support duplicate column names #6543 (#21126)

Revert "Fix/support duplicate column names apache#6543 (apache#21126)"

fix: Revert "Fix/support duplicate column names #6543 (#21126)" (#21254)

We had to revert this -- see #21126 (comment)

I'll try and make a PR to update the extended tests too

Reapply "Fix/support duplicate column names apache#6543 (apache#21126)…

Fix/support duplicate column names apache#6543 (apache#21126)

fix: Revert "Fix/support duplicate column names apache#6543 (apache#2…

Another data point for this issue, from a semantic layer that plans user SQL with DataFusion 53 and sends the unparsed SQL to other engines.

Over 30 days, our planner logged 3,483 lines carrying 1 of the 3 duplicate-name messages: Projections require unique expression names, Schema contains duplicate unqualified field name, or Schema contains qualified field name … which would be ambiguous. They carry at most 373 distinct statement fingerprints. That is an upper bound: we sum fingerprints per message, so a statement that failed 2 ways counts twice.

3,356 of those lines, and at most 248 fingerprints, name e.email. The e.email shape we reproduced, SELECT lower(e.email) AS email FROM employees e ORDER BY lower(e.email), fails on 53.0 and plans on 54.0.

The SELECT x, x shape this issue names still fails on 55.0. SELECT email, email FROM t and SELECT e.email, e.email AS email FROM t e both hit Projections require unique expression names. Is #21260 still the plan of record?

Navigation Menu

Support columns having the same alias #6543

Describe the bug

To Reproduce

Expected behavior

Additional context

5 remaining items

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Support columns having the same alias #6543

Description

Describe the bug

To Reproduce

Expected behavior

Additional context

Activity

comphead commented on Jun 6, 2023

berkaysynnada commented on Jun 6, 2023

alamb commented on Jun 24, 2023

alamb commented on Jun 24, 2023

comphead commented on Jun 24, 2023

jackwener commented on Jun 25, 2023

alamb commented on Jun 26, 2023

comphead commented on Jul 12, 2023

alamb commented on Jul 13, 2023

tv42 commented on Feb 12, 2024

alamb commented on Feb 12, 2024

Jefffrey commented on Apr 5, 2024

comphead commented on Apr 5, 2024

tv42 commented on Apr 16, 2024

5 remaining items

alamb commented on Jun 5, 2025

osipovartem commented on Jun 6, 2025

alamb commented on Jun 6, 2025

RafaelHerrero commented on Mar 23, 2026

alamb commented on Mar 30, 2026

fmcmac commented on Oct 18, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions