MySQL Bugs: #34280: CREATE TABLE fails if in NO_ZERO

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

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).

Thank you for the report.

Verified as described.

note that zero TIME is perfectly valid in any SQL mode.

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

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.