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

Table references in subqueries cause invalid references in top-level projection #13027

Open
peasee opened this issue Oct 21, 2024 · 0 comments
Open
Labels
bug Something isn't working

Comments

@peasee
Copy link
Contributor

peasee commented Oct 21, 2024

Describe the bug

SQL with table references in a subquery:

SELECT j1_id FROM (SELECT ta.j1_id AS j1_id FROM j1 ta)

gets re-written in both GenericDialect and MySqlDialect with a table reference that does not exist.

GenericDialect:

SELECT ta.j1_id FROM (SELECT ta.j1_id FROM j1 AS ta)

MySqlDialect:

SELECT `ta`.`j1_id` FROM (SELECT `ta`.`j1_id` FROM `j1` AS `ta`) AS `derived_projection`

For Datafusion, the behavior of GenericDialect still executes. However, DuckDB with GenericDialect and MySQL with MySqlDialect both fail. I have not tested additional databases.

To Reproduce

Run a query that contains a subquery with a table reference that is not used in the top-level projection, like:

SELECT j1_id FROM (SELECT ta.j1_id AS j1_id FROM j1 ta)

Datafusion will re-write it and include the ta table reference in the top-level project, which does not exist outside of the subquery.

Expected behavior

Datafusion should probably remove the non-existent table reference from the top-level project. Alternatively, make subquery/derived table aliases mandatory for all dialects and re-write the table reference to the alias of the subquery like derived_projection.j1_id.

Additional context

Discussion where the bug was originally identified: #12994

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.

1 participant