|
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
|