Bug #34280 CREATE TABLE fails if in NO_ZERO_DATE SQL mode
Submitted: 4 Feb 2008 14:20 Modified: 10 Apr 2012 18:30
Reporter: Alexander Nozdrin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.0, 5.1, 6.0 BK OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D3 (Medium)

[4 Feb 2008 14:20] Alexander Nozdrin
Description:
Similar to the second part of Bug#34277 (NO_ZERO_IN_DATE does not work properly).

According to the documentation:
(http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html)
<quote>
NO_ZERO_DATE

In strict mode, don't allow '0000-00-00' as a valid date. You can still insert zero dates with the IGNORE option. When not in strict mode, the date is accepted but a warning is generated.
</quote>

However, it's impossible to create a table with zero date as a default
column value.

How to repeat:
> set sql_mode = 'NO_ZERO_DATE';

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

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

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

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

It's possible however, to create table with TIME and YEAR columns:

> CREATE TABLE t4(c1 TIME DEFAULT 0);
  -- Ok
> CREATE TABLE t5(c1 YEAR NOT NULL DEFAULT 0);
  -- Ok

That's will be reported as another bug.

'NOT NULL' clause in the last statement is required due
to Bug#34274 (Invalid handling of 'DEFAULT 0' for YEAR data type).
[4 Feb 2008 19:13] Sveta Smirnova
Thank you for the report.

Verified as described.
[5 Feb 2008 13:03] Sergei Golubchik
note that zero TIME is perfectly valid in any SQL mode.
[13 Feb 2008 18:01] Sveta Smirnova
But is in not possibility to create table. INSERT IGNORE works as should:

set sql_mode = 'NO_ZERO_DATE';
DROP TABLE IF EXISTS t1, t2, t3, t4, t5;
Warnings:
Note    1051    Unknown table 't1'
Note    1051    Unknown table 't2'
Note    1051    Unknown table 't3'
Note    1051    Unknown table 't4'
Note    1051    Unknown table 't5'
CREATE TABLE t1(c1 TIMESTAMP DEFAULT 0);
ERROR 42000: Invalid default value for 'c1'
CREATE TABLE t2(c1 DATETIME DEFAULT 0);
ERROR 42000: Invalid default value for 'c1'
CREATE TABLE t3(c1 DATE DEFAULT 0);
ERROR 42000: Invalid default value for 'c1'
CREATE TABLE t4(c1 TIME DEFAULT 0);
CREATE TABLE t5(c1 YEAR NOT NULL DEFAULT 0);
set sql_mode = '';
CREATE TABLE t1(c1 TIMESTAMP DEFAULT 0);
CREATE TABLE t2(c1 DATETIME DEFAULT 0);
CREATE TABLE t3(c1 DATE DEFAULT 0);
set sql_mode = 'NO_ZERO_DATE';
insert ignore into t1 values('0000-00-00');
Warnings:
Warning 1265    Data truncated for column 'c1' at row 1
insert ignore into t2 values('0000-00-00');
Warnings:
Warning 1264    Out of range value for column 'c1' at row 1
insert ignore into t3 values('0000-00-00');
Warnings:
Warning 1265    Data truncated for column 'c1' at row 1
[10 Apr 2012 18:30] Paul Dubois
Noted in 5.6.6 changelog.

MySQL was overly agressive in enforcing the NO_ZERO_DATE and
NO_ZERO_IN_DATE SQL modes for default values in column definitions
for CREATE TABLE and ALTER TABLE statements. Previously, default
dates that were invalid with those SQL modes enabled produced an 
error, even when strict mode was not enabled. Now with NO_ZERO_DATE
or NO_ZERO_IN_DATE enabled, invalid default dates produce a warning
if strict SQL mode is not enabled, and an error if strict mode is
enabled.