Many indexed reads in a compound index with NULLs [CORE2709] #3106
Description
Submitted by: Alex Bekhtin (afgm)
Is related to CORE3449
Relate to CORE3675
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
Commits: c1c5d2b cf6e3d0 a021e4d ce9e812 FirebirdSQL/fbt-repository@a290acc