Re: [sqlite] No error on selecting non-grouped column
On 9/14/2011 15:29, Jay A. Kreibich wrote: Most RDBMS systems will throw an error if you don't group or aggregate column references, but SQLite trust you to know what you're doing. Personally I've always found this to be very useful, as I often have queries that lead to great frustation on other systems. When I know a computed column or something similar is unique (or unique enough, such as only caps differences), it is nice to be able to keep the groupings simple. -j Thanks Jay and others for very thoughtful answers. My personal preference would be for a (perhaps optional) strict enforcement of the syntax, supplemented by two aggregate functions: choose_one() : Chooses one value, could be identical to current behavior, but make it explicit. all_identical() : Returns the value of the column if there is only one distinct value there. Otherwise it will throw an error. I find this use-case, which is the one Jay mentioned, very useful, but sometimes it is very important to guard against insane data. Having SQLite automatically figure out if a non-grouped column is actually guaranteed to be unique seems useful, but probably more involved than a pragma simply implementing this restriction in all cases. Anyway, since this is not something that can be changed currently, I'll find a way to work around this. Best, Magnus ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] No error on selecting non-grouped column
On Thu, Sep 15, 2011 at 07:00:43AM +0800, Mohd Radzi Ibrahim scratched on the wall: > > On 15-Sep-2011, at 2:55 AM, Magnus Thor Torfason wrote: > > > > > I then ran a query grouping employees by job: > > > > > select ename, job from emp group by job; > > "ENAME", "JOB" > > == > > "FORD", "ANALYST" > > "MILLER", "CLERK" > > "CLARK", "MANAGER" > > "KING", "PRESIDENT" > > "TURNER", "SALESMAN" > > > > Now, I get a list of the jobs, and a random selection of employees. I would > > have expected an error here. Of course, my actual > > It's not random selection, it's the last one from the list. Yes, but as always, "last" is a relative thing. Result sets have no set or defined ordering without an ORDER BY, and you cannot use an ORDER BY in this case, as it is applied after the GROUP BY operation. Adding an index, or future query optimizations may cause the order to change. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] No error on selecting non-grouped column
On 15-Sep-2011, at 2:55 AM, Magnus Thor Torfason wrote: > > I then ran a query grouping employees by job: > > > select ename, job from emp group by job; > "ENAME", "JOB" > == > "FORD", "ANALYST" > "MILLER", "CLERK" > "CLARK", "MANAGER" > "KING", "PRESIDENT" > "TURNER", "SALESMAN" > > Now, I get a list of the jobs, and a random selection of employees. I would > have expected an error here. Of course, my actual It's not random selection, it's the last one from the list. -radzi- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] No error on selecting non-grouped column
On Sep 14, 2011, at 9:27 PM, Darren Duncan wrote: > Petite Abeille wrote: >> On Sep 14, 2011, at 8:55 PM, Magnus Thor Torfason wrote: >>> Now, I get a list of the jobs, and a random selection of employees. I would >>> have expected an error here. Of course, my actual query was different (this >>> is based on the Oracle example data base from very old days), but it was >>> also much more complicated, so I did not notice the error until a bit of >>> fishing around. So getting an explicit error here would have made things >>> simpler. >>> Is there a way to do that? "PRAGMA strict" was one thing I thought about >>> looking for, but I did not find any such pragma. >> Well, this is one of these, hmmm, rather unwelcome features of SQLite. No way >> around it except vigilance :/ > > Actually, I think that this *is* welcome. Well, one person feature is another person bug :) To misquote Larry Wall: P1: I find this a interesting feature but it is not according to the common sense. Or is it a BUG? P2: Let's call it an accidental feature. :-) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] No error on selecting non-grouped column
On Wed, Sep 14, 2011 at 12:24:57PM -0700, Gerry Snyder scratched on the wall: > Would be pragma to reverse unordered selects show a different result? Very likely, yes. http://sqlite.org/pragma.html#pragma_reverse_unordered_selects -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] No error on selecting non-grouped column
On Wed, Sep 14, 2011 at 02:55:06PM -0400, Magnus Thor Torfason scratched on the wall: > I have this database of employees. A simple select looks like this: > Now, I get a list of the jobs, and a random selection of employees. > I would have expected an error here. Of course, my actual query was > different (this is based on the Oracle example data base from very > old days), but it was also much more complicated, so I did not > notice the error until a bit of fishing around. So getting an > explicit error here would have made things simpler. > > Is there a way to do that? "PRAGMA strict" was one thing I thought > about looking for, but I did not find any such pragma. As other have said, I'm not aware of anyway to force an error. Most RDBMS systems will throw an error if you don't group or aggregate column references, but SQLite trust you to know what you're doing. Personally I've always found this to be very useful, as I often have queries that lead to great frustation on other systems. When I know a computed column or something similar is unique (or unique enough, such as only caps differences), it is nice to be able to keep the groupings simple. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] No error on selecting non-grouped column
Petite Abeille wrote: On Sep 14, 2011, at 8:55 PM, Magnus Thor Torfason wrote: Now, I get a list of the jobs, and a random selection of employees. I would have expected an error here. Of course, my actual query was different (this is based on the Oracle example data base from very old days), but it was also much more complicated, so I did not notice the error until a bit of fishing around. So getting an explicit error here would have made things simpler. Is there a way to do that? "PRAGMA strict" was one thing I thought about looking for, but I did not find any such pragma. Well, this is one of these, hmmm, rather unwelcome features of SQLite. No way around it except vigilance :/ Actually, I think that this *is* welcome. For example, in situations like this: select a.foo, a.bar, sum(b.baz) from a inner join b using (quux) group by a.foo; Now say that "foo" is a primary or unique key of "a". We already know, then, that since we grouped by a key of a source table, that all other fields from that table have 1 distinct value per value of "foo", and so there is no reason to have to say "group by a.foo, a.bar" etc. As I said, this is *good*. Now if there is any reason to be more restrictive, it would be that one can't reference a field directly in the select list that isn't in the group by unless we are grouping by a key of the table that the fields in the select list are from, so we have this uniqueness guarantee. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] No error on selecting non-grouped column
Would be pragma to reverse unordered selects show a different result? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] No error on selecting non-grouped column
On 9/14/2011 2:55 PM, Magnus Thor Torfason wrote: I then ran a query grouping employees by job: select ename, job from emp group by job; "ENAME", "JOB" == "FORD", "ANALYST" "MILLER", "CLERK" "CLARK", "MANAGER" "KING", "PRESIDENT" "TURNER", "SALESMAN" Now, I get a list of the jobs, and a random selection of employees. I would have expected an error here. It's a SQLite-specific extension. Very useful in certain cases. So getting an explicit error here would have made things simpler. Is there a way to do that? None that I know of. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] No error on selecting non-grouped column
On Sep 14, 2011, at 8:55 PM, Magnus Thor Torfason wrote: > Now, I get a list of the jobs, and a random selection of employees. I would > have expected an error here. Of course, my actual query was different (this > is based on the Oracle example data base from very old days), but it was also > much more complicated, so I did not notice the error until a bit of fishing > around. So getting an explicit error here would have made things simpler. > > Is there a way to do that? "PRAGMA strict" was one thing I thought about > looking for, but I did not find any such pragma. Well, this is one of these, hmmm, rather unwelcome features of SQLite. No way around it except vigilance :/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] No error on selecting non-grouped column
I have this database of employees. A simple select looks like this: > select ename, job from emp order by job; "ENAME", "JOB" == "SCOTT", "ANALYST" "FORD", "ANALYST" "SMITH", "CLERK" "ADAMS", "CLERK" "JAMES", "CLERK" "MILLER", "CLERK" "JONES", "MANAGER" "BLAKE", "MANAGER" "CLARK", "MANAGER" "KING", "PRESIDENT" "ALLEN", "SALESMAN" "WARD", "SALESMAN" "MARTIN", "SALESMAN" "TURNER", "SALESMAN" I then ran a query grouping employees by job: > select ename, job from emp group by job; "ENAME", "JOB" == "FORD", "ANALYST" "MILLER", "CLERK" "CLARK", "MANAGER" "KING", "PRESIDENT" "TURNER", "SALESMAN" Now, I get a list of the jobs, and a random selection of employees. I would have expected an error here. Of course, my actual query was different (this is based on the Oracle example data base from very old days), but it was also much more complicated, so I did not notice the error until a bit of fishing around. So getting an explicit error here would have made things simpler. Is there a way to do that? "PRAGMA strict" was one thing I thought about looking for, but I did not find any such pragma. Best, Magnus ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
