Issue Details (XML | Word | Printable)

Key: CORE-2709
Type: Bug Bug
Status: Reopened Reopened
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Alex Bekhtin
Votes: 0
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Many indexed reads in a compound index with NULLs

Created: 26/Oct/09 02:35 AM   Updated: 06/May/13 02:21 PM
Component/s: Engine
Affects Version/s: 2.1.0, 2.0.4, 2.1.1, 2.0.5, 2.1.2, 2.5 Beta 2, 2.1.3, 3.0 Initial, 2.0.6, 2.5.0, 2.1.4
Fix Version/s: None

Time Tracking:
Not Specified

Environment: Firebird-2.5.0.25731-0_Win32
Issue Links:
Relate

Planning Status: Unspecified


 Description  « Hide
CREATE TABLE TEST_TABLE (
    ID1 INTEGER,
    ID2 INTEGER,
    ID3 INTEGER
);
COMMIT;

INSERT INTO TEST_TABLE (ID1, ID2, ID3) VALUES (1, 1, NULL);
INSERT INTO TEST_TABLE (ID1, ID2, ID3) VALUES (1, 2, NULL);
INSERT INTO TEST_TABLE (ID1, ID2, ID3) VALUES (1, 3, NULL);
INSERT INTO TEST_TABLE (ID1, ID2, ID3) VALUES (2, 1, NULL);
INSERT INTO TEST_TABLE (ID1, ID2, ID3) VALUES (2, 2, NULL);
INSERT INTO TEST_TABLE (ID1, ID2, ID3) VALUES (2, 3, NULL);
COMMIT;

CREATE INDEX TEST_TABLE_IDX1 ON TEST_TABLE (ID1, ID2);
COMMIT;

select * from test_table where ID1 = 1 and ID2 = 2
-- one indexed read
select * from test_table where ID1 = 1 and ID2 IS NULL
-- zero indexed read

DROP INDEX TEST_TABLE_IDX1;
CREATE INDEX TEST_TABLE_IDX1 ON TEST_TABLE (ID1,ID2,ID3);

select * from test_table where ID1 = 1 and ID2 = 2
-- one indexed read again
select * from test_table where ID1 = 1 and ID2 IS NULL
-- 3(!!!) indexed reads

 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 22/Jul/11 08:25 AM
Indices in existing (not freshly restored) databases should be recreated in order to benefit from the fix.

Dmitry Yemanov added a comment - 10/Jun/12 01:27 PM
The fix has been removed from the FB 2.5 branch (in version v2.5.2) because of the found regressions. A partial solution that satisfies CORE-3449 is committed instead. The generic solution will be available in v3.0 only.

Dmitry Yemanov added a comment - 06/May/13 02:21 PM
Rolled back from FB3 as well, due to the found regressions. Other solutions are being investigated.