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

Details

Type: Bug
Status: Closed (View Workflow)
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);

Attachments

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

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

Activity

Ascending order - Click to sort in descending order

View 9 older comments

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

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

Jan Lindström (Inactive) 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

Jan Lindström (Inactive) 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

Jan Lindström (Inactive) 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;

Jan Lindström (Inactive) 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;

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

ok to push

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

Jan Lindström (Inactive) 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.

Jan Lindström (Inactive) 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.

MariaDB Server

Assigned expression is evaluated twice when updating column TIMESTAMP NOT NULL

Details

Description

Attachments

Attachments

Activity

People

Dates

Git Integration