[IMPALA-6083] Clarify scope of STRAIGHT_JOIN query hint in Impala docs

• Type: Improvement
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: Impala 2.10.0
• Fix Version/s: Impala 2.10.0
• Component/s: Docs
• Epic Color: ghx-label-9
• Assignee: John Russell
• Reporter: Alexander Behm
• Created: 19/Oct/17 18:54
• Updated: 18/Jan/18 19:54
• Resolved: 18/Jan/18 19:54

Description

The documentation for STRAIGHT_JOIN is missing one very important detail:
The scope of the hint is limited to the FROM clause of the query block where the hint appears. In particular, the hint does not apply recursively to all subqueries.

Existing docs are here:
https://impala.incubator.apache.org/docs/build/html/topics/impala_perf_joins.html

Examples
1. The hint here will prevent reordering of "v1" and "v2", but the joins between "t1" and "t2" and "t3" and "t4" can still be reordered.

select /* +straight_join */ count(*) from
(select t1.id as id from t1 join t2 on t1.id = t2.id) v1
join
(select t3.id as id from t3 join t4 on t3.id = t4.id) v2
on (v1.id = v2.id)

2. Fully hinted query. No join ordering is applied whatsoever.

select /* +straight_join */ count(*) from
(select /* +straight_join */ t1.id as id from t1 join t2 on t1.id = t2.id) v1
join
(select /* +straight_join */ t3.id as id from t3 join t4 on t3.id = t4.id) v2
on (v1.id = v2.id)