Optimizer fails applying stream-local predicates before merging [CORE2832]

[firebird-automations]

Submitted by: Nikolay Ponomarenko (pnv82)

There is different number of reading for joined table RDB$RELATION_FIELDS, when "hint" SKIP 0 is added:

SELECT * FROM (
SELECT R.*
FROM RDB$RELATIONS_SEL R
WHERE R.RDB$RELATION_ID < 10
) REL
JOIN RDB$RELATION_FIELDS F ON F.RDB$RELATION_NAME = REL.RDB$RELATION_NAME

SELECT * FROM (
SELECT SKIP 0 R.*
FROM RDB$RELATIONS_SEL R
WHERE R.RDB$RELATION_ID < 10
) REL
JOIN RDB$RELATION_FIELDS F ON F.RDB$RELATION_NAME = REL.RDB$RELATION_NAME

Predicate WHERE R.RDB$RELATION_ID < 10 is applied after merging flows

CREATE OR ALTER PROCEDURE RDB$RELATIONS_SEL
RETURNS (
RDB$RELATION_ID SMALLINT,
RDB$SYSTEM_FLAG SMALLINT,
RDB$DBKEY_LENGTH SMALLINT,
RDB$FORMAT SMALLINT,
RDB$FIELD_ID SMALLINT,
RDB$RELATION_NAME CHAR(31),
RDB$SECURITY_CLASS CHAR(31),
RDB$EXTERNAL_FILE VARCHAR(255),
RDB$OWNER_NAME CHAR(31),
RDB$DEFAULT_CLASS CHAR(31),
RDB$FLAGS SMALLINT,
RDB$RELATION_TYPE SMALLINT)
AS
BEGIN
FOR SELECT RDB$RELATION_ID,
RDB$SYSTEM_FLAG,
RDB$DBKEY_LENGTH,
RDB$FORMAT,
RDB$FIELD_ID,
RDB$RELATION_NAME,
RDB$SECURITY_CLASS,
RDB$EXTERNAL_FILE,
RDB$OWNER_NAME,
RDB$DEFAULT_CLASS,
RDB$FLAGS,
RDB$RELATION_TYPE
FROM RDB$RELATIONS
INTO :RDB$RELATION_ID,
:RDB$SYSTEM_FLAG,
:RDB$DBKEY_LENGTH,
:RDB$FORMAT,
:RDB$FIELD_ID,
:RDB$RELATION_NAME,
:RDB$SECURITY_CLASS,
:RDB$EXTERNAL_FILE,
:RDB$OWNER_NAME,
:RDB$DEFAULT_CLASS,
:RDB$FLAGS,
:RDB$RELATION_TYPE
DO
BEGIN
SUSPEND;
END
END

Commits: 71e4f9a

[firebird-automations]

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

[firebird-automations]

Modified by: @dyemanov

status: Open [ 1 ] => In Progress [ 3 ]

[firebird-automations]

Commented by: @dyemanov

Just to add to the test case. The simpler join should also evaluate the predicate earlier:

SELECT *
FROM RDB$RELATIONS_SEL R
JOIN RDB$RELATION_FIELDS F ON F.RDB$RELATION_NAME = R.RDB$RELATION_NAME
WHERE R.RDB$RELATION_ID < 10

[firebird-automations]

Modified by: @dyemanov

issuetype: Improvement [ 4 ] => Bug [ 1 ]

Version: 2.5 Beta 2 [ 10300 ]

Version: 2.5 Beta 1 [ 10251 ]

Version: 2.1.2 [ 10270 ]

Version: 2.0.5 [ 10222 ]

Version: 2.1.1 [ 10223 ]

Version: 2.5 Alpha 1 [ 10224 ]

Version: 2.0.4 [ 10211 ]

Version: 2.1.0 [ 10041 ]

Version: 2.0.3 [ 10200 ]

Version: 2.0.2 [ 10130 ]

Version: 2.0.1 [ 10090 ]

Version: 2.0.0 [ 10091 ]

summary: Optimizer fails placing where predicate before merging => Optimizer fails placing stream-local predicates before merging

[firebird-automations]

Modified by: @dyemanov

summary: Optimizer fails placing stream-local predicates before merging => Optimizer fails applying stream-local predicates before merging

[firebird-automations]

Modified by: @dyemanov

status: In Progress [ 3 ] => Open [ 1 ]

[firebird-automations]

Modified by: @dyemanov

status: Open [ 1 ] => In Progress [ 3 ]

[firebird-automations]

Modified by: @dyemanov

Fix Version: 3.0 Beta 2 [ 10586 ]

[firebird-automations]

Modified by: @dyemanov

Fix Version: 3.0 RC 1 [ 10584 ]

Fix Version: 3.0 Beta 2 [ 10586 ] =>

[firebird-automations]

Modified by: @dyemanov

Fix Version: 3.0.0 [ 10048 ]

Fix Version: 3.0 RC 1 [ 10584 ] =>

[firebird-automations]

Modified by: @dyemanov

Fix Version: 3.0.0 [ 10048 ] =>