weishiuntsai commented on code in PR #17861:
URL: https://github.com/apache/druid/pull/17861#discussion_r2072139239
##########
quidem-ut/src/test/quidem/org.apache.druid.quidem.QTest/qaUnnest/array_sql_col_datatype_array.std.iq:
##########
@@ -0,0 +1,805 @@
+!set useApproximateCountDistinct false
+!use
druidtest://?componentSupplier=StandardComponentSupplier&datasets=sql/src/test/quidem/qatests/qaUnnest/array
+!set outputformat mysql
+SELECT *
+FROM test_unnest;
++-------------------------+-------+--------+--------------+--------------+------------------+----------------+--------------------+---------+--------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
+| __time | s_int | s_null | a_bool | a_int |
a_float | a_str | a_null | a_empty | a_mixed
| a_nested
|
++-------------------------+-------+--------+--------------+--------------+------------------+----------------+--------------------+---------+--------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
+| 2022-01-01 00:00:00.000 | 1 | | [1, 0, null] | [1, 2, null] |
[0.1, 0.2, null] | [S1, S2, null] | [null, null, null] | [] | [true, 1,
0.1, S1, null] |
[[true,false,null],[1,2,null],[0.1,0.2,null],["S1","S2",null],[null,null,null],[],[true,1,0.1,"S1",null],[[true,false,null],[1,2,null],[0.1,0.2,null],["S1","S2",null],[null,null,null],[],[true,1,0.1,"S1",null],[[true,false,null],[1,2,null],[0.1,0.2,null],["S1","S2",null],[null,null,null],[],[true,1,0.1,"S1",null]]]]
|
+| 2022-02-01 00:00:00.000 | 2 | | [null, 1, 0] | [null, 1, 2] |
[null, 0.1, 0.2] | [null, S1, S2] | [null, null, null] | [] | [null, true,
1, 0.1, S1] |
[[null,true,false],[null,1,2],[null,0.1,0.2],[null,"S1","S2"],[null,null,null],[],[null,true,1,0.1,"S1"],[[null,true,false],[null,1,2],[null,0.1,0.2],[null,"S1","S2"],[null,null,null],[],[null,true,1,0.1,"S1"],[[null,true,false],[null,1,2],[null,0.1,0.2],[null,"S1","S2"],[null,null,null],[],[null,true,1,0.1,"S1"]]]]
|
+| 2022-03-01 00:00:00.000 | | | [0, null, 1] | [2, null, 1] |
[0.2, null, 0.1] | [S2, null, S1] | [null, null, null] | [] | [S1, null,
true, 1, 0.1] |
[[false,null,true],[2,null,1],[0.2,null,0.1],["S2",null,"S1"],[null,null,null],[],["S1",null,true,1,0.1],[[false,null,true],[2,null,1],[0.2,null,0.1],["S2",null,"S1"],[null,null,null],[],["S1",null,true,1,0.1],[[false,null,true],[2,null,1],[0.2,null,0.1],["S2",null,"S1"],[null,null,null],[],["S1",null,true,1,0.1]]]]
|
++-------------------------+-------+--------+--------------+--------------+------------------+----------------+--------------------+---------+--------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A1_B1 TYPE: NEGATIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(s_int) AS u(c);
+Cannot apply
+!error
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A1_B2 TYPE: NEGATIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(s_int) AS u(c)
+GROUP BY c;
+Cannot apply
+!error
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A2_B1 TYPE: NEGATIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(s_null) AS u(c);
+Cannot apply
+!error
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A2_B2 TYPE: NEGATIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(s_null) AS u(c)
+GROUP BY c;
+Cannot apply
+!error
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A3_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(a_null) AS u(c);
++---+
+| c |
++---+
+| |
+| |
+| |
+| |
+| |
+| |
+| |
+| |
+| |
++---+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A3_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(a_null) AS u(c)
+GROUP BY c;
++---+
+| c |
++---+
+| |
++---+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A4_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(a_empty) AS u(c);
++---+
+| c |
++---+
++---+
+(0 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A4_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(a_empty) AS u(c)
+GROUP BY c;
++---+
+| c |
++---+
++---+
+(0 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A5_B1 TYPE: NEGATIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(a_nested) AS u(c);
+Cannot apply
+!error
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A5_B2 TYPE: NEGATIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(a_nested) AS u(c)
+GROUP BY c;
+Cannot apply
+!error
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A6_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM unnest(array[NULL, 1, 2]) AS u(c);
++---+
+| c |
++---+
+| 1 |
+| 2 |
+| |
++---+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A6_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM unnest(array[NULL, 1, 2]) AS u(c)
+GROUP BY c;
++---+-----+
+| c | cnt |
++---+-----+
+| 1 | 1 |
+| 2 | 1 |
+| | 1 |
++---+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A7_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(array_append(a_int, 9)) AS u(c);
++---+
+| c |
++---+
+| 1 |
+| 1 |
+| 1 |
+| 2 |
+| 2 |
+| 2 |
+| 9 |
+| 9 |
+| 9 |
+| |
+| |
+| |
++---+
+(12 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A7_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(array_append(a_int, 9)) AS u(c)
+GROUP BY c;
++---+
+| c |
++---+
+| 1 |
+| 2 |
+| 9 |
+| |
++---+
+(4 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A8_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(array_concat(a_int, a_int)) AS u(c);
++---+
+| c |
++---+
+| 1 |
+| 1 |
+| 1 |
+| 1 |
+| 1 |
+| 1 |
+| 2 |
+| 2 |
+| 2 |
+| 2 |
+| 2 |
+| 2 |
+| |
+| |
+| |
+| |
+| |
+| |
++---+
+(18 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A8_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(array_concat(a_int, a_int)) AS u(c)
+GROUP BY c;
++---+
+| c |
++---+
+| 1 |
+| 2 |
+| |
++---+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A9_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(array_slice(a_int, 0, 2)) AS u(c);
++---+
+| c |
++---+
+| 1 |
+| 1 |
+| 2 |
+| 2 |
+| |
+| |
++---+
+(6 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A9_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(array_slice(a_int, 0, 2)) AS u(c)
+GROUP BY c;
++---+
+| c |
++---+
+| 1 |
+| 2 |
+| |
++---+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A10_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(a_bool) AS u(c);
++---+
+| c |
++---+
+| 0 |
+| 0 |
+| 0 |
+| 1 |
+| 1 |
+| 1 |
+| |
+| |
+| |
++---+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A10_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(a_bool) AS u(c)
+GROUP BY c;
++---+
+| c |
++---+
+| 0 |
+| 1 |
+| |
++---+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A11_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(a_int) AS u(c);
++---+
+| c |
++---+
+| 1 |
+| 1 |
+| 1 |
+| 2 |
+| 2 |
+| 2 |
+| |
+| |
+| |
++---+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A11_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(a_int) AS u(c)
+GROUP BY c;
++---+
+| c |
++---+
+| 1 |
+| 2 |
+| |
++---+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A12_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(a_float) AS u(c);
++-----+
+| c |
++-----+
+| 0.1 |
+| 0.1 |
+| 0.1 |
+| 0.2 |
+| 0.2 |
+| 0.2 |
+| |
+| |
+| |
++-----+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A12_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(a_float) AS u(c)
+GROUP BY c;
++-----+
+| c |
++-----+
+| 0.1 |
+| 0.2 |
+| |
++-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A13_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(a_str) AS u(c);
++----+
+| c |
++----+
+| S1 |
+| S1 |
+| S1 |
+| S2 |
+| S2 |
+| S2 |
+| |
+| |
+| |
++----+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A13_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(a_str) AS u(c)
+GROUP BY c;
++----+
+| c |
++----+
+| S1 |
+| S2 |
+| |
++----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A14_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(a_mixed) AS u(c);
++------+
+| c |
++------+
+| 0.1 |
+| 0.1 |
+| 0.1 |
+| 1 |
+| 1 |
+| 1 |
+| S1 |
+| S1 |
+| S1 |
+| true |
+| true |
+| true |
+| |
+| |
+| |
++------+
+(15 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A14_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(a_mixed) AS u(c)
+GROUP BY c;
++------+
+| c |
++------+
+| 0.1 |
+| 1 |
+| S1 |
+| true |
+| |
++------+
+(5 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A15_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u(c);
++-------+
+| c |
++-------+
+| false |
+| false |
+| false |
+| true |
+| true |
+| true |
+| |
+| |
+| |
++-------+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A15_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u(c)
+GROUP BY c;
++-------+
+| c |
++-------+
+| false |
+| true |
+| |
++-------+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A16_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u(c);
++---+
+| c |
++---+
+| 1 |
+| 1 |
+| 1 |
+| 2 |
+| 2 |
+| 2 |
+| |
+| |
+| |
++---+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A16_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u(c)
+GROUP BY c;
++---+
+| c |
++---+
+| 1 |
+| 2 |
+| |
++---+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A17_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[2]' RETURNING DOUBLE array)) AS u(c);
++-----+
+| c |
++-----+
+| 0.1 |
+| 0.1 |
+| 0.1 |
+| 0.2 |
+| 0.2 |
+| 0.2 |
+| |
+| |
+| |
++-----+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A17_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[2]' RETURNING DOUBLE array)) AS u(c)
+GROUP BY c;
++-----+
+| c |
++-----+
+| 0.1 |
+| 0.2 |
+| |
++-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A18_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[3]' RETURNING varchar array)) AS u(c);
++----+
+| c |
++----+
+| S1 |
+| S1 |
+| S1 |
+| S2 |
+| S2 |
+| S2 |
+| |
+| |
+| |
++----+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A18_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[3]' RETURNING varchar array)) AS u(c)
+GROUP BY c;
++----+
+| c |
++----+
+| S1 |
+| S2 |
+| |
++----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A19_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[4]' RETURNING varchar array)) AS u(c);
++---+
+| c |
++---+
+| |
+| |
+| |
+| |
+| |
+| |
+| |
+| |
+| |
++---+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A19_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[4]' RETURNING varchar array)) AS u(c)
+GROUP BY c;
++---+
+| c |
++---+
+| |
++---+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A20_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[5]' RETURNING varchar array)) AS u(c);
++---+
+| c |
++---+
++---+
+(0 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A20_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[5]' RETURNING varchar array)) AS u(c)
+GROUP BY c;
++---+
+| c |
++---+
++---+
+(0 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A21_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[6]' RETURNING varchar array)) AS u(c);
++------+
+| c |
++------+
+| 0.1 |
+| 0.1 |
+| 0.1 |
+| 1 |
+| 1 |
+| 1 |
+| S1 |
+| S1 |
+| S1 |
+| true |
+| true |
+| true |
+| |
+| |
+| |
++------+
+(15 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_datatype_array TEST_ID: A21_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[6]' RETURNING varchar array)) AS u(c)
+GROUP BY c;
++------+
+| c |
++------+
+| 0.1 |
+| 1 |
+| S1 |
+| true |
+| |
++------+
+(5 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# Total query count 43 Positive tests: 37 Negative tests: 6
Review Comment:
Addressed in
https://github.com/apache/druid/pull/17861/commits/e2df8ea12b4b27df86b5fa28668eb38f738cc679
--
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]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
