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

Wrong result for SELECT..WHERE time_column=TIMESTAMP'2015-08-30 00:00:00' AND time_column='00:00:00'

    Details

      Description

      In this script:

      SET timestamp=UNIX_TIMESTAMP('2015-08-30 10:20:30');
      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a TIME);
      INSERT INTO t1 VALUES ('00:00:00');
      SELECT * FROM t1 WHERE a=TIMESTAMP'2015-08-30 00:00:00';
      SELECT * FROM t1 WHERE a='00:00:00';
      SELECT * FROM t1 WHERE a=TIMESTAMP'2015-08-30 00:00:00' AND a='00:00:00';
      SELECT * FROM t1 WHERE a>=TIMESTAMP'2015-08-30 00:00:00' AND a='00:00:00';

      the first and the second SELECT queries correctly return one row.
      The third and the fourth SELECT query erroneously return empty set.

      If I rewrite the third query to use TIME literal:

      SELECT * FROM t1 WHERE a=TIMESTAMP'2015-08-30 00:00:00' AND a=TIME'00:00:00';

      it correctly returns one row.

      Another example:

      SET timestamp=UNIX_TIMESTAMP('2015-08-30 10:20:30');
      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a DATE);
      INSERT INTO t1 VALUES ('2015-08-30'),('2015-08-31');
      SELECT * FROM t1 WHERE a=TIME'00:00:00';
      SELECT * FROM t1 WHERE LENGTH(a)=10;
      SELECT * FROM t1 WHERE LENGTH(a)=10 AND a=TIME'00:00:00';

      correctly returns one row in the first SELECT and both rows in the second SELECT, but erroneously returns empty set in the third SELECT.

      The reason is that Item_equal remembers a wrong equal constant. In case if time_column=<const> equality it should remember a constant of TIME type.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                bar Alexander Barkov
                Reporter:
                bar Alexander Barkov
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: