Issue Details (XML | Word | Printable)

Key: CORE-4809
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Minor Minor
Assignee: Dmitry Yemanov
Reporter: Pavel Zotov
Votes: 1
Watchers: 3
Operations

If you were logged in you would be able to see more operations.
Firebird Core

HASH/MERGE JOIN is not used for more than two streams if they are joined via USING/NATURAL clauses and join is based on DBKEY concatenations

Created: 20/May/15 05:32 PM   Updated: 01/Jun/15 05:51 PM
Component/s: Engine
Affects Version/s: 2.5.0, 2.5.1, 2.5.2, 2.1.5 Update 1, 2.5.2 Update 1, 3.0 Alpha 1, 3.0 Alpha 2, 2.1.6, 2.5.3, 3.0 Beta 1, 2.1.7, 2.5.3 Update 1, 2.5.4
Fix Version/s: 3.0 Beta 2, 2.5.5

QA Status: Done successfully
Test Details: Test verifies only 3.0. For 2.5.x see test for CORE-4822.


 Description  « Hide
recreate table tn(x int primary key using index tn_x);
commit;
insert into tn select row_number()over() from rdb$types;
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 HASH (S TN NATURAL, R 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 HASH (HASH (T TN NATURAL, S TN NATURAL), R 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 HASH (HASH (HASH (U TN NATURAL, T TN NATURAL), S TN NATURAL), R TN NATURAL)

Conclusion: ALL OK for this form of joins, optimizer DOES take in account ability to apply HJ regardless of data sources number.

----------- 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 HASH (S TN NATURAL, R 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 (HASH (S TN NATURAL, R TN NATURAL), T TN NATURAL) ----------- NESTED LOOPS on outer phase

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 (HASH (S TN NATURAL, R TN NATURAL), T TN NATURAL, U TN NATURAL) ----------- NESTED LOOPS on outer phase

----------- 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 HASH (S TN NATURAL, R 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 (HASH (S TN NATURAL, R TN NATURAL), T TN NATURAL) ----------- NESTED LOOPS on outer phase

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 (HASH (S TN NATURAL, R TN NATURAL), T TN NATURAL, U TN NATURAL) ----------- NESTED LOOPS on outer phase



 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
[ Permalink | « Hide ]
Sean Leyne added a comment - 20/May/15 05:58 PM
It appears that USING is not SQL standard conformant -- it seems to be an MySQL'ism.

[ Permalink | « Hide ]
Pavel Zotov added a comment - 21/May/15 09:36 AM
> USING is not SQL standard conforman

Quote 5WD-02-Foundation-2003-09.pdf (SQL-2003), page 312:

7.7 <joined table>
Function
Specify a table derived from a Cartesian product, inner join, or outer join.

Format
<joined table> ::=
<cross join>
| <qualified join>
| <natural join>
<cross join> ::=
<table reference> CROSS JOIN <table factor>
<qualified join> ::=
<table reference>[ <join type>] JOIN <table reference> <join specification>
<natural join> ::=
<table reference> NATURAL[ <join type>] JOIN <table factor> ----------------------------------------- [ 1 ]
<join specification> ::=
<join condition>
| <named columns join>
<join condition> ::= ON <search condition>
<named columns join> ::= USING <left paren> <join column list> <right paren> ---------------- [ 2 ]
<join type> ::=


What about this ?

PS. I was sure that JOIN USING & NATURAL JOIN both are just 'syntax sugar' in FB... :-)

[ Permalink | « Hide ]
Sean Leyne added a comment - 21/May/15 02:02 PM
Sorry, I had done some googling and could not find any usage aside from MySQL, hence the comment.

[ Permalink | « Hide ]
Pavel Zotov added a comment - 31/May/15 12:55 PM
If key for join is changed from RDB$DB_KEY to common field than all become OK in 3.0: optimizer DOES make plans with HASH join in all cases (checked on WI-T3.0.0.31846):

recreate table tn(id_key int primary key using index tn_id_key);
commit;
insert into tn(id_key)
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_id_key;
commit;

set planonly;

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

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

PLAN HASH (S TN NATURAL, R TN NATURAL)


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

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


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

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

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

select count(*)
from (select id_key||'' a from tn) r
join (select id_key||'' a from tn) s using(a);

PLAN HASH (S TN NATURAL, R TN NATURAL)

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

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

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

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

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

select count(*)
from (select id_key||'' a from tn) r
natural join (select id_key||'' a from tn) s;

PLAN HASH (S TN NATURAL, R TN NATURAL)

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

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

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

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


Powered by a free Atlassian JIRA open source license for Firebird (RDBMS). Try JIRA - bug tracking software for your team.
Atlassian JIRA the Professional Issue Tracker. (Enterprise Edition, Version: 3.13.5-#360) - Bug/feature request - Atlassian news - Contact Administrators