Bug #119663 Query reports error when turn on optimizer_switch

Submitted: 13 Jan 4:24 Modified: 13 Jan 7:45
Reporter: Jiyuan Li Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version: 9.3.0 OS:Ubuntu
Assigned to: 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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.038 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.