Description:
No-op UPDATE assigning NULL to an already-NULL executes update, firing triggers etc
Broken case:
CREATE TABLE t1 (
id INT PRIMARY KEY,
c CHAR(8) NULL DEFAULT 'x',
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1, NULL, '2020-01-01 00:00:00');
# affected rows: 1
# info: Rows matched: 1 Changed: 1 Warnings: 0
UPDATE t1 SET c = NULL WHERE id = 1;
# Returns UPDATE timestamp
SELECT ts FROM t1 WHERE id = 1;
Working case (empty DEFAULT in the table definition, everything else the same)
CREATE TABLE t2 (
id INT PRIMARY KEY,
c CHAR(8) NULL DEFAULT '',
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;
INSERT INTO t2 VALUES (1, NULL, '2020-01-01 00:00:00');
# affected rows: 0
# info: Rows matched: 1 Changed: 0 Warnings: 0
UPDATE t2 SET c = NULL WHERE id = 1;
# original 2020-01-01 00:00:00
SELECT ts FROM t2 WHERE id = 1;
DROP TABLE t1, t2;
How to repeat:
MTR:
CREATE TABLE t1 (
id INT PRIMARY KEY,
c CHAR(8) NULL DEFAULT 'x',
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1, NULL, '2020-01-01 00:00:00');
--enable_info
UPDATE t1 SET c = NULL WHERE id = 1;
SELECT ts FROM t1 WHERE id = 1;
CREATE TABLE t2 (
id INT PRIMARY KEY,
c CHAR(8) NULL DEFAULT '',
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;
INSERT INTO t2 VALUES (1, NULL, '2020-01-01 00:00:00');
--enable_info
UPDATE t2 SET c = NULL WHERE id = 1;
SELECT ts FROM t2 WHERE id = 1;
DROP TABLE t1, t2;
Description: No-op UPDATE assigning NULL to an already-NULL executes update, firing triggers etc Broken case: CREATE TABLE t1 ( id INT PRIMARY KEY, c CHAR(8) NULL DEFAULT 'x', ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB; INSERT INTO t1 VALUES (1, NULL, '2020-01-01 00:00:00'); # affected rows: 1 # info: Rows matched: 1 Changed: 1 Warnings: 0 UPDATE t1 SET c = NULL WHERE id = 1; # Returns UPDATE timestamp SELECT ts FROM t1 WHERE id = 1; Working case (empty DEFAULT in the table definition, everything else the same) CREATE TABLE t2 ( id INT PRIMARY KEY, c CHAR(8) NULL DEFAULT '', ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB; INSERT INTO t2 VALUES (1, NULL, '2020-01-01 00:00:00'); # affected rows: 0 # info: Rows matched: 1 Changed: 0 Warnings: 0 UPDATE t2 SET c = NULL WHERE id = 1; # original 2020-01-01 00:00:00 SELECT ts FROM t2 WHERE id = 1; DROP TABLE t1, t2; How to repeat: MTR: CREATE TABLE t1 ( id INT PRIMARY KEY, c CHAR(8) NULL DEFAULT 'x', ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB; INSERT INTO t1 VALUES (1, NULL, '2020-01-01 00:00:00'); --enable_info UPDATE t1 SET c = NULL WHERE id = 1; SELECT ts FROM t1 WHERE id = 1; CREATE TABLE t2 ( id INT PRIMARY KEY, c CHAR(8) NULL DEFAULT '', ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB; INSERT INTO t2 VALUES (1, NULL, '2020-01-01 00:00:00'); --enable_info UPDATE t2 SET c = NULL WHERE id = 1; SELECT ts FROM t2 WHERE id = 1; DROP TABLE t1, t2;