You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@druid.apache.org by ka...@apache.org on 2025/03/21 04:15:46 UTC
(druid) branch master updated: Add unnest tests to quidem (#17825)
This is an automated email from the ASF dual-hosted git repository.
karan pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/druid.git
The following commit(s) were added to refs/heads/master by this push:
new b54c12fa0b5 Add unnest tests to quidem (#17825)
b54c12fa0b5 is described below
commit b54c12fa0b53dc47f186485f8ece0d458f72b993
Author: Andy Tsai <61...@users.noreply.github.com>
AuthorDate: Thu Mar 20 21:15:39 2025 -0700
Add unnest tests to quidem (#17825)
This PR adds the sql-native unnest tests to quidem. This set of tests has 6392 queries in total, with 5247 positive tests and 1145 negative tests.
---
.../qaUnnest_array_sql.iq | 29891 ++++++++++++
.../qaUnnest_array_sql_filter.iq | 24180 ++++++++++
.../qaUnnest_array_sql_ops.iq | 309 +
.../qaUnnest_array_sql_other_funcs.iq | 20689 ++++++++
.../qaUnnest_array_sql_scalar_funcs.iq | 7011 +++
.../qaUnnest_mv_sql.iq | 21585 +++++++++
.../qaUnnest_mv_sql_filter.iq | 46780 +++++++++++++++++++
.../qaUnnest_mv_sql_ops.iq | 331 +
.../qaUnnest_mv_sql_other_funcs.iq | 7720 +++
.../qaUnnest_mv_sql_scalar_funcs.iq | 7220 +++
.../test/quidem/qatests/qaUnnest/array/ingest.json | 92 +
.../test/quidem/qatests/qaUnnest/mv/ingest.json | 112 +
12 files changed, 165920 insertions(+)
diff --git a/quidem-ut/src/test/quidem/org.apache.druid.quidem.QTest/qaUnnest_array_sql.iq b/quidem-ut/src/test/quidem/org.apache.druid.quidem.QTest/qaUnnest_array_sql.iq
new file mode 100644
index 00000000000..45966103fc5
--- /dev/null
+++ b/quidem-ut/src/test/quidem/org.apache.druid.quidem.QTest/qaUnnest_array_sql.iq
@@ -0,0 +1,29891 @@
+!set useApproximateCountDistinct false
+!use druidtest://?componentSupplier=StandardComponentSupplier&datasets=sql/src/test/quidem/qatests/qaUnnest/array&numMergeBuffers=3
+!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_general TEST_ID: A1_B1 TYPE: NEGATIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(doesnt_exist) AS u(c);
+not found
+!error
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_general TEST_ID: A1_B2 TYPE: NEGATIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(doesnt_exist) AS u(c)
+GROUP BY c;
+not found
+!error
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_general TEST_ID: A1_B3 TYPE: NEGATIVE TEST
+#-------------------------------------------------------------------------
+SELECT *
+FROM test_unnest,
+ unnest(a_int) AS u(a_int);
+is ambiguous
+!error
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_general TEST_ID: A1_B4 TYPE: NEGATIVE TEST
+#-------------------------------------------------------------------------
+SELECT *
+FROM test_unnest,
+ unnest(a_int) AS u(a_int)
+GROUP BY c;
+is ambiguous
+!error
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_general TEST_ID: A1_B5 TYPE: NEGATIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int0
+FROM test_unnest,
+ unnest(a_int) AS u(s_int);
+not found in any table
+!error
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_general TEST_ID: A1_B6 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT u.s_int
+FROM test_unnest,
+ unnest(a_int) AS u(s_int);
++-------+
+| s_int |
++-------+
+| 1 |
+| 1 |
+| 1 |
+| 2 |
+| 2 |
+| 2 |
+| |
+| |
+| |
++-------+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_general TEST_ID: A1_B7 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT count(*)
+FROM
+ (SELECT u.s_int
+ FROM test_unnest,
+ unnest(a_int) AS u(s_int));
++--------+
+| EXPR$0 |
++--------+
+| 9 |
++--------+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_general TEST_ID: A1_B8 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT count(*)
+FROM test_unnest,
+ unnest(a_int) AS u(s_int);
++--------+
+| EXPR$0 |
++--------+
+| 9 |
++--------+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_general TEST_ID: A1_B9 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int0
+FROM
+ (SELECT *
+ FROM test_unnest,
+ unnest(a_int) AS u(s_int));
++--------+
+| s_int0 |
++--------+
+| 1 |
+| 1 |
+| 1 |
+| 2 |
+| 2 |
+| 2 |
+| |
+| |
+| |
++--------+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_general TEST_ID: A1_B10 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT count(*) cnt
+FROM
+ (SELECT *
+ FROM test_unnest,
+ unnest(a_int) AS u(s_int));
++-----+
+| cnt |
++-----+
+| 9 |
++-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_general TEST_ID: A1_B11 TYPE: NEGATIVE TEST
+#-------------------------------------------------------------------------
+SELECT u.s_int
+FROM
+ (SELECT *
+ FROM test_unnest,
+ unnest(a_int) AS u(s_int));
+not found
+!error
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_general TEST_ID: A1_B12 TYPE: NEGATIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int0,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_int) AS u(s_int)
+GROUP BY s_int0;
+not found in any table
+!error
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_general TEST_ID: A1_B13 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT u.s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_int) AS u(s_int)
+GROUP BY u.s_int;
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
++-------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_general TEST_ID: A1_B14 TYPE: NEGATIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ strlen(unnest(a_int)) AS u(c);
+unexpected token
+!error
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_general TEST_ID: A1_B15 TYPE: NEGATIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ strlen(unnest(a_int)) AS u(c)
+GROUP BY c;
+unexpected token
+!error
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_general TEST_ID: A1_B16 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_general TEST_ID: A1_B17 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_general TEST_ID: A1_B18 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_general TEST_ID: A1_B19 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_general TEST_ID: A1_B20 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT (c || 'Z') col
+FROM test_unnest,
+ unnest(a_int) AS u(c);
++-----+
+| col |
++-----+
+| 1Z |
+| 1Z |
+| 1Z |
+| 2Z |
+| 2Z |
+| 2Z |
+| |
+| |
+| |
++-----+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_general TEST_ID: A1_B21 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT (c || 'Z') col
+FROM test_unnest,
+ unnest(a_int) AS u(c)
+GROUP BY c;
++-----+
+| col |
++-----+
+| 1Z |
+| 2Z |
+| |
++-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_general TEST_ID: A1_B22 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT length(c) col
+FROM test_unnest,
+ unnest(a_int) AS u(c);
++-----+
+| col |
++-----+
+| 1 |
+| 1 |
+| 1 |
+| 1 |
+| 1 |
+| 1 |
+| |
+| |
+| |
++-----+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_general TEST_ID: A1_B23 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT length(c) col
+FROM test_unnest,
+ unnest(a_int) AS u(c)
+GROUP BY c;
++-----+
+| col |
++-----+
+| 1 |
+| 1 |
+| |
++-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_general TEST_ID: A1_B24 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT length(c || 'Z') col
+FROM test_unnest,
+ unnest(a_int) AS u(c);
++-----+
+| col |
++-----+
+| 2 |
+| 2 |
+| 2 |
+| 2 |
+| 2 |
+| 2 |
+| |
+| |
+| |
++-----+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_general TEST_ID: A1_B25 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT length(c || 'Z') col
+FROM test_unnest,
+ unnest(a_int) AS u(c)
+GROUP BY c;
++-----+
+| col |
++-----+
+| 2 |
+| 2 |
+| |
++-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_general TEST_ID: A1_B26 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT length(c || c) col
+FROM test_unnest,
+ unnest(a_int) AS u(c);
++-----+
+| col |
++-----+
+| 2 |
+| 2 |
+| 2 |
+| 2 |
+| 2 |
+| 2 |
+| |
+| |
+| |
++-----+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_general TEST_ID: A1_B27 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT length(c || c) col
+FROM test_unnest,
+ unnest(a_int) AS u(c)
+GROUP BY c;
++-----+
+| col |
++-----+
+| 2 |
+| 2 |
+| |
++-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_general TEST_ID: A1_B28 TYPE: NEGATIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ a_int AS new_col
+FROM test_unnest,
+ unnest(new_col) AS u(c);
+not found in any table
+!error
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_general TEST_ID: A1_B29 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM
+ (SELECT a_int AS new_col
+ FROM test_unnest) t1,
+ unnest(new_col) AS u(c);
++---+
+| c |
++---+
+| 1 |
+| 1 |
+| 1 |
+| 2 |
+| 2 |
+| 2 |
+| |
+| |
+| |
++---+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_col_general TEST_ID: A1_B30 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c AS new_col
+FROM test_unnest,
+ unnest(a_int) AS u(c);
++---------+
+| new_col |
++---------+
+| 1 |
+| 1 |
+| 1 |
+| 2 |
+| 2 |
+| 2 |
+| |
+| |
+| |
++---------+
+(9 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
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_explain TEST_ID: A1_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
+
+{
+ "queryType" : "scan",
+ "dataSource" : {
+ "type" : "unnest",
+ "base" : {
+ "type" : "table",
+ "name" : "test_unnest"
+ },
+ "virtualColumn" : {
+ "type" : "expression",
+ "name" : "j0.unnest",
+ "expression" : "\"a_int\"",
+ "outputType" : "ARRAY<LONG>"
+ },
+ "unnestFilter" : null
+ },
+ "intervals" : {
+ "type" : "intervals",
+ "intervals" : [ "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z" ]
+ },
+ "resultFormat" : "compactedList",
+ "columns" : [ "j0.unnest" ],
+ "columnTypes" : [ "LONG" ],
+ "granularity" : {
+ "type" : "all"
+ },
+ "legacy" : false
+}
+!nativePlan
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_explain TEST_ID: A1_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+
+SELECT *
+FROM
+ (SELECT *
+ FROM test_unnest,
+ unnest(a_int) AS u(c));
++-------------------------+-------+--------+--------------+--------------+------------------+----------------+--------------------+---------+--------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---+
+| __time | s_int | s_null | a_bool | a_int | a_float | a_str | a_null | a_empty | a_mixed | a_nested | c |
++-------------------------+-------+--------+--------------+--------------+------------------+----------------+--------------------+---------+--------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---+
+| 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]]]] | 1 |
+| 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]]]] | 2 |
+| 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"]]]] | 1 |
+| 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"]]]] | 2 |
+| 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]]]] | 1 |
+| 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]]]] | 2 |
+| 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]]]] | |
++-------------------------+-------+--------+--------------+--------------+------------------+----------------+--------------------+---------+--------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---+
+(9 rows)
+
+!ok
+
+{
+ "queryType" : "scan",
+ "dataSource" : {
+ "type" : "unnest",
+ "base" : {
+ "type" : "table",
+ "name" : "test_unnest"
+ },
+ "virtualColumn" : {
+ "type" : "expression",
+ "name" : "j0.unnest",
+ "expression" : "\"a_int\"",
+ "outputType" : "ARRAY<LONG>"
+ },
+ "unnestFilter" : null
+ },
+ "intervals" : {
+ "type" : "intervals",
+ "intervals" : [ "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z" ]
+ },
+ "resultFormat" : "compactedList",
+ "columns" : [ "__time", "s_int", "s_null", "a_bool", "a_int", "a_float", "a_str", "a_null", "a_empty", "a_mixed", "a_nested", "j0.unnest" ],
+ "columnTypes" : [ "LONG", "LONG", "STRING", "ARRAY<LONG>", "ARRAY<LONG>", "ARRAY<DOUBLE>", "ARRAY<STRING>", "ARRAY<LONG>", "ARRAY<LONG>", "ARRAY<STRING>", "COMPLEX<json>", "LONG" ],
+ "granularity" : {
+ "type" : "all"
+ },
+ "legacy" : false
+}
+!nativePlan
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_explain TEST_ID: A1_B3 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_int) AS u(c)
+GROUP BY c;
++---+-----+
+| c | cnt |
++---+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
++---+-----+
+(3 rows)
+
+!ok
+
+{
+ "queryType" : "groupBy",
+ "dataSource" : {
+ "type" : "unnest",
+ "base" : {
+ "type" : "table",
+ "name" : "test_unnest"
+ },
+ "virtualColumn" : {
+ "type" : "expression",
+ "name" : "j0.unnest",
+ "expression" : "\"a_int\"",
+ "outputType" : "ARRAY<LONG>"
+ },
+ "unnestFilter" : null
+ },
+ "intervals" : {
+ "type" : "intervals",
+ "intervals" : [ "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z" ]
+ },
+ "granularity" : {
+ "type" : "all"
+ },
+ "dimensions" : [ {
+ "type" : "default",
+ "dimension" : "j0.unnest",
+ "outputName" : "d0",
+ "outputType" : "LONG"
+ } ],
+ "aggregations" : [ {
+ "type" : "count",
+ "name" : "a0"
+ } ],
+ "limitSpec" : {
+ "type" : "NoopLimitSpec"
+ }
+}
+!nativePlan
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_count TEST_ID: A1_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT count(*) cnt
+FROM test_unnest,
+ unnest(a_bool) AS u(c);
++-----+
+| cnt |
++-----+
+| 9 |
++-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_count TEST_ID: A1_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT count(c) cnt
+FROM test_unnest,
+ unnest(a_bool) AS u(c);
++-----+
+| cnt |
++-----+
+| 6 |
++-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_count TEST_ID: A1_B3 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT count(DISTINCT c) cnt
+FROM test_unnest,
+ unnest(a_bool) AS u(c);
++-----+
+| cnt |
++-----+
+| 2 |
++-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_count TEST_ID: A2_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT count(*) cnt
+FROM test_unnest,
+ unnest(a_int) AS u(c);
++-----+
+| cnt |
++-----+
+| 9 |
++-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_count TEST_ID: A2_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT count(c) cnt
+FROM test_unnest,
+ unnest(a_int) AS u(c);
++-----+
+| cnt |
++-----+
+| 6 |
++-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_count TEST_ID: A2_B3 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT count(DISTINCT c) cnt
+FROM test_unnest,
+ unnest(a_int) AS u(c);
++-----+
+| cnt |
++-----+
+| 2 |
++-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_count TEST_ID: A3_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT count(*) cnt
+FROM test_unnest,
+ unnest(a_float) AS u(c);
++-----+
+| cnt |
++-----+
+| 9 |
++-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_count TEST_ID: A3_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT count(c) cnt
+FROM test_unnest,
+ unnest(a_float) AS u(c);
++-----+
+| cnt |
++-----+
+| 6 |
++-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_count TEST_ID: A3_B3 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT count(DISTINCT c) cnt
+FROM test_unnest,
+ unnest(a_float) AS u(c);
++-----+
+| cnt |
++-----+
+| 2 |
++-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_count TEST_ID: A4_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT count(*) cnt
+FROM test_unnest,
+ unnest(a_str) AS u(c);
++-----+
+| cnt |
++-----+
+| 9 |
++-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_count TEST_ID: A4_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT count(c) cnt
+FROM test_unnest,
+ unnest(a_str) AS u(c);
++-----+
+| cnt |
++-----+
+| 6 |
++-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_count TEST_ID: A4_B3 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT count(DISTINCT c) cnt
+FROM test_unnest,
+ unnest(a_str) AS u(c);
++-----+
+| cnt |
++-----+
+| 2 |
++-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_count TEST_ID: A5_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT count(*) cnt
+FROM test_unnest,
+ unnest(a_mixed) AS u(c);
++-----+
+| cnt |
++-----+
+| 15 |
++-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_count TEST_ID: A5_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT count(c) cnt
+FROM test_unnest,
+ unnest(a_mixed) AS u(c);
++-----+
+| cnt |
++-----+
+| 12 |
++-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_count TEST_ID: A5_B3 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT count(DISTINCT c) cnt
+FROM test_unnest,
+ unnest(a_mixed) AS u(c);
++-----+
+| cnt |
++-----+
+| 4 |
++-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_count TEST_ID: A6_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u(c);
++-----+
+| cnt |
++-----+
+| 9 |
++-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_count TEST_ID: A6_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT count(c) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u(c);
++-----+
+| cnt |
++-----+
+| 6 |
++-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_count TEST_ID: A6_B3 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT count(DISTINCT c) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u(c);
++-----+
+| cnt |
++-----+
+| 2 |
++-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_count TEST_ID: A7_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u(c);
++-----+
+| cnt |
++-----+
+| 9 |
++-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_count TEST_ID: A7_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT count(c) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u(c);
++-----+
+| cnt |
++-----+
+| 6 |
++-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_count TEST_ID: A7_B3 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT count(DISTINCT c) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u(c);
++-----+
+| cnt |
++-----+
+| 2 |
++-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_count TEST_ID: A8_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[2]' RETURNING DOUBLE array)) AS u(c);
++-----+
+| cnt |
++-----+
+| 9 |
++-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_count TEST_ID: A8_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT count(c) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[2]' RETURNING DOUBLE array)) AS u(c);
++-----+
+| cnt |
++-----+
+| 6 |
++-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_count TEST_ID: A8_B3 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT count(DISTINCT c) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[2]' RETURNING DOUBLE array)) AS u(c);
++-----+
+| cnt |
++-----+
+| 2 |
++-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_count TEST_ID: A9_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[3]' RETURNING varchar array)) AS u(c);
++-----+
+| cnt |
++-----+
+| 9 |
++-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_count TEST_ID: A9_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT count(c) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[3]' RETURNING varchar array)) AS u(c);
++-----+
+| cnt |
++-----+
+| 6 |
++-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_count TEST_ID: A9_B3 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT count(DISTINCT c) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[3]' RETURNING varchar array)) AS u(c);
++-----+
+| cnt |
++-----+
+| 2 |
++-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_count TEST_ID: A10_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[4]' RETURNING varchar array)) AS u(c);
++-----+
+| cnt |
++-----+
+| 9 |
++-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_count TEST_ID: A10_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT count(c) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[4]' RETURNING varchar array)) AS u(c);
++-----+
+| cnt |
++-----+
+| 0 |
++-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_count TEST_ID: A10_B3 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT count(DISTINCT c) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[4]' RETURNING varchar array)) AS u(c);
++-----+
+| cnt |
++-----+
+| 0 |
++-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_count TEST_ID: A11_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[5]' RETURNING varchar array)) AS u(c);
++-----+
+| cnt |
++-----+
+| 0 |
++-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_count TEST_ID: A11_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT count(c) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[5]' RETURNING varchar array)) AS u(c);
++-----+
+| cnt |
++-----+
+| 0 |
++-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_count TEST_ID: A11_B3 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT count(DISTINCT c) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[5]' RETURNING varchar array)) AS u(c);
++-----+
+| cnt |
++-----+
+| 0 |
++-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_count TEST_ID: A12_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[6]' RETURNING varchar array)) AS u(c);
++-----+
+| cnt |
++-----+
+| 15 |
++-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_count TEST_ID: A12_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT count(c) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[6]' RETURNING varchar array)) AS u(c);
++-----+
+| cnt |
++-----+
+| 12 |
++-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_count TEST_ID: A12_B3 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT count(DISTINCT c) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[6]' RETURNING varchar array)) AS u(c);
++-----+
+| cnt |
++-----+
+| 4 |
++-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_distinct TEST_ID: A1_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT DISTINCT c
+FROM test_unnest,
+ unnest(a_bool) AS u(c);
++---+
+| c |
++---+
+| 0 |
+| 1 |
+| |
++---+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_distinct TEST_ID: A1_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT DISTINCT c
+FROM test_unnest,
+ unnest(a_bool) AS u(c)
+GROUP BY c;
++---+
+| c |
++---+
+| 0 |
+| 1 |
+| |
++---+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_distinct TEST_ID: A2_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT DISTINCT c
+FROM test_unnest,
+ unnest(a_int) AS u(c);
++---+
+| c |
++---+
+| 1 |
+| 2 |
+| |
++---+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_distinct TEST_ID: A2_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT DISTINCT c
+FROM test_unnest,
+ unnest(a_int) AS u(c)
+GROUP BY c;
++---+
+| c |
++---+
+| 1 |
+| 2 |
+| |
++---+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_distinct TEST_ID: A3_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT DISTINCT c
+FROM test_unnest,
+ unnest(a_float) AS u(c);
++-----+
+| c |
++-----+
+| 0.1 |
+| 0.2 |
+| |
++-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_distinct TEST_ID: A3_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT DISTINCT c
+FROM test_unnest,
+ unnest(a_float) AS u(c)
+GROUP BY c;
++-----+
+| c |
++-----+
+| 0.1 |
+| 0.2 |
+| |
++-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_distinct TEST_ID: A4_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT DISTINCT c
+FROM test_unnest,
+ unnest(a_str) AS u(c);
++----+
+| c |
++----+
+| S1 |
+| S2 |
+| |
++----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_distinct TEST_ID: A4_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT DISTINCT c
+FROM test_unnest,
+ unnest(a_str) AS u(c)
+GROUP BY c;
++----+
+| c |
++----+
+| S1 |
+| S2 |
+| |
++----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_distinct TEST_ID: A5_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT DISTINCT c
+FROM test_unnest,
+ unnest(a_mixed) AS u(c);
++------+
+| c |
++------+
+| 0.1 |
+| 1 |
+| S1 |
+| true |
+| |
++------+
+(5 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_distinct TEST_ID: A5_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT DISTINCT c
+FROM test_unnest,
+ unnest(a_mixed) AS u(c)
+GROUP BY c;
++------+
+| c |
++------+
+| 0.1 |
+| 1 |
+| S1 |
+| true |
+| |
++------+
+(5 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_distinct TEST_ID: A6_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT DISTINCT c
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u(c);
++-------+
+| c |
++-------+
+| false |
+| true |
+| |
++-------+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_distinct TEST_ID: A6_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT DISTINCT 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_select_distinct TEST_ID: A7_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT DISTINCT c
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u(c);
++---+
+| c |
++---+
+| 1 |
+| 2 |
+| |
++---+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_distinct TEST_ID: A7_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT DISTINCT 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_select_distinct TEST_ID: A8_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT DISTINCT c
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[2]' RETURNING DOUBLE array)) AS u(c);
++-----+
+| c |
++-----+
+| 0.1 |
+| 0.2 |
+| |
++-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_distinct TEST_ID: A8_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT DISTINCT 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_select_distinct TEST_ID: A9_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT DISTINCT c
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[3]' RETURNING varchar array)) AS u(c);
++----+
+| c |
++----+
+| S1 |
+| S2 |
+| |
++----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_distinct TEST_ID: A9_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT DISTINCT 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_select_distinct TEST_ID: A10_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT DISTINCT c
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[4]' RETURNING varchar array)) AS u(c);
++---+
+| c |
++---+
+| |
++---+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_distinct TEST_ID: A10_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT DISTINCT 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_select_distinct TEST_ID: A11_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT DISTINCT c
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[5]' RETURNING varchar array)) AS u(c);
++---+
+| c |
++---+
++---+
+(0 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_distinct TEST_ID: A11_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT DISTINCT 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_select_distinct TEST_ID: A12_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT DISTINCT c
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[6]' RETURNING varchar array)) AS u(c);
++------+
+| c |
++------+
+| 0.1 |
+| 1 |
+| S1 |
+| true |
+| |
++------+
+(5 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_select_distinct TEST_ID: A12_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT DISTINCT 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
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A6_B6_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u2(c2);
++-------+-------+
+| c1 | c2 |
++-------+-------+
+| false | false |
+| false | false |
+| false | false |
+| false | true |
+| false | true |
+| false | true |
+| false | |
+| false | |
+| false | |
+| true | false |
+| true | false |
+| true | false |
+| true | true |
+| true | true |
+| true | true |
+| true | |
+| true | |
+| true | |
+| | false |
+| | false |
+| | false |
+| | true |
+| | true |
+| | true |
+| | |
+| | |
+| | |
++-------+-------+
+(27 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A6_B7_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u2(c2);
++-------+----+
+| c1 | c2 |
++-------+----+
+| false | 1 |
+| false | 1 |
+| false | 1 |
+| false | 2 |
+| false | 2 |
+| false | 2 |
+| false | |
+| false | |
+| false | |
+| true | 1 |
+| true | 1 |
+| true | 1 |
+| true | 2 |
+| true | 2 |
+| true | 2 |
+| true | |
+| true | |
+| true | |
+| | 1 |
+| | 1 |
+| | 1 |
+| | 2 |
+| | 2 |
+| | 2 |
+| | |
+| | |
+| | |
++-------+----+
+(27 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A6_B8_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[2]' RETURNING DOUBLE array)) AS u2(c2);
++-------+-----+
+| c1 | c2 |
++-------+-----+
+| false | 0.1 |
+| false | 0.1 |
+| false | 0.1 |
+| false | 0.2 |
+| false | 0.2 |
+| false | 0.2 |
+| false | |
+| false | |
+| false | |
+| true | 0.1 |
+| true | 0.1 |
+| true | 0.1 |
+| true | 0.2 |
+| true | 0.2 |
+| true | 0.2 |
+| true | |
+| true | |
+| true | |
+| | 0.1 |
+| | 0.1 |
+| | 0.1 |
+| | 0.2 |
+| | 0.2 |
+| | 0.2 |
+| | |
+| | |
+| | |
++-------+-----+
+(27 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A6_B9_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[3]' RETURNING varchar array)) AS u2(c2);
++-------+----+
+| c1 | c2 |
++-------+----+
+| false | S1 |
+| false | S1 |
+| false | S1 |
+| false | S2 |
+| false | S2 |
+| false | S2 |
+| false | |
+| false | |
+| false | |
+| true | S1 |
+| true | S1 |
+| true | S1 |
+| true | S2 |
+| true | S2 |
+| true | S2 |
+| true | |
+| true | |
+| true | |
+| | S1 |
+| | S1 |
+| | S1 |
+| | S2 |
+| | S2 |
+| | S2 |
+| | |
+| | |
+| | |
++-------+----+
+(27 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A6_B10_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[4]' RETURNING varchar array)) AS u2(c2);
++-------+----+
+| c1 | c2 |
++-------+----+
+| false | |
+| false | |
+| false | |
+| false | |
+| false | |
+| false | |
+| false | |
+| false | |
+| false | |
+| true | |
+| true | |
+| true | |
+| true | |
+| true | |
+| true | |
+| true | |
+| true | |
+| true | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
++-------+----+
+(27 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A6_B11_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[5]' RETURNING varchar array)) AS u2(c2);
++----+----+
+| c1 | c2 |
++----+----+
++----+----+
+(0 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A6_B12_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[6]' RETURNING varchar array)) AS u2(c2);
++-------+------+
+| c1 | c2 |
++-------+------+
+| false | 0.1 |
+| false | 0.1 |
+| false | 0.1 |
+| false | 1 |
+| false | 1 |
+| false | 1 |
+| false | S1 |
+| false | S1 |
+| false | S1 |
+| false | true |
+| false | true |
+| false | true |
+| false | |
+| false | |
+| false | |
+| true | 0.1 |
+| true | 0.1 |
+| true | 0.1 |
+| true | 1 |
+| true | 1 |
+| true | 1 |
+| true | S1 |
+| true | S1 |
+| true | S1 |
+| true | true |
+| true | true |
+| true | true |
+| true | |
+| true | |
+| true | |
+| | 0.1 |
+| | 0.1 |
+| | 0.1 |
+| | 1 |
+| | 1 |
+| | 1 |
+| | S1 |
+| | S1 |
+| | S1 |
+| | true |
+| | true |
+| | true |
+| | |
+| | |
+| | |
++-------+------+
+(45 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A7_B6_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u2(c2);
++----+-------+
+| c1 | c2 |
++----+-------+
+| 1 | false |
+| 1 | false |
+| 1 | false |
+| 1 | true |
+| 1 | true |
+| 1 | true |
+| 1 | |
+| 1 | |
+| 1 | |
+| 2 | false |
+| 2 | false |
+| 2 | false |
+| 2 | true |
+| 2 | true |
+| 2 | true |
+| 2 | |
+| 2 | |
+| 2 | |
+| | false |
+| | false |
+| | false |
+| | true |
+| | true |
+| | true |
+| | |
+| | |
+| | |
++----+-------+
+(27 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A7_B7_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u2(c2);
++----+----+
+| c1 | c2 |
++----+----+
+| 1 | 1 |
+| 1 | 1 |
+| 1 | 1 |
+| 1 | 2 |
+| 1 | 2 |
+| 1 | 2 |
+| 1 | |
+| 1 | |
+| 1 | |
+| 2 | 1 |
+| 2 | 1 |
+| 2 | 1 |
+| 2 | 2 |
+| 2 | 2 |
+| 2 | 2 |
+| 2 | |
+| 2 | |
+| 2 | |
+| | 1 |
+| | 1 |
+| | 1 |
+| | 2 |
+| | 2 |
+| | 2 |
+| | |
+| | |
+| | |
++----+----+
+(27 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A7_B8_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[2]' RETURNING DOUBLE array)) AS u2(c2);
++----+-----+
+| c1 | c2 |
++----+-----+
+| 1 | 0.1 |
+| 1 | 0.1 |
+| 1 | 0.1 |
+| 1 | 0.2 |
+| 1 | 0.2 |
+| 1 | 0.2 |
+| 1 | |
+| 1 | |
+| 1 | |
+| 2 | 0.1 |
+| 2 | 0.1 |
+| 2 | 0.1 |
+| 2 | 0.2 |
+| 2 | 0.2 |
+| 2 | 0.2 |
+| 2 | |
+| 2 | |
+| 2 | |
+| | 0.1 |
+| | 0.1 |
+| | 0.1 |
+| | 0.2 |
+| | 0.2 |
+| | 0.2 |
+| | |
+| | |
+| | |
++----+-----+
+(27 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A7_B9_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[3]' RETURNING varchar array)) AS u2(c2);
++----+----+
+| c1 | c2 |
++----+----+
+| 1 | S1 |
+| 1 | S1 |
+| 1 | S1 |
+| 1 | S2 |
+| 1 | S2 |
+| 1 | S2 |
+| 1 | |
+| 1 | |
+| 1 | |
+| 2 | S1 |
+| 2 | S1 |
+| 2 | S1 |
+| 2 | S2 |
+| 2 | S2 |
+| 2 | S2 |
+| 2 | |
+| 2 | |
+| 2 | |
+| | S1 |
+| | S1 |
+| | S1 |
+| | S2 |
+| | S2 |
+| | S2 |
+| | |
+| | |
+| | |
++----+----+
+(27 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A7_B10_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[4]' RETURNING varchar array)) AS u2(c2);
++----+----+
+| c1 | c2 |
++----+----+
+| 1 | |
+| 1 | |
+| 1 | |
+| 1 | |
+| 1 | |
+| 1 | |
+| 1 | |
+| 1 | |
+| 1 | |
+| 2 | |
+| 2 | |
+| 2 | |
+| 2 | |
+| 2 | |
+| 2 | |
+| 2 | |
+| 2 | |
+| 2 | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
++----+----+
+(27 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A7_B11_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[5]' RETURNING varchar array)) AS u2(c2);
++----+----+
+| c1 | c2 |
++----+----+
++----+----+
+(0 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A7_B12_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[6]' RETURNING varchar array)) AS u2(c2);
++----+------+
+| c1 | c2 |
++----+------+
+| 1 | 0.1 |
+| 1 | 0.1 |
+| 1 | 0.1 |
+| 1 | 1 |
+| 1 | 1 |
+| 1 | 1 |
+| 1 | S1 |
+| 1 | S1 |
+| 1 | S1 |
+| 1 | true |
+| 1 | true |
+| 1 | true |
+| 1 | |
+| 1 | |
+| 1 | |
+| 2 | 0.1 |
+| 2 | 0.1 |
+| 2 | 0.1 |
+| 2 | 1 |
+| 2 | 1 |
+| 2 | 1 |
+| 2 | S1 |
+| 2 | S1 |
+| 2 | S1 |
+| 2 | true |
+| 2 | true |
+| 2 | true |
+| 2 | |
+| 2 | |
+| 2 | |
+| | 0.1 |
+| | 0.1 |
+| | 0.1 |
+| | 1 |
+| | 1 |
+| | 1 |
+| | S1 |
+| | S1 |
+| | S1 |
+| | true |
+| | true |
+| | true |
+| | |
+| | |
+| | |
++----+------+
+(45 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A8_B6_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[2]' RETURNING DOUBLE array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u2(c2);
++-----+-------+
+| c1 | c2 |
++-----+-------+
+| 0.1 | false |
+| 0.1 | false |
+| 0.1 | false |
+| 0.1 | true |
+| 0.1 | true |
+| 0.1 | true |
+| 0.1 | |
+| 0.1 | |
+| 0.1 | |
+| 0.2 | false |
+| 0.2 | false |
+| 0.2 | false |
+| 0.2 | true |
+| 0.2 | true |
+| 0.2 | true |
+| 0.2 | |
+| 0.2 | |
+| 0.2 | |
+| | false |
+| | false |
+| | false |
+| | true |
+| | true |
+| | true |
+| | |
+| | |
+| | |
++-----+-------+
+(27 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A8_B7_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[2]' RETURNING DOUBLE array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u2(c2);
++-----+----+
+| c1 | c2 |
++-----+----+
+| 0.1 | 1 |
+| 0.1 | 1 |
+| 0.1 | 1 |
+| 0.1 | 2 |
+| 0.1 | 2 |
+| 0.1 | 2 |
+| 0.1 | |
+| 0.1 | |
+| 0.1 | |
+| 0.2 | 1 |
+| 0.2 | 1 |
+| 0.2 | 1 |
+| 0.2 | 2 |
+| 0.2 | 2 |
+| 0.2 | 2 |
+| 0.2 | |
+| 0.2 | |
+| 0.2 | |
+| | 1 |
+| | 1 |
+| | 1 |
+| | 2 |
+| | 2 |
+| | 2 |
+| | |
+| | |
+| | |
++-----+----+
+(27 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A8_B8_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[2]' RETURNING DOUBLE array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[2]' RETURNING DOUBLE array)) AS u2(c2);
++-----+-----+
+| c1 | c2 |
++-----+-----+
+| 0.1 | 0.1 |
+| 0.1 | 0.1 |
+| 0.1 | 0.1 |
+| 0.1 | 0.2 |
+| 0.1 | 0.2 |
+| 0.1 | 0.2 |
+| 0.1 | |
+| 0.1 | |
+| 0.1 | |
+| 0.2 | 0.1 |
+| 0.2 | 0.1 |
+| 0.2 | 0.1 |
+| 0.2 | 0.2 |
+| 0.2 | 0.2 |
+| 0.2 | 0.2 |
+| 0.2 | |
+| 0.2 | |
+| 0.2 | |
+| | 0.1 |
+| | 0.1 |
+| | 0.1 |
+| | 0.2 |
+| | 0.2 |
+| | 0.2 |
+| | |
+| | |
+| | |
++-----+-----+
+(27 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A8_B9_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[2]' RETURNING DOUBLE array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[3]' RETURNING varchar array)) AS u2(c2);
++-----+----+
+| c1 | c2 |
++-----+----+
+| 0.1 | S1 |
+| 0.1 | S1 |
+| 0.1 | S1 |
+| 0.1 | S2 |
+| 0.1 | S2 |
+| 0.1 | S2 |
+| 0.1 | |
+| 0.1 | |
+| 0.1 | |
+| 0.2 | S1 |
+| 0.2 | S1 |
+| 0.2 | S1 |
+| 0.2 | S2 |
+| 0.2 | S2 |
+| 0.2 | S2 |
+| 0.2 | |
+| 0.2 | |
+| 0.2 | |
+| | S1 |
+| | S1 |
+| | S1 |
+| | S2 |
+| | S2 |
+| | S2 |
+| | |
+| | |
+| | |
++-----+----+
+(27 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A8_B10_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[2]' RETURNING DOUBLE array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[4]' RETURNING varchar array)) AS u2(c2);
++-----+----+
+| c1 | c2 |
++-----+----+
+| 0.1 | |
+| 0.1 | |
+| 0.1 | |
+| 0.1 | |
+| 0.1 | |
+| 0.1 | |
+| 0.1 | |
+| 0.1 | |
+| 0.1 | |
+| 0.2 | |
+| 0.2 | |
+| 0.2 | |
+| 0.2 | |
+| 0.2 | |
+| 0.2 | |
+| 0.2 | |
+| 0.2 | |
+| 0.2 | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
++-----+----+
+(27 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A8_B11_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[2]' RETURNING DOUBLE array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[5]' RETURNING varchar array)) AS u2(c2);
++----+----+
+| c1 | c2 |
++----+----+
++----+----+
+(0 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A8_B12_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[2]' RETURNING DOUBLE array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[6]' RETURNING varchar array)) AS u2(c2);
++-----+------+
+| c1 | c2 |
++-----+------+
+| 0.1 | 0.1 |
+| 0.1 | 0.1 |
+| 0.1 | 0.1 |
+| 0.1 | 1 |
+| 0.1 | 1 |
+| 0.1 | 1 |
+| 0.1 | S1 |
+| 0.1 | S1 |
+| 0.1 | S1 |
+| 0.1 | true |
+| 0.1 | true |
+| 0.1 | true |
+| 0.1 | |
+| 0.1 | |
+| 0.1 | |
+| 0.2 | 0.1 |
+| 0.2 | 0.1 |
+| 0.2 | 0.1 |
+| 0.2 | 1 |
+| 0.2 | 1 |
+| 0.2 | 1 |
+| 0.2 | S1 |
+| 0.2 | S1 |
+| 0.2 | S1 |
+| 0.2 | true |
+| 0.2 | true |
+| 0.2 | true |
+| 0.2 | |
+| 0.2 | |
+| 0.2 | |
+| | 0.1 |
+| | 0.1 |
+| | 0.1 |
+| | 1 |
+| | 1 |
+| | 1 |
+| | S1 |
+| | S1 |
+| | S1 |
+| | true |
+| | true |
+| | true |
+| | |
+| | |
+| | |
++-----+------+
+(45 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A9_B6_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[3]' RETURNING varchar array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u2(c2);
++----+-------+
+| c1 | c2 |
++----+-------+
+| S1 | false |
+| S1 | false |
+| S1 | false |
+| S1 | true |
+| S1 | true |
+| S1 | true |
+| S1 | |
+| S1 | |
+| S1 | |
+| S2 | false |
+| S2 | false |
+| S2 | false |
+| S2 | true |
+| S2 | true |
+| S2 | true |
+| S2 | |
+| S2 | |
+| S2 | |
+| | false |
+| | false |
+| | false |
+| | true |
+| | true |
+| | true |
+| | |
+| | |
+| | |
++----+-------+
+(27 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A9_B7_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[3]' RETURNING varchar array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u2(c2);
++----+----+
+| c1 | c2 |
++----+----+
+| S1 | 1 |
+| S1 | 1 |
+| S1 | 1 |
+| S1 | 2 |
+| S1 | 2 |
+| S1 | 2 |
+| S1 | |
+| S1 | |
+| S1 | |
+| S2 | 1 |
+| S2 | 1 |
+| S2 | 1 |
+| S2 | 2 |
+| S2 | 2 |
+| S2 | 2 |
+| S2 | |
+| S2 | |
+| S2 | |
+| | 1 |
+| | 1 |
+| | 1 |
+| | 2 |
+| | 2 |
+| | 2 |
+| | |
+| | |
+| | |
++----+----+
+(27 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A9_B8_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[3]' RETURNING varchar array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[2]' RETURNING DOUBLE array)) AS u2(c2);
++----+-----+
+| c1 | c2 |
++----+-----+
+| S1 | 0.1 |
+| S1 | 0.1 |
+| S1 | 0.1 |
+| S1 | 0.2 |
+| S1 | 0.2 |
+| S1 | 0.2 |
+| S1 | |
+| S1 | |
+| S1 | |
+| S2 | 0.1 |
+| S2 | 0.1 |
+| S2 | 0.1 |
+| S2 | 0.2 |
+| S2 | 0.2 |
+| S2 | 0.2 |
+| S2 | |
+| S2 | |
+| S2 | |
+| | 0.1 |
+| | 0.1 |
+| | 0.1 |
+| | 0.2 |
+| | 0.2 |
+| | 0.2 |
+| | |
+| | |
+| | |
++----+-----+
+(27 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A9_B9_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[3]' RETURNING varchar array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[3]' RETURNING varchar array)) AS u2(c2);
++----+----+
+| c1 | c2 |
++----+----+
+| S1 | S1 |
+| S1 | S1 |
+| S1 | S1 |
+| S1 | S2 |
+| S1 | S2 |
+| S1 | S2 |
+| S1 | |
+| S1 | |
+| S1 | |
+| S2 | S1 |
+| S2 | S1 |
+| S2 | S1 |
+| S2 | S2 |
+| S2 | S2 |
+| S2 | S2 |
+| S2 | |
+| S2 | |
+| S2 | |
+| | S1 |
+| | S1 |
+| | S1 |
+| | S2 |
+| | S2 |
+| | S2 |
+| | |
+| | |
+| | |
++----+----+
+(27 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A9_B10_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[3]' RETURNING varchar array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[4]' RETURNING varchar array)) AS u2(c2);
++----+----+
+| c1 | c2 |
++----+----+
+| S1 | |
+| S1 | |
+| S1 | |
+| S1 | |
+| S1 | |
+| S1 | |
+| S1 | |
+| S1 | |
+| S1 | |
+| S2 | |
+| S2 | |
+| S2 | |
+| S2 | |
+| S2 | |
+| S2 | |
+| S2 | |
+| S2 | |
+| S2 | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
++----+----+
+(27 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A9_B11_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[3]' RETURNING varchar array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[5]' RETURNING varchar array)) AS u2(c2);
++----+----+
+| c1 | c2 |
++----+----+
++----+----+
+(0 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A9_B12_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[3]' RETURNING varchar array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[6]' RETURNING varchar array)) AS u2(c2);
++----+------+
+| c1 | c2 |
++----+------+
+| S1 | 0.1 |
+| S1 | 0.1 |
+| S1 | 0.1 |
+| S1 | 1 |
+| S1 | 1 |
+| S1 | 1 |
+| S1 | S1 |
+| S1 | S1 |
+| S1 | S1 |
+| S1 | true |
+| S1 | true |
+| S1 | true |
+| S1 | |
+| S1 | |
+| S1 | |
+| S2 | 0.1 |
+| S2 | 0.1 |
+| S2 | 0.1 |
+| S2 | 1 |
+| S2 | 1 |
+| S2 | 1 |
+| S2 | S1 |
+| S2 | S1 |
+| S2 | S1 |
+| S2 | true |
+| S2 | true |
+| S2 | true |
+| S2 | |
+| S2 | |
+| S2 | |
+| | 0.1 |
+| | 0.1 |
+| | 0.1 |
+| | 1 |
+| | 1 |
+| | 1 |
+| | S1 |
+| | S1 |
+| | S1 |
+| | true |
+| | true |
+| | true |
+| | |
+| | |
+| | |
++----+------+
+(45 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A10_B6_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[4]' RETURNING varchar array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u2(c2);
++----+-------+
+| c1 | c2 |
++----+-------+
+| | false |
+| | false |
+| | false |
+| | false |
+| | false |
+| | false |
+| | false |
+| | false |
+| | false |
+| | true |
+| | true |
+| | true |
+| | true |
+| | true |
+| | true |
+| | true |
+| | true |
+| | true |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
++----+-------+
+(27 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A10_B7_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[4]' RETURNING varchar array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u2(c2);
++----+----+
+| c1 | c2 |
++----+----+
+| | 1 |
+| | 1 |
+| | 1 |
+| | 1 |
+| | 1 |
+| | 1 |
+| | 1 |
+| | 1 |
+| | 1 |
+| | 2 |
+| | 2 |
+| | 2 |
+| | 2 |
+| | 2 |
+| | 2 |
+| | 2 |
+| | 2 |
+| | 2 |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
++----+----+
+(27 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A10_B8_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[4]' RETURNING varchar array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[2]' RETURNING DOUBLE array)) AS u2(c2);
++----+-----+
+| c1 | c2 |
++----+-----+
+| | 0.1 |
+| | 0.1 |
+| | 0.1 |
+| | 0.1 |
+| | 0.1 |
+| | 0.1 |
+| | 0.1 |
+| | 0.1 |
+| | 0.1 |
+| | 0.2 |
+| | 0.2 |
+| | 0.2 |
+| | 0.2 |
+| | 0.2 |
+| | 0.2 |
+| | 0.2 |
+| | 0.2 |
+| | 0.2 |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
++----+-----+
+(27 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A10_B9_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[4]' RETURNING varchar array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[3]' RETURNING varchar array)) AS u2(c2);
++----+----+
+| c1 | c2 |
++----+----+
+| | S1 |
+| | S1 |
+| | S1 |
+| | S1 |
+| | S1 |
+| | S1 |
+| | S1 |
+| | S1 |
+| | S1 |
+| | S2 |
+| | S2 |
+| | S2 |
+| | S2 |
+| | S2 |
+| | S2 |
+| | S2 |
+| | S2 |
+| | S2 |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
++----+----+
+(27 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A10_B10_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[4]' RETURNING varchar array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[4]' RETURNING varchar array)) AS u2(c2);
++----+----+
+| c1 | c2 |
++----+----+
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
++----+----+
+(27 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A10_B11_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[4]' RETURNING varchar array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[5]' RETURNING varchar array)) AS u2(c2);
++----+----+
+| c1 | c2 |
++----+----+
++----+----+
+(0 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A10_B12_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[4]' RETURNING varchar array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[6]' RETURNING varchar array)) AS u2(c2);
++----+------+
+| c1 | c2 |
++----+------+
+| | 0.1 |
+| | 0.1 |
+| | 0.1 |
+| | 0.1 |
+| | 0.1 |
+| | 0.1 |
+| | 0.1 |
+| | 0.1 |
+| | 0.1 |
+| | 1 |
+| | 1 |
+| | 1 |
+| | 1 |
+| | 1 |
+| | 1 |
+| | 1 |
+| | 1 |
+| | 1 |
+| | S1 |
+| | S1 |
+| | S1 |
+| | S1 |
+| | S1 |
+| | S1 |
+| | S1 |
+| | S1 |
+| | S1 |
+| | true |
+| | true |
+| | true |
+| | true |
+| | true |
+| | true |
+| | true |
+| | true |
+| | true |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
++----+------+
+(45 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A11_B6_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[5]' RETURNING varchar array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u2(c2);
++----+----+
+| c1 | c2 |
++----+----+
++----+----+
+(0 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A11_B7_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[5]' RETURNING varchar array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u2(c2);
++----+----+
+| c1 | c2 |
++----+----+
++----+----+
+(0 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A11_B8_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[5]' RETURNING varchar array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[2]' RETURNING DOUBLE array)) AS u2(c2);
++----+----+
+| c1 | c2 |
++----+----+
++----+----+
+(0 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A11_B9_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[5]' RETURNING varchar array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[3]' RETURNING varchar array)) AS u2(c2);
++----+----+
+| c1 | c2 |
++----+----+
++----+----+
+(0 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A11_B10_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[5]' RETURNING varchar array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[4]' RETURNING varchar array)) AS u2(c2);
++----+----+
+| c1 | c2 |
++----+----+
++----+----+
+(0 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A11_B11_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[5]' RETURNING varchar array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[5]' RETURNING varchar array)) AS u2(c2);
++----+----+
+| c1 | c2 |
++----+----+
++----+----+
+(0 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A11_B12_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[5]' RETURNING varchar array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[6]' RETURNING varchar array)) AS u2(c2);
++----+----+
+| c1 | c2 |
++----+----+
++----+----+
+(0 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A12_B6_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[6]' RETURNING varchar array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u2(c2);
++------+-------+
+| c1 | c2 |
++------+-------+
+| 0.1 | false |
+| 0.1 | false |
+| 0.1 | false |
+| 0.1 | true |
+| 0.1 | true |
+| 0.1 | true |
+| 0.1 | |
+| 0.1 | |
+| 0.1 | |
+| 1 | false |
+| 1 | false |
+| 1 | false |
+| 1 | true |
+| 1 | true |
+| 1 | true |
+| 1 | |
+| 1 | |
+| 1 | |
+| S1 | false |
+| S1 | false |
+| S1 | false |
+| S1 | true |
+| S1 | true |
+| S1 | true |
+| S1 | |
+| S1 | |
+| S1 | |
+| true | false |
+| true | false |
+| true | false |
+| true | true |
+| true | true |
+| true | true |
+| true | |
+| true | |
+| true | |
+| | false |
+| | false |
+| | false |
+| | true |
+| | true |
+| | true |
+| | |
+| | |
+| | |
++------+-------+
+(45 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A12_B7_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[6]' RETURNING varchar array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u2(c2);
++------+----+
+| c1 | c2 |
++------+----+
+| 0.1 | 1 |
+| 0.1 | 1 |
+| 0.1 | 1 |
+| 0.1 | 2 |
+| 0.1 | 2 |
+| 0.1 | 2 |
+| 0.1 | |
+| 0.1 | |
+| 0.1 | |
+| 1 | 1 |
+| 1 | 1 |
+| 1 | 1 |
+| 1 | 2 |
+| 1 | 2 |
+| 1 | 2 |
+| 1 | |
+| 1 | |
+| 1 | |
+| S1 | 1 |
+| S1 | 1 |
+| S1 | 1 |
+| S1 | 2 |
+| S1 | 2 |
+| S1 | 2 |
+| S1 | |
+| S1 | |
+| S1 | |
+| true | 1 |
+| true | 1 |
+| true | 1 |
+| true | 2 |
+| true | 2 |
+| true | 2 |
+| true | |
+| true | |
+| true | |
+| | 1 |
+| | 1 |
+| | 1 |
+| | 2 |
+| | 2 |
+| | 2 |
+| | |
+| | |
+| | |
++------+----+
+(45 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A12_B8_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[6]' RETURNING varchar array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[2]' RETURNING DOUBLE array)) AS u2(c2);
++------+-----+
+| c1 | c2 |
++------+-----+
+| 0.1 | 0.1 |
+| 0.1 | 0.1 |
+| 0.1 | 0.1 |
+| 0.1 | 0.2 |
+| 0.1 | 0.2 |
+| 0.1 | 0.2 |
+| 0.1 | |
+| 0.1 | |
+| 0.1 | |
+| 1 | 0.1 |
+| 1 | 0.1 |
+| 1 | 0.1 |
+| 1 | 0.2 |
+| 1 | 0.2 |
+| 1 | 0.2 |
+| 1 | |
+| 1 | |
+| 1 | |
+| S1 | 0.1 |
+| S1 | 0.1 |
+| S1 | 0.1 |
+| S1 | 0.2 |
+| S1 | 0.2 |
+| S1 | 0.2 |
+| S1 | |
+| S1 | |
+| S1 | |
+| true | 0.1 |
+| true | 0.1 |
+| true | 0.1 |
+| true | 0.2 |
+| true | 0.2 |
+| true | 0.2 |
+| true | |
+| true | |
+| true | |
+| | 0.1 |
+| | 0.1 |
+| | 0.1 |
+| | 0.2 |
+| | 0.2 |
+| | 0.2 |
+| | |
+| | |
+| | |
++------+-----+
+(45 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A12_B9_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[6]' RETURNING varchar array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[3]' RETURNING varchar array)) AS u2(c2);
++------+----+
+| c1 | c2 |
++------+----+
+| 0.1 | S1 |
+| 0.1 | S1 |
+| 0.1 | S1 |
+| 0.1 | S2 |
+| 0.1 | S2 |
+| 0.1 | S2 |
+| 0.1 | |
+| 0.1 | |
+| 0.1 | |
+| 1 | S1 |
+| 1 | S1 |
+| 1 | S1 |
+| 1 | S2 |
+| 1 | S2 |
+| 1 | S2 |
+| 1 | |
+| 1 | |
+| 1 | |
+| S1 | S1 |
+| S1 | S1 |
+| S1 | S1 |
+| S1 | S2 |
+| S1 | S2 |
+| S1 | S2 |
+| S1 | |
+| S1 | |
+| S1 | |
+| true | S1 |
+| true | S1 |
+| true | S1 |
+| true | S2 |
+| true | S2 |
+| true | S2 |
+| true | |
+| true | |
+| true | |
+| | S1 |
+| | S1 |
+| | S1 |
+| | S2 |
+| | S2 |
+| | S2 |
+| | |
+| | |
+| | |
++------+----+
+(45 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A12_B10_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[6]' RETURNING varchar array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[4]' RETURNING varchar array)) AS u2(c2);
++------+----+
+| c1 | c2 |
++------+----+
+| 0.1 | |
+| 0.1 | |
+| 0.1 | |
+| 0.1 | |
+| 0.1 | |
+| 0.1 | |
+| 0.1 | |
+| 0.1 | |
+| 0.1 | |
+| 1 | |
+| 1 | |
+| 1 | |
+| 1 | |
+| 1 | |
+| 1 | |
+| 1 | |
+| 1 | |
+| 1 | |
+| S1 | |
+| S1 | |
+| S1 | |
+| S1 | |
+| S1 | |
+| S1 | |
+| S1 | |
+| S1 | |
+| S1 | |
+| true | |
+| true | |
+| true | |
+| true | |
+| true | |
+| true | |
+| true | |
+| true | |
+| true | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
++------+----+
+(45 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A12_B11_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[6]' RETURNING varchar array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[5]' RETURNING varchar array)) AS u2(c2);
++----+----+
+| c1 | c2 |
++----+----+
++----+----+
+(0 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_multi_unnests TEST_ID: A12_B12_D1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c1,
+ c2
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[6]' RETURNING varchar array)) AS u1(c1),
+ unnest(json_value(a_nested, '$[6]' RETURNING varchar array)) AS u2(c2);
++------+------+
+| c1 | c2 |
++------+------+
+| 0.1 | 0.1 |
+| 0.1 | 0.1 |
+| 0.1 | 0.1 |
+| 0.1 | 1 |
+| 0.1 | 1 |
+| 0.1 | 1 |
+| 0.1 | S1 |
+| 0.1 | S1 |
+| 0.1 | S1 |
+| 0.1 | true |
+| 0.1 | true |
+| 0.1 | true |
+| 0.1 | |
+| 0.1 | |
+| 0.1 | |
+| 1 | 0.1 |
+| 1 | 0.1 |
+| 1 | 0.1 |
+| 1 | 1 |
+| 1 | 1 |
+| 1 | 1 |
+| 1 | S1 |
+| 1 | S1 |
+| 1 | S1 |
+| 1 | true |
+| 1 | true |
+| 1 | true |
+| 1 | |
+| 1 | |
+| 1 | |
+| S1 | 0.1 |
+| S1 | 0.1 |
+| S1 | 0.1 |
+| S1 | 1 |
+| S1 | 1 |
+| S1 | 1 |
+| S1 | S1 |
+| S1 | S1 |
+| S1 | S1 |
+| S1 | true |
+| S1 | true |
+| S1 | true |
+| S1 | |
+| S1 | |
+| S1 | |
+| true | 0.1 |
+| true | 0.1 |
+| true | 0.1 |
+| true | 1 |
+| true | 1 |
+| true | 1 |
+| true | S1 |
+| true | S1 |
+| true | S1 |
+| true | true |
+| true | true |
+| true | true |
+| true | |
+| true | |
+| true | |
+| | 0.1 |
+| | 0.1 |
+| | 0.1 |
+| | 1 |
+| | 1 |
+| | 1 |
+| | S1 |
+| | S1 |
+| | S1 |
+| | true |
+| | true |
+| | true |
+| | |
+| | |
+| | |
++------+------+
+(75 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A1_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_bool) AS u(c)
+GROUP BY c;
++---+-----+
+| c | cnt |
++---+-----+
+| 0 | 3 |
+| 1 | 3 |
+| | 3 |
++---+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A1_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_bool) AS u(c)
+GROUP BY s_int,
+ c;
++-------+---+-----+
+| s_int | c | cnt |
++-------+---+-----+
+| 1 | 0 | 1 |
+| 1 | 1 | 1 |
+| 1 | | 1 |
+| 2 | 0 | 1 |
+| 2 | 1 | 1 |
+| 2 | | 1 |
+| | 0 | 1 |
+| | 1 | 1 |
+| | | 1 |
++-------+---+-----+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A1_B3 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_bool) AS u(c)
+GROUP BY s_int;
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
++-------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A1_B4 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_bool) AS u(c)
+GROUP BY GROUPING
+SETS (c);
++---+-----+
+| c | cnt |
++---+-----+
+| 0 | 3 |
+| 1 | 3 |
+| | 3 |
++---+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A1_B5 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_bool) AS u(c)
+GROUP BY GROUPING
+SETS (s_int,
+ c);
++-------+---+-----+
+| s_int | c | cnt |
++-------+---+-----+
+| 1 | | 3 |
+| 2 | | 3 |
+| | 0 | 3 |
+| | 1 | 3 |
+| | | 3 |
+| | | 3 |
++-------+---+-----+
+(6 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A1_B6 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_bool) AS u(c)
+GROUP BY GROUPING
+SETS (s_int);
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
++-------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A1_B7 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_bool) AS u(c)
+GROUP BY rollup(c);
++---+-----+
+| c | cnt |
++---+-----+
+| 0 | 3 |
+| 1 | 3 |
+| | 3 |
+| | 9 |
++---+-----+
+(4 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A1_B8 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_bool) AS u(c)
+GROUP BY rollup(s_int, c);
++-------+---+-----+
+| s_int | c | cnt |
++-------+---+-----+
+| 1 | 0 | 1 |
+| 1 | 1 | 1 |
+| 1 | | 1 |
+| 1 | | 3 |
+| 2 | 0 | 1 |
+| 2 | 1 | 1 |
+| 2 | | 1 |
+| 2 | | 3 |
+| | 0 | 1 |
+| | 1 | 1 |
+| | | 1 |
+| | | 3 |
+| | | 9 |
++-------+---+-----+
+(13 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A1_B9 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_bool) AS u(c)
+GROUP BY rollup(s_int);
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
+| | 9 |
++-------+-----+
+(4 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A1_B10 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_bool) AS u(c)
+GROUP BY cube(c);
++---+-----+
+| c | cnt |
++---+-----+
+| 0 | 3 |
+| 1 | 3 |
+| | 3 |
+| | 9 |
++---+-----+
+(4 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A1_B11 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_bool) AS u(c)
+GROUP BY cube(s_int, c);
++-------+---+-----+
+| s_int | c | cnt |
++-------+---+-----+
+| 1 | 0 | 1 |
+| 1 | 1 | 1 |
+| 1 | | 1 |
+| 1 | | 3 |
+| 2 | 0 | 1 |
+| 2 | 1 | 1 |
+| 2 | | 1 |
+| 2 | | 3 |
+| | 0 | 1 |
+| | 0 | 3 |
+| | 1 | 1 |
+| | 1 | 3 |
+| | | 1 |
+| | | 3 |
+| | | 3 |
+| | | 9 |
++-------+---+-----+
+(16 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A1_B12 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_bool) AS u(c)
+GROUP BY cube(s_int);
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
+| | 9 |
++-------+-----+
+(4 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A1_B13 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_bool) AS u(c)
+GROUP BY c
+HAVING c = 0;
++---+-----+
+| c | cnt |
++---+-----+
+| 0 | 3 |
++---+-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A1_B14 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_bool) AS u(c)
+GROUP BY s_int
+HAVING s_int=1;
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
++-------+-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A2_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u(c)
+GROUP BY c;
++-------+-----+
+| c | cnt |
++-------+-----+
+| false | 3 |
+| true | 3 |
+| | 3 |
++-------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A2_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u(c)
+GROUP BY s_int,
+ c;
++-------+-------+-----+
+| s_int | c | cnt |
++-------+-------+-----+
+| 1 | false | 1 |
+| 1 | true | 1 |
+| 1 | | 1 |
+| 2 | false | 1 |
+| 2 | true | 1 |
+| 2 | | 1 |
+| | false | 1 |
+| | true | 1 |
+| | | 1 |
++-------+-------+-----+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A2_B3 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u(c)
+GROUP BY s_int;
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
++-------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A2_B4 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u(c)
+GROUP BY GROUPING
+SETS (c);
++-------+-----+
+| c | cnt |
++-------+-----+
+| false | 3 |
+| true | 3 |
+| | 3 |
++-------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A2_B5 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u(c)
+GROUP BY GROUPING
+SETS (s_int,
+ c);
++-------+-------+-----+
+| s_int | c | cnt |
++-------+-------+-----+
+| 1 | | 3 |
+| 2 | | 3 |
+| | false | 3 |
+| | true | 3 |
+| | | 3 |
+| | | 3 |
++-------+-------+-----+
+(6 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A2_B6 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u(c)
+GROUP BY GROUPING
+SETS (s_int);
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
++-------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A2_B7 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u(c)
+GROUP BY rollup(c);
++-------+-----+
+| c | cnt |
++-------+-----+
+| false | 3 |
+| true | 3 |
+| | 3 |
+| | 9 |
++-------+-----+
+(4 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A2_B8 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u(c)
+GROUP BY rollup(s_int, c);
++-------+-------+-----+
+| s_int | c | cnt |
++-------+-------+-----+
+| 1 | false | 1 |
+| 1 | true | 1 |
+| 1 | | 1 |
+| 1 | | 3 |
+| 2 | false | 1 |
+| 2 | true | 1 |
+| 2 | | 1 |
+| 2 | | 3 |
+| | false | 1 |
+| | true | 1 |
+| | | 1 |
+| | | 3 |
+| | | 9 |
++-------+-------+-----+
+(13 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A2_B9 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u(c)
+GROUP BY rollup(s_int);
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
+| | 9 |
++-------+-----+
+(4 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A2_B10 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u(c)
+GROUP BY cube(c);
++-------+-----+
+| c | cnt |
++-------+-----+
+| false | 3 |
+| true | 3 |
+| | 3 |
+| | 9 |
++-------+-----+
+(4 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A2_B11 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u(c)
+GROUP BY cube(s_int, c);
++-------+-------+-----+
+| s_int | c | cnt |
++-------+-------+-----+
+| 1 | false | 1 |
+| 1 | true | 1 |
+| 1 | | 1 |
+| 1 | | 3 |
+| 2 | false | 1 |
+| 2 | true | 1 |
+| 2 | | 1 |
+| 2 | | 3 |
+| | false | 1 |
+| | false | 3 |
+| | true | 1 |
+| | true | 3 |
+| | | 1 |
+| | | 3 |
+| | | 3 |
+| | | 9 |
++-------+-------+-----+
+(16 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A2_B12 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u(c)
+GROUP BY cube(s_int);
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
+| | 9 |
++-------+-----+
+(4 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A2_B13 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u(c)
+GROUP BY c
+HAVING c IS FALSE;
++-------+-----+
+| c | cnt |
++-------+-----+
+| false | 3 |
++-------+-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A2_B14 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u(c)
+GROUP BY s_int
+HAVING s_int=1;
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
++-------+-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A3_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_int) AS u(c)
+GROUP BY c;
++---+-----+
+| c | cnt |
++---+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
++---+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A3_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_int) AS u(c)
+GROUP BY s_int,
+ c;
++-------+---+-----+
+| s_int | c | cnt |
++-------+---+-----+
+| 1 | 1 | 1 |
+| 1 | 2 | 1 |
+| 1 | | 1 |
+| 2 | 1 | 1 |
+| 2 | 2 | 1 |
+| 2 | | 1 |
+| | 1 | 1 |
+| | 2 | 1 |
+| | | 1 |
++-------+---+-----+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A3_B3 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_int) AS u(c)
+GROUP BY s_int;
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
++-------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A3_B4 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_int) AS u(c)
+GROUP BY GROUPING
+SETS (c);
++---+-----+
+| c | cnt |
++---+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
++---+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A3_B5 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_int) AS u(c)
+GROUP BY GROUPING
+SETS (s_int,
+ c);
++-------+---+-----+
+| s_int | c | cnt |
++-------+---+-----+
+| 1 | | 3 |
+| 2 | | 3 |
+| | 1 | 3 |
+| | 2 | 3 |
+| | | 3 |
+| | | 3 |
++-------+---+-----+
+(6 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A3_B6 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_int) AS u(c)
+GROUP BY GROUPING
+SETS (s_int);
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
++-------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A3_B7 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_int) AS u(c)
+GROUP BY rollup(c);
++---+-----+
+| c | cnt |
++---+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
+| | 9 |
++---+-----+
+(4 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A3_B8 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_int) AS u(c)
+GROUP BY rollup(s_int, c);
++-------+---+-----+
+| s_int | c | cnt |
++-------+---+-----+
+| 1 | 1 | 1 |
+| 1 | 2 | 1 |
+| 1 | | 1 |
+| 1 | | 3 |
+| 2 | 1 | 1 |
+| 2 | 2 | 1 |
+| 2 | | 1 |
+| 2 | | 3 |
+| | 1 | 1 |
+| | 2 | 1 |
+| | | 1 |
+| | | 3 |
+| | | 9 |
++-------+---+-----+
+(13 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A3_B9 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_int) AS u(c)
+GROUP BY rollup(s_int);
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
+| | 9 |
++-------+-----+
+(4 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A3_B10 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_int) AS u(c)
+GROUP BY cube(c);
++---+-----+
+| c | cnt |
++---+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
+| | 9 |
++---+-----+
+(4 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A3_B11 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_int) AS u(c)
+GROUP BY cube(s_int, c);
++-------+---+-----+
+| s_int | c | cnt |
++-------+---+-----+
+| 1 | 1 | 1 |
+| 1 | 2 | 1 |
+| 1 | | 1 |
+| 1 | | 3 |
+| 2 | 1 | 1 |
+| 2 | 2 | 1 |
+| 2 | | 1 |
+| 2 | | 3 |
+| | 1 | 1 |
+| | 1 | 3 |
+| | 2 | 1 |
+| | 2 | 3 |
+| | | 1 |
+| | | 3 |
+| | | 3 |
+| | | 9 |
++-------+---+-----+
+(16 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A3_B12 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_int) AS u(c)
+GROUP BY cube(s_int);
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
+| | 9 |
++-------+-----+
+(4 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A3_B13 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_int) AS u(c)
+GROUP BY c
+HAVING c = 1;
++---+-----+
+| c | cnt |
++---+-----+
+| 1 | 3 |
++---+-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A3_B14 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_int) AS u(c)
+GROUP BY s_int
+HAVING s_int=1;
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
++-------+-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A4_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u(c)
+GROUP BY c;
++---+-----+
+| c | cnt |
++---+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
++---+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A4_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u(c)
+GROUP BY s_int,
+ c;
++-------+---+-----+
+| s_int | c | cnt |
++-------+---+-----+
+| 1 | 1 | 1 |
+| 1 | 2 | 1 |
+| 1 | | 1 |
+| 2 | 1 | 1 |
+| 2 | 2 | 1 |
+| 2 | | 1 |
+| | 1 | 1 |
+| | 2 | 1 |
+| | | 1 |
++-------+---+-----+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A4_B3 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u(c)
+GROUP BY s_int;
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
++-------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A4_B4 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u(c)
+GROUP BY GROUPING
+SETS (c);
++---+-----+
+| c | cnt |
++---+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
++---+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A4_B5 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u(c)
+GROUP BY GROUPING
+SETS (s_int,
+ c);
++-------+---+-----+
+| s_int | c | cnt |
++-------+---+-----+
+| 1 | | 3 |
+| 2 | | 3 |
+| | 1 | 3 |
+| | 2 | 3 |
+| | | 3 |
+| | | 3 |
++-------+---+-----+
+(6 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A4_B6 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u(c)
+GROUP BY GROUPING
+SETS (s_int);
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
++-------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A4_B7 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u(c)
+GROUP BY rollup(c);
++---+-----+
+| c | cnt |
++---+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
+| | 9 |
++---+-----+
+(4 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A4_B8 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u(c)
+GROUP BY rollup(s_int, c);
++-------+---+-----+
+| s_int | c | cnt |
++-------+---+-----+
+| 1 | 1 | 1 |
+| 1 | 2 | 1 |
+| 1 | | 1 |
+| 1 | | 3 |
+| 2 | 1 | 1 |
+| 2 | 2 | 1 |
+| 2 | | 1 |
+| 2 | | 3 |
+| | 1 | 1 |
+| | 2 | 1 |
+| | | 1 |
+| | | 3 |
+| | | 9 |
++-------+---+-----+
+(13 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A4_B9 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u(c)
+GROUP BY rollup(s_int);
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
+| | 9 |
++-------+-----+
+(4 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A4_B10 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u(c)
+GROUP BY cube(c);
++---+-----+
+| c | cnt |
++---+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
+| | 9 |
++---+-----+
+(4 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A4_B11 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u(c)
+GROUP BY cube(s_int, c);
++-------+---+-----+
+| s_int | c | cnt |
++-------+---+-----+
+| 1 | 1 | 1 |
+| 1 | 2 | 1 |
+| 1 | | 1 |
+| 1 | | 3 |
+| 2 | 1 | 1 |
+| 2 | 2 | 1 |
+| 2 | | 1 |
+| 2 | | 3 |
+| | 1 | 1 |
+| | 1 | 3 |
+| | 2 | 1 |
+| | 2 | 3 |
+| | | 1 |
+| | | 3 |
+| | | 3 |
+| | | 9 |
++-------+---+-----+
+(16 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A4_B12 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u(c)
+GROUP BY cube(s_int);
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
+| | 9 |
++-------+-----+
+(4 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A4_B13 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u(c)
+GROUP BY c
+HAVING c = 1;
++---+-----+
+| c | cnt |
++---+-----+
+| 1 | 3 |
++---+-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A4_B14 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u(c)
+GROUP BY s_int
+HAVING s_int=1;
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
++-------+-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A5_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_float) AS u(c)
+GROUP BY c;
++-----+-----+
+| c | cnt |
++-----+-----+
+| 0.1 | 3 |
+| 0.2 | 3 |
+| | 3 |
++-----+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A5_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_float) AS u(c)
+GROUP BY s_int,
+ c;
++-------+-----+-----+
+| s_int | c | cnt |
++-------+-----+-----+
+| 1 | 0.1 | 1 |
+| 1 | 0.2 | 1 |
+| 1 | | 1 |
+| 2 | 0.1 | 1 |
+| 2 | 0.2 | 1 |
+| 2 | | 1 |
+| | 0.1 | 1 |
+| | 0.2 | 1 |
+| | | 1 |
++-------+-----+-----+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A5_B3 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_float) AS u(c)
+GROUP BY s_int;
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
++-------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A5_B4 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_float) AS u(c)
+GROUP BY GROUPING
+SETS (c);
++-----+-----+
+| c | cnt |
++-----+-----+
+| 0.1 | 3 |
+| 0.2 | 3 |
+| | 3 |
++-----+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A5_B5 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_float) AS u(c)
+GROUP BY GROUPING
+SETS (s_int,
+ c);
++-------+-----+-----+
+| s_int | c | cnt |
++-------+-----+-----+
+| 1 | | 3 |
+| 2 | | 3 |
+| | 0.1 | 3 |
+| | 0.2 | 3 |
+| | | 3 |
+| | | 3 |
++-------+-----+-----+
+(6 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A5_B6 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_float) AS u(c)
+GROUP BY GROUPING
+SETS (s_int);
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
++-------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A5_B7 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_float) AS u(c)
+GROUP BY rollup(c);
++-----+-----+
+| c | cnt |
++-----+-----+
+| 0.1 | 3 |
+| 0.2 | 3 |
+| | 3 |
+| | 9 |
++-----+-----+
+(4 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A5_B8 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_float) AS u(c)
+GROUP BY rollup(s_int, c);
++-------+-----+-----+
+| s_int | c | cnt |
++-------+-----+-----+
+| 1 | 0.1 | 1 |
+| 1 | 0.2 | 1 |
+| 1 | | 1 |
+| 1 | | 3 |
+| 2 | 0.1 | 1 |
+| 2 | 0.2 | 1 |
+| 2 | | 1 |
+| 2 | | 3 |
+| | 0.1 | 1 |
+| | 0.2 | 1 |
+| | | 1 |
+| | | 3 |
+| | | 9 |
++-------+-----+-----+
+(13 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A5_B9 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_float) AS u(c)
+GROUP BY rollup(s_int);
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
+| | 9 |
++-------+-----+
+(4 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A5_B10 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_float) AS u(c)
+GROUP BY cube(c);
++-----+-----+
+| c | cnt |
++-----+-----+
+| 0.1 | 3 |
+| 0.2 | 3 |
+| | 3 |
+| | 9 |
++-----+-----+
+(4 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A5_B11 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_float) AS u(c)
+GROUP BY cube(s_int, c);
++-------+-----+-----+
+| s_int | c | cnt |
++-------+-----+-----+
+| 1 | 0.1 | 1 |
+| 1 | 0.2 | 1 |
+| 1 | | 1 |
+| 1 | | 3 |
+| 2 | 0.1 | 1 |
+| 2 | 0.2 | 1 |
+| 2 | | 1 |
+| 2 | | 3 |
+| | 0.1 | 1 |
+| | 0.1 | 3 |
+| | 0.2 | 1 |
+| | 0.2 | 3 |
+| | | 1 |
+| | | 3 |
+| | | 3 |
+| | | 9 |
++-------+-----+-----+
+(16 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A5_B12 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_float) AS u(c)
+GROUP BY cube(s_int);
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
+| | 9 |
++-------+-----+
+(4 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A5_B13 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_float) AS u(c)
+GROUP BY c
+HAVING c = 0.1;
++-----+-----+
+| c | cnt |
++-----+-----+
+| 0.1 | 3 |
++-----+-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A5_B14 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_float) AS u(c)
+GROUP BY s_int
+HAVING s_int=1;
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
++-------+-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A6_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[2]' RETURNING DOUBLE array)) AS u(c)
+GROUP BY c;
++-----+-----+
+| c | cnt |
++-----+-----+
+| 0.1 | 3 |
+| 0.2 | 3 |
+| | 3 |
++-----+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A6_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[2]' RETURNING DOUBLE array)) AS u(c)
+GROUP BY s_int,
+ c;
++-------+-----+-----+
+| s_int | c | cnt |
++-------+-----+-----+
+| 1 | 0.1 | 1 |
+| 1 | 0.2 | 1 |
+| 1 | | 1 |
+| 2 | 0.1 | 1 |
+| 2 | 0.2 | 1 |
+| 2 | | 1 |
+| | 0.1 | 1 |
+| | 0.2 | 1 |
+| | | 1 |
++-------+-----+-----+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A6_B3 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[2]' RETURNING DOUBLE array)) AS u(c)
+GROUP BY s_int;
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
++-------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A6_B4 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[2]' RETURNING DOUBLE array)) AS u(c)
+GROUP BY GROUPING
+SETS (c);
++-----+-----+
+| c | cnt |
++-----+-----+
+| 0.1 | 3 |
+| 0.2 | 3 |
+| | 3 |
++-----+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A6_B5 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[2]' RETURNING DOUBLE array)) AS u(c)
+GROUP BY GROUPING
+SETS (s_int,
+ c);
++-------+-----+-----+
+| s_int | c | cnt |
++-------+-----+-----+
+| 1 | | 3 |
+| 2 | | 3 |
+| | 0.1 | 3 |
+| | 0.2 | 3 |
+| | | 3 |
+| | | 3 |
++-------+-----+-----+
+(6 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A6_B6 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[2]' RETURNING DOUBLE array)) AS u(c)
+GROUP BY GROUPING
+SETS (s_int);
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
++-------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A6_B7 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[2]' RETURNING DOUBLE array)) AS u(c)
+GROUP BY rollup(c);
++-----+-----+
+| c | cnt |
++-----+-----+
+| 0.1 | 3 |
+| 0.2 | 3 |
+| | 3 |
+| | 9 |
++-----+-----+
+(4 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A6_B8 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[2]' RETURNING DOUBLE array)) AS u(c)
+GROUP BY rollup(s_int, c);
++-------+-----+-----+
+| s_int | c | cnt |
++-------+-----+-----+
+| 1 | 0.1 | 1 |
+| 1 | 0.2 | 1 |
+| 1 | | 1 |
+| 1 | | 3 |
+| 2 | 0.1 | 1 |
+| 2 | 0.2 | 1 |
+| 2 | | 1 |
+| 2 | | 3 |
+| | 0.1 | 1 |
+| | 0.2 | 1 |
+| | | 1 |
+| | | 3 |
+| | | 9 |
++-------+-----+-----+
+(13 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A6_B9 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[2]' RETURNING DOUBLE array)) AS u(c)
+GROUP BY rollup(s_int);
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
+| | 9 |
++-------+-----+
+(4 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A6_B10 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[2]' RETURNING DOUBLE array)) AS u(c)
+GROUP BY cube(c);
++-----+-----+
+| c | cnt |
++-----+-----+
+| 0.1 | 3 |
+| 0.2 | 3 |
+| | 3 |
+| | 9 |
++-----+-----+
+(4 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A6_B11 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[2]' RETURNING DOUBLE array)) AS u(c)
+GROUP BY cube(s_int, c);
++-------+-----+-----+
+| s_int | c | cnt |
++-------+-----+-----+
+| 1 | 0.1 | 1 |
+| 1 | 0.2 | 1 |
+| 1 | | 1 |
+| 1 | | 3 |
+| 2 | 0.1 | 1 |
+| 2 | 0.2 | 1 |
+| 2 | | 1 |
+| 2 | | 3 |
+| | 0.1 | 1 |
+| | 0.1 | 3 |
+| | 0.2 | 1 |
+| | 0.2 | 3 |
+| | | 1 |
+| | | 3 |
+| | | 3 |
+| | | 9 |
++-------+-----+-----+
+(16 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A6_B12 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[2]' RETURNING DOUBLE array)) AS u(c)
+GROUP BY cube(s_int);
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
+| | 9 |
++-------+-----+
+(4 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A6_B13 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[2]' RETURNING DOUBLE array)) AS u(c)
+GROUP BY c
+HAVING c = 0.1;
++-----+-----+
+| c | cnt |
++-----+-----+
+| 0.1 | 3 |
++-----+-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A6_B14 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[2]' RETURNING DOUBLE array)) AS u(c)
+GROUP BY s_int
+HAVING s_int=1;
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
++-------+-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A7_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_str) AS u(c)
+GROUP BY c;
++----+-----+
+| c | cnt |
++----+-----+
+| S1 | 3 |
+| S2 | 3 |
+| | 3 |
++----+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A7_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_str) AS u(c)
+GROUP BY s_int,
+ c;
++-------+----+-----+
+| s_int | c | cnt |
++-------+----+-----+
+| 1 | S1 | 1 |
+| 1 | S2 | 1 |
+| 1 | | 1 |
+| 2 | S1 | 1 |
+| 2 | S2 | 1 |
+| 2 | | 1 |
+| | S1 | 1 |
+| | S2 | 1 |
+| | | 1 |
++-------+----+-----+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A7_B3 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_str) AS u(c)
+GROUP BY s_int;
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
++-------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A7_B4 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_str) AS u(c)
+GROUP BY GROUPING
+SETS (c);
++----+-----+
+| c | cnt |
++----+-----+
+| S1 | 3 |
+| S2 | 3 |
+| | 3 |
++----+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A7_B5 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_str) AS u(c)
+GROUP BY GROUPING
+SETS (s_int,
+ c);
++-------+----+-----+
+| s_int | c | cnt |
++-------+----+-----+
+| 1 | | 3 |
+| 2 | | 3 |
+| | S1 | 3 |
+| | S2 | 3 |
+| | | 3 |
+| | | 3 |
++-------+----+-----+
+(6 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A7_B6 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_str) AS u(c)
+GROUP BY GROUPING
+SETS (s_int);
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
++-------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A7_B7 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_str) AS u(c)
+GROUP BY rollup(c);
++----+-----+
+| c | cnt |
++----+-----+
+| S1 | 3 |
+| S2 | 3 |
+| | 3 |
+| | 9 |
++----+-----+
+(4 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A7_B8 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_str) AS u(c)
+GROUP BY rollup(s_int, c);
++-------+----+-----+
+| s_int | c | cnt |
++-------+----+-----+
+| 1 | S1 | 1 |
+| 1 | S2 | 1 |
+| 1 | | 1 |
+| 1 | | 3 |
+| 2 | S1 | 1 |
+| 2 | S2 | 1 |
+| 2 | | 1 |
+| 2 | | 3 |
+| | S1 | 1 |
+| | S2 | 1 |
+| | | 1 |
+| | | 3 |
+| | | 9 |
++-------+----+-----+
+(13 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A7_B9 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_str) AS u(c)
+GROUP BY rollup(s_int);
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
+| | 9 |
++-------+-----+
+(4 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A7_B10 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_str) AS u(c)
+GROUP BY cube(c);
++----+-----+
+| c | cnt |
++----+-----+
+| S1 | 3 |
+| S2 | 3 |
+| | 3 |
+| | 9 |
++----+-----+
+(4 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A7_B11 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_str) AS u(c)
+GROUP BY cube(s_int, c);
++-------+----+-----+
+| s_int | c | cnt |
++-------+----+-----+
+| 1 | S1 | 1 |
+| 1 | S2 | 1 |
+| 1 | | 1 |
+| 1 | | 3 |
+| 2 | S1 | 1 |
+| 2 | S2 | 1 |
+| 2 | | 1 |
+| 2 | | 3 |
+| | S1 | 1 |
+| | S1 | 3 |
+| | S2 | 1 |
+| | S2 | 3 |
+| | | 1 |
+| | | 3 |
+| | | 3 |
+| | | 9 |
++-------+----+-----+
+(16 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A7_B12 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_str) AS u(c)
+GROUP BY cube(s_int);
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
+| | 9 |
++-------+-----+
+(4 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A7_B13 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_str) AS u(c)
+GROUP BY c
+HAVING c = 'S1';
++----+-----+
+| c | cnt |
++----+-----+
+| S1 | 3 |
++----+-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A7_B14 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_str) AS u(c)
+GROUP BY s_int
+HAVING s_int=1;
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
++-------+-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A8_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[3]' RETURNING varchar array)) AS u(c)
+GROUP BY c;
++----+-----+
+| c | cnt |
++----+-----+
+| S1 | 3 |
+| S2 | 3 |
+| | 3 |
++----+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A8_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[3]' RETURNING varchar array)) AS u(c)
+GROUP BY s_int,
+ c;
++-------+----+-----+
+| s_int | c | cnt |
++-------+----+-----+
+| 1 | S1 | 1 |
+| 1 | S2 | 1 |
+| 1 | | 1 |
+| 2 | S1 | 1 |
+| 2 | S2 | 1 |
+| 2 | | 1 |
+| | S1 | 1 |
+| | S2 | 1 |
+| | | 1 |
++-------+----+-----+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A8_B3 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[3]' RETURNING varchar array)) AS u(c)
+GROUP BY s_int;
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
++-------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A8_B4 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[3]' RETURNING varchar array)) AS u(c)
+GROUP BY GROUPING
+SETS (c);
++----+-----+
+| c | cnt |
++----+-----+
+| S1 | 3 |
+| S2 | 3 |
+| | 3 |
++----+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A8_B5 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[3]' RETURNING varchar array)) AS u(c)
+GROUP BY GROUPING
+SETS (s_int,
+ c);
++-------+----+-----+
+| s_int | c | cnt |
++-------+----+-----+
+| 1 | | 3 |
+| 2 | | 3 |
+| | S1 | 3 |
+| | S2 | 3 |
+| | | 3 |
+| | | 3 |
++-------+----+-----+
+(6 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A8_B6 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[3]' RETURNING varchar array)) AS u(c)
+GROUP BY GROUPING
+SETS (s_int);
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
++-------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A8_B7 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[3]' RETURNING varchar array)) AS u(c)
+GROUP BY rollup(c);
++----+-----+
+| c | cnt |
++----+-----+
+| S1 | 3 |
+| S2 | 3 |
+| | 3 |
+| | 9 |
++----+-----+
+(4 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A8_B8 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[3]' RETURNING varchar array)) AS u(c)
+GROUP BY rollup(s_int, c);
++-------+----+-----+
+| s_int | c | cnt |
++-------+----+-----+
+| 1 | S1 | 1 |
+| 1 | S2 | 1 |
+| 1 | | 1 |
+| 1 | | 3 |
+| 2 | S1 | 1 |
+| 2 | S2 | 1 |
+| 2 | | 1 |
+| 2 | | 3 |
+| | S1 | 1 |
+| | S2 | 1 |
+| | | 1 |
+| | | 3 |
+| | | 9 |
++-------+----+-----+
+(13 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A8_B9 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[3]' RETURNING varchar array)) AS u(c)
+GROUP BY rollup(s_int);
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
+| | 9 |
++-------+-----+
+(4 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A8_B10 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[3]' RETURNING varchar array)) AS u(c)
+GROUP BY cube(c);
++----+-----+
+| c | cnt |
++----+-----+
+| S1 | 3 |
+| S2 | 3 |
+| | 3 |
+| | 9 |
++----+-----+
+(4 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A8_B11 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[3]' RETURNING varchar array)) AS u(c)
+GROUP BY cube(s_int, c);
++-------+----+-----+
+| s_int | c | cnt |
++-------+----+-----+
+| 1 | S1 | 1 |
+| 1 | S2 | 1 |
+| 1 | | 1 |
+| 1 | | 3 |
+| 2 | S1 | 1 |
+| 2 | S2 | 1 |
+| 2 | | 1 |
+| 2 | | 3 |
+| | S1 | 1 |
+| | S1 | 3 |
+| | S2 | 1 |
+| | S2 | 3 |
+| | | 1 |
+| | | 3 |
+| | | 3 |
+| | | 9 |
++-------+----+-----+
+(16 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A8_B12 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[3]' RETURNING varchar array)) AS u(c)
+GROUP BY cube(s_int);
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
+| | 9 |
++-------+-----+
+(4 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A8_B13 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[3]' RETURNING varchar array)) AS u(c)
+GROUP BY c
+HAVING c = 'S1';
++----+-----+
+| c | cnt |
++----+-----+
+| S1 | 3 |
++----+-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A8_B14 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[3]' RETURNING varchar array)) AS u(c)
+GROUP BY s_int
+HAVING s_int=1;
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
++-------+-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A9_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_mixed) AS u(c)
+GROUP BY c;
++------+-----+
+| c | cnt |
++------+-----+
+| 0.1 | 3 |
+| 1 | 3 |
+| S1 | 3 |
+| true | 3 |
+| | 3 |
++------+-----+
+(5 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A9_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_mixed) AS u(c)
+GROUP BY s_int,
+ c;
++-------+------+-----+
+| s_int | c | cnt |
++-------+------+-----+
+| 1 | 0.1 | 1 |
+| 1 | 1 | 1 |
+| 1 | S1 | 1 |
+| 1 | true | 1 |
+| 1 | | 1 |
+| 2 | 0.1 | 1 |
+| 2 | 1 | 1 |
+| 2 | S1 | 1 |
+| 2 | true | 1 |
+| 2 | | 1 |
+| | 0.1 | 1 |
+| | 1 | 1 |
+| | S1 | 1 |
+| | true | 1 |
+| | | 1 |
++-------+------+-----+
+(15 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A9_B3 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_mixed) AS u(c)
+GROUP BY s_int;
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 5 |
+| 2 | 5 |
+| | 5 |
++-------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A9_B4 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_mixed) AS u(c)
+GROUP BY GROUPING
+SETS (c);
++------+-----+
+| c | cnt |
++------+-----+
+| 0.1 | 3 |
+| 1 | 3 |
+| S1 | 3 |
+| true | 3 |
+| | 3 |
++------+-----+
+(5 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A9_B5 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_mixed) AS u(c)
+GROUP BY GROUPING
+SETS (s_int,
+ c);
++-------+------+-----+
+| s_int | c | cnt |
++-------+------+-----+
+| 1 | | 5 |
+| 2 | | 5 |
+| | 0.1 | 3 |
+| | 1 | 3 |
+| | S1 | 3 |
+| | true | 3 |
+| | | 3 |
+| | | 5 |
++-------+------+-----+
+(8 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A9_B6 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_mixed) AS u(c)
+GROUP BY GROUPING
+SETS (s_int);
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 5 |
+| 2 | 5 |
+| | 5 |
++-------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A9_B7 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_mixed) AS u(c)
+GROUP BY rollup(c);
++------+-----+
+| c | cnt |
++------+-----+
+| 0.1 | 3 |
+| 1 | 3 |
+| S1 | 3 |
+| true | 3 |
+| | 15 |
+| | 3 |
++------+-----+
+(6 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A9_B8 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_mixed) AS u(c)
+GROUP BY rollup(s_int, c);
++-------+------+-----+
+| s_int | c | cnt |
++-------+------+-----+
+| 1 | 0.1 | 1 |
+| 1 | 1 | 1 |
+| 1 | S1 | 1 |
+| 1 | true | 1 |
+| 1 | | 1 |
+| 1 | | 5 |
+| 2 | 0.1 | 1 |
+| 2 | 1 | 1 |
+| 2 | S1 | 1 |
+| 2 | true | 1 |
+| 2 | | 1 |
+| 2 | | 5 |
+| | 0.1 | 1 |
+| | 1 | 1 |
+| | S1 | 1 |
+| | true | 1 |
+| | | 1 |
+| | | 15 |
+| | | 5 |
++-------+------+-----+
+(19 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A9_B9 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_mixed) AS u(c)
+GROUP BY rollup(s_int);
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 5 |
+| 2 | 5 |
+| | 15 |
+| | 5 |
++-------+-----+
+(4 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A9_B10 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_mixed) AS u(c)
+GROUP BY cube(c);
++------+-----+
+| c | cnt |
++------+-----+
+| 0.1 | 3 |
+| 1 | 3 |
+| S1 | 3 |
+| true | 3 |
+| | 15 |
+| | 3 |
++------+-----+
+(6 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A9_B11 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_mixed) AS u(c)
+GROUP BY cube(s_int, c);
++-------+------+-----+
+| s_int | c | cnt |
++-------+------+-----+
+| 1 | 0.1 | 1 |
+| 1 | 1 | 1 |
+| 1 | S1 | 1 |
+| 1 | true | 1 |
+| 1 | | 1 |
+| 1 | | 5 |
+| 2 | 0.1 | 1 |
+| 2 | 1 | 1 |
+| 2 | S1 | 1 |
+| 2 | true | 1 |
+| 2 | | 1 |
+| 2 | | 5 |
+| | 0.1 | 1 |
+| | 0.1 | 3 |
+| | 1 | 1 |
+| | 1 | 3 |
+| | S1 | 1 |
+| | S1 | 3 |
+| | true | 1 |
+| | true | 3 |
+| | | 1 |
+| | | 15 |
+| | | 3 |
+| | | 5 |
++-------+------+-----+
+(24 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A9_B12 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_mixed) AS u(c)
+GROUP BY cube(s_int);
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 5 |
+| 2 | 5 |
+| | 15 |
+| | 5 |
++-------+-----+
+(4 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A9_B13 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_mixed) AS u(c)
+GROUP BY c
+HAVING c = 'S1';
++----+-----+
+| c | cnt |
++----+-----+
+| S1 | 3 |
++----+-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A9_B14 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_mixed) AS u(c)
+GROUP BY s_int
+HAVING s_int=1;
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 5 |
++-------+-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A10_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[6]' RETURNING varchar array)) AS u(c)
+GROUP BY c;
++------+-----+
+| c | cnt |
++------+-----+
+| 0.1 | 3 |
+| 1 | 3 |
+| S1 | 3 |
+| true | 3 |
+| | 3 |
++------+-----+
+(5 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A10_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[6]' RETURNING varchar array)) AS u(c)
+GROUP BY s_int,
+ c;
++-------+------+-----+
+| s_int | c | cnt |
++-------+------+-----+
+| 1 | 0.1 | 1 |
+| 1 | 1 | 1 |
+| 1 | S1 | 1 |
+| 1 | true | 1 |
+| 1 | | 1 |
+| 2 | 0.1 | 1 |
+| 2 | 1 | 1 |
+| 2 | S1 | 1 |
+| 2 | true | 1 |
+| 2 | | 1 |
+| | 0.1 | 1 |
+| | 1 | 1 |
+| | S1 | 1 |
+| | true | 1 |
+| | | 1 |
++-------+------+-----+
+(15 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A10_B3 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[6]' RETURNING varchar array)) AS u(c)
+GROUP BY s_int;
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 5 |
+| 2 | 5 |
+| | 5 |
++-------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A10_B4 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[6]' RETURNING varchar array)) AS u(c)
+GROUP BY GROUPING
+SETS (c);
++------+-----+
+| c | cnt |
++------+-----+
+| 0.1 | 3 |
+| 1 | 3 |
+| S1 | 3 |
+| true | 3 |
+| | 3 |
++------+-----+
+(5 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A10_B5 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[6]' RETURNING varchar array)) AS u(c)
+GROUP BY GROUPING
+SETS (s_int,
+ c);
++-------+------+-----+
+| s_int | c | cnt |
++-------+------+-----+
+| 1 | | 5 |
+| 2 | | 5 |
+| | 0.1 | 3 |
+| | 1 | 3 |
+| | S1 | 3 |
+| | true | 3 |
+| | | 3 |
+| | | 5 |
++-------+------+-----+
+(8 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A10_B6 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[6]' RETURNING varchar array)) AS u(c)
+GROUP BY GROUPING
+SETS (s_int);
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 5 |
+| 2 | 5 |
+| | 5 |
++-------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A10_B7 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[6]' RETURNING varchar array)) AS u(c)
+GROUP BY rollup(c);
++------+-----+
+| c | cnt |
++------+-----+
+| 0.1 | 3 |
+| 1 | 3 |
+| S1 | 3 |
+| true | 3 |
+| | 15 |
+| | 3 |
++------+-----+
+(6 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A10_B8 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[6]' RETURNING varchar array)) AS u(c)
+GROUP BY rollup(s_int, c);
++-------+------+-----+
+| s_int | c | cnt |
++-------+------+-----+
+| 1 | 0.1 | 1 |
+| 1 | 1 | 1 |
+| 1 | S1 | 1 |
+| 1 | true | 1 |
+| 1 | | 1 |
+| 1 | | 5 |
+| 2 | 0.1 | 1 |
+| 2 | 1 | 1 |
+| 2 | S1 | 1 |
+| 2 | true | 1 |
+| 2 | | 1 |
+| 2 | | 5 |
+| | 0.1 | 1 |
+| | 1 | 1 |
+| | S1 | 1 |
+| | true | 1 |
+| | | 1 |
+| | | 15 |
+| | | 5 |
++-------+------+-----+
+(19 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A10_B9 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[6]' RETURNING varchar array)) AS u(c)
+GROUP BY rollup(s_int);
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 5 |
+| 2 | 5 |
+| | 15 |
+| | 5 |
++-------+-----+
+(4 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A10_B10 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[6]' RETURNING varchar array)) AS u(c)
+GROUP BY cube(c);
++------+-----+
+| c | cnt |
++------+-----+
+| 0.1 | 3 |
+| 1 | 3 |
+| S1 | 3 |
+| true | 3 |
+| | 15 |
+| | 3 |
++------+-----+
+(6 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A10_B11 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[6]' RETURNING varchar array)) AS u(c)
+GROUP BY cube(s_int, c);
++-------+------+-----+
+| s_int | c | cnt |
++-------+------+-----+
+| 1 | 0.1 | 1 |
+| 1 | 1 | 1 |
+| 1 | S1 | 1 |
+| 1 | true | 1 |
+| 1 | | 1 |
+| 1 | | 5 |
+| 2 | 0.1 | 1 |
+| 2 | 1 | 1 |
+| 2 | S1 | 1 |
+| 2 | true | 1 |
+| 2 | | 1 |
+| 2 | | 5 |
+| | 0.1 | 1 |
+| | 0.1 | 3 |
+| | 1 | 1 |
+| | 1 | 3 |
+| | S1 | 1 |
+| | S1 | 3 |
+| | true | 1 |
+| | true | 3 |
+| | | 1 |
+| | | 15 |
+| | | 3 |
+| | | 5 |
++-------+------+-----+
+(24 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A10_B12 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[6]' RETURNING varchar array)) AS u(c)
+GROUP BY cube(s_int);
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 5 |
+| 2 | 5 |
+| | 15 |
+| | 5 |
++-------+-----+
+(4 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A10_B13 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[6]' RETURNING varchar array)) AS u(c)
+GROUP BY c
+HAVING c = 'S1';
++----+-----+
+| c | cnt |
++----+-----+
+| S1 | 3 |
++----+-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A10_B14 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[6]' RETURNING varchar array)) AS u(c)
+GROUP BY s_int
+HAVING s_int=1;
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 5 |
++-------+-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A11_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[4]' RETURNING varchar array)) AS u(c)
+GROUP BY c;
++---+-----+
+| c | cnt |
++---+-----+
+| | 9 |
++---+-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A11_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[4]' RETURNING varchar array)) AS u(c)
+GROUP BY s_int,
+ c;
++-------+---+-----+
+| s_int | c | cnt |
++-------+---+-----+
+| 1 | | 3 |
+| 2 | | 3 |
+| | | 3 |
++-------+---+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A11_B3 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[4]' RETURNING varchar array)) AS u(c)
+GROUP BY s_int;
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
++-------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A11_B4 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[4]' RETURNING varchar array)) AS u(c)
+GROUP BY GROUPING
+SETS (c);
++---+-----+
+| c | cnt |
++---+-----+
+| | 9 |
++---+-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A11_B5 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[4]' RETURNING varchar array)) AS u(c)
+GROUP BY GROUPING
+SETS (s_int,
+ c);
++-------+---+-----+
+| s_int | c | cnt |
++-------+---+-----+
+| 1 | | 3 |
+| 2 | | 3 |
+| | | 3 |
+| | | 9 |
++-------+---+-----+
+(4 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A11_B6 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[4]' RETURNING varchar array)) AS u(c)
+GROUP BY GROUPING
+SETS (s_int);
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
++-------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A11_B7 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[4]' RETURNING varchar array)) AS u(c)
+GROUP BY rollup(c);
++---+-----+
+| c | cnt |
++---+-----+
+| | 9 |
+| | 9 |
++---+-----+
+(2 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A11_B8 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[4]' RETURNING varchar array)) AS u(c)
+GROUP BY rollup(s_int, c);
++-------+---+-----+
+| s_int | c | cnt |
++-------+---+-----+
+| 1 | | 3 |
+| 1 | | 3 |
+| 2 | | 3 |
+| 2 | | 3 |
+| | | 3 |
+| | | 3 |
+| | | 9 |
++-------+---+-----+
+(7 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A11_B9 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[4]' RETURNING varchar array)) AS u(c)
+GROUP BY rollup(s_int);
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
+| | 9 |
++-------+-----+
+(4 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A11_B10 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[4]' RETURNING varchar array)) AS u(c)
+GROUP BY cube(c);
++---+-----+
+| c | cnt |
++---+-----+
+| | 9 |
+| | 9 |
++---+-----+
+(2 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A11_B11 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[4]' RETURNING varchar array)) AS u(c)
+GROUP BY cube(s_int, c);
++-------+---+-----+
+| s_int | c | cnt |
++-------+---+-----+
+| 1 | | 3 |
+| 1 | | 3 |
+| 2 | | 3 |
+| 2 | | 3 |
+| | | 3 |
+| | | 3 |
+| | | 9 |
+| | | 9 |
++-------+---+-----+
+(8 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A11_B12 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[4]' RETURNING varchar array)) AS u(c)
+GROUP BY cube(s_int);
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
+| 2 | 3 |
+| | 3 |
+| | 9 |
++-------+-----+
+(4 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A11_B13 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[4]' RETURNING varchar array)) AS u(c)
+GROUP BY c
+HAVING c IS NULL;
++---+-----+
+| c | cnt |
++---+-----+
+| | 9 |
++---+-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A11_B14 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[4]' RETURNING varchar array)) AS u(c)
+GROUP BY s_int
+HAVING s_int=1;
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 1 | 3 |
++-------+-----+
+(1 row)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A12_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[5]' RETURNING varchar array)) AS u(c)
+GROUP BY c;
++---+-----+
+| c | cnt |
++---+-----+
++---+-----+
+(0 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A12_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[5]' RETURNING varchar array)) AS u(c)
+GROUP BY s_int,
+ c;
++-------+---+-----+
+| s_int | c | cnt |
++-------+---+-----+
++-------+---+-----+
+(0 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A12_B3 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[5]' RETURNING varchar array)) AS u(c)
+GROUP BY s_int;
++-------+-----+
+| s_int | cnt |
++-------+-----+
++-------+-----+
+(0 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A12_B4 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[5]' RETURNING varchar array)) AS u(c)
+GROUP BY GROUPING
+SETS (c);
++---+-----+
+| c | cnt |
++---+-----+
++---+-----+
+(0 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A12_B5 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[5]' RETURNING varchar array)) AS u(c)
+GROUP BY GROUPING
+SETS (s_int,
+ c);
++-------+---+-----+
+| s_int | c | cnt |
++-------+---+-----+
++-------+---+-----+
+(0 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A12_B6 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[5]' RETURNING varchar array)) AS u(c)
+GROUP BY GROUPING
+SETS (s_int);
++-------+-----+
+| s_int | cnt |
++-------+-----+
++-------+-----+
+(0 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A12_B7 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[5]' RETURNING varchar array)) AS u(c)
+GROUP BY rollup(c);
++---+-----+
+| c | cnt |
++---+-----+
++---+-----+
+(0 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A12_B8 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[5]' RETURNING varchar array)) AS u(c)
+GROUP BY rollup(s_int, c);
++-------+---+-----+
+| s_int | c | cnt |
++-------+---+-----+
++-------+---+-----+
+(0 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A12_B9 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[5]' RETURNING varchar array)) AS u(c)
+GROUP BY rollup(s_int);
++-------+-----+
+| s_int | cnt |
++-------+-----+
++-------+-----+
+(0 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A12_B10 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[5]' RETURNING varchar array)) AS u(c)
+GROUP BY cube(c);
++---+-----+
+| c | cnt |
++---+-----+
++---+-----+
+(0 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A12_B11 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[5]' RETURNING varchar array)) AS u(c)
+GROUP BY cube(s_int, c);
++-------+---+-----+
+| s_int | c | cnt |
++-------+---+-----+
++-------+---+-----+
+(0 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A12_B12 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[5]' RETURNING varchar array)) AS u(c)
+GROUP BY cube(s_int);
++-------+-----+
+| s_int | cnt |
++-------+-----+
++-------+-----+
+(0 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A12_B13 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[5]' RETURNING varchar array)) AS u(c)
+GROUP BY c
+HAVING c = 1;
++---+-----+
+| c | cnt |
++---+-----+
++---+-----+
+(0 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_group_by TEST_ID: A12_B14 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[5]' RETURNING varchar array)) AS u(c)
+GROUP BY s_int
+HAVING s_int=1;
++-------+-----+
+| s_int | cnt |
++-------+-----+
++-------+-----+
+(0 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A1_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_bool) AS u(c)
+GROUP BY c
+ORDER BY c ASC;
++---+-----+
+| c | cnt |
++---+-----+
+| | 3 |
+| 0 | 3 |
+| 1 | 3 |
++---+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A1_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_bool) AS u(c)
+GROUP BY c
+ORDER BY 1 DESC;
++---+-----+
+| c | cnt |
++---+-----+
+| 1 | 3 |
+| 0 | 3 |
+| | 3 |
++---+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A1_B3 TYPE: NEGATIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(a_bool) AS u(c)
+ORDER BY c ASC;
+CannotBuildQueryException
+!error
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A1_B4 TYPE: NEGATIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(a_bool) AS u(c)
+ORDER BY 1 DESC;
+CannotBuildQueryException
+!error
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A1_B5 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM
+ (SELECT c
+ FROM test_unnest,
+ unnest(a_bool) AS u(c)
+ ORDER BY c ASC);
++---+
+| c |
++---+
+| 0 |
+| 0 |
+| 0 |
+| 1 |
+| 1 |
+| 1 |
+| |
+| |
+| |
++---+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A1_B6 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM
+ (SELECT c
+ FROM test_unnest,
+ unnest(a_bool) AS u(c)
+ ORDER BY 1 DESC);
++---+
+| c |
++---+
+| 0 |
+| 0 |
+| 0 |
+| 1 |
+| 1 |
+| 1 |
+| |
+| |
+| |
++---+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A1_B7 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT __time,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_bool) AS u(c)
+GROUP BY __time
+ORDER BY __time ASC;
++-------------------------+-----+
+| __time | cnt |
++-------------------------+-----+
+| 2022-01-01 00:00:00.000 | 3 |
+| 2022-02-01 00:00:00.000 | 3 |
+| 2022-03-01 00:00:00.000 | 3 |
++-------------------------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A1_B8 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT __time,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_bool) AS u(c)
+GROUP BY __time
+ORDER BY __time DESC;
++-------------------------+-----+
+| __time | cnt |
++-------------------------+-----+
+| 2022-03-01 00:00:00.000 | 3 |
+| 2022-02-01 00:00:00.000 | 3 |
+| 2022-01-01 00:00:00.000 | 3 |
++-------------------------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A1_B9 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT __time
+FROM test_unnest,
+ unnest(a_bool) AS u(c)
+ORDER BY __time ASC;
++-------------------------+
+| __time |
++-------------------------+
+| 2022-01-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
++-------------------------+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A1_B10 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT __time
+FROM test_unnest,
+ unnest(a_bool) AS u(c)
+ORDER BY 1 DESC;
++-------------------------+
+| __time |
++-------------------------+
+| 2022-03-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
++-------------------------+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A1_B11 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT __time
+FROM
+ (SELECT __time
+ FROM test_unnest,
+ unnest(a_bool) AS u(c)
+ ORDER BY __time ASC);
++-------------------------+
+| __time |
++-------------------------+
+| 2022-01-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
++-------------------------+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A1_B12 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT __time
+FROM
+ (SELECT __time
+ FROM test_unnest,
+ unnest(a_bool) AS u(c)
+ ORDER BY 1 DESC);
++-------------------------+
+| __time |
++-------------------------+
+| 2022-01-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
++-------------------------+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A1_B13 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_bool) AS u(c)
+GROUP BY s_int
+ORDER BY s_int ASC;
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| | 3 |
+| 1 | 3 |
+| 2 | 3 |
++-------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A1_B14 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_bool) AS u(c)
+GROUP BY s_int
+ORDER BY 1 DESC;
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 2 | 3 |
+| 1 | 3 |
+| | 3 |
++-------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A1_B15 TYPE: NEGATIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int
+FROM test_unnest,
+ unnest(a_bool) AS u(c)
+ORDER BY s_int ASC;
+CannotBuildQueryException
+!error
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A1_B16 TYPE: NEGATIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int
+FROM test_unnest,
+ unnest(a_bool) AS u(c)
+ORDER BY 1 DESC;
+CannotBuildQueryException
+!error
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A1_B17 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int
+FROM
+ (SELECT s_int
+ FROM test_unnest,
+ unnest(a_bool) AS u(c)
+ ORDER BY s_int ASC);
++-------+
+| s_int |
++-------+
+| 1 |
+| 1 |
+| 1 |
+| 2 |
+| 2 |
+| 2 |
+| |
+| |
+| |
++-------+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A1_B18 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int
+FROM
+ (SELECT s_int
+ FROM test_unnest,
+ unnest(a_bool) AS u(c)
+ ORDER BY 1 DESC);
++-------+
+| s_int |
++-------+
+| 1 |
+| 1 |
+| 1 |
+| 2 |
+| 2 |
+| 2 |
+| |
+| |
+| |
++-------+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A2_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u(c)
+GROUP BY c
+ORDER BY c ASC;
++-------+-----+
+| c | cnt |
++-------+-----+
+| | 3 |
+| false | 3 |
+| true | 3 |
++-------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A2_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u(c)
+GROUP BY c
+ORDER BY 1 DESC;
++-------+-----+
+| c | cnt |
++-------+-----+
+| true | 3 |
+| false | 3 |
+| | 3 |
++-------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A2_B3 TYPE: NEGATIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u(c)
+ORDER BY c ASC;
+CannotBuildQueryException
+!error
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A2_B4 TYPE: NEGATIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u(c)
+ORDER BY 1 DESC;
+CannotBuildQueryException
+!error
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A2_B5 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM
+ (SELECT c
+ FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u(c)
+ ORDER BY c ASC);
++-------+
+| c |
++-------+
+| false |
+| false |
+| false |
+| true |
+| true |
+| true |
+| |
+| |
+| |
++-------+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A2_B6 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM
+ (SELECT c
+ FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u(c)
+ ORDER BY 1 DESC);
++-------+
+| c |
++-------+
+| false |
+| false |
+| false |
+| true |
+| true |
+| true |
+| |
+| |
+| |
++-------+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A2_B7 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT __time,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u(c)
+GROUP BY __time
+ORDER BY __time ASC;
++-------------------------+-----+
+| __time | cnt |
++-------------------------+-----+
+| 2022-01-01 00:00:00.000 | 3 |
+| 2022-02-01 00:00:00.000 | 3 |
+| 2022-03-01 00:00:00.000 | 3 |
++-------------------------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A2_B8 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT __time,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u(c)
+GROUP BY __time
+ORDER BY __time DESC;
++-------------------------+-----+
+| __time | cnt |
++-------------------------+-----+
+| 2022-03-01 00:00:00.000 | 3 |
+| 2022-02-01 00:00:00.000 | 3 |
+| 2022-01-01 00:00:00.000 | 3 |
++-------------------------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A2_B9 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT __time
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u(c)
+ORDER BY __time ASC;
++-------------------------+
+| __time |
++-------------------------+
+| 2022-01-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
++-------------------------+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A2_B10 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT __time
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u(c)
+ORDER BY 1 DESC;
++-------------------------+
+| __time |
++-------------------------+
+| 2022-03-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
++-------------------------+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A2_B11 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT __time
+FROM
+ (SELECT __time
+ FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u(c)
+ ORDER BY __time ASC);
++-------------------------+
+| __time |
++-------------------------+
+| 2022-01-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
++-------------------------+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A2_B12 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT __time
+FROM
+ (SELECT __time
+ FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u(c)
+ ORDER BY 1 DESC);
++-------------------------+
+| __time |
++-------------------------+
+| 2022-01-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
++-------------------------+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A2_B13 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u(c)
+GROUP BY s_int
+ORDER BY s_int ASC;
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| | 3 |
+| 1 | 3 |
+| 2 | 3 |
++-------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A2_B14 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u(c)
+GROUP BY s_int
+ORDER BY 1 DESC;
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 2 | 3 |
+| 1 | 3 |
+| | 3 |
++-------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A2_B15 TYPE: NEGATIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u(c)
+ORDER BY s_int ASC;
+CannotBuildQueryException
+!error
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A2_B16 TYPE: NEGATIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u(c)
+ORDER BY 1 DESC;
+CannotBuildQueryException
+!error
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A2_B17 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int
+FROM
+ (SELECT s_int
+ FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u(c)
+ ORDER BY s_int ASC);
++-------+
+| s_int |
++-------+
+| 1 |
+| 1 |
+| 1 |
+| 2 |
+| 2 |
+| 2 |
+| |
+| |
+| |
++-------+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A2_B18 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int
+FROM
+ (SELECT s_int
+ FROM test_unnest,
+ unnest(json_value(a_nested, '$[0]' RETURNING boolean array)) AS u(c)
+ ORDER BY 1 DESC);
++-------+
+| s_int |
++-------+
+| 1 |
+| 1 |
+| 1 |
+| 2 |
+| 2 |
+| 2 |
+| |
+| |
+| |
++-------+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A3_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_int) AS u(c)
+GROUP BY c
+ORDER BY c ASC;
++---+-----+
+| c | cnt |
++---+-----+
+| | 3 |
+| 1 | 3 |
+| 2 | 3 |
++---+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A3_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_int) AS u(c)
+GROUP BY c
+ORDER BY 1 DESC;
++---+-----+
+| c | cnt |
++---+-----+
+| 2 | 3 |
+| 1 | 3 |
+| | 3 |
++---+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A3_B3 TYPE: NEGATIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(a_int) AS u(c)
+ORDER BY c ASC;
+CannotBuildQueryException
+!error
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A3_B4 TYPE: NEGATIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(a_int) AS u(c)
+ORDER BY 1 DESC;
+CannotBuildQueryException
+!error
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A3_B5 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM
+ (SELECT c
+ FROM test_unnest,
+ unnest(a_int) AS u(c)
+ ORDER BY c ASC);
++---+
+| c |
++---+
+| 1 |
+| 1 |
+| 1 |
+| 2 |
+| 2 |
+| 2 |
+| |
+| |
+| |
++---+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A3_B6 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM
+ (SELECT c
+ FROM test_unnest,
+ unnest(a_int) AS u(c)
+ ORDER BY 1 DESC);
++---+
+| c |
++---+
+| 1 |
+| 1 |
+| 1 |
+| 2 |
+| 2 |
+| 2 |
+| |
+| |
+| |
++---+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A3_B7 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT __time,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_int) AS u(c)
+GROUP BY __time
+ORDER BY __time ASC;
++-------------------------+-----+
+| __time | cnt |
++-------------------------+-----+
+| 2022-01-01 00:00:00.000 | 3 |
+| 2022-02-01 00:00:00.000 | 3 |
+| 2022-03-01 00:00:00.000 | 3 |
++-------------------------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A3_B8 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT __time,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_int) AS u(c)
+GROUP BY __time
+ORDER BY __time DESC;
++-------------------------+-----+
+| __time | cnt |
++-------------------------+-----+
+| 2022-03-01 00:00:00.000 | 3 |
+| 2022-02-01 00:00:00.000 | 3 |
+| 2022-01-01 00:00:00.000 | 3 |
++-------------------------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A3_B9 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT __time
+FROM test_unnest,
+ unnest(a_int) AS u(c)
+ORDER BY __time ASC;
++-------------------------+
+| __time |
++-------------------------+
+| 2022-01-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
++-------------------------+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A3_B10 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT __time
+FROM test_unnest,
+ unnest(a_int) AS u(c)
+ORDER BY 1 DESC;
++-------------------------+
+| __time |
++-------------------------+
+| 2022-03-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
++-------------------------+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A3_B11 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT __time
+FROM
+ (SELECT __time
+ FROM test_unnest,
+ unnest(a_int) AS u(c)
+ ORDER BY __time ASC);
++-------------------------+
+| __time |
++-------------------------+
+| 2022-01-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
++-------------------------+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A3_B12 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT __time
+FROM
+ (SELECT __time
+ FROM test_unnest,
+ unnest(a_int) AS u(c)
+ ORDER BY 1 DESC);
++-------------------------+
+| __time |
++-------------------------+
+| 2022-01-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
++-------------------------+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A3_B13 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_int) AS u(c)
+GROUP BY s_int
+ORDER BY s_int ASC;
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| | 3 |
+| 1 | 3 |
+| 2 | 3 |
++-------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A3_B14 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_int) AS u(c)
+GROUP BY s_int
+ORDER BY 1 DESC;
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 2 | 3 |
+| 1 | 3 |
+| | 3 |
++-------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A3_B15 TYPE: NEGATIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int
+FROM test_unnest,
+ unnest(a_int) AS u(c)
+ORDER BY s_int ASC;
+CannotBuildQueryException
+!error
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A3_B16 TYPE: NEGATIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int
+FROM test_unnest,
+ unnest(a_int) AS u(c)
+ORDER BY 1 DESC;
+CannotBuildQueryException
+!error
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A3_B17 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int
+FROM
+ (SELECT s_int
+ FROM test_unnest,
+ unnest(a_int) AS u(c)
+ ORDER BY s_int ASC);
++-------+
+| s_int |
++-------+
+| 1 |
+| 1 |
+| 1 |
+| 2 |
+| 2 |
+| 2 |
+| |
+| |
+| |
++-------+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A3_B18 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int
+FROM
+ (SELECT s_int
+ FROM test_unnest,
+ unnest(a_int) AS u(c)
+ ORDER BY 1 DESC);
++-------+
+| s_int |
++-------+
+| 1 |
+| 1 |
+| 1 |
+| 2 |
+| 2 |
+| 2 |
+| |
+| |
+| |
++-------+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A4_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u(c)
+GROUP BY c
+ORDER BY c ASC;
++---+-----+
+| c | cnt |
++---+-----+
+| | 3 |
+| 1 | 3 |
+| 2 | 3 |
++---+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A4_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u(c)
+GROUP BY c
+ORDER BY 1 DESC;
++---+-----+
+| c | cnt |
++---+-----+
+| 2 | 3 |
+| 1 | 3 |
+| | 3 |
++---+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A4_B3 TYPE: NEGATIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u(c)
+ORDER BY c ASC;
+CannotBuildQueryException
+!error
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A4_B4 TYPE: NEGATIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u(c)
+ORDER BY 1 DESC;
+CannotBuildQueryException
+!error
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A4_B5 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM
+ (SELECT c
+ FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u(c)
+ ORDER BY c ASC);
++---+
+| c |
++---+
+| 1 |
+| 1 |
+| 1 |
+| 2 |
+| 2 |
+| 2 |
+| |
+| |
+| |
++---+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A4_B6 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM
+ (SELECT c
+ FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u(c)
+ ORDER BY 1 DESC);
++---+
+| c |
++---+
+| 1 |
+| 1 |
+| 1 |
+| 2 |
+| 2 |
+| 2 |
+| |
+| |
+| |
++---+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A4_B7 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT __time,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u(c)
+GROUP BY __time
+ORDER BY __time ASC;
++-------------------------+-----+
+| __time | cnt |
++-------------------------+-----+
+| 2022-01-01 00:00:00.000 | 3 |
+| 2022-02-01 00:00:00.000 | 3 |
+| 2022-03-01 00:00:00.000 | 3 |
++-------------------------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A4_B8 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT __time,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u(c)
+GROUP BY __time
+ORDER BY __time DESC;
++-------------------------+-----+
+| __time | cnt |
++-------------------------+-----+
+| 2022-03-01 00:00:00.000 | 3 |
+| 2022-02-01 00:00:00.000 | 3 |
+| 2022-01-01 00:00:00.000 | 3 |
++-------------------------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A4_B9 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT __time
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u(c)
+ORDER BY __time ASC;
++-------------------------+
+| __time |
++-------------------------+
+| 2022-01-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
++-------------------------+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A4_B10 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT __time
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u(c)
+ORDER BY 1 DESC;
++-------------------------+
+| __time |
++-------------------------+
+| 2022-03-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
++-------------------------+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A4_B11 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT __time
+FROM
+ (SELECT __time
+ FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u(c)
+ ORDER BY __time ASC);
++-------------------------+
+| __time |
++-------------------------+
+| 2022-01-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
++-------------------------+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A4_B12 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT __time
+FROM
+ (SELECT __time
+ FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u(c)
+ ORDER BY 1 DESC);
++-------------------------+
+| __time |
++-------------------------+
+| 2022-01-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
++-------------------------+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A4_B13 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u(c)
+GROUP BY s_int
+ORDER BY s_int ASC;
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| | 3 |
+| 1 | 3 |
+| 2 | 3 |
++-------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A4_B14 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u(c)
+GROUP BY s_int
+ORDER BY 1 DESC;
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 2 | 3 |
+| 1 | 3 |
+| | 3 |
++-------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A4_B15 TYPE: NEGATIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u(c)
+ORDER BY s_int ASC;
+CannotBuildQueryException
+!error
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A4_B16 TYPE: NEGATIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u(c)
+ORDER BY 1 DESC;
+CannotBuildQueryException
+!error
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A4_B17 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int
+FROM
+ (SELECT s_int
+ FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u(c)
+ ORDER BY s_int ASC);
++-------+
+| s_int |
++-------+
+| 1 |
+| 1 |
+| 1 |
+| 2 |
+| 2 |
+| 2 |
+| |
+| |
+| |
++-------+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A4_B18 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int
+FROM
+ (SELECT s_int
+ FROM test_unnest,
+ unnest(json_value(a_nested, '$[1]' RETURNING bigint array)) AS u(c)
+ ORDER BY 1 DESC);
++-------+
+| s_int |
++-------+
+| 1 |
+| 1 |
+| 1 |
+| 2 |
+| 2 |
+| 2 |
+| |
+| |
+| |
++-------+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A5_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_float) AS u(c)
+GROUP BY c
+ORDER BY c ASC;
++-----+-----+
+| c | cnt |
++-----+-----+
+| | 3 |
+| 0.1 | 3 |
+| 0.2 | 3 |
++-----+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A5_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_float) AS u(c)
+GROUP BY c
+ORDER BY 1 DESC;
++-----+-----+
+| c | cnt |
++-----+-----+
+| 0.2 | 3 |
+| 0.1 | 3 |
+| | 3 |
++-----+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A5_B3 TYPE: NEGATIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(a_float) AS u(c)
+ORDER BY c ASC;
+CannotBuildQueryException
+!error
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A5_B4 TYPE: NEGATIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(a_float) AS u(c)
+ORDER BY 1 DESC;
+CannotBuildQueryException
+!error
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A5_B5 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM
+ (SELECT c
+ FROM test_unnest,
+ unnest(a_float) AS u(c)
+ ORDER BY c ASC);
++-----+
+| c |
++-----+
+| 0.1 |
+| 0.1 |
+| 0.1 |
+| 0.2 |
+| 0.2 |
+| 0.2 |
+| |
+| |
+| |
++-----+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A5_B6 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM
+ (SELECT c
+ FROM test_unnest,
+ unnest(a_float) AS u(c)
+ ORDER BY 1 DESC);
++-----+
+| c |
++-----+
+| 0.1 |
+| 0.1 |
+| 0.1 |
+| 0.2 |
+| 0.2 |
+| 0.2 |
+| |
+| |
+| |
++-----+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A5_B7 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT __time,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_float) AS u(c)
+GROUP BY __time
+ORDER BY __time ASC;
++-------------------------+-----+
+| __time | cnt |
++-------------------------+-----+
+| 2022-01-01 00:00:00.000 | 3 |
+| 2022-02-01 00:00:00.000 | 3 |
+| 2022-03-01 00:00:00.000 | 3 |
++-------------------------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A5_B8 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT __time,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_float) AS u(c)
+GROUP BY __time
+ORDER BY __time DESC;
++-------------------------+-----+
+| __time | cnt |
++-------------------------+-----+
+| 2022-03-01 00:00:00.000 | 3 |
+| 2022-02-01 00:00:00.000 | 3 |
+| 2022-01-01 00:00:00.000 | 3 |
++-------------------------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A5_B9 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT __time
+FROM test_unnest,
+ unnest(a_float) AS u(c)
+ORDER BY __time ASC;
++-------------------------+
+| __time |
++-------------------------+
+| 2022-01-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
++-------------------------+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A5_B10 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT __time
+FROM test_unnest,
+ unnest(a_float) AS u(c)
+ORDER BY 1 DESC;
++-------------------------+
+| __time |
++-------------------------+
+| 2022-03-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
++-------------------------+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A5_B11 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT __time
+FROM
+ (SELECT __time
+ FROM test_unnest,
+ unnest(a_float) AS u(c)
+ ORDER BY __time ASC);
++-------------------------+
+| __time |
++-------------------------+
+| 2022-01-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
++-------------------------+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A5_B12 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT __time
+FROM
+ (SELECT __time
+ FROM test_unnest,
+ unnest(a_float) AS u(c)
+ ORDER BY 1 DESC);
++-------------------------+
+| __time |
++-------------------------+
+| 2022-01-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
+| 2022-01-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-02-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
+| 2022-03-01 00:00:00.000 |
++-------------------------+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A5_B13 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_float) AS u(c)
+GROUP BY s_int
+ORDER BY s_int ASC;
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| | 3 |
+| 1 | 3 |
+| 2 | 3 |
++-------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A5_B14 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int,
+ count(*) cnt
+FROM test_unnest,
+ unnest(a_float) AS u(c)
+GROUP BY s_int
+ORDER BY 1 DESC;
++-------+-----+
+| s_int | cnt |
++-------+-----+
+| 2 | 3 |
+| 1 | 3 |
+| | 3 |
++-------+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A5_B15 TYPE: NEGATIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int
+FROM test_unnest,
+ unnest(a_float) AS u(c)
+ORDER BY s_int ASC;
+CannotBuildQueryException
+!error
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A5_B16 TYPE: NEGATIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int
+FROM test_unnest,
+ unnest(a_float) AS u(c)
+ORDER BY 1 DESC;
+CannotBuildQueryException
+!error
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A5_B17 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int
+FROM
+ (SELECT s_int
+ FROM test_unnest,
+ unnest(a_float) AS u(c)
+ ORDER BY s_int ASC);
++-------+
+| s_int |
++-------+
+| 1 |
+| 1 |
+| 1 |
+| 2 |
+| 2 |
+| 2 |
+| |
+| |
+| |
++-------+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A5_B18 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT s_int
+FROM
+ (SELECT s_int
+ FROM test_unnest,
+ unnest(a_float) AS u(c)
+ ORDER BY 1 DESC);
++-------+
+| s_int |
++-------+
+| 1 |
+| 1 |
+| 1 |
+| 2 |
+| 2 |
+| 2 |
+| |
+| |
+| |
++-------+
+(9 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A6_B1 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[2]' RETURNING DOUBLE array)) AS u(c)
+GROUP BY c
+ORDER BY c ASC;
++-----+-----+
+| c | cnt |
++-----+-----+
+| | 3 |
+| 0.1 | 3 |
+| 0.2 | 3 |
++-----+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A6_B2 TYPE: POSITIVE TEST
+#-------------------------------------------------------------------------
+SELECT c,
+ count(*) cnt
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[2]' RETURNING DOUBLE array)) AS u(c)
+GROUP BY c
+ORDER BY 1 DESC;
++-----+-----+
+| c | cnt |
++-----+-----+
+| 0.2 | 3 |
+| 0.1 | 3 |
+| | 3 |
++-----+-----+
+(3 rows)
+
+!ok
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A6_B3 TYPE: NEGATIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[2]' RETURNING DOUBLE array)) AS u(c)
+ORDER BY c ASC;
+CannotBuildQueryException
+!error
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A6_B4 TYPE: NEGATIVE TEST
+#-------------------------------------------------------------------------
+SELECT c
+FROM test_unnest,
+ unnest(json_value(a_nested, '$[2]' RETURNING DOUBLE array)) AS u(c)
+ORDER BY 1 DESC;
+CannotBuildQueryException
+!error
+
+#-------------------------------------------------------------------------
+# TESTCASE: test_order_by TEST_ID: A6_B5 TYPE: POSITIVE TEST
... 156018 lines suppressed ...
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org