This is an automated email from the ASF dual-hosted git repository. kxiao pushed a commit to branch branch-2.0 in repository https://gitbox.apache.org/repos/asf/doris.git
commit 1d9571a9091c3d4ea3e678c29cbdfa5e8c77db98 Author: Pxl <[email protected]> AuthorDate: Mon Jul 17 10:08:25 2023 +0800 [Bug](materialized-view) fix some problems of mv and make ssb mv work on nereids (#21559) fix some problems of mv and make ssb mv work on nereids --- .../mv/AbstractSelectMaterializedIndexRule.java | 7 +- .../doris/nereids/util/TypeCoercionUtils.java | 26 ++- regression-test/data/mv_p0/k1s2m3/k1s2m3.out | 64 ++++++ .../ssb/multiple_no_where/multiple_no_where.out | 24 +++ regression-test/suites/mv_p0/k1s2m3/k1s2m3.groovy | 101 +++++++++ .../ssb/multiple_no_where/multiple_no_where.groovy | 226 +++++++++++++++++++++ .../sql_functions/test_arith_functions.groovy | 2 +- 7 files changed, 444 insertions(+), 6 deletions(-) diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/mv/AbstractSelectMaterializedIndexRule.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/mv/AbstractSelectMaterializedIndexRule.java index 1960dbabdc..bc77b8d058 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/mv/AbstractSelectMaterializedIndexRule.java +++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/mv/AbstractSelectMaterializedIndexRule.java @@ -71,6 +71,7 @@ import java.util.Map; import java.util.Optional; import java.util.Set; import java.util.TreeMap; +import java.util.TreeSet; import java.util.function.Function; import java.util.stream.Collectors; @@ -98,12 +99,12 @@ public abstract class AbstractSelectMaterializedIndexRule { OlapTable table = scan.getTable(); Set<String> requiredMvColumnNames = requiredScanOutput.stream() - .map(s -> normalizeName(Column.getNameWithoutMvPrefix(s.getName()))) - .collect(Collectors.toSet()); + .map(s -> normalizeName(Column.getNameWithoutMvPrefix(s.getName()))) + .collect(Collectors.toCollection(() -> new TreeSet<String>(String.CASE_INSENSITIVE_ORDER))); Set<String> mvColNames = table.getSchemaByIndexId(index.getId(), true).stream() .map(c -> normalizeName(parseMvColumnToSql(c.getNameWithoutMvPrefix()))) - .collect(Collectors.toSet()); + .collect(Collectors.toCollection(() -> new TreeSet<String>(String.CASE_INSENSITIVE_ORDER))); return mvColNames.containsAll(requiredMvColumnNames) || requiredExpr.stream() diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/util/TypeCoercionUtils.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/util/TypeCoercionUtils.java index 05de35a388..afdc19b2fe 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/nereids/util/TypeCoercionUtils.java +++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/util/TypeCoercionUtils.java @@ -114,6 +114,18 @@ import java.util.stream.Stream; */ public class TypeCoercionUtils { + /** + * integer type precedence for type promotion. + * bigger numeric has smaller ordinal + */ + public static final List<DataType> INTEGER_PRECEDENCE = ImmutableList.of( + LargeIntType.INSTANCE, + BigIntType.INSTANCE, + IntegerType.INSTANCE, + SmallIntType.INSTANCE, + TinyIntType.INSTANCE + ); + /** * numeric type precedence for type promotion. * bigger numeric has smaller ordinal @@ -486,8 +498,18 @@ public class TypeCoercionUtils { left = castIfNotSameType(left, t1); right = castIfNotSameType(right, t2); - Expression newLeft = TypeCoercionUtils.castIfNotSameType(left, BigIntType.INSTANCE); - Expression newRight = TypeCoercionUtils.castIfNotSameType(right, BigIntType.INSTANCE); + DataType commonType = BigIntType.INSTANCE; + if (t1.isIntegralType() && t2.isIntegralType()) { + for (DataType dataType : TypeCoercionUtils.INTEGER_PRECEDENCE) { + if (t1.equals(dataType) || t2.equals(dataType)) { + commonType = dataType; + break; + } + } + } + + Expression newLeft = TypeCoercionUtils.castIfNotSameType(left, commonType); + Expression newRight = TypeCoercionUtils.castIfNotSameType(right, commonType); return divide.withChildren(newLeft, newRight); } diff --git a/regression-test/data/mv_p0/k1s2m3/k1s2m3.out b/regression-test/data/mv_p0/k1s2m3/k1s2m3.out new file mode 100644 index 0000000000..1da9198a32 --- /dev/null +++ b/regression-test/data/mv_p0/k1s2m3/k1s2m3.out @@ -0,0 +1,64 @@ +-- This file is automatically generated. You should know what you did if you want to edit this +-- !select_star -- +\N 4 \N d +-4 -4 -4 d +1 1 1 a +2 2 2 b +3 -3 \N c + +-- !select_mv -- +\N \N +-4 16 +1 1 +2 4 +3 \N + +-- !select_mv -- +\N \N +-4 16 +1 1 +2 4 +3 \N + +-- !select_mv -- +\N \N +-4 16 +1 1 +2 4 +3 \N + +-- !select_mv -- +\N \N +-4 16 +1 1 +2 4 +3 \N + +-- !select_mv -- +\N \N +-4 16 +1 1 +2 4 +3 \N + +-- !select_mv -- +\N \N +-4 16 +1 1 +2 4 +3 \N + +-- !select_mv -- +\N \N +-4 16 +1 1 +2 4 +3 \N + +-- !select_mv -- +\N \N +-4 16 +1 1 +2 4 +3 \N + diff --git a/regression-test/data/mv_p0/ssb/multiple_no_where/multiple_no_where.out b/regression-test/data/mv_p0/ssb/multiple_no_where/multiple_no_where.out new file mode 100644 index 0000000000..dd404cf8ed --- /dev/null +++ b/regression-test/data/mv_p0/ssb/multiple_no_where/multiple_no_where.out @@ -0,0 +1,24 @@ +-- This file is automatically generated. You should know what you did if you want to edit this +-- !select_star -- +1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2023-06-09 shipmode name address city nation AMERICA phone mktsegment name address city nation AMERICA phone name MFGR#1 category brand color type 4 container +2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2023-06-09 shipmode name address city nation region phone mktsegment name address city nation region phone name mfgr category brand color type 4 container +19920101 1 1 1 1 1 1 1 1 1 1 100 1 1 1 2023-06-09 ASIA ASIA ASIA ASIA ASIA ASIA ASIA ASIA ASIA ASIA ASIA ASIA ASIA ASIA ASIA MFGR#12 MFGR#12 brand color type 4 container +19930101 1 1 1 1 1 1 1 1 1 1 100 1 1 1 2023-06-09 shipmode name address city nation AMERICA phone mktsegment name address city nation AMERICA phone name MFGR#1 category brand color type 4 container +19930101 1 1 1 1 1 1 1 1 1 1 100 1 1 1 2023-06-09 shipmode name address city nation AMERICA phone mktsegment name address city nation AMERICA phone name MFGR#12 MFGR#12 brand color type 4 container +19930101 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2023-06-09 shipmode name address city nation region phone mktsegment name address city nation region phone name mfgr category brand color type 4 container +19930101 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2023-06-09 shipmode name address city nation region phone mktsegment name address city nation region phone name mfgr category brand color type 4 container +19930101 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2023-06-09 shipmode name address city nation region phone mktsegment name address city nation region phone name mfgr category brand color type 4 container + +-- !select_q_1_1 -- +12 + +-- !select_q_2_1 -- +1 1993 brand + +-- !select_mv -- +ASIA ASIA 1992 1 + +-- !select_mv -- +0 nation 0 +1993 nation 0 + diff --git a/regression-test/suites/mv_p0/k1s2m3/k1s2m3.groovy b/regression-test/suites/mv_p0/k1s2m3/k1s2m3.groovy new file mode 100644 index 0000000000..9c732f5699 --- /dev/null +++ b/regression-test/suites/mv_p0/k1s2m3/k1s2m3.groovy @@ -0,0 +1,101 @@ +// 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.codehaus.groovy.runtime.IOGroovyMethods + +suite ("k1s2m3") { + + sql """ DROP TABLE IF EXISTS d_table; """ + + sql """ + create table d_table( + k1 int null, + k2 int not null, + k3 bigint null, + k4 varchar(100) null + ) + duplicate key (k1,k2,k3) + distributed BY hash(k1) buckets 3 + properties("replication_num" = "1"); + """ + + sql "insert into d_table select 1,1,1,'a';" + sql "insert into d_table select 2,2,2,'b';" + sql "insert into d_table select 3,-3,null,'c';" + + createMV("create materialized view k1s2m3 as select k1,sum(k2*k3) from d_table group by k1;") + + sql "insert into d_table select -4,-4,-4,'d';" + sql "insert into d_table(k4,k2) values('d',4);" + + qt_select_star "select * from d_table order by k1;" + + sql "set enable_nereids_planner=false" + explain { + sql("select k1,sum(k2*k3) from d_table group by k1 order by k1;") + contains "(k1s2m3)" + } + qt_select_mv "select k1,sum(k2*k3) from d_table group by k1 order by k1;" + + explain { + sql("select K1,sum(K2*K3) from d_table group by K1 order by K1;") + contains "(k1s2m3)" + } + qt_select_mv "select K1,sum(K2*K3) from d_table group by K1 order by K1;" + + sql "set enable_nereids_planner=true" + explain { + sql("select k1,sum(k2*k3) from d_table group by k1 order by k1;") + contains "(k1s2m3)" + } + qt_select_mv "select k1,sum(k2*k3) from d_table group by k1 order by k1;" + + explain { + sql("select K1,sum(K2*K3) from d_table group by K1 order by K1;") + contains "(k1s2m3)" + } + qt_select_mv "select K1,sum(K2*K3) from d_table group by K1 order by K1;" + + sql""" drop materialized view k1s2m3 on d_table; """ + createMV("create materialized view k1s2m3 as select K1,sum(K2*K3) from d_table group by K1;") + + sql "set enable_nereids_planner=false" + explain { + sql("select k1,sum(k2*k3) from d_table group by k1 order by k1;") + contains "(k1s2m3)" + } + qt_select_mv "select k1,sum(k2*k3) from d_table group by k1 order by k1;" + + explain { + sql("select K1,sum(K2*K3) from d_table group by K1 order by K1;") + contains "(k1s2m3)" + } + qt_select_mv "select K1,sum(K2*K3) from d_table group by K1 order by K1;" + + sql "set enable_nereids_planner=true" + explain { + sql("select k1,sum(k2*k3) from d_table group by k1 order by k1;") + contains "(k1s2m3)" + } + qt_select_mv "select k1,sum(k2*k3) from d_table group by k1 order by k1;" + + explain { + sql("select K1,sum(K2*K3) from d_table group by K1 order by K1;") + contains "(k1s2m3)" + } + qt_select_mv "select K1,sum(K2*K3) from d_table group by K1 order by K1;" +} diff --git a/regression-test/suites/mv_p0/ssb/multiple_no_where/multiple_no_where.groovy b/regression-test/suites/mv_p0/ssb/multiple_no_where/multiple_no_where.groovy new file mode 100644 index 0000000000..36f6fc634a --- /dev/null +++ b/regression-test/suites/mv_p0/ssb/multiple_no_where/multiple_no_where.groovy @@ -0,0 +1,226 @@ +// 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.codehaus.groovy.runtime.IOGroovyMethods + +suite ("multiple_no_where") { + sql """ DROP TABLE IF EXISTS lineorder_flat; """ + sql """set enable_nereids_planner=true""" + sql """SET enable_fallback_to_original_planner=false""" + + sql """ + CREATE TABLE IF NOT EXISTS `lineorder_flat` ( + `LO_ORDERDATE` int(11) NOT NULL COMMENT "", + `LO_ORDERKEY` int(11) NOT NULL COMMENT "", + `LO_LINENUMBER` tinyint(4) NOT NULL COMMENT "", + `LO_CUSTKEY` int(11) NOT NULL COMMENT "", + `LO_PARTKEY` int(11) NOT NULL COMMENT "", + `LO_SUPPKEY` int(11) NOT NULL COMMENT "", + `LO_ORDERPRIORITY` varchar(100) NOT NULL COMMENT "", + `LO_SHIPPRIORITY` tinyint(4) NOT NULL COMMENT "", + `LO_QUANTITY` tinyint(4) NOT NULL COMMENT "", + `LO_EXTENDEDPRICE` int(11) NOT NULL COMMENT "", + `LO_ORDTOTALPRICE` int(11) NOT NULL COMMENT "", + `LO_DISCOUNT` tinyint(4) NOT NULL COMMENT "", + `LO_REVENUE` int(11) NOT NULL COMMENT "", + `LO_SUPPLYCOST` int(11) NOT NULL COMMENT "", + `LO_TAX` tinyint(4) NOT NULL COMMENT "", + `LO_COMMITDATE` date NOT NULL COMMENT "", + `LO_SHIPMODE` varchar(100) NOT NULL COMMENT "", + `C_NAME` varchar(100) NOT NULL COMMENT "", + `C_ADDRESS` varchar(100) NOT NULL COMMENT "", + `C_CITY` varchar(100) NOT NULL COMMENT "", + `C_NATION` varchar(100) NOT NULL COMMENT "", + `C_REGION` varchar(100) NOT NULL COMMENT "", + `C_PHONE` varchar(100) NOT NULL COMMENT "", + `C_MKTSEGMENT` varchar(100) NOT NULL COMMENT "", + `S_NAME` varchar(100) NOT NULL COMMENT "", + `S_ADDRESS` varchar(100) NOT NULL COMMENT "", + `S_CITY` varchar(100) NOT NULL COMMENT "", + `S_NATION` varchar(100) NOT NULL COMMENT "", + `S_REGION` varchar(100) NOT NULL COMMENT "", + `S_PHONE` varchar(100) NOT NULL COMMENT "", + `P_NAME` varchar(100) NOT NULL COMMENT "", + `P_MFGR` varchar(100) NOT NULL COMMENT "", + `P_CATEGORY` varchar(100) NOT NULL COMMENT "", + `P_BRAND` varchar(100) NOT NULL COMMENT "", + `P_COLOR` varchar(100) NOT NULL COMMENT "", + `P_TYPE` varchar(100) NOT NULL COMMENT "", + `P_SIZE` tinyint(4) NOT NULL COMMENT "", + `P_CONTAINER` varchar(100) NOT NULL COMMENT "" + ) ENGINE=OLAP + DUPLICATE KEY(`LO_ORDERDATE`, `LO_ORDERKEY`) + COMMENT "OLAP" + PARTITION BY RANGE(`LO_ORDERDATE`) + (PARTITION p1992 VALUES [("-2147483648"), ("19930101")), + PARTITION p1993 VALUES [("19930101"), ("19940101")), + PARTITION p1994 VALUES [("19940101"), ("19950101")), + PARTITION p1995 VALUES [("19950101"), ("19960101")), + PARTITION p1996 VALUES [("19960101"), ("19970101")), + PARTITION p1997 VALUES [("19970101"), ("19980101")), + PARTITION p1998 VALUES [("19980101"), ("19990101"))) + DISTRIBUTED BY HASH(`LO_ORDERKEY`) BUCKETS 48 + PROPERTIES ( + "replication_num" = "1", + "colocate_with" = "groupxx1", + "in_memory" = "false", + "storage_format" = "DEFAULT" + ); + """ + + sql """INSERT INTO lineorder_flat (LO_ORDERDATE, LO_ORDERKEY, LO_LINENUMBER, LO_CUSTKEY, LO_PARTKEY, LO_SUPPKEY, LO_ORDERPRIORITY, LO_SHIPPRIORITY, LO_QUANTITY, LO_EXTENDEDPRICE, LO_ORDTOTALPRICE, LO_DISCOUNT, LO_REVENUE, LO_SUPPLYCOST, LO_TAX, LO_COMMITDATE, LO_SHIPMODE, C_NAME, C_ADDRESS, C_CITY, C_NATION, C_REGION, C_PHONE, C_MKTSEGMENT, S_NAME, S_ADDRESS, S_CITY, S_NATION, S_REGION, S_PHONE, P_NAME, P_MFGR, P_CATEGORY, P_BRAND, P_COLOR,P_TYPE,P_SIZE,P_CONTAINER) VALUES (19930101 [...] + + test { + sql """create materialized view lineorder_q_1_1 as + SELECT LO_ORDERKEY, SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) + FROM lineorder_flat GROUP BY + LO_ORDERKEY, LO_ORDERDATE, LO_DISCOUNT, LO_QUANTITY;""" + exception "not in select list" + } + + createMV ("""create materialized view lineorder_q_1_1 as + SELECT LO_ORDERKEY, LO_ORDERDATE, LO_DISCOUNT, LO_QUANTITY, SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) + FROM lineorder_flat GROUP BY + LO_ORDERKEY, LO_ORDERDATE, LO_DISCOUNT, LO_QUANTITY;""") + + createMV ("""create materialized view lineorder_q_2_1 as + SELECT + (LO_ORDERDATE DIV 10000) AS YEAR, + P_BRAND, P_CATEGORY, S_REGION, + SUM(LO_REVENUE) + FROM lineorder_flat + GROUP BY YEAR, P_BRAND, P_CATEGORY,S_REGION;""") + + createMV ("""create materialized view lineorder_q_3_1 as + SELECT + C_NATION, + S_NATION, (LO_ORDERDATE DIV 10000) AS YEAR, C_REGION, S_REGION, LO_ORDERDATE, + SUM(LO_REVENUE) AS revenue + FROM lineorder_flat + GROUP BY C_NATION, S_NATION, YEAR, C_REGION, S_REGION, LO_ORDERDATE;""") + + createMV ("""create materialized view lineorder_q_4_1 as + SELECT (LO_ORDERDATE DIV 10000) AS YEAR, + C_NATION,C_REGION,S_REGION,P_MFGR, + SUM(LO_REVENUE - LO_SUPPLYCOST) AS profit + FROM lineorder_flat + GROUP BY YEAR, C_NATION,C_REGION,S_REGION,P_MFGR;""") + + sql """INSERT INTO lineorder_flat (LO_ORDERDATE, LO_ORDERKEY, LO_LINENUMBER, LO_CUSTKEY, LO_PARTKEY, LO_SUPPKEY, LO_ORDERPRIORITY, LO_SHIPPRIORITY, LO_QUANTITY, LO_EXTENDEDPRICE, LO_ORDTOTALPRICE, LO_DISCOUNT, LO_REVENUE, LO_SUPPLYCOST, LO_TAX, LO_COMMITDATE, LO_SHIPMODE,C_NAME,C_ADDRESS,C_CITY,C_NATION,C_REGION,C_PHONE,C_MKTSEGMENT,S_NAME,S_ADDRESS,S_CITY,S_NATION,S_REGION,S_PHONE,P_NAME,P_MFGR,P_CATEGORY,P_BRAND,P_COLOR,P_TYPE,P_SIZE,P_CONTAINER) VALUES (19930101 , 2 , 2 , 2 , 2 , [...] + + sql """INSERT INTO lineorder_flat (LO_ORDERDATE, LO_ORDERKEY, LO_LINENUMBER, LO_CUSTKEY, LO_PARTKEY, LO_SUPPKEY, LO_ORDERPRIORITY, LO_SHIPPRIORITY, LO_QUANTITY, LO_EXTENDEDPRICE, LO_ORDTOTALPRICE, LO_DISCOUNT, LO_REVENUE, LO_SUPPLYCOST, LO_TAX, LO_COMMITDATE, LO_SHIPMODE, C_NAME, C_ADDRESS, C_CITY, C_NATION, C_REGION, C_PHONE, C_MKTSEGMENT, S_NAME, S_ADDRESS, S_CITY, S_NATION, S_REGION, S_PHONE, P_NAME, P_MFGR, P_CATEGORY, P_BRAND, P_COLOR,P_TYPE,P_SIZE,P_CONTAINER) VALUES (19930101 [...] + + sql """INSERT INTO lineorder_flat (LO_ORDERDATE, LO_ORDERKEY, LO_LINENUMBER, LO_CUSTKEY, LO_PARTKEY, LO_SUPPKEY, LO_ORDERPRIORITY, LO_SHIPPRIORITY, LO_QUANTITY, LO_EXTENDEDPRICE, LO_ORDTOTALPRICE, LO_DISCOUNT, LO_REVENUE, LO_SUPPLYCOST, LO_TAX, LO_COMMITDATE, LO_SHIPMODE,C_NAME,C_ADDRESS,C_CITY,C_NATION,C_REGION,C_PHONE,C_MKTSEGMENT,S_NAME,S_ADDRESS,S_CITY,S_NATION,S_REGION,S_PHONE,P_NAME,P_MFGR,P_CATEGORY,P_BRAND,P_COLOR,P_TYPE,P_SIZE,P_CONTAINER) VALUES (19930101 , 2 , 2 , 2 , 2 , [...] + + sql """INSERT INTO lineorder_flat (LO_ORDERDATE, LO_ORDERKEY, LO_LINENUMBER, LO_CUSTKEY, LO_PARTKEY, LO_SUPPKEY, LO_ORDERPRIORITY, LO_SHIPPRIORITY, LO_QUANTITY, LO_EXTENDEDPRICE, LO_ORDTOTALPRICE, LO_DISCOUNT, LO_REVENUE, LO_SUPPLYCOST, LO_TAX, LO_COMMITDATE, LO_SHIPMODE, C_NAME, C_ADDRESS, C_CITY, C_NATION, C_REGION, C_PHONE, C_MKTSEGMENT, S_NAME, S_ADDRESS, S_CITY, S_NATION, S_REGION, S_PHONE, P_NAME, P_MFGR, P_CATEGORY, P_BRAND, P_COLOR,P_TYPE,P_SIZE,P_CONTAINER) VALUES (19920101 [...] + + sql """INSERT INTO lineorder_flat (LO_ORDERDATE, LO_ORDERKEY, LO_LINENUMBER, LO_CUSTKEY, LO_PARTKEY, LO_SUPPKEY, LO_ORDERPRIORITY, LO_SHIPPRIORITY, LO_QUANTITY, LO_EXTENDEDPRICE, LO_ORDTOTALPRICE, LO_DISCOUNT, LO_REVENUE, LO_SUPPLYCOST, LO_TAX, LO_COMMITDATE, LO_SHIPMODE,C_NAME,C_ADDRESS,C_CITY,C_NATION,C_REGION,C_PHONE,C_MKTSEGMENT,S_NAME,S_ADDRESS,S_CITY,S_NATION,S_REGION,S_PHONE,P_NAME,P_MFGR,P_CATEGORY,P_BRAND,P_COLOR,P_TYPE,P_SIZE,P_CONTAINER) VALUES (19930101 , 2 , 2 , 2 , 2 , [...] + + sql """INSERT INTO lineorder_flat (LO_ORDERDATE, LO_ORDERKEY, LO_LINENUMBER, LO_CUSTKEY, LO_PARTKEY, LO_SUPPKEY, LO_ORDERPRIORITY, LO_SHIPPRIORITY, LO_QUANTITY, LO_EXTENDEDPRICE, LO_ORDTOTALPRICE, LO_DISCOUNT, LO_REVENUE, LO_SUPPLYCOST, LO_TAX, LO_COMMITDATE, LO_SHIPMODE,C_NAME,C_ADDRESS,C_CITY,C_NATION,C_REGION,C_PHONE,C_MKTSEGMENT,S_NAME,S_ADDRESS,S_CITY,S_NATION,S_REGION,S_PHONE,P_NAME,P_MFGR,P_CATEGORY,P_BRAND,P_COLOR,P_TYPE,P_SIZE,P_CONTAINER) VALUES (2 , 2 , 2 , 2 , 2 , 2 ,'2', [...] + + sql """INSERT INTO lineorder_flat (LO_ORDERDATE, LO_ORDERKEY, LO_LINENUMBER, LO_CUSTKEY, LO_PARTKEY, LO_SUPPKEY, LO_ORDERPRIORITY, LO_SHIPPRIORITY, LO_QUANTITY, LO_EXTENDEDPRICE, LO_ORDTOTALPRICE, LO_DISCOUNT, LO_REVENUE, LO_SUPPLYCOST, LO_TAX, LO_COMMITDATE, LO_SHIPMODE, C_NAME, C_ADDRESS, C_CITY, C_NATION, C_REGION, C_PHONE, C_MKTSEGMENT, S_NAME, S_ADDRESS, S_CITY, S_NATION, S_REGION, S_PHONE, P_NAME, P_MFGR, P_CATEGORY, P_BRAND, P_COLOR,P_TYPE,P_SIZE,P_CONTAINER) VALUES (1 , 1 , 1 [...] + + qt_select_star "select * from lineorder_flat order by 1,2;" + + explain { + sql("""SELECT SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue + FROM lineorder_flat + WHERE + LO_ORDERDATE >= 19930101 + AND LO_ORDERDATE <= 19931231 + AND LO_DISCOUNT >= 1 AND LO_DISCOUNT <= 3 + AND LO_QUANTITY < 25;""") + contains "(lineorder_q_1_1)" + } + qt_select_q_1_1 """SELECT SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue + FROM lineorder_flat + WHERE + LO_ORDERDATE >= 19930101 + AND LO_ORDERDATE <= 19931231 + AND LO_DISCOUNT >= 1 AND LO_DISCOUNT <= 3 + AND LO_QUANTITY < 25;""" + + explain { + sql("""SELECT + SUM(LO_REVENUE), (LO_ORDERDATE DIV 10000) AS YEAR, + P_BRAND + FROM lineorder_flat + WHERE P_CATEGORY = 'MFGR#12' AND S_REGION = 'AMERICA' + GROUP BY (LO_ORDERDATE DIV 10000), P_BRAND + ORDER BY YEAR, P_BRAND;""") + contains "(lineorder_q_2_1)" + } + qt_select_q_2_1 """SELECT + SUM(LO_REVENUE), (LO_ORDERDATE DIV 10000) AS YEAR, + P_BRAND + FROM lineorder_flat + WHERE P_CATEGORY = 'MFGR#12' AND S_REGION = 'AMERICA' + GROUP BY YEAR, P_BRAND + ORDER BY YEAR, P_BRAND;""" + + explain { + sql("""SELECT + C_NATION, + S_NATION, (LO_ORDERDATE DIV 10000) AS YEAR, + SUM(LO_REVENUE) AS revenue + FROM lineorder_flat + WHERE + C_REGION = 'ASIA' + AND S_REGION = 'ASIA' + AND LO_ORDERDATE >= 19920101 + AND LO_ORDERDATE <= 19971231 + GROUP BY C_NATION, S_NATION, YEAR + ORDER BY YEAR ASC, revenue DESC;""") + contains "(lineorder_q_3_1)" + } + qt_select_mv """SELECT + C_NATION, + S_NATION, (LO_ORDERDATE DIV 10000) AS YEAR, + SUM(LO_REVENUE) AS revenue + FROM lineorder_flat + WHERE + C_REGION = 'ASIA' + AND S_REGION = 'ASIA' + AND LO_ORDERDATE >= 19920101 + AND LO_ORDERDATE <= 19971231 + GROUP BY C_NATION, S_NATION, YEAR + ORDER BY YEAR ASC, revenue DESC;""" + + explain { + sql("""SELECT (LO_ORDERDATE DIV 10000) AS YEAR, + C_NATION, + SUM(LO_REVENUE - LO_SUPPLYCOST) AS profit + FROM lineorder_flat + WHERE + C_REGION = 'AMERICA' + AND S_REGION = 'AMERICA' + AND P_MFGR IN ('MFGR#1', 'MFGR#2') + GROUP BY YEAR, C_NATION + ORDER BY YEAR ASC, C_NATION ASC;""") + contains "(lineorder_q_4_1)" + } + qt_select_mv """SELECT (LO_ORDERDATE DIV 10000) AS YEAR, + C_NATION, + SUM(LO_REVENUE - LO_SUPPLYCOST) AS profit + FROM lineorder_flat + WHERE + C_REGION = 'AMERICA' + AND S_REGION = 'AMERICA' + AND P_MFGR IN ('MFGR#1', 'MFGR#2') + GROUP BY YEAR, C_NATION + ORDER BY YEAR ASC, C_NATION ASC;""" +} diff --git a/regression-test/suites/nereids_p0/sql_functions/test_arith_functions.groovy b/regression-test/suites/nereids_p0/sql_functions/test_arith_functions.groovy index 5294a0e58e..2778d0f359 100644 --- a/regression-test/suites/nereids_p0/sql_functions/test_arith_functions.groovy +++ b/regression-test/suites/nereids_p0/sql_functions/test_arith_functions.groovy @@ -26,7 +26,7 @@ suite("test_arith_functions") { } test { sql 'select int_divide(1, 1), bitand(1, 1), bitor(2, 2), bitxor(3.0, 2.0), bitnot(3.0)' - result([[1L, 1, 2, 1L, -4L]]) + result([[1, 1, 2, 1L, -4L]]) } test { sql 'select add(k1, k2) + subtract(k2, k3) + multiply(k3, k4), cast(divide(k4, k3) + mod(k4, k3) as bigint) from test order by k1 limit 1' --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
