Bug #20910 NOT NULL column reported as NULL in SHOW FIELDS or INFORMATION_SCHEMA
Submitted: 7 Jul 2006 21:06 Modified: 13 Sep 2006 2:14
Reporter: Marc Delisle Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.24-BK, 5.0.21 OS:Linux (Linux)
Assigned to: Alexey Botchkov CPU Architecture:Any

[7 Jul 2006 21:06] Marc Delisle
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.
[8 Jul 2006 10:07] Valeriy Kravchuk
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.
[19 Jul 2006 2:12] Rachel McConnell
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.
[8 Aug 2006 7:34] Alexey Botchkov
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?
[8 Aug 2006 8:10] Bugs System
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
[10 Aug 2006 9:26] Bugs System
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
[16 Aug 2006 20:53] Reggie Burnett
Pushed to 5.0.25
[18 Aug 2006 14:28] Jon Stephens
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!
[18 Aug 2006 14:53] Reggie Burnett
pushed to 5.0.25 and 5.1.12
[13 Sep 2006 2:14] Paul Dubois
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.