You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cloudberry.apache.org by yj...@apache.org on 2026/10/18 19:36:02 UTC
(cloudberry) branch main updated: Fix two AQUMV rewrite errors found by fuzzing
This is an automated email from the ASF dual-hosted git repository.
yjhjstz pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
The following commit(s) were added to refs/heads/main by this push:
new 9e94f510486 Fix two AQUMV rewrite errors found by fuzzing
9e94f510486 is described below
commit 9e94f51048645c99ee20e24c966a485dbe17e065
Author: reshke <re...@double.cloud>
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 29a51740eba..73fb0fa7a54 100755
--- a/src/test/regress/greenplum_schedule
+++ b/src/test/regress/greenplum_schedule
@@ -353,6 +353,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: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org