[MDEV-7254] Assigned expression is evaluated twice when updating column TIMESTAMP NOT NULL Created: 2014-12-03  Updated: 2015-01-17  Resolved: 2015-01-16

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.0.14, 10.0.15
Fix Version/s: 10.0.16-galera

Type: Bug Priority: Major
Reporter: Slava Assignee: Jan Lindström (Inactive)
Resolution: Fixed Votes: 1
Labels: None

Attachments: File timestamp.diff    

 Description   

Assigned expression is evaluated twice when updating column TIMESTAMP NOT NULL. This does not happen on any other types of column and does not depend on table engine. This does not happen on MySQL 5.5.40, 5.6.21, but does on MariaDB 10.0.14 and 10.0.15.

How to repeat:

-- create table
 
drop table if exists foo;
 
create table foo(value TIMESTAMP NOT NULL);
 
 
 
-- insert one row
 
insert foo set value=now();
 
 
 
-- update of 1 row takes 2 seconds instead of 1 second
 
update foo set value=sleep(1);
 
 
 
-- insert one more row (two rows total)
 
insert foo set value=now();
 
 
 
-- update of 2 rows takes 4 seconds instead of 2 seconds
 
update foo set value=sleep(1);
 
 
 
 
 
-- ----------------------------------------
 
-- just for fun - make it NULL instead of NOT NULL:
 
alter table foo modify column value TIMESTAMP NULL;
 
 
 
-- update of 2 rows takes 2 seconds, as expected
 
update foo set value=sleep(1);



 Comments   
Comment by Kirill Chernyshov [ 2014-12-03 ]

Yet another way to reproduce the bug.

-- create table
 
drop table if exists foo;
 
create table foo(value TIMESTAMP NOT NULL);
 
 
 
-- insert one row
 
insert foo set value=now();
 
 
 
-- define result holders
 
set @old:=null, @new:=null;
 
 
 
-- update fetching the value before and after change
 
update foo set value=@new:=greatest(now() + interval 2 second, @old:=value);
 
 
 
-- both values appear to be the same while expected a 2 second difference
 
select @old, @new;

It looks like @old var is getting re-evaluated after update.

Comment by Elena Stepanova [ 2014-12-03 ]

Reproducible all way back to 10.0.1 (at least).

Comment by Jan Lindström (Inactive) [ 2015-01-14 ]

Tested with MySQL 5.6.22:

drop table if exists t1,t2;
 
CREATE TABLE t1(value TIMESTAMP);
 
INSERT t1 SET value=now();
 
SELECT * FROM t1;
 
value
 
2015-01-14 18:38:13
 
UPDATE t1 SET value=sleep(1);
 
SELECT * FROM t1;
 
value
 
0000-00-00 00:00:00
 
INSERT t1 SET VALUE=now();
 
UPDATE t1 SET VALUE=sleep(1);
 
SELECT * FROM t1;
 
value
 
0000-00-00 00:00:00
 
0000-00-00 00:00:00
 
DROP TABLE t1;
 
CREATE TABLE t1(value TIMESTAMP NOT NULL);
 
INSERT t1 SET value=now();
 
SELECT * FROM t1;
 
value
 
2015-01-14 18:38:16
 
UPDATE t1 SET value=sleep(1);
 
SELECT * FROM t1;
 
value
 
0000-00-00 00:00:00
 
INSERT t1 SET VALUE=now();
 
UPDATE t1 SET VALUE=sleep(1);
 
SELECT * FROM t1;
 
value
 
0000-00-00 00:00:00
 
0000-00-00 00:00:00
 
ALTER TABLE t1 MODIFY COLUMN VALUE TIMESTAMP NULL;
 
UPDATE t1 SET VALUE=sleep(1);
 
SELECT * FROM t1;
 
VALUE
 
0000-00-00 00:00:00
 
0000-00-00 00:00:00
 
DROP TABLE t1;
 
create table t1(value TIMESTAMP);
 
insert t1 set value=now();
 
set @old:=null, @new:=null;
 
update t1 set value=@new:=greatest(now() + interval 2 second, @old:=value);
 
select @old, @new;
 
@old	@new
 
2015-01-14 18:38:21	2015-01-14 18:38:23
 
select * from t1;
 
value
 
2015-01-14 18:38:23
 
drop table t1;
 
create table t1(value TIMESTAMP NOT NULL);
 
insert t1 set value=now();
 
set @old:=null, @new:=null;
 
update t1 set value=@new:=greatest(now() + interval 2 second, @old:=value);
 
select @old, @new;
 
@old	@new
 
2015-01-14 18:38:21	2015-01-14 18:38:23
 
select * from t1;
 
value
 
2015-01-14 18:38:23
 
drop table t1;

I do not see difference on TIMESTAMP or TIMESTAMP NOT NULl behaviour.

Comment by Slava [ 2015-01-14 ]

I assume this is MariaDB-only bug (as I mentioned in the bug description, this does not happen on MySQL 5.5.40, 5.6.21).

Comment by Jan Lindström (Inactive) [ 2015-01-14 ]

From MariaDB 10.0.15:

drop table if exists t1,t2;
 
CREATE TABLE t1(value TIMESTAMP);
 
INSERT t1 SET value=now();
 
SELECT * FROM t1;
 
value
 
2015-01-14 18:00:13
 
UPDATE t1 SET value=sleep(1);
 
SELECT * FROM t1;
 
value
 
0000-00-00 00:00:00
 
INSERT t1 SET VALUE=now();
 
UPDATE t1 SET VALUE=sleep(1);
 
SELECT * FROM t1;
 
value
 
0000-00-00 00:00:00
 
0000-00-00 00:00:00
 
DROP TABLE t1;
 
CREATE TABLE t1(value TIMESTAMP NOT NULL);
 
INSERT t1 SET value=now();
 
SELECT * FROM t1;
 
value
 
2015-01-14 18:00:19
 
UPDATE t1 SET value=sleep(1);
 
SELECT * FROM t1;
 
value
 
0000-00-00 00:00:00
 
INSERT t1 SET VALUE=now();
 
UPDATE t1 SET VALUE=sleep(1);
 
SELECT * FROM t1;
 
value
 
0000-00-00 00:00:00
 
0000-00-00 00:00:00
 
ALTER TABLE t1 MODIFY COLUMN VALUE TIMESTAMP NULL;
 
UPDATE t1 SET VALUE=sleep(1);
 
SELECT * FROM t1;
 
VALUE
 
0000-00-00 00:00:00
 
0000-00-00 00:00:00
 
DROP TABLE t1;
 
create table t1(value TIMESTAMP);
 
insert t1 set value=now();
 
set @old:=null, @new:=null;
 
update t1 set value=@new:=greatest(now() + interval 2 second, @old:=value);
 
select @old, @new;
 
@old	@new
 
2015-01-14 18:00:29	2015-01-14 18:00:29
 
select * from t1;
 
value
 
2015-01-14 18:00:29
 
drop table t1;
 
create table t1(value TIMESTAMP NOT NULL);
 
insert t1 set value=now();
 
set @old:=null, @new:=null;
 
update t1 set value=@new:=greatest(now() + interval 2 second, @old:=value);
 
select @old, @new;
 
@old	@new
 
2015-01-14 18:00:29	2015-01-14 18:00:29
 
select * from t1;
 
value
 
2015-01-14 18:00:29
 
drop table t1;

Comment by Jan Lindström (Inactive) [ 2015-01-14 ]

Now the questions is which one is correct and that is depenent on set value=@new:=greatest(now() + interval 2 second, @old:=value); execution, we set value and @new to be bigger of now()+2 and @old to be value.

Comment by Jan Lindström (Inactive) [ 2015-01-15 ]

There clearly is a bug on MariaDB 10.0.15:

drop table if exists t1,t2;
 
CREATE TABLE t1(value TIMESTAMP NOT NULL);
 
set @a:=0;
 
create function f1 () returns timestamp
 
begin
 
set @a = @a + 1;
 
return now();
 
end//
 
INSERT t1 SET value=now();
 
select * from t1;
 
value
 
2015-01-15 07:40:32
 
update t1 set value = f1();
 
select * from t1;
 
value
 
2015-01-15 07:40:32
 
select @a;
 
@a
 
2
 
drop table t1;
 
drop function f1;

Compare to MySQL 5.6.22:

drop table if exists t1,t2;
 
CREATE TABLE t1(value TIMESTAMP NOT NULL);
 
set @a:=0;
 
create function f1 () returns timestamp
 
begin
 
set @a = @a + 1;
 
return now();
 
end//
 
INSERT t1 SET value=now();
 
select * from t1;
 
value
 
2015-01-15 08:42:26
 
update t1 set value = f1();
 
select * from t1;
 
value
 
2015-01-15 08:42:26
 
select @a;
 
@a
 
1
 
drop table t1;
 
drop function f1;

Comment by Jan Lindström (Inactive) [ 2015-01-15 ]

revno: 4554
committer: Jan Lindström <jplindst@mariadb.org>
branch nick: 10.0-bugs2
timestamp: Thu 2015-01-15 13:21:14 +0200
message:
MDEV-7254: Assigned expression is evaluated twice when updating
column TIMESTAMP NOT NULL

Analysis: Problem was that value->is_null() function is called
even when user had explicitly set the value for timestamp
field. Calling this function had the side effect that
expression was evaluated twice.

Fix: (by Sergei Golubchik) check instead value->null_value.

Comment by Jan Lindström (Inactive) [ 2015-01-15 ]

Fix patch attached to mdev.

Comment by Sergei Golubchik [ 2015-01-15 ]

The code change is ok, so my comments are about test cases

  1. why do you need a function with return now(); — what do you test with it?
  2. don't sleep in tests, instead use SET TIMESTAMP to alter the value of NOW().
  3. I don't think the test with (1 = f2() is necessary
  4. it looks like you repeat the same test many times in the test file, what is the point of that?
  5. please remove the test with greatest it's too confusing, there are simpler tests for double execution (and you have them already in the test file).
    Here's what a test file can be: 

    --echo #
    		 
    --echo # MDEV-7254: Assigned expression is evaluated twice when updating column TIMESTAMP NOT NULL
    		 
    --echo #
    		 
     
    		 
    create table t1(value timestamp not null);
    		 
    set @a:=0;
    		 
    delimiter //;
    		 
    create function f1 () returns timestamp
    		 
    begin
    		 
      set @a = @a + 1;
    		 
      return NULL;
    		 
    end//
    		 
    delimiter ;//
    		 
    set timestamp=12340;
    		 
    insert t1 values (f1());
    		 
    select @a, value from t1;
    		 
    set timestamp=12350;
    		 
    update t1 set value = f1();
    		 
    select @a, value from t1;
    		 
    drop table t1;
    		 
    drop function f1;
    		 
    set timestamp=0;
    		 
    --echo End of 10.0 tests

Comment by Jan Lindström (Inactive) [ 2015-01-16 ]

Let me answer your questions:

  • You are correct, this is unnecessary
  • Ok
  • True
  • Bug and fix affects only timestamp type (this is reason why I added only timestamp tests), thus in my opinion we should test both TIMESTAMP NOT NULL and TIMESTAMP NULL cases to test fix to TIMESTAMP NOT NULL and to avoid regression to TIMESTAMP NULL.
  • Will do
Comment by Jan Lindström (Inactive) [ 2015-01-16 ]

My proposal for test:

--echo #
 
--echo # MDEV-7254: Assigned expression is evaluated twice when updating column TIMESTAMP NOT NULL
 
--echo #
 
 
 
create table t1(value timestamp not null);
 
set @a:=0;
 
delimiter //;
 
create function f1 () returns timestamp
 
begin
 
  set @a = @a + 1;
 
  return NULL;
 
end//
 
delimiter ;//
 
set timestamp=12340;
 
insert t1 values (f1());
 
select @a, value from t1;
 
set timestamp=12350;
 
update t1 set value = f1();
 
select @a, value from t1;
 
drop table t1;
 
drop function f1;
 
set timestamp=0;
 
 
 
# Verify no regressions to TIMESTAMP NULL
 
create table t1(value timestamp null);
 
set @a:=0;
 
delimiter //;
 
create function f1 () returns timestamp
 
begin
 
  set @a = @a + 1;
 
  return NULL;
 
end//
 
delimiter ;//
 
set timestamp=12340;
 
insert t1 values (f1());
 
select @a, value from t1;
 
set timestamp=12350;
 
update t1 set value = f1();
 
select @a, value from t1;
 
drop table t1;
 
drop function f1;
 
set timestamp=0;

Comment by Sergei Golubchik [ 2015-01-16 ]

ok to push

Comment by Jan Lindström (Inactive) [ 2015-01-16 ]

revno: 4557
committer: Jan Lindström <jplindst@mariadb.org>
branch nick: 10.0-bugs2
timestamp: Fri 2015-01-16 12:00:07 +0200
message:
MDEV-7254: Assigned expression is evaluated twice when updating
column TIMESTAMP NOT NULL

Analysis: Problem was that value->is_null() function is called
even when user had explicitly set the value for timestamp
field. Calling this function had the side effect that
expression was evaluated twice.

Fix: (by Sergei Golubchik) check instead value->null_value.

Generated at Thu Feb 08 07:18:10 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.