Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
firebird-automations opened this issue Apr 20, 2010 · 5 comments

Comments

@firebird-automations
Copy link
Collaborator

firebird-automations commented Apr 20, 2010

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
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

@firebird-automations
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Modified by: @dyemanov

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is duplicated by CORE4640 [ CORE4640 ]

dyemanov added a commit that referenced this issue Feb 8, 2022
…le streams via a function) and its kissing cousin #7118 (Chained JOIN USING across the same column names may be optimized badly)
dyemanov added a commit that referenced this issue Feb 8, 2022
…le streams via a function) and its kissing cousin #7118 (Chained JOIN USING across the same column names may be optimized badly)
dyemanov added a commit that referenced this issue Sep 13, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment