Table names with periods are not handled correctly

[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.

[alamb]

I believe the issue is that this code does not handle " properly: https://github.com/apache/arrow-datafusion/blob/740a4fa2c6ba4b85875a433bb86e5b00435a5969/datafusion/common/src/table_reference.rs#L97

[alamb]

I am actively working on this issue

[waitingkuo]

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")

[waitingkuo]

@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);

[alamb]

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

[alamb]

Proposed PR to fix: #4530