Bug #20910 NOT NULL column reported as NULL in SHOW FIELDS or INFORMATION_SCHEMA

Description:
One TIMESTAMP column created as NOT NULL is being shown with the NULL attribute by SHOW FIELDS or querying INFORMATION_SCHEMA. Only SHOW CREATE TABLE reports the true NOT NULL attribute.

Thank you.
Marc Delisle
phpMyAdmin project

How to repeat:
mysql> CREATE TABLE `letemps` (
`id` int(11) NOT NULL auto_increment,
`username` varchar(80) NOT NULL default '',
`posted_on` timestamp NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

mysql> show fields from letemps;
+-----------+-------------+------+-----+---------------------+----------------+
| Field     | Type        | Null | Key | Default             | Extra          |
+-----------+-------------+------+-----+---------------------+----------------+
| id        | int(11)     | NO   | PRI | NULL                | auto_increment | 
| username  | varchar(80) | NO   |     | NULL                |                | 
| posted_on | timestamp   | YES  |     | 0000-00-00 00:00:00 |                | 
+-----------+-------------+------+-----+---------------------+----------------+
mysql> select is_nullable from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='letemps' and COLUMN_NAME='posted_on';
+-------------+
| is_nullable |
+-------------+
| YES         | 
+-------------+
1 row in set (0.01 sec)

mysql> show create table letemps;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| letemps | CREATE TABLE `letemps` (
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(80) NOT NULL default '',
  `posted_on` timestamp NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 | 
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
All commands should report the same information.

Thank you for a bug report. Verified just as described with 5.0.24-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.24

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

mysql> CREATE TABLE `letemps` (
    -> `id` int(11) NOT NULL auto_increment,
    -> `username` varchar(80) NOT NULL default '',
    -> `posted_on` timestamp NOT NULL default '0000-00-00 00:00:00',
    -> PRIMARY KEY (`id`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;
Query OK, 0 rows affected (0.13 sec)

mysql> show fields from letemps;
+-----------+-------------+------+-----+---------------------+----------------+
| Field     | Type        | Null | Key | Default             | Extra          |
+-----------+-------------+------+-----+---------------------+----------------+
| id        | int(11)     | NO   | PRI | NULL                | auto_increment |
| username  | varchar(80) | NO   |     |                     |                |
| posted_on | timestamp   | YES  |     | 0000-00-00 00:00:00 |                |
+-----------+-------------+------+-----+---------------------+----------------+
3 rows in set (0.02 sec)

mysql> select is_nullable from INFORMATION_SCHEMA.COLUMNS where
    -> TABLE_NAME='letemps' and COLUMN_NAME='posted_on';
+-------------+
| is_nullable |
+-------------+
| YES         |
+-------------+
1 row in set (0.09 sec)

mysql> show create table letemps\G
*************************** 1. row ***************************
       Table: letemps
Create Table: CREATE TABLE `letemps` (
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(80) NOT NULL default '',
  `posted_on` timestamp NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.03 sec)

So, inconsistency is obvious. It is a bug.

Column surely does not contain NULLs:

mysql> insert into letemps values(1, 'root', NULL);
Query OK, 1 row affected (0.10 sec)

mysql> select * from letemps;
+----+----------+---------------------+
| id | username | posted_on           |
+----+----------+---------------------+
|  1 | root     | 2006-07-08 11:11:16 |
+----+----------+---------------------+
1 row in set (0.00 sec)

mysql> update letemps set posted_on = NULL;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from letemps;
+----+----------+---------------------+
| id | username | posted_on           |
+----+----------+---------------------+
|  1 | root     | 2006-07-08 11:11:54 |
+----+----------+---------------------+
1 row in set (0.00 sec)

But, moreover, it works NOT as described in http://dev.mysql.com/doc/refman/5.0/en/timestamp-4-1.html (it is updated, although there is a constant DEFAULT value, and no ON UPDATE clause):

"In a CREATE TABLE statement, the first TIMESTAMP column can be declared in any of the following ways:

-     With both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses, the column has the current timestamp for its default value, and is automatically updated.
-     With neither DEFAULT nor ON UPDATE clauses, it is the same as DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.
-      With a DEFAULT CURRENT_TIMESTAMP clause and no ON UPDATE clause, the column has the current timestamp for its default value but is not automatically updated.
-      With no DEFAULT clause and with an ON UPDATE CURRENT_TIMESTAMP clause, the column has a default of 0 and is automatically updated.
-      With a constant DEFAULT value, the column has the given default. If the column has an ON UPDATE CURRENT_TIMESTAMP clause, it is automatically updated, otherwise not."

We have last case here, but column automatically updated when it is set to NULL. So, we may have two bugs here.

Additional to this,

1. it is impossible to set a timestamp field to NULL, no matter how it is described.

2. the default value seems always to be set at CURRENT_TIME() regardless of its stated value.

mysql> create table a(id int not null auto_increment, t1 timestamp, t2 timestamp default '0000-00-00 00:00:00', primary key (id));
Query OK, 0 rows affected (0.08 sec)

exercise the reported bug:

mysql> desc a;
+-------+-----------+------+-----+---------------------+----------------+
| Field | Type      | Null | Key | Default             | Extra          |
+-------+-----------+------+-----+---------------------+----------------+
| id    | int(11)   | NO   | PRI | NULL                | auto_increment | 
| t1    | timestamp | YES  |     | CURRENT_TIMESTAMP   |                | 
| t2    | timestamp | YES  |     | 0000-00-00 00:00:00 |                | 
+-------+-----------+------+-----+---------------------+----------------+
3 rows in set (0.27 sec)

mysql> show create table a;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                      |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| a     | CREATE TABLE `a` (
  `id` int(11) NOT NULL auto_increment,
  `t1` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `t2` timestamp NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 | 
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

try to insert null values as these are supposed to be nullable columns:

mysql> insert into a values(null, null, null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from a;
+----+---------------------+---------------------+
| id | t1                  | t2                  |
+----+---------------------+---------------------+
|  1 | 2006-07-18 18:59:15 | 2006-07-18 18:59:15 | 
+----+---------------------+---------------------+
1 row in set (0.00 sec)

Notice the value in t2 is not the stated default value

Now try to update to set null values:

mysql> update a set t1 = null, t2 = null;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from a;
+----+---------------------+---------------------+
| id | t1                  | t2                  |
+----+---------------------+---------------------+
|  1 | 2006-07-18 19:01:54 | 2006-07-18 19:01:54 | 
+----+---------------------+---------------------+
1 row in set (0.00 sec)

I have done all of the above with the same results on these OSes and versions:

* Mac OSX, mysql  Ver 14.12 Distrib 5.0.21, for apple-darwin8.5.1 (i686) using readline 5.0
* RedHat EL3, mysql  Ver 14.7 Distrib 4.1.19, for pc-linux-gnu (i686) using readline 4.3
* RedHat EL3, mysql  Ver 14.12 Distrib 5.0.22, for pc-linux-gnu (i686) using readline 5.0

There are at least 3 bugs here:

* inconsistent reporting of NOT NULL
* inability to set NULL values on a supposedly nullable column (why would a nullable column have a default value?)
* default value other than CURRENT_TIMESTAMP is ignored

I think it's pretty clear they're all related, so I am not filing Additional bugs on the second two.

Rachel,
here is what i see in the manual:
__________________________________________________
You can include the NULL attribute in the definition of a TIMESTAMP column to allow the column to contain NULL values. For example: 

CREATE TABLE t
(
  ts1 TIMESTAMP NULL DEFAULT NULL,
  ts2 TIMESTAMP NULL DEFAULT 0,
  ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
);

If the NULL attribute is not specified, setting the column to NULL sets it to the current timestamp. Note that a TIMESTAMP column which allows NULL values will not take on the current timestamp except under one of the following conditions: 

Its default value is defined as CURRENT_TIMESTAMP 

NOW() or CURRENT_TIMESTAMP is inserted into the column 

__________________________________________________

Doesn't that answer your question?

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/10148

ChangeSet@1.2231, 2006-08-08 13:34:27+05:00, holyfoot@mysql.com +3 -0
  bug #20910 (NOT NULL reported as NULL for TIMESTAMP)
  
  we intentionally reported that for TIMESTAMPS, which isn't right

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/10244

ChangeSet@1.2234, 2006-08-10 14:50:54+05:00, holyfoot@mysql.com +2 -0
  bug #20910 (NOT NULL column reported as NULL in SHOW FIELDS)
  
  two test results changed after the patch

Pushed to 5.0.25

Not a P1, tagged for 5.1, need a 5.1.x version number so we know which changelog the bugfix report is to go in. Thanks!

pushed to 5.0.25 and 5.1.12

Noted in 5.0.25 and 5.1.12 changelogs.

TIMESTAMP columns that are NOT NULL now are reported that way by SHOW
COLUMNS and INFORMATION_SCHEMA.