Bug #34274 Invalid handling of 'DEFAULT 0' for YEAR data type.
Submitted: 4 Feb 2008 12:26 Modified: 26 Mar 2008 17:49
Reporter: Alexander Nozdrin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.1-bk OS:Any
Assigned to: Alexander Nozdrin
Triage: D4 (Minor)

[4 Feb 2008 12:26] Alexander Nozdrin
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)
[6 Feb 2008 10:02] Miguel Solorzano
Thank you for the bug report. Verified as described.
[13 Feb 2008 1:42] Omer Barnir
Workaround: specify the NOT NULL in the create command
[20 Mar 2008 8:05] Bugs System
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.
[22 Mar 2008 8:31] Bugs System
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.
[22 Mar 2008 8:35] Alexander Nozdrin
Pushed into 5.1-runtime.
[26 Mar 2008 14:13] Bugs System
Pushed into 5.1.24-rc
[26 Mar 2008 17:49] Paul Dubois
Noted in 5.1.24 changelog.

DEFAULT 0 was not allowed for the YEAR data type.
[27 Mar 2008 17:50] Bugs System
Pushed into 6.0.5-alpha
[2 Apr 2008 19:39] Jon Stephens
Also noted in the 5.1.23-ndb-6.3.11 and 6.0.5 changelogs.