vincev commented on code in PR #6796: URL: https://github.com/apache/arrow-datafusion/pull/6796#discussion_r1278314487
########## datafusion/core/tests/sqllogictests/test_files/array.slt: ########## @@ -1794,9 +1802,150 @@ select make_array(f0) from fixed_size_list_array ---- [[1, 2], [3, 4]] +## Unnest -### Delete tables +# Set target partitions to 1 for deterministic results +statement ok +set datafusion.execution.target_partitions = 1; + +query ?? +select unnest(make_array(1,2,3)), + unnest(make_array(4,5)) +; +---- +1 4 +2 5 +3 NULL + +query ??? +select unnest(make_array(1,2,3)), + unnest(make_array(4,5)), + unnest(make_array(6,7,8,9)) +; +---- +1 4 6 +2 5 7 +3 NULL 8 +NULL NULL 9 + +query ??? +select unnest(make_array(1,2,3,4,5)), + unnest(make_array(6,7)), + unnest(make_array(8,9,10,11,22,33)) +; +---- +1 6 8 +2 7 9 +3 NULL 10 +4 NULL 11 +5 NULL 22 +NULL NULL 33 + +# Select From + +query IIIII +select * from unnest( + make_array(1), + make_array(2,3), + make_array(4,5,6), + make_array(7,8), + make_array(9) +); +---- Review Comment: I find having `unnest` in the `from` clause a bit strange, [DuckDB](https://duckdb.org/docs/sql/query_syntax/unnest.html) syntax looks intuitive, the above would look like: ``` D SELECT * FROM (VALUES ([1]), ([2, 3]), ([4,5,6]), ([7,8]), ([9])) tbl(l); ┌───────────┐ │ l │ │ int32[] │ ├───────────┤ │ [1] │ │ [2, 3] │ │ [4, 5, 6] │ │ [7, 8] │ │ [9] │ └───────────┘ ``` and then unnesting: ``` D SELECT unnest(l) FROM (VALUES ([1]), ([2, 3]), ([4,5,6]), ([7,8]), ([9])) tbl(l); ┌───────────┐ │ unnest(l) │ │ int32 │ ├───────────┤ │ 1 │ │ 2 │ │ 3 │ │ 4 │ │ 5 │ │ 6 │ │ 7 │ │ 8 │ │ 9 │ └───────────┘ ``` This would also work if we mix array and non array values: ``` D SELECT * FROM (VALUES ([11],1), ([21, 22],2), ([31,32,33],3), ([41,42],4), ([51],5)) tbl(l, m); ┌──────────────┬───────┐ │ l │ m │ │ int32[] │ int32 │ ├──────────────┼───────┤ │ [11] │ 1 │ │ [21, 22] │ 2 │ │ [31, 32, 33] │ 3 │ │ [41, 42] │ 4 │ │ [51] │ 5 │ └──────────────┴───────┘ ``` unnesting them: ``` D SELECT unnest(l), m FROM (VALUES ([11],1), ([21, 22],2), ([31,32,33],3), ([41,42],4), ([51],5)) tbl(l, m); ┌───────────┬───────┐ │ unnest(l) │ m │ │ int32 │ int32 │ ├───────────┼───────┤ │ 11 │ 1 │ │ 21 │ 2 │ │ 22 │ 2 │ │ 31 │ 3 │ │ 32 │ 3 │ │ 33 │ 3 │ │ 41 │ 4 │ │ 42 │ 4 │ │ 51 │ 5 │ └───────────┴───────┘ ``` ########## datafusion/core/tests/sqllogictests/test_files/array.slt: ########## @@ -1794,9 +1802,150 @@ select make_array(f0) from fixed_size_list_array ---- [[1, 2], [3, 4]] +## Unnest -### Delete tables +# Set target partitions to 1 for deterministic results +statement ok +set datafusion.execution.target_partitions = 1; + +query ?? +select unnest(make_array(1,2,3)), + unnest(make_array(4,5)) +; +---- +1 4 +2 5 +3 NULL + +query ??? +select unnest(make_array(1,2,3)), + unnest(make_array(4,5)), + unnest(make_array(6,7,8,9)) +; +---- +1 4 6 +2 5 7 +3 NULL 8 +NULL NULL 9 + +query ??? +select unnest(make_array(1,2,3,4,5)), + unnest(make_array(6,7)), + unnest(make_array(8,9,10,11,22,33)) +; +---- +1 6 8 +2 7 9 +3 NULL 10 +4 NULL 11 +5 NULL 22 +NULL NULL 33 + +# Select From + +query IIIII +select * from unnest( + make_array(1), + make_array(2,3), + make_array(4,5,6), + make_array(7,8), + make_array(9) +); +---- Review Comment: I find having `unnest` in the `from` clause a bit strange, [DuckDB](https://duckdb.org/docs/sql/query_syntax/unnest.html) syntax looks intuitive, the above would look like: ``` D SELECT * FROM (VALUES ([1]), ([2, 3]), ([4,5,6]), ([7,8]), ([9])) tbl(l); ┌───────────┐ │ l │ │ int32[] │ ├───────────┤ │ [1] │ │ [2, 3] │ │ [4, 5, 6] │ │ [7, 8] │ │ [9] │ └───────────┘ ``` and then unnesting: ``` D SELECT unnest(l) FROM (VALUES ([1]), ([2, 3]), ([4,5,6]), ([7,8]), ([9])) tbl(l); ┌───────────┐ │ unnest(l) │ │ int32 │ ├───────────┤ │ 1 │ │ 2 │ │ 3 │ │ 4 │ │ 5 │ │ 6 │ │ 7 │ │ 8 │ │ 9 │ └───────────┘ ``` This would also work if we mix array and non array values: ``` D SELECT * FROM (VALUES ([11],1), ([21, 22],2), ([31,32,33],3), ([41,42],4), ([51],5)) tbl(l, m); ┌──────────────┬───────┐ │ l │ m │ │ int32[] │ int32 │ ├──────────────┼───────┤ │ [11] │ 1 │ │ [21, 22] │ 2 │ │ [31, 32, 33] │ 3 │ │ [41, 42] │ 4 │ │ [51] │ 5 │ └──────────────┴───────┘ ``` unnesting them: ``` D SELECT unnest(l), m FROM (VALUES ([11],1), ([21, 22],2), ([31,32,33],3), ([41,42],4), ([51],5)) tbl(l, m); ┌───────────┬───────┐ │ unnest(l) │ m │ │ int32 │ int32 │ ├───────────┼───────┤ │ 11 │ 1 │ │ 21 │ 2 │ │ 22 │ 2 │ │ 31 │ 3 │ │ 32 │ 3 │ │ 33 │ 3 │ │ 41 │ 4 │ │ 42 │ 4 │ │ 51 │ 5 │ └───────────┴───────┘ ``` -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
