Re: [HACKERS] 6.5 TODO list

>
> Bruce Momjian wrote:
>
> > > > GROUP BY can reference columns not in target list
> > >
> > > What's wrong with that?
> >
> > Is that not a problem. What possible use would GROUP BY on columns not
> > in target list be of use. Maybe it is. I remember someone asking for
> > something like this. I will remove the item unless someone complains.
> > I thought you were the one complaining about it.
>
> This can happen if the GROUP BY clause is coming from a view,
> but the grouping columns of the view aren't in the
> targetlist.
>
> Usually the view's grouping is required because of use of
> aggregates in the view, so omitting them isn't a good idea.
>
> I'm actually testing what happens if I use junk TLE's for
> rule generated GROUP BY entries...

Oh jesus - what a mess!

I've tested it and it solved the problem with

CREATE TABLE t1 (a int4, b int4);
CREATE VIEW v1 AS SELECT b, count(b) AS n
FROM t1 GROUP BY b;
SELECT n FROM v1;

This one produces now the correct output. But it does not
handle

SELECT n FROM v1 WHERE 2 < n;

because the group clause isn't added to the aggregate
subplan, the rule system generated for the qual - that's
maybe fixable. Worse is, that one of the queries in the rules
regression test fails, because a GROUP BY attribute wasn't
found in the targetlist.

The problem is that the planner modifies the targetlist if
the operation is an INSERT/DELETE by first creating a clean
one representing the result relation and then moving the old
expressions into. Then it adds some junk stuff and specially
marked TLE's from the original targetlist.

BUT - during this (preprocess_targetlist()) all the resno's
can get reassigned and later the planner tries to match the
GROUP BY entries only by resno. But the resno's in the group
clauses haven't been adjusted!

Another interesting detail I found is this:

CREATE TABLE t1 (a int4, b int4);
-- insert some stuff into t1
CREATE TABLE t2 (b int4, n int4);

-- This one is working correct:
SELECT b, count(b) FROM t1 GROUP BY b;

-- This one doesn't
INSERT INTO t2 SELECT b, count(b) FROM t1 GROUP BY b;
ERROR: Illegal use of aggregates or non-group column in target list

Ooops - I think it should work - especially because the plain
SELECT returned the correct result. But it fail during the
parse already and I don't get a parser debug output at all
from tcop. As soon as this is fixed, I assume a problem with
a query like this:

INSERT INTO t2 (n) SELECT count(b) FROM t1 GROUP BY b;

(currently it tells "Aggregates not allowed in GROUP BY
clause" - what's totally braindead) The problem I expect is
that the parser creates resno 1 for "count(b)" and a junk TLE
with resno 2 for "b" which is referenced in the group clause.
preprocess_targetlist() will now create the new targetlist
with resno 1 = "b" = NULL, resno 2 = "n" = "count(b)" and
maybe the junk resno 3 for the grouping. Voila, the group
clause will reference the wrong TLE (still resno 2)!

Currently I think the correct solution would be to expand the
targetlist already in the rewrite system and leave it
untouched in the planner. Comments?

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck(at)debis(dot)com (Jan Wieck) #