Bad execution plan if some stream depends on multiple streams via a function [CORE2975]

[firebird-automations]

Submitted by: @dyemanov

Votes: 2

Test case:

create table T1 (ID int, COL int);
create index T1_ID on T1 (ID);
create unique index T1_COL on T1 (COL);

create table T2 (ID int);
create index T2_ID on T2 (ID);

create table T3 (ID int);
create index T3_ID on T3 (ID);


select *
from T1
join T2 on T2.ID = T1.ID
join T3 on T3.ID = coalesce(T1.ID, T2.ID)
where T1.COL = ?

-- bad plan by FB 1.5
-- PLAN JOIN (T3 NATURAL, T1 INDEX (T1_COL), T2 INDEX (T2_ID))

-- bad plan by FB 2.x
-- PLAN MERGE (SORT (JOIN (T1 INDEX (T1_COL), T2 INDEX (T2_ID))), SORT (T3 NATURAL))

-- expected plan
-- PLAN JOIN (T1 INDEX (T1_COL), T2 INDEX (T2_ID), T3 INDEX (T3_ID))

[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

description: Test case:

create table T1 (ID int, COL int);
create index T1_ID on T1 (ID);
create unique index T1_COL on T1 (COL);

create table T2 (ID int);
create index T2_ID on T2 (ID);

create table T3 (ID int);
create index T3_ID on T3 (ID);

select *
from T1
join T2 on http://T2.ID = http://T1.ID
join T3 on http://T3.ID = coalesce(http://T1.ID, http://T2.ID)
where T1.COL = ?

-- bad plan by FB 1.5
-- PLAN JOIN (T3 NATURAL, T1 INDEX (T1_COL), T2 INDEX (T2_ID))

-- bad plan by FB 2.x
-- PLAN MERGE (SORT (JOIN (T1 INDEX (T1_COL), T2 INDEX (T2_ID))), SORT (T3 NATURAL))

-- expected plan
-- PLAN JOIN (T1 INDEX (T1_ID), T2 INDEX (T2_ID), T3 INDEX (T3_ID))

=>

Test case:

create table T1 (ID int, COL int);
create index T1_ID on T1 (ID);
create unique index T1_COL on T1 (COL);

create table T2 (ID int);
create index T2_ID on T2 (ID);

create table T3 (ID int);
create index T3_ID on T3 (ID);

select *
from T1
join T2 on http://T2.ID = http://T1.ID
join T3 on http://T3.ID = coalesce(http://T1.ID, http://T2.ID)
where T1.COL = ?

-- bad plan by FB 1.5
-- PLAN JOIN (T3 NATURAL, T1 INDEX (T1_COL), T2 INDEX (T2_ID))

-- bad plan by FB 2.x
-- PLAN MERGE (SORT (JOIN (T1 INDEX (T1_COL), T2 INDEX (T2_ID))), SORT (T3 NATURAL))

-- expected plan
-- PLAN JOIN (T1 INDEX (T1_COL), T2 INDEX (T2_ID), T3 INDEX (T3_ID))

[firebird-automations]

Modified by: @dyemanov

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

[firebird-automations]

Modified by: @dyemanov

Link: This issue is duplicated by CORE4640 [ CORE4640 ]