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

Many indexed reads in a compound index with NULLs [CORE2709] #3106

Closed
firebird-automations opened this issue Oct 26, 2009 · 12 comments
Closed

Comments

@firebird-automations
Copy link
Collaborator

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

assignee: Vlad Khorsun [ hvlad ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to CORE3449 [ CORE3449 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: 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 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 2.5.1 [ 10333 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Indices in existing (not freshly restored) databases should be recreated in order to benefit from the fix.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue relate to CORE3675 [ CORE3675 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

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 CORE3449 is committed instead. The generic solution will be available in v3.0 only.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 2.5.1 [ 10333 ] =>

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Rolled back from FB3 as well, due to the found regressions. Other solutions are being investigated.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

resolution: Fixed [ 1 ] =>

Fix Version: 3.0 Alpha 1 [ 10331 ] =>

@hvlad
Copy link
Member

hvlad commented Apr 13, 2023

I can't reproduce it with 3.0.0 and 3.0.11

@hvlad hvlad closed this as completed Apr 13, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment