MySQL Bugs: #67732: WHERE var IS NOT TRUE yields too little

Bug #67732	WHERE var IS NOT TRUE yields too little
Submitted:	27 Nov 2012 19:49	Modified:	11 Nov 2019 9:44
Reporter:	halászsándor halászsándor	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.5.8, 5.5.30, 5.7.1, 5.0.97, 5.1.67	OS:	Windows (Vista)
Assigned to:		CPU Architecture:	Any

Description:
mysql> desc xm;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| MemberID | int(11) | NO   | PRI | NULL    |       |
+----------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> desc wrong;
+------------+------------+------+-----+---------+-------+
| Field      | Type       | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| MemberID   | int(11)    | NO   | PRI | NULL    |       |
| withdrawn  | tinyint(1) | NO   |     | 0       |       |
| misaddress | tinyint(1) | NO   |     | 0       |       |
+------------+------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> select * from xm;
+----------+
| MemberID |
+----------+
|      892 |
|      905 |
|      965 |
|     1061 |
|     1067 |
|     1867 |
|     2062 |
|     2075 |
|     2081 |
|     2087 |
|     2161 |
|     2165 |
|     2321 |
|     2330 |
|     2335 |
|     2339 |
|     2341 |
|     2350 |
|     2351 |
|     2354 |
|     2377 |
|     2389 |
|     2390 |
|     2395 |
+----------+
24 rows in set (0.00 sec)

mysql> select * from wrong;
+----------+-----------+------------+
| MemberID | withdrawn | misaddress |
+----------+-----------+------------+
|      892 |         0 |          1 |
|     1061 |         0 |          1 |
|     2081 |         0 |          1 |
|     2165 |         1 |          0 |
|     2335 |         1 |          0 |
|     2339 |         0 |          1 |
|     2354 |         0 |          1 |
|     2377 |         1 |          0 |
|     2395 |         0 |          1 |
+----------+-----------+------------+
9 rows in set (0.00 sec)

********** The following yields too few rows; the right output follows

mysql> select * from xm natural left join wrong where withdrawn is not true;
+----------+-----------+------------+
| MemberID | withdrawn | misaddress |
+----------+-----------+------------+
|      892 |         0 |          1 |
|     1061 |         0 |          1 |
|     2081 |         0 |          1 |
|     2339 |         0 |          1 |
|     2354 |         0 |          1 |
|     2395 |         0 |          1 |
+----------+-----------+------------+
6 rows in set (0.00 sec)

******** This following, I believe, is the right output for the foregoing:

mysql> select * from xm natural left join wrong where withdrawn is not true or withdrawn is null;
+----------+-----------+------------+
| MemberID | withdrawn | misaddress |
+----------+-----------+------------+
|      892 |         0 |          1 |
|      905 |      NULL |       NULL |
|      965 |      NULL |       NULL |
|     1061 |         0 |          1 |
|     1067 |      NULL |       NULL |
|     1867 |      NULL |       NULL |
|     2062 |      NULL |       NULL |
|     2075 |      NULL |       NULL |
|     2081 |         0 |          1 |
|     2087 |      NULL |       NULL |
|     2161 |      NULL |       NULL |
|     2321 |      NULL |       NULL |
|     2330 |      NULL |       NULL |
|     2339 |         0 |          1 |
|     2341 |      NULL |       NULL |
|     2350 |      NULL |       NULL |
|     2351 |      NULL |       NULL |
|     2354 |         0 |          1 |
|     2389 |      NULL |       NULL |
|     2390 |      NULL |       NULL |
|     2395 |         0 |          1 |
+----------+-----------+------------+
21 rows in set (0.00 sec)

How to repeat:
Make up tables and a query as in the description.

create temporary table xm (MemberID INT PRIMARY KEY);
insert into xm value
(892),(905
),(965
),(1061
),(1067
),(1867
),(2062
),(2075
),(2081
),(2087
),(2161
),(2165
),(2321
),(2330
),(2335
),(2339
),(2341
),(2350
),(2351
),(2354
),(2377
),(2389
),(2390
),(2395);

create temporary table wrong (MemberID INT PRIMARY KEY, withdrawn BOOL NOT NULL DEFAULT 0, misaddress BOOL NOT NULL DEFAULT 0);

insert into wrong value
( 892,0, 1),
(1061,0, 1),
(2081,0, 1),
(2165,1, 0),
(2335,1, 0),
(2339,0, 1),
(2354,0, 1),
(2377,1, 0),
(2395,0, 1);

select * from xm natural left join wrong where withdrawn is not true;

Suggested fix:
After WHERE, NULL, too, IS NOT TRUE

Thank you for the report.

Verified as described.

Either this query should return 21 rows or following should return different results:

select null is true, null is not true;
null is true	null is not true
0	1

FWIW PostgreSQL returns a 21 row result set, and

postgres=# select null is true, null is not true;
 ?column? | ?column? 
----------+----------
 f        | t
(1 row)

I experienced similar problems.

I posted this http://bugs.mysql.com/bug.php?id=70986 (but in my case the 'reduced test case' was wrong/misunderstood).

There is really a need for elaboration in http://dev.mysql.com/doc/refman/5.6/en/working-with-null.html, I believe.

Yes, some extra clarification on e.g.

  NULL IS NOT TRUE   

not being the same as 

  NULL != TRUE

would be good, too

http://dev.mysql.com/doc/refman/5.6/en/comparison-operators.html#operator_is

isn't really helpful either ... (and hard to find, too, when just searching for "IS NOT TRUE")

And this 'seemingly paradox' as well: SELECT NOT NULL -- returns NULL

I think it should be stated that when NULL is used in boolean comparisons and expressions, 'boolean logic' evaluation is extended to 'ternary boolean logic' evaluation (with a good reference).

Try this:
create temporary table a (b int,c int);
create temporary table d (e int,f int);
insert into a value (0,1),(1,2);
insert into d value (0,3);
select * from a;
select * from d;
select * from a left join d on b=e;
select * from a left join d on b=e where f = 3 is null;
select * from a left join d on b=e where f = 3 is not true;
select * from a left join d on b=e where f = 3 is true;
insert into d value (4,NULL);
select * from d where f = 3 is not true;

The outcome of the last clearly shows that MySQL mistreats "IS [NOT] TRUE/FALSE" when that is apply'd to generated NULL from a left or right join.

The query shown in the test case returns the expected 21 rows if we change WHERE to HAVING.

mysql> select *, withdrawn is null, withdrawn is true, withdrawn is not true from xm natural left join wrong HAVING withdrawn is not true;
+----------+-----------+------------+-------------------+-------------------+-----------------------+
| MemberID | withdrawn | misaddress | withdrawn is null | withdrawn is true | withdrawn is not true |
+----------+-----------+------------+-------------------+-------------------+-----------------------+
|      892 |         0 |          1 |                 0 |                 0 |                     1 |
|      905 |      NULL |       NULL |                 1 |                 0 |                     1 |
|      965 |      NULL |       NULL |                 1 |                 0 |                     1 |
|     1061 |         0 |          1 |                 0 |                 0 |                     1 |
|     1067 |      NULL |       NULL |                 1 |                 0 |                     1 |
|     1867 |      NULL |       NULL |                 1 |                 0 |                     1 |
|     2062 |      NULL |       NULL |                 1 |                 0 |                     1 |
|     2075 |      NULL |       NULL |                 1 |                 0 |                     1 |
|     2081 |         0 |          1 |                 0 |                 0 |                     1 |
|     2087 |      NULL |       NULL |                 1 |                 0 |                     1 |
|     2161 |      NULL |       NULL |                 1 |                 0 |                     1 |
|     2321 |      NULL |       NULL |                 1 |                 0 |                     1 |
|     2330 |      NULL |       NULL |                 1 |                 0 |                     1 |
|     2339 |         0 |          1 |                 0 |                 0 |                     1 |
|     2341 |      NULL |       NULL |                 1 |                 0 |                     1 |
|     2350 |      NULL |       NULL |                 1 |                 0 |                     1 |
|     2351 |      NULL |       NULL |                 1 |                 0 |                     1 |
|     2354 |         0 |          1 |                 0 |                 0 |                     1 |
|     2389 |      NULL |       NULL |                 1 |                 0 |                     1 |
|     2390 |      NULL |       NULL |                 1 |                 0 |                     1 |
|     2395 |         0 |          1 |                 0 |                 0 |                     1 |
+----------+-----------+------------+-------------------+-------------------+-----------------------+

Also if we wrap the join in a derived-table subquery, we get the same 21 row result, even if we use WHERE.

mysql> select *, withdrawn is null, withdrawn is true, withdrawn is not true from (select * from xm natural left join wrong) as t  WHERE withdrawn is not true;
<same as above>

It's as if the NULL resulting from an outer join is treated differently from a NULL after being processed by the select-list.

One more clue: If we insert a row with a NULL, this is treated as expected.

mysql> insert into wrong values (905,null,1);

mysql> select * from xm natural left join wrong where withdrawn is not true;
+----------+-----------+------------+
| MemberID | withdrawn | misaddress |
+----------+-----------+------------+
|      892 |         0 |          1 |
|      905 |      NULL |          1 |
|     1061 |         0 |          1 |
|     2081 |         0 |          1 |
|     2339 |         0 |          1 |
|     2354 |         0 |          1 |
|     2395 |         0 |          1 |
+----------+-----------+------------+

So a real NULL appearing in a row matching the join does satisfy the condition IS NOT TRUE.

This is further evidence that the missing data in the result of an outer join is something different from a concrete NULL.

Posted by developer:
 
Fixed in 8.0.17 by

WL#4245 Convert NOT IN and NOT EXISTS in WHERE/ON to an antijoin