[MDEV-7254] Assigned expression is evaluated twice when updating column TIMESTAMP NOT NULL - JIRA

Details

Type: Bug
Status: Closed
Priority: Major
Resolution: Fixed
Affects Version/s: 10.0.14, 10.0.15
Fix Version/s: 10.0.16-galera
Component/s: Optimizer
Labels:
None

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);

Gliffy Diagrams

Attachments

Options
- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

Attachments

timestamp.diff
5 kB
2015-01-15 13:26

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Kirill Chernyshov added a comment - 2014-12-03 06:25

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.

Show

Kirill Chernyshov added a comment - 2014-12-03 06:25 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.

8 older comments

Hide

Permalink

Sergei Golubchik added a comment - 2015-01-15 20:01

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

why do you need a function with return now(); — what do you test with it?
don't sleep in tests, instead use SET TIMESTAMP to alter the value of NOW().
I don't think the test with (1 = f2() is necessary
it looks like you repeat the same test many times in the test file, what is the point of that?

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

Show

Sergei Golubchik added a comment - 2015-01-15 20:01 The code change is ok, so my comments are about test cases why do you need a function with return now(); — what do you test with it? don't sleep in tests, instead use SET TIMESTAMP to alter the value of NOW() . I don't think the test with (1 = f2() is necessary it looks like you repeat the same test many times in the test file, what is the point of that? 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

Hide

Permalink

Jan Lindström added a comment - 2015-01-16 07:47

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

Show

Jan Lindström added a comment - 2015-01-16 07:47 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

Hide

Permalink

Jan Lindström added a comment - 2015-01-16 08:41

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;

Show

Jan Lindström added a comment - 2015-01-16 08:41 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;

Hide

Permalink

Sergei Golubchik added a comment - 2015-01-16 10:54

ok to push

Show

Sergei Golubchik added a comment - 2015-01-16 10:54 ok to push

Hide

Permalink

Jan Lindström added a comment - 2015-01-16 12:02

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.

Show

Jan Lindström added a comment - 2015-01-16 12:02 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.

Assigned expression is evaluated twice when updating column TIMESTAMP NOT NULL

Details

Description

Gliffy Diagrams

Attachments

Attachments

Activity

People

Dates

Agile