Diverging results with TIME(3) and ranges depending on the execution plan in 5.1-micro
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
High
|
Sergei Golubchik |
Bug Description
Using BETWEEN in the WHERE clause returns different results depending on the query plan:
--source include/
CREATE TABLE t1 ( f1 time(3), f2 datetime, KEY (f1)) Engine=innodb;
INSERT IGNORE INTO t1 VALUES ('07:07:
('17:41:
('13:27:
SELECT * FROM t1 FORCE KEY ( f1 ) WHERE f2 AND f1 BETWEEN '02:18:49' AND '2006-11-16 09:33:34' ;
returns 1 row
SELECT * FROM t1 IGNORE KEY ( f1 ) WHERE f2 AND f1 BETWEEN '02:18:49' AND '2006-11-16 09:33:34' ;
returns 2 rows
in either case, no warnings are produced. Not repeatable with TIME(0).
Even though the above example with with TIME(3) and a slightly weird BETWEEN, the issue is also repeatable with DATETIME(3) and a range expression that involves only DATETIME values.
Related branches
Changed in maria: | |
milestone: | none → 5.3 |
assignee: | nobody → Sergei (sergii) |
description: | updated |
summary: |
- Diverging results with TIME(3) and BETWEEN depending on the execution + Diverging results with TIME(3) and ranges depending on the execution plan in 5.1-micro |
description: | updated |
Changed in maria: | |
importance: | Undecided → High |
status: | New → In Progress |
Changed in maria: | |
status: | In Progress → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |
Another test case:
--source include/ have_innodb. inc
CREATE TABLE t1 ( f1 int, f2 datetime(3), KEY (f2), KEY (f1,f2)) ENGINE=InnoDB; 000'),( 2,'2007- 01-14 15:18:50.000');
INSERT IGNORE INTO t1 VALUES (2,'0000-00-00 00:00:00.
SELECT * FROM t1 FORCE KEY ( f2 ) WHERE f2 <= '2004-08-28 23:36:31.000000' ;
SELECT * FROM t1 IGNORE KEY ( f2 ) WHERE f2 <= '2004-08-28 23:36:31.000000' ;