not with "is not null" inconsistent?

[russellr]

Am I missing something obvious, or is this bug...

    (data = 15 and data is not null)
    not (data = 15 and data is not null)

should be opposites.

However, the following SQL script shows this not to be the case:

        drop table if exists bugtest;
        create table bugtest (id int not null auto_increment primary key,data smallint);
        insert into bugtest values (NULL,10);
        insert into bugtest values (NULL,20);
        insert into bugtest values (NULL,30);
        insert into bugtest values (NULL,40);
        insert into bugtest values (NULL,50);
        insert into bugtest values (NULL,NULL);

        select * from bugtest;

        select count(*) from bugtest where (data = 15 and data is not null);
        select count(*) from bugtest where not (data = 15 and data is not null);
        select count(*) from bugtest where (data <> 15 or data is null);
        drop table bugtest;

The second select should return "6" not "5".

Note that the third select is logically identical to the second select according to DeMorgan's Law. And it returns "6", correctly.

 

[piti]

maybe this is the prob:

Logical NOT. Returns 1 if the argument is 0, otherwise returns 0. Exception: NOT NULL returns NULL

 

[swampBoogie]

russelr:

I agree with you. It seems like Mysql doesn't handle three valued logic properly.

 

[likelylad]

What results happen when you have the following

select count(*) from bugtest where (data != 15 and data is null);

 

[russellr]

Thanks for the replies.

To answer "likelylad", this query:

    select count(*) from bugtest where (data <> 15 and data is null)

returns 0. Which is correct, because &quot;NULL <> 15&quot; is FALSE.

Here's how to write query 2, so it works!!!

    select count(*) from bugtest where not (data is not null 
and data = 15)

In other words, just reverse the AND clause.

FYI, here's the final test script, and its output:

    drop table if exists bugtest;
    create table bugtest (id int not null auto_increment primary key,data smallint);
    insert into bugtest values (NULL,10);
    insert into bugtest values (NULL,20);
    insert into bugtest values (NULL,30);
    insert into bugtest values (NULL,40);
    insert into bugtest values (NULL,50);
    insert into bugtest values (NULL,NULL);

    select * from bugtest;

    select count(*) from bugtest where (data = 15 and data is not null);
    select count(*) from bugtest where not (data = 15 and data is not null);
    select count(*) from bugtest where not (data is not null and data = 15);
    select count(*) from bugtest where (data <> 15 or data is null);
    select count(*) from bugtest where (data <> 15 and data is null);
    drop table bugtest;

OUTPUT:

--------------
drop table if exists bugtest
--------------
--------------
create table bugtest (id int not null auto_increment primary key,data smallint)
--------------
--------------
insert into bugtest values (NULL,10)
--------------
--------------
insert into bugtest values (NULL,20)
--------------
--------------
insert into bugtest values (NULL,30)
--------------
--------------
insert into bugtest values (NULL,40)
--------------
--------------
insert into bugtest values (NULL,50)
--------------
--------------
insert into bugtest values (NULL,NULL)
--------------
--------------
select * from bugtest
--------------
id      data
1       10
2       20
3       30
4       40
5       50
6       NULL
--------------
select count(*) from bugtest where (data = 15 and data is not null)
--------------
count(*)
0
--------------
select count(*) from bugtest where not (data = 15 and data is not null)
--------------
count(*)
5
--------------
select count(*) from bugtest where not (data is not null and data = 15)
--------------
count(*)
6
--------------
select count(*) from bugtest where (data <> 15 or data is null)
--------------
count(*)
6
--------------
select count(*) from bugtest where (data <> 15 and data is null)
--------------
count(*)
0
--------------
drop table bugtest
--------------