Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
jayzhan211 opened this issue Apr 29, 2024 · 6 comments · Fixed by #12810
Closed

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

jayzhan211 opened this issue Apr 29, 2024 · 6 comments · Fixed by #12810
Labels
enhancement New feature or request good first issue Good for newcomers

Comments

@jayzhan211
Copy link
Contributor

jayzhan211 commented Apr 29, 2024

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

@jayzhan211 jayzhan211 added the enhancement New feature or request label Apr 29, 2024
@vaibhawvipul
Copy link

vaibhawvipul commented Apr 30, 2024

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

@vaibhawvipul
Copy link

take

@alamb
Copy link
Contributor

alamb commented Apr 30, 2024

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#

@jayzhan211
Copy link
Contributor Author

jayzhan211 commented May 7, 2024

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
Copy link
Contributor

alamb commented May 7, 2024

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 apache/datafusion-sqlparser-rs#1230 (and will be available once #10392 gets merged -- shout out to @tisonkun and @jmhain)

@vaibhawvipul vaibhawvipul removed their assignment Oct 2, 2024
@jayzhan211 jayzhan211 added the good first issue Good for newcomers label Oct 7, 2024
@jandremarais
Copy link
Contributor

I want to make my first contribution and this issue looks simple enough.
take

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
enhancement New feature or request good first issue Good for newcomers
Projects
None yet
Development

Successfully merging a pull request may close this issue.

4 participants