Bug #119386 unexpected error "Can't write; duplicate key in table '/tmp/#sql1_372_1'"

• Submitted: 13 Nov 2025 11:44
• Modified: 15 Jan 12:34
• Reporter: chi zhang
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 9.4.0
• OS: Any
• CPU Architecture: Any

[13 Nov 2025 11:44] chi zhang

Description:
Hi,

The following test case triggers an unexpected error:

```
CREATE TABLE IF NOT EXISTS t0(c0 TINYTEXT, c1 DECIMAL, c2 FLOAT) ;
INSERT HIGH_PRIORITY IGNORE INTO t0(c2) VALUES('u^%pzX');
INSERT INTO t0(c2) VALUES(0.7674682736888384);
SELECT DISTINCTROW MAX(TRUE) AS ref0, (( EXISTS (SELECT 1)) && (0)) AS ref1, -1 AS ref2 FROM t0 GROUP BY  ((1) && ((t0.c0) < (t0.c1))), -1;
```

This is the error message: `ERROR 1022 (23000) at line 7: Can't write; duplicate key in table '/tmp/#sql1_372_1'`

I found there is a similar bug report in https://bugs.mysql.com/bug.php?id=103632, however, the test case shown in this page can not be reproduced, so I gaussed that these two are different bugs.

How to repeat:
```
CREATE TABLE IF NOT EXISTS t0(c0 TINYTEXT, c1 DECIMAL, c2 FLOAT) ;
INSERT HIGH_PRIORITY IGNORE INTO t0(c2) VALUES('u^%pzX');
INSERT INTO t0(c2) VALUES(0.7674682736888384);
SELECT DISTINCTROW MAX(TRUE) AS ref0, (( EXISTS (SELECT 1)) && (0)) AS ref1, -1 AS ref2 FROM t0 GROUP BY  ((1) && ((t0.c0) < (t0.c1))), -1;
```

[13 Nov 2025 12:23] chi zhang

Hi,

I triggered this bug in a mysql docker, and the bug must be triggered with two test cases.
These are the full steps:
1. Create the mysql docker
```
docker run -itd --name mysql -e MYSQL_ROOT_PASSWORD=root -p 3306:3306 mysql:latest
```
2. Create a `test` database:
```
docker exec -it mysql mysql -uroot -proot
create database test;
```
3. Run the first test case:
test1.sql
```
DROP DATABASE IF EXISTS database91;
CREATE DATABASE database91;
USE database91;
SET GLOBAL optimizer_switch = 'firstmatch=on,batched_key_access=on,derived_merge=off,block_nested_loop=off,derived_condition_pushdown=on,condition_fanout_filter=off,duplicateweedout=on,mrr_cost_based=on,semijoin=on,materialization=on,loosescan=on,subquery_to_derived=on,index_merge=on,skip_scan=on,prefer_ordering_index=on,index_condition_pushdown=on,subquery_materialization_cost_based=on,index_merge_union=off,mrr=off,use_invisible_indexes=on,index_merge_sort_union=on,index_merge_intersection=on,use_index_extensions=on,hash_join=on';
```
docker exec -i mysql mysql -uroot -proot -f < test1.sql
4. Run the second test case:
test2.sql
```
DROP DATABASE IF EXISTS database93;
CREATE DATABASE database93;
USE database93;
CREATE TABLE IF NOT EXISTS t0(c0 TINYTEXT, c1 DECIMAL, c2 FLOAT) ;
INSERT HIGH_PRIORITY IGNORE INTO t0(c2) VALUES('u^%pzX');
INSERT INTO t0(c2) VALUES(0.7674682736888384);
SELECT DISTINCTROW MAX(TRUE) AS ref0, (( EXISTS (SELECT 1)) && (0)) AS ref1, -1 AS ref2 FROM t0 GROUP BY  ((1) && ((t0.c0) < (t0.c1))), -1;
```
docker exec -i mysql mysql -uroot -proot -f < test2.sql

[15 Jan 12:34] Øystein Grøvlen

Verified on 9.5.0:

mysql [localhost:9500] {msandbox} (test) > SET optimizer_switch = 'subquery_to_derived=on';
Query OK, 0 rows affected (0.000 sec)

mysql [localhost:9500] {msandbox} (test) > DROP DATABASE IF EXISTS database93;
Query OK, 1 row affected (0.004 sec)

mysql [localhost:9500] {msandbox} (test) > CREATE DATABASE database93;
Query OK, 1 row affected (0.003 sec)

mysql [localhost:9500] {msandbox} (test) > USE database93;
Database changed
mysql [localhost:9500] {msandbox} (database93) > CREATE TABLE IF NOT EXISTS t0(c0 TINYTEXT, c1 DECIMAL, c2 FLOAT) ;
Query OK, 0 rows affected (0.008 sec)

mysql [localhost:9500] {msandbox} (database93) > INSERT HIGH_PRIORITY IGNORE INTO t0(c2) VALUES('u^%pzX');
Query OK, 1 row affected, 1 warning (0.003 sec)

mysql [localhost:9500] {msandbox} (database93) > INSERT INTO t0(c2) VALUES(0.7674682736888384);
Query OK, 1 row affected (0.001 sec)

mysql [localhost:9500] {msandbox} (database93) > SELECT DISTINCTROW MAX(TRUE) AS ref0, (( EXISTS (SELECT 1)) && (0)) AS ref1, -1 AS ref2 FROM t0 GROUP BY  ((1) && ((t0.c0) < (t0.c1))), -1;
ERROR 1022 (23000): Can't write; duplicate key in table '/home/ogrovlen/sandboxes/msb_9_5_0/tmp/#sql2a88a9_d_5'