[MDEV-11890] Effect of NO_ZERO_DATE and NO_ZERO_IN_DATE is reversed Created: 2017-01-23  Updated: 2023-12-15

Status: Stalled
Project: MariaDB Server
Component/s: Temporal Types
Affects Version/s: 10.1, 10.2
Fix Version/s: 10.2

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Joe Cotellese
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-7635 update defaults and simplify mysqld c... Closed

 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)



 Comments   
Comment by Elena Stepanova [ 2017-01-24 ]

Setting to 10.2-ga in case these switches become a part of the new default sql_mode. If they don't, then there is no rush.

Comment by Elena Stepanova [ 2017-04-23 ]

Removed 10.2-ga label since the switches didn't become a part of the default sql_mode.

Comment by Alexander Barkov [ 2019-04-18 ]

elenst,

Zero date is a date, datetime, timestamp value with all fields equal to 0:

  • DATE'0000-00-00' is a zero date.
  • TIMESTAMP'0000-00-00 00:00:00' is a zero date.

However:

  • '0000-00-00 10:20:10.000000' is not a zero date, as its time part is non-zero. But it is a 'zero in date', because it has zeros in the 'YYYY-MM-DD' part.

There is a difference between them, hence they have two separate sql_mode flags:

  • Zero date '0000-00-00 00:00:00.000000' is a special value: it's allowed in TIMESTAMP. These values are controlled by NO_ZERO_DATE.
  • Values like '0000-00-00 10:20:10.000000' with zero DATE and non-zero TIME part are not allowed in TIMESTAMP. These values are controlled by NO_ZERO_IN_DATE.

So in the above scripts it looks correct to:

  • return '0000-00-00 10:20:10.000000' with NO_ZERO_DATE
  • return NULL with a warning with NO_ZERO_IN_DATE
Comment by Alexander Barkov [ 2019-04-18 ]

Hi KennethDyer,

can you please make sure that 'zero date' and 'zero in date' are properly documented in the manual?

Thanks.

Comment by Alexander Barkov [ 2019-04-18 ]

More examples proving this behavior:

MariaDB [mysql]> SET sql_mode=NO_ZERO_DATE; SELECT TIMESTAMP('0000-00-00 00:00:01');
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
+----------------------------------+
 
| TIMESTAMP('0000-00-00 00:00:01') |
 
+----------------------------------+
 
| 0000-00-00 00:00:01              |
 
+----------------------------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [mysql]> 
MariaDB [mysql]> 
MariaDB [mysql]> SET sql_mode=NO_ZERO_IN_DATE; SELECT TIMESTAMP('0000-00-00 00:00:01');
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
+----------------------------------+
 
| TIMESTAMP('0000-00-00 00:00:01') |
 
+----------------------------------+
 
| NULL                             |
 
+----------------------------------+
 
1 row in set, 1 warning (0.000 sec)

Comment by Elena Stepanova [ 2019-04-18 ]

bar,

0000-00-00 10:20:10.000000' is not a zero date, as its time part is non-zero. But it is a 'zero in date', because it has zeros in the 'YYYY-MM-DD' part.

It might be so in MariaDB now, and I am not going to argue which one is correct(er), but at least MySQL thinks differently about it. The quote in the description was from MySQL manual, and it's still basically the same in 8.0.
It is very specific about what "zero in date" means, and "0000-00-00 00:00:01" is not it, neither by the manual nor by implementation:

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. 

mysql> SET sql_mode=NO_ZERO_IN_DATE; SELECT TIMESTAMP('0000-00-00 00:00:01');
 
Query OK, 0 rows affected, 1 warning (0.01 sec)
 
 
 
+----------------------------------+
 
| TIMESTAMP('0000-00-00 00:00:01') |
 
+----------------------------------+
 
| 0000-00-00 00:00:01              |
 
+----------------------------------+
 
1 row in set (0.00 sec)

So, if it's intentional, KennethDyer, please have it documented not only as expected behavior, but as expected and intentional difference with MySQL (a.k.a. incompatibility).

Comment by Alexander Barkov [ 2019-04-18 ]

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.

Comment by Alexander Barkov [ 2019-04-23 ]

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.

Comment by Oleksandr Byelkin [ 2020-11-03 ]

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

Comment by Daniel Black [ 2020-11-19 ]

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?

Comment by Oleksandr Byelkin [ 2020-11-19 ]

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

Generated at Thu Feb 08 07:53:27 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.