Description:
Hi, MySQL Developers,
Please see the below test cases.
The second query produces the wrong result. According to the first query, the expression '((IFNULL(t0.c0, 1)) IN (t0.c0))' should be evaluated as NULL in the second query and any queries with this expression as predicate should produce the empty result. However, the second query in this test case still produce 1 row.
Besides, I can reproduce this test case on MySQL 9.3.0. I also tried it on 9.0, 8.0, 5.7, 5.6, and these version all produce correctly.
```
CREATE TABLE IF NOT EXISTS t0(c0 DECIMAL) ;
CREATE TABLE IF NOT EXISTS t2(c0 INT) ;
INSERT INTO t0(c0) VALUES(NULL);
INSERT INTO t0(c0) VALUES (1);
INSERT INTO t2(c0) VALUES(0);
SELECT ((IFNULL(t0.c0, 1)) IN (t0.c0))
FROM t0
RIGHT JOIN t2 ON (0.5) = (t0.c0);
-- NULL
SELECT *
FROM t0
RIGHT JOIN t2 ON (0.5) = (t0.c0)
where ((IFNULL(t0.c0, 1)) IN (t0.c0));
-- 1, 0
-- wrong result, this query should produce the empty result.
```
How to repeat:
```
CREATE TABLE IF NOT EXISTS t0(c0 DECIMAL) ;
CREATE TABLE IF NOT EXISTS t2(c0 INT) ;
INSERT INTO t0(c0) VALUES(NULL);
INSERT INTO t0(c0) VALUES (1);
INSERT INTO t2(c0) VALUES(0);
SELECT ((IFNULL(t0.c0, 1)) IN (t0.c0))
FROM t0
RIGHT JOIN t2 ON (0.5) = (t0.c0);
-- NULL
SELECT *
FROM t0
RIGHT JOIN t2 ON (0.5) = (t0.c0)
where ((IFNULL(t0.c0, 1)) IN (t0.c0));
-- 1, 0
-- wrong result, this query should produce the empty result.
```
Description: Hi, MySQL Developers, Please see the below test cases. The second query produces the wrong result. According to the first query, the expression '((IFNULL(t0.c0, 1)) IN (t0.c0))' should be evaluated as NULL in the second query and any queries with this expression as predicate should produce the empty result. However, the second query in this test case still produce 1 row. Besides, I can reproduce this test case on MySQL 9.3.0. I also tried it on 9.0, 8.0, 5.7, 5.6, and these version all produce correctly. ``` CREATE TABLE IF NOT EXISTS t0(c0 DECIMAL) ; CREATE TABLE IF NOT EXISTS t2(c0 INT) ; INSERT INTO t0(c0) VALUES(NULL); INSERT INTO t0(c0) VALUES (1); INSERT INTO t2(c0) VALUES(0); SELECT ((IFNULL(t0.c0, 1)) IN (t0.c0)) FROM t0 RIGHT JOIN t2 ON (0.5) = (t0.c0); -- NULL SELECT * FROM t0 RIGHT JOIN t2 ON (0.5) = (t0.c0) where ((IFNULL(t0.c0, 1)) IN (t0.c0)); -- 1, 0 -- wrong result, this query should produce the empty result. ``` How to repeat: ``` CREATE TABLE IF NOT EXISTS t0(c0 DECIMAL) ; CREATE TABLE IF NOT EXISTS t2(c0 INT) ; INSERT INTO t0(c0) VALUES(NULL); INSERT INTO t0(c0) VALUES (1); INSERT INTO t2(c0) VALUES(0); SELECT ((IFNULL(t0.c0, 1)) IN (t0.c0)) FROM t0 RIGHT JOIN t2 ON (0.5) = (t0.c0); -- NULL SELECT * FROM t0 RIGHT JOIN t2 ON (0.5) = (t0.c0) where ((IFNULL(t0.c0, 1)) IN (t0.c0)); -- 1, 0 -- wrong result, this query should produce the empty result. ```