Re: [HACKERS] 6.5 TODO list - Mailing list pgsql-hackers

>
> 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@debis.com (Jan Wieck) #