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

Unnecessary 1 index read may occur when using strict inequality condition [CORE2165] #2596

Closed
firebird-automations opened this issue Nov 4, 2008 · 5 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Kuznetsov Eugene (eugene)

Is related to QA510

Run isql-script (for 3-d dialect, for example)

SET SQL DIALECT 3;
SET NAMES NONE;
CREATE DATABASE 'localhost:test_index3d'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 4096
DEFAULT CHARACTER SET NONE;

COMMIT;

CREATE TABLE MASTER1
(ID INTEGER NOT NULL);

CREATE TABLE MASTER2
(ID INTEGER NOT NULL);

ALTER TABLE MASTER1
ADD CONSTRAINT PK_MASTER1 PRIMARY KEY(ID);

ALTER TABLE MASTER2
ADD CONSTRAINT PK_MASTER2 PRIMARY KEY(ID);

COMMIT;

INSERT INTO MASTER1 VALUES (1);
INSERT INTO MASTER1 VALUES (2);
INSERT INTO MASTER1 VALUES (3);

SET TERM ^ ;

EXECUTE BLOCK
AS
DECLARE VARIABLE I INTEGER;
BEGIN
I = 0;
WHILE (I < 100000) DO BEGIN
I = I +1;
INSERT INTO MASTER2
VALUES
(:I);
END
END^

SET TERM ; ^

COMMIT;

SET STATISTICS INDEX PK_MASTER1;
SET STATISTICS INDEX PK_MASTER2;

COMMIT;

Now,

select * from master1
where id > 3

requires 1 index read from master1 (it shouldn't)

and

select Count(*) from master2
where id > 99000

requires 1001 index reads from master2 (should be 1000).

It seems, it's "floating" error, as

select * from master1
where id > 2

requires 1 index read - that is correct.

--
Best regards, Eugene

Commits: f58313e f3094b0 2e7ca28 FirebirdSQL/fbt-repository@be0eb2a FirebirdSQL/fbt-repository@b0a62c3 FirebirdSQL/fbt-repository@7b5f1a1

@firebird-automations
Copy link
Collaborator Author

Modified by: Kuznetsov Eugene (eugene)

summary: Unneccessary 1 index read may occur when using strict inequality condition => Unnecessary 1 index read may occur when using strict inequality condition

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

assignee: Vlad Khorsun [ hvlad ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 2 [ 10560 ]

Fix Version: 2.5.3 [ 10461 ]

Fix Version: 2.1.6 [ 10460 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA510 [ QA510 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test

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