[MDEV-2380] LP:743017 - Diverging results with TIME(3) and ranges depending on the execution plan in 5.1-micro Created: 2011-03-26  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Philip Stoev (Inactive) Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug743017.xml    

 Description   

Using BETWEEN in the WHERE clause returns different results depending on the query plan:

--source include/have_innodb.inc
CREATE TABLE t1 ( f1 time(3), f2 datetime, KEY (f1)) Engine=innodb;
INSERT IGNORE INTO t1 VALUES ('07:07:20.000','0000-00-00 00:00:00.000'),('15:47:11.000','2000-12-22 17:22:54.000'),
('17:41:58.000','2006-09-09 00:00:00.000'),('00:20:03.000','2006-01-19 00:00:00.000'),
('13:27:12.000','0000-00-00 00:00:00.000'),('00:20:04.000','2001-12-22 10:14:36.000');

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.

 Comments   
Comment by Philip Stoev (Inactive) [ 2011-03-26 ]

Diverging results with TIME(3) and BETWEEN depending on the execution plan in 5.1-micro
The following query which mixes date and datetime in BETWEEN returns different results depending on the query plan:

--source include/have_innodb.inc
CREATE TABLE t1 ( f1 time(3), f2 datetime, KEY (f1)) Engine=innodb;
INSERT IGNORE INTO t1 VALUES ('07:07:20.000','0000-00-00 00:00:00.000'),('15:47:11.000','2000-12-22 17:22:54.000'),
('17:41:58.000','2006-09-09 00:00:00.000'),('00:20:03.000','2006-01-19 00:00:00.000'),
('13:27:12.000','0000-00-00 00:00:00.000'),('00:20:04.000','2001-12-22 10:14:36.000');

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).

Comment by Philip Stoev (Inactive) [ 2011-03-26 ]

Re: Diverging results with TIME(3) and ranges depending on the execution plan in 5.1-micro
Another test case:

--source include/have_innodb.inc

CREATE TABLE t1 ( f1 int, f2 datetime(3), KEY (f2), KEY (f1,f2)) ENGINE=InnoDB;
INSERT IGNORE INTO t1 VALUES (2,'0000-00-00 00:00:00.000'),(2,'2007-01-14 15:18:50.000');

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' ;

Comment by Rasmus Johansson (Inactive) [ 2011-10-28 ]

Launchpad bug id: 743017

Generated at Thu Feb 08 06:41:27 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.