Bug #93410 Insert is fine but "On Duplicate Update" updating wrong data
Submitted: 29 Nov 2018 16:57 Modified: 21 Feb 2019 2:39
Reporter: Avinash sachan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S1 (Critical)
Version:8.0.13 OS:CentOS
Assigned to: CPU Architecture:Any
Tags: regression

[29 Nov 2018 16:57] Avinash sachan
Description:
We have upgraded our system from 5.6 to 8.0.13 & moved to Linux plateform and found that data getting updated using self join & same table in sub-query is updating wrongly  in insert query with "on duplicate update" . 

Insert is working fine , but same query if executed , update causes wrong value update.

our previous Version 5.6 in windows was working fine.

How to repeat:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.13 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql>
mysql>
mysql> create schema if not exists temp ;
Query OK, 1 row affected (0.32 sec)

mysql> USE temp;
Database changed
mysql> DROP TABLE IF EXISTS tbl_ne2;
Query OK, 0 rows affected, 1 warning (0.14 sec)

mysql>
mysql> CREATE TABLE  tbl_ne2 (
    ->   `OBJECT_NAME` varchar(100) NOT NULL,
    ->   `LOCATION` varchar(45) NOT NULL,
    ->   `OBJECT_TYPE` varchar(5) DEFAULT NULL,
    ->   PRIMARY KEY (`OBJECT_NAME`) USING BTREE,
    ->   KEY `Index_2` (`OBJECT_TYPE`,`LOCATION`) USING BTREE
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (1.01 sec)

mysql>
mysql>
mysql>
mysql> insert into tbl_ne2 values ('A1' , 'A' , 'P');
Query OK, 1 row affected (0.19 sec)

mysql> insert into tbl_ne2 values ('B1' , 'B' , 'P');
Query OK, 1 row affected (0.16 sec)

mysql>
mysql>
mysql>
mysql> DROP TABLE IF EXISTS tbl_ont_inventory2;
Query OK, 0 rows affected, 1 warning (0.21 sec)

mysql> CREATE TABLE  tbl_ont_inventory2 (
    ->   `OBJECT_NAME` varchar(200) NOT NULL,
    ->   `PARENT` varchar(100) NOT NULL,
    ->   PRIMARY KEY (`OBJECT_NAME`) USING BTREE
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.84 sec)

mysql>
mysql>
mysql> insert into tbl_ont_inventory2 values ('A11' , 'A1');
Query OK, 1 row affected (0.15 sec)

mysql> insert into tbl_ont_inventory2 values ('B11' , 'B1');
Query OK, 1 row affected (0.22 sec)

mysql>
mysql>
mysql> insert into tbl_ne2 (OBJECT_NAME, LOCATION, OBJECT_TYPE)
    -> SELECT a.OBJECT_NAME, b.LOCATION, 'C' OBJECT_TYPE
    -> FROM tbl_ne2 b
    -> inner join tbl_ont_inventory2 a on  b.object_name = a.PARENT
    -> where b.object_type = 'P'
    -> on duplicate key update
    -> tbl_ne2.LOCATION = b.LOCATION;
Query OK, 2 rows affected (0.19 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from tbl_ne2 ;
+-------------+----------+-------------+
| OBJECT_NAME | LOCATION | OBJECT_TYPE |
+-------------+----------+-------------+
| A11         | A        | C           |
| B11         | B        | C           |
| A1          | A        | P           |
| B1          | B        | P           |
+-------------+----------+-------------+
4 rows in set (0.00 sec)

mysql>
mysql>
mysql>
mysql> insert into tbl_ne2 (OBJECT_NAME, LOCATION, OBJECT_TYPE)
    -> SELECT a.OBJECT_NAME, b.LOCATION, 'C' OBJECT_TYPE
    -> FROM tbl_ne2 b
    -> inner join tbl_ont_inventory2 a on  b.object_name = a.PARENT
    -> where b.object_type = 'P'
    -> on duplicate key update
    -> tbl_ne2.LOCATION = b.LOCATION;
Query OK, 2 rows affected (0.29 sec)
Records: 2  Duplicates: 1  Warnings: 0

mysql>
mysql>
mysql>
mysql> select * from tbl_ne2 ;
+-------------+----------+-------------+
| OBJECT_NAME | LOCATION | OBJECT_TYPE |
+-------------+----------+-------------+
| A11         | B        | C           |
| B11         | B        | C           |
| A1          | A        | P           |
| B1          | B        | P           |
+-------------+----------+-------------+
4 rows in set (0.00 sec)

mysql>
mysql>
mysql>
mysql>
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.13    |
+-----------+
1 row in set (0.00 sec)

mysql>

Suggested fix:
creating a temporary table & then update is working fine but not suitable for large table .
[29 Nov 2018 17:00] Avinash sachan
demo queries

Attachment: mysql bug.txt (text/plain), 1.36 KiB.

[30 Nov 2018 8:49] MySQL Verification Team
Thank you for the bug report and test case.
[7 Jan 2019 9:24] Guilhem Bichot
Hello Avinash.
I'm working on it. Just to make sure: the expected result of the final SELECT is:
OBJECT_NAME	LOCATION	OBJECT_TYPE
A11	A	C
B11	B	C
A1	A	P
B1	B	P
?
In other words, the second INSERT ON DUPLICATE KEY UPDATE should change no row, right?
[19 Jan 2019 16:17] Avinash sachan
yes, it should change no row. Thanks. Let me know if you need any help.
[21 Feb 2019 2:39] Paul DuBois
Posted by developer:
 
Fixed in 8.0.16.

INSERT ... ON DUPLICATE KEY UPDATE could perform incorrect updates.