Bug #9689 wrong result for subselect in on duplicate key update (query cache)
Submitted: 6 Apr 2005 17:37 Modified: 22 Apr 2005 21:10
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:4.1.11 OS:Any (*)
Assigned to: Igor Babaev CPU Architecture:Any

[6 Apr 2005 17:37] Martin Friebe
Description:
I would expect the ast row in the example below to be 5.

step by step:

mysql tries to insert 3; this is a duplicate; max(a)+1 is 4; mysql sets the row to 4;

mysql tries to insert 4; this is now a duplicate; max(a)+1 should now be 5

reexecute with:
set GLOBAL query_cache_limit=0

and the result is as expected

How to repeat:
CREATE TABLE t1 (a int(11));
alter table t1 add unique (a);
insert into t1 values (1), (2), (3);

insert into t1 select 3  union all  select 4    on duplicate key update a = (select max(a)+1 from t1);
#Records: 2  Duplicates: 2  Warnings: 0
select * from t1;

+------+
| a    |
+------+
|    1 |
|    2 |
|    4 |
+------+

drop table t2;
drop table t1;

Suggested fix:
-
[22 Apr 2005 21:10] Igor Babaev
(select max(a)+1 from t1) is not correlated subquery and it returns a constant with a particular contents of table t1.
In our case it returns 4 (max(1,2,3)+1=4). So we try to update setting a to 4  on both updates.