MySQL Bugs: #17626: CREATE TABLE ... SELECT failure with TRADITIONAL SQL mode

Bug #17626	CREATE TABLE ... SELECT failure with TRADITIONAL SQL mode
Submitted:	21 Feb 2006 19:28	Modified:	24 May 2006 17:54
Reporter:	Paul DuBois	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.x/5.1.x	OS:	Linux (Linux/Windows)
Assigned to:	Sergei Glukhov	CPU Architecture:	Any

Description:
This CREATE ... SELECT statement works:
I asked this on dev-*@:

DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1 (date DATE NOT NULL);
CREATE TABLE t2 SELECT date FROM t1;

This one fails with ERROR 1067 (42000): Invalid default value for 'date':

SET sql_mode='traditional';
DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1 (date DATE NOT NULL);
CREATE TABLE t2 SELECT date FROM t1;

I don't know whether to expect this or whether it's a bug.  Can
someone tell me?

Serg replied:

Apparently, the second table is created with DEFAULT '0000-00-00',
which is not allowed in traditional mode.

I'd call it a bug - a column should be created with no default value.
Though it's kind of incompatible change in behavior.

How to repeat:
See above.

Thank you for the bug 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/4439

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

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

Fixed in 5.0.22

Noted in 5.0.23 changelog.

<literal>CREATE TABLE ... SELECT</literal> did not always
produce the proper column default value in
<literal>TRADITIONAL</literal> SQL mode.