MySQL Bugs: #47705: Inconsistency with TEXT/BLOB defaults

Bug #47705	Inconsistency with TEXT/BLOB defaults
Submitted:	29 Sep 2009 9:44	Modified:	19 Aug 2010 7:35
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.1.39	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
TEXT/BLOBs cannot have defaults.  However sometimes an error and sometimes a warning only is returned when a default is specified.

How to repeat:
SHOW VARIABLES LIKE 'sql_mode'; -- '' (empty mode)

CREATE TABLE a (b TEXT DEFAULT 'c');
/*
Error Code : 1101
BLOB/TEXT column 'b' can't have a default value"
*/

CREATE TABLE a (b TEXT DEFAULT ''); -- executed with a warning

SHOW WARNINGS;
/*
Level      Code  Message                                        
-------  ------  -----------------------------------------------
Warning    1101  BLOB/TEXT column 'b' can't have a default value
*/

SHOW CREATE TABLE a;
/*
CREATE TABLE `a` (
   `b` text
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
*/

Suggested fix:
Make it consistent.  I would prefer an error in all cases - also with this SQL_mode.

Thank you for the problem report.

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

2944 Vladislav Vaintroub	2009-12-21
      Bug#49834 - fixed a bug introduced by mismerge.
      Remove version checks and 2 incompatible MYSQL_STORAGE_ENGINE()
      Currently, a single CMakeLists.txt cannot have more that 1 MYSQL_STORAGE_ENGINE()
      with different engines, due to Bug#47705

This bug seems to have been set to patch-pending by accident (Referred to in a commit mail for another bug fix)