Uploaded image for project: 'IMPALA'
  1. IMPALA
  2. IMPALA-4610

add support for left outer join nested table expressions

    XMLWordPrintableJSON

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: Impala 2.8.0
    • Fix Version/s: None
    • Component/s: Frontend
    • Labels:

      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)
      

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              grahn Greg Rahn
            • Votes:
              1 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated: