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

MERGE JOIN cannot be used for DBKEY based expressions [CORE4822] #5119

Closed
firebird-issue-importer opened this issue May 31, 2015 · 4 comments
Closed

Comments

@firebird-issue-importer

Submitted by: @pavel-zotov

This ticket is derived from CORE4809 but represents a more generic case, only v2.x series are affected.

Following samples use only NESTED LOOPS:

recreate table tn(x int primary key using index tn_x);
commit;
insert into tn(x)
with recursive r as (select 0 i from rdb$database union all select r.i+1 from r where r.i<99)
select r1.i*100+r0.i from r r1, r r0;
commit;
set statistics index tn_x;
commit;

set planonly;

----------- test `traditional` join form -----------------

select count(*)
from (select rdb$db_key||'' a from tn) r
join (select rdb$db_key||'' a from tn) s on r.a = s.a;

PLAN JOIN (R TN NATURAL, S TN NATURAL)

select count(*)
from (select rdb$db_key||'' a from tn) r
join (select rdb$db_key||'' a from tn) s on r.a = s.a
join (select rdb$db_key||'' a from tn) t on s.a = t.a;

PLAN JOIN (R TN NATURAL, S TN NATURAL, T TN NATURAL)

select count(*)
from (select rdb$db_key||'' a from tn) r
join (select rdb$db_key||'' a from tn) s on r.a = s.a
join (select rdb$db_key||'' a from tn) t on s.a = t.a
join (select rdb$db_key||'' a from tn) u on t.a = u.a;

PLAN JOIN (R TN NATURAL, S TN NATURAL, T TN NATURAL, U TN NATURAL)

----------- test join on named columns form -----------------

select count(*)
from (select rdb$db_key||'' a from tn) r
join (select rdb$db_key||'' a from tn) s using(a);

PLAN JOIN (R TN NATURAL, S TN NATURAL)

select count(*)
from (select rdb$db_key||'' a from tn) r
join (select rdb$db_key||'' a from tn) s using(a)
join (select rdb$db_key||'' a from tn) t using(a);

PLAN JOIN (R TN NATURAL, S TN NATURAL, T TN NATURAL)

select count(*)
from (select rdb$db_key||'' a from tn) r
join (select rdb$db_key||'' a from tn) s using(a)
join (select rdb$db_key||'' a from tn) t using(a)
join (select rdb$db_key||'' a from tn) u using(a);

PLAN JOIN (R TN NATURAL, S TN NATURAL, T TN NATURAL, U TN NATURAL)

----------- test natural join form -----------------

select count(*)
from (select rdb$db_key||'' a from tn) r
natural join (select rdb$db_key||'' a from tn) s;

PLAN JOIN (R TN NATURAL, S TN NATURAL)

select count(*)
from (select rdb$db_key||'' a from tn) r
natural join (select rdb$db_key||'' a from tn) s
natural join (select rdb$db_key||'' a from tn) t;

PLAN JOIN (R TN NATURAL, S TN NATURAL, T TN NATURAL)

select count(*)
from (select rdb$db_key||'' a from tn) r
natural join (select rdb$db_key||'' a from tn) s
natural join (select rdb$db_key||'' a from tn) t
natural join (select rdb$db_key||'' a from tn) u;

PLAN JOIN (R TN NATURAL, S TN NATURAL, T TN NATURAL, U TN NATURAL)

Commits: 920b9fd FirebirdSQL/fbt-repository@d292a3f

====== Test Details ======

Test is related ONLY to versions 2.x. Block for 3.0 intentionally left EMPTY.
See test for CORE4809 in order to check issues about FB 3.0

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented May 31, 2015

Modified by: @dyemanov

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

Version: 2.1.7 [ 10651 ]

Version: 2.5.3 [ 10461 ]

Version: 2.1.6 [ 10460 ]

Version: 2.5.2 Update 1 [ 10521 ]

Version: 2.1.5 Update 1 [ 10522 ]

Version: 2.5.2 [ 10450 ]

Version: 2.5.1 [ 10333 ]

Version: 2.5.0 [ 10221 ]

assignee: Dmitry Yemanov [ dimitr ]

description: I've opened this ticket on request by dimitr, in order to separate issues about 2.5 and 3.0.
AFAIU, ticket 4809 is relevant only to FB 3.0.

Following samples use only NESTED LOOPS:

recreate table tn(x int primary key using index tn_x);
commit;
insert into tn(x)
with recursive r as (select 0 i from rdb$database union all select r.i+1 from r where r.i<99)
select r1.i*100+r0.i from r r1, r r0;
commit;
set statistics index tn_x;
commit;

set planonly;

----------- test `traditional` join form -----------------

select count(*)
from (select rdb$db_key||'' a from tn) r
join (select rdb$db_key||'' a from tn) s on r.a = s.a;

PLAN JOIN (R TN NATURAL, S TN NATURAL)

select count(*)
from (select rdb$db_key||'' a from tn) r
join (select rdb$db_key||'' a from tn) s on r.a = s.a
join (select rdb$db_key||'' a from tn) t on s.a = t.a;

PLAN JOIN (R TN NATURAL, S TN NATURAL, T TN NATURAL)

select count(*)
from (select rdb$db_key||'' a from tn) r
join (select rdb$db_key||'' a from tn) s on r.a = s.a
join (select rdb$db_key||'' a from tn) t on s.a = t.a
join (select rdb$db_key||'' a from tn) u on t.a = u.a;

PLAN JOIN (R TN NATURAL, S TN NATURAL, T TN NATURAL, U TN NATURAL)

----------- test join on named columns form -----------------

select count(*)
from (select rdb$db_key||'' a from tn) r
join (select rdb$db_key||'' a from tn) s using(a);

PLAN JOIN (R TN NATURAL, S TN NATURAL)

select count(*)
from (select rdb$db_key||'' a from tn) r
join (select rdb$db_key||'' a from tn) s using(a)
join (select rdb$db_key||'' a from tn) t using(a);

PLAN JOIN (R TN NATURAL, S TN NATURAL, T TN NATURAL)

select count(*)
from (select rdb$db_key||'' a from tn) r
join (select rdb$db_key||'' a from tn) s using(a)
join (select rdb$db_key||'' a from tn) t using(a)
join (select rdb$db_key||'' a from tn) u using(a);

PLAN JOIN (R TN NATURAL, S TN NATURAL, T TN NATURAL, U TN NATURAL)

----------- test natural join form -----------------

select count(*)
from (select rdb$db_key||'' a from tn) r
natural join (select rdb$db_key||'' a from tn) s;

PLAN JOIN (R TN NATURAL, S TN NATURAL)

select count(*)
from (select rdb$db_key||'' a from tn) r
natural join (select rdb$db_key||'' a from tn) s
natural join (select rdb$db_key||'' a from tn) t;

PLAN JOIN (R TN NATURAL, S TN NATURAL, T TN NATURAL)

select count(*)
from (select rdb$db_key||'' a from tn) r
natural join (select rdb$db_key||'' a from tn) s
natural join (select rdb$db_key||'' a from tn) t
natural join (select rdb$db_key||'' a from tn) u;

PLAN JOIN (R TN NATURAL, S TN NATURAL, T TN NATURAL, U TN NATURAL)

=>

This ticket is derived from CORE4809 but represents a more generic case, only v2.x series are affected.

Following samples use only NESTED LOOPS:

recreate table tn(x int primary key using index tn_x);
commit;
insert into tn(x)
with recursive r as (select 0 i from rdb$database union all select r.i+1 from r where r.i<99)
select r1.i*100+r0.i from r r1, r r0;
commit;
set statistics index tn_x;
commit;

set planonly;

----------- test `traditional` join form -----------------

select count(*)
from (select rdb$db_key||'' a from tn) r
join (select rdb$db_key||'' a from tn) s on r.a = s.a;

PLAN JOIN (R TN NATURAL, S TN NATURAL)

select count(*)
from (select rdb$db_key||'' a from tn) r
join (select rdb$db_key||'' a from tn) s on r.a = s.a
join (select rdb$db_key||'' a from tn) t on s.a = t.a;

PLAN JOIN (R TN NATURAL, S TN NATURAL, T TN NATURAL)

select count(*)
from (select rdb$db_key||'' a from tn) r
join (select rdb$db_key||'' a from tn) s on r.a = s.a
join (select rdb$db_key||'' a from tn) t on s.a = t.a
join (select rdb$db_key||'' a from tn) u on t.a = u.a;

PLAN JOIN (R TN NATURAL, S TN NATURAL, T TN NATURAL, U TN NATURAL)

----------- test join on named columns form -----------------

select count(*)
from (select rdb$db_key||'' a from tn) r
join (select rdb$db_key||'' a from tn) s using(a);

PLAN JOIN (R TN NATURAL, S TN NATURAL)

select count(*)
from (select rdb$db_key||'' a from tn) r
join (select rdb$db_key||'' a from tn) s using(a)
join (select rdb$db_key||'' a from tn) t using(a);

PLAN JOIN (R TN NATURAL, S TN NATURAL, T TN NATURAL)

select count(*)
from (select rdb$db_key||'' a from tn) r
join (select rdb$db_key||'' a from tn) s using(a)
join (select rdb$db_key||'' a from tn) t using(a)
join (select rdb$db_key||'' a from tn) u using(a);

PLAN JOIN (R TN NATURAL, S TN NATURAL, T TN NATURAL, U TN NATURAL)

----------- test natural join form -----------------

select count(*)
from (select rdb$db_key||'' a from tn) r
natural join (select rdb$db_key||'' a from tn) s;

PLAN JOIN (R TN NATURAL, S TN NATURAL)

select count(*)
from (select rdb$db_key||'' a from tn) r
natural join (select rdb$db_key||'' a from tn) s
natural join (select rdb$db_key||'' a from tn) t;

PLAN JOIN (R TN NATURAL, S TN NATURAL, T TN NATURAL)

select count(*)
from (select rdb$db_key||'' a from tn) r
natural join (select rdb$db_key||'' a from tn) s
natural join (select rdb$db_key||'' a from tn) t
natural join (select rdb$db_key||'' a from tn) u;

PLAN JOIN (R TN NATURAL, S TN NATURAL, T TN NATURAL, U TN NATURAL)

summary: Force optimizer to consider usage of MERGE JOIN when data sources are joined on USING(<col>) or by NATURAL clauses (related to 2.5 only) => MERGE JOIN cannot be used for DBKEY based expressions

Version: 2.5.5 [ 10670 ] =>

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented May 31, 2015

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 2.5.5 [ 10670 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jun 1, 2015

Modified by: @pavel-zotov

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

QA Status: No test => Done successfully

Test Details: Test is related ONLY to versions 2.x. Block for 3.0 intentionally left EMPTY.
See test for CORE4809 in order to check issues about FB 3.0

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jun 1, 2015

Modified by: @pavel-zotov

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment