Quote Nested Table Column Types

[jclum]

Nested column types should be quoted properly in information_schema selects, describe, and show columns statements.

To illustrate the situation, lets say I have a table like this:

create table my_table (foo row("bar varchar, baz" varchar))

The following works great:

insert into my_table values (ROW(ROW('my_value')))
select * from my_table;

But when I do a describe, instead of 1 column, it seems as if there are two columns rather than one:

trino> describe my_table2;
 Column |             Type              | Extra | Comment 
--------+-------------------------------+-------+---------
 foo    | row(bar varchar, baz varchar) |       | 

The same behavior happens with information_schema:

trino> select * from information_schema.columns where table_schema = 'my_schema' and table_name = 'my_table';
 table_catalog | table_schema | table_name | column_name | ordinal_position | column_default | is_nullable |           data_type           
---------------+--------------+------------+-------------+------------------+----------------+-------------+-------------------------------
 my_catalog         | my_schema     | my_table   | entry       |                1 | NULL           | YES         | row(foo VARCHAR, bar varchar)

As a side note, SHOW CREATE TABLE seems to quote nested columns.

[ebyhr]

Let me close this issue as it's basically a duplicate of #3584.

[findepi]

As a side note, SHOW CREATE TABLE seems to quote nested columns.

#3584 is about SHOW CREATE TABLE and @jclum reports this is not affected.
Maybe #3584 is obsolete?

[ebyhr]

Sorry, I was missing the last sentence. Yes, I think the issue is obsolete.

SHOW CREATE TABLE t;
          Create Table
---------------------------------
 CREATE TABLE memory.default.t (
    "table" varchar
 )

[jclum]

@findepi or @ebyhr we did some research into this and implemented in a fork. What we noticed is that adding quotes around everything is going to break currently existing queries against information schema. One thought we had was only quoting columns that are non alpha numeric underscore. Thoughts?

[cpcloud]

SHOW CREATE TABLE doesn't work for metadata queries on prepared SELECT statements, so it's not really enough to address the issue completely:

trino:default> prepare out from select cast(row(1) as row("a b c" bigint));
PREPARE
trino:default> describe output out;
 Column Name | Catalog | Schema | Table |       Type        | Type Size | Aliased
-------------+---------+--------+-------+-------------------+-----------+---------
 _col0       |         |        |       | row(a b c bigint) |         0 | false
(1 row)

Query 20240326_135048_10947_nxepw, FINISHED, 1 node
Splits: 1 total, 1 done (100.00%)
0.05 [0 rows, 0B] [0 rows/s, 0B/s]

Regarding the breakage of existing queries, isn't there a way to determine whether an identifier needs quoting when the type string is being produced such that identifiers that need quoting are quoted and existing ones that don't aren't quoted. The ones that need quoting can't be parsed today, so at least superficially it seems like this can be done with introducing anything backwards incompatible.

I guess that question is: are there identifiers that would appear to require quoting, that can be successfully parsed today without quotes?

[findepi]

I guess that question is: are there identifiers that would appear to require quoting, that can be successfully parsed today without quotes?

cc @martint