| 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: | |
| 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
[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.
