Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
5.5.56, 5.5.60, 5.5(EOL)
-
CentOS7, Windows 10
Description
It seems that MariaDB 5.5 handles NOW() and equality checks against time columns in an unexpected way. Notice:
select a.* from ( |
select now() as now, TIME('06:00:00') as start, TIME('19:00:00') as stop |
) a
|
WHERE start <= NOW() |
AND stop >= now(); |
In MySQL 5.0, 5.5, 5.6, 5.7, and MariaDB 10, this query returns a row when NOW() is a time of day between 06:00 and 19:00. However, on MariaDB 5.5, no row is returned. Changing the query to force casting like this allows the query to work:
select a.* from ( |
select now() as now, TIME('06:00:00') as start, TIME('19:00:00') as stop |
) a
|
WHERE start <= time(NOW()) |
AND stop >= time(now()); |
It is my guess that MariaDB is casting the NOW() to a string, then comparing against the string equivalent of 'start'. Or perhaps it is casting both fields to DATETIMEs and since the start/stop column omits the date component, MariaDB might be putting some incorrect date into the cast DATETIME.
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
Starting from 10.0.12, when comparing DATETIME and TIME, MariaDB extends TIME to DATETIME using CURRENT_DATE.
(In 5.5 it worked in a different way: extended TIME to DATETIME using date '0000-00-00')
So in 10.0 and higher:
WHERE datetime=time; |
is equivalent to:
WHERE datetime=TIMESTAMP(CURRENT_DATE, time); |
In 5.5, comparison between datetime and time was buggy.
In 5.5 you can either use time(now()) like you did, or better rewrite the query to use CURRENT_TIME instead of NOW(), to avoid implicit data type conversion:
WHERE start <= CURRENT_TIME AND stop >= CURRENT_TIME; |
The latter is a preferred way.
Thanks for the report! Reproduced as described on MariaDB 5.5, not presented on 10.0-10.3
MariaDB Version 5.5.61-MariaDB-debug==============================================================================TEST RESULT TIME (ms) or COMMENT--------------------------------------------------------------------------worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019select a.* from (select now() as now, TIME('06:00:00') as start, TIME('19:00:00') as stop) aWHERE start <= NOW()AND stop >= now();now start stopselect a.* from (select now() as now, TIME('06:00:00') as start, TIME('19:00:00') as stop) aWHERE start <= time(NOW())AND stop >= time(now());now start stop2018-06-20 12:49:12 06:00:00 19:00:00