[MDEV-15262] Wrong results for SELECT..WHERE non_indexed_datetime_column=indexed_time_column - Jira

XML

Word

Printable

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    |

+---------------------+--------------+

blocks

MDEV-8894 Inserting fractional seconds into MySQL 5.6 master breaks consistency on MariaDB 10 slave

relates to

MDEV-6065 MySQL Bug #13623473 MISSING ROWS ON SELECT AND JOIN WITH TIME/DATETIME COMPARE

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.