Details
-
Bug
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.1(EOL), 10.2(EOL)
-
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
-