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 .