You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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).
summary: Unneccessary 1 index read may occur when using strict inequality condition => Unnecessary 1 index read may occur when using strict inequality condition
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
The text was updated successfully, but these errors were encountered: