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

Equality checks between now() and time columns behaves unexpectedly

    XMLWordPrintable

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

            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.