From 6f7e1d1db8897fd3de2f5262b3d226a84407306f Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Tue, 10 Mar 2009 20:58:41 +0000 Subject: [PATCH] Fix set_subquery_pathlist() to copy the RTE's subquery before it gets mangled by the planning process. This prevents the "failed to locate grouping columns" error recently reported by Dickson Guedes. That happens because planning replaces SubLinks by SubPlans in the subquery's targetlist, and exprTypmod() is smarter about the former than the latter, causing the apparent type of the subquery's output columns to change. This seems to be a deficiency we should fix in exprTypmod(), but that will be a much more invasive patch with possible side-effects elsewhere, so I'll do that only in HEAD. Back-patch to 8.3. Arguably the lack of a copying step is broken/dangerous all the way back, but in the absence of known problems I'll refrain from making the older branches pay the extra cost. (The reason this particular symptom didn't appear before is that exprTypmod() wasn't smart about SubLinks either, until 8.3.) --- src/backend/optimizer/path/allpaths.c | 9 ++++++++- src/test/regress/expected/subselect.out | 12 ++++++++++++ src/test/regress/sql/subselect.sql | 11 +++++++++++ 3 files changed, 31 insertions(+), 1 deletion(-) diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c index 8187ab37d5..94c8b6cba6 100644 --- a/src/backend/optimizer/path/allpaths.c +++ b/src/backend/optimizer/path/allpaths.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/optimizer/path/allpaths.c,v 1.168.2.3 2008/11/11 18:13:43 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/optimizer/path/allpaths.c,v 1.168.2.4 2009/03/10 20:58:41 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -478,6 +478,13 @@ set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel, PlannerInfo *subroot; List *pathkeys; + /* + * Must copy the Query so that planning doesn't mess up the RTE contents + * (really really need to fix the planner to not scribble on its input, + * someday). + */ + subquery = copyObject(subquery); + /* We need a workspace for keeping track of set-op type coercions */ differentTypes = (bool *) palloc0((list_length(subquery->targetList) + 1) * sizeof(bool)); diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out index 6a4f36dc2b..0774f228d1 100644 --- a/src/test/regress/expected/subselect.out +++ b/src/test/regress/expected/subselect.out @@ -466,3 +466,15 @@ from tc; 3 (2 rows) +-- +-- Test case for 8.3 "failed to locate grouping columns" bug +-- +create temp table t1 (f1 numeric(14,0), f2 varchar(30)); +select * from + (select distinct f1, f2, (select f2 from t1 x where x.f1 = up.f1) as fs + from t1 up) ss +group by f1,f2,fs; + f1 | f2 | fs +----+----+---- +(0 rows) + diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql index 3a3f11793d..fd8d5df3a0 100644 --- a/src/test/regress/sql/subselect.sql +++ b/src/test/regress/sql/subselect.sql @@ -298,3 +298,14 @@ select ( select min(tb.id) from tb where tb.aval = (select ta.val from ta where ta.id = tc.aid) ) as min_tb_id from tc; + +-- +-- Test case for 8.3 "failed to locate grouping columns" bug +-- + +create temp table t1 (f1 numeric(14,0), f2 varchar(30)); + +select * from + (select distinct f1, f2, (select f2 from t1 x where x.f1 = up.f1) as fs + from t1 up) ss +group by f1,f2,fs; -- 2.50.1