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: Descending order - Click to sort in ascending order
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 added a comment - 16/Jan/14 09:47 PM
Denis, is it a regression or is v2.5 also affected?

Sean Leyne added a comment - 16/Jan/14 09:28 PM
Edited for readability