Bug #21189 Functions in an update statement yield inconsistent results depending on order
Submitted: 20 Jul 2006 17:50 Modified: 20 Jul 2006 22:26
Reporter: Sam Dribin Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.22 and 4.1.20 OS:Linux (Linux, WIndows)
Assigned to: CPU Architecture:Any
Tags: floor update

[20 Jul 2006 17:50] Sam Dribin
Floor function in an update statement yields inconsistent results depending upon the order used.

How to repeat:
drop table if exists x;

create table x (a int(10) unsigned, c decimal(19,6));

insert into x values (0,0);

update x
set a = floor(a + 103 / 10),
c = floor(a + 103 / 10);

/*Desired results: a=10,b=10*/
select * from x;

delete from x;

insert into x values (0,0);

update x
c = floor(a + 103 / 10),
a = floor(a + 103 / 10);

/*Now is a=10, b=10*/
select * from x;
[20 Jul 2006 17:57] Sam Dribin
ceil and mod functions seem to yield the same results.
[20 Jul 2006 22:26] Hartmut Holzgraefe
Expressions are evaluated from left to right here
so that the new value of column 'a' that you've set
in the first assignment is already in effect on the
2nd assignment. It is not related to the use of a 
function, a simple

  UPDATE x SET a=a+10, b=a+10;

will show the same effect.