Skip to content

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

@alamb

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)

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