MySQL Bugs: #46719: TIMESTAMP column updated with no changes when a DECIMAL column is NULL

Bug #46719	TIMESTAMP column updated with no changes when a DECIMAL column is NULL
Submitted:	14 Aug 2009 11:36	Modified:	17 Aug 2009 11:43
Reporter:	Yasuo Shirai	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.45	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	decimal, timestamp

Description:
TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP column always updated when a DECIMAL column set from NULL to NULL (actually no change) on InnoDB table.

How to repeat:
1. Create table below

CREATE TABLE test
(
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
 value DECIMAL(8,1) UNSIGNED,
 stamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL
) ENGINE = 'InnoDB';

2. Insert a row with a DECIMAL column set to NULL

mysql> INSERT INTO test ( value ) VALUES ( NULL );
Query OK, 1 row affected (0.03 sec)

3. Check the TIMESTAMP column

mysql> SELECT * FROM test;
+----+-------+---------------------+
| id | value | stamp               |
+----+-------+---------------------+
|  1 |  NULL | 2009-08-14 20:32:19 | 
+----+-------+---------------------+
1 row in set (0.00 sec)

4. Update the DECIMAL column with NULL (actually no change)

mysql> UPDATE test SET value = NULL WHERE id = 1;
Query OK, 1 row affected (0.11 sec)
Rows matched: 1  Changed: 1  Warnings: 0

5. Check the TIMESTAMP column again

mysql> SELECT * FROM test;
+----+-------+---------------------+
| id | value | stamp               |
+----+-------+---------------------+
|  1 |  NULL | 2009-08-14 20:32:34 | 
+----+-------+---------------------+
1 row in set (0.00 sec)

the TIMESTAMP column is updated.

Thank you for the bug report. I couldn't repeat with latest server source tree:

c:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.85-Win X64 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.0 > use test
Database changed
mysql 5.0 > CREATE TABLE test
    -> (
    ->  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
    ->  value DECIMAL(8,1) UNSIGNED,
    ->  stamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL
    -> ) ENGINE = 'InnoDB';
Query OK, 0 rows affected (0.17 sec)

mysql 5.0 > INSERT INTO test ( value ) VALUES ( NULL );
Query OK, 1 row affected (0.06 sec)

mysql 5.0 > SELECT * FROM test;
+----+-------+---------------------+
| id | value | stamp               |
+----+-------+---------------------+
|  1 |  NULL | 2009-08-14 10:01:35 |
+----+-------+---------------------+
1 row in set (0.00 sec)

mysql 5.0 > UPDATE test SET value = NULL WHERE id = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql 5.0 > SELECT * FROM test;
+----+-------+---------------------+
| id | value | stamp               |
+----+-------+---------------------+
|  1 |  NULL | 2009-08-14 10:01:35 |
+----+-------+---------------------+
1 row in set (0.00 sec)

mysql 5.0 >

I found this bug first on v5.0.45 on Ubuntu.

Additionally, I tested on v5.0.83 on Mac OS X v10.4. The bug repeated like following.
Any additional information required?

I guess that this bug was fixed between v5.0.84 and v5.0.85 or Windows version does not have this bug.

$ mysql -u root 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.83-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
Database changed
mysql> CREATE TABLE test
    -> (
    ->  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
    ->  value DECIMAL(8,1) UNSIGNED,
    ->  stamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL
    -> ) ENGINE = 'InnoDB';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test ( value ) VALUES ( NULL );
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test;
+----+-------+---------------------+
| id | value | stamp               |
+----+-------+---------------------+
|  1 |  NULL | 2009-08-17 20:34:30 | 
+----+-------+---------------------+
1 row in set (0.00 sec)

mysql> UPDATE test SET value = NULL WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM test;
+----+-------+---------------------+
| id | value | stamp               |
+----+-------+---------------------+
|  1 |  NULL | 2009-08-17 20:34:43 | 
+----+-------+---------------------+
1 row in set (0.00 sec)

mysql>