Bug #118019 Wrong Right Join with a subquery containing distinct and wrong NULL-value expression optimization

• Submitted: 20 Apr 16:30
• Modified: 21 Apr 7:50
• Reporter: Zhaokun Xiang
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 9.0, 8.0, 9.3.0, 8.4.5, 8.0.42
• OS: Any
• CPU Architecture: Any

[20 Apr 16:30] Zhaokun Xiang

Description:
Hi, MySQL developers.

Please see below test cases. The second query produces empty results, which is wrong,  because  (subQuery1.col0) IS UNKNOWN should be evaluated as TRUE. 

```
CREATE TABLE t1(c0 INT, c1 INT);
CREATE TABLE t2(c0 INT, c1 INT);

INSERT INTO t1(c0, c1) VALUES(NULL, NULL);

SELECT *
FROM t2
         RIGHT JOIN (SELECT DISTINCT t1.c0 and t1.c1 AS col0
                     FROM t1) as subQuery1 ON true;
-- c0,    c1, col0
-- NULL, NULL, NULL

SELECT *
FROM t2
         RIGHT JOIN (SELECT DISTINCT (t1.c0 and t1.c1) AS col0
                     FROM t1) as subQuery1 ON true
where (subQuery1.col0) IS UNKNOWN;
-- empty result, which is wrong. 
-- '(subQuery1.col0) IS UNKNOWN' should be evaluated as true, and then this query should produce NULL,NULL,NULL

SELECT *
FROM t2
         RIGHT JOIN (SELECT DISTINCT (NULL) AS col0
                     FROM t1) as subQuery1 ON true
where (subQuery1.col0) IS UNKNOWN;
-- NULL,NULL,NULL
-- if I change '(t1.c0 and t1.c1)' to NULL, this query then produces correct result. So I guess there are also some bugs in the optimization of expression '(t1.c0 and t1.c1)'.

```

How to repeat:
CREATE TABLE t1(c0 INT, c1 INT);
CREATE TABLE t2(c0 INT, c1 INT);

INSERT INTO t1(c0, c1) VALUES(NULL, NULL);

SELECT *
FROM t2
         RIGHT JOIN (SELECT DISTINCT t1.c0 and t1.c1 AS col0
                     FROM t1) as subQuery1 ON true;
-- c0,    c1, col0
-- NULL, NULL, NULL

SELECT *
FROM t2
         RIGHT JOIN (SELECT DISTINCT (t1.c0 and t1.c1) AS col0
                     FROM t1) as subQuery1 ON true
where (subQuery1.col0) IS UNKNOWN;
-- empty result, which is wrong. 

SELECT *
FROM t2
         RIGHT JOIN (SELECT DISTINCT (NULL) AS col0
                     FROM t1) as subQuery1 ON true
where (subQuery1.col0) IS UNKNOWN;
-- NULL,NULL,NULL

[21 Apr 7:50] MySQL Verification Team

Hello Zhaokun Xiang,

Thank you for the report and feedback.
Verified as described.

regards,
Umesh