Details
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 |