Re: [HACKERS] [SQL] What's wrong with this group by clause?
[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])
Re: [HACKERS] [SQL] What's wrong with this group by clause?
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?
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