[HIVE-24957] Wrong results when subquery has COALESCE in correlation predicate

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 4.0.0
• Fix Version/s: 4.0.0-alpha-1
• Labels: pull-request-available
• Assignee: Stamatis Zampetakis
• Reporter: Stamatis Zampetakis
• Created: 30/Mar/21 13:10
• Updated: 17/Nov/22 08:47
• Resolved: 22/Apr/21 11:21

Description

Consider the following example:

create table author (
    a_authorkey   int,
    a_name varchar(50));

create table book (
    b_bookkey   int,
    b_title varchar(50),
    b_authorkey int);

insert into author values (10, 'Victor Hugo');
insert into author values (20, 'Alexandre Dumas');
insert into author values (300, 'UNKNOWN');

insert into book values (1, 'Les Miserables', 10);
insert into book values (2, 'The Count of Monte Cristo', 20);
insert into book values (3, 'Men Without Women', 30);
insert into book values (4, 'Odyssey', null);

select b.b_title
from book b
where exists
          (select a_authorkey
           from author a
           where coalesce(b.b_authorkey, 300) = a.a_authorkey);

Expected results

B_TITLE
Les Miserables
The Count of Monte Cristo
Odyssey

Actual results

B_TITLE
Les Miserables
The Count of Monte Cristo

Odyssey is missing from the result set and it shouldn't since with the application of COALESCE operator it should match with the UNKNOWN author.

Issue Links

• depends upon: HIVE-24999 HiveSubQueryRemoveRule generates invalid plan for IN subquery with multiple correlations
• is related to: CALCITE-4560 Wrong plan when decorrelating EXISTS subquery with COALESCE in the predicate
• links to: GitHub Pull Request #2186