[#MDEV-19790] Wrong result for query with outer join and IS NOT TRUE predicate in where clause

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)

[MDEV-19790] Wrong result for query with outer join and IS NOT TRUE predicate in where clause Created: 2019-06-17 Updated: 2020-12-19 Resolved: 2019-06-26
Status:	Closed
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	None
Fix Version/s:	10.2.26, 5.5.65, 10.1.41, 10.3.17, 10.4.7

[MDEV-19790] Wrong result for query with outer join and IS NOT TRUE predicate in where clause Created: 2019-06-17 Updated: 2020-12-19 Resolved: 2019-06-26