Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Ordering by compound index together with a range condition gives wrong results [CORE4984] #5275

Closed
firebird-issue-importer opened this issue Oct 30, 2015 · 7 comments

Comments

@firebird-issue-importer

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.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Oct 30, 2015

Commented by: Claudio Valderrama C. (robocop)

Confirmed that this is a regression in v3 respect to v2.5. In FB2.5 all your examples produce:
MIN

2014-02-01 01:01:00.0000

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Oct 30, 2015

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Oct 30, 2015

Modified by: @dyemanov

Link: This issue is related to CORE1846 [ CORE1846 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Oct 30, 2015

Modified by: @dyemanov

summary: Optimizer on range query with multiple column index gives wrong results => Ordering by compound index together with a range condition gives wrong results

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Oct 30, 2015

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0 RC 1 [ 10584 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 3, 2015

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done successfully

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.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 3, 2015

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants