Bug #119663 Query reports error when turn on optimizer_switch

• Submitted: 13 Jan 4:24
• Modified: 13 Jan 7:45
• Reporter: Jiyuan Li
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 9.3.0
• OS: Ubuntu
• CPU Architecture: Any

[13 Jan 4:24] Jiyuan Li

Description:
Query reports error when turn on optimizer_switch, however, if I turn off optimizer_switch, it will execute successfully. 

How to repeat:
SET session optimizer_switch = 'subquery_to_derived=on';

create table if not exists t0(c0 float zerofill  unique key, c1 decimal , c2 double  storage disk  column_format dynamic) ;
create table if not exists t1 like t0;

delete ignore from t0 where (1156199452) is not true;

insert low_priority ignore into t0(c0, c2, c1) values(null, 1286430482, 2044447572), (293256983, 0.4024046347809098, null), (0.02272151386735255, "", '{f~?w7'), ('q', null, 161514226), ("_7<u跴蜌v*", 0.9088770938932345, "jx");

replace low_priority into t1(c1, c0, c2) values(0.6171324718015674, -1.267817879e9, 0.8666916318778376);

SELECT /*+ NO_INDEX_MERGE(t1, i0)*/ DISTINCTROW  EXISTS (SELECT 1 wHERE FALSE) AS ref0, t1.c1 AS ref1, MIN(DISTINCT (CASE t0.c1 WHEN t0.c0 THEN t0.c0 ELSE 0.40827181455382355 END)) AS ref2 FROM t0 RIGHT JOIN t1 ON CAST(-1231468407 AS SIGNED) WHERE (t0.c1) IS TRUE GROUP BY  EXISTS (SELECT 1 wHERE FALSE), t1.c1 ORDER BY (t0.c1) BETWEEN (t0.c0) AND (1819142688) ASC LIMIT 2968519667640263323 OFFSET 9062057545009688125;
-- ERROR 1062 (23000): Duplicate entry '0' for key '/tmp/#sql1_504_c6.<group_key>'

[13 Jan 7:45] Roy Lyseng

Thank you for the bug report.
Verified as described.