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

• Submitted: 13 Nov 11:44
• Modified: 13 Nov 12:23
• Reporter: chi zhang
• Status: Open
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 9.4.0
• OS: Any
• CPU Architecture: Any

[13 Nov 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 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