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

Cannot JOIN ON result of COALESCE

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 2.3.2
    • Fix Version/s: None
    • Component/s: Query Processor
    • Labels:
      None
    • Environment:

      Description

      A join is returning no results when the ON clause is equating the results of two COALESCE functions. To reproduce:

      CREATE TABLE t5 (
            dno INTEGER,
            dname VARCHAR(30),
            eno INTEGER,
            ename VARCHAR(30));
      CREATE TABLE t6 (
            dno INTEGER,
            dname VARCHAR(30),
            eno INTEGER,
            ename VARCHAR(30));
      INSERT INTO t5 VALUES
            (10, 'FOO', NULL, NULL),
            (20, 'BAR', NULL, NULL),
            (NULL, NULL, 7300, 'LARRY'),
            (NULL, NULL, 7400, 'MOE'),
            (NULL, NULL, 7500, 'CURLY');
      INSERT INTO t6 VALUES
            (10, 'LENNON', NULL, NULL),
            (20, 'MCCARTNEY', NULL, NULL),
            (NULL, NULL, 7300, 'READY'),
            (NULL, NULL, 7400, 'WILLING'),
            (NULL, NULL, 7500, 'ABLE');
      
      -- Fails with 0 results
      SELECT *
      FROM t5
      INNER JOIN t6
      ON COALESCE(`t5`.`eno`, `t5`.`dno`) = COALESCE(`t6`.`eno`, `t6`.`dno`)
      
      -- Full cross with where clause works (in nonstrict mode), returning 5 results
      SELECT *
      FROM t5
      JOIN t6
      WHERE `t5`.`eno` = `t6`.`eno` OR `t5`.`dno` = `t6`.`dno`
      
      -- Strange that coalescing the same field returns 2 results...
      SELECT *
      FROM t5
      INNER JOIN t6
      ON COALESCE(`t5`.`dno`, `t5`.`dno`) = COALESCE(`t6`.`dno`, `t6`.`dno`)
      
      -- ...and coalescing the other field returns 3 results
      SELECT *
      FROM t5
      INNER JOIN t6
      ON COALESCE(`t5`.`eno`, `t5`.`eno`) = COALESCE(`t6`.`eno`, `t6`.`eno`)
      

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              pauljackson123 Paul Jackson
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated: