[sqlite] Select query becomes distinct on where matches unique with null value

[Simon Davies]

On 11 April 2015 at 07:07, Mike Gladysch <mail at mike-gladysch.de> 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