Re: [sqlite] Seems like a bug in the parser
On 8/23/06, Andrew McCollum <[EMAIL PROTECTED]> wrote: I find this feature useful, especially in queries which use aggregate functions, such as the following: SELECT sum(a) FROM tbl GROUP BY b The question should be what the compelling reason is to remove a useful feature. Of course it's OK to have an aggregated function in the select list for non-grouped column. This is correct many-to-one mapping. -- Alexei Alexandrov - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Seems like a bug in the parser
> The question should be what the compelling reason is to remove a useful > feature. ... And *that* is exactly why Windows will always be full of security holes. I thought it was because it used the network for inter process communications (thus allowing external processes to attack it) -- SqliteImporter and SqliteReplicator: Command line utilities for Sqlite http://www.reddawn.net/~jsprenkl/Sqlite Cthulhu Bucks! http://www.cthulhubucks.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Seems like a bug in the parser
--- Kurt Welgehausen <[EMAIL PROTECTED]> wrote: > > select a from qqq group by b; > > This question was discussed on the list a year or 2 ago. > > The column a in the simple query above is meaningless; it's > an arbitrary value from each group. There are queries, > however, where a non-grouped column is meaningful, such as > a join where the grouping column is a foreign key that > references the primary key of another table. Although I don't follow your example, I suppose that if someone wanted to select a not-so-random arbitrary member of a group then this GROUP BY extension would do the trick. But I would favor an explicit approach that disallows this non-standard GROUP BY extension, and instead adds a new non-standard, easily understood aggregate function, such as: select arbitrary(a) from qqq group by b; that would give comparable functionality yet still provide the traditional error checking one would expect with GROUP BY SELECT statements. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Seems like a bug in the parser
On 8/23/06, Andrew McCollum <[EMAIL PROTECTED]> wrote: I find this feature useful, especially in queries which use aggregate functions, such as the following: SELECT sum(a) FROM tbl GROUP BY b The question should be what the compelling reason is to remove a useful feature. ... And *that* is exactly why Windows will always be full of security holes. -Jonathan - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Seems like a bug in the parser
I find this feature useful, especially in queries which use aggregate functions, such as the following: SELECT sum(a) FROM tbl GROUP BY b The question should be what the compelling reason is to remove a useful feature. -Andrew McCollum -Original Message- From: Kurt Welgehausen [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 23, 2006 7:02 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Seems like a bug in the parser > select a from qqq group by b; This question was discussed on the list a year or 2 ago. The column a in the simple query above is meaningless; it's an arbitrary value from each group. There are queries, however, where a non-grouped column is meaningful, such as a join where the grouping column is a foreign key that references the primary key of another table. I don't remember whether the '92 std calls this an error, but later stds allow the dbms to analyze such functional dependencies to decide whether to accept or reject a query with a non-grouped column in the column list. In SQLite you have to do the functional-dependency analysis yourself. Regards - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Seems like a bug in the parser
> select a from qqq group by b; This question was discussed on the list a year or 2 ago. The column a in the simple query above is meaningless; it's an arbitrary value from each group. There are queries, however, where a non-grouped column is meaningful, such as a join where the grouping column is a foreign key that references the primary key of another table. I don't remember whether the '92 std calls this an error, but later stds allow the dbms to analyze such functional dependencies to decide whether to accept or reject a query with a non-grouped column in the column list. In SQLite you have to do the functional-dependency analysis yourself. Regards - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Seems like a bug in the parser
Joe Wilson wrote: --- [EMAIL PROTECTED] wrote: SQLite accepts the above and does the right thing with it. It is the equivalent of saying: SELECT a FROM (SELECT a,b FROM qqq GROUP BY b); Not sure what you mean by the "right thing". It's not obvious why the rows returned by this GROUP BY are significant. sqlite> select a,b from qqq group by b; 2|9 3|10 -3|11 mysql> select a,b from qqq group by b; +--+--+ | a| b| +--+--+ | 4|9 | | 1| 10 | | 4| 11 | +--+--+ 3 rows in set (0.00 sec) postgresql says: ERROR: column "qqq.a" must appear in the GROUP BY clause or be used in an aggregate function oracle says: ORA-00979: non รจ un'espressione GROUP BY (in translation: this is not a GROUP BY expression) as you see, the two engines returning a result give different results on the same data inserted in the same order. as Joe, I also don't see which should be considered "the" right thing and would rather have an error message. what does SQL92 say? regards, Mario - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Seems like a bug in the parser
On 8/22/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: SQLite accepts the above and does the right thing with it. It is the equivalent of saying: SELECT a FROM (SELECT a,b FROM qqq GROUP BY b); The subquery here doesn't make any sense to me. How a single 'a' is chosen for the grouped by 'b' sets? I believe it's in SQL standard that only columns listed in the "group by" clause or aggregation expressions must be used. Basically, every expression in the column list must be uniquelly determined for a set of grouped rows. All other databases I know will complain if you give them this query. -- Alexei Alexandrov - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Seems like a bug in the parser
--- [EMAIL PROTECTED] wrote: > "Alexei Alexandrov" <[EMAIL PROTECTED]> wrote: > > I noticed something like a bug in the SQLite parser: queries with > > "group by" expression should accept only fields listed in the "group > > by" clause or aggregated fields (with sum(), max() etc). For example, > > given the table > > > > create table qqq (a text, b integer); > > > > the following query should not be accepted: > > > > select a from qqq group by b; > > > > but it is. > > SQLite accepts the above and does the right thing with it. > It is the equivalent of saying: > >SELECT a FROM (SELECT a,b FROM qqq GROUP BY b); Not sure what you mean by the "right thing". It's not obvious why the rows returned by this GROUP BY are significant. The SQLite query above is equivalent to this query: -- works in both SQLite and Oracle select qqq.a from qqq, (select distinct b from qqq) d where qqq.rowid = (select max(rowid) from qqq where qqq.b = d.b) order by qqq.b; which essentially returns the entry "a" for the rows corresponding to each unique "b" with the highest rowid. The "a" values returned are basically governed by initial insert order. CREATE TABLE qqq(a,b); INSERT INTO "qqq" VALUES(1, 10); INSERT INTO "qqq" VALUES(2, 10); INSERT INTO "qqq" VALUES(3, 10); INSERT INTO "qqq" VALUES(4, 11); INSERT INTO "qqq" VALUES(5, 11); INSERT INTO "qqq" VALUES(6, 10); INSERT INTO "qqq" VALUES(-7, 10); INSERT INTO "qqq" VALUES(3, 10); INSERT INTO "qqq" VALUES(-3, 11); INSERT INTO "qqq" VALUES(4, 9); INSERT INTO "qqq" VALUES(2, 9); sqlite> select * from qqq group by b; a|b 2|9 3|10 -3|11 sqlite> select qqq.* from qqq, (select distinct b from qqq) d where qqq.rowid = (select max(rowid) from qqq where qqq.b = d.b) order by qqq.b; a|b 2|9 3|10 -3|11 The same data, populated in different order: sqlite> drop table qqq; sqlite> CREATE TABLE qqq(a,b); sqlite> INSERT INTO "qqq" VALUES(2, 9); sqlite> INSERT INTO "qqq" VALUES(1, 10); sqlite> INSERT INTO "qqq" VALUES(3, 10); sqlite> INSERT INTO "qqq" VALUES(2, 10); sqlite> INSERT INTO "qqq" VALUES(-3, 11); sqlite> INSERT INTO "qqq" VALUES(3, 10); sqlite> INSERT INTO "qqq" VALUES(4, 9); sqlite> INSERT INTO "qqq" VALUES(4, 11); sqlite> INSERT INTO "qqq" VALUES(5, 11); sqlite> INSERT INTO "qqq" VALUES(6, 10); sqlite> INSERT INTO "qqq" VALUES(-7, 10); sqlite> select * from qqq group by b; 4|9 -7|10 5|11 sqlite> select qqq.* from qqq, (select distinct b from qqq) d where qqq.rowid = (select max(rowid) from qqq where qqq.b = d.b) order by qqq.b; 4|9 -7|10 5|11 Does anyone have a real world use for this GROUP BY extension? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Seems like a bug in the parser
"Alexei Alexandrov" <[EMAIL PROTECTED]> wrote: > I noticed something like a bug in the SQLite parser: queries with > "group by" expression should accept only fields listed in the "group > by" clause or aggregated fields (with sum(), max() etc). For example, > given the table > > create table qqq (a text, b integer); > > the following query should not be accepted: > > select a from qqq group by b; > > but it is. > SQLite accepts the above and does the right thing with it. It is the equivalent of saying: SELECT a FROM (SELECT a,b FROM qqq GROUP BY b); -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Seems like a bug in the parser
I noticed something like a bug in the SQLite parser: queries with "group by" expression should accept only fields listed in the "group by" clause or aggregated fields (with sum(), max() etc). For example, given the table create table qqq (a text, b integer); the following query should not be accepted: select a from qqq group by b; but it is. -- Alexei Alexandrov - To unsubscribe, send email to [EMAIL PROTECTED] -
