On Thu, Jun 02, 2011 at 11:30:12AM -0700, Roger Binns scratched on the wall: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > On 06/02/2011 01:18 AM, Dagdamor wrote: > > Patrick Earl <pate...@patearl.net> ?????(?) ? ????? ?????? Wed, 01 Jun 2011 > > 22:24:10 +0600: > > > >> Is there another work-around? > > > > Yes. Do not feed SQLite with stupid queries. ;) > > The query was actually correct and not stupid in any way.
> > If, according to SQLite standards, LIMIT shouldn't be specified in > > that subquery, then you shouldn't use it. > > SQLite was actually in error IMHO. The subquery is the right hand side of > an equality and SQLite was looking for a single row/value. As documented. A sub-query used as an expression generates a single scalar value. As such, it is expected to return only one column, and only the first row is used: http://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. All types of SELECT statement, including aggregate and compound SELECT queries (queries with keywords like UNION or EXCEPT) are allowed as scalar subqueries. If you want a sub-query to act as an IN operator that can deal with more than one value on the right hand side, then you actually need to use the IN operator, not an equality operator. When using IN, the LIMIT becomes meaningful, and I'm sure it won't be optimized out. See "The IN and NOT IN opeators" on the above URL. > SQLite ignored the LIMIT and hard coded the value one. This > "optimisation" resulted in the binding specifying that limit to > be optimized out causing the whole problem. I suppose it would be technically correct to allow a LIMIT to 0, but that seems like a highly questionable approach. Any parameter value outside of 0 and 1 *is* non-sense and should be dutifully ignored and/or corrected-- or the whole statement should be considered invalid and an error thrown. While the phantom parameter issue might be worth addressing, in this specific case I think it is fair to call the query incorrect. -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
