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

[forwarding to -hackers]

On Tue, 4 Nov 2003 18:28:12 -0300, Franco Bruno Borghesi
<franco@akyasociados.com.ar> wrote:
>Below you can find a simplified example of a real case. 
>I don't understand why I'm getting the "john" record twice. 

ISTM you have found a Postgres 7.3 bug.

I get one john withPostgreSQL 7.1.3 on i686-pc-cygwin, compiled by GCC 2.95.3-5
andPostgreSQL 7.2 on i686-pc-linux-gnu, compiled by GCC 2.7.2.1

but two johns withPostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC 2.7.2.1

>/*EXAMPLE*/
>CREATE TABLE people
>(
>   name TEXT
>);
>INSERT INTO people VALUES ('john');
>INSERT INTO people VALUES ('john');
>INSERT INTO people VALUES ('pete');
>INSERT INTO people VALUES ('pete');
>INSERT INTO people VALUES ('ernest');
>INSERT INTO people VALUES ('john');
>   
>SELECT
>   0 AS field1,
>   0 AS field2, 
>   name
>FROM
>   people
>GROUP BY
>   field1,
>   field2,
>   name;
>
> field1 | field2 |  name
>--------+--------+--------
>      0 |      0 | john
>      0 |      0 | pete
>      0 |      0 | ernest
>      0 |      0 | john
>(4 rows)

Same forSELECT 0 AS field1, 0 AS field2, name  FROM people GROUP BY 1, 2, name;

ServusManfred

Manfred Koizar <mkoi-pg@aon.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 wherebyparser would make only one sort-list entry when two
equal()targetlistitems were to be added to an ORDER BY or DISTINCT list. Although indeed this would make sorting
fractionallyfaster bysometimes saving a comparison, it confuses the heck out of laterstages of processing, because it
makesit look like the user wroteDISTINCT ON rather than DISTINCT.  Bug reported byjoe@piscitella.com.
 

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);