Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Projections require unique expression names error #9130

Closed
lewiszlw opened this issue Feb 5, 2024 · 4 comments · Fixed by #9154
Closed

Projections require unique expression names error #9130

lewiszlw opened this issue Feb 5, 2024 · 4 comments · Fixed by #9154
Labels
bug Something isn't working

Comments

@lewiszlw
Copy link
Member

lewiszlw commented Feb 5, 2024

Describe the bug

Datafusion failed to execute select * from (t1 cross join t2) as t cross join t3 with error message

Error during planning: Projections require unique expression names but the expression "t.a" at position 0 and "t.a" at position 2 have the same name. Consider aliasing ("AS") one of them.

I tested on sqlite3 and it return expected result.
latest commit: 840499f

To Reproduce

create table t1 (a int, b int);
create table t2 (a int, b int);
create table t3 (a int, b int);
insert into t1 values (1, 2);
insert into t2 values (3, 4);
insert into t3 values (5, 6);
select * from (t1 cross join t2) as t cross join t3;

Expected behavior

Return result like sqlite3

1|2|3|4|5|6

Additional context

No response

@lewiszlw lewiszlw added the bug Something isn't working label Feb 5, 2024
@Jefffrey
Copy link
Contributor

Jefffrey commented Feb 5, 2024

Related: #6543

@Lordworms
Copy link
Contributor

I want to try this one

@Lordworms
Copy link
Contributor

I tested the sqllite3 and it does not support ambiguous expressions on the tmp table. But when I create a table, it could work, and the table schema turns out to add another :1 to the columns, I guess we could do what they do.
image

@Lordworms
Copy link
Contributor

The rule is here, basically, we need to implement two things
I. first one, in select * from (t1 cross join t2) as t cross join t3. if t is a tmp table and there are no other actions on that tmp table. we just ignore the 'as t' since this works
image
II. the rest following the design of sqlite3, when create a real table, change its schema and add a ':num' to the name of the column inorder to make this works
image

Lordworms added a commit to Lordworms/arrow-datafusion that referenced this issue Feb 7, 2024
Lordworms added a commit to Lordworms/arrow-datafusion that referenced this issue Feb 17, 2024
alamb pushed a commit that referenced this issue Feb 17, 2024
…9154)

* fix: issue #9130 substitute redundant columns when doing cross join

* add test

* fix:bugs

* optimize code

* optimize code

* deleting extra debug info

* adding test and optimized code

* fix test

* fix test
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working
Projects
None yet
Development

Successfully merging a pull request may close this issue.

3 participants