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

[Subqueries] Throw an error if COALESCE/NVL is used in correlated condition

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Open
    • Major
    • Resolution: Unresolved
    • None
    • None
    • Query Planning
    • None

    Description

      NVL could lead to wrong results

      create table TABLEA (id int, lib string);
      insert into TABLEA values (1, 'a'),(2, 'b'),(3, null),(5,'zx');
      create table TABLEB (id int, lib string);
      insert into TABLEB values (1, 'a'),(4, 'c'),(3, null),(5,'zy');
      
      select *
      from TABLEA a
      where exists (
             select 1
             from TABLEB b
             where nvl(a.lib,0) = nvl(b.lib,0)
      );
      

      **OUTPUT**

      +-------+--------+
      | a.id  | a.lib  |
      +-------+--------+
      | 1     | a      |
      +-------+--------+
      

      **EXPECTED**

      +-------+--------+
      | a.id  | a.lib  |
      +-------+--------+
      | 1     | a      |
      | 3     | NULL   |
      +-------+--------+
      

      Attachments

        Activity

          People

            vgarg Vineet Garg
            vgarg Vineet Garg
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated: