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

2003-03-13 Thread Tom Lane 
Manfred Koizar <[EMAIL PROTECTED]> 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
[EMAIL PROTECTED]

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

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

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

2003-03-13 Thread Christoph Haller 
>
> On Tue, 4 Nov 2003 18:28:12 -0300, Franco Bruno Borghesi
> <[EMAIL PROTECTED]> 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 with
>  PostgreSQL 7.1.3 on i686-pc-cygwin, compiled by GCC 2.95.3-5
> and
>  PostgreSQL 7.2 on i686-pc-linux-gnu, compiled by GCC 2.7.2.1
>
> but two johns with
>  PostgreSQL 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)
>
 PostgreSQL 7.2.3 on hppa-hp-hpux10.20, compiled by GCC 2.95.2

SELECT   0 AS field1,   0 AS field2,name FROM   people GROUP BY
field1,   field2,   name;
 field1 | field2 |  name
++
  0 |  0 | ernest
  0 |  0 | john
  0 |  0 | pete
(3 rows)

 PostgreSQL 7.3.2 on hppa-hp-hpux10.20, compiled by GCC 2.95.2

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 | john
  0 |  0 | pete
  0 |  0 | john
  0 |  0 | ernest
(6 rows)

I doubt this is a bug in 7.3.2 but in prior versions.
I've cross-checked how another DBMS (HP's ALLBASE) handles GROUP BY
without an aggregate, and it acts like 7.3.2.

Regards, Christoph




---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

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

2003-03-13 Thread Manfred Koizar 
[forwarding to -hackers]

On Tue, 4 Nov 2003 18:28:12 -0300, Franco Bruno Borghesi
<[EMAIL PROTECTED]> 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 with
 PostgreSQL 7.1.3 on i686-pc-cygwin, compiled by GCC 2.95.3-5
and
 PostgreSQL 7.2 on i686-pc-linux-gnu, compiled by GCC 2.7.2.1

but two johns with
 PostgreSQL 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 for
SELECT 0 AS field1, 0 AS field2, name
  FROM people
 GROUP BY 1, 2, name;

Servus
 Manfred

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])