[MDEV-11890] Effect of NO_ZERO_DATE and NO_ZERO_IN_DATE is reversed - Jira

Details

Type: Bug
Status: Stalled (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.1(EOL), 10.2(EOL)
Fix Version/s: 10.2(EOL)
Component/s: Temporal Types
Labels:
None

Description

The data and query example were taken from date_formats.test.

I'm not sure SELECTs are supposed to be affected by NO_ZERO... modes at all, at least the manual doesn't say anything about it, it only mentions INSERTs.

NO_ZERO_IN_DATE

The NO_ZERO_IN_DATE mode affects whether the server permits dates in which the year part is nonzero but the month or day part is 0. (This mode affects dates such as '2010-00-01' or '2010-01-00', but not '0000-00-00'. To control whether the server permits '0000-00-00', use the NO_ZERO_DATE mode.)

If this mode is enabled, dates with zero parts are inserted as '0000-00-00' and produce a warning.

If NO_ZERO_IN_DATE and strict mode are enabled, dates with zero parts are not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, dates with zero parts are inserted as '0000-00-00' and produce a warning.

NO_ZERO_DATE

The NO_ZERO_DATE mode affects whether the server permits '0000-00-00' as a valid date.

If this mode is enabled, '0000-00-00' is permitted and inserts produce a warning.

If NO_ZERO_DATE mode and strict mode are enabled, '0000-00-00' is not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, '0000-00-00' is permitted and inserts produce a warning.

However, assuming that SELECTs are also supposed to obey the modes, they seem to do it in an exactly opposite fashion:

MariaDB [test]> set sql_mode='';

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> create table t1 (date char(30), format char(30) not null);

Query OK, 0 rows affected (0.28 sec)

MariaDB [test]> insert into t1 values ('10:20:10', '%H:%i:%s');

Query OK, 1 row affected (0.09 sec)

Without any special modes, the query returns zero date:

MariaDB [test]> set sql_mode='';

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select date,format,str_to_date(date, format) as str_to_date from t1;

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

| date     | format   | str_to_date                |

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

| 10:20:10 | %H:%i:%s | 0000-00-00 10:20:10.000000 |

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

1 row in set (0.01 sec)

MariaDB [test]> set sql_mode='NO_ZERO_DATE';

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select date,format,str_to_date(date, format) as str_to_date from t1;

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

| date     | format   | str_to_date                |

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

| 10:20:10 | %H:%i:%s | 0000-00-00 10:20:10.000000 |

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

1 row in set (0.00 sec)

MariaDB [test]> set sql_mode='NO_ZERO_IN_DATE';

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select date,format,str_to_date(date, format) as str_to_date from t1;

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

| date     | format   | str_to_date |

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

| 10:20:10 | %H:%i:%s | NULL        |

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

1 row in set, 1 warning (0.00 sec)

MariaDB [test]> show warnings;

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

| Level   | Code | Message                                                       |

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

| Warning | 1411 | Incorrect datetime value: '10:20:10' for function str_to_date |

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

1 row in set (0.00 sec)

It started happening in 10.1. MariaDB 10.0 has the reverse behavior, and so does MySQL 5.7:

MySQL [test]> select @@version;

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

| @@version    |

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

| 5.7.17-debug |

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

1 row in set (0.01 sec)

MySQL [test]> set sql_mode='NO_ZERO_DATE';

Query OK, 0 rows affected, 2 warnings (0.00 sec)

MySQL [test]> select date,format,str_to_date(date, format) as str_to_date from t1;

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

| date     | format   | str_to_date |

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

| 10:20:10 | %H:%i:%s | NULL        |

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

1 row in set, 1 warning (0.00 sec)

MySQL [test]> show warnings;

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

| Level   | Code | Message                                                       |

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

| Warning | 1411 | Incorrect datetime value: '10:20:10' for function str_to_date |

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

1 row in set (0.00 sec)

MySQL [test]> set sql_mode='NO_ZERO_IN_DATE';

Query OK, 0 rows affected, 1 warning (0.00 sec)

MySQL [test]> select date,format,str_to_date(date, format) as str_to_date from t1;

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

| date     | format   | str_to_date                |

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

| 10:20:10 | %H:%i:%s | 0000-00-00 10:20:10.000000 |

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

1 row in set (0.00 sec)

Attachments

Issue Links

relates to

MDEV-7635 update defaults and simplify mysqld config parameters

Closed

Activity

Ascending order - Click to sort in descending order

View 6 older comments

Alexander Barkov added a comment - 2019-04-18 11:54 - edited

MySQL manual is wrong. There is nothing special with year. In reality MySQL-5.7 works with DATE as follows:

SET sql_mode=NO_ZERO_IN_DATE;

SELECT DATE'0000-00-00'; -- This returns a result  (it's a zero date, not 'zero in date')

SELECT DATE'0000-00-01'; -- This returns an error

SELECT DATE'0000-01-00'; -- This returns an error

SELECT DATE'0001-00-00'; -- This returns an error

SELECT DATE'0001-00-01'; -- This returns an error

SELECT DATE'0001-01-00'; -- This returns an error

So:

DATE'0000-00-00' is special - it's a zero date (all parts are zeros)
All combinations of zero and non-zero parts are 'zero in date'

MariaDB works with DATEs exactly the same way.

I've just checked how MariaDB-10.4.5 vs MySQL-5.7.25 work with DATETIME:

NO_ZERO_IN_DATE+CAST work similar in MariaDB and MySQL

The following returns NULL, because it's not a zero date, but there are zeros in YYYY-MM-DD

SET sql_mode=NO_ZERO_IN_DATE;
SELECT CAST('0000-00-00 23:59:59.5555556' AS DATETIME);

NO_ZERO_DATE+INSERT work similar in MariaDB and MySQL

SET sql_mode='STRICT_ALL_TABLES,NO_ZERO_DATE';
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a DATETIME);
INSERT INTO t1 VALUES ('0000-00-00 00:00:00'); – error
INSERT INTO t1 VALUES ('0000-00-00 00:00:01'); – success
INSERT INTO t1 VALUES ('0000-00-00 00:01:00'); – success
INSERT INTO t1 VALUES ('0000-00-00 01:00:00'); – success
INSERT INTO t1 VALUES ('0000-00-01 00:00:00'); – success
INSERT INTO t1 VALUES ('0000-01-00 00:00:00'); – success
INSERT INTO t1 VALUES ('0001-00-00 00:00:00'); – success

So the above disallows only '0000-00-00 00:00:00', which is a 'zero date'.
It does not disallow other values, because they are 'zero in date'.

NO_ZERO_IN_DATE+INSERT works similar in MariaDB and MySQL

SET sql_mode='STRICT_ALL_TABLES,NO_ZERO_IN_DATE';
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a DATETIME);
INSERT INTO t1 VALUES ('0000-00-00 00:00:00'); – success
INSERT INTO t1 VALUES ('0000-00-00 00:00:01'); – error
INSERT INTO t1 VALUES ('0000-00-00 00:01:00'); – error
INSERT INTO t1 VALUES ('0000-00-00 01:00:00'); – error
INSERT INTO t1 VALUES ('0000-00-01 00:00:00'); – error
INSERT INTO t1 VALUES ('0000-01-00 00:00:00'); – error
INSERT INTO t1 VALUES ('0001-00-00 00:00:00'); – error

The above script allows '0000-00-00 00:00:00', because it is 'zero date' (it is not 'zero in date').
All other values are not 'zero dates', but they have non-zero parts in YYYY-MM-DD, so they are 'zero in date', hence they are rejected.

So MySQL:

has different rules for fields and for functions
is wrong in the manual in defining what NO_ZERO_DATE and NO_ZERO_IN_DATE mean

In MariaDB we made functions use the same rules for fields and for functions, and this was intentional.

Alexander Barkov added a comment - 2019-04-18 11:54 - edited MySQL manual is wrong. There is nothing special with year. In reality MySQL-5.7 works with DATE as follows: SET sql_mode=NO_ZERO_IN_DATE; SELECT DATE '0000-00-00' ; -- This returns a result (it's a zero date, not 'zero in date') SELECT DATE '0000-00-01 '; -- This returns an error SELECT DATE' 0000-01-00 '; -- This returns an error SELECT DATE' 0001-00-00 '; -- This returns an error SELECT DATE' 0001-00-01 '; -- This returns an error SELECT DATE' 0001-01-00'; -- This returns an error So: DATE'0000-00-00' is special - it's a zero date (all parts are zeros) All combinations of zero and non-zero parts are 'zero in date' MariaDB works with DATEs exactly the same way. I've just checked how MariaDB-10.4.5 vs MySQL-5.7.25 work with DATETIME: NO_ZERO_IN_DATE+CAST work similar in MariaDB and MySQL The following returns NULL, because it's not a zero date, but there are zeros in YYYY-MM-DD SET sql_mode=NO_ZERO_IN_DATE; SELECT CAST('0000-00-00 23:59:59.5555556' AS DATETIME); NO_ZERO_DATE+INSERT work similar in MariaDB and MySQL SET sql_mode='STRICT_ALL_TABLES,NO_ZERO_DATE'; DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a DATETIME); INSERT INTO t1 VALUES ('0000-00-00 00:00:00'); – error INSERT INTO t1 VALUES ('0000-00-00 00:00:01'); – success INSERT INTO t1 VALUES ('0000-00-00 00:01:00'); – success INSERT INTO t1 VALUES ('0000-00-00 01:00:00'); – success INSERT INTO t1 VALUES ('0000-00-01 00:00:00'); – success INSERT INTO t1 VALUES ('0000-01-00 00:00:00'); – success INSERT INTO t1 VALUES ('0001-00-00 00:00:00'); – success So the above disallows only '0000-00-00 00:00:00', which is a 'zero date'. It does not disallow other values, because they are 'zero in date'. NO_ZERO_IN_DATE+INSERT works similar in MariaDB and MySQL SET sql_mode='STRICT_ALL_TABLES,NO_ZERO_IN_DATE'; DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a DATETIME); INSERT INTO t1 VALUES ('0000-00-00 00:00:00'); – success INSERT INTO t1 VALUES ('0000-00-00 00:00:01'); – error INSERT INTO t1 VALUES ('0000-00-00 00:01:00'); – error INSERT INTO t1 VALUES ('0000-00-00 01:00:00'); – error INSERT INTO t1 VALUES ('0000-00-01 00:00:00'); – error INSERT INTO t1 VALUES ('0000-01-00 00:00:00'); – error INSERT INTO t1 VALUES ('0001-00-00 00:00:00'); – error The above script allows '0000-00-00 00:00:00', because it is 'zero date' (it is not 'zero in date'). All other values are not 'zero dates', but they have non-zero parts in YYYY-MM-DD, so they are 'zero in date', hence they are rejected. So MySQL: has different rules for fields and for functions is wrong in the manual in defining what NO_ZERO_DATE and NO_ZERO_IN_DATE mean In MariaDB we made functions use the same rules for fields and for functions, and this was intentional.

Alexander Barkov added a comment - 2019-04-23 08:34

Sorry, I overlooked that '0000-01-01' is not actually controlled by 'NO_ZERO_IN_DATE'.

Just discussed with Serg. We agreed that this behavior is confusing.

In 10.5 we'll fix NO_ZERO_IN_DATE to disallow '0000-01-01'. See '~~MDEV-19311~~.

Alexander Barkov added a comment - 2019-04-23 08:34 Sorry, I overlooked that '0000-01-01' is not actually controlled by 'NO_ZERO_IN_DATE'. Just discussed with Serg. We agreed that this behavior is confusing. In 10.5 we'll fix NO_ZERO_IN_DATE to disallow '0000-01-01'. See ' MDEV-19311 .

Oleksandr Byelkin added a comment - 2020-11-03 12:58

KennethDyer I inherited this task from Bar, could you say me what I have to review?

Oleksandr Byelkin added a comment - 2020-11-03 12:58 KennethDyer I inherited this task from Bar, could you say me what I have to review?

Daniel Black added a comment - 2020-11-19 09:22

https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=4802df2ee8bdd42f1b5107624a5189ea

With NO_ZERO_IN_DATE, there a difference between:

str_to_date('10:20:10', '%H:%i:%s'); and
str_to_date(date, format) as str_to_date from t1;

If as bar says, functions and fields are the same. Should these be the same?

Daniel Black added a comment - 2020-11-19 09:22 https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=4802df2ee8bdd42f1b5107624a5189ea With NO_ZERO_IN_DATE, there a difference between: str_to_date('10:20:10', '%H:%i:%s'); and str_to_date(date, format) as str_to_date from t1; If as bar says, functions and fields are the same. Should these be the same?

Oleksandr Byelkin added a comment - 2020-11-19 13:27

OK for me it will require long investigation (I have no idea what is going on with dates)

Oleksandr Byelkin added a comment - 2020-11-19 13:27 OK for me it will require long investigation (I have no idea what is going on with dates)

People

Assignee:: Daniel Bartholomew

Reporter:: Elena Stepanova

Votes:: 0 Vote for this issue

Watchers:: 7 Start watching this issue

Dates

Created:: 2017-01-23 22:18

Updated:: 2024-05-07 13:34

Git Integration

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

MariaDB Server