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

Wrong result for WHERE datetime_column > TIME('00:00:00')

Details

    Description

      In this script:

      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (a DATETIME);
      		 
      INSERT INTO t1 VALUES ('2000-09-12 00:00:00'), ('2007-04-25 05:08:49');
      		 
      SELECT * FROM t1 WHERE a>TIME'00:00:00';
      		 
      SELECT * FROM t1 WHERE a>TIME('00:00:00');

      the first SELECT query correctly returns empty set, and the second SELECT query erroneously returns two rows:

      +---------------------+
      		 
      | a                   |
      		 
      +---------------------+
      		 
      | 2000-09-12 00:00:00 |
      		 
      | 2007-04-25 05:08:49 |
      		 
      +--------------------

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          bar Alexander Barkov created issue -
          bar Alexander Barkov made changes -
          Field Original Value New Value
          Status Open [ 1 ] In Progress [ 3 ]
          bar Alexander Barkov made changes -
          Description In this script:
          {code}
          CREATE TABLE t1 (a DATETIME);
          INSERT INTO t1 VALUES ('2000-09-12 00:00:00'), ('2007-04-25 05:08:49');
          SELECT * FROM t1 WHERE a>TIME'00:00:00';
          SELECT * FROM t1 WHERE a>TIME('00:00:00');
          {code}
          the first SELECT query correctly returns empty set, and the second SELECT query erroneously returns two rows:
          {noformat}
          +---------------------+
          | a |
          +---------------------+
          | 2000-09-12 00:00:00 |
          | 2007-04-25 05:08:49 |
          +--------------------
          {noformat}
          		 In this script:
          {code}
          DROP TABLE IF EXISTS t1;
          CREATE TABLE t1 (a DATETIME);
          INSERT INTO t1 VALUES ('2000-09-12 00:00:00'), ('2007-04-25 05:08:49');
          SELECT * FROM t1 WHERE a>TIME'00:00:00';
          SELECT * FROM t1 WHERE a>TIME('00:00:00');
          {code}
          the first SELECT query correctly returns empty set, and the second SELECT query erroneously returns two rows:
          {noformat}
          +---------------------+
          | a |
          +---------------------+
          | 2000-09-12 00:00:00 |
          | 2007-04-25 05:08:49 |
          +--------------------
          {noformat}
          bar Alexander Barkov made changes -
          Labels upstream-not-affected
          bar Alexander Barkov added a comment -

          Not repeatable in MySQL-5.7.8

          bar Alexander Barkov added a comment - Not repeatable in MySQL-5.7.8
          bar Alexander Barkov made changes -
          Fix Version/s 10.1.8 [ 19605 ]
          Fix Version/s 10.1 [ 16100 ]
          Resolution Fixed [ 1 ]
          Status In Progress [ 3 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 71651 ] MariaDB v4 [ 149620 ]

          People

            bar Alexander Barkov
            bar Alexander Barkov
            Votes:
            0 Vote for this issue
            Watchers:
            1 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.