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 ] =>

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

resolution: Fixed [ 1 ]

Fix Version: 2.5.5 [ 10670 ]

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

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