Submitted by: James Linse (jlinse)

Is related to CORE1846

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.

Commits: 7134690 FirebirdSQL/fbt-repository@300c9f2 FirebirdSQL/fbt-repository@a90d37e

====== Test Details ======

Confirmed on WI-V3.0.0.32081: wrong output for CHECKED_RESULT_1, CHECKED_RESULT_3 & CHECKED_RESULT_4
(but this was only for ASC index; for DESC index all was fine).
Since WI-V3.0.0.32137 result is correct.