Description:
As reported by Igor Babaev elsewhere, the problem manifests itself when running queries with IS NOT TRUE or IS NOT FALSE predicates in their where clauses. The following simple test case can demonstrate the problem in 5.5 and all upper versions:
create table t1 (a int);
create table t2 (b int);
insert into t1 values (3), (7), (1);
insert into t2 values (7), (4), (3);
select * from t1 left join t2 on a=b;
select * from t1 left join t2 on a=b where (b > 3) is not true;
select * from t1 left join t2 on a=b where (b > 3) is not false;
Note that row (1, NULL) in left join results:
mysql> select * from t1 left join t2 on a=b;
+------+------+
| a | b |
+------+------+
| 7 | 7 |
| 3 | 3 |
| 1 | NULL |
+------+------+
3 rows in set (0.00 sec)
satisfies this kind of WHERE conditions, as NULL is neither TRUE nor FALSE:
mysql> select null is not false;
+-------------------+
| null is not false |
+-------------------+
| 1 |
+-------------------+
1 row in set (0.00 sec)
mysql> select null is not true;
+------------------+
| null is not true |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
PosgreSQL returns correct result for this test.
How to repeat:
[openxs@fc29 8.0]$ bin/mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.16 Source distribution
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> drop table t1, t2;
ERROR 1051 (42S02): Unknown table 'test.t1,test.t2'
mysql> create table t1 (a int);
Query OK, 0 rows affected (0.42 sec)
mysql> create table t2 (b int);
Query OK, 0 rows affected (0.55 sec)
mysql> insert into t1 values (3), (7), (1);
Query OK, 3 rows affected (0.10 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into t2 values (7), (4), (3);
Query OK, 3 rows affected (0.08 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t1 left join t2 on a=b;
+------+------+
| a | b |
+------+------+
| 7 | 7 |
| 3 | 3 |
| 1 | NULL |
+------+------+
3 rows in set (0.00 sec)
mysql> select * from t1 left join t2 on a=b where (b > 3) is not true;
+------+------+
| a | b |
+------+------+
| 3 | 3 |
+------+------+
1 row in set (0.00 sec)
mysql> select * from t1 left join t2 on a=b where (b > 3) is not false;
+------+------+
| a | b |
+------+------+
| 7 | 7 |
+------+------+
1 row in set (0.00 sec)
mysql> select * from t1 left join t2 on a=b where (b > 1) is not true;
Empty set (0.00 sec)
mysql> select null is not false;
+-------------------+
| null is not false |
+-------------------+
| 1 |
+-------------------+
1 row in set (0.00 sec)
mysql> select null is not true;
+------------------+
| null is not true |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
Suggested fix:
Process IS NOT RTUE and IS NOT FALSE conditions properly?
