Re: [SQL] What's wrong with this group by clause?

Manfred Koizar <mkoi-pg(at)aon(dot)at> writes:
> ISTM you have found a Postgres 7.3 bug.

Yeah. Actually, the planner bug has been there a long time, but it was
only latent until the parser stopped suppressing duplicate GROUP BY
items:

2002-08-18 14:46 tgl

* src/backend/parser/parse_clause.c: Remove optimization whereby
parser would make only one sort-list entry when two equal()
targetlist items were to be added to an ORDER BY or DISTINCT list.
Although indeed this would make sorting fractionally faster by
sometimes saving a comparison, it confuses the heck out of later
stages of processing, because it makes it look like the user wrote
DISTINCT ON rather than DISTINCT. Bug reported by
joe(at)piscitella(dot)com(dot)

7.3 patch is attached if you need it.

regards, tom lane

*** src/backend/optimizer/plan/planner.c.orig Wed Mar 5 13:38:26 2003
--- src/backend/optimizer/plan/planner.c Thu Mar 13 11:21:16 2003
***************
*** 1498,1510 ****
* are just dummies with no extra execution cost.)
*/
List *sort_tlist = new_unsorted_tlist(subplan->targetlist);
int keyno = 0;
List *gl;

foreach(gl, groupClause)
{
GroupClause *grpcl = (GroupClause *) lfirst(gl);
! TargetEntry *te = nth(grpColIdx[keyno] - 1, sort_tlist);
Resdom *resdom = te->resdom;

/*
--- 1498,1511 ----
* are just dummies with no extra execution cost.)
*/
List *sort_tlist = new_unsorted_tlist(subplan->targetlist);
+ int grpno = 0;
int keyno = 0;
List *gl;

foreach(gl, groupClause)
{
GroupClause *grpcl = (GroupClause *) lfirst(gl);
! TargetEntry *te = nth(grpColIdx[grpno] - 1, sort_tlist);
Resdom *resdom = te->resdom;

/*
***************
*** 1518,1523 ****
--- 1519,1525 ----
resdom->reskey = ++keyno;
resdom->reskeyop = grpcl->sortop;
}
+ grpno++;
}

Assert(keyno > 0);