Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.0(EOL), 10.1(EOL)
-
None
Description
Hi,
While comparing the date behaviour between MySQL 5.5 and MariaDB 10.x, I found some strange behaviours with date :
SET SQL_MODE=''; |
SELECT CAST('' AS DATE); |
+------------------+ |
| CAST('' AS DATE) | |
+------------------+ |
| NULL | |
+------------------+ |
|
|
SELECT CAST('' AS DATE)='0000-00-00'; |
+-------------------------------+ |
| CAST('' AS DATE)='0000-00-00' | |
+-------------------------------+ |
| 1 |
|
+-------------------------------+ |
NULL = '0000-00-00' ??
SET SQL_MODE='NO_ZERO_DATE'; |
Query OK, 0 rows affected (0.00 sec) |
|
|
SELECT CAST('' AS DATE)='0000-00-00'; |
+-------------------------------+ |
| CAST('' AS DATE)='0000-00-00' | |
+-------------------------------+ |
| NULL | |
+-------------------------------+ |
1 row in set, 2 warnings (0.01 sec) |
Why SQL_MODE is impacting the result here ?
SELECT NOW() - INTERVAL '736598' DAY; |
+-------------------------------+ |
| NOW() - INTERVAL '736598' DAY | |
+-------------------------------+ |
| NULL | |
+-------------------------------+ |
1 row in set, 1 warning (0.00 sec) |
|
|
SHOW WARNINGS;
|
+---------+------+--------------------------------------------+ |
| Level | Code | Message | |
+---------+------+--------------------------------------------+ |
| Warning | 1441 | Datetime function: datetime field overflow | |
+---------+------+--------------------------------------------+ |
1 row in set (0.00 sec) |
=> Ok, overflow
SELECT NOW() - INTERVAL '3212121' DAY; |
+--------------------------------+ |
| NOW() - INTERVAL '3212121' DAY | |
+--------------------------------+ |
| 6777-10-01 04:01:55 |
|
+--------------------------------+ |
1 row in set (0.00 sec) |
=> no overflow ??
DROP TABLE IF EXISTS test_datetime; |
CREATE TABLE test_datetime (`id` int(10) unsigned NOT NULL AUTO_INCREMENT, `last_open_date` date NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB; |
INSERT INTO test_datetime (last_open_date) VALUES ('0000-00-00'); |
SELECT COUNT(*) FROM test_datetime WHERE last_open_date='121'; |
+----------+ |
| COUNT(*) | |
+----------+ |
| 1 |
|
+----------+ |
1 row in set, 1 warning (0.00 sec) |
MariaDB [test]> SHOW WARNINGS;
|
+---------+------+---------------------------------+ |
| Level | Code | Message | |
+---------+------+---------------------------------+ |
| Warning | 1292 | Incorrect datetime value: '121' | |
+---------+------+---------------------------------+ |
1 row in set (0.00 sec) |
MariaDB [test]> SELECT COUNT(*) FROM test_datetime WHERE last_open_date=CAST('121' AS date); |
+----------+ |
| COUNT(*) | |
+----------+ |
| 0 |
|
+----------+ |
1 row in set, 1 warning (0.00 sec) |
|
|
MariaDB [test]> SELECT CAST('121' AS date); |
+---------------------+ |
| CAST('121' AS date) | |
+---------------------+ |
| NULL | |
+---------------------+ |
1 row in set, 1 warning (0.00 sec) |
|
|
MariaDB [test]> SELECT * FROM test_datetime WHERE last_open_date IS NULL; |
+----+----------------+ |
| id | last_open_date |
|
+----+----------------+ |
| 1 | 0000-00-00 |
|
+----+----------------+ |
1 row in set (0.00 sec) |
Note because of this issue, we had to switch back to percona 5.6 which doesn't have this behaviour.
Thanks and regards,
Jocelyn Fournier