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
MDEV-6102Comparison between TIME and DATETIME does not use CURRENT_DATE
It seems that MariaDB 5.5 handles NOW() and equality checks against time columns in an unexpected way. Notice:
{code:sql}
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();
{code}
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:
{code:java}
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());
{code}
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.
It seems that MariaDB 5.5 handles NOW() and equality checks against time columns in an unexpected way. Notice:
{code:sql}
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();
{code}
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:
{code:sql}
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());
{code}
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.
worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019
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();
now start stop
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());
now start stop
2018-06-20 12:49:12 06:00:00 19:00:00
Alice Sherepa
added a comment - 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..16019
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();
now start stop
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());
now start stop
2018-06-20 12:49:12 06:00:00 19:00:00
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_TIMEAND stop >= CURRENT_TIME;
The latter is a preferred way.
Alexander Barkov
added a comment - - edited 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.
Bug
Major
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