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

Equality checks between now() and time columns behaves unexpectedly

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Won't Fix
    • 5.5.56, 5.5.60, 5.5(EOL)
    • N/A
    • Data types
    • 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

            Transition Time In Source Status Execution Times
            Alexander Barkov made transition -
            Open Closed
            12h 13m 1
            Sergei Golubchik made transition -
            Closed Stalled
            10h 11m 1
            Sergei Golubchik made transition -
            Stalled Closed
            8s 1

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.