Bug #3844 Cannot set a default NULL value for 2nd TIMESTAMP column

• Submitted: 21 May 2004 10:30
• Modified: 21 May 2004 18:12
• Reporter: Marty Brands
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.0.20
• OS: Linux (Linux 2.4.26)
• Assigned to: Dean Ellis
• CPU Architecture: Any

[21 May 2004 10:30] Marty Brands

Description:
Setting a default of "NULL" for 2nd (and further) timetamp columns works with version 4.0.15, there was a change in 4.0.17 which should have been fixed in 4.0.18 however the problem is still there. When you have a default of "NULL" for a 2nd (or further) timestamp column it always returns 00000000000000 as the default value.

How to repeat:
Version 4.0.18 (Debian package) and 4.0.20 (downloaded from mysql.com):
mysql> create table test (
    -> ts1 timestamp not null,
    -> ts2 timestamp default NULL
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> describe test;
+-------+---------------+------+-----+----------------+-------+
| Field | Type          | Null | Key | Default        | Extra |
+-------+---------------+------+-----+----------------+-------+
| ts1   | timestamp(14) | YES  |     | NULL           |       |
| ts2   | timestamp(14) | YES  |     | 00000000000000 |       |
+-------+---------------+------+-----+----------------+-------+

Version 4.0.15 (downloaded from mysql.com):
mysql> create table test (
    -> ts1 timestamp not null,
    -> ts2 timestamp default null
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> describe test;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| ts1   | timestamp(14) | YES  |     | NULL    |       |
| ts2   | timestamp(14) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+

[21 May 2004 18:12] Dean Ellis

This is tricky, but I have to view it as not a bug.  What you are asking for here would basically violate the defined/documented behavior for TIMESTAMP columns.

Only the first column receives auto-generated date/time; setting any TIMESTAMP column to NULL generates current date/time; having more than one TIMESTAMP column default to NULL would circumvent this.