Maria DB / MySQL difference when comparing TimeStamp

Question

I have two different development environments: MySQL 5.6 and Maria DB 10.0

I use the WHERE query, for example time>="userInputBeginTime", to limit the minimum data date.

Unfortunately, on the front side there is incorrect date information (in my case, the expected input is a date string formatted as "yyyy-MM-dd", but the real input was "Invalid date", which is due to a javascript error)

And then I found the difference between MySQL and Maria DB

In MySQL:

SELECT NOW() > "Invalid Date" test;
+-----------+
|      test |
+-----------+
|         0 |
+-----------+

In Maria DB:

SELECT NOW() > "Invalid Date" test;
+-----------+
|      test |
+-----------+
|         1 |
+-----------+

This difference is directly related to the fact that MySQL did not return anything, and Maria DB returned everything.

I think they can use different methods to compare.

My workaround is using SQL like this

SELECT * FROM table_name 
WHERE 
  NOT ISNULL(CONVERT("userInputBeginTime",datetime)) 
  AND time >= "userInputBeginTime"

to force Maria DB to return nothing when invalid input was detected.

, ?

Answer 1

STR_TO_DATE . NULL, . JavaScript NULL:

SELECT *
FROM table_name
WHERE STR_TO_DATE(userInput, '%Y-%m-%d') IS NOT NULL AND
      <other conditions here>

STR_TO_DATE MariaDB
STR_TO_DATE MySQL