MySQL Bugs: #26849: False default value in describe $table

Bug #26849	False default value in describe $table
Submitted:	5 Mar 2007 21:44	Modified:	21 Apr 2008 7:37
Reporter:	Ask Hansen (Basic Quality Contributor)	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S2 (Serious)
Version:	5.0.38-BK, 5.0.27	OS:	Any (all)
Assigned to:	Sergei Glukhov	CPU Architecture:	Any
Tags:	DBD::MySQL, default values, DESCRIBE, information_schema, qc

Description:
"describe table" claims a "" (empty string, not-NULL) default value for "not null" columns without an explicit default value.

information_schema.columns does have the expected data (NULL).

This makes DBD::mysql report a default value for columns where none should be specified (filed in a separate bug http://bugs.mysql.com/bug.php?id=26544 )

How to repeat:
CREATE TABLE `bars` (
  `id` int(10) unsigned NOT NULL,
  `foo` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=

mysql> select column_default from information_schema.columns where column_name = 'foo' and table_name = 'bars';
+----------------+
| column_default |
+----------------+
| NULL           | 
+----------------+
1 row in set (0.00 sec)

mysql> describe bars;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | NO   | PRI |         |       | 
| foo   | varchar(255)     | NO   |     |         |       | 
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Suggested fix:
Fix DESCRIBE to show "NULL" as the DEFAULT value for NOT NULL columns without an explicit default value.

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

Bug #33716 was marked as a duplicate of this one. Although, reporter demands different behaviour there for INFOERMATION_SCHEMA...

can't repeat on lates 5.0 tree

mysql> create table t1(f1 int(10) unsigned NOT NULL,                                              
    ->                 f2 varchar(255) NOT NULL,                                                  
    ->                 PRIMARY KEY (f1)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> select column_default from information_schema.columns where table_name = 't1';
+----------------+
| column_default |
+----------------+
| NULL           | 
| NULL           | 
+----------------+
2 rows in set (0.00 sec)

mysql> describe t1;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| f1    | int(10) unsigned | NO   | PRI | NULL    |       | 
| f2    | varchar(255)     | NO   |     | NULL    |       | 
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

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

mysql>

I can see the same behavior on MySQL 5.0.46, but I cannot see this behavior on MySQL 5.0.50sp1, so this bug seems to be solved MySQL 5.0.50sp1 or later. 
(There is no description on release notes of 5.0.50/5.0.50sp1).