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
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.22 and 4.1.20
• OS: Linux (Linux, WIndows)
• CPU Architecture: Any
• Tags: floor update

[20 Jul 2006 17:50] Sam Dribin

Description:
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
set
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.