yujun777 commented on code in PR #68861: URL: https://github.com/apache/doris/pull/68861#discussion_r4237058683
########## regression-test/suites/nereids_rules_p0/mv/agg_scalar_empty_input/agg_scalar_empty_input.groovy: ########## @@ -0,0 +1,118 @@ +// 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("agg_scalar_empty_input") { + String db = context.config.getDbNameByFile(context.file) + sql "use ${db}" + sql "set pre_materialized_view_rewrite_strategy = TRY_IN_RBO" + sql "set runtime_filter_mode=OFF" + + sql """DROP MATERIALIZED VIEW IF EXISTS scalar_agg_empty_mv""" + sql """DROP TABLE IF EXISTS scalar_agg_empty_base""" + + sql """ + CREATE TABLE scalar_agg_empty_base (k INT NOT NULL, v INT NOT NULL) + DUPLICATE KEY(k) + DISTRIBUTED BY HASH(k) BUCKETS 1 + PROPERTIES ("replication_num" = "1") + """ + + // 2000 rows over 100 distinct k values. k = 9999 matches nothing, and k = 1 holds + // v = 1, 101, ... 1901. + sql """INSERT INTO scalar_agg_empty_base SELECT number % 100, number FROM numbers("number" = "2000")""" + + def mvSql = """select k, count(*) as cnt, sum(v) as s, min(v) as mn, max(v) as mx """ + + """from scalar_agg_empty_base group by k""" + + def qCountEmpty = """select count(*) from scalar_agg_empty_base where k = 9999""" + def qCountMatched = """select count(*) from scalar_agg_empty_base where k = 1""" + def qCountAll = """select count(*) from scalar_agg_empty_base""" + def qCountProjected = """select count(*) + 1 from scalar_agg_empty_base where k = 9999""" + def qCountDistinct = """select count(distinct v) from scalar_agg_empty_base where k = 9999""" + def qSumEmpty = """select sum(v) from scalar_agg_empty_base where k = 9999""" + def qSumMatched = """select sum(v) from scalar_agg_empty_base where k = 1""" + def qSumAll = """select sum(v) from scalar_agg_empty_base""" + def qMinEmpty = """select min(v) from scalar_agg_empty_base where k = 9999""" + def qMinMatched = """select min(v) from scalar_agg_empty_base where k = 1""" + def qMaxEmpty = """select max(v) from scalar_agg_empty_base where k = 9999""" + def qMaxMatched = """select max(v) from scalar_agg_empty_base where k = 1""" + + // Reference results, computed on the base table: a scalar aggregate always emits exactly one + // row, COUNT of an empty input is 0 while SUM/MIN/MAX of an empty input are NULL. + sql "set enable_materialized_view_rewrite=false" + order_qt_count_off_empty "${qCountEmpty}" + order_qt_count_off_matched "${qCountMatched}" + order_qt_count_off_all "${qCountAll}" + order_qt_count_off_projected "${qCountProjected}" + order_qt_count_off_distinct "${qCountDistinct}" + order_qt_sum_off_empty "${qSumEmpty}" + order_qt_sum_off_matched "${qSumMatched}" + order_qt_sum_off_all "${qSumAll}" + order_qt_min_off_empty "${qMinEmpty}" + order_qt_min_off_matched "${qMinMatched}" + order_qt_max_off_empty "${qMaxEmpty}" + order_qt_max_off_matched "${qMaxMatched}" + + // The mv groups by k while these queries are scalar, so the mv dimensions may only be dropped + // if the aggregate node is kept: rolling COUNT up must yield 0 and SUM/MIN/MAX must yield NULL. + // + // Each check below only requires the mv to produce a rewritten candidate, not that the optimizer + // picks it. Keeping the aggregate makes the mv candidate cost about the same as reading the base + // table, so which one wins depends on the cluster, while the results are the same either way. + sql "set enable_materialized_view_rewrite=true" + async_mv_rewrite_success_without_check_chosen(db, mvSql, qCountEmpty, "scalar_agg_empty_mv") Review Comment: Fixed in 483a075d. The mv is now created explicitly and both the base table and the mv are analyzed before the assertions, which makes the pre-aggregated mv clearly the cheaper side, and the two empty-input COUNT queries are asserted with `mv_rewrite_success` so that the rewritten plan is the one actually executed: `select count(*) from scalar_agg_empty_base where k = 9999` and `select count(*) + 1 from scalar_agg_empty_base where k = 9999`. Both are then executed with the rewrite enabled, so the case asserts 0 and 1 against the chosen mv plan, with the mv-disabled goldens kept as the reference for what a scalar aggregate must return. The aggregates whose rewritten plan costs about as much as reading the base table (`count(*)` on a matched group, SUM, MIN, MAX) stay at candidate level, because pinning those to a cbo choice is exactly what broke the three `mv_p0` suites on the previous build; the `ifnull(sum(` check in the memo plan still gives the direct evidence of the empty-input compensation. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
