Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL)
Description
In case of multi-row update like this
INSERT ... SELECT ... ON DUPLICATE KEY UPDATE b=(select ... )
all rows get last subquery result
This bug has been reported upstream, see https://bugs.mysql.com/107325
Tested mariadb versions (with issue):
- 10.7.3-MariaDB-1:10.7.3+maria~focal (demo: https://onecompiler.com/mariadb/3y4dh8xj2)
- 10.3.28-MariaDB-1:10.3.28+maria~focal
Tested mysql versions (with issue):
- 8.0.27 (demo: https://onecompiler.com/mysql/3y4dh73zw)
- 5.7.22 (demo: https://www.db-fiddle.com/f/bwXWot2AGMRypKqaguLj77/0)
- 5.6.40
- 5.5.62
Thank you!
I repeated as described on 10.3-10.9
MariaDB [test]> CREATE TABLE tb1 (a int, b int, PRIMARY KEY (a));Query OK, 0 rows affected (0.055 sec)MariaDB [test]> insert into tb1 values (1,5),(2,5),(3,5);Query OK, 3 rows affected (0.009 sec)Records: 3 Duplicates: 0 Warnings: 0MariaDB [test]> insert into tb1 (a,b)-> select a,6 from tb1 t1-> on duplicate key update b=(select t2.d from ((select 1 c,1 d) union (values (2,20),(3,33))) t2 where t2.c=t1.a);Query OK, 6 rows affected (0.010 sec)Records: 3 Duplicates: 3 Warnings: 0MariaDB [test]> select * from tb1;+---+------+| a | b |+---+------+| 1 | 33 || 2 | 33 || 3 | 33 |+---+------+3 rows in set (0.002 sec)MariaDB [test]> select version();+----------------------+| version() |+----------------------+| 10.9.2-MariaDB-debug |+----------------------+1 row in set (0.001 sec)