github-actions[bot] commented on code in PR #68861: URL: https://github.com/apache/doris/pull/68861#discussion_r4237038161
########## 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: [P2] Exercise the repaired MV plan in this regression. `mv_rewrite_success_without_check_chosen` accepts `.scalar_agg_empty_mv not chose` for every query here, while the later `order_qt_*_on_*` assertions run unhinted SQL. The intended empty case is `Project(ifnull(sum(mv.cnt), 0)) -> Aggregate([]) -> Filter(k=9999) -> MVScan`, but a base-scan winner would pass all result assertions even if that MV plan returned the wrong value; the memo check only matches `ifnull(sum(` text. Please verify this MV is selected for at least empty `COUNT(*)` and `COUNT(*) + 1`, then execute those same queries and assert 0 and 1. -- 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]
