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

Reply via email to