OK, I poked into this. The test case can be simplified to this:
regression=# create table t1 (f1 numeric(14,0), f2 varchar(30));
CREATE TABLE
regression=# create view vv as
select distinct f1,f2,(select f2 from t1 x where x.f1=aa.f1) as fs
from t1 aa;
CREATE VIEW
regression=# select * from vv group by f1,f2,fs;
ERROR: failed to locate grouping columns
The reason that locate_grouping_columns fails is that it's being asked
to match up a Var with type varchar(30) (representing the result of the
view's fs column) to a Var with typmod -1, and those are not equal
according to equal(). The Var with default typmod is being manufactured
by build_physical_tlist(), which is looking at a subquery RTE whose
targetlist contains a SubPlan node. Since exprTypmod just punts on
SubPlans, it constructs a Var with typmod -1.
So there are a couple of places we could assign blame here:
1. Subqueries in RTE nodes are supposed to be virgin, unplanned
querytrees, so finding a SubPlan in the targetlist is unexpected.
On this theory, the fault is that of set_subquery_pathlist(), which
ought to copy the RTE's subquery before it turns subquery_planner
loose on it (not to mention the changes it itself makes...). More
generally it's another reason to fix the planner to not scribble on
its input, but that's a task for some other day.
2. It would still work if only SubPlans didn't lose information relative
to SubLinks. On this theory we ought to add a firstColTypmod field to
SubPlan. (The reason we didn't see this behavior before 8.3 is that
exprTypmod punted on SubLinks, too, before 8.3, and so the output of
the calling view would have been assigned typmod -1 anyway.)
Solution #1 is a bit annoying from a planner performance point of view,
but is probably the safest thing in the near term. Solution #2 is
seeming like a good idea in the long run; but it also seems like it is
just fixing one symptom of the general issue that we're scribbling on
the content of a subquery RTE. I'm also a tad hesitant to back-patch it
because I'm not sure if there are any places where it would change
user-visible behavior in unexpected ways.
So what I'm inclined to do is insert a copyObject() call into
set_subquery_pathlist(), and maybe in the future add a typmod field to
SubPlan. I remain a bit uncertain about how far back to back-patch.
We know that 8.3 is broken and that 8.2 and before do not exhibit this
particular symptom. It seems like there might be other problems with
the same root cause that do afflict pre-8.3 versions, but if we've gone
this long without finding them, are they really there? Should we slow
down the planner in back versions to prevent a hypothetical problem?
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
