[MDEV-15262] Wrong results for SELECT..WHERE non_indexed_datetime_column=indexed_time_column Created: 2018-02-09  Updated: 2018-02-09  Resolved: 2018-02-09

Status: Closed
Project: MariaDB Server
Component/s: Optimizer, Temporal Types
Affects Version/s: 10.0, 10.1, 10.2, 10.3
Fix Version/s: 10.0.35

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Blocks
blocks MDEV-8894 Inserting fractional seconds into My... Closed
Relates
relates to MDEV-6065 MySQL Bug #13623473 MISSING ROWS ON ... Closed

 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    |
 
+---------------------+--------------+


Generated at Thu Feb 08 08:19:56 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.