View with "subselect" column join table and not use index [CORE3176]

[firebird-automations]

Submitted by: Pavel Vakeš (goffix)

Is related to CORE3090
Is related to QA446

Votes: 1

CREATE TABLE TMP
(
ID Integer NOT NULL,
CONSTRAINT PK_TMP_1 PRIMARY KEY (ID)
);

CREATE VIEW TMP_VIEW (ID1, ID2)
AS
SELECT 1,(SELECT 1 FROM RDB$DATABASE) FROM RDB$DATABASE;

/*** With view + subselect column ***/

SELECT * FROM tmp_view TV LEFT JOIN tmp T ON T.id=TV.id2

> PLAN JOIN (TV RDB$DATABASE NATURAL, T NATURAL)

- PK_TMP_1 is not used.

/*** With view + normal column ***/

SELECT * FROM tmp_view TV LEFT JOIN tmp T ON T.id=TV.id1

> PLAN JOIN (TV RDB$DATABASE NATURAL, T INDEX (PK_TMP_1))

- PK_TMP_1 is used.

/*** Without view + subselect column ***/

SELECT * FROM RDB$DATABASE TV LEFT JOIN tmp T ON T.id=(SELECT 1 FROM RDB$DATABASE)

> PLAN JOIN (TV NATURAL, T INDEX (PK_TMP_1))

- PK_TMP_1 is used.

In FB 2.1.3 and FB 2.5 RC3 is PK_TMP_1 used.

Commits: dbd1775 e78cf74 FirebirdSQL/fbt-repository@1185d93

[firebird-automations]

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

[firebird-automations]

Modified by: @dyemanov

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

[firebird-automations]

Modified by: @dyemanov

Link: This issue is related to CORE3090 [ CORE3090 ]

[firebird-automations]

Commented by: @dyemanov

This regression is caused by the fix for CORE3090.

[firebird-automations]

Modified by: @dyemanov

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

[firebird-automations]

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 2.5.1 [ 10333 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

[firebird-automations]

Modified by: @pcisar

Link: This issue is related to QA446 [ QA446 ]

[firebird-automations]

Modified by: @pmakowski

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

Fix Version: 3.0 Alpha 1 [ 10331 ] =>

[firebird-automations]

Modified by: @pavel-zotov

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

QA Status: Done successfully