Skip to content

Table names with periods are not handled correctly #4513

Closed

Description

@alamb

Describe the bug
We have a customer with a table whose name contains a period

In SQL you can refer to such tables using double quotes "foo.bar". Note this is different than foo.bar which means table named bar in the schema named foo.

You can see how this works in postgres:

postgres=# create table "foo.bar" as values (1), (2);
SELECT 2
postgres=# select * from foo.bar;
ERROR:  relation "foo.bar" does not exist
LINE 1: select * from foo.bar;
                      ^
postgres=# select * from "foo.bar";
 column1 
---------
       1
       2
(2 rows)

postgres=# \d foo.bar
Did not find any relation named "foo.bar".
postgres=# \d "foo.bar"
               Table "public.foo.bar"
 Column  |  Type   | Collation | Nullable | Default 
---------+---------+-----------+----------+---------
 column1 | integer |           |          | 


**To Reproduce**
Try to create (or query) tables with double quotes:

From the error message it is clear that DataFusion is treating `"foo.bar"`  as schema named `foo` table named `bar` 

```sql
❯ select * from "foo.bar";
Plan("failed to resolve schema: foo")
❯ select * from foo.bar;
Plan("failed to resolve schema: foo")
❯ create table "foo.bar" as values (1), (2);
Plan("failed to resolve schema: \"foo")

Expected behavior
DataFusion should treat "foo.bar" as a table named foo.bar like SQL / postgres

Additional context
Add any other context about the problem here.

Activity

  1. added
    bugSomething isn't working
    on Dec 5, 2022
  2. changed the title [-]Table names with periods are not supported correctly[/-] [+]Table names with periods are not handled correctly[/+] on Dec 5, 2022
  3. alamb commented on Dec 5, 2022

    @alamb
    ContributorAuthor
  4. self-assigned this
    on Dec 6, 2022
  5. alamb commented on Dec 6, 2022

    @alamb
    ContributorAuthor

    I am actively working on this issue

  6. waitingkuo commented on Dec 6, 2022

    @waitingkuo
    Contributor

    hi @alamb

    i tried to add this

            if s.len() >= 2 && s.chars().nth(0) == Some('"') && s.chars().last() == Some('"') {
                return Self::Bare {table: s}
            }

    to return the whole double quoted string before let parts: Vec<&str> = s.split('.').collect();

    and it works while creating a new table

    ❯ create table "foo.bar" as values (1), (2);
    0 rows in set. Query took 0.001 seconds.
    
    ❯ show tables;
    +---------------+--------------------+-------------+------------+
    | table_catalog | table_schema       | table_name  | table_type |
    +---------------+--------------------+-------------+------------+
    | datafusion    | public             | "foo.bar"   | BASE TABLE |
    | datafusion    | information_schema | tables      | VIEW       |
    | datafusion    | information_schema | views       | VIEW       |
    | datafusion    | information_schema | columns     | VIEW       |
    | datafusion    | information_schema | df_settings | VIEW       |
    +---------------+--------------------+-------------+------------+
    6 rows in set. Query took 0.003 seconds.

    but failed to select it

    ❯ select * from "foo.bar";
    Plan("failed to resolve schema: foo")
  7. waitingkuo commented on Dec 6, 2022

    @waitingkuo
    Contributor

    @alamb
    i also tested the single quoted string

    ❯ create table 'foo' as values (1), (2);
    0 rows in set. Query took 0.001 seconds.

    i'm not sure whether this is expected

    ❯ show tables;
    s information_schema.tables
    +---------------+--------------------+-------------+------------+
    | table_catalog | table_schema       | table_name  | table_type |
    +---------------+--------------------+-------------+------------+
    | datafusion    | public             | 'foo'       | BASE TABLE |
    | datafusion    | information_schema | tables      | VIEW       |
    | datafusion    | information_schema | views       | VIEW       |
    | datafusion    | information_schema | columns     | VIEW       |
    | datafusion    | information_schema | df_settings | VIEW       |
    +---------------+--------------------+-------------+------------+

    as postgresql doesn't support it

    willy=# create table 'foo' as values (1), (2);
    ERROR:  syntax error at or near "'foo'"
    LINE 1: create table 'foo' as values (1), (2);
  8. alamb commented on Dec 6, 2022

    @alamb
    ContributorAuthor

    I have a fix for this partially coded -- I'll get it up shortly

  9. alamb commented on Dec 6, 2022

    @alamb
    ContributorAuthor

    Proposed PR to fix: #4530

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