Re: [sqlite] BUG? sqlite not reporting error like "more than one row returned by a subquery used as an expression"
On Feb 20, 2013, at 9:35 PM, "Jay A. Kreibich"wrote: > Not covert... works as documented: "Let us be charitable, and call it a misleading feature" -- Larry Wall > Not random either... at least, not any more random than any other > query. Result order is never meaningful unless there is an > ORDER BY. Q.E.D. > As for "productive", I suppose that depends on if you want SQL to > find poorly thought out queries on behalf of the developer, or just > assume the developer knows what they're doing and do the best it can > with what it was given. That the problem right there: … " do the best it can with what it was given"… That's basically second guessing and is rather harmful. Just the opposite of the first assertion ( "assume the developer knows what they're doing" ). SQLite shouldn't assume, or guess, anything and just fail-fast instead. Everyone would be better off that way. > For good or bad, SQL is definitely a "shoot > yourself in the foot" language. Nah. No more or less than any other programmatic constructs. On the other hand, there is a clear tendency in SQLite for creative second guessing (scalar, group by, etc) or ignore issues altogether (constraints violations opacity). Just my 2¢ though. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BUG? sqlite not reporting error like "more than one row returned by a subquery used as an expression"
On Feb 20, 2013, at 9:29 PM, Richard Hippwrote: > On Wed, Feb 20, 2013 at 3:25 PM, Petite Abeille > wrote: > >> >> On Feb 20, 2013, at 2:15 PM, Richard Hipp wrote: >> >>> SQLite automatically adds a LIMIT 1 to a scalar subquery. >> >> Yeah… that's a bit of a death trap though… would be much more productive >> if SQLite would raise an exception instead of doing something covert and >> random... >> > > There are over one million applications that use SQLite as it is currently > implement. Perhaps you are right that the proposed behavior makes better > sense. (Or perhaps not - the case can be argued.) But how many of those > one million applications would be busted and need to be fixed? You want to > test them all? Well… considering that such applications are already broken as is… making such defect explicit might be beneficial overall… just imagine how many hidden, potentially dangerous, bugs SQLite could uncover in one swell swoop... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BUG? sqlite not reporting error like "more than one row returned by a subquery used as an expression"
On Wed, Feb 20, 2013 at 09:25:54PM +0100, Petite Abeille scratched on the wall: > On Feb 20, 2013, at 2:15 PM, Richard Hippwrote: > > > SQLite automatically adds a LIMIT 1 to a scalar subquery. > > Yeah? that's a bit of a death trap though? would be much more productive > if SQLite would raise an exception instead of doing something covert > and random... Not covert... works as documented: http://www.sqlite.org/lang_expr.html Scalar Subqueries A SELECT statement enclosed in parentheses may appear as a scalar quantity. A SELECT used as a scalar quantity must return a result set with a single column. The result of the expression is the value of the only column in the first row returned by the SELECT statement. If the SELECT yields more than one result row, all rows after the first are ignored. If the SELECT yields no rows, then the value of the expression is NULL. The LIMIT of a scalar subquery is always 1. Any other LIMIT value given in the SQL text is ignored. All types of SELECT statement, including aggregate and compound SELECT queries (queries with keywords like UNION or EXCEPT) are allowed as scalar subqueries. Not random either... at least, not any more random than any other query. Result order is never meaningful unless there is an ORDER BY. As for "productive", I suppose that depends on if you want SQL to find poorly thought out queries on behalf of the developer, or just assume the developer knows what they're doing and do the best it can with what it was given. For good or bad, SQL is definitely a "shoot yourself in the foot" language. -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] BUG? sqlite not reporting error like "more than one row returned by a subquery used as an expression"
On Wed, Feb 20, 2013 at 3:25 PM, Petite Abeillewrote: > > On Feb 20, 2013, at 2:15 PM, Richard Hipp wrote: > > > SQLite automatically adds a LIMIT 1 to a scalar subquery. > > Yeah… that's a bit of a death trap though… would be much more productive > if SQLite would raise an exception instead of doing something covert and > random... > There are over one million applications that use SQLite as it is currently implement. Perhaps you are right that the proposed behavior makes better sense. (Or perhaps not - the case can be argued.) But how many of those one million applications would be busted and need to be fixed? You want to test them all? -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BUG? sqlite not reporting error like "more than one row returned by a subquery used as an expression"
On Feb 20, 2013, at 2:15 PM, Richard Hippwrote: > SQLite automatically adds a LIMIT 1 to a scalar subquery. Yeah… that's a bit of a death trap though… would be much more productive if SQLite would raise an exception instead of doing something covert and random... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BUG? sqlite not reporting error like "more than one row returned by a subquery used as an expression"
On 20 Feb 2013, at 12:17pm, Marcin Kałużawrote: > sqlite> select * from t where v = (select v from t); > 1 There is no definition for what this means under SQL. Any SQL implementation might consider it an error, or always evaluate (item = list) as false, or do anything else. I don't think you can point to either postgres or SQLite as definitely being buggy on this one. By the way it is an error to use postgres as a model of correct behaviour about SQL. It has just as many weird characteristics as the other implementations of SQL I've seen. Since SQL is not procedurally specified there are many muddy areas in its definition. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BUG? sqlite not reporting error like "more than one row returned by a subquery used as an expression"
On Wed, Feb 20, 2013 at 7:17 AM, Marcin Kałużawrote: > We've encountered strange sqlite behavior: > > SQLite version 3.7.15.2 2013-01-09 11:53:05 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> create table t(v int4); > sqlite> insert into t values (1),(2),(3); > sqlite> select * from t where v = (select v from t); > 1 > sqlite> select * from t where v in (select v from t); > v > -- > 1 > 2 > 3 > > While on postgres it works like this (as far as I remember oracle does > this as well, and that's the way it should work imho): > mail=> create table t (v int4); > CREATE TABLE > mail=> insert into t values (1),(2),(3); > INSERT 0 3 > mail=> select * from t where v = (select v from t); > ERROR: more than one row returned by a subquery used as an expression > mail=> select * from t where v in (select v from t); > v > --- > 1 > 2 > 3 > (3 rows) > > Is this a bug, or a feature? > SQLite automatically adds a LIMIT 1 to a scalar subquery. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] BUG? sqlite not reporting error like "more than one row returned by a subquery used as an expression"
We've encountered strange sqlite behavior: SQLite version 3.7.15.2 2013-01-09 11:53:05 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table t(v int4); sqlite> insert into t values (1),(2),(3); sqlite> select * from t where v = (select v from t); 1 sqlite> select * from t where v in (select v from t); v -- 1 2 3 While on postgres it works like this (as far as I remember oracle does this as well, and that's the way it should work imho): mail=> create table t (v int4); CREATE TABLE mail=> insert into t values (1),(2),(3); INSERT 0 3 mail=> select * from t where v = (select v from t); ERROR: more than one row returned by a subquery used as an expression mail=> select * from t where v in (select v from t); v --- 1 2 3 (3 rows) Is this a bug, or a feature? Marcin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users