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

Wrong results for SELECT..WHERE non_indexed_datetime_column=indexed_time_column

    Details

      Description

      I create and populate two tables as follows:

      SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
      DROP TABLE IF EXISTS t1, t2;
      CREATE TABLE t1 (col_time_key TIME, KEY(col_time_key));
      CREATE TABLE t2 (col_datetime_key DATETIME);
      INSERT INTO t1 VALUES ('-760:00:00'),('760:00:00');
      INSERT INTO t1 VALUES ('-770:00:00'),('770:00:00');
      INSERT INTO t2 SELECT * FROM t1;
      

      Now I run this query with IGNORE INDEX, it returns four rows as expected:

      SELECT * FROM t2 STRAIGHT_JOIN t1 IGNORE INDEX(col_time_key) WHERE col_time_key = col_datetime_key;
      

      +---------------------+--------------+
      | col_datetime_key    | col_time_key |
      +---------------------+--------------+
      | 2011-12-30 08:00:00 | -760:00:00   |
      | 2012-03-02 16:00:00 | 760:00:00    |
      | 2011-12-29 22:00:00 | -770:00:00   |
      | 2012-03-03 02:00:00 | 770:00:00    |
      +---------------------+--------------+
      

      Now I run the same query but with FORCE INDEX, it erroneously returns three rows:

      SELECT * FROM t2 STRAIGHT_JOIN t1 FORCE INDEX (col_time_key) WHERE col_time_key = col_datetime_key;
      

      +---------------------+--------------+
      | col_datetime_key    | col_time_key |
      +---------------------+--------------+
      | 2011-12-30 08:00:00 | -760:00:00   |
      | 2012-03-02 16:00:00 | 760:00:00    |
      | 2012-03-03 02:00:00 | 770:00:00    |
      +---------------------+--------------+
      

        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: