Skip to content

CREATE EXTERNAL TABLE does not support schema (fully qualified names) #12607

Description

@alamb

Describe the bug

DataFusion will create external tables with qualified names as though they were single names

For example, this statement will create a table name "staging.foo" in the default schema, rather than a table named foo in the staging schema

create external table staging.foo stored as csv location 'foo.csv';

To Reproduce

Create foo.csv:

echo "x" > foo.csv
echo "1" >> foo.csv

Run SQL:

DataFusion CLI v42.0.0
> create schema staging;
0 row(s) fetched.
Elapsed 0.001 seconds.

> create external table staging.foo stored as csv location 'foo.csv';
0 row(s) fetched.
Elapsed 0.011 seconds.

> select * from staging.foo;
Error during planning: table 'datafusion.staging.foo' not found
> select * from "staging.foo";
+---+
| x |
+---+
| 1 |
+---+
1 row(s) fetched.
Elapsed 0.005 seconds.

Note the table is in the default (public) schema:

> show tables;
+---------------+--------------------+-------------+------------+
| table_catalog | table_schema       | table_name  | table_type |
+---------------+--------------------+-------------+------------+
| datafusion    | public             | staging.foo | BASE TABLE |
| datafusion    | information_schema | tables      | VIEW       |
| datafusion    | information_schema | views       | VIEW       |
| datafusion    | information_schema | columns     | VIEW       |
| datafusion    | information_schema | df_settings | VIEW       |
| datafusion    | information_schema | schemata    | VIEW       |
+---------------+--------------------+-------------+------------+
6 row(s) fetched.
Elapsed 0.002 seconds.

Expected behavior

The external table should be named foo in the staging schema

Note this works as expected for CREATE TABLE (just not CREATE EXTERNAL TABLE)

> create table staging.bar as values (2);
0 row(s) fetched.
Elapsed 0.006 seconds.

> select * from staging.bar;
+---------+
| column1 |
+---------+
| 2       |
+---------+
1 row(s) fetched.
Elapsed 0.001 seconds.

> show tables;
+---------------+--------------------+-------------+------------+
| table_catalog | table_schema       | table_name  | table_type |
+---------------+--------------------+-------------+------------+
| datafusion    | public             | staging.foo | BASE TABLE |
| datafusion    | staging            | bar         | BASE TABLE |
| datafusion    | information_schema | tables      | VIEW       |
| datafusion    | information_schema | views       | VIEW       |
| datafusion    | information_schema | columns     | VIEW       |
| datafusion    | information_schema | df_settings | VIEW       |
| datafusion    | information_schema | schemata    | VIEW       |
+---------------+--------------------+-------------+------------+
7 row(s) fetched.
Elapsed 0.004 seconds.

Additional context

Found by @matthewmturner in datafusion-contrib/datafusion-dft#154 (comment)

Activity

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

Metadata

Metadata

Assignees

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