/*

Wrong "Changed:" for multi-UPDATE of INNODB with TIMESPAMP when join in subquery


With complex conditions UPDATE reports "Changed:" rows even if no changes happened. (5.1.45)

I put high severity because this silently puts Slave out of sync in 5.0->5.1 replication. 
(Because TIMESTAMP gets updated on slave, but not on 5.0 master where bug is not reproducible. Even more problems if ON UPDATE triggers exists).

Not sure about all necessary prerequisites, but for attached testcase:

1. InnoDB Table has column defined TIMESTAMP on update CURRENT_TIMESTAMP (not reproducible with MyISAM or when no TIMESTAMP column) (See steps 1-5 in the testcase)

2. Next update goes not_immediately after previous update (see steps 5 and 6 in the testcase)

3. Multi-table update with join in subquery (see steps 7 and 8) 

The problem not reproducible in 5.0.90
*/



create table t1(id int primary key, data varchar(20)) engine=innodb;
create table t2(id int primary key, data varchar(20)) engine=innodb;
create table t3(id1 int, id2 int) engine=innodb;

insert into t1 values (1,'A'),(2,'B'),(3,'C');
insert into t2 values (1,'X'),(2,'Y'),(3,'Z');
insert into t3 values (1,1),(2,3);
# 1. this is initial update
update t1 join t3 as x on (t1.id = x.id1)
set data=(
    select data
    from t3 join t2 on (t2.id = id2)
    where id1 = x.id1);

select * from t1;
# 2. this is the same update which correctly reports "Changed: 0"
update t1 join t3 as x on (t1.id = x.id1)
set data=(
    select data
    from t3 join t2 on (t2.id = id2)
    where id1 = x.id1);

select * from t1;

# 3. lets add TIMESTAMP and check update again
alter table t1 add column `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP;

select * from t1;

# 4. this doesn't change anything but reports "Changed: 2"
update t1 join t3 as x on (t1.id = x.id1)
set data=(
    select data
    from t3 join t2 on (t2.id = id2)
    where id1 = x.id1);

select * from t1;

select sleep(1);

# 5. but this still doesn't change anything but still reports "Changed: 2"
update t1 join t3 as x on (t1.id = x.id1)
set data=(
    select data
    from t3 join t2 on (t2.id = id2)
    where id1 = x.id1);

# 6. this may report "Changed: 0" 
# (because executed immediately after previous update)
update t1 join t3 as x on (t1.id = x.id1)
set data=(
    select data
    from t3 join t2 on (t2.id = id2)
    where id1 = x.id1);

select * from t1;

select sleep(1);

# 7. let's test similar update, but without JOIN in subquery 
# Reports "Changed: 0"
update t1 join t3 as x on (t1.id = x.id1)
set data=(
    select data
    from t3 -- join t2 on (t2.id = id2)
    where id1 = x.id1);

select * from t1;
select sleep(1);

# 8. let's test similar update, but not Multi-table
# Reports "Changed: 0"
update t1 -- join t3 as x on (t1.id = x.id1)
set data=(
    select data
    from t3 join t2 on (t2.id = id2)
    where id1 = t1.id) -- where id1 = x.id1);
where id in (1,2);

select * from t1;