Skip to content

Unable to query view with a qualified name #6016

Closed

Description

@scsmithr

Describe the bug

Creating a view in a new schema then trying to querying that view using its qualified name results in an error. See below:

DataFusion CLI v22.0.0
❯ create schema s;
0 rows in set. Query took 0.007 seconds.
❯ create view s.v as (select 1 as a);
0 rows in set. Query took 0.004 seconds.
❯ select * from s.v;
Schema error: No field named s.v.a. Valid fields are "s.v".a.

I'm also not able to use fully qualified field names to select from this view either:

❯ select a from s.v;
Schema error: No field named s.v.a. Valid fields are "s.v".a.
❯ select s.v.a;
Schema error: No field named s.v.a.
❯ select "s.v".a;
Schema error: No field named s.v.a.

To Reproduce

Using datafusion-cli

DataFusion CLI v22.0.0
❯ create schema s;
0 rows in set. Query took 0.007 seconds.
❯ create view s.v as (select 1 as a);
0 rows in set. Query took 0.004 seconds.
❯ select * from s.v;
Schema error: No field named s.v.a. Valid fields are "s.v".a.

Expected behavior

I can run select * from s.v (along with the qualified alternatives).

Additional context

Also fails in DataFusion 21.1.0 & 21.0.0:

DataFusion CLI v21.1.0
❯ create schema s;
0 rows in set. Query took 0.007 seconds.
❯ create view s.v as select 1 as a;
0 rows in set. Query took 0.004 seconds.
❯ select * from s.v
;
Schema error: No field named "s"."v"."a". Valid fields are "s.v"."a".

Works as expected in 20.0.0

DataFusion CLI v20.0.0
❯ create schema s;
0 rows in set. Query took 0.008 seconds.
❯ create view s.v as select 1 as a;
0 rows in set. Query took 0.004 seconds.
❯ select * from s.v;
+---+
| a |
+---+
| 1 |
+---+
1 row in set. Query took 0.010 seconds.

Activity

  1. Jefffrey commented on Apr 15, 2023

    @Jefffrey
    Contributor

    Looks to be due to the InlineTableScan analyzer rule, specifically:

    https://github.com/apache/arrow-datafusion/blob/68bd113a74f045cd24d01418c92c83f2e3a18853/datafusion/optimizer/src/analyzer/inline_table_scan.rs#L56-L79

    Where it converts view into a subquery alias, using the table_name as the subquery alias:

    https://github.com/apache/arrow-datafusion/blob/68bd113a74f045cd24d01418c92c83f2e3a18853/datafusion/optimizer/src/analyzer/inline_table_scan.rs#L76

    But doesn't rewrite the parent projection to use this correct subquery alias.

    So given input plan:

    Projection: cat.sch.v.c1, cat.sch.v.c2, cat.sch.v.c3
      TableScan: cat.sch.v
    

    After the rule this becomes:

    Projection: cat.sch.v.c1, cat.sch.v.c2, cat.sch.v.c3
      SubqueryAlias: cat.sch.v
        Projection: aggregate_test_100.c1, aggregate_test_100.c2, aggregate_test_100.c3
          Projection: aggregate_test_100.c1, aggregate_test_100.c2, aggregate_test_100.c3
            TableScan: aggregate_test_100
    

    The top level projection, Projection: cat.sch.v.c1, cat.sch.v.c2, cat.sch.v.c3, taking only c1, is better understood as:

    Column { relation: Some(Full { catalog: "cat", schema: "sch", table: "v" }), name: "c1" }
    

    Can see it still refers to specific catalog, schema, and table. Whereas the subquery alias SubqueryAlias: cat.sch.v has erased this since the alias is cat.sch.v as a whole

  2. scsmithr commented on Apr 15, 2023

    @scsmithr
    ContributorAuthor

    This rule was in place before (but on the Optimizer). I believe changes made in #5343 are what led to this.

    I opened up #6022 which makes subquery aliases a table reference which should fix this issue.

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