Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-435

LoptOptimizeJoinRule incorrectly re-orders outer joins

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 0.9.0-incubating
    • Fix Version/s: 0.9.2-incubating
    • Component/s: None
    • Labels:
      None

      Description

      Following query reproduces the problem:
      SELECT ss_item_sk, ss_ticket_number, ss_customer_sk, sr_return_quantity FROM store_sales LEFT OUTER JOIN store_returns ON (store_returns.sr_item_sk = store_sales.ss_item_sk AND store_returns.sr_ticket_number = store_sales.ss_ticket_number), reason WHERE store_returns.sr_reason_sk = reason.r_reason_sk AND r_reason_desc = 'reason 28';

      The Join graph given is:
      (SS --> SR) – Reason gets transformed in to
      (SS x Reason) --> SR

      This is an invalid reordering.

      A short term solution may be to split join graph on outer join.

        Activity

        Hide
        julianhyde Julian Hyde added a comment -

        Bulk update closing "fixed" issues that are more than a year old.

        Show
        julianhyde Julian Hyde added a comment - Bulk update closing "fixed" issues that are more than a year old.
        Show
        julianhyde Julian Hyde added a comment - Fixed in http://git-wip-us.apache.org/repos/asf/incubator-optiq/commit/99c7ff96 .
        Hide
        julianhyde Julian Hyde added a comment -

        Fixed in https://github.com/julianhyde/optiq/tree/optiq-435; still needs to be committed to master.

        Show
        julianhyde Julian Hyde added a comment - Fixed in https://github.com/julianhyde/optiq/tree/optiq-435 ; still needs to be committed to master.
        Hide
        jpullokkaran Laljo John Pullokkaran added a comment -

        Problem reproduces regardless; modified query:
        SELECT ss_item_sk, ss_ticket_number, ss_customer_sk, sr_return_quantity FROM store_sales LEFT OUTER JOIN store_returns ON (store_returns.sr_item_sk = store_sales.ss_item_sk AND store_returns.sr_ticket_number = store_sales.ss_ticket_number) inner join reason on store_returns.sr_reason_sk = reason.r_reason_sk AND r_reason_desc = 'reason 28';

        Show
        jpullokkaran Laljo John Pullokkaran added a comment - Problem reproduces regardless; modified query: SELECT ss_item_sk, ss_ticket_number, ss_customer_sk, sr_return_quantity FROM store_sales LEFT OUTER JOIN store_returns ON (store_returns.sr_item_sk = store_sales.ss_item_sk AND store_returns.sr_ticket_number = store_sales.ss_ticket_number) inner join reason on store_returns.sr_reason_sk = reason.r_reason_sk AND r_reason_desc = 'reason 28';
        Hide
        julianhyde Julian Hyde added a comment -

        Does this issue reproduce if you use "JOIN ... ON" rather than ", ... WHERE"? It help clarify, because I am not quite sure about the relative precedence of JOIN and ",".

        Show
        julianhyde Julian Hyde added a comment - Does this issue reproduce if you use "JOIN ... ON" rather than ", ... WHERE"? It help clarify, because I am not quite sure about the relative precedence of JOIN and ",".

          People

          • Assignee:
            julianhyde Julian Hyde
            Reporter:
            jpullokkaran Laljo John Pullokkaran
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development