Uploaded image for project: 'Hive'
  1. Hive
  2. HIVE-17039 Implement optimization rewritings that rely on database SQL constraints
  3. HIVE-17040

Join elimination in the presence of FK relationship

    XMLWordPrintableJSON

Details

    • Sub-task
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 3.0.0
    • 4.0.0-alpha-1
    • Logical Optimizer
    • None

    Description

      If the PK/UK table is not filtered, we can safely remove the join.

      A simple example:

      SELECT c_current_cdemo_sk
FROM customer, customer_address
ON c_current_addr_sk = ca_address_sk;

      As a Calcite rule, we could implement this rewriting by 1) matching a Project on top of a Join operator, 2) checking that only columns from the FK are used in the Project, 3) checking that the join condition matches the FK - PK/UK relationship, 4) pulling all the predicates from the PK/UK side and checking that the input is not filtered, and 5) removing the join, possibly adding a IS NOT NULL condition on the join column from the FK side.

      If the PK/UK table is filtered, we should still transform the Join into a SemiJoin operator.

      Attachments

        1. HIVE-17040.01.patch
          242 kB
          jcamachorodriguez
        2. HIVE-17040.02.patch
          260 kB
          jcamachorodriguez
        3. HIVE-17040.04.patch
          271 kB
          jcamachorodriguez
        4. HIVE-17040.05.patch
          275 kB
          jcamachorodriguez
        5. HIVE-17040.06.patch
          276 kB
          jcamachorodriguez
        6. HIVE-17040.07.patch
          276 kB
          jcamachorodriguez
        7. HIVE-17040.patch
          59 kB
          jcamachorodriguez

        Issue Links

          duplicates

          Bug - A problem which impairs or prevents the functions of the product. HIVE-20333 CBO: Join removal based on PK-FK declared constraints

          • Major - Major loss of function.
          • Resolved
          incorporates

          Sub-task - The sub-task of the issue HIVE-17044 Transform LEFT/RIGHT OUTER into INNER join in the presence of FK relationship

          • Major - Major loss of function.
          • Closed
          is required by

          Sub-task - The sub-task of the issue HIVE-27008 Fix multi_in_clause.q and current_date_timestamp tests in branch-3

          • Major - Major loss of function.
          • Open
          supercedes

          Improvement - An improvement or enhancement to an existing feature or task. HIVE-20501 Vectorization: Closed range fast-path for Fast Long hashset

          • Major - Major loss of function.
          • Resolved
          links to

          Web Link RB link

          Activity

            People

              jcamacho Jesús Camacho Rodríguez
              jcamacho Jesús Camacho Rodríguez
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: