Issue Details (XML | Word | Printable)

Key: CORE-4318
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Simonov Denis
Votes: 0
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Firebird Core

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

Created: 16/Jan/14 08:13 PM   Updated: 22/Sep/15 09:40 AM
Component/s: Engine
Affects Version/s: 3.0 Alpha 2
Fix Version/s: 3.0 Beta 1

Environment: Firebird 3.0.0.30837, isql

QA Status: Done successfully


 Description  « Hide
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 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;

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Sean Leyne added a comment - 16/Jan/14 09:28 PM
Edited for readability

Sean Leyne made changes - 16/Jan/14 09:28 PM
Field Original Value New Value
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 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 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;
Dmitry Yemanov added a comment - 16/Jan/14 09:47 PM
Denis, is it a regression or is v2.5 also affected?

Dmitry Yemanov made changes - 16/Jan/14 09:59 PM
Assignee Dmitry Yemanov [ dimitr ]
Simonov Denis added a comment - 17/Jan/14 05:20 AM
It is regression. I checked on the Firebird-2.5.3.26735 version isn't reproduced

Dmitry Yemanov made changes - 17/Jan/14 11:49 AM
Summary sub-optimal query execution inside PSQL block using parameters Predicates involving PSQL variables/parameters are not pushed inside the aggregation
Dmitry Yemanov made changes - 17/Jan/14 12:19 PM
Status Open [ 1 ] Resolved [ 5 ]
Fix Version/s 3.0 Beta 1 [ 10332 ]
Resolution Fixed [ 1 ]
Sean Leyne made changes - 17/Jan/14 04:24 PM
Summary Predicates involving PSQL variables/parameters are not pushed inside the aggregation Regression: Predicates involving PSQL variables/parameters are not pushed inside the aggregation
Pavel Zotov made changes - 29/May/15 04:07 PM
Status Resolved [ 5 ] Resolved [ 5 ]
QA Status Done successfully
Pavel Cisar made changes - 22/Sep/15 09:39 AM
Status Resolved [ 5 ] Closed [ 6 ]