MySQL Bugs: #14947: Missing Default-Value "empty string"

Bug #14947	Missing Default-Value "empty string"
Submitted:	15 Nov 2005 13:24	Modified:	15 Nov 2005 14:36
Reporter:	Bernhard Geyer	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.15	OS:	Windows (XP SP2)
Assigned to:		CPU Architecture:	Any

Description:
If creating a varchar-column with a "NOT NULL"-Option MySQL should defines the column with an explicit DEFAULT clause, for varchar with a empty string

(http://dev.mysql.com/doc/refman/5.0/en/create-table.html, 
Text:  If the column cannot take NULL as the value, MySQL defines the column ...)

This doesn't work for MySQL 5.0.15.
I get a error: "Field 'Not_Null' doesn't have a default value".

How to repeat:
Define a Table like

create table test (
  Not_Null varchar(10) NOT NULL,
  Null_OK varchar(10));

Try to insert following

insert into test(Null_OK) values('x');

This works for 4.1/4.0/3.23, but not for 5.0.15.

Thank you for a problem report. Looks like not a bug for me, but just to be sure, please, send the results of the

show variables like 'sql_mode%';

and

show create table test;

statements on your system.

On Linux I have:

mysql> create table test (
    ->    Not_Null varchar(10) NOT NULL,
    ->   Null_OK varchar(10));
Query OK, 0 rows affected (0,00 sec)

mysql> insert into test(Null_OK) values('x');
Query OK, 1 row affected, 1 warning (0,01 sec)

mysql> show variables like 'sql_%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
| sql_notes     | ON    |
| sql_warnings  | ON    |
+---------------+-------+
3 rows in set (0,00 sec)

mysql> show create table test;
+-------+--------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                               |
+-------+--------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `Not_Null` varchar(10) NOT NULL,
  `Null_OK` varchar(10) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.17    |
+-----------+
1 row in set (0,00 sec)

I hope, the results, as well as the following page, http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html, will explain you the reason.

show variables like 'sql_mode%';

sql_mode = STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

show create table test;

CREATE TABLE `test` (
  `Not_Null` varchar(10) NOT NULL,
  `Null_OK` varchar(10) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

So, you have 5.0.15, InnoDB (transactional) table, strict mode enabled by default on Windows (STRICT_TRANS_TABLES). Now keep reading the proper part of http://dev.mysql.com/doc/refman/5.0/en/create-table.html:

"As of MySQL 5.0.2, if a column definition includes no explicit DEFAULT value, MySQL determines the default value as follows:

If the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause. This is the same as before 5.0.2.

If the column cannot take NULL as the value, MySQL defines the column with no explicit DEFAULT clause. For data entry, if an INSERT or REPLACE statement includes no value for the column, MySQL handles the column according to the SQL mode in effect at the time:
- If strict mode is not enabled, MySQL sets the column to the implicit default value for the column data type.
-  If strict mode is enabled, an error occurs for transactional tables and the statement is rolled back."

The last sentence describe exactly what you got. It is not a bug, but intended and documented behaviour.

"Only" confusing because default-installation behaviour is changed.