RE: [sqlite] Aggregates in SELECT without GROUP BY
Joe Wilson <[EMAIL PROTECTED]> wrote: --- Ken wrote: > Doing this in oracle results in an error: > > SQL> select max(addr_id), emp_id from z_address; > select max(addr_id), emp_id from z_address > * > ERROR at line 1: > ORA-00937: not a single-group group function As expected. > I think an error is more appropriate when there is no group by clause. But as > a developer I know > better, and write aggregated sql with a group by. > > select max(addr_id), emp_id from z_address group by null; > > Does not return an error nor does it return data. The GROUP BY NULL thing is not standard, which is why I qualified it with for sqlite. It varies from database to database. -- mysql, sqlite select max(a) from t group by null; select max(a) from t group by ''; -- postgres select max(a) from t group by +0; Oracle may or may not have an equivalent. Agreed. :)
RE: [sqlite] Aggregates in SELECT without GROUP BY
--- Ken <[EMAIL PROTECTED]> wrote: > Doing this in oracle results in an error: > > SQL> select max(addr_id), emp_id from z_address; > select max(addr_id), emp_id from z_address > * > ERROR at line 1: > ORA-00937: not a single-group group function As expected. > I think an error is more appropriate when there is no group by clause. But as > a developer I know > better, and write aggregated sql with a group by. > > select max(addr_id), emp_id from z_address group by null; > > Does not return an error nor does it return data. The GROUP BY NULL thing is not standard, which is why I qualified it with for sqlite. It varies from database to database. -- mysql, sqlite select max(a) from t group by null; select max(a) from t group by ''; -- postgres select max(a) from t group by +0; Oracle may or may not have an equivalent. Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Aggregates in SELECT without GROUP BY
Doing this in oracle results in an error: SQL> select max(addr_id), emp_id from z_address; select max(addr_id), emp_id from z_address * ERROR at line 1: ORA-00937: not a single-group group function I think an error is more appropriate when there is no group by clause. But as a developer I know better, and write aggregated sql with a group by. select max(addr_id), emp_id from z_address group by null; Does not return an error nor does it return data. Ken Joe Wilson <[EMAIL PROTECTED]> wrote: --- Darren Duncan wrote: > At 10:17 PM -0500 1/14/08, Griggs, Donald wrote: > >Regarding: " A DBMS accepting such queries isn't just a little > >dangerous, its flat out wrong. I would ask what rationale there is for > >this query not failing. -- Darren Duncan" > > > >I'm not asserting that you have to agree with the rationale, but did you > >see and read the discussion that Joe Wilson pointed out to you? > > > >= > >This issue is debated from time to time on the list: > > > > http://www.mail-archive.com/sqlite-users@sqlite.org/msg17769.html > > Sorry, I missed the url on my first reading. > > Also, my first comment was based on the idea that SQL usually returns > exactly one row on a query that uses an aggregate but no group-by, > and where all result field values are scalar. In sqlite, assuming there's at least one row, an aggregate SELECT with no GROUP BY clause is conceptually the same as an equivalent SELECT with GROUP BY NULL - i.e., the group of all rows. (I say 'conceptually' because GROUP BY NULL is much slower). But I agree with your point. Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ - To unsubscribe, send email to [EMAIL PROTECTED] - Joe Wilson <[EMAIL PROTECTED]> wrote: --- Darren Duncan wrote: > At 10:17 PM -0500 1/14/08, Griggs, Donald wrote: > >Regarding: " A DBMS accepting such queries isn't just a little > >dangerous, its flat out wrong. I would ask what rationale there is for > >this query not failing. -- Darren Duncan" > > > >I'm not asserting that you have to agree with the rationale, but did you > >see and read the discussion that Joe Wilson pointed out to you? > > > >= > >This issue is debated from time to time on the list: > > > > http://www.mail-archive.com/sqlite-users@sqlite.org/msg17769.html > > Sorry, I missed the url on my first reading. > > Also, my first comment was based on the idea that SQL usually returns > exactly one row on a query that uses an aggregate but no group-by, > and where all result field values are scalar. In sqlite, assuming there's at least one row, an aggregate SELECT with no GROUP BY clause is conceptually the same as an equivalent SELECT with GROUP BY NULL - i.e., the group of all rows. (I say 'conceptually' because GROUP BY NULL is much slower). But I agree with your point. Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Aggregates in SELECT without GROUP BY
At 11:41 PM -0800 1/14/08, Joe Wilson wrote: In sqlite, assuming there's at least one row, an aggregate SELECT with no GROUP BY clause is conceptually the same as an equivalent SELECT with GROUP BY NULL - i.e., the group of all rows. (I say 'conceptually' because GROUP BY NULL is much slower). I actually thought of raising this issue too, but then thought it would complicate the discussion. One could conceive a SQL SELECT, if it has no explicit GROUP BY but has explicit aggregate functions in the select list, as if it had an explicit GROUP BY but an empty column list, that is, a group per distinct source sub-rows of zero columns rather than per distinct source sub-rows of 1..M columns, and so a source rowset of 1..N rows would turn into a result rowset of exactly 1 row. However, unless I'm mistaken about SQL behaviour, I see this analogy not holding true when there are zero source rows. Normal SQL will return exactly 1 row when using aggregate functions and no GROUP BY clause, which is actually good when using things like COUNT or SUM. However, any GROUP BY, whether over zero columns or 1..N columns, would return zero rows if there were zero input rows. That is the only way it can work if its behaviour is intended to be consistent. Of course, that's not to say that there is any overall logical inconsistency, IF you consider that the native environment for aggregate functions is NOT with a GROUP BY. So, use an aggregate on any rowset of 0..N rows, you get 1 row back. If you conceive GROUP BY as actually just creating a table some of whose row field values are themselves tables (the columns being grouped by are outside of the inner tables, those not being grouped by are inside them), then using aggregate functions together with a GROUP BY is treating each inner table like the only table as far as the aggregates are concerned, and so applying the aggregates to inner tables to convert them to inner tables of one row each, then typically each of those is merged with its containing single outer row again. On that note, a group-by of zero columns would then produce a table having a single row and single field whose value is the original table. Now smarter relational DBMSs that support table-valued-fields could then let you use a GROUP BY in isolation, since if you keep any fields not being grouped by, they form rows of inner tables. Less capable DBMSs don't let you directly use the actual result of a relational group, and require you to do the additional step of either discarding non-grouped-by columns or using aggregates on them. I don't know if SQL has provisions for a relational operator that results in the intermediate value I mentioned (table of tables), but even if it doesn't, a truly relational DBMS would have it. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Aggregates in SELECT without GROUP BY
--- Darren Duncan <[EMAIL PROTECTED]> wrote: > At 10:17 PM -0500 1/14/08, Griggs, Donald wrote: > >Regarding: " A DBMS accepting such queries isn't just a little > >dangerous, its flat out wrong. I would ask what rationale there is for > >this query not failing. -- Darren Duncan" > > > >I'm not asserting that you have to agree with the rationale, but did you > >see and read the discussion that Joe Wilson pointed out to you? > > > >= > >This issue is debated from time to time on the list: > > > > http://www.mail-archive.com/sqlite-users@sqlite.org/msg17769.html > > Sorry, I missed the url on my first reading. > > Also, my first comment was based on the idea that SQL usually returns > exactly one row on a query that uses an aggregate but no group-by, > and where all result field values are scalar. In sqlite, assuming there's at least one row, an aggregate SELECT with no GROUP BY clause is conceptually the same as an equivalent SELECT with GROUP BY NULL - i.e., the group of all rows. (I say 'conceptually' because GROUP BY NULL is much slower). But I agree with your point. Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Aggregates in SELECT without GROUP BY
At 10:17 PM -0500 1/14/08, Griggs, Donald wrote:
Hi Duncan,
Regarding: " A DBMS accepting such queries isn't just a little
dangerous, its flat out wrong. I would ask what rationale there is for
this query not failing. -- Darren Duncan"
I'm not asserting that you have to agree with the rationale, but did you
see and read the discussion that Joe Wilson pointed out to you?
=
This issue is debated from time to time on the list:
http://www.mail-archive.com/sqlite-users@sqlite.org/msg17769.html
Sorry, I missed the url on my first reading.
Also, my first comment was based on the idea that SQL usually returns
exactly one row on a query that uses an aggregate but no group-by,
and where all result field values are scalar.
However, I can see example "SELECT MAX(a), b FROM T;" conceptually
being valid where either 1 row is returned with the 'b' value being
collection-typed (containing {'Cat','Dog','Mouse'}), or alternately
(such as because actual collection-typed values aren't supported by
the DBMS) where that answer were ungrouped such that the main query
results in 3 rows where the 'b' value has each of those 3 and the 'a'
value is 7 for every row. Or substitute 5 for 3 in either case if
you are operating bag-oriented like SQL prefers rather than
set-oriented.
So if that's what happens, then fine.
But the OP implied that the query returned exactly 1 row, with '7'
for 'a' and a random value 'Mouse' for 'b', and such a result is what
I am objecting to.
-- Darren Duncan
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Aggregates in SELECT without GROUP BY
Hi Duncan, Regarding: " A DBMS accepting such queries isn't just a little dangerous, its flat out wrong. I would ask what rationale there is for this query not failing. -- Darren Duncan" I'm not asserting that you have to agree with the rationale, but did you see and read the discussion that Joe Wilson pointed out to you? = This issue is debated from time to time on the list: http://www.mail-archive.com/sqlite-users@sqlite.org/msg17769.html The only other database that I'm aware of that supports selecting non-aggregates that are not listed in GROUP BY is MySQL: -- valid in sqlite and mysql, invalid in postgres select b from t group by a; But your particular example is not valid in MySQL: mysql> SELECT MAX(a), b FROM T; ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause This email and any attachments have been scanned for known viruses using multiple scanners. We believe that this email and any attachments are virus free, however the recipient must take full responsibility for virus checking. This email message is intended for the named recipient only. It may be privileged and/or confidential. If you are not the named recipient of this email please notify us immediately and do not copy it or use it for any purpose, nor disclose its contents to any other person. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Aggregates in SELECT without GROUP BY
At 3:14 PM +0200 1/14/08, Lauri Nurmi wrote: SQLite seems to be accepting SELECT queries that use aggregate functions without a GROUP BY. This is a little dangerous, because queries that should not work at all are returning sensible-looking results. sqlite> SELECT MAX(a), b FROM T; 7|Mouse I would argue that this is a bug in the general case, where b does not have the same value in every row of T. A DBMS accepting such queries isn't just a little dangerous, its flat out wrong. I would ask what rationale there is for this query not failing. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Aggregates in SELECT without GROUP BY
This issue is debated from time to time on the list: http://www.mail-archive.com/sqlite-users@sqlite.org/msg17769.html The only other database that I'm aware of that supports selecting non-aggregates that are not listed in GROUP BY is MySQL: -- valid in sqlite and mysql, invalid in postgres select b from t group by a; But your particular example is not valid in MySQL: mysql> SELECT MAX(a), b FROM T; ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause --- Lauri Nurmi <[EMAIL PROTECTED]> wrote: > SQLite seems to be accepting SELECT queries that use aggregate functions > without a GROUP BY. This is a little dangerous, because queries that > should not work at all are returning sensible-looking results. > > Example: > > Let's have a simple table T with the following structure and content: > > CREATE TABLE T(a INTEGER, b TEXT); > INSERT INTO "T" VALUES(1,'Dog'); > INSERT INTO "T" VALUES(2,'Cat'); > INSERT INTO "T" VALUES(3,'Mouse'); > INSERT INTO "T" VALUES(6,'Cat'); > INSERT INTO "T" VALUES(7,'Mouse'); > > Now, let's say we want the maximum value of "a" and the animal name > related to it. Easy: > > sqlite> SELECT MAX(a), b FROM T; > 7|Mouse > > The result was as expected, and everyone is happy? > > Let's find the minimum of "a" and the related animal name: > > sqlite> SELECT MIN(a), b FROM T; > 1|Mouse > > Wait -- this is not what we expected. But in a database with hundreds > or thousands of lines we might not have noticed the result is wrong. > Also the result of the previous MAX(a) query was "correct" only by > coincidence. Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Aggregates in SELECT without GROUP BY
I've run into this issue myself and had more trouble than necessary tracking down problems related to it. Personally I would consider it a bug, but it's been discussed hear as accepted behavior. Sam --- We're Hiring! Seeking a passionate developer to join our team building Flex based products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Lauri Nurmi [mailto:[EMAIL PROTECTED] Sent: Monday, January 14, 2008 8:15 AM To: sqlite-users@sqlite.org Subject: [sqlite] Aggregates in SELECT without GROUP BY Hello, SQLite seems to be accepting SELECT queries that use aggregate functions without a GROUP BY. This is a little dangerous, because queries that should not work at all are returning sensible-looking results. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Aggregates in SELECT without GROUP BY
Hello, SQLite seems to be accepting SELECT queries that use aggregate functions without a GROUP BY. This is a little dangerous, because queries that should not work at all are returning sensible-looking results. Example: Let's have a simple table T with the following structure and content: CREATE TABLE T(a INTEGER, b TEXT); INSERT INTO "T" VALUES(1,'Dog'); INSERT INTO "T" VALUES(2,'Cat'); INSERT INTO "T" VALUES(3,'Mouse'); INSERT INTO "T" VALUES(6,'Cat'); INSERT INTO "T" VALUES(7,'Mouse'); Now, let's say we want the maximum value of "a" and the animal name related to it. Easy: sqlite> SELECT MAX(a), b FROM T; 7|Mouse The result was as expected, and everyone is happy? Let's find the minimum of "a" and the related animal name: sqlite> SELECT MIN(a), b FROM T; 1|Mouse Wait -- this is not what we expected. But in a database with hundreds or thousands of lines we might not have noticed the result is wrong. Also the result of the previous MAX(a) query was "correct" only by coincidence. The correct result can be obtained with the query: sqlite> SELECT MIN(a), b FROM T GROUP BY b ORDER BY a; 1|Dog 2|Cat 3|Mouse Regards, -LN - To unsubscribe, send email to [EMAIL PROTECTED] -
