Uploaded image for project: 'Hive'
  1. Hive
  2. HIVE-3381

Result of outer join is not valid

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 0.10.0
    • Fix Version/s: 0.11.0
    • Component/s: Query Processor
    • Labels:
      None

      Description

      Outer joins, especially full outer joins or outer join with filter on 'ON clause' is not showing proper results. For example, query in test join_1to1.q

      SELECT * FROM join_1to1_1 a full outer join join_1to1_2 b on a.key1 = b.key1 and a.value = 66 and b.value = 66 ORDER BY a.key1 ASC, a.key2 ASC, a.value ASC, b.key1 ASC, b.key2 ASC, b.value ASC;
      

      results

      NULL	NULL	NULL	NULL	NULL	66
      NULL	NULL	NULL	NULL	10050	66
      NULL	NULL	NULL	10	10010	66
      NULL	NULL	NULL	30	10030	88
      NULL	NULL	NULL	35	10035	88
      NULL	NULL	NULL	40	10040	88
      NULL	NULL	NULL	40	10040	88
      NULL	NULL	NULL	50	10050	88
      NULL	NULL	NULL	50	10050	88
      NULL	NULL	NULL	50	10050	88
      NULL	NULL	NULL	70	10040	88
      NULL	NULL	NULL	70	10040	88
      NULL	NULL	NULL	70	10040	88
      NULL	NULL	NULL	70	10040	88
      NULL	NULL	66	NULL	NULL	NULL
      NULL	10050	66	NULL	NULL	NULL
      5	10005	66	5	10005	66
      15	10015	66	NULL	NULL	NULL
      20	10020	66	20	10020	66
      25	10025	88	NULL	NULL	NULL
      30	10030	66	NULL	NULL	NULL
      35	10035	88	NULL	NULL	NULL
      40	10040	66	NULL	NULL	NULL
      40	10040	66	40	10040	66
      40	10040	88	NULL	NULL	NULL
      40	10040	88	NULL	NULL	NULL
      50	10050	66	NULL	NULL	NULL
      50	10050	66	50	10050	66
      50	10050	66	50	10050	66
      50	10050	88	NULL	NULL	NULL
      50	10050	88	NULL	NULL	NULL
      50	10050	88	NULL	NULL	NULL
      50	10050	88	NULL	NULL	NULL
      50	10050	88	NULL	NULL	NULL
      50	10050	88	NULL	NULL	NULL
      60	10040	66	60	10040	66
      60	10040	66	60	10040	66
      60	10040	66	60	10040	66
      60	10040	66	60	10040	66
      70	10040	66	NULL	NULL	NULL
      70	10040	66	NULL	NULL	NULL
      70	10040	66	NULL	NULL	NULL
      70	10040	66	NULL	NULL	NULL
      80	10040	88	NULL	NULL	NULL
      80	10040	88	NULL	NULL	NULL
      80	10040	88	NULL	NULL	NULL
      80	10040	88	NULL	NULL	NULL
      

      but it seemed not right. This should be

      NULL	NULL	NULL	NULL	NULL	66
      NULL	NULL	NULL	NULL	10050	66
      NULL	NULL	NULL	10	10010	66
      NULL	NULL	NULL	25	10025	66
      NULL	NULL	NULL	30	10030	88
      NULL	NULL	NULL	35	10035	88
      NULL	NULL	NULL	40	10040	88
      NULL	NULL	NULL	50	10050	88
      NULL	NULL	NULL	70	10040	88
      NULL	NULL	NULL	70	10040	88
      NULL	NULL	NULL	80	10040	66
      NULL	NULL	NULL	80	10040	66
      NULL	NULL	66	NULL	NULL	NULL
      NULL	10050	66	NULL	NULL	NULL
      5	10005	66	5	10005	66
      15	10015	66	NULL	NULL	NULL
      20	10020	66	20	10020	66
      25	10025	88	NULL	NULL	NULL
      30	10030	66	NULL	NULL	NULL
      35	10035	88	NULL	NULL	NULL
      40	10040	66	40	10040	66
      40	10040	88	NULL	NULL	NULL
      50	10050	66	50	10050	66
      50	10050	66	50	10050	66
      50	10050	88	NULL	NULL	NULL
      50	10050	88	NULL	NULL	NULL
      60	10040	66	60	10040	66
      60	10040	66	60	10040	66
      60	10040	66	60	10040	66
      60	10040	66	60	10040	66
      70	10040	66	NULL	NULL	NULL
      70	10040	66	NULL	NULL	NULL
      80	10040	88	NULL	NULL	NULL
      80	10040	88	NULL	NULL	NULL
      

        Attachments

        1. HIVE-3381.D5565.3.patch
          362 kB
          Phabricator
        2. HIVE-3381.D5565.4.patch
          366 kB
          Phabricator
        3. HIVE-3381.D5565.5.patch
          364 kB
          Phabricator
        4. HIVE-3381.D5565.6.patch
          365 kB
          Phabricator
        5. mapjoin_testOuter.q
          2 kB
          Vikram Dixit K
        6. HIVE-3381.D5565.7.patch
          399 kB
          Phabricator

          Issue Links

            Activity

              People

              • Assignee:
                navis Navis
                Reporter:
                navis Navis
              • Votes:
                0 Vote for this issue
                Watchers:
                11 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: