[IMPALA-4610] add support for left outer join nested table expressions

• Type: Improvement
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: Impala 2.8.0
• Component/s: Frontend
• Labels: sql-language
• Assignee: Unassigned
• Reporter: Greg Rahn
• Created: 07/Dec/16 01:53
• Updated: 26/Jan/17 16:13

Description

Impala does not support the following syntax:

from branch
inner join order_header on branch.branch_code = order_header.sales_branch_code
inner join order_details on order_header.order_number = order_details.order_number
left outer join product
  inner join product_type on product.product_type_code = product_type.product_type_code
  inner join product_line on product_type.product_line_code = product_line.product_line_code
on order_details.product_number = product.product_number

or similarly

from branch 
inner join order_header on branch.branch_code = order_header.sales_branch_code
inner join order_details on order_header.order_number = order_details.order_number
left outer join (product 
                 inner join product_type
                 on product.product_type_code = product_type.product_type_code
                 inner join product_line 
                 on product_type.product_line_code = product_line.product_line_code
                )
on order_details.product_number = product.product_number

however it does support using a CTE this way

with a as (
  select
    product.product_number,
    product.introduction_date,
    product_line.product_line_en,
    product_type.product_type_en
  from product
  inner join product_type on product.product_type_code = product_type.product_type_code
  inner join product_line on product_type.product_line_code = product_line.product_line_code
)
select
  branch.address1 as address1,
  order_header.order_date as order_date,
  order_details.ship_date as ship_date,
  a.introduction_date as introduction_date,
  a.product_line_en as product_line_en,
  a.product_type_en as product_type_en
from branch
inner join order_header on branch.branch_code = order_header.sales_branch_code
inner join order_details on order_header.order_number = order_details.order_number
left outer join a on order_details.product_number = a.product_number

Related is also supporting the syntax as found in this query (note the parentheses, although do not change meaning for inner joins, would for outer joins).

select
  movies.movieid movieid,
  movies.title title,
  ratings.rating ratings,
  users.userid userid,
  users.gender gender,
  users.age age,
  users.occupation occupation
from users
join (ratings join movies
      on (ratings.movieid = movies.movieid))
on (users.userid = ratings.userid)