MySQL Bugs: #22377: iNCONSISTENCY WITH null

Bug #22377	iNCONSISTENCY WITH null
Submitted:	15 Sep 2006 4:55	Modified:	8 Feb 2007 18:21
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.28-BK	OS:	Linux (Linux)
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any

Description:
SHOW CREATE TABLE and SHOW FULL FIELDS reports NULL/NOT NULL differently with TIMESTAMPs

How to repeat:
show create table aaa;

/* returns
CREATE TABLE `aaaa` ( 
`id` bigint(20) NOT NULL auto_increment,
`ts` timestamp(14) NOT NULL, 
PRIMARY KEY (`id`)) TYPE=MyISAM  
*/

show full fields from aaaa;

/* returns
Field   Type           Null    Key     Default  Extra           Privileges 
------  -------------  ------  ------  -------  --------------  -------------------------------
id      bigint(20)             PRI     (NULL)   auto_increment  select,insert,update,references
ts      timestamp(14)  YES             (NULL)                   select,insert,update,references
/* returns

It the timestamp NULL or NOT NULL?

This is an example with MySQL 4.0.27 but 5.1.11 shows the same inconsistent behaviour.

Suggested fix:
SHOW FULL FIELDS should reflect the CREATE statement.

Thank you for a problem report. I was not able to repeat the behaviour described with 5.0.26-BK:

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.26-debug

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

mysql> CREATE TABLE `aaaa` (
    -> `id` bigint(20) NOT NULL auto_increment,
    -> `ts` timestamp(14) NOT NULL,
    -> PRIMARY KEY (`id`)) TYPE=MyISAM  ;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> show full fields from aaaa;
+-------+------------+-----------+------+-----+-------------------+-------------
---+---------------------------------+---------+
| Field | Type       | Collation | Null | Key | Default           | Extra
   | Privileges                      | Comment |
+-------+------------+-----------+------+-----+-------------------+-------------
---+---------------------------------+---------+
| id    | bigint(20) | NULL      | NO   | PRI | NULL              | auto_increme
nt | select,insert,update,references |         |
| ts    | timestamp  | NULL      | NO   |     | CURRENT_TIMESTAMP |
   | select,insert,update,references |         |
+-------+------------+-----------+------+-----+-------------------+-------------
---+---------------------------------+---------+
2 rows in set (0.01 sec)

and with 5.1.12-BK:

openxs@suse:~/dbs/5.1> 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 2 to server version: 5.1.12-beta-debug

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

mysql> CREATE TABLE `aaaa` (  `id` bigint(20) NOT NULL auto_increment, `ts` tim
estamp(14) NOT NULL,  PRIMARY KEY (`id`)) TYPE=MyISAM;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> show full fields from aaaa;
+-------+------------+-----------+------+-----+-------------------+-------------
---+---------------------------------+---------+
| Field | Type       | Collation | Null | Key | Default           | Extra
   | Privileges                      | Comment |
+-------+------------+-----------+------+-----+-------------------+-------------
---+---------------------------------+---------+
| id    | bigint(20) | NULL      | NO   | PRI | NULL              | auto_increme
nt | select,insert,update,references |         |
| ts    | timestamp  | NULL      | NO   |     | CURRENT_TIMESTAMP |
   | select,insert,update,references |         |
+-------+------------+-----------+------+-----+-------------------+-------------
---+---------------------------------+---------+
2 rows in set (0.01 sec)

But with 4.0.28-BK it is repeatable:

openxs@suse:~/dbs/4.0> bin/mysql -uroot -proot -P3340 -h 127.0.0.1 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 2 to server version: 4.0.28-log

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

mysql> CREATE TABLE `aaaa` (  `id` bigint(20) NOT NULL auto_increment, `ts` tim
estamp(14) NOT NULL,  PRIMARY KEY (`id`)) TYPE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> show full fields from aaaa; bigint(20) NOT NU
+-------+---------------+------+-----+---------+----------------+---------------
------------------+
| Field | Type          | Null | Key | Default | Extra          | Privileges
                  |
+-------+---------------+------+-----+---------+----------------+---------------
------------------+
| id    | bigint(20)    |      | PRI | NULL    | auto_increment | select,insert,
update,references |
| ts    | timestamp(14) | YES  |     | NULL    |                | select,insert,
update,references |
+-------+---------------+------+-----+---------+----------------+---------------
------------------+
2 rows in set (0.00 sec)

yes - seems fixed in 5.0.25
http://bugs.mysql.com/bug.php?id=20910

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/13108

ChangeSet@1.2543, 2006-10-05 17:29:50+05:00, ramil@mysql.com +3 -0
  Fix for bug #22377: iNCONSISTENCY WITH null
  
  Backport. See #20910: NOT NULL column reported as NULL in SHOW FIELDS or INFORMATION_SCHEMA

Available in 4.1.23, 5.0.36, 5.1.15-beta.

Noted in 4.1.23, 5.0.36, 5.1.15 changelogs.

SHOW COLUMNS reported some NOT NULL columns as NULL.