Description:
When updating a partitioned table using a JOIN operation, if the partition key is modified, the referenced NEW.val does not correctly reflect CURRENT_TIMESTAMP as expected.
got
> select * from t1
+---------------------+---+
| a | b |
+---------------------+---+
| 2017-04-25 01:01:01 | 2 |
+---------------------+---+
> select @x
+---------------------+
| @x |
+---------------------+
| 2017-04-25 11:11:11 |
+---------------------+
explected
> select * from t1
+---------------------+---+
| a | b |
+---------------------+---+
| 2017-04-25 01:01:01 | 2 |
+---------------------+---+
> select @x
+---------------------+
| @x |
+---------------------+
| 2017-04-25 01:01:01 |
+---------------------+
How to repeat:
drop table if exists t1, t2;
drop trigger if exists trigger_before_update_with_join;
SET TIMESTAMP= UNIX_TIMESTAMP("2017-04-25 11:11:11");
CREATE TABLE t1( a DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, b INT) partition by range (b) (partition p0 values less than(10000), partition p1 values less than maxvalue);
CREATE TABLE t2( d INT);
INSERT INTO t1(b) VALUES(1);
INSERT INTO t2 VALUES(2);
CREATE TRIGGER trigger_before_update_with_join BEFORE UPDATE ON t1 FOR EACH ROW SET @x:= NEW.a;
SET TIMESTAMP= UNIX_TIMESTAMP("2017-04-25 01:01:01");
UPDATE t1, t2 SET t1.b= t2.d;
SELECT * FROM t1;
SELECT @x;
Description: When updating a partitioned table using a JOIN operation, if the partition key is modified, the referenced NEW.val does not correctly reflect CURRENT_TIMESTAMP as expected. got > select * from t1 +---------------------+---+ | a | b | +---------------------+---+ | 2017-04-25 01:01:01 | 2 | +---------------------+---+ > select @x +---------------------+ | @x | +---------------------+ | 2017-04-25 11:11:11 | +---------------------+ explected > select * from t1 +---------------------+---+ | a | b | +---------------------+---+ | 2017-04-25 01:01:01 | 2 | +---------------------+---+ > select @x +---------------------+ | @x | +---------------------+ | 2017-04-25 01:01:01 | +---------------------+ How to repeat: drop table if exists t1, t2; drop trigger if exists trigger_before_update_with_join; SET TIMESTAMP= UNIX_TIMESTAMP("2017-04-25 11:11:11"); CREATE TABLE t1( a DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, b INT) partition by range (b) (partition p0 values less than(10000), partition p1 values less than maxvalue); CREATE TABLE t2( d INT); INSERT INTO t1(b) VALUES(1); INSERT INTO t2 VALUES(2); CREATE TRIGGER trigger_before_update_with_join BEFORE UPDATE ON t1 FOR EACH ROW SET @x:= NEW.a; SET TIMESTAMP= UNIX_TIMESTAMP("2017-04-25 01:01:01"); UPDATE t1, t2 SET t1.b= t2.d; SELECT * FROM t1; SELECT @x;