This is an automated email from the ASF dual-hosted git repository. reshke pushed a commit to branch fix/aqumv-rewrites in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit 06ca9c3f1de3750d39b8d974f6b89a7bccfea352 Author: reshke <[email protected]> AuthorDate: Sat Oct 3 16:23:56 2026 +0000 Fix two AQUMV rewrite errors found by fuzzing With enable_answer_query_using_materialized_views=on, valid queries could fail with "ORDER/GROUP BY expression not found in targetlist" (distinct from #344/#582): 1. The parse->hasAggs && viewQuery->hasAggs branch assumed no GROUP BY on either side, so an aggregate MV with a groupClause survived the tlist replacement and left a dangling ressortgroupref. Decline such MVs. 2. The join exact-match rewrite dropped resjunk tlist entries but kept sortClause, so an MV ordered by a column outside its SELECT list broke even its exact defining query (2e27d4c2b3d fixed only the in-tlist case). Decline MVs whose sortClause references dropped entries. Example (MV: SELECT ... FROM t WHERE k IS NOT NULL GROUP BY k): SELECT count(*) FROM t WHERE k IS NOT NULL; ERROR: ORDER/GROUP BY expression not found in targetlist --- src/backend/optimizer/plan/aqumv.c | 46 ++++++- .../regress/expected/aqumv_regress_rewrites.out | 133 +++++++++++++++++++++ src/test/regress/greenplum_schedule | 2 + src/test/regress/sql/aqumv_regress_rewrites.sql | 57 +++++++++ 4 files changed, 237 insertions(+), 1 deletion(-) diff --git a/src/backend/optimizer/plan/aqumv.c b/src/backend/optimizer/plan/aqumv.c index 8ff560972fb..b1809e2df0a 100644 --- a/src/backend/optimizer/plan/aqumv.c +++ b/src/backend/optimizer/plan/aqumv.c @@ -373,7 +373,14 @@ answer_query_using_materialized_views(PlannerInfo *root, AqumvContext aqumv_cont } else if (parse->hasAggs && viewQuery->hasAggs) { - /* Both don't have group by. */ + /* + * Both have no GROUP BY here (viewQuery's is declined below): + * the tlist is replaced by plain Vars on the MV's columns, + * which cannot carry GROUP BY entries. + */ + if (viewQuery->groupClause != NIL) + continue; + { if (parse->hasDistinctOn || parse->distinctClause != NIL || @@ -1214,6 +1221,43 @@ answer_query_using_materialized_views_for_join(PlannerInfo *root, AqumvContext a viewQuery->targetList = new_tlist; } + /* + * ORDER BY expressions outside the MV's SELECT list existed only as + * resjunk tlist entries, which the new tlist drops: decline the MV. + */ + if (viewQuery->sortClause != NIL) + { + ListCell *slc; + bool sort_refs_missing = false; + + foreach(slc, viewQuery->sortClause) + { + SortGroupClause *sgc = lfirst_node(SortGroupClause, slc); + ListCell *tlc; + bool found = false; + + foreach(tlc, viewQuery->targetList) + { + TargetEntry *tle = lfirst_node(TargetEntry, tlc); + + if (tle->ressortgroupref == sgc->tleSortGroupRef) + { + found = true; + break; + } + } + + if (!found) + { + sort_refs_missing = true; + break; + } + } + + if (sort_refs_missing) + continue; + } + /* Create new RTE for the MV. */ mvrte = makeNode(RangeTblEntry); mvrte->rtekind = RTE_RELATION; diff --git a/src/test/regress/expected/aqumv_regress_rewrites.out b/src/test/regress/expected/aqumv_regress_rewrites.out new file mode 100644 index 00000000000..39f8be7f268 --- /dev/null +++ b/src/test/regress/expected/aqumv_regress_rewrites.out @@ -0,0 +1,133 @@ +-- +-- AQUMV rewrite regressions: MVs that cannot serve a query must be +-- declined, not produce "ORDER/GROUP BY expression not found in targetlist". +-- Postgres planner only. +set optimizer = off; +create schema aqumv_rw; +set search_path to aqumv_rw; +-- A grouped aggregate MV used to break ungrouped aggregate queries. +create table rw_t1(id int, k int, v numeric(12,2)) distributed by (id); +insert into rw_t1 select g, g % 50, (g % 1000)::numeric(12,2) from generate_series(1, 20000) g; +analyze rw_t1; +create materialized view rw_mv_grouped as + select k, count(*) as c, sum(v) as s + from rw_t1 where k is not null group by k distributed by (k); +analyze rw_mv_grouped; +set enable_answer_query_using_materialized_views = on; +-- used to fail with "ORDER/GROUP BY expression not found in targetlist" +select count(*) from rw_t1 where k is not null; + count +------- + 20000 +(1 row) + +-- a grouped query matching the MV keeps working +select k, count(*) as c, sum(v) as s + from rw_t1 where k is not null group by k order by k limit 3; + k | c | s +---+-----+----------- + 0 | 400 | 190000.00 + 1 | 400 | 190400.00 + 2 | 400 | 190800.00 +(3 rows) + +-- An exact-match join MV used to break queries ordered by a column +-- that is not in its SELECT list. +create table rw_ta(id int, k int) distributed by (id); +create table rw_tb(id int, w int) distributed by (id); +insert into rw_ta select g, g % 1000 from generate_series(1, 6000) g; +insert into rw_tb select g, g from generate_series(1, 6000) g; +analyze rw_ta; +analyze rw_tb; +create materialized view rw_mv_join as + select a.id as aid from rw_ta a join rw_tb b on a.id = b.id + where a.k > 995 order by b.w distributed by (aid); +analyze rw_mv_join; +-- the exact defining query; used to fail with the GUC on +select a.id as aid from rw_ta a join rw_tb b on a.id = b.id + where a.k > 995 order by b.w; + aid +------ + 996 + 997 + 998 + 999 + 1996 + 1997 + 1998 + 1999 + 2996 + 2997 + 2998 + 2999 + 3996 + 3997 + 3998 + 3999 + 4996 + 4997 + 4998 + 4999 + 5996 + 5997 + 5998 + 5999 +(24 rows) + +-- ORDER BY over a selected column is still rewritten (2e27d4c2b3d) +create materialized view rw_mv_join_intlist as + select a.id as aid, b.w as w from rw_ta a join rw_tb b on a.id = b.id + where a.k > 995 order by a.id distributed by (aid); +analyze rw_mv_join_intlist; +explain (costs off) + select a.id as aid, b.w as w from rw_ta a join rw_tb b on a.id = b.id + where a.k > 995 order by a.id; + QUERY PLAN +-------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) + Merge Key: aid + -> Sort + Sort Key: aid + -> Seq Scan on rw_mv_join_intlist + Optimizer: Postgres query optimizer +(6 rows) + +select a.id as aid, b.w as w from rw_ta a join rw_tb b on a.id = b.id + where a.k > 995 order by a.id; + aid | w +------+------ + 996 | 996 + 997 | 997 + 998 | 998 + 999 | 999 + 1996 | 1996 + 1997 | 1997 + 1998 | 1998 + 1999 | 1999 + 2996 | 2996 + 2997 | 2997 + 2998 | 2998 + 2999 | 2999 + 3996 | 3996 + 3997 | 3997 + 3998 | 3998 + 3999 | 3999 + 4996 | 4996 + 4997 | 4997 + 4998 | 4998 + 4999 | 4999 + 5996 | 5996 + 5997 | 5997 + 5998 | 5998 + 5999 | 5999 +(24 rows) + +reset enable_answer_query_using_materialized_views; +drop schema aqumv_rw cascade; +NOTICE: drop cascades to 6 other objects +DETAIL: drop cascades to table rw_t1 +drop cascades to materialized view rw_mv_grouped +drop cascades to table rw_ta +drop cascades to table rw_tb +drop cascades to materialized view rw_mv_join +drop cascades to materialized view rw_mv_join_intlist diff --git a/src/test/regress/greenplum_schedule b/src/test/regress/greenplum_schedule index 84e8766844b..7c8cb1cd5ad 100755 --- a/src/test/regress/greenplum_schedule +++ b/src/test/regress/greenplum_schedule @@ -352,6 +352,8 @@ test: aqumv # Tests of materialized view data catalog maintenance test: matview_data +test: aqumv_regress_rewrites + # test access method with encoding options test: am_encoding diff --git a/src/test/regress/sql/aqumv_regress_rewrites.sql b/src/test/regress/sql/aqumv_regress_rewrites.sql new file mode 100644 index 00000000000..c41d49a048b --- /dev/null +++ b/src/test/regress/sql/aqumv_regress_rewrites.sql @@ -0,0 +1,57 @@ +-- +-- AQUMV rewrite regressions: MVs that cannot serve a query must be +-- declined, not produce "ORDER/GROUP BY expression not found in targetlist". +-- Postgres planner only. +set optimizer = off; +create schema aqumv_rw; +set search_path to aqumv_rw; + +-- A grouped aggregate MV used to break ungrouped aggregate queries. +create table rw_t1(id int, k int, v numeric(12,2)) distributed by (id); +insert into rw_t1 select g, g % 50, (g % 1000)::numeric(12,2) from generate_series(1, 20000) g; +analyze rw_t1; + +create materialized view rw_mv_grouped as + select k, count(*) as c, sum(v) as s + from rw_t1 where k is not null group by k distributed by (k); +analyze rw_mv_grouped; + +set enable_answer_query_using_materialized_views = on; +-- used to fail with "ORDER/GROUP BY expression not found in targetlist" +select count(*) from rw_t1 where k is not null; +-- a grouped query matching the MV keeps working +select k, count(*) as c, sum(v) as s + from rw_t1 where k is not null group by k order by k limit 3; + +-- An exact-match join MV used to break queries ordered by a column +-- that is not in its SELECT list. +create table rw_ta(id int, k int) distributed by (id); +create table rw_tb(id int, w int) distributed by (id); +insert into rw_ta select g, g % 1000 from generate_series(1, 6000) g; +insert into rw_tb select g, g from generate_series(1, 6000) g; +analyze rw_ta; +analyze rw_tb; + +create materialized view rw_mv_join as + select a.id as aid from rw_ta a join rw_tb b on a.id = b.id + where a.k > 995 order by b.w distributed by (aid); +analyze rw_mv_join; + +-- the exact defining query; used to fail with the GUC on +select a.id as aid from rw_ta a join rw_tb b on a.id = b.id + where a.k > 995 order by b.w; + +-- ORDER BY over a selected column is still rewritten (2e27d4c2b3d) +create materialized view rw_mv_join_intlist as + select a.id as aid, b.w as w from rw_ta a join rw_tb b on a.id = b.id + where a.k > 995 order by a.id distributed by (aid); +analyze rw_mv_join_intlist; + +explain (costs off) + select a.id as aid, b.w as w from rw_ta a join rw_tb b on a.id = b.id + where a.k > 995 order by a.id; +select a.id as aid, b.w as w from rw_ta a join rw_tb b on a.id = b.id + where a.k > 995 order by a.id; + +reset enable_answer_query_using_materialized_views; +drop schema aqumv_rw cascade; --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
