MySQL Bugs: #107325: Subquery gives wrong results in ON DUPLICATE KEY UPDATE ... part of INSERT

Bug #107325	Subquery gives wrong results in ON DUPLICATE KEY UPDATE ... part of INSERT
Submitted:	18 May 2022 10:03	Modified:	18 May 2022 14:12
Reporter:	Janez Resnik	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:

In case of multi-row update like this

INSERT ... SELECT ... ON DUPLICATE KEY UPDATE b=(select ... )

all rows get last subquery result

demo: https://onecompiler.com/mysql/3y4dh73zw

How to repeat:
select @@version;

create table _test (
a int(10) unsigned not null,
b int(10) unsigned not null,
primary key (a)
);

insert into _test (a,b) values
(1,5),
(2,5),
(3,5)
on duplicate key update b=values(b)
;
select * from _test; #<-- ok

insert into _test (a,b)
with t2 as ((select 1 c,1 d)
union all (select 2,20)
union all (select 3,33)
)
select a,6
from _test t1
on duplicate key update b=(select t2.d from t2 where t2.c=t1.a)
;
select * from _test; #<-- gives (1,33),(2,33),(3,33) instead of (1,1),(2,20),(3,33)

insert into _test (a,b)
with t2 as ((select 1 c,1 d)
union all (select 2,20)
union all (select 3,33)
)
select a,(select t2.d from t2 where t2.c=t1.a)
from _test t1
on duplicate key update b=values(b)
;
select * from _test; #<-- ok

Suggested fix:
Each row must get corresponding subquery result, same as b=VALUES(b) variant produces above.

My guess is that SELECT executes first, then INSERT uses result set. Maybe the simplest solid solution is to add hidden columns to result set for each extra value needed in UPDATE part.

This in turn will require calculating those values for all rows not just for updated ones, which needs to be documented properly (any @vars will be affected, ...)

I tried this example with a few older versions of mysql:
- 8.0.12 ... gives "Error: ER_BAD_FIELD_ERROR: Unknown column 't1.a' in 'where clause'"
- 5.7.22 ... "WITH ... SELECT" is not supported, after resolving that it has the same issue

demo: https://www.db-fiddle.com/f/bwXWot2AGMRypKqaguLj77/0

- 5.6.40 ... same issue, see demo above
- 5.5.62 ... same issue, see demo above

HI Mr. Resnik,

Thank you for your bug report.

We have run your test cases and we reproduced your results.

We agree that the results are wrong.

Verified as reported.