Details
- 
    Bug 
- 
    Status: Confirmed (View Workflow)
- 
    Major 
- 
    Resolution: Unresolved
- 
    11.3.2, 11.4.1, 10.6, 10.11, 10.4(EOL), 10.5(EOL), 11.0(EOL), 11.1(EOL), 11.2(EOL), 11.3(EOL)
- 
    None
- 
    None
- 
    Run on Docker container mariadb:latest and mariadb:11.4-rc
Description
Consider the below queries, it is unexpected that the third query return an empty set.
The `RIGHT JOIN` query returns `NULL 0`. The predicate `NOT (t1.c0 IS TRUE)` should be evaluated to `1`, since `t1.c0` is `NULL`, `t1.c0 IS TRUE` is `0`, and thus the whole expression should be evaluated to `1`, which also shown in the second query. The third query should then return `NULL 0` since the where predicate is `1`.
MySQL could return the expected result.
| DROP DATABASE db0; | 
| CREATE DATABASE db0; | 
| USE db0; | 
|  | 
| CREATE TABLE t0(c0 INT); | 
| CREATE TABLE t1(c0 INT); | 
| INSERT INTO t0 (c0) VALUES (0); | 
|  | 
| SELECT * FROM t1 RIGHT JOIN t0 ON true; -- NULL 0 | 
| SELECT (NOT ((t1.c0) IS TRUE)) FROM t1 RIGHT JOIN t0 ON true; -- 1 | 
|  | 
| SELECT * FROM t1 RIGHT JOIN t0 ON true WHERE (NOT ((t1.c0) IS TRUE)); | 
| -- Expected: NULL 0 | 
| -- Actual: Empty set |