Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-16532

Equality checks between now() and time columns behaves unexpectedly

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Won't Fix
    • Affects Version/s: 5.5.56, 5.5, 5.5.60
    • Fix Version/s: N/A
    • Component/s: Data types
    • Labels:
    • Environment:
      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

            Activity

              People

              • Assignee:
                bar Alexander Barkov
                Reporter:
                voodoodrul Jesse Skrivseth
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: