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
