Details
Major Description
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; |
MariaDB [test]> select * from t1 left join t2 on a=b;
|
+------+------+
|
| a | b |
|
+------+------+
|
| 7 | 7 |
|
| 3 | 3 |
|
| 1 | NULL |
|
+------+------+
|
3 rows in set (0.00 sec)
|
|
|
MariaDB [test]> 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)
|
MariaDB [test]> 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 8.0 (and most probably all prior versions) returns the same result sets for the above queries.
Postgres returns correct results:
postgres=# select * from t1 left join t2 on a=b;
|
a | b
|
---+---
|
1 |
|
3 | 3
|
7 | 7
|
(3 rows)
|
|
|
postgres=# select * from t1 left join t2 on a=b where (b > 1) is not true;
|
a | b
|
---+---
|
1 |
|
(1 row)
|