[sqlite] Select query becomes distinct on where matches unique with null value
On 2015-04-11 06:12 PM, Simon Slavin wrote: > On 11 Apr 2015, at 4:13pm, Keith Medcalf wrote: > >> Interestingly if you run analyze, it works properly ... > Oh my. I don't like the idea that ANALYZE changes the result set. > > Simon. Yes, that would be worrisome... but not to worry, the bug is in the index usage and analyze probably coaxed it into using another index that works - so not an ANALYZE bug. It's already checked in and getting fixed at: http://www.sqlite.org/src/info/e3b1f625518edc0e
[sqlite] Select query becomes distinct on where matches unique with null value
This problem is fixed on trunk. Thanks for the report. On 4/11/15, Mike Gladysch wrote: > Hi, > > I expected that SQLite misbehaves in current versions. Since 3.8.4.3 > (bundled with PHP 5.5.20 and PHP 5.6.4) all is still ok, but with next > update of PHP there is SQLite 3.8.7.2 bundled. > > Table: > 6 colums including an primary on col1, an unique on col2 (not null), an > unique on col3 nullable, col 4) and two more nullable colums. > > Data: > 1;1;null;test;null;null > 2;2;null;test;null;null > 3;3;null;test;null;null > 4;4;something;test;null;null > > Select col1, col2, col3, col4, col5, col6 > From table > Where col3 is null and col4 ='test' > > Delivers different results: > > 3.8.4.3: 3 rows (expected, ok) > 3.8.7.2: 1 row (wrong) > 3.8.9: 1 row (wrong) > > Tested with Windows shell binaries. > > Mike > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp drh at sqlite.org
[sqlite] Select query becomes distinct on where matches unique with null value
On 11 Apr 2015, at 4:13pm, Keith Medcalf wrote: > Interestingly if you run analyze, it works properly ... Oh my. I don't like the idea that ANALYZE changes the result set. Simon.
[sqlite] Select query becomes distinct on where matches unique with null value
Yep, it's a Bug since at least 3.8.8. Minimal Test-case to reproduce: create table t( c1 integer primary key, c2 integer, c3 integer, UNIQUE (c2, c3) ); insert into t values( 1,null,'a'); insert into t values( 2,null,'a'); insert into t values( 3,'xxx','a'); select * from t; -- c1 | c2 | c3 -- | - | - -- 1 | | a -- 2 | | a -- 3 | xxx | a select * from t where c2 is null and c3='a'; -- c1 | c2 | c3 -- | --- | - -- 1 | | a SQLite implements a Unique constraint as one that regards NULL values as different to all other values (including other NULL values), as does Postgres and MySQL (Some others don't). I am guessing the SQLite engine hits the Unique key on a lookup and doesn't check if there are any other values that satisfies the WHERE clause if the where clause seems specific (as in this case). It should change the behaviour when a NULL check is included. On 2015-04-11 08:07 AM, Mike Gladysch wrote: > Hi, > > I expected that SQLite misbehaves in current versions. Since 3.8.4.3 (bundled > with PHP 5.5.20 and PHP 5.6.4) all is still ok, but with next update of PHP > there is SQLite 3.8.7.2 bundled. > > Table: > 6 colums including an primary on col1, an unique on col2 (not null), an > unique on col3 nullable, col 4) and two more nullable colums. > > Data: > 1;1;null;test;null;null > 2;2;null;test;null;null > 3;3;null;test;null;null > 4;4;something;test;null;null > > Select col1, col2, col3, col4, col5, col6 > From table > Where col3 is null and col4 ='test' > > Delivers different results: > > 3.8.4.3: 3 rows (expected, ok) > 3.8.7.2: 1 row (wrong) > 3.8.9: 1 row (wrong) > > Tested with Windows shell binaries. > > Mike
[sqlite] Select query becomes distinct on where matches unique with null value
Mike Gladysch wrote: > Select col1, col2, col3, col4, col5, col6 > From table > Where col3 is null and col4 ='test' That is not valid SQL. > 3.8.4.3: 3 rows (expected, ok) > 3.8.7.2: 1 row (wrong) How to reproduce: create table t(x, y, unique(x, y)); insert into t values(null, 1); insert into t values(null, 1); select * from t where x is null and y = 1; |1 Regards, Clemens
[sqlite] Select query becomes distinct on where matches unique with null value
On 11 April 2015 at 07:07, Mike Gladysch wrote: > Hi, . > Data: > 1;1;null;test;null;null > 2;2;null;test;null;null > 3;3;null;test;null;null > 4;4;something;test;null;null > > Select col1, col2, col3, col4, col5, col6 > From table > Where col3 is null and col4 ='test' > > Delivers different results: > > 3.8.4.3: 3 rows (expected, ok) > 3.8.7.2: 1 row (wrong) > 3.8.9: 1 row (wrong) > > Tested with Windows shell binaries. > > Mike So I tried this: SQLite version 3.8.9 2015-04-08 12:16:33 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> sqlite> create table t( c1 integer primary key, c2 integer unique not null, c3 integer unique, c4 integer, c5 integer, c6 integer ); sqlite> insert into t values( 1,1,null,'test',null,null); sqlite> insert into t values( 2,2,null,'test',null,null); sqlite> insert into t values( 3,3,null,'test',null,null); sqlite> insert into t values( 4,4,'something','test',null,null); sqlite> select c1,c2,c3,c4,c5,c6 from t where c3 is null and c4='test'; 1|1||test|| 2|2||test|| 3|3||test|| sqlite> Am I doing anything different from you? Regards, Simon
[sqlite] Select query becomes distinct on where matches unique with null value
Interestingly if you run analyze, it works properly ... --- Theory is when you know everything but nothing works. Practice is when everything works but no one knows why. Sometimes theory and practice are combined: nothing works and no one knows why. >-Original Message- >From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- >bounces at mailinglists.sqlite.org] On Behalf Of R.Smith >Sent: Saturday, 11 April, 2015 06:09 >To: sqlite-users at mailinglists.sqlite.org >Subject: Re: [sqlite] Select query becomes distinct on where matches >unique with null value > >Yep, it's a Bug since at least 3.8.8. > >Minimal Test-case to reproduce: > >create table t( > c1 integer primary key, > c2 integer, > c3 integer, > UNIQUE (c2, c3) >); >insert into t values( 1,null,'a'); >insert into t values( 2,null,'a'); >insert into t values( 3,'xxx','a'); > >select * from t; > -- c1 | c2 | c3 > -- | - | - > -- 1 | | a > -- 2 | | a > -- 3 | xxx | a > >select * from t where c2 is null and c3='a'; > -- c1 | c2 | c3 > -- | --- | - > -- 1 | | a > > >SQLite implements a Unique constraint as one that regards NULL values as >different to all other values (including other NULL values), as does >Postgres and MySQL (Some others don't). > >I am guessing the SQLite engine hits the Unique key on a lookup and >doesn't check if there are any other values that satisfies the WHERE >clause if the where clause seems specific (as in this case). It should >change the behaviour when a NULL check is included. > > > >On 2015-04-11 08:07 AM, Mike Gladysch wrote: >> Hi, >> >> I expected that SQLite misbehaves in current versions. Since 3.8.4.3 >(bundled with PHP 5.5.20 and PHP 5.6.4) all is still ok, but with next >update of PHP there is SQLite 3.8.7.2 bundled. >> >> Table: >> 6 colums including an primary on col1, an unique on col2 (not null), an >unique on col3 nullable, col 4) and two more nullable colums. >> >> Data: >> 1;1;null;test;null;null >> 2;2;null;test;null;null >> 3;3;null;test;null;null >> 4;4;something;test;null;null >> >> Select col1, col2, col3, col4, col5, col6 >> From table >> Where col3 is null and col4 ='test' >> >> Delivers different results: >> >> 3.8.4.3: 3 rows (expected, ok) >> 3.8.7.2: 1 row (wrong) >> 3.8.9: 1 row (wrong) >> >> Tested with Windows shell binaries. >> >> Mike > >___ >sqlite-users mailing list >sqlite-users at mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Select query becomes distinct on where matches unique with null value
Hi, I expected that SQLite misbehaves in current versions. Since 3.8.4.3 (bundled with PHP 5.5.20 and PHP 5.6.4) all is still ok, but with next update of PHP there is SQLite 3.8.7.2 bundled. Table: 6 colums including an primary on col1, an unique on col2 (not null), an unique on col3 nullable, col 4) and two more nullable colums. Data: 1;1;null;test;null;null 2;2;null;test;null;null 3;3;null;test;null;null 4;4;something;test;null;null Select col1, col2, col3, col4, col5, col6 >From table Where col3 is null and col4 ='test' Delivers different results: 3.8.4.3: 3 rows (expected, ok) 3.8.7.2: 1 row (wrong) 3.8.9: 1 row (wrong) Tested with Windows shell binaries. Mike
