Description:
INFORMATION_SCHEMA.COLUMNS.COLUMN_DEFAULT shows NULL as a column's default value where it is explicitly created NOT NULL, whereas SHOW COLUMNS FROM reports that the default value is an empty string (''). The bug here is that information_schema.columns.column_default should also show an empty string.
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.0.45 |
+-----------+
1 row in set (0.03 sec)
mysql> create table default_test (
-> id int(10) unsigned not null auto_increment primary key,
-> notnull_vc varchar(255) not null,
-> null_vc varchar(255));
Query OK, 0 rows affected (0.46 sec)
mysql> show columns from default_test;
+------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| notnull_vc | varchar(255) | NO | | | |
| null_vc | varchar(255) | YES | | NULL | |
+------------+------------------+------+-----+---------+----------------+
3 rows in set (0.17 sec)
mysql> select column_name as field, column_type as type, is_nullable as `Null`, column_key as `Key`, column_default as `Default`, extra from information_schema.columns where table_name='default_test';
+------------+------------------+------+-----+---------+----------------+
| field | type | Null | Key | Default | extra |
+------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| notnull_vc | varchar(255) | NO | | NULL | |
| null_vc | varchar(255) | YES | | NULL | |
+------------+------------------+------+-----+---------+----------------+
3 rows in set (0.09 sec)
There is another, similar, bug (26544), but not exact.
How to repeat:
Execute the SQL above.
Description: INFORMATION_SCHEMA.COLUMNS.COLUMN_DEFAULT shows NULL as a column's default value where it is explicitly created NOT NULL, whereas SHOW COLUMNS FROM reports that the default value is an empty string (''). The bug here is that information_schema.columns.column_default should also show an empty string. mysql> select @@version; +-----------+ | @@version | +-----------+ | 5.0.45 | +-----------+ 1 row in set (0.03 sec) mysql> create table default_test ( -> id int(10) unsigned not null auto_increment primary key, -> notnull_vc varchar(255) not null, -> null_vc varchar(255)); Query OK, 0 rows affected (0.46 sec) mysql> show columns from default_test; +------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | notnull_vc | varchar(255) | NO | | | | | null_vc | varchar(255) | YES | | NULL | | +------------+------------------+------+-----+---------+----------------+ 3 rows in set (0.17 sec) mysql> select column_name as field, column_type as type, is_nullable as `Null`, column_key as `Key`, column_default as `Default`, extra from information_schema.columns where table_name='default_test'; +------------+------------------+------+-----+---------+----------------+ | field | type | Null | Key | Default | extra | +------------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | notnull_vc | varchar(255) | NO | | NULL | | | null_vc | varchar(255) | YES | | NULL | | +------------+------------------+------+-----+---------+----------------+ 3 rows in set (0.09 sec) There is another, similar, bug (26544), but not exact. How to repeat: Execute the SQL above.