You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by yi...@apache.org on 2023/12/08 17:36:54 UTC

(doris) branch master updated: [Feature](materialized-view) support count(1) on materialized view (#28135)

This is an automated email from the ASF dual-hosted git repository.

yiguolei 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 027b06059a7 [Feature](materialized-view) support count(1) on materialized view (#28135)
027b06059a7 is described below

commit 027b06059a79f1c45e4ba58362cdd709a15c3c16
Author: Pxl <px...@qq.com>
AuthorDate: Sat Dec 9 01:36:46 2023 +0800

    [Feature](materialized-view) support count(1) on materialized view (#28135)
    
    support count(1) on materialized view
    fix match failed like select k1, sum(k1) from t group by k1
---
 be/src/olap/schema_change.cpp                      |  30 ++-----
 be/src/vec/data_types/data_type_decimal.h          |  21 +++--
 .../doris/analysis/CreateMaterializedViewStmt.java |  15 ++--
 .../mv/SelectMaterializedIndexWithAggregate.java   | 100 +++++++++++----------
 .../org/apache/doris/task/AlterReplicaTask.java    |   1 -
 .../data/mv_p0/ssb/multiple_ssb/multiple_ssb.out   |  10 ++-
 .../test_agg_keys_schema_change_decimalv3.out      |  13 +--
 .../mv_p0/ssb/multiple_ssb/multiple_ssb.groovy     |  19 +++-
 .../test_agg_keys_schema_change_decimalv3.groovy   |  16 ++--
 9 files changed, 115 insertions(+), 110 deletions(-)

diff --git a/be/src/olap/schema_change.cpp b/be/src/olap/schema_change.cpp
index a11a7a2af99..e910eaffa89 100644
--- a/be/src/olap/schema_change.cpp
+++ b/be/src/olap/schema_change.cpp
@@ -903,13 +903,6 @@ Status SchemaChangeHandler::_do_process_alter_tablet_v2(const TAlterTabletReqV2&
             for (auto item : request.materialized_view_params) {
                 AlterMaterializedViewParam mv_param;
                 mv_param.column_name = item.column_name;
-                /*
-                 * origin_column_name is always be set now,
-                 * but origin_column_name may be not set in some materialized view function. eg:count(1)
-                */
-                if (item.__isset.origin_column_name) {
-                    mv_param.origin_column_name = item.origin_column_name;
-                }
 
                 if (item.__isset.mv_expr) {
                     mv_param.expr = std::make_shared<TExpr>(item.mv_expr);
@@ -1206,9 +1199,7 @@ Status SchemaChangeHandler::_parse_request(const SchemaChangeParams& sc_params,
         if (materialized_function_map.find(column_name_lower) != materialized_function_map.end()) {
             auto mv_param = materialized_function_map.find(column_name_lower)->second;
             column_mapping->expr = mv_param.expr;
-            int32_t column_index = base_tablet_schema->field_index(mv_param.origin_column_name);
-            column_mapping->ref_column = column_index;
-            if (column_index >= 0 || column_mapping->expr != nullptr) {
+            if (column_mapping->expr != nullptr) {
                 continue;
             }
         }
@@ -1264,7 +1255,7 @@ Status SchemaChangeHandler::_parse_request(const SchemaChangeParams& sc_params,
     for (int i = 0, new_schema_size = new_tablet->num_key_columns(); i < new_schema_size; ++i) {
         ColumnMapping* column_mapping = changer->get_mutable_column_mapping(i);
 
-        if (column_mapping->ref_column < 0) {
+        if (column_mapping->expr == nullptr) {
             num_default_value++;
             continue;
         }
@@ -1312,22 +1303,11 @@ Status SchemaChangeHandler::_parse_request(const SchemaChangeParams& sc_params,
 
     for (size_t i = 0; i < new_tablet->num_columns(); ++i) {
         ColumnMapping* column_mapping = changer->get_mutable_column_mapping(i);
-        if (column_mapping->ref_column < 0) {
+        if (column_mapping->expr == nullptr) {
             continue;
         } else {
-            auto column_new = new_tablet_schema->column(i);
-            auto column_old = base_tablet_schema->column(column_mapping->ref_column);
-            if (column_new.type() != column_old.type() ||
-                column_new.precision() != column_old.precision() ||
-                column_new.frac() != column_old.frac() ||
-                column_new.length() != column_old.length() ||
-                column_new.is_bf_column() != column_old.is_bf_column() ||
-                column_new.has_bitmap_index() != column_old.has_bitmap_index() ||
-                new_tablet_schema->has_inverted_index(column_new) !=
-                        base_tablet_schema->has_inverted_index(column_old)) {
-                *sc_directly = true;
-                return Status::OK();
-            }
+            *sc_directly = true;
+            return Status::OK();
         }
     }
 
diff --git a/be/src/vec/data_types/data_type_decimal.h b/be/src/vec/data_types/data_type_decimal.h
index 74be8f59921..fb89c68ec61 100644
--- a/be/src/vec/data_types/data_type_decimal.h
+++ b/be/src/vec/data_types/data_type_decimal.h
@@ -514,7 +514,9 @@ void convert_decimal_cols(
                             } else {
                                 if (UNLIKELY(res > max_result.value || res < -max_result.value)) {
                                     throw Exception(ErrorCode::ARITHMETIC_OVERFLOW_ERRROR,
-                                                    "Arithmetic overflow");
+                                                    "Arithmetic overflow, convert failed from {}, "
+                                                    "expected data is [{}, {}]",
+                                                    res, -max_result.value, max_result.value);
                                 } else {
                                     vec_to[i] = ToFieldType(res);
                                 }
@@ -524,7 +526,9 @@ void convert_decimal_cols(
                             if constexpr (narrow_integral) {
                                 if (UNLIKELY(res > max_result.value || res < -max_result.value)) {
                                     throw Exception(ErrorCode::ARITHMETIC_OVERFLOW_ERRROR,
-                                                    "Arithmetic overflow");
+                                                    "Arithmetic overflow, convert failed from {}, "
+                                                    "expected data is [{}, {}]",
+                                                    res, -max_result.value, max_result.value);
                                 }
                             }
                             vec_to[i] = ToFieldType(res);
@@ -540,7 +544,10 @@ void convert_decimal_cols(
                             if (UNLIKELY(vec_from[i].value > max_result.value ||
                                          vec_from[i].value < -max_result.value)) {
                                 throw Exception(ErrorCode::ARITHMETIC_OVERFLOW_ERRROR,
-                                                "Arithmetic overflow");
+                                                "Arithmetic overflow, convert failed from {}, "
+                                                "expected data is [{}, {}]",
+                                                vec_from[i].value, -max_result.value,
+                                                max_result.value);
                             }
                         }
                         vec_to[i] = ToFieldType(vec_from[i].value);
@@ -559,7 +566,9 @@ void convert_decimal_cols(
                                 res = (vec_from[i].value + multiplier / 2) / multiplier;
                                 if (UNLIKELY(res > max_result.value)) {
                                     throw Exception(ErrorCode::ARITHMETIC_OVERFLOW_ERRROR,
-                                                    "Arithmetic overflow");
+                                                    "Arithmetic overflow, convert failed from {}, "
+                                                    "expected data is [{}, {}]",
+                                                    res, -max_result.value, max_result.value);
                                 }
                                 vec_to[i] = ToFieldType(res);
                             } else {
@@ -571,7 +580,9 @@ void convert_decimal_cols(
                                 res = (vec_from[i].value - multiplier / 2) / multiplier;
                                 if (UNLIKELY(res < -max_result.value)) {
                                     throw Exception(ErrorCode::ARITHMETIC_OVERFLOW_ERRROR,
-                                                    "Arithmetic overflow");
+                                                    "Arithmetic overflow, convert failed from {}, "
+                                                    "expected data is [{}, {}]",
+                                                    res, -max_result.value, max_result.value);
                                 }
                                 vec_to[i] = ToFieldType(res);
                             } else {
diff --git a/fe/fe-core/src/main/java/org/apache/doris/analysis/CreateMaterializedViewStmt.java b/fe/fe-core/src/main/java/org/apache/doris/analysis/CreateMaterializedViewStmt.java
index a248d1404b5..f40033ed3cd 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/analysis/CreateMaterializedViewStmt.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/CreateMaterializedViewStmt.java
@@ -258,14 +258,6 @@ public class CreateMaterializedViewStmt extends DdlStmt {
                 throw new AnalysisException("The materialized view only support the single column or function expr. "
                         + "Error column: " + selectListItemExpr.toSql());
             }
-            List<SlotRef> slots = new ArrayList<>();
-            selectListItemExpr.collect(SlotRef.class, slots);
-            if (!isReplay && slots.size() == 0) {
-                throw new AnalysisException(
-                        "The materialized view contain constant expr is disallowed, expr: "
-                                + selectListItemExpr.toSql());
-            }
-
 
             if (selectListItemExpr instanceof FunctionCallExpr
                     && ((FunctionCallExpr) selectListItemExpr).isAggregateFunction()) {
@@ -278,6 +270,13 @@ public class CreateMaterializedViewStmt extends DdlStmt {
                 // build mv column item
                 mvColumnItemList.add(buildMVColumnItem(analyzer, functionCallExpr));
             } else {
+                List<SlotRef> slots = new ArrayList<>();
+                selectListItemExpr.collect(SlotRef.class, slots);
+                if (!isReplay && slots.size() == 0) {
+                    throw new AnalysisException(
+                            "The materialized view contain constant expr is disallowed, expr: "
+                                    + selectListItemExpr.toSql());
+                }
                 if (meetAggregate) {
                     throw new AnalysisException("The aggregate column should be after the single column");
                 }
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/mv/SelectMaterializedIndexWithAggregate.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/mv/SelectMaterializedIndexWithAggregate.java
index 67dd0e68a33..2cf0797a311 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/mv/SelectMaterializedIndexWithAggregate.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/mv/SelectMaterializedIndexWithAggregate.java
@@ -55,6 +55,7 @@ import org.apache.doris.nereids.trees.expressions.functions.scalar.BitmapHash;
 import org.apache.doris.nereids.trees.expressions.functions.scalar.HllHash;
 import org.apache.doris.nereids.trees.expressions.functions.scalar.ToBitmap;
 import org.apache.doris.nereids.trees.expressions.functions.scalar.ToBitmapWithCheck;
+import org.apache.doris.nereids.trees.expressions.literal.TinyIntLiteral;
 import org.apache.doris.nereids.trees.expressions.visitor.DefaultExpressionRewriter;
 import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
 import org.apache.doris.nereids.trees.plans.Plan;
@@ -1072,24 +1073,26 @@ public class SelectMaterializedIndexWithAggregate extends AbstractSelectMaterial
 
         // has rewritten agg functions
         Map<Slot, Slot> slotMap = exprRewriteMap.slotMap;
-        if (!slotMap.isEmpty()) {
-            // Note that the slots in the rewritten agg functions shouldn't appear in filters or grouping expressions.
-            // For example: we have a duplicated-type table t(c1, c2) and a materialized index that has
-            // a bitmap_union column `mv_bitmap_union_c2` for the column c2.
-            // The query `select c1, count(distinct c2) from t where c2 > 0 group by c1` can't use the materialized
-            // index because we have a filter `c2 > 0` for the aggregated column c2.
-            Set<Slot> slotsToReplace = slotMap.keySet();
-            Set<String> indexConjuncts = PlanNode
+        // Note that the slots in the rewritten agg functions shouldn't appear in filters or grouping expressions.
+        // For example: we have a duplicated-type table t(c1, c2) and a materialized index that has
+        // a bitmap_union column `mv_bitmap_union_c2` for the column c2.
+        // The query `select c1, count(distinct c2) from t where c2 > 0 group by c1` can't use the materialized
+        // index because we have a filter `c2 > 0` for the aggregated column c2.
+        Set<Slot> slotsToReplace = slotMap.keySet();
+        Set<String> indexConjuncts;
+        try {
+            indexConjuncts = PlanNode
                     .splitAndCompoundPredicateToConjuncts(context.checkContext.getMeta().getWhereClause()).stream()
                     .map(e -> new NereidsParser().parseExpression(e.toSql()).toSql()).collect(Collectors.toSet());
-            if (isInputSlotsContainsNone(
-                    predicates.stream().filter(e -> !indexConjuncts.contains(e.toSql())).collect(Collectors.toList()),
-                    slotsToReplace) && isInputSlotsContainsNone(groupingExprs, slotsToReplace)) {
-                ImmutableSet<Slot> newRequiredSlots = requiredScanOutput.stream()
-                        .map(slot -> (Slot) ExpressionUtils.replace(slot, slotMap))
-                        .collect(ImmutableSet.toImmutableSet());
-                return new AggRewriteResult(index, true, newRequiredSlots, exprRewriteMap);
-            }
+        } catch (Exception e) {
+            return new AggRewriteResult(index, false, null, null);
+        }
+        if (isInputSlotsContainsNone(
+                predicates.stream().filter(e -> !indexConjuncts.contains(e.toSql())).collect(Collectors.toList()),
+                slotsToReplace) && isInputSlotsContainsNone(groupingExprs, slotsToReplace)) {
+            ImmutableSet<Slot> newRequiredSlots = requiredScanOutput.stream()
+                    .map(slot -> (Slot) ExpressionUtils.replace(slot, slotMap)).collect(ImmutableSet.toImmutableSet());
+            return new AggRewriteResult(index, true, newRequiredSlots, exprRewriteMap);
         }
 
         return new AggRewriteResult(index, false, null, null);
@@ -1207,8 +1210,7 @@ public class SelectMaterializedIndexWithAggregate extends AbstractSelectMaterial
 
                 Expression expr = new ToBitmapWithCheck(castIfNeed(count.child(0), BigIntType.INSTANCE));
                 // count distinct a value column.
-                if (slotOpt.isPresent() && !context.checkContext.keyNameToColumn.containsKey(
-                        normalizeName(expr.toSql()))) {
+                if (slotOpt.isPresent()) {
                     String bitmapUnionColumn = normalizeName(CreateMaterializedViewStmt.mvColumnBuilder(
                             AggregateType.BITMAP_UNION, CreateMaterializedViewStmt.mvColumnBuilder(expr.toSql())));
 
@@ -1229,33 +1231,37 @@ public class SelectMaterializedIndexWithAggregate extends AbstractSelectMaterial
                         return bitmapUnionCount;
                     }
                 }
-            } else if (!count.isDistinct() && count.arity() == 1) {
-                // count(col) -> sum(mva_SUM__CASE WHEN col IS NULL THEN 0 ELSE 1 END)
+            }
 
+            Expression child = null;
+            if (!count.isDistinct() && count.arity() == 1) {
+                // count(col) -> sum(mva_SUM__CASE WHEN col IS NULL THEN 0 ELSE 1 END)
                 Optional<Slot> slotOpt = ExpressionUtils.extractSlotOrCastOnSlot(count.child(0));
-                // count a value column.
-                if (slotOpt.isPresent() && !context.checkContext.keyNameToColumn.containsKey(
-                        normalizeName(slotOpt.get().toSql()))) {
-                    String countColumn = normalizeName(CreateMaterializedViewStmt
-                            .mvColumnBuilder(AggregateType.SUM,
-                                CreateMaterializedViewStmt.mvColumnBuilder(slotToCaseWhen(slotOpt.get()).toSql())));
-
-                    Column mvColumn = context.checkContext.getColumn(countColumn);
-                    // has bitmap_union_count column
-                    if (mvColumn != null && context.checkContext.valueNameToColumn.containsValue(mvColumn)) {
-                        Slot countSlot = context.checkContext.scan.getOutputByIndex(context.checkContext.index)
-                                .stream()
-                                .filter(s -> countColumn.equalsIgnoreCase(normalizeName(s.getName())))
-                                .findFirst()
-                                .orElseThrow(() -> new AnalysisException(
-                                        "cannot find count slot when select mv"));
+                if (slotOpt.isPresent()) {
+                    child = slotOpt.get();
+                }
+            } else if (count.arity() == 0) {
+                // count(*) / count(1) -> sum(mva_SUM__CASE WHEN 1 IS NULL THEN 0 ELSE 1 END)
+                child = new TinyIntLiteral((byte) 1);
+            }
 
-                        context.exprRewriteMap.slotMap.put(slotOpt.get(), countSlot);
-                        context.exprRewriteMap.projectExprMap.put(slotOpt.get(), countSlot);
-                        Sum sum = new Sum(countSlot);
-                        context.exprRewriteMap.aggFuncMap.put(count, sum);
-                        return sum;
+            if (child != null) {
+                String countColumn = normalizeName(CreateMaterializedViewStmt.mvColumnBuilder(AggregateType.SUM,
+                        CreateMaterializedViewStmt.mvColumnBuilder(slotToCaseWhen(child).toSql())));
+
+                Column mvColumn = context.checkContext.getColumn(countColumn);
+                if (mvColumn != null && context.checkContext.valueNameToColumn.containsValue(mvColumn)) {
+                    Slot countSlot = context.checkContext.scan.getOutputByIndex(context.checkContext.index).stream()
+                            .filter(s -> countColumn.equalsIgnoreCase(normalizeName(s.getName()))).findFirst()
+                            .orElseThrow(() -> new AnalysisException("cannot find count slot when select mv"));
+
+                    if (child instanceof Slot) {
+                        context.exprRewriteMap.slotMap.put((Slot) child, countSlot);
                     }
+                    context.exprRewriteMap.projectExprMap.put(child, countSlot);
+                    Sum sum = new Sum(countSlot);
+                    context.exprRewriteMap.aggFuncMap.put(count, sum);
+                    return sum;
                 }
             }
             return count;
@@ -1416,8 +1422,7 @@ public class SelectMaterializedIndexWithAggregate extends AbstractSelectMaterial
             }
             Optional<Slot> slotOpt = ExpressionUtils.extractSlotOrCastOnSlot(ndv.child(0));
             // ndv on a value column.
-            if (slotOpt.isPresent() && !context.checkContext.keyNameToColumn.containsKey(
-                    normalizeName(slotOpt.get().toSql()))) {
+            if (slotOpt.isPresent()) {
                 Expression expr = castIfNeed(ndv.child(), VarcharType.SYSTEM_DEFAULT);
                 String hllUnionColumn = normalizeName(
                         CreateMaterializedViewStmt.mvColumnBuilder(AggregateType.HLL_UNION,
@@ -1450,8 +1455,7 @@ public class SelectMaterializedIndexWithAggregate extends AbstractSelectMaterial
                 return result;
             }
             Optional<Slot> slotOpt = ExpressionUtils.extractSlotOrCastOnSlot(sum.child(0));
-            if (!sum.isDistinct() && slotOpt.isPresent()
-                    && !context.checkContext.keyNameToColumn.containsKey(normalizeName(slotOpt.get().toSql()))) {
+            if (!sum.isDistinct() && slotOpt.isPresent()) {
                 Expression expr = castIfNeed(sum.child(), BigIntType.INSTANCE);
                 String sumColumn = normalizeName(CreateMaterializedViewStmt.mvColumnBuilder(AggregateType.SUM,
                         CreateMaterializedViewStmt.mvColumnBuilder(expr.toSql())));
@@ -1487,10 +1491,8 @@ public class SelectMaterializedIndexWithAggregate extends AbstractSelectMaterial
 
                 Set<Slot> slots = aggregateFunction.collect(SlotReference.class::isInstance);
                 for (Slot slot : slots) {
-                    if (!context.checkContext.keyNameToColumn.containsKey(normalizeName(slot.toSql()))) {
-                        context.exprRewriteMap.slotMap.put(slot, aggStateSlot);
-                        context.exprRewriteMap.projectExprMap.put(slot, aggStateSlot);
-                    }
+                    context.exprRewriteMap.slotMap.put(slot, aggStateSlot);
+                    context.exprRewriteMap.projectExprMap.put(slot, aggStateSlot);
                 }
 
                 MergeCombinator mergeCombinator = new MergeCombinator(Arrays.asList(aggStateSlot), aggregateFunction);
diff --git a/fe/fe-core/src/main/java/org/apache/doris/task/AlterReplicaTask.java b/fe/fe-core/src/main/java/org/apache/doris/task/AlterReplicaTask.java
index 178550dd229..cd6502da762 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/task/AlterReplicaTask.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/task/AlterReplicaTask.java
@@ -128,7 +128,6 @@ public class AlterReplicaTask extends AgentTask {
                 List<SlotRef> slots = Lists.newArrayList();
                 entry.getValue().collect(SlotRef.class, slots);
                 TAlterMaterializedViewParam mvParam = new TAlterMaterializedViewParam(entry.getKey());
-                mvParam.setOriginColumnName(slots.get(0).getColumnName());
                 mvParam.setMvExpr(entry.getValue().treeToThrift());
                 req.addToMaterializedViewParams(mvParam);
             }
diff --git a/regression-test/data/mv_p0/ssb/multiple_ssb/multiple_ssb.out b/regression-test/data/mv_p0/ssb/multiple_ssb/multiple_ssb.out
index 00e05ab400e..8cdb12f0f50 100644
--- a/regression-test/data/mv_p0/ssb/multiple_ssb/multiple_ssb.out
+++ b/regression-test/data/mv_p0/ssb/multiple_ssb/multiple_ssb.out
@@ -2,7 +2,7 @@
 -- !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
+19920101	1	1	1	1	1	11	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
@@ -22,3 +22,11 @@ ASIA	ASIA	1992	1
 0	nation	0
 1993	nation	0
 
+-- !select_count_1 --
+1	1
+2	2
+
+-- !select_count_3 --
+1	3
+2	4
+
diff --git a/regression-test/data/schema_change_p0/decimalv3/test_agg_keys_schema_change_decimalv3.out b/regression-test/data/schema_change_p0/decimalv3/test_agg_keys_schema_change_decimalv3.out
index fc5d8e286ce..1a305a594ec 100644
--- a/regression-test/data/schema_change_p0/decimalv3/test_agg_keys_schema_change_decimalv3.out
+++ b/regression-test/data/schema_change_p0/decimalv3/test_agg_keys_schema_change_decimalv3.out
@@ -15,17 +15,8 @@
 0.11111111111111111111111111111111100000	11111111111111111111111111111.110	0.11111111111111111111111111111111100000	11111111111111111111111111111.110
 
 -- !sql --
-0.11111111111111111111111111111111100000	11111111111111111111111111111.1100000000	0.11111111111111111111111111111111100000	11111111111111111111111111111.110
-
--- !sql --
-0.11111111111111111111111111111111100000	-999999999999999.999	0.11111111111111111111111111111111100000	11111111111111111111111111111.110
-
--- !sql --
-0.11111111111111111111111111111111100000	-9223.372036854775808	0.11111111111111111111111111111111100000	11111111111111111111111111111.110
-
--- !sql --
-0.11111111111111111111111111111111100000	-9223	0.11111111111111111111111111111111100000	11111111111111111111111111111.110
+0.11111111111111111111111111111111100000	11111111111111111111111111111.110	0.11111111111111111111111111111111100000	11111111111111111111111111111.110
 
 -- !sql --
-0.11111111111111111111111111111111100000	-2147.483648	0.11111111111111111111111111111111100000	11111111111111111111111111111.110
+0.11111111111111111111111111111111100000	11111111111111111111111111111.110	0.11111111111111111111111111111111100000	11111111111111111111111111111.110
 
diff --git a/regression-test/suites/mv_p0/ssb/multiple_ssb/multiple_ssb.groovy b/regression-test/suites/mv_p0/ssb/multiple_ssb/multiple_ssb.groovy
index 3a137a2a8fa..0c8f6ee9a7e 100644
--- a/regression-test/suites/mv_p0/ssb/multiple_ssb/multiple_ssb.groovy
+++ b/regression-test/suites/mv_p0/ssb/multiple_ssb/multiple_ssb.groovy
@@ -137,6 +137,9 @@ suite ("multiple_ssb") {
                 AND P_MFGR IN ('MFGR#1', 'MFGR#2')
                 GROUP BY YEAR, C_NATION
                 ORDER BY YEAR ASC, C_NATION ASC;""")
+    
+    createMV("CREATE MATERIALIZED VIEW count_LO_ORDERPRIORITY_1 as select LO_ORDERDATE, sum(LO_ORDERDATE) from lineorder_flat where LO_ORDERDATE in (1,2,3) group by LO_ORDERDATE;");
+    createMV("CREATE MATERIALIZED VIEW count_LO_ORDERPRIORITY_3 as select LO_ORDERPRIORITY, count(1) from lineorder_flat where LO_ORDERPRIORITY in ('1','2','3') group by LO_ORDERPRIORITY;");
 
     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 ,  [...]
 
@@ -144,7 +147,7 @@ suite ("multiple_ssb") {
 
     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 (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 ,  [...]
 
@@ -152,7 +155,7 @@ suite ("multiple_ssb") {
 
     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;"
+    qt_select_star "select * from lineorder_flat order by 1,2,LO_ORDERPRIORITY;"
     
     explain {
         sql("""SELECT SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
@@ -241,4 +244,16 @@ suite ("multiple_ssb") {
                 AND P_MFGR IN ('MFGR#1', 'MFGR#2')
                 GROUP BY YEAR, C_NATION
                 ORDER BY YEAR ASC, C_NATION ASC;"""
+
+    explain {
+        sql("""select LO_ORDERDATE, sum(LO_ORDERDATE) from lineorder_flat where LO_ORDERDATE in (1,2,3) group by LO_ORDERDATE;""")
+        contains "(count_LO_ORDERPRIORITY_1)"
+    }
+    qt_select_count_1 "select LO_ORDERDATE, sum(LO_ORDERDATE) from lineorder_flat where LO_ORDERDATE in (1,2,3) group by LO_ORDERDATE order by 1,2;"
+
+    explain {
+        sql("""select LO_ORDERPRIORITY, count(1) from lineorder_flat where LO_ORDERPRIORITY in ('1','2','3') group by LO_ORDERPRIORITY;""")
+        contains "(count_LO_ORDERPRIORITY_3)"
+    }
+    qt_select_count_3 "select LO_ORDERPRIORITY, count(1) from lineorder_flat where LO_ORDERPRIORITY in ('1','2','3') group by LO_ORDERPRIORITY order by 1,2;"
 }
diff --git a/regression-test/suites/schema_change_p0/decimalv3/test_agg_keys_schema_change_decimalv3.groovy b/regression-test/suites/schema_change_p0/decimalv3/test_agg_keys_schema_change_decimalv3.groovy
index 83dbdefe843..41f2a32854a 100644
--- a/regression-test/suites/schema_change_p0/decimalv3/test_agg_keys_schema_change_decimalv3.groovy
+++ b/regression-test/suites/schema_change_p0/decimalv3/test_agg_keys_schema_change_decimalv3.groovy
@@ -89,7 +89,7 @@ suite("test_agg_keys_schema_change_decimalv3") {
             sleep(3000)
             break
         } else {
-            sleep(100)
+            sleep(1000)
             if (max_try_time < 1){
                 assertEquals(1,2)
             }
@@ -108,7 +108,7 @@ suite("test_agg_keys_schema_change_decimalv3") {
             sleep(3000)
             break
         } else {
-            sleep(100)
+            sleep(1000)
             if (max_try_time < 1){
                 assertEquals(1,2)
             }
@@ -121,11 +121,11 @@ suite("test_agg_keys_schema_change_decimalv3") {
     max_try_time = 1000
     while (max_try_time--){
         String result = getJobState(tbName)
-        if (result == "FINISHED") {
+        if (result == "CANCELLED") {
             sleep(3000)
             break
         } else {
-            sleep(100)
+            sleep(1000)
             if (max_try_time < 1){
                 assertEquals(1,2)
             }
@@ -139,11 +139,11 @@ suite("test_agg_keys_schema_change_decimalv3") {
     max_try_time = 1000
     while (max_try_time--){
         String result = getJobState(tbName)
-        if (result == "FINISHED") {
+        if (result == "CANCELLED") {
             sleep(3000)
             break
         } else {
-            sleep(100)
+            sleep(1000)
             if (max_try_time < 1){
                 assertEquals(1,2)
             }
@@ -157,11 +157,11 @@ suite("test_agg_keys_schema_change_decimalv3") {
     max_try_time = 1000
     while (max_try_time--){
         String result = getJobState(tbName)
-        if (result == "FINISHED") {
+        if (result == "CANCELLED") {
             sleep(3000)
             break
         } else {
-            sleep(100)
+            sleep(1000)
             if (max_try_time < 1){
                 assertEquals(1,2)
             }


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org