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

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-6102 Comparison between TIME and DATETIME does not use CURRENT_DATE

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            voodoodrul Jesse Skrivseth created issue -
            voodoodrul Jesse Skrivseth made changes -
            Field Original Value New Value
            Description 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.
            voodoodrul Jesse Skrivseth made changes -
            Summary Casting of now() against time columns behaves unexpectedly Equality checks between now() and time columns behaves unexpectedly
            elenst Elena Stepanova made changes -
            Affects Version/s 5.5 [ 15800 ]
            alice Alice Sherepa made changes -
            Fix Version/s 5.5 [ 15800 ]
            alice Alice Sherepa made changes -
            Assignee Alexander Barkov [ bar ]
            bar Alexander Barkov made changes -
            Fix Version/s N/A [ 14700 ]
            Fix Version/s 5.5 [ 15800 ]
            Resolution Not a Bug [ 6 ]
            Status Open [ 1 ] Closed [ 6 ]
            bar Alexander Barkov made changes -
            serg Sergei Golubchik made changes -
            Resolution Not a Bug [ 6 ]
            Status Closed [ 6 ] Stalled [ 10000 ]
            serg Sergei Golubchik made changes -
            Resolution Won't Fix [ 2 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 87939 ] MariaDB v4 [ 154542 ]

            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.