MySQL Bugs: #59526: ZERO-date can be inserted in 'no_zero_date' sql

Bug #59526	ZERO-date can be inserted in 'no_zero_date' sql_mode
Submitted:	15 Jan 2011 14:08	Modified:	31 Jan 2011 17:35
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.0, 5.1, 5.6.2	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
This maybe be a more general problem affecting more sql_mode (but just cannot think of any) that a specified DEFAULT for a column has precedence above sql_mode. 

But the case shows that ZERO-date will insert in 'no-zero-date' sql_mode to a DATE-column with default ZERO.

How to repeat:
SET SQL_MODE = '';

DROP TABLE IF EXISTS datemode;
CREATE TABLE `datemode` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `d` DATE DEFAULT '0000-00-00',
  `s` VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
INSERT INTO datemode (s) VALUES ('a');
SET SQL_MODE = 'no_zero_date';
INSERT INTO datemode (s) VALUES ('b');
SELECT * FROM datemode;
/*
    id  d           s     
------  ----------  ------
     1  0000-00-00  a     
     2  0000-00-00  b     
*/

SET SQL_MODE = '';

-- case 2
DROP TABLE IF EXISTS datemode;
SET SQL_MODE = 'no_zero_date';
CREATE TABLE `datemode` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `d` DATE DEFAULT '0000-00-00',
  `s` VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
-- Error Code : 1067
-- Invalid default value for 'd'

Suggested fix:
In case 1) I think the INSERT should return an error as it violates the sql_mode.  

Alternatively if you want to keep the implementation that sql_mode setting is 'weaker' than DEFAULT setting for a column it should be documented.

Case 2) is fine!

Let me add that if I "SET @@global.sql_mode = 'no_zero_date';" on top it does not change anything (what it should not either).

I marked this as a '5.x' bug.  I tested with 5.1.54 (but assume all 5.x are affected).

Thank you for the report.

According to http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html:

In strict mode, do not permit '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. 

Have you seen warning in first case? What SHOW WARNINGS outputs?

Well what exactly is a 'strict mode'? is 'no_zero_date' a 'strict mode' or is it only 'strict_all_tables' and 'strict_trans_table' that are? Actually the frequent references to 'strict mode' is rather unprecise as there is no exact definition of a 'strict mode'. If a strict mode means a mode that *restricts* what statements are accepted, than most sql_modes are 'strict' (ie. 'stricter' than '' (empty) mode).

There is no warning:

DROP TABLE IF EXISTS datemode;
CREATE TABLE `datemode` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `d` DATE DEFAULT '0000-00-00',
  `s` VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
INSERT INTO datemode (s) VALUES ('a');
SET SQL_MODE = 'no_zero_date';
INSERT INTO datemode (s) VALUES ('b');
SHOW WARNINGS; -- empty set

I believe that this is an architectural issue with the sql_mode implementation that it does not check what happens internally when a DEFAULT is inserted to a table. 

I don't claim that this is serious at all and not even important, but as I encountered it I thought I would report it.

Thank you for the feedback.

Verified as described: behavior is contradict with user manual.

Ok .. manual defines what a 'strict mode' is:

When this manual refers to “strict mode,” it means a mode where at least one of STRICT_TRANS_TABLES or STRICT_ALL_TABLES is enabled.

(http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html).

So 'no_zero_date, 'error_on_division_by_zero' etc. are 'non-strict' modes as per definition (but I don't agree the definition)

Test case with 'strict' mode is probably more clear. last INSERT should not insert but it does:

SET SQL_MODE = '';
USE test;
DROP TABLE IF EXISTS datemode;
CREATE TABLE `datemode` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `d` DATE DEFAULT '0000-00-00',
  `s` VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO datemode (s) VALUES ('a'); -- 1 row(s) affected
SET SQL_MODE = 'strict_all_tables,no_zero_date';
INSERT INTO datemode (s) VALUES ('b'); -- 1 row(s) affected

triage: setting to CHECKED (I4 - legacy issue - W4 - don't set to Zero default if you don't want it

I doubt that we'd want to change the column's default
based on the sql_mode setting, so I'd suggest merely
changing the description in the MySQL Reference Manual.
From:
In strict mode, do not permit '0000-00-00'  as a valid date.
To:
In strict mode, do not allow statements
which attempt to set datetime columns to
'0000-00-00'.

well ..

1)
Docs should be more *verbose* then than what PG suggests. It should tell explicitly that sql_modes do not prevent DEFAULT settings to have effect for a column if the column is not specified in a statement. It is without value that the manual is *correct* if it is not *understandable for users what it means for users*

2)
I guess it affects 'no_zero_in_dates' mode as well? More modes affected where the restriction imposed by a sql_mode could conflict with a DEFAULT setting? this was actually where I'd expect PG to *contribute* (and not try to *kill* discussion).

3)
This is a complete misunderstanding: "change the column's default based on the sql_mode setting". Nobody asked for that and of course it should not and cannot either. It is just plain impossible as it is SESSION sql_mode we are talking about and multiple clients may use different SESSION modes. But "sql_mode checks" could be performed before a DEFAULT would insert - and if there is a conflict a warning or an error could occur (depending on whether a 'strict mode' is set or not).

4)
I wonder: who are *we*, that don't want .. . On behalf of whom do you speak here? Is there a 'product committee' or similar that takes such decisions? Does developers? Does management (in edge cases)?

I do not claim it is a big issue, but I think it should be recognized as an architectural flaw that sql_mode checks are not always done before inserting. A note in docs does not change that. But as long as this is the behavior docs could be more explict of course.

There's some value in sending out notification (a warning) to the user creating a table like this and certainly the INSERT shouldn't work on such a table in some sql modes.
It is a valid error report, despite the fact that we all seem to agree that this error doesn't affect many.

Okay, I'll be verbose.

I'll start by withdrawing my comment of 2011-01-21.
The only part I'll be defensive about is the sentence beginning with
"I doubt that we'd ...". When I say "I" I am speaking for myself,
about my own doubts, and there is no need to think there is a broader meaning.

Now, here are three hypothetical examples, with no suggestion that
they were either advocated or criticized in the bug report.

Example #1:
SET @@sql_mode='';
CREATE TABLE t1 (s1 DATE, s2 INT);
INSERT INTO t1 VALUES ('0000-00-00',1);
SET @@sql_mode='no_zero_in_date';
UPDATE t1 SET s2 = 2;
No error or warning -- and the updated row has '0000-00-00' date.

Example #2:
CREATE TABLE t1 (s1 DATE, s2 DATE);
CREATE TRIGGER t1_ai BEFORE INSERT ON t1 FOR EACH ROW SET new.s2 = '0000-00-00';
SET @@sql_mode='no_zero_in_date';
INSERT INTO t1 VALUES (CURRENT_DATE, CURRENT_DATE);
No error or warning -- and the inserted row has '0000-00-00' date.

Example #3:
CREATE TABLE t1 (s1 DATE);
SET @@sql_mode='no_zero_date';
CREATE PROCEDURE p1 () INSERT INTO t1 VALUES ('0000-00-00');
SET @@sql_mode='';
CALL p1(); /* This generates a warning */
SET @@sql_mode='';
CREATE PROCEDURE p2 () INSERT INTO t1 VALUES ('0000-00-00');
SET @@sql_mode='no_zero_date';
CALL p2();
No error or warning -- and the inserted row has '0000-00-00' date.

As the examples show, sql_mode isn't controlling whether an
updated/inserted row will end up with a column containing '0000-00-00'.
What then the does the MySQL 5.5 Reference Manual,
Section "5.1.7. Server SQL Modes",
http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html,
mean by
"NO_ZERO_DATE ... In strict mode, do not permit '0000-00-00'  as a valid date."
"NO_ZERO_IN_DATE ... When not in strict mode, the date is accepted but a warning is generated."
etc.? For answer let's look at the beginning of the section.
It says:
"The MySQL server [applies modes] differently for different clients.
... Modes define what SQL syntax MySQL should support
and what kind of data validation checks it should perform".

An "SQL syntax" check would apply to client syntax (since
the manual explicitly mentions clients), so it would not
apply to operations that happen without a client statement.
That directive can be indirect (as in the example where "p1"
is called and causes a warning because the mode was in effect
during creation of the procedure), but it's expected to be explicit.
Something that's implied, but not in the statement, isn't something
we can expect to be affected by rules for the statement's syntax.

That leaves the possibility that the phrases "do not permit"
or "is accepted but a warning is generated" refer to results for
"data validation" rather than syntax checking. Data validation
can involve looking whether a row contains an illegitimate value
(as would happen for a standard CHECK clause), and in that case
there should be an error or warning for all three of the examples.
And for that possibility, I'll have to say: yes, it could have
meant that. But the fact -- as we can see from the results -- is
that it didn't mean that. In fact, for most sql_mode values, it
seems impossible that anything other than syntax checking could
be meant. Consider PIPES_AS_CONCAT or IGNORE_SPACE or
NO_BACKSLASH_ESCAPES and try to think how they'd be applicable
for data validation.

Therefore I believe that MySQL's behaviour is intentional and
does not contradict the documentation, but that the section
"5.1.7. Server SQL Modes" should not have said "what SQL syntax
MySQL should support and what kind of data validation checks"
without saying that certain/most modes are for syntax not data.
In that case, though, this is a documentation issue.

Well .. not a big deal under any circumstance. 

But as a client user I'd expect the mode to protect me (or warn me) in case of a human mistake or a client bug if I really do not want this value inserted. 

Thanks for replying.