Skip to content

SQL: WHERE-clauses don't support tables or schemas or catalogs #4872

Closed

Description

@crepererum

Describe the bug
Columns in WHERE clauses cannot be qualified by table or schema.

To Reproduce
These tests are added to information_schema.slt.

This works:

query CCC rowsort
SELECT * from information_schema.tables WHERE table_schema='information_schema';
----
datafusion information_schema columns VIEW
datafusion information_schema df_settings VIEW
datafusion information_schema tables VIEW
datafusion information_schema views VIEW

This fails:

query CCC rowsort
SELECT * from information_schema.tables WHERE tables.table_schema='information_schema';
----
datafusion information_schema columns VIEW
datafusion information_schema df_settings VIEW
datafusion information_schema tables VIEW
datafusion information_schema views VIEW

with

Error: SqlLogicTest(query failed: DataFusion error: Schema error: No field named 'tables'.'table_schema'. Valid fields are 'information_schema.tables'.'table_catalog', 'information_schema.tables'.'table_schema', 'information_schema.tables'.'table_name', 'information_schema.tables'.'table_type'.

This fails:

query CCC rowsort
SELECT * from information_schema.tables WHERE information_schema.tables.table_schema='information_schema';
----
datafusion information_schema columns VIEW
datafusion information_schema df_settings VIEW
datafusion information_schema tables VIEW
datafusion information_schema views VIEW

with

Error: SqlLogicTest(query failed: DataFusion error: Error during planning: Unsupported compound identifier 'Full { catalog: "information_schema", schema: "tables", table: "table_schema" }'

This fails:

query CCC rowsort
SELECT * from information_schema.tables WHERE datafusion.information_schema.tables.table_schema='information_schema';
----
datafusion information_schema columns VIEW
datafusion information_schema df_settings VIEW
datafusion information_schema tables VIEW
datafusion information_schema views VIEW

with

Error: SqlLogicTest(query failed: DataFusion error: Error during planning: Unsupported compound identifier '[Ident { value: "datafusion", quote_style: None }, Ident { value: "information_schema", quote_style: None }, Ident { value: "tables", quote_style: None }, Ident { value: "table_schema", quote_style: None }]'

Expected behavior
All tests pass.

Additional context
Tested w/ commit 556282a .

Activity

  1. Jefffrey commented on Feb 5, 2023

    @Jefffrey
    Contributor

    Most relevant part seems to be here (where most errors are originating from):

    https://github.com/apache/arrow-datafusion/blob/f8607c238d7c6dd1d33cf494722fa73466f73dd2/datafusion/sql/src/expr/identifier.rs#L73-L112

    However there might be need to refactor Column:

    https://github.com/apache/arrow-datafusion/blob/5dd5ffd5ea84d843b9ef34d0eaa9ac992618f6e2/datafusion/common/src/column.rs#L29-L34

    Specifically with how it treats relation as an Option<String>. The below SQL does work:

    SELECT * from information_schema.tables WHERE "information_schema.tables".table_schema='information_schema';

    Since the relation here is information_schema.tables as a whole. Could try to make relation an Option<OwnedTableReference> instead. I'll take a shot at implementing this and exploring what this could look like.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't working

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions