This is an automated email from the ASF dual-hosted git repository.
yangzhg pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 1938899aa3 [regression-test] add grouping sets test case (#18194)
1938899aa3 is described below
commit 1938899aa3b54945ddf34d646c3ecebbfa240432
Author: zhangy5 <[email protected]>
AuthorDate: Fri Mar 31 11:00:38 2023 +0800
[regression-test] add grouping sets test case (#18194)
---
.../query_p0/grouping_sets/test_grouping_sets.out | 151 +++++++++++++++++++
regression-test/plugins/plugin_check.groovy | 41 ++++++
.../grouping_sets/test_grouping_sets.groovy | 162 ++++++++++++++++++++-
3 files changed, 351 insertions(+), 3 deletions(-)
diff --git a/regression-test/data/query_p0/grouping_sets/test_grouping_sets.out
b/regression-test/data/query_p0/grouping_sets/test_grouping_sets.out
index 564103d871..b3d3050ee7 100644
--- a/regression-test/data/query_p0/grouping_sets/test_grouping_sets.out
+++ b/regression-test/data/query_p0/grouping_sets/test_grouping_sets.out
@@ -52,3 +52,154 @@
-- !select8 --
test 2
+-- !select9 --
+\N 5970
+1001 3978
+1002 1992
+
+-- !select10 --
+\N 1986
+\N 1989
+1 \N
+2 \N
+3 \N
+
+-- !select11 --
+\N \N 3654.0
+1901-12-31 \N 789.25
+1901-12-31 1989-03-21T13:00 789.25
+1989-03-21 \N 6.333
+1989-03-21 1989-03-21T13:00 6.333
+2012-03-14 \N 3654.0
+2012-03-14 2000-01-01T00:00 3654.0
+
+-- !select12 --
+\N \N \N 0.1
+\N \N 1901-12-31 20.268
+\N \N 1989-03-21 0.1
+\N \N 2012-03-14 78945.0
+\N false \N 20.268
+\N false 1901-12-31 20.268
+\N false 2012-03-14 78945.0
+\N true \N 0.1
+\N true 1989-03-21 0.1
+123.123 \N \N 0.1
+123.123 \N 1989-03-21 0.1
+123.123 true \N 0.1
+123.123 true 1989-03-21 0.1
+1243.500 \N \N 20.268
+1243.500 \N 1901-12-31 20.268
+1243.500 false \N 20.268
+1243.500 false 1901-12-31 20.268
+24453.325 \N \N 78945.0
+24453.325 \N 2012-03-14 78945.0
+24453.325 false \N 78945.0
+24453.325 false 2012-03-14 78945.0
+
+-- !select13 --
+\N \N 1002
+\N 1986 1001
+\N 1989 1002
+\N 11011902 1001
+\N 11011903 1001
+\N 11011905 1002
+1 \N 1001
+1 1989 1001
+1 11011902 1001
+2 \N 1001
+2 1986 1001
+2 11011903 1001
+3 \N 1002
+3 1989 1002
+3 11011905 1002
+
+-- !select14 --
+\N \N 1 1 3 3004
+\N 1986 1 0 2 1001
+\N 1989 1 0 2 2003
+1 \N 0 1 1 1001
+1 1989 0 0 0 1001
+2 \N 0 1 1 1001
+2 1986 0 0 0 1001
+3 \N 0 1 1 1002
+3 1989 0 0 0 1002
+
+-- !select15 --
+\N \N 1 1 3654.0
+1901-12-31 \N 0 1 789.25
+1901-12-31 1989-03-21T13:00 0 0 789.25
+1989-03-21 \N 0 1 6.333
+1989-03-21 1989-03-21T13:00 0 0 6.333
+2012-03-14 \N 0 1 3654.0
+2012-03-14 2000-01-01T00:00 0 0 3654.0
+
+-- !select16 --
+\N \N \N 1 3 7 0.1
+\N \N 1901-12-31 1 3 6 20.268
+\N \N 1989-03-21 1 3 6 0.1
+\N \N 2012-03-14 1 3 6 78945.0
+\N false \N 1 2 3 20.268
+\N false 1901-12-31 1 2 2 20.268
+\N false 2012-03-14 1 2 2 78945.0
+\N true \N 1 2 3 0.1
+\N true 1989-03-21 1 2 2 0.1
+123.123 \N \N 0 1 5 0.1
+123.123 \N 1989-03-21 0 1 4 0.1
+123.123 true \N 0 0 1 0.1
+123.123 true 1989-03-21 0 0 0 0.1
+1243.500 \N \N 0 1 5 20.268
+1243.500 \N 1901-12-31 0 1 4 20.268
+1243.500 false \N 0 0 1 20.268
+1243.500 false 1901-12-31 0 0 0 20.268
+24453.325 \N \N 0 1 5 78945.0
+24453.325 \N 2012-03-14 0 1 4 78945.0
+24453.325 false \N 0 0 1 78945.0
+24453.325 false 2012-03-14 0 0 0 78945.0
+
+-- !select17 --
+1 0
+2 0
+3 0
+
+-- !select18 --
+\N 1
+1 0
+2 0
+3 0
+
+-- !select19 --
+\N 1
+1 0
+2 0
+3 0
+
+-- !select20 --
+\N 1
+1 0
+2 0
+3 0
+
+-- !select21 --
+\N \N 3004 1 0 2
+\N 1986 1001 1 0 2
+\N 1989 2003 1 0 2
+1 \N 2002 0 1 1
+2 \N 2002 0 1 1
+3 \N 2004 0 1 1
+
+-- !select22 --
+\N \N 1001 1 0 2
+\N false 1001 1 0 2
+\N true 1001 1 0 2
+123.123 \N 1001 0 1 1
+1243.500 \N 1001 0 1 1
+24453.325 \N 1002 0 1 1
+
+-- !select23 --
+\N \N 1002 0 1 1
+\N 1989-03-21T13:00 1001 1 0 2
+\N 2000-01-01T00:00 1002 1 0 2
+1901-12-31 \N 1001 0 1 1
+1989-03-21 \N 1001 0 1 1
+2012-03-14 \N 1002 0 1 1
+
diff --git a/regression-test/plugins/plugin_check.groovy
b/regression-test/plugins/plugin_check.groovy
new file mode 100644
index 0000000000..492da18adc
--- /dev/null
+++ b/regression-test/plugins/plugin_check.groovy
@@ -0,0 +1,41 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+import org.apache.doris.regression.suite.Suite
+
+// check sql1 & sql2's results are same
+Suite.metaClass.check_sql_equal = { String sql1, String sql2 /* param */ ->
+ Suite suite = delegate as Suite
+ def res1 = suite.order_sql(sql1)
+ def res2 = suite.order_sql(sql2)
+ // suite.getLogger().info("res1: ${res1}".toString())
+ // suite.getLogger().info("res2: ${res2}".toString())
+ // suite.getLogger().info("Test plugin: suiteName:
${suite.name}".toString())
+ if (res1 != res2) {
+ assert res1.size() == res2.size(): "res1 length: ${res1.size()}, res2
length: ${res2.size()}".toString()
+ int maxSize = res1.size()
+ for(i in java.util.stream.LongStream.range(0, maxSize)) {
+ assert res1[i].size() != res2[i].size(): "result[${i}] size
mismatch"
+ assert res1[i] == res2[i]: "result[${i}] data mismatch"
+ }
+ }
+ suite.getLogger().info("${suite.name}: check these two sql
equal".toString())
+ return true
+}
+
+logger.info("Added 'check_sql_equal' function to Suite")
+
diff --git
a/regression-test/suites/query_p0/grouping_sets/test_grouping_sets.groovy
b/regression-test/suites/query_p0/grouping_sets/test_grouping_sets.groovy
index d0c28ea703..fa296c3a12 100644
--- a/regression-test/suites/query_p0/grouping_sets/test_grouping_sets.groovy
+++ b/regression-test/suites/query_p0/grouping_sets/test_grouping_sets.groovy
@@ -15,7 +15,8 @@
// specific language governing permissions and limitations
// under the License.
-suite("test_grouping_sets") {
+// Test grouping sets()/cube()/rollup()/grouping_id()/grouping()
+suite("test_grouping_sets", "p0") {
qt_select """
SELECT k1, k2, SUM(k3) FROM test_query_db.test
GROUP BY GROUPING SETS ((k1, k2), (k1), (k2), ( ) ) order by
k1, k2
@@ -59,9 +60,9 @@ suite("test_grouping_sets") {
exception "errCode = 2, detailMessage = column: `k3` cannot both in
select list and aggregate functions"
}
- qt_select7 """ select k1,k2,sum(k3) from test_query_db.test where 1 = 2
group by grouping sets((k1), (k1,k2)) """
+ qt_select7 """ select k1,k2,sum(k3) from test_query_db.test where 1 = 2
group by grouping sets((k1), (k1,k2)) """
- qt_select8 """ WITH dt AS
+ qt_select8 """ WITH dt AS
(select 'test' as name,1 as score
UNION
all
@@ -78,4 +79,159 @@ suite("test_grouping_sets") {
FROM result_data
WHERE name = 'test';
"""
+ // test grouping sets
+ qt_select9 """
+ SELECT k3, SUM( k1+k2 ) FROM test_query_db.test
+ GROUP BY GROUPING SETS ( (k3), ( ) ) order by k3
+ """
+ qt_select10 """
+ SELECT k1, k2 FROM test_query_db.test
+ GROUP BY GROUPING SETS ( (k1), (k2) ) order by k1, k2
+ """
+ def sql1_1 = """
+ SELECT k10, k11, MAX( k9 ) FROM test_query_db.test GROUP BY
ROLLUP(k10, k11) ORDER BY k10, k11
+ """
+ qt_select11 sql1_1
+ def sql1_2 = """
+ SELECT k10, k11, MAX( k9 ) FROM test_query_db.test
+ GROUP BY GROUPING SETS (( k10, k11 ), ( k10 ), ( )) ORDER BY
k10, k11;
+ """
+ check_sql_equal(sql1_1, sql1_2)
+
+ def sql2_1 = """
+ SELECT k5, k6, k10, MIN( k8 ) FROM test_query_db.test
+ GROUP BY CUBE( k5, k6, k10 ) ORDER BY k5, k6, k10
+ """
+ qt_select12 sql2_1
+ def sql2_2 = """
+ SELECT k5, k6, k10, MIN( k8 ) FROM test_query_db.test GROUP BY
+ GROUPING SETS (( k5, k6, k10 ), ( k5, k6 ), ( k5, k10 ), ( k5 ), ( k6,
k10 ), ( k6 ), ( k10 ), ( ))
+ ORDER BY k5, k6, k10;
+ """
+ check_sql_equal(sql2_1, sql2_2)
+ test {
+ sql """
+ SELECT k1, k3, MAX( k8 ) FROM test_query_db.test
+ GROUP BY k1, GROUPING SETS ( (k1, k3), (k1), ( ) ), ROLLUP(k1, k3)
+ """
+ exception "Syntax error"
+ }
+
+ qt_select13"""
+ SELECT * FROM
+ (SELECT k1,k4,MAX(k3) FROM test_query_db.test GROUP BY GROUPING
sets ((k1,k4),(k1),(k4),())
+ UNION SELECT k1,k2,MAX(k3) FROM test_query_db.test GROUP BY
GROUPING sets ((k1,k2),(k1),(k2),())
+ ) t ORDER BY k1, k4
+ """
+ // test grouping sets id
+ qt_select14 """
+ SELECT k1, k2, GROUPING(k1), GROUPING(k2), GROUPING_ID(k1, k2), SUM(
k3 )
+ FROM test_query_db.test
+ GROUP BY GROUPING SETS ( (k1, k2), (k1), (k2), ( ) )
+ ORDER BY k1, k2
+ """
+ def sql3_1 = """
+ SELECT k10, k11, GROUPING(k10), GROUPING(k11), MAX( k9 )
+ FROM test_query_db.test
+ GROUP BY ROLLUP(k10, k11) ORDER BY k10, k11
+ """
+ qt_select15 sql3_1
+ def sql3_2 = """
+ SELECT k10, k11, GROUPING(k10), GROUPING(k11), MAX( k9 )
+ FROM test_query_db.test GROUP BY
+ GROUPING SETS (( k10, k11 ), ( k10 ), ( )) ORDER BY k10, k11;
+ """
+ check_sql_equal(sql3_1, sql3_2)
+
+ def sql4_1 = """
+ SELECT k5, k6, k10, GROUPING_ID(k5), GROUPING_ID(k5, k6),
GROUPING_ID(k6, k5, k10), MIN( k8 )
+ FROM test_query_db.test
+ GROUP BY CUBE( k5, k6, k10 )
+ ORDER BY k5, k6, k10
+ """
+ qt_select16 sql4_1
+ def sql4_2 = """
+ SELECT k5, k6, k10, GROUPING_ID(k5), GROUPING_ID(k5, k6),
GROUPING_ID(k6, k5, k10), MIN( k8 )
+ FROM test_query_db.test
+ GROUP BY
+ GROUPING SETS ((k5, k6, k10), (k5, k6), (k5, k10), (k5), (k6,
k10), (k6), (k10), ())
+ ORDER BY k5, k6, k10
+ """
+ check_sql_equal(sql4_1, sql4_2)
+
+ qt_select17 """SELECT k1 ,GROUPING(k1) FROM test_query_db.test GROUP BY
GROUPING sets ((k1)) ORDER BY k1"""
+ qt_select18 """SELECT k1 ,GROUPING(k1) FROM test_query_db.test GROUP BY
GROUPING sets ((k1), ()) ORDER BY k1"""
+ qt_select19 """SELECT k1 ,GROUPING(k1) FROM test_query_db.test GROUP BY
ROLLUP (k1) ORDER BY k1"""
+ qt_select20 """SELECT k1 ,GROUPING(k1) FROM test_query_db.test GROUP BY
CUBE (k1) ORDER BY k1"""
+ test {
+ sql "SELECT k1 ,GROUPING(k2) FROM test_query_db.test GROUP BY CUBE
(k1) ORDER BY k1"
+ exception "Column `k2` in GROUP_ID() does not exist in GROUP BY clause"
+ }
+ // test grouping sets id contain null data
+ sql """drop table if exists test_query_db.test_grouping_sets_id_null"""
+ sql """create table if not exists test_query_db.test_grouping_sets_id_null
like test_query_db.test"""
+ sql """insert into test_query_db.test_grouping_sets_id_null SELECT * FROM
test_query_db.test"""
+ sql """
+ insert into test_query_db.test_grouping_sets_id_null
+ SELECT k0,k1,null,k3,k4,k5,null,null,k11,k7,k8,k9,k12,k13 FROM
test_query_db.test
+ """
+ qt_select21 """
+ SELECT k1,k2,SUM(k3),GROUPING(k1),GROUPING(k2),GROUPING_id(k1,k2)
+ FROM test_query_db.test_grouping_sets_id_null
+ GROUP BY GROUPING sets ((k1),(k2)) order by k1,k2
+ """
+ qt_select22 """
+ SELECT k5,k6,MIN(k3),GROUPING(k5),GROUPING(k6),GROUPING_id(k5,k6)
+ FROM test_query_db.test_grouping_sets_id_null
+ GROUP BY GROUPING sets ((k5),(k6)) order by k5,k6
+ """
+ qt_select23 """
+ SELECT k10,k11,MAX(k3) as a,GROUPING(k10) as b,GROUPING(k11) as
c,GROUPING_id(k10,k11) as d
+ FROM test_query_db.test_grouping_sets_id_null
+ GROUP BY GROUPING sets ((k10),(k11)) order by k10,k11,a,b,c,d
+ """
+ sql """drop table if exists test_query_db.test_grouping_sets_id_null"""
+ // test grouping sets shoot rollup
+ sql "drop table if exists test_query_db.test_grouping_sets_rollup"
+ sql """
+ create table if not exists test_query_db.test_grouping_sets_rollup(
+ k1 tinyint, k2 smallint, k3 int, k4 bigint, k5 decimal(9,3),
+ k6 char(5), k10 date, k11 datetime, k7 varchar(20), k8 double max,
k9 float SUM)
+ engine=olap distributed by hash(k1) buckets 5
+ ROLLUP(idx(k1, k2, k3), idx1(k1, k2, k3, k8))
+ properties("replication_num"="1")
+ """
+ sql """insert into test_query_db.test_grouping_sets_rollup
+ select k1, k2, k3, k4, k5, k6, k10, k11, k7, k8, k9 from
test_query_db.test
+ """
+ explain {
+ sql("SELECT k1, MAX( k8 ) FROM test_query_db.test_grouping_sets_rollup
GROUP BY GROUPING SETS( (k1), ())")
+ contains "(idx1)"
+ }
+ explain {
+ sql("""SELECT k1, k2, MAX( k8 )
+ FROM test_query_db.test_grouping_sets_rollup
+ GROUP BY GROUPING SETS ( (k1, k2), (k1), (k2), ( ) )
+ """)
+ contains "(idx1)"
+ }
+ explain {
+ sql("""SELECT k1, k2, MAX( k8 ) FROM
test_query_db.test_grouping_sets_rollup GROUP BY ROLLUP(k1, k2)""")
+ contains "(idx1)"
+ }
+ explain {
+ sql("""SELECT k1, k2, MAX( k8 ) FROM
test_query_db.test_grouping_sets_rollup GROUP BY ROLLUP(k1, k2)""")
+ contains "(idx1)"
+ }
+ explain {
+ sql("SELECT k1, k2, MAX( k8 ) FROM
test_query_db.test_grouping_sets_rollup GROUP BY CUBE(k1, k2)")
+ contains "(idx1)"
+ }
+ sql "drop table if exists test_query_db.test_grouping_sets_rollup"
+ // test_grouping_select
+ test {
+ sql "select k1, if(grouping(k1)=1, count(k1), 0) from
test_query_db.test group by grouping sets((k1))"
+ exception "`k1` cannot both in select list and aggregate functions " +
+ "when using GROUPING SETS/CUBE/ROLLUP, please use union
instead."
+ }
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
