Bug #10967 SHOW COLUMNS FROM does not return NULL for no DEFAULT value
Submitted: 30 May 2005 17:36 Modified: 30 May 2005 17:47
Reporter: Michael G. Zinner Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.4 OS:Windows (Windows)
Assigned to: CPU Architecture:Any

[30 May 2005 17:36] Michael G. Zinner
Description:
SHOW [FULL] COLUMNS FROM makes it impossible to distinguish between no default value and '' as default value for string and other types. It works for numeric types.

Same in the information schema.

How to repeat:
DROP TABLE IF EXISTS `test`.`pic`;
CREATE TABLE `pic` (
  `idpic` int(10) unsigned NOT NULL auto_increment,
  `caption` varchar(45) NOT NULL,
  `img` longblob NOT NULL,
  PRIMARY KEY  (`idpic`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

mysql> SHOW COLUMNS FROM test.pic;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| idpic   | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| caption | varchar(45)      | NO   |     |         |                |
| img     | longblob         | NO   |     |         |                |
+---------+------------------+------+-----+---------+----------------+

All rows should show NULL in the Default column.

SELECT column_name, column_default 
FROM information_schema.columns C, information_schema.tables t
WHERE c.table_schema=t.table_schema AND 
 c.table_name=t.table_name AND
 t.table_schema='test' AND 
 t.table_name='pic';

+-------------+----------------+
| column_name | column_default |
+-------------+----------------+
| idpic       | NULL           |
| caption     |                |
| img         |                |
+-------------+----------------+

Same thing.

Suggested fix:
Return NULL if no DEFAULT value exists in all cases.
[30 May 2005 17:47] Michael G. Zinner
I forgot that NOT NULL will force the creation of DEFAULT ''