MySQL Bugs: #118016: Wrong inner join result involving subquery containing distinct

Bug #118016	Wrong inner join result involving subquery containing distinct
Submitted:	20 Apr 10:34	Modified:	20 Apr 13:32
Reporter:	Zhaokun Xiang	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	9.3, 9.0, 8.0, 8.0.42	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hi, MySQL developers.

Please see below test cases. The second query produces wrong results, because  (!(subQuery1.col0)) should be evaluated as NULL. Instead, It should produce empty results. 

I also try this test case on MySQL 5.7, which can produce correct results.

```
CREATE TABLE t0(c0 DOUBLE);
INSERT INTO t0(c0) VALUES(0.1);

SELECT *
FROM t0
         INNER JOIN (SELECT DISTINCTROW
                              (NULL) AND (t0.c0) AS col0
                          FROM
                               t0) as subQuery1 ON true;
-- c0, col0
-- 0.1, NULL

SELECT *
FROM t0
         INNER JOIN (SELECT DISTINCTROW
                              (NULL) AND (t0.c0) AS col0
                          FROM
                               t0) as subQuery1 ON true
WHERE (!(subQuery1.col0));
-- c0, col0
-- 0.1, NULL
-- wrong result, should produce empty result, as (!(subQuery1.col0)) should be evaluated as NULL
```

How to repeat:
```
CREATE TABLE t0(c0 DOUBLE);
INSERT INTO t0(c0) VALUES(0.1);

SELECT *
FROM t0
         INNER JOIN (SELECT DISTINCTROW
                              (NULL) AND (t0.c0) AS col0
                          FROM
                               t0) as subQuery1 ON true;
-- c0, col0
-- 0.1, NULL

SELECT *
FROM t0
         INNER JOIN (SELECT DISTINCTROW
                              (NULL) AND (t0.c0) AS col0
                          FROM
                               t0) as subQuery1 ON true
WHERE (!(subQuery1.col0));
-- c0, col0
-- 0.1, NULL
-- wrong result, should produce empty result, as (!(subQuery1.col0)) should be evaluated as NULL
```

Hello Zhaokun Xiang,

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

regards,
Umesh

Thanks! I mistakenly chose the vulnerability-related settings when I submitted the bug report, leading to no access of this bug except me. How can I remove this setting?