[jira] [Created] (HIVE-26653) Wrong results when (map) joining multiple tables on partition column

["Stamatis Zampetakis (Jira)" <ji...@apache.org>]

Stamatis Zampetakis created HIVE-26653:
------------------------------------------

             Summary: Wrong results when (map) joining multiple tables on partition column
                 Key: HIVE-26653
                 URL: https://issues.apache.org/jira/browse/HIVE-26653
             Project: Hive
          Issue Type: Bug
          Components: HiveServer2
            Reporter: Stamatis Zampetakis
            Assignee: Stamatis Zampetakis


The result of the query must have exactly one row matching the date specified in the WHERE clause but the query returns nothing.
{code:sql}
CREATE TABLE table_a (`aid` string ) PARTITIONED BY (`p_dt` string)
row format delimited fields terminated by ',' stored as textfile;

LOAD DATA LOCAL INPATH '../../data/files/_tbla.csv' into TABLE table_a;

CREATE TABLE table_b (`bid` string) PARTITIONED BY (`p_dt` string)
row format delimited fields terminated by ',' stored as textfile;

LOAD DATA LOCAL INPATH '../../data/files/_tblb.csv' into TABLE table_b;

set hive.auto.convert.join=true;
set hive.optimize.semijoin.conversion=false;

SELECT a.p_dt
FROM ((SELECT p_dt
       FROM table_b
       GROUP BY p_dt) a
         JOIN
     (SELECT p_dt
      FROM table_a
      GROUP BY p_dt) b ON a.p_dt = b.p_dt
         JOIN
     (SELECT p_dt
      FROM table_a
      GROUP BY p_dt) c ON a.p_dt = c.p_dt)
WHERE a.p_dt =  translate(cast(to_date(date_sub('2022-08-01', 1)) AS string), '-', '');
{code}
+Expected result+
20220731

+Actual result+
Empty

To reproduce the problem the tables need to have some data. Values in aid and bid columns are not important. For p_dt column use one of the following values 20220731, 20220630.

I will attach some sample data with which the problem can be reproduced. The tables look like below.
||aid|pdt||
|611|20220731|
|239|20220630|
|...|...|

The problem can be reproduced via qtest in current master 
(commit [6b05d64ce8c7161415d97a7896ea50025322e30a|https://github.com/apache/hive/commit/6b05d64ce8c7161415d97a7896ea50025322e30a]) by running the TestMiniLlapLocalCliDriver.

There is specific query plan (will attach shortly) for which the problem shows up so if the plan changes slightly the problem may not appear anymore; this is why we need to set explicitly hive.optimize.semijoin.conversion and hive.auto.convert.join to trigger the problem.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)