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-issue-importer opened this issue Nov 4, 2008 · 5 comments

Comments

@firebird-issue-importer
Copy link

firebird-issue-importer commented Nov 4, 2008

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-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 4, 2008

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-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 4, 2008

Modified by: @hvlad

assignee: Vlad Khorsun [ hvlad ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jan 9, 2014

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-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 27, 2014

Modified by: @pcisar

Link: This issue is related to QA510 [ QA510 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jan 19, 2016

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