Uploaded image for project: 'Engineering'
  1. Engineering
  2. ENG-8932

AAUIW support for FULL OUTER JOIN queries

    XMLWordPrintable

    Details

    • Type: Story
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: V5.5.1
    • Fix Version/s: V6.2
    • Component/s: Core
    • Team Backlog:
      CORE team
    • Epic Link:
    • Story Points:
      13
    • Sprint:
      core75, core76

      Description

      The simple cases of equality joins one or a small number of columns will probably satisfy most user requirements. For example:

      SELECT t1., t2.
      FROM t1
      FULL OUTER JOIN t2 on t1.id = t2.id;

      or

      SELECT t1., t2.
      FROM t1
      FULL OUTER JOIN t2 on t1.id1 = t2.id1 AND t1.id2 = t2.id2;

      There are three pieces:
      planner support
      – make sure planner tries both/all join orders (unlike left/right joins that force outer-side first).
      – make sure outer joins against partitioned data only happens in the coordinator (like the one current case of left join) in any case where "non-matching rows" can only be determined with global context.

      EE support:

      For Nest Loop Index Join, collect in a hashset the right-side rows matched by the normal LEFT JOIN implementation. Then do a final scan (not qualified by any ON criteria) of all the right-side rows to produce with left column padding any rows that were not previously matched.

      For Nest Loop Join, use that same technique OR collect up front all the right-side row addresses into a hash set. Eliminate them as they are matched by the usual LEFT JOIN processing, then produce left-padded any right-side rows remaining in the hashset. It's not clear which of these alternatives is easier/better.

      This needs positive test cases for equality joins (partitioned and replicated tables) on one or more columns.
      It also needs test cases (positive or negative) for non-equality joins, combinations of equality and other join criteria (this SHOULD include left-side-only and right-side-only criteria).

      Advanced cases should be considered lower priority "nice to have"s:
      Reserve the option to guard against (disallow) FULL JOIN specifically on partitioned data without an equality condition (if it helps). If so, define a separate ticket.
      Reserve the option to guard against (disallow) FULL JOIN without an equality condition (if it helps). If so, define a separate ticket.
      Reserve the option to guard against (disallow) FULL JOIN with ANY non-equality condition – even if there is also an equality (if it helps). If so, define a separate ticket.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              pmartel Paul Martel
              Reporter:
              bballard Ben Ballard
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Zendesk Support