Bug #17626 CREATE TABLE ... SELECT failure with TRADITIONAL SQL mode

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.