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
Comments
Commented by: Sean Leyne (seanleyne) Edited for readability |
Modified by: Sean Leyne (seanleyne)description: SET ECHO ON; CONNECT 'localhost/3051:test' USER 'sysdba' PASSWORD 'masterkey'; CREATE TABLE T1 ( CREATE TABLE T2 ( SET TERM ^; EXECUTE BLOCK 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 Expression
===================== Records affected: 1 SET TERM ^; EXECUTE BLOCK Select Expression
============ Records affected: 1 EXECUTE BLOCK Select Expression
============ Records affected: 1 EXECUTE BLOCK Select Expression
============ Records affected: 1 SET TERM ;^ QUIT; => SET ECHO ON; CONNECT 'localhost/3051:test' USER 'sysdba' PASSWORD 'masterkey'; CREATE TABLE T1 ( CREATE TABLE T2 ( SET TERM ^; EXECUTE BLOCK
END -- WHILE (I < 1000) DO BEGIN END^ SET TERM ;^ COMMIT; ALTER TABLE T1 ADD CONSTRAINT PK_T1 PRIMARY KEY (ID); WITH T AS ( Select Expression
===================== Records affected: 1 SET TERM ^; EXECUTE BLOCK SUSPEND; Select Expression
============ Records affected: 1 EXECUTE BLOCK SUSPEND; Select Expression
============ Records affected: 1 EXECUTE BLOCK SUSPEND; Select Expression
============ Records affected: 1 SET TERM ;^ QUIT; |
Commented by: @dyemanov Denis, is it a regression or is v2.5 also affected? |
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Commented by: @sim1984 It is regression. I checked on the Firebird-2.5.3.26735 version isn't reproduced |
Modified by: @dyemanovsummary: sub-optimal query execution inside PSQL block using parameters => Predicates involving PSQL variables/parameters are not pushed inside the aggregation |
Modified by: @dyemanovstatus: Open [ 1 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 3.0 Beta 1 [ 10332 ] |
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 |
Modified by: @pcisarstatus: Resolved [ 5 ] => Closed [ 6 ] |
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;
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
=====================
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
============
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
============
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
============
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
The text was updated successfully, but these errors were encountered: