[MDEV-16532] Equality checks between now() and time columns behaves unexpectedly Created: 2018-06-20  Updated: 2018-06-20  Resolved: 2018-06-20

Status: Closed
Project: MariaDB Server
Component/s: Data types
Affects Version/s: 5.5, 5.5.56, 5.5.60
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Jesse Skrivseth Assignee: Alexander Barkov
Resolution: Won't Fix Votes: 0
Labels: Compatibility
Environment:

CentOS7, Windows 10

Issue Links:
Relates
relates to MDEV-6102 Comparison between TIME and DATETIME ... Closed

 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.



 Comments   
Comment by Alice Sherepa [ 2018-06-20 ]

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

Comment by Alexander Barkov [ 2018-06-20 ]

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.

Comment by Alexander Barkov [ 2018-06-20 ]

Behavior was changed in 10.0.12 under terms of MDEV-6102.

Generated at Thu Feb 08 08:29:36 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.