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
Many indexed reads in a compound index with NULLs [CORE2709] #3106
Comments
Modified by: @hvladassignee: Vlad Khorsun [ hvlad ] |
Modified by: @dyemanovassignee: Vlad Khorsun [ hvlad ] => Dmitry Yemanov [ dimitr ] Version: 2.1.4 [ 10361 ] Version: 2.5.0 [ 10221 ] Version: 2.0.6 [ 10303 ] Version: 3.0 Initial [ 10301 ] Version: 2.1.3 [ 10302 ] Version: 2.1.2 [ 10270 ] Version: 2.0.5 [ 10222 ] Version: 2.1.1 [ 10223 ] Version: 2.0.4 [ 10211 ] Version: 2.1.0 [ 10041 ] |
Modified by: @dyemanovstatus: Open [ 1 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 3.0 Alpha 1 [ 10331 ] |
Modified by: @dyemanovFix Version: 2.5.1 [ 10333 ] |
Commented by: @dyemanov Indices in existing (not freshly restored) databases should be recreated in order to benefit from the fix. |
Modified by: @dyemanovFix Version: 2.5.1 [ 10333 ] => |
Commented by: @dyemanov Rolled back from FB3 as well, due to the found regressions. Other solutions are being investigated. |
Modified by: @dyemanovstatus: Resolved [ 5 ] => Reopened [ 4 ] resolution: Fixed [ 1 ] => Fix Version: 3.0 Alpha 1 [ 10331 ] => |
I can't reproduce it with 3.0.0 and 3.0.11 |
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
The text was updated successfully, but these errors were encountered: