Bug #34274 Invalid handling of 'DEFAULT 0' for YEAR data type.

Description:
'DEFAULT 0' clause is handled incorrectly for YEAR data type:
'YEAR DEFAULT 0' means 'YEAR DEFAULT NULL'.

However, for any other date types 'DEFAULT 0' means 'NOT NULL DEFAULT 0'.

How to repeat:
> CREATE TABLE t1(c1 YEAR DEFAULT 0);
> CREATE TABLE t2(c1 DATE DEFAULT 0);

> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` year(4) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
  -- Note 'DEFAULT NULL' in the output.

> SHOW CREATE TABLE t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `c1` date DEFAULT '0000-00-00'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

> INSERT INTO t1 VALUES ();
> INSERT INTO t2 VALUES ();

> SELECT * FROM t1;
+------+
| c1   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

> SELECT * FROM t2;
+------------+
| c1         |
+------------+
| 0000-00-00 |
+------------+
1 row in set (0.00 sec)

Thank you for the bug report. Verified as described.

Workaround: specify the NOT NULL in the create command

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

ChangeSet@1.2566, 2008-03-20 11:06:59+03:00, anozdrin@quad. +3 -0
  Fix for Bug#34274: Invalid handling of 'DEFAULT 0'
  for YEAR data type.
  
  The problem was that for some unknown reason 0 was not allowed
  as a default value for YEAR data type. That was coded before BK.
  However the Manual does not say a word about such a limitation.
  Also, it looks inconsistent with other data types.
  
  The fix is to allow 0 as a default value.

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

ChangeSet@1.2570, 2008-03-22 11:32:24+03:00, anozdrin@quad.opbmk +3 -0
  Fix for Bug#34274: Invalid handling of 'DEFAULT 0'
  for YEAR data type.
  
  The problem was that for some unknown reason 0 was not allowed
  as a default value for YEAR data type. That was coded before BK.
  However the Manual does not say a word about such a limitation.
  Also, it looks inconsistent with other data types.
  
  The fix is to allow 0 as a default value.

Pushed into 5.1-runtime.

Pushed into 5.1.24-rc

Noted in 5.1.24 changelog.

DEFAULT 0 was not allowed for the YEAR data type.

Pushed into 6.0.5-alpha

Also noted in the 5.1.23-ndb-6.3.11 and 6.0.5 changelogs.