This is an automated email from the ASF dual-hosted git repository. avamingli pushed a commit to branch main in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit e9abbe0f7914fc9ed59b26e2b73d495dd96beb3c Author: Zhang Mingli <[email protected]> AuthorDate: Tue Mar 10 16:34:01 2026 +0800 Address review feedback for AQUMV join exact-match Fix three issues in aqumv_query_is_exact_match(): - Add groupDistinct comparison (GROUP BY vs GROUP BY DISTINCT) - Add limitOption comparison (LIMIT vs FETCH FIRST WITH TIES) - Clear qp_extra in-place via aqumv_context->qp_extra instead of allocating a local char array; move standard_qp_extra typedef to planner.h so aqumv.c can reference the proper struct type Add test cases 26-28 to verify the new comparisons: - LIMIT vs FETCH FIRST WITH TIES non-match and exact match - GROUP BY DISTINCT vs GROUP BY non-match --- src/backend/optimizer/plan/aqumv.c | 23 +++-- src/backend/optimizer/plan/planner.c | 7 +- src/include/optimizer/planner.h | 7 ++ src/test/regress/expected/matview_data.out | 155 ++++++++++++++++++++++++++++- src/test/regress/sql/matview_data.sql | 63 ++++++++++++ 5 files changed, 237 insertions(+), 18 deletions(-) diff --git a/src/backend/optimizer/plan/aqumv.c b/src/backend/optimizer/plan/aqumv.c index 2084bcbc181..15203a201a2 100644 --- a/src/backend/optimizer/plan/aqumv.c +++ b/src/backend/optimizer/plan/aqumv.c @@ -1045,6 +1045,8 @@ aqumv_query_is_exact_match(Query *raw_parse, Query *viewQuery) /* Compare GROUP BY, HAVING, ORDER BY, DISTINCT, LIMIT */ if (!equal(raw_parse->groupClause, viewQuery->groupClause)) return false; + if (raw_parse->groupDistinct != viewQuery->groupDistinct) + return false; if (!equal(raw_parse->havingQual, viewQuery->havingQual)) return false; if (!equal(raw_parse->sortClause, viewQuery->sortClause)) @@ -1055,6 +1057,8 @@ aqumv_query_is_exact_match(Query *raw_parse, Query *viewQuery) return false; if (!equal(raw_parse->limitOffset, viewQuery->limitOffset)) return false; + if (raw_parse->limitOption != viewQuery->limitOption) + return false; /* Compare boolean flags */ if (raw_parse->hasAggs != viewQuery->hasAggs) @@ -1285,20 +1289,19 @@ answer_query_using_materialized_views_for_join(PlannerInfo *root, AqumvContext a /* * Plan the MV scan. * - * We need a clean qp_extra with no groupClause or activeWindows, - * because the rewritten viewQuery is a simple SELECT from the MV - * with no GROUP BY, windowing, etc. The standard_qp_callback uses - * qp_extra->groupClause to compute group_pathkeys, which would fail - * if it still contained the original query's GROUP BY expressions. + * Clear qp_extra's groupClause and activeWindows because the + * rewritten viewQuery is a simple SELECT from the MV with no + * GROUP BY or windowing. standard_qp_callback would otherwise + * try to compute group_pathkeys from stale expressions. * - * standard_qp_extra is { List *activeWindows; List *groupClause; }, - * so a zeroed struct of that size works correctly (both fields NIL). + * Safe: grouping_planner() no longer reads qp_extra after AQUMV. */ { - char clean_qp_extra[2 * sizeof(List *)]; - memset(clean_qp_extra, 0, sizeof(clean_qp_extra)); - mv_final_rel = query_planner(subroot, qp_callback, clean_qp_extra); + standard_qp_extra *qp = (standard_qp_extra *) aqumv_context->qp_extra; + qp->activeWindows = NIL; + qp->groupClause = NIL; } + mv_final_rel = query_planner(subroot, qp_callback, aqumv_context->qp_extra); /* Cost-based decision: use MV only if cheaper. */ if (mv_final_rel->cheapest_total_path->total_cost < current_rel->cheapest_total_path->total_cost) diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index 89e3611c3a5..9c7021d4cb1 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -120,12 +120,7 @@ create_upper_paths_hook_type create_upper_paths_hook = NULL; #define EXPRKIND_TABLEFUNC_LATERAL 12 #define EXPRKIND_WINDOW_BOUND 13 -/* Passthrough data for standard_qp_callback */ -typedef struct -{ - List *activeWindows; /* active windows, if any */ - List *groupClause; /* overrides parse->groupClause */ -} standard_qp_extra; +/* standard_qp_extra is defined in optimizer/planner.h */ /* * Data specific to grouping sets diff --git a/src/include/optimizer/planner.h b/src/include/optimizer/planner.h index 610034b2c62..9715d9fb31a 100644 --- a/src/include/optimizer/planner.h +++ b/src/include/optimizer/planner.h @@ -66,4 +66,11 @@ extern bool optimizer_init; extern void preprocess_qual_conditions(PlannerInfo *root, Node *jtnode); +/* Passthrough data for standard_qp_callback */ +typedef struct +{ + List *activeWindows; /* active windows, if any */ + List *groupClause; /* overrides parse->groupClause */ +} standard_qp_extra; + #endif /* PLANNER_H */ diff --git a/src/test/regress/expected/matview_data.out b/src/test/regress/expected/matview_data.out index e415ceaf363..a624ab31f3e 100644 --- a/src/test/regress/expected/matview_data.out +++ b/src/test/regress/expected/matview_data.out @@ -2037,7 +2037,158 @@ select c.region, o.status, count(*) as cnt, sum(o.amount) as total north | delivered | 16 | 16800.00 (6 rows) +-- 26. Non-match: LIMIT vs FETCH FIRST WITH TIES (limitOption differs) +create materialized view mv_aqj_limit_test as + select o.order_id, o.amount + from aqj_orders o join aqj_customers c on o.customer_id = c.customer_id + where o.status = 'shipped' + order by o.order_id limit 5; +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'order_id' as the Apache Cloudberry data distribution key for this table. +HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. +analyze mv_aqj_limit_test; +set enable_answer_query_using_materialized_views = on; +-- Same tables/WHERE/ORDER BY but FETCH FIRST WITH TIES: should NOT match +explain(costs off) + select o.order_id, o.amount + from aqj_orders o join aqj_customers c on o.customer_id = c.customer_id + where o.status = 'shipped' + order by o.order_id fetch first 5 rows with ties; + QUERY PLAN +--------------------------------------------------------------------------- + Limit + -> Gather Motion 3:1 (slice1; segments: 3) + Merge Key: o.order_id + -> Limit + -> Sort + Sort Key: o.order_id + -> Hash Join + Hash Cond: (c.customer_id = o.customer_id) + -> Broadcast Motion 3:3 (slice2; segments: 3) + -> Seq Scan on aqj_customers c + -> Hash + -> Seq Scan on aqj_orders o + Filter: (status = 'shipped'::text) + Optimizer: Postgres query optimizer +(14 rows) + +-- Identical LIMIT query: should match +explain(costs off) + select o.order_id, o.amount + from aqj_orders o join aqj_customers c on o.customer_id = c.customer_id + where o.status = 'shipped' + order by o.order_id limit 5; + QUERY PLAN +------------------------------------------------- + Limit + -> Gather Motion 3:1 (slice1; segments: 3) + -> Limit + -> Seq Scan on mv_aqj_limit_test + Optimizer: Postgres query optimizer +(5 rows) + +-- 27. Match: FETCH FIRST WITH TIES exact match +create materialized view mv_aqj_with_ties as + select o.order_id, o.amount + from aqj_orders o join aqj_customers c on o.customer_id = c.customer_id + where o.status = 'pending' + order by o.order_id fetch first 5 rows with ties; +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'order_id' as the Apache Cloudberry data distribution key for this table. +HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. +analyze mv_aqj_with_ties; +set enable_answer_query_using_materialized_views = off; +explain(costs off) + select o.order_id, o.amount + from aqj_orders o join aqj_customers c on o.customer_id = c.customer_id + where o.status = 'pending' + order by o.order_id fetch first 5 rows with ties; + QUERY PLAN +--------------------------------------------------------------------------- + Limit + -> Gather Motion 3:1 (slice1; segments: 3) + Merge Key: o.order_id + -> Limit + -> Sort + Sort Key: o.order_id + -> Hash Join + Hash Cond: (c.customer_id = o.customer_id) + -> Broadcast Motion 3:3 (slice2; segments: 3) + -> Seq Scan on aqj_customers c + -> Hash + -> Seq Scan on aqj_orders o + Filter: (status = 'pending'::text) + Optimizer: Postgres query optimizer +(14 rows) + +select o.order_id, o.amount + from aqj_orders o join aqj_customers c on o.customer_id = c.customer_id + where o.status = 'pending' + order by o.order_id fetch first 5 rows with ties; + order_id | amount +----------+-------- + 1 | 10.50 + 5 | 52.50 + 9 | 94.50 + 13 | 136.50 + 17 | 178.50 +(5 rows) + +set enable_answer_query_using_materialized_views = on; +explain(costs off) + select o.order_id, o.amount + from aqj_orders o join aqj_customers c on o.customer_id = c.customer_id + where o.status = 'pending' + order by o.order_id fetch first 5 rows with ties; + QUERY PLAN +------------------------------------------------ + Limit + -> Gather Motion 3:1 (slice1; segments: 3) + -> Limit + -> Seq Scan on mv_aqj_with_ties + Optimizer: Postgres query optimizer +(5 rows) + +select o.order_id, o.amount + from aqj_orders o join aqj_customers c on o.customer_id = c.customer_id + where o.status = 'pending' + order by o.order_id fetch first 5 rows with ties; + order_id | amount +----------+-------- + 1 | 10.50 + 5 | 52.50 + 9 | 94.50 + 13 | 136.50 + 17 | 178.50 +(5 rows) + +-- 28. Non-match: GROUP BY vs GROUP BY DISTINCT (groupDistinct differs) +-- MV mv_aqj_grp_multi uses GROUP BY (groupDistinct=false, registered in catalog) +-- Query uses GROUP BY DISTINCT — should NOT match +set enable_answer_query_using_materialized_views = on; +explain(costs off) + select c.region, o.status, count(*) as cnt, sum(o.amount) as total + from aqj_orders o join aqj_customers c on o.customer_id = c.customer_id + group by distinct c.region, o.status; + QUERY PLAN +--------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) + -> Finalize HashAggregate + Group Key: c.region, o.status + -> Redistribute Motion 3:3 (slice2; segments: 3) + Hash Key: c.region, o.status + -> Streaming Partial HashAggregate + Group Key: c.region, o.status + -> Hash Join + Hash Cond: (o.customer_id = c.customer_id) + -> Seq Scan on aqj_orders o + -> Hash + -> Broadcast Motion 3:3 (slice3; segments: 3) + -> Seq Scan on aqj_customers c + Optimizer: Postgres query optimizer +(14 rows) + -- Clean up AQUMV join test objects +drop materialized view mv_aqj_with_ties; +drop materialized view mv_aqj_limit_test; drop materialized view mv_aqj_implicit3; drop materialized view mv_aqj_3way_agg; drop materialized view mv_aqj_grp_multi; @@ -2412,10 +2563,10 @@ select mvname, datastatus from gp_matview_aux where mvname like 'mv_par%'; -----------+------------ mv_par2 | u mv_par2_1 | u - mv_par1_1 | i + mv_par1_2 | i mv_par1 | i mv_par | i - mv_par1_2 | i + mv_par1_1 | i (6 rows) abort; diff --git a/src/test/regress/sql/matview_data.sql b/src/test/regress/sql/matview_data.sql index 8b5a56986e0..65de9dd5c9b 100644 --- a/src/test/regress/sql/matview_data.sql +++ b/src/test/regress/sql/matview_data.sql @@ -855,7 +855,70 @@ select c.region, o.status, count(*) as cnt, sum(o.amount) as total group by c.region, o.status order by c.region, o.status limit 6; +-- 26. Non-match: LIMIT vs FETCH FIRST WITH TIES (limitOption differs) +create materialized view mv_aqj_limit_test as + select o.order_id, o.amount + from aqj_orders o join aqj_customers c on o.customer_id = c.customer_id + where o.status = 'shipped' + order by o.order_id limit 5; +analyze mv_aqj_limit_test; + +set enable_answer_query_using_materialized_views = on; +-- Same tables/WHERE/ORDER BY but FETCH FIRST WITH TIES: should NOT match +explain(costs off) + select o.order_id, o.amount + from aqj_orders o join aqj_customers c on o.customer_id = c.customer_id + where o.status = 'shipped' + order by o.order_id fetch first 5 rows with ties; +-- Identical LIMIT query: should match +explain(costs off) + select o.order_id, o.amount + from aqj_orders o join aqj_customers c on o.customer_id = c.customer_id + where o.status = 'shipped' + order by o.order_id limit 5; + +-- 27. Match: FETCH FIRST WITH TIES exact match +create materialized view mv_aqj_with_ties as + select o.order_id, o.amount + from aqj_orders o join aqj_customers c on o.customer_id = c.customer_id + where o.status = 'pending' + order by o.order_id fetch first 5 rows with ties; +analyze mv_aqj_with_ties; + +set enable_answer_query_using_materialized_views = off; +explain(costs off) + select o.order_id, o.amount + from aqj_orders o join aqj_customers c on o.customer_id = c.customer_id + where o.status = 'pending' + order by o.order_id fetch first 5 rows with ties; +select o.order_id, o.amount + from aqj_orders o join aqj_customers c on o.customer_id = c.customer_id + where o.status = 'pending' + order by o.order_id fetch first 5 rows with ties; + +set enable_answer_query_using_materialized_views = on; +explain(costs off) + select o.order_id, o.amount + from aqj_orders o join aqj_customers c on o.customer_id = c.customer_id + where o.status = 'pending' + order by o.order_id fetch first 5 rows with ties; +select o.order_id, o.amount + from aqj_orders o join aqj_customers c on o.customer_id = c.customer_id + where o.status = 'pending' + order by o.order_id fetch first 5 rows with ties; + +-- 28. Non-match: GROUP BY vs GROUP BY DISTINCT (groupDistinct differs) +-- MV mv_aqj_grp_multi uses GROUP BY (groupDistinct=false, registered in catalog) +-- Query uses GROUP BY DISTINCT — should NOT match +set enable_answer_query_using_materialized_views = on; +explain(costs off) + select c.region, o.status, count(*) as cnt, sum(o.amount) as total + from aqj_orders o join aqj_customers c on o.customer_id = c.customer_id + group by distinct c.region, o.status; + -- Clean up AQUMV join test objects +drop materialized view mv_aqj_with_ties; +drop materialized view mv_aqj_limit_test; drop materialized view mv_aqj_implicit3; drop materialized view mv_aqj_3way_agg; drop materialized view mv_aqj_grp_multi; --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
