Bug #34277 NO_ZERO_IN_DATE does not work properly
Submitted: 4 Feb 2008 13:38 Modified: 7 Feb 2008 4:02
Reporter: Alexander Nozdrin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.0/5.1/6.0 OS:Any
Assigned to: Paul Dubois CPU Architecture:Any
Triage: D4 (Minor)

[4 Feb 2008 13:38] Alexander Nozdrin
Description:
According to The Manual
(http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html):
<quote>
* NO_ZERO_IN_DATE

In strict mode, don't accept dates where the month or day part is 0.
If used with the IGNORE option, MySQL inserts a '0000-00-00' date for
any such date. When not in strict mode, the date is accepted but a warning
is generated.
</quote>

A date is considered invalid for this mode only if year part of
the date is not zero.

I.e.

  - 0000-00-00 00:00:00 -- is a valid date for NO_ZERO_IN_DATE

  - 0001-00-00 00:00:00 -- is not a valid date.

If this was the intention, the documentation should be updated.
Otherwise, the code should be fixed.

How to repeat:
set sql_mode = '';

DROP TABLE IF EXISTS t1, t2, t3, t4, t5;

CREATE TABLE t1(c1 TIMESTAMP NULL);
CREATE TABLE t2(c1 DATETIME DEFAULT NULL);
CREATE TABLE t3(c1 DATE DEFAULT NULL);
CREATE TABLE t4(c1 TIME DEFAULT NULL);
CREATE TABLE t5(c1 YEAR DEFAULT NULL);

set sql_mode = NO_ZERO_IN_DATE;

INSERT INTO t1 VALUES ('0000-00-00 00:00:00');
INSERT INTO t2 VALUES ('0000-00-00 00:00:00');
INSERT INTO t3 VALUES ('0000-00-00');
INSERT INTO t4 VALUES ('00:00:00');
INSERT INTO t5 VALUES ('0000');

set sql_mode = 'NO_ZERO_IN_DATE,STRICT_ALL_TABLES';

INSERT INTO t1 VALUES ('0000-00-00 00:00:00');
INSERT INTO t2 VALUES ('0000-00-00 00:00:00');
INSERT INTO t3 VALUES ('0000-00-00');
INSERT INTO t4 VALUES ('00:00:00');
INSERT INTO t5 VALUES ('0000');

SELECT * FROM t1;
SELECT * FROM t2;
SELECT * FROM t3;
SELECT * FROM t4;
SELECT * FROM t5;

-- All statements executed successfully. No warning or error returned.
-- Each table contains two rows.
[4 Feb 2008 13:45] Alexander Nozdrin
Another thing: CREATE TABLE fails even in non-strict mode if default value
is not correct according to NO_ZERO_IN_DATE:

set sql_mode = 'NO_ZERO_IN_DATE';

DROP TABLE IF EXISTS t1, t2, t3, t4, t5;

CREATE TABLE t1(c1 TIMESTAMP DEFAULT '0001-00-00 00:00:00');
  -- ERROR 1067 (42000): Invalid default value for 'c1'

CREATE TABLE t2(c1 DATETIME DEFAULT  '0001-00-00 00:00:00');
  -- ERROR 1067 (42000): Invalid default value for 'c1'

CREATE TABLE t3(c1 DATE DEFAULT '0001-00-00');
  -- ERROR 1067 (42000): Invalid default value for 'c1'

CREATE TABLE t4(c1 TIME DEFAULT '00:00:00');
  -- Ok

CREATE TABLE t5(c1 YEAR DEFAULT '0000');
  -- Ok

If this behavior is desirable, it seems the documentation
should be updated.
[4 Feb 2008 14:28] Miguel Solorzano
Thank you for the bug report. Verified as described.
[5 Feb 2008 12:58] Sergei Golubchik
documentation bug
[7 Feb 2008 4:02] Paul Dubois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Revised description for NO_ZERO_IN_DATE:

In strict mode, do not accept dates where the year part is non-zero
but the the month or day part is 0 (for example, '0000-00-00' is
legal but '2010-00-01' and '2010-01-00' are not). If used with the
IGNORE option, MySQL inserts a '0000-00-00' date for any such date. 
When not in strict mode, the date is accepted but a warning is
generated.  

Also updated the CREATE TABLE description.