Create fixed size list table with syntax <type name>[<length>]

[jayzhan211]

Is your feature request related to a problem or challenge?

To create a table with fixed size list, we can only do it by casting

statement ok
CREATE TABLE fixed_size_arrays
AS VALUES
  (arrow_cast(make_array(make_array(NULL, 2),make_array(3, NULL)), 'FixedSizeList(2, List(Int64))'), arrow_cast(make_array(1.1, 2.2, 3.3), 'FixedSizeList(3, Float64)'), arrow_cast(make_array('L', 'o', 'r', 'e', 'm'), 'FixedSizeList(5, Utf8)')),
  (arrow_cast(make_array(make_array(3, 4),make_array(5, 6)), 'FixedSizeList(2, List(Int64))'), arrow_cast(make_array(NULL, 5.5, 6.6), 'FixedSizeList(3, Float64)'), arrow_cast(make_array('i', 'p', NULL, 'u', 'm'), 'FixedSizeList(5, Utf8)')),
  (arrow_cast(make_array(make_array(5, 6),make_array(7, 8)), 'FixedSizeList(2, List(Int64))'), arrow_cast(make_array(7.7, 8.8, 9.9), 'FixedSizeList(3, Float64)'), arrow_cast(make_array('d', NULL, 'l', 'o', 'r'), 'FixedSizeList(5, Utf8)')),
  (arrow_cast(make_array(make_array(7, NULL),make_array(9, 10)), 'FixedSizeList(2, List(Int64))'), arrow_cast(make_array(10.1, NULL, 12.2), 'FixedSizeList(3, Float64)'), arrow_cast(make_array('s', 'i', 't', 'a', 'b'), 'FixedSizeList(5, Utf8)')),
  (NULL, arrow_cast(make_array(13.3, 14.4, 15.5), 'FixedSizeList(3, Float64)'), arrow_cast(make_array('a', 'm', 'e', 't', 'x'), 'FixedSizeList(5, Utf8)')),
  (arrow_cast(make_array(make_array(11, 12),make_array(13, 14)), 'FixedSizeList(2, List(Int64))'), NULL, arrow_cast(make_array(',','a','b','c','d'), 'FixedSizeList(5, Utf8)')),
  (arrow_cast(make_array(make_array(15, 16),make_array(NULL, 18)), 'FixedSizeList(2, List(Int64))'), arrow_cast(make_array(16.6, 17.7, 18.8), 'FixedSizeList(3, Float64)'), NULL)
;

In DuckDB, they has syntax like <type name> [<length>], which I think is quite nice

CREATE TABLE array_table (id INTEGER, arr INTEGER[3]);
INSERT INTO array_table VALUES (10, [1, 2, 3]), (20, [4, 5, 6]);

What we don't have the syntax supported yet, we need to fix the parser first

query error DataFusion error: SQL error: ParserError\("Expected \], found: 3"\)
create table t1 (a int[3]) as values ([1, 2, 3]), ([4, 5, 6]);

Describe the solution you'd like

Expect result

query
create table t1 (a int[3]) as values ([1, 2, 3]), ([4, 5, 6]);
----
[1,2,3]
[4,5,6]

with type FixedSizeList(i64)

Describe alternatives you've considered

No response

Additional context

No response

[vaibhawvipul]

I would like to work on this is no one else is working on it.

[vaibhawvipul]

take

[alamb]

Thanks @vaibhawvipul - I think the first thing to do is see what happens if you try to parse this syntax -- here is the sqlparser syntax: https://docs.rs/sqlparser/latest/sqlparser/ast/enum.DataType.html#

[vaibhawvipul]

@jayzhan211 Do we need this for external tables too?

[jayzhan211]

I'm not familiar with how an external table is created, but I think the change here does not matter whether the table is external or not. If it does matter, we can support the non-external table first

[alamb]

I think we may be able to use the syntax https://docs.rs/sqlparser/latest/sqlparser/ast/enum.DataType.html#variant.Array

So something like this to create a 256 integer array

CREATE TABLE foo(my_col INT[256])

This was added by @universalmind303 in https://github.com/sqlparser-rs/sqlparser-rs/issues/1230 (and will be available once https://github.com/apache/datafusion/pull/10392 gets merged -- shout out to @tisonkun and @jmhain)

[vaibhawvipul]

I think we may be able to use the syntax https://docs.rs/sqlparser/latest/sqlparser/ast/enum.DataType.html#variant.Array

So something like this to create a 256 integer array

CREATE TABLE foo(my_col INT[256])

This was added by @universalmind303 in sqlparser-rs/sqlparser-rs#1230 (and will be available once #10392 gets merged -- shout out to @tisonkun and @jmhain)

this is ready, I will start working on this now.