Datetime behavior in MariaDB vs MySQL
Hi,
I have a MariaDB db and MySQL db. My question is why do I see the following different datetime behavior between the two? One returns a record with a zero date, the other only returns a warning. Is this due to a setting I'm overlooking or is it simply due to an intrinsic behavior difference between the two?
Thanks
MariaDB Server version: 10.1.29-MariaDB MariaDB Server MySQL Server version: 5.6.27 MySQL Community Server (GPL)
------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------
I have the following table in both:
CREATE table TYPEMISC (a FLOAT, b DATETIME, c TIME, d VARCHAR(10)); insert into TYPEMISC values ( 111.222, '23Apr75', '05May99' , 'a');
------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------
The same query gives two different responses:
MySQL:
mysql> select * from TYPEMISC where b='31Jan1999'; Empty set, 2 warnings (0.00 sec)
mysql> show warning; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'warning' at line 1
MariaDB > select * from TYPEMISC where b='31Jan1999'; +---------+---------------------+----------+------+ | a | b | c | d | +---------+---------------------+----------+------+ | 111.222 | 0000-00-00 00:00:00 | 00:00:05 | a | +---------+---------------------+----------+------+ 1 row in set, 1 warning (0.00 sec)
MariaDB > show warning; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'warning' at line 1
------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------
They both have the same SQL Modes:
MariaDB> SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session; +-------------------------------------------------------------+-------------------------------------------------------------+ | global | session | +-------------------------------------------------------------+-------------------------------------------------------------+ | REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI | REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI | +-------------------------------------------------------------+-------------------------------------------------------------+ 1 row in set (0.00 sec)
mysql> SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session; +-------------------------------------------------------------+-------------------------------------------------------------+ | global | session | +-------------------------------------------------------------+-------------------------------------------------------------+ | REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI | REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI | +-------------------------------------------------------------+-------------------------------------------------------------+ 1 row in set (0.00 sec)
Answer
MariaDB considers all badly formatted strings as '0000-00-00 00:00:00' for comparison purposes. Current versions of MySQL are inconsistent, in that they handle constants one way, and non-constants another way.
Consider the following example from MySQL, with confusing results:
SET sql_mode=''; DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a FLOAT, b DATETIME, c TIME, d VARCHAR(10)); INSERT INTO t1 VALUES ( 111.222, '23Apr75', '05May99', 'a'); SELECT * FROM t1 WHERE b='31Jan1999'; SELECT * FROM t1 WHERE b=IF(RAND(),'31Jan1999','31Jan1999'); SELECT * FROM t1 WHERE b='31Jan1999'; Empty set, 2 warnings (0.00 sec) SELECT * FROM t1 WHERE b=IF(RAND(),'31Jan1999','31Jan1999'); +---------+---------------------+----------+------+ | a | b | c | d | +---------+---------------------+----------+------+ | 111.222 | 0000-00-00 00:00:00 | 00:00:05 | a | +---------+---------------------+----------+------+ 1 row in set, 1 warning (0.00 sec)