Bug #112243 Unexpected Result by subquery_to_derived
Submitted: 4 Sep 2023 4:54 Modified: 4 Sep 2023 8:30
Reporter: JINSHENG BA Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.34 OS:Any
Assigned to: CPU Architecture:Any

[4 Sep 2023 4:54] JINSHENG BA 
Description:
CREATE TABLE t0(c0 INT, c1 INT ZEROFILL UNIQUE) ;
INSERT INTO t0(c1) VALUES(0.01);

SET SESSION optimizer_switch = 'subquery_to_derived=off';
SELECT t0.c0 AS ref0, t0.c1 AS ref1 FROM t0 WHERE t0.c1 IN (COALESCE(0.188)); -- {}
SET SESSION optimizer_switch = 'subquery_to_derived=on';
SELECT t0.c0 AS ref0, t0.c1 AS ref1 FROM t0 WHERE t0.c1 IN (COALESCE(0.188)); -- {NULL|0000000000}

Changing the configuration 'subquery_to_derived' affects the result.

How to repeat:
docker run -it -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root mysql:8.1.0

Then execute the above test case.
[4 Sep 2023 8:30] MySQL Verification Team 
Hello Jinsheng Ba,

Thank you for the report and test case.

regards,
Umesh
[21 Sep 2023 7:13] huahua xu 
Hi, all:

When setting the subquery_to_derived flag to on, the optimizer does not detect tables that are const (0 or 1 row), and fill the key value with wrong data which choose the best QUICK access method for table.