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 2e27d4c2b3d1df1eeaa541785393c5146a926d67 Author: Zhang Mingli <[email protected]> AuthorDate: Tue Mar 10 17:21:39 2026 +0800 Fix ORDER BY lost after AQUMV join rewrite The rewrite cleared sortClause, so grouping_planner() skipped adding a Sort node — queries with ORDER BY returned unsorted results from the MV scan. Fix: preserve sortClause and copy ressortgroupref to rewritten target entries so the upper planner generates Sort correctly. Before: Limit -> Gather -> Limit -> Seq Scan on mv After: Limit -> Gather -> Limit -> Sort -> Seq Scan on mv --- src/backend/optimizer/plan/aqumv.c | 5 +++-- src/test/regress/expected/matview_data.out | 22 ++++++++++++++-------- 2 files changed, 17 insertions(+), 10 deletions(-) diff --git a/src/backend/optimizer/plan/aqumv.c b/src/backend/optimizer/plan/aqumv.c index 15203a201a2..04c173f41de 100644 --- a/src/backend/optimizer/plan/aqumv.c +++ b/src/backend/optimizer/plan/aqumv.c @@ -1206,6 +1206,7 @@ answer_query_using_materialized_views_for_join(PlannerInfo *root, AqumvContext a (AttrNumber) attnum, old_tle->resname, false); + new_tle->ressortgroupref = old_tle->ressortgroupref; new_tlist = lappend(new_tlist, new_tle); } @@ -1243,11 +1244,11 @@ answer_query_using_materialized_views_for_join(PlannerInfo *root, AqumvContext a viewQuery->jointree = makeFromExpr(list_make1(makeNode(RangeTblRef)), NULL); ((RangeTblRef *) linitial(viewQuery->jointree->fromlist))->rtindex = 1; - /* Clear aggregation/grouping/sorting state — all materialized. */ + /* Clear aggregation/grouping state — already materialized in MV. */ viewQuery->hasAggs = false; viewQuery->groupClause = NIL; viewQuery->havingQual = NULL; - viewQuery->sortClause = NIL; + /* Keep sortClause: upper planner needs it to add Sort node. */ viewQuery->distinctClause = NIL; viewQuery->hasDistinctOn = false; viewQuery->hasWindowFuncs = false; diff --git a/src/test/regress/expected/matview_data.out b/src/test/regress/expected/matview_data.out index a624ab31f3e..9a9074cd2d5 100644 --- a/src/test/regress/expected/matview_data.out +++ b/src/test/regress/expected/matview_data.out @@ -2077,14 +2077,17 @@ explain(costs off) 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 -------------------------------------------------- + QUERY PLAN +------------------------------------------------------- Limit -> Gather Motion 3:1 (slice1; segments: 3) + Merge Key: order_id -> Limit - -> Seq Scan on mv_aqj_limit_test + -> Sort + Sort Key: order_id + -> Seq Scan on mv_aqj_limit_test Optimizer: Postgres query optimizer -(5 rows) +(8 rows) -- 27. Match: FETCH FIRST WITH TIES exact match create materialized view mv_aqj_with_ties as @@ -2138,14 +2141,17 @@ explain(costs off) 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 ------------------------------------------------- + QUERY PLAN +------------------------------------------------------ Limit -> Gather Motion 3:1 (slice1; segments: 3) + Merge Key: order_id -> Limit - -> Seq Scan on mv_aqj_with_ties + -> Sort + Sort Key: order_id + -> Seq Scan on mv_aqj_with_ties Optimizer: Postgres query optimizer -(5 rows) +(8 rows) select o.order_id, o.amount from aqj_orders o join aqj_customers c on o.customer_id = c.customer_id --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
