Optimizer on range query with multiple column index gives wrong results
------------------------------------------------------------------------
Key: CORE-4984
URL: http://tracker.firebirdsql.org/browse/CORE-4984
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 3.0 Beta 2
Environment: Windows 8.1. Tested with 3.0 Beta 2 and snapshot
3.0.0.32134 (Win32)
Reporter: James Linse
Priority: Critical
Incorrect results may be returned when running a range query on a table with an
index with multiple columns.
Below is a simple test case script.
CREATE TABLE TEST_IDX
(
ID integer NOT NULL,
VAL integer,
CREATE_DATE timestamp,
PRIMARY KEY (ID)
);
INSERT INTO TEST_IDX VALUES(1,1,'2016-01-01 01:01:00');
INSERT INTO TEST_IDX VALUES(2,1,'2015-01-02 01:01:00');
INSERT INTO TEST_IDX VALUES(3,2,'2014-02-01 01:01:00');
INSERT INTO TEST_IDX VALUES(4,2,'2015-02-02 01:01:00');
INSERT INTO TEST_IDX VALUES(5,3,'2015-03-01 01:01:00');
INSERT INTO TEST_IDX VALUES(6,3,'2015-03-02 01:01:00');
INSERT INTO TEST_IDX VALUES(7,4,'2015-04-01 01:01:00');
Before creating the multi column index running the query below will return
"01.02.2014, 01:01:00.000" This is correct.
SELECT MIN(CREATE_DATE) FROM TEST_IDX WHERE VAL >=1 AND VAL <=3;
Plan generated: PLAN (TEST_IDX NATURAL)
Now create the multi column index
CREATE INDEX IDX_VAL_CREATE_DATE ON TEST_IDX (VAL, CREATE_DATE);
The query below will now return "02.01.2015, 01:01:00.000"
SELECT MIN(CREATE_DATE) FROM TEST_IDX WHERE VAL >=1 AND VAL <=3;
Plan generated: PLAN (TEST_IDX ORDER IDX_VAL_CREATE_DATE)
If the query is rewritten as
SELECT MIN(CREATE_DATE) FROM TEST_IDX WHERE VAL BETWEEN 1 AND 3;
We still get "02.01.2015, 01:01:00.000"
Plan generated: PLAN (TEST_IDX ORDER IDX_VAL_CREATE_DATE)
However if the query is rewritten as
SELECT MIN(CREATE_DATE) FROM TEST_IDX WHERE VAL IN (1,2,3)
We get the correct result "01.02.2014, 01:01:00.000"
Plan generated: PLAN (TEST_IDX INDEX (IDX_VAL_CREATE_DATE, IDX_VAL_CREATE_DATE,
IDX_VAL_CREATE_DATE))
It would appear that the broken results are first getting the minimum VAL then
finding the minimum CREATE_DATE of that VAL.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
