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

Regression: Predicates involving PSQL variables/parameters are not pushed inside the aggregation [CORE4318] #4641

Closed
firebird-automations opened this issue Jan 16, 2014 · 10 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @sim1984

SET ECHO ON;
SET NAMES WIN1251;
SET STAT ON;
SET COUNT ON;
SET AUTODDL ON;

CONNECT 'localhost/3051:test' USER 'sysdba' PASSWORD 'masterkey';

CREATE TABLE T1 (
ID INTEGER NOT NULL
);

CREATE TABLE T2 (
ID INTEGER NOT NULL,
T1_ID INTEGER
);

SET TERM ^;

EXECUTE BLOCK
AS
DECLARE VARIABLE I INTEGER = 0;
BEGIN
WHILE (I < 1000) DO BEGIN
I = I + 1;

INSERT INTO T2\(ID, T1\_ID\) VALUES\(:I, MOD\(:I, 10\)\);

MERGE INTO T1 USING \(
  SELECT MOD\(:I, 10\) AS F FROM RDB$DATABASE
\) SRC ON <http://T1.ID> = SRC\.F
WHEN NOT MATCHED THEN
   INSERT \(ID\) VALUES\(SRC\.F\);	

END -- WHILE (I < 1000) DO BEGIN

END^

SET TERM ;^

COMMIT;

ALTER TABLE T1 ADD CONSTRAINT PK_T1 PRIMARY KEY (ID);
ALTER TABLE T2 ADD CONSTRAINT PK_T2 PRIMARY KEY (ID);
ALTER TABLE T2 ADD CONSTRAINT FK_T2_REF_T1 FOREIGN KEY (T1_ID) REFERENCES T1(ID);

WITH T AS (
SELECT T1_ID AS T1_ID, SUM(ID) AS S
FROM T2
GROUP BY 1
)
SELECT S
FROM T
WHERE T1_ID = 1;

Select Expression
-> Filter
-> Aggregate
-> Filter
-> Table "T T2" Access By ID
-> Index "FK_T2_REF_T1" Scan

                S 

=====================
49600

Records affected: 1
Current memory = 279233856
Delta memory = 8600
Max memory = 279289456
Elapsed time= 0.00 sec
Buffers = 16384
Reads = 0
Writes 0
Fetches = 310

SET TERM ^;

EXECUTE BLOCK
RETURNS (
S INTEGER
)
AS
BEGIN
WITH T AS (
SELECT T1_ID AS T1_ID, SUM(ID) AS S
FROM T2
GROUP BY 1
)
SELECT S
FROM T
WHERE T1_ID = 1
INTO :S;

SUSPEND;
END^

Select Expression
-> Singularity Check
-> Filter
-> Aggregate
-> Filter
-> Table "T T2" Access By ID
-> Index "FK_T2_REF_T1" Scan

       S 

============
49600

Records affected: 1
Current memory = 279241576
Delta memory = 7720
Max memory = 279289456
Elapsed time= 0.00 sec
Buffers = 16384
Reads = 0
Writes 0
Fetches = 310

EXECUTE BLOCK
RETURNS (
S INTEGER
)
AS
DECLARE VARIABLE V INTEGER = 1;
BEGIN
WITH T AS (
SELECT T1_ID AS T1_ID, SUM(ID) AS S
FROM T2
GROUP BY 1
)
SELECT S
FROM T
WHERE T1_ID = :V
INTO :S;

SUSPEND;
END^

Select Expression
-> Singularity Check
-> Filter
-> Aggregate
-> Table "T T2" Access By ID
-> Index "FK_T2_REF_T1" Scan

       S 

============
49600

Records affected: 1
Current memory = 279244168
Delta memory = 2592
Max memory = 279289456
Elapsed time= 0.01 sec
Buffers = 16384
Reads = 0
Writes 0
Fetches = 3010 <---------------------- This value is very large

EXECUTE BLOCK
RETURNS (
S INTEGER
)
AS
BEGIN
WITH T AS (
SELECT T1_ID AS T1_ID, SUM(ID) AS S
FROM T2
GROUP BY 1
)
SELECT S
FROM T
WHERE T1_ID = 1
INTO :S;

SUSPEND;
END^

Select Expression
-> Singularity Check
-> Filter
-> Aggregate
-> Filter
-> Table "T T2" Access By ID
-> Index "FK_T2_REF_T1" Scan

       S 

============
49600

Records affected: 1
Current memory = 279242200
Delta memory = -1968
Max memory = 279289456
Elapsed time= 0.00 sec
Buffers = 16384
Reads = 0
Writes 0
Fetches = 310

SET TERM ;^

QUIT;

Commits: cad838b FirebirdSQL/fbt-repository@74ac37d

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

Edited for readability

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

description: SET ECHO ON;
SET NAMES WIN1251;
SET STAT ON;
SET COUNT ON;
SET AUTODDL ON;

CONNECT 'localhost/3051:test' USER 'sysdba' PASSWORD 'masterkey';

CREATE TABLE T1 (
ID INTEGER NOT NULL
);

CREATE TABLE T2 (
ID INTEGER NOT NULL,
T1_ID INTEGER
);

SET TERM ^;

EXECUTE BLOCK
AS
DECLARE VARIABLE I INTEGER = 0;
BEGIN
WHILE (I < 1000) DO
BEGIN
I = I + 1;
INSERT INTO T2(ID, T1_ID) VALUES(:I, MOD(:I, 10));
MERGE INTO T1
USING (
SELECT MOD(:I, 10) AS F
FROM RDB$DATABASE
) SRC ON http://T1.ID = SRC.F
WHEN NOT MATCHED THEN
INSERT (ID) VALUES(SRC.F);
END
END^

SET TERM ;^

COMMIT;

ALTER TABLE T1 ADD CONSTRAINT PK_T1 PRIMARY KEY (ID);

ALTER TABLE T2 ADD CONSTRAINT PK_T2 PRIMARY KEY (ID);

ALTER TABLE T2 ADD CONSTRAINT FK_T2_REF_T1 FOREIGN KEY (T1_ID) REFERENCES T1(ID);

WITH T AS (
SELECT
T1_ID AS T1_ID,
SUM(ID) AS S
FROM T2
GROUP BY 1
)
SELECT S
FROM T
WHERE T1_ID = 1;

Select Expression
-> Filter
-> Aggregate
-> Filter
-> Table "T T2" Access By ID
-> Index "FK_T2_REF_T1" Scan

                S 

=====================
49600

Records affected: 1
Current memory = 279233856
Delta memory = 8600
Max memory = 279289456
Elapsed time= 0.00 sec
Buffers = 16384
Reads = 0
Writes 0
Fetches = 310

SET TERM ^;

EXECUTE BLOCK
RETURNS (
S INTEGER
)
AS
BEGIN
WITH T AS (
SELECT
T1_ID AS T1_ID,
SUM(ID) AS S
FROM T2
GROUP BY 1
)
SELECT S
FROM T
WHERE T1_ID = 1
INTO :S;
SUSPEND;
END^

Select Expression
-> Singularity Check
-> Filter
-> Aggregate
-> Filter
-> Table "T T2" Access By ID
-> Index "FK_T2_REF_T1" Scan

       S 

============
49600

Records affected: 1
Current memory = 279241576
Delta memory = 7720
Max memory = 279289456
Elapsed time= 0.00 sec
Buffers = 16384
Reads = 0
Writes 0
Fetches = 310

EXECUTE BLOCK
RETURNS (
S INTEGER
)
AS
DECLARE VARIABLE V INTEGER = 1;
BEGIN
WITH T AS (
SELECT
T1_ID AS T1_ID,
SUM(ID) AS S
FROM T2
GROUP BY 1
)
SELECT S
FROM T
WHERE T1_ID = :V
INTO :S;
SUSPEND;
END^

Select Expression
-> Singularity Check
-> Filter
-> Aggregate
-> Table "T T2" Access By ID
-> Index "FK_T2_REF_T1" Scan

       S 

============
49600

Records affected: 1
Current memory = 279244168
Delta memory = 2592
Max memory = 279289456
Elapsed time= 0.01 sec
Buffers = 16384
Reads = 0
Writes 0
Fetches = 3010 <---------------------- This value is very large

EXECUTE BLOCK
RETURNS (
S INTEGER
)
AS
BEGIN
WITH T AS (
SELECT
T1_ID AS T1_ID,
SUM(ID) AS S
FROM T2
GROUP BY 1
)
SELECT S
FROM T
WHERE T1_ID = 1
INTO :S;
SUSPEND;
END^

Select Expression
-> Singularity Check
-> Filter
-> Aggregate
-> Filter
-> Table "T T2" Access By ID
-> Index "FK_T2_REF_T1" Scan

       S 

============
49600

Records affected: 1
Current memory = 279242200
Delta memory = -1968
Max memory = 279289456
Elapsed time= 0.00 sec
Buffers = 16384
Reads = 0
Writes 0
Fetches = 310

SET TERM ;^

QUIT;

=>

SET ECHO ON;
SET NAMES WIN1251;
SET STAT ON;
SET COUNT ON;
SET AUTODDL ON;

CONNECT 'localhost/3051:test' USER 'sysdba' PASSWORD 'masterkey';

CREATE TABLE T1 (
ID INTEGER NOT NULL
);

CREATE TABLE T2 (
ID INTEGER NOT NULL,
T1_ID INTEGER
);

SET TERM ^;

EXECUTE BLOCK
AS
DECLARE VARIABLE I INTEGER = 0;
BEGIN
WHILE (I < 1000) DO BEGIN
I = I + 1;

INSERT INTO T2\(ID, T1\_ID\) VALUES\(:I, MOD\(:I, 10\)\);

MERGE INTO T1 USING \(
  SELECT MOD\(:I, 10\) AS F FROM RDB$DATABASE
\) SRC ON <http://T1.ID> = SRC\.F
WHEN NOT MATCHED THEN
   INSERT \(ID\) VALUES\(SRC\.F\);	

END -- WHILE (I < 1000) DO BEGIN

END^

SET TERM ;^

COMMIT;

ALTER TABLE T1 ADD CONSTRAINT PK_T1 PRIMARY KEY (ID);
ALTER TABLE T2 ADD CONSTRAINT PK_T2 PRIMARY KEY (ID);
ALTER TABLE T2 ADD CONSTRAINT FK_T2_REF_T1 FOREIGN KEY (T1_ID) REFERENCES T1(ID);

WITH T AS (
SELECT T1_ID AS T1_ID, SUM(ID) AS S
FROM T2
GROUP BY 1
)
SELECT S
FROM T
WHERE T1_ID = 1;

Select Expression
-> Filter
-> Aggregate
-> Filter
-> Table "T T2" Access By ID
-> Index "FK_T2_REF_T1" Scan

                S 

=====================
49600

Records affected: 1
Current memory = 279233856
Delta memory = 8600
Max memory = 279289456
Elapsed time= 0.00 sec
Buffers = 16384
Reads = 0
Writes 0
Fetches = 310

SET TERM ^;

EXECUTE BLOCK
RETURNS (
S INTEGER
)
AS
BEGIN
WITH T AS (
SELECT T1_ID AS T1_ID, SUM(ID) AS S
FROM T2
GROUP BY 1
)
SELECT S
FROM T
WHERE T1_ID = 1
INTO :S;

SUSPEND;
END^

Select Expression
-> Singularity Check
-> Filter
-> Aggregate
-> Filter
-> Table "T T2" Access By ID
-> Index "FK_T2_REF_T1" Scan

       S 

============
49600

Records affected: 1
Current memory = 279241576
Delta memory = 7720
Max memory = 279289456
Elapsed time= 0.00 sec
Buffers = 16384
Reads = 0
Writes 0
Fetches = 310

EXECUTE BLOCK
RETURNS (
S INTEGER
)
AS
DECLARE VARIABLE V INTEGER = 1;
BEGIN
WITH T AS (
SELECT T1_ID AS T1_ID, SUM(ID) AS S
FROM T2
GROUP BY 1
)
SELECT S
FROM T
WHERE T1_ID = :V
INTO :S;

SUSPEND;
END^

Select Expression
-> Singularity Check
-> Filter
-> Aggregate
-> Table "T T2" Access By ID
-> Index "FK_T2_REF_T1" Scan

       S 

============
49600

Records affected: 1
Current memory = 279244168
Delta memory = 2592
Max memory = 279289456
Elapsed time= 0.01 sec
Buffers = 16384
Reads = 0
Writes 0
Fetches = 3010 <---------------------- This value is very large

EXECUTE BLOCK
RETURNS (
S INTEGER
)
AS
BEGIN
WITH T AS (
SELECT T1_ID AS T1_ID, SUM(ID) AS S
FROM T2
GROUP BY 1
)
SELECT S
FROM T
WHERE T1_ID = 1
INTO :S;

SUSPEND;
END^

Select Expression
-> Singularity Check
-> Filter
-> Aggregate
-> Filter
-> Table "T T2" Access By ID
-> Index "FK_T2_REF_T1" Scan

       S 

============
49600

Records affected: 1
Current memory = 279242200
Delta memory = -1968
Max memory = 279289456
Elapsed time= 0.00 sec
Buffers = 16384
Reads = 0
Writes 0
Fetches = 310

SET TERM ;^

QUIT;

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Denis, is it a regression or is v2.5 also affected?

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @sim1984

It is regression. I checked on the Firebird-2.5.3.26735 version isn't reproduced

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

summary: sub-optimal query execution inside PSQL block using parameters => Predicates involving PSQL variables/parameters are not pushed inside the aggregation

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 Beta 1 [ 10332 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

summary: Predicates involving PSQL variables/parameters are not pushed inside the aggregation => Regression: Predicates involving PSQL variables/parameters are not pushed inside the aggregation

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: Done successfully

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

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

No branches or pull requests

2 participants