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)
|
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Summary | Wrong result for query with outer join and IS NOT TRUE predicate in the where clause | Wrong result for query with outer join and IS NOT TRUE predicate in where clause |
| Labels | upstream |
| Fix Version/s | 5.5.65 [ 23402 ] | |
| Fix Version/s | 10.1.41 [ 23406 ] | |
| Fix Version/s | 10.2.26 [ 23409 ] | |
| Fix Version/s | 10.3.17 [ 23411 ] | |
| Fix Version/s | 10.4.7 [ 23720 ] | |
| Fix Version/s | 5.5 [ 15800 ] |
| Resolution | Fixed [ 1 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |
| Issue Type | Task [ 3 ] | Bug [ 1 ] |
| Workflow | MariaDB v3 [ 97568 ] | MariaDB v4 [ 156364 ] |