This is an automated email from the ASF dual-hosted git repository.
panxiaolei 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 86841d8653 [Bug](materialized-view) fix some problems of mv and make
ssb mv work on nereids (#21559)
86841d8653 is described below
commit 86841d86532978184433a267e0b386dcb5ff5642
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]
