Re: [sqlite] Bug in sqlite3_bind_parameter_name

2011-06-02 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 06/02/2011 07:09 PM, Igor Tandetnik wrote: > For a subselect like the one in question, SQLite picks the value from > the first row - whichever row this happens to be - and ignores the rest. > Providing an explicit LIMIT 1, or for that matter

Re: [sqlite] Bug in sqlite3_bind_parameter_name

2011-06-02 Thread Igor Tandetnik
On 6/2/2011 9:57 PM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 06/02/2011 12:31 PM, Jay A. Kreibich wrote: >>I suppose it would be technically correct to allow a LIMIT to 0, but >>that seems like a highly questionable approach. Any parameter value >>

Re: [sqlite] Bug in sqlite3_bind_parameter_name

2011-06-02 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 06/02/2011 12:31 PM, Jay A. Kreibich wrote: > 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

Re: [sqlite] Bug in sqlite3_bind_parameter_name

2011-06-02 Thread Dagdamor
Roger Binns писал(а) в своём письме Fri, 03 Jun 2011 00:30:12 +0600: > 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. The query > generation code specifically added a LIMIT to ensure

Re: [sqlite] Bug in sqlite3_bind_parameter_name

2011-06-02 Thread Jay A. Kreibich
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 ?(?) ? ? ?? Wed, 01 Jun 2011 > > 22:24:10 +0600: > > > >> Is there

Re: [sqlite] Bug in sqlite3_bind_parameter_name

2011-06-02 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 06/02/2011 01:18 AM, Dagdamor wrote: > Patrick Earl писал(а) в своём письме 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

Re: [sqlite] Bug in sqlite3_bind_parameter_name

2011-06-02 Thread Dagdamor
Patrick Earl писал(а) в своём письме Wed, 01 Jun 2011 22:24:10 +0600: > Is there another work-around? Yes. Do not feed SQLite with stupid queries. ;) If, according to SQLite standards, LIMIT shouldn't be specified in that subquery, then you shouldn't use it. Especially

Re: [sqlite] Bug in sqlite3_bind_parameter_name

2011-06-01 Thread Patrick Earl
That's awesome. Thanks so much. :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in sqlite3_bind_parameter_name

2011-06-01 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 06/01/2011 06:42 PM, Patrick Earl wrote: > So, to move forward on this bug a decision needs to be made at what > level it will be handled. > > 1. At the SQLite level. That is exactly what the team did. See the timeline:

Re: [sqlite] Bug in sqlite3_bind_parameter_name

2011-06-01 Thread Patrick Earl
So, to move forward on this bug a decision needs to be made at what level it will be handled. 1. At the SQLite level. 2. At the provider level (ex. System.Data.SQLite, Python providers, etc.) 3. At the user level (ex. NHibernate, Entity Framework, User Applications, etc.) Doing it in #3 would

Re: [sqlite] Bug in sqlite3_bind_parameter_name

2011-06-01 Thread Stephan Beal
On Wed, Jun 1, 2011 at 6:24 PM, Patrick Earl wrote: > From these, the "best" solution seems to be re-tokenizing the sql in > the provider. Is there another work-around? It seems that > implementing this at the database level would be the most efficient > approach.

Re: [sqlite] Bug in sqlite3_bind_parameter_name

2011-06-01 Thread Patrick Earl
On Wed, Jun 1, 2011 at 12:32 AM, Roger Binns wrote: > It does seem to me that this specific example is "wrong".  Selects return > zero or more rows yet the equality wants exactly one value.  I would expect > an error rather an implied "LIMIT 1" should there be anything

Re: [sqlite] Bug in sqlite3_bind_parameter_name

2011-06-01 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 >> It's been that way for 6 years, and nobody has noticed until Patrick >> complained earlier today. Schrödinbug - http://en.wikipedia.org/wiki/Unusual_software_bug#Schr.C3.B6dinbug >> Hence, the fix is a little trickier than it might seem at first

Re: [sqlite] Bug in sqlite3_bind_parameter_name

2011-05-31 Thread Nico Williams
You could set a flag nothing that parameters have been optimized out, so the statement author can be warned. Where to encode the flag? Maybe in some opcode's unused parameter? Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Bug in sqlite3_bind_parameter_name

2011-05-31 Thread Richard Hipp
On Tue, May 31, 2011 at 8:31 PM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 05/31/2011 05:00 AM, Richard Hipp wrote: > > Any parameter that is optimized out > > becomes an anonymous parameter for which sqlite3_bind_parameter_name > returns

Re: [sqlite] Bug in sqlite3_bind_parameter_name

2011-05-31 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/31/2011 05:00 AM, Richard Hipp wrote: > Any parameter that is optimized out > becomes an anonymous parameter for which sqlite3_bind_parameter_name returns > NULL. This is extremely annoying behaviour, unexpected and non-obvious. The developer

Re: [sqlite] Bug in sqlite3_bind_parameter_name

2011-05-31 Thread Patrick Earl
I just had an amusing realization... if SQLite itself won't change, then the problem falls on the shoulders of System.Data.SQLite, which is incidentally also handled by this mailing list. It's ultimately this code that is causing the problem because of the case mentioned here: private void

Re: [sqlite] Bug in sqlite3_bind_parameter_name

2011-05-31 Thread Patrick Earl
The generated limit parameter does have a value of 1, so it's a valid query. It's SQLite that has taken this valid query with a valid parameter value of 1 and has exposed its internal implementation details by removing it and causing additional work-arounds in parameter binding. It's possible

Re: [sqlite] Bug in sqlite3_bind_parameter_name

2011-05-31 Thread Jan Hudec
On Tue, May 31, 2011 at 08:00:40 -0400, Richard Hipp wrote: > On Mon, May 30, 2011 at 11:27 PM, Patrick Earl wrote: > > SELECT this_.studentId as studentId143_0_, > > this_.Name as Name143_0_, > > this_.address_city as address3_143_0_, > > this_.address_state as

Re: [sqlite] Bug in sqlite3_bind_parameter_name

2011-05-31 Thread Stephan Beal
On Tue, May 31, 2011 at 2:00 PM, Richard Hipp wrote: > The LIMIT in a scalar subquery is always ignored. A scalar subquery > operates with a LIMIT of 1 regardless of any LIMIT that you might specify. > That means the @p1 is optimized out - it does not appear in the generated >

Re: [sqlite] Bug in sqlite3_bind_parameter_name

2011-05-31 Thread Richard Hipp
On Mon, May 30, 2011 at 11:27 PM, Patrick Earl wrote: > Greetings! > > I have the following query: > > SELECT this_.studentId as studentId143_0_, this_.Name as Name143_0_, > this_.address_city as address3_143_0_, this_.address_state as > address4_143_0_,

[sqlite] Bug in sqlite3_bind_parameter_name

2011-05-30 Thread Patrick Earl
Greetings! I have the following query: SELECT this_.studentId as studentId143_0_, this_.Name as Name143_0_, this_.address_city as address3_143_0_, this_.address_state as address4_143_0_, this_.preferredCourseCode as preferre5_143_0_ FROM Student this_ WHERE this_.Name = (SELECT this_0_.Name as