Add the ability to create tables with deeply nested schemas in SQL #11746

Activity

1. adragomir

   added

   enhancementNew feature or requestNew feature or request

   on Jul 31, 2024
2. jayzhan211

   self-assigned this

   on Aug 9, 2024
3. jayzhan211 commented on Aug 9, 2024

   

   jayzhan211

   on Aug 9, 2024

   Last edited by jayzhan211

   Contributor

   More actions

   statement error DataFusion error: This feature is not implemented: Unsupported CAST from List\(Field \{ name: "item", data_type: Struct\(\[Field \{ name: "name", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: \{\} \}, Field \{ name: "info", data_type: Struct\(\[Field \{ name: "color", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: \{\} \}, Field \{ name: "size", data_type: Int64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: \{\} \}\]\), nullable: true, dict_id: 0, dict_is_ordered: false, metadata: \{\} \}\]\), nullable: true, dict_id: 0, dict_is_ordered: false, metadata: \{\} \}\) to Struct\(\[Field \{ name: "name", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: \{\} \}, Field \{ name: "info", data_type: List\(Field \{ name: "item", data_type: Struct\(\[Field \{ name: "color", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: \{\} \}, Field \{ name: "size", data_type: Int32, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: \{\} \}\]\), nullable: true, dict_id: 0, dict_is_ordered: false, metadata: \{\} \}\), nullable: true, dict_id: 0, dict_is_ordered: false, metadata: \{\} \}\]\)
   create table t (s struct<name varchar, info struct<color varchar, size int>>[]) as values 
   (
       [
           {'name': 'n1', 'info': {'color': 'r', 'size': 1}}, 
           {'name': 'n2', 'info': {'color': 'b', 'size': 2}}
       ]
   );
   

   We need to support such syntax in sqlparser.
   List of struct is tracked in apache/datafusion-sqlparser-rs#1372

   Since we mirror the behavior from duckdb for struct and map mostly, I prefer we switch to duckdb syntax for them in datafusion, so we can check against the result with copy&paste easily

   TODO:

   • Support syntax for struct in map
     CREATE TABLE tb2 (col MAP(INTEGER,STRUCT(a INTEGER, b VARCHAR)))
4. alamb commented on Feb 13, 2025

   

   alamb

   on Feb 13, 2025

   Contributor

   More actions

   I think this is woring wekk now

   > create table t as values
   (
       [
           {'name': 'n1', 'info': {'color': 'r', 'size': 1}},
           {'name': 'n2', 'info': {'color': 'b', 'size': 2}}
       ]
   );
   0 row(s) fetched.
   Elapsed 0.002 seconds.
   
   > select * from t;
   +--------------------------------------------------------------------------------+
   | column1                                                                        |
   +--------------------------------------------------------------------------------+
   | [{name: n1, info: {color: r, size: 1}}, {name: n2, info: {color: b, size: 2}}] |
   +--------------------------------------------------------------------------------+
   1 row(s) fetched.
   Elapsed 0.001 seconds.
   

   > create table t (s struct<name varchar, info struct<color varchar, size int>>[]);
   0 row(s) fetched.
   Elapsed 0.001 seconds.
   
   > select * from t;
   +---+
   | s |
   +---+
   +---+
   0 row(s) fetched.
   Elapsed 0.001 seconds.
5. alamb

   closed this as completedon Feb 13, 2025