MySQL Bugs: #34277: NO_ZERO_IN_DATE does not work properly

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

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.

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.

Thank you for the bug report. Verified as described.

documentation bug

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.