[MDEV-7824] [Bug #68041] Zero date can be inserted in strict no-zero mode through a default value - Jira

XML

Word

Printable

I create a table with zero date:

SET sql_mode=DEFAULT;

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a DATE  DEFAULT '0000-00-00');

Now change sql_mode to traditional:

SET sql_mode=traditional;

INSERT INTO t1 VALUES ('0000-00-00');

It correctly rejects the value:

ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'a' at row 1

However, It's still possible to insert the bad value through the default:

INSERT INTO t1 VALUES ();

SELECT * FROM t1;

The INSERT statement works (even without warnings) and the SELECT statement returns:

+------------+

| a          |

+------------+

| 0000-00-00 |

+------------+

The expected behaviour would be to reject the INSERT that uses the default value '0000-00-00'.

Using DEFAULT also does not reject:

SET sql_mode=DEFAULT;

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a TIMESTAMP DEFAULT '0000-00-00 00:00:00');

SET sql_mode=traditional;

INSERT INTO t1 VALUES (DEFAULT);

It also does not reject bad values when using INSERT..SELECT:

SET sql_mode=DEFAULT;

DROP TABLE IF EXISTS t1,t2;

CREATE TABLE t1 (a DATE DEFAULT '0000-00-00');

CREATE TABLE t2 (a DATE DEFAULT '0000-00-00');

INSERT INTO t2 VALUES ('0000-00-00');

SET sql_mode=traditional;

INSERT INTO t1 (a) SELECT a FROM t2;

DROP TABLE t1, t2;

blocks

MDEV-3929 Add system variable explicit_defaults_for_timestamp for compatibility with MySQL

links to

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.