MySQL Bugs: #18852: DESCRIBE output shows TIMESTAMP columns always NULL

Bug #18852	DESCRIBE output shows TIMESTAMP columns always NULL
Submitted:	6 Apr 2006 15:27	Modified:	8 Aug 2006 13:42
Reporter:	Andreas Krueger	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.21-BK, 5.0.17-nt	OS:	Linux (Linux, Windows XP Home SP2)
Assigned to:		CPU Architecture:	Any

Description:
Using mysql client on Windows XP Home with MySQL Server 5.0.17 installed, I observe a bug in the DESCRIBE output for TIMESTAMP columns. In the columns of the DESCRIBE output, the column with title 'Null' always(!) shows NULL for TIMESTAMP values. No matter, whether one defines them as NULL or NOT NULL. The behavior although under insertion of plain NULL values into that column is different, as the following example shows:

D:\Programme\Microsoft Visual Studio 8\VC>mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.17-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE nulltest ( ts TIMESTAMP );
Query OK, 0 rows affected (0.06 sec)

mysql> DESCRIBE nulltest;
+-------+-----------+------+-----+-------------------+-------+
| Field | Type      | Null | Key | Default           | Extra |
+-------+-----------+------+-----+-------------------+-------+
| ts    | timestamp | YES  |     | CURRENT_TIMESTAMP |       |
+-------+-----------+------+-----+-------------------+-------+
1 row in set (0.02 sec)

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

mysql> SELECT * FROM nulltest;
+---------------------+
| ts                  |
+---------------------+
| 2006-04-06 17:17:49 |
+---------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE nulltest MODIFY ts TIMESTAMP NULL;
Query OK, 1 row affected (0.16 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO nulltest VALUES (NULL);
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM nulltest;
+---------------------+
| ts                  |
+---------------------+
| 2006-04-06 17:17:49 |
|                NULL |
+---------------------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE nulltest MODIFY ts TIMESTAMP;
Query OK, 2 rows affected (0.11 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> DESCRIBE nulltest;
+-------+-----------+------+-----+-------------------+-------+
| Field | Type      | Null | Key | Default           | Extra |
+-------+-----------+------+-----+-------------------+-------+
| ts    | timestamp | YES  |     | CURRENT_TIMESTAMP |       |
+-------+-----------+------+-----+-------------------+-------+
1 row in set (0.01 sec)

mysql> ALTER TABLE nulltest MODIFY ts TIMESTAMP NOT NULL;
Query OK, 2 rows affected (0.14 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> DESCRIBE nulltest;
+-------+-----------+------+-----+-------------------+-------+
| Field | Type      | Null | Key | Default           | Extra |
+-------+-----------+------+-----+-------------------+-------+
| ts    | timestamp | YES  |     | CURRENT_TIMESTAMP |       |
+-------+-----------+------+-----+-------------------+-------+
1 row in set (0.02 sec)

How to repeat:
Start MySQL client from the MS-DOS prompt.

Repeat the above steps in the example.

Suggested fix:
The DESCRIBE output for TIMESTAMP columns that are NOT NULL (don' t allows NULL values) must show 'NO' in the referring column.

Thank you for a bug report. Verified just as described with 5.0.21-BK (ChangeSet@1.2143, 2006-04-10 08:29:24+02:00) on Linux:

mysql> CREATE TABLE nulltest ( ts TIMESTAMP );
Query OK, 0 rows affected (0.01 sec)

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

mysql> SELECT * FROM nulltest;
+---------------------+
| ts                  |
+---------------------+
| 2006-04-10 12:49:06 |
+---------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE nulltest MODIFY ts TIMESTAMP NULL;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO nulltest VALUES (NULL);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM nulltest;
+---------------------+
| ts                  |
+---------------------+
| 2006-04-10 12:49:06 |
| NULL                |
+---------------------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE nulltest MODIFY ts TIMESTAMP;
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> DESCRIBE nulltest;
+-------+-----------+------+-----+-------------------+-------+
| Field | Type      | Null | Key | Default           | Extra |
+-------+-----------+------+-----+-------------------+-------+
| ts    | timestamp | YES  |     | CURRENT_TIMESTAMP |       |
+-------+-----------+------+-----+-------------------+-------+
1 row in set (0.01 sec)

mysql> ALTER TABLE nulltest MODIFY ts TIMESTAMP NOT NULL;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> DESCRIBE nulltest;
+-------+-----------+------+-----+-------------------+-------+
| Field | Type      | Null | Key | Default           | Extra |
+-------+-----------+------+-----+-------------------+-------+
| ts    | timestamp | YES  |     | CURRENT_TIMESTAMP |       |
+-------+-----------+------+-----+-------------------+-------+
1 row in set (0.01 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.21    |
+-----------+
1 row in set (0.00 sec)

Test case:

-- bug #18852
CREATE TABLE nulltest ( ts TIMESTAMP );
INSERT INTO nulltest VALUES (NULL);
ALTER TABLE nulltest MODIFY ts TIMESTAMP NULL;
INSERT INTO nulltest VALUES (NULL);
ALTER TABLE nulltest MODIFY ts TIMESTAMP;
DESCRIBE nulltest;
ALTER TABLE nulltest MODIFY ts TIMESTAMP NOT NULL;
DESCRIBE nulltest; 
-- same result as previous one. NO expected!

This is a duplicate of the bug#20910