[jira] [Work logged] (HIVE-24957) Wrong results when subquery has COALESCE in correlation predicate

["ASF GitHub Bot (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/HIVE-24957?focusedWorklogId=584736&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-584736 ]

ASF GitHub Bot logged work on HIVE-24957:
-----------------------------------------

                Author: ASF GitHub Bot
            Created on: 18/Apr/21 10:13
            Start Date: 18/Apr/21 10:13
    Worklog Time Spent: 10m 
      Work Description: zabetak opened a new pull request #2186:
URL: https://github.com/apache/hive/pull/2186


   ### What changes were proposed in this pull request and why?
   Check commit messages for HIVE-24999 and HIVE-24957.
   
   ### Does this PR introduce _any_ user-facing change?
   Better performance and correct query results.
   
   ### How was this patch tested?
   Via existing tests:
   ```
   mvn test -Dtest=TestMiniLlapLocalCliDriver -Dqfile_regex="subquery.*"
   mvn test -Dtest=TestMiniLlapLocalCliDriver -Dqfile_regex="masking.*"
   mvn test -Dtest=TestTezTPCDS30TBPerfCliDriver
   ```
   Via newly added tests:
   mvn test -Dtest=TestMiniLlapLocalCliDriver -Dqfile="subquery_complex_correlation_predicates.q"
   mvn test -Dtest=TestMiniLlapLocalCliDriver -Dqfile="subquery_in_invalid_intermediate_plan.q" -Dcalcite.debug


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Issue Time Tracking
-------------------

            Worklog Id:     (was: 584736)
    Remaining Estimate: 0h
            Time Spent: 10m

> Wrong results when subquery has COALESCE in correlation predicate
> -----------------------------------------------------------------
>
>                 Key: HIVE-24957
>                 URL: https://issues.apache.org/jira/browse/HIVE-24957
>             Project: Hive
>          Issue Type: Bug
>    Affects Versions: 4.0.0
>            Reporter: Stamatis Zampetakis
>            Assignee: Stamatis Zampetakis
>            Priority: Major
>          Time Spent: 10m
>  Remaining Estimate: 0h
>
> Consider the following example:
> {code:sql}
> 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);
> {code}
> *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.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)