Re: pg, mysql comparison with "group by" clause - Mailing list pgsql-sql

Tom,
 I'm sure there's all sorts of cool optimizations under the covers  to perhaps maybe to this short circuiting,   but as
thesql goes, yeah, I'm sure. 

Here's an example on oracle 10g release 2 (copy paste from my screen so
you can see the error messages and all):



SQL> create table foo(id number primary key, name varchar2(10));

Table created.

SQL> insert into foo values (1,'sam');

1 row created.

SQL> insert into foo values (2,'sam');

1 row created.

SQL> commit;

Commit complete.

SQL> select id, count(*) from foo;
select id, count(*) from foo      *
ERROR at line 1:
ORA-00937: not a single-group group function


SQL> select name, count(*) from foo;
select name, count(*) from foo      *
ERROR at line 1:
ORA-00937: not a single-group group function


SQL> select name, count(*) from foo group by id;
select name, count(*) from foo group by id      *
ERROR at line 1:
ORA-00979: not a GROUP BY expression


SQL> select name, count(*) from foo group by name;

NAME         COUNT(*)
---------- ----------
sam                 2

SQL> select name, count(*) from foo group by name,id;

NAME         COUNT(*)
---------- ----------
sam                 1
sam                 1

SQL>


I can't imagine Oracle making a change such as the one we're discussing
at this point. Perhaps in 8.1.6, ~7 years ago, when *tons* of sql
changes were implemented (analytics, CASE, ordering in inline views,
CUBE, ROLLUP),
but not now...

then again, oracle is 100% completely driven by money, so, if enough
customers ask for it, it will happen eventually. I just can't imagine
anyone asking for this feature when we're paying 40k per cpu just to
run oracle; there are much more important things for them to be workin
on...


Regards, Anthony

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, October 13, 2005 3:17 PM
To: Anthony Molinaro
Cc: Scott Marlowe; Greg Stark; Stephan Szabo; Rick Schumeyer;
pgsql-sql@postgresql.org
Subject: Re: [SQL] pg, mysql comparison with "group by" clause

"Anthony Molinaro" <amolinaro@wgen.net> writes:
> An additional gripe is that this isn't a good feature (standard or
not).
> Oracle doesn't do it. Db2 doesn't do it.

You sure about that?  It's hard to believe that the SQL committee would
put a feature into the spec that neither Oracle nor IBM intended to
implement.  Those two pretty much control the committee after all ...
        regards, tom lane