Bug #118621 subquery_to_derived optimizer makes query produce incorrect result

• Submitted: 9 Jul 12:39
• Modified: 9 Jul 14:16
• Reporter: chi zhang
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 9.3.0, 8.0.42
• OS: Any
• CPU Architecture: Any

[9 Jul 12:39] chi zhang

Description:
Hi,

In the following test case, there is only one value `1` in table `t0`, so the expression `CAST(0.8 AS DOUBLE) IN (t0.c0)` should return false and this query should have empty result, but in MySQL, it returns one row.

```
CREATE TABLE t0(c0 SMALLINT(54)  PRIMARY KEY) ;
SET SESSION optimizer_switch = 'subquery_to_derived=on';
INSERT IGNORE INTO t0(c0) VALUES (1);
SET @b = 0.8;
SELECT t0.c0 FROM t0 WHERE CAST(0.8 AS DOUBLE) IN (t0.c0); -- 1
```

If I remove the `SET SESSION optimizer_switch = 'subquery_to_derived=on';` statement, the query can generate the correct result.

Furthermore, if I remove the `PRIMARY KEY`, the query can also generate the correct result.

How to repeat:
```
CREATE TABLE t0(c0 SMALLINT(54)  PRIMARY KEY) ;
SET SESSION optimizer_switch = 'subquery_to_derived=on';
INSERT IGNORE INTO t0(c0) VALUES (1);
SET @b = 0.8;
SELECT t0.c0 FROM t0 WHERE CAST(0.8 AS DOUBLE) IN (t0.c0);
```

[9 Jul 14:16] MySQL Verification Team

Hello chi zhang,

Thank you for the report and test case.
Verified as described.

regards,
Umesh