You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by mo...@apache.org on 2023/01/06 08:29:57 UTC
[doris] branch master updated: [test](Nereids) add two regression test cases for Nereids (#15598)
This is an automated email from the ASF dual-hosted git repository.
morrysnow 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 cad47dd9d9 [test](Nereids) add two regression test cases for Nereids (#15598)
cad47dd9d9 is described below
commit cad47dd9d94bf694ba6d75ed5f857f1f07893984
Author: yongkang.zhong <zh...@qq.com>
AuthorDate: Fri Jan 6 16:29:50 2023 +0800
[test](Nereids) add two regression test cases for Nereids (#15598)
1. test predicates infer could work well with push down predicates through join
2. test count with subquery containing constant literal
---
.../sub_query_count_with_const.out | 4 +
.../sub_query_join_where_pushdown.groovy | 113 +++++++++++++++++++++
.../sub_query_count_with_const.groovy | 53 ++++++++++
3 files changed, 170 insertions(+)
diff --git a/regression-test/data/nereids_syntax_p0/sub_query_count_with_const.out b/regression-test/data/nereids_syntax_p0/sub_query_count_with_const.out
new file mode 100644
index 0000000000..72d126351a
--- /dev/null
+++ b/regression-test/data/nereids_syntax_p0/sub_query_count_with_const.out
@@ -0,0 +1,4 @@
+-- This file is automatically generated. You should know what you did if you want to edit this
+-- !select --
+1
+
diff --git a/regression-test/suites/nereids_performance_p0/sub_query_join_where_pushdown.groovy b/regression-test/suites/nereids_performance_p0/sub_query_join_where_pushdown.groovy
new file mode 100644
index 0000000000..7678bdd000
--- /dev/null
+++ b/regression-test/suites/nereids_performance_p0/sub_query_join_where_pushdown.groovy
@@ -0,0 +1,113 @@
+// 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.
+
+suite("sub_query_join_where_pushdown") {
+ sql "SET enable_vectorized_engine=true"
+ sql "SET enable_nereids_planner=true"
+ sql "SET enable_fallback_to_original_planner=false"
+
+ sql """
+ DROP TABLE IF EXISTS sub_query_join_where_pushdown1
+ """
+
+ sql """
+ DROP TABLE IF EXISTS sub_query_join_where_pushdown2
+ """
+
+ sql """
+ DROP TABLE IF EXISTS sub_query_join_where_pushdown3
+ """
+
+ sql """CREATE TABLE `sub_query_join_where_pushdown1` (
+ `id` int NULL,
+ `day` date NULL,
+ `hour` int NULL,
+ `code` string NULL
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`id`)
+ COMMENT 'OLAP'
+ DISTRIBUTED BY HASH(`id`) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );"""
+
+ sql """CREATE TABLE `sub_query_join_where_pushdown2` (
+ `day` date NULL,
+ `hour` int NULL
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`day`)
+ COMMENT 'OLAP'
+ DISTRIBUTED BY HASH(`day`) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );"""
+
+ sql """CREATE TABLE `sub_query_join_where_pushdown3` (
+ `id` int NULL,
+ `day` date NULL,
+ `hour` int NULL,
+ `code` string NULL
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`id`)
+ COMMENT 'OLAP'
+ DISTRIBUTED BY HASH(`id`) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );"""
+
+ sql """insert into sub_query_join_where_pushdown1 values (1,'2023-01-01',1,'big'),(2,'2023-01-01',2,'big');"""
+ sql """insert into sub_query_join_where_pushdown2 values ('2023-01-01',3),('2023-01-01',4);"""
+ sql """insert into sub_query_join_where_pushdown3 values (1,'2023-01-01',1,'big'),(2,'2023-01-01',2,'big');"""
+
+ explain {
+ sql ("""with tmp as
+ (
+ select a.day,a.code,a.hour,count(*)
+ from
+ (
+ select day,code,hour,count(*)
+ from sub_query_join_where_pushdown1
+ group by 1,2,3
+ ) a
+ right join (select day,hour from sub_query_join_where_pushdown2) b
+ on b.hour>=a.hour and b.day=a.day
+ where a.day is not null
+ group by 1,2,3
+ )
+ select * from tmp where code = 'big' and day = '2023-01-01';""")
+ contains "PREDICATES: code[#5] = 'big', day[#3] = '2023-01-01', day[#3] IS NOT NULL"
+ contains "PREDICATES: day[#0] = '2023-01-01'"
+ }
+
+ explain {
+ sql ("""select dd.*
+ from
+ (select day,hour,code,count(*)
+ from sub_query_join_where_pushdown1
+ group by 1,2,3
+ ) final
+ JOIN
+ ( select day,hour,code,count(*)
+ from sub_query_join_where_pushdown3
+ group by 1,2,3
+ ) dd
+ on dd.hour=final.`hour` and dd.`day`=final.`day` and dd.code=final.code
+ where dd.code = 'big';""")
+ contains "PREDICATES: code[#14] = 'big'"
+ contains "PREDICATES: code[#3] = 'big'"
+ }
+}
\ No newline at end of file
diff --git a/regression-test/suites/nereids_syntax_p0/sub_query_count_with_const.groovy b/regression-test/suites/nereids_syntax_p0/sub_query_count_with_const.groovy
new file mode 100644
index 0000000000..9ed1624ea7
--- /dev/null
+++ b/regression-test/suites/nereids_syntax_p0/sub_query_count_with_const.groovy
@@ -0,0 +1,53 @@
+// 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.
+
+suite("sub_query_count_with_const") {
+ sql "SET enable_vectorized_engine=true"
+ sql "SET enable_nereids_planner=true"
+ sql "SET enable_fallback_to_original_planner=false"
+
+ sql """
+ DROP TABLE IF EXISTS sub_query_count_with_const
+ """
+
+ sql """CREATE TABLE `sub_query_count_with_const` (
+ `id` int(11) NULL
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`id`)
+ COMMENT 'OLAP'
+ DISTRIBUTED BY HASH(`id`) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );"""
+
+ sql """insert into sub_query_count_with_const values(1),(2),(3);"""
+
+ qt_select """select count(1) as count
+ from (
+ select 2022 as dt ,sum(id)
+ from sub_query_count_with_const
+ ) tmp;"""
+
+ explain {
+ sql ("""select count(1) as count
+ from (
+ select 2022 as dt ,sum(id)
+ from sub_query_count_with_const
+ ) tmp;""")
+ contains "output: sum(id[#0])[#1]"
+ }
+}
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org