Sourabh Badhya created HIVE-27267:
-------------------------------------
Summary: Incorrect results when doing bucket map join on decimal
bucketed column with subquery
Key: HIVE-27267
URL: https://issues.apache.org/jira/browse/HIVE-27267
Project: Hive
Issue Type: Bug
Reporter: Sourabh Badhya
The following queries when run on a Hive cluster produce no results -
Repro queries -
{code:java}
set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
set hive.support.concurrency=true;
set hive.convert.join.bucket.mapjoin.tez=true;
drop table if exists test_external_source;
create external table test_external_source (date_col date, string_col string,
decimal_col decimal(38,0)) stored as orc tblproperties
('external.table.purge'='true');
insert into table test_external_source values ('2022-08-30', 'pipeline',
'50000000000000000005905545593'), ('2022-08-16', 'pipeline',
'50000000000000000005905545593'), ('2022-09-01', 'pipeline',
'50000000000000000006008686831'), ('2022-08-30', 'pipeline',
'50000000000000000005992620837'), ('2022-09-01', 'pipeline',
'50000000000000000005992620837'), ('2022-09-01', 'pipeline',
'50000000000000000005992621067'), ('2022-08-30', 'pipeline',
'50000000000000000005992621067');
drop table if exists test_external_target;
create external table test_external_target (date_col date, string_col string,
decimal_col decimal(38,0)) stored as orc tblproperties
('external.table.purge'='true');
insert into table test_external_target values ('2017-05-17', 'pipeline',
'50000000000000000000441610525'), ('2018-12-20', 'pipeline',
'50000000000000000001048981030'), ('2020-06-30', 'pipeline',
'50000000000000000002332575516'), ('2021-08-16', 'pipeline',
'50000000000000000003897973989'), ('2017-06-06', 'pipeline',
'50000000000000000000449148729'), ('2017-09-08', 'pipeline',
'50000000000000000000525378314'), ('2022-08-30', 'pipeline',
'50000000000000000005905545593'), ('2022-08-16', 'pipeline',
'50000000000000000005905545593'), ('2018-05-03', 'pipeline',
'50000000000000000000750826355'), ('2020-01-10', 'pipeline',
'50000000000000000001816579677'), ('2021-11-01', 'pipeline',
'50000000000000000004269423714'), ('2017-11-07', 'pipeline',
'50000000000000000000585901787'), ('2019-10-15', 'pipeline',
'50000000000000000001598843430'), ('2020-04-01', 'pipeline',
'50000000000000000002035795461'), ('2020-02-24', 'pipeline',
'50000000000000000001932600185'), ('2020-04-27', 'pipeline',
'50000000000000000002108160849'), ('2016-07-05', 'pipeline',
'50000000000000000000054405114'), ('2020-06-02', 'pipeline',
'50000000000000000002234387967'), ('2020-08-21', 'pipeline',
'50000000000000000002529168758'), ('2021-02-17', 'pipeline',
'50000000000000000003158511687');
drop table if exists target_table;
drop table if exists source_table;
create table target_table(date_col date, string_col string, decimal_col
decimal(38,0)) clustered by (decimal_col) into 7 buckets stored as orc
tblproperties ('bucketing_version'='2', 'transactional'='true',
'transactional_properties'='default');
create table source_table(date_col date, string_col string, decimal_col
decimal(38,0)) clustered by (decimal_col) into 7 buckets stored as orc
tblproperties ('bucketing_version'='2', 'transactional'='true',
'transactional_properties'='default');
insert into table target_table select * from test_external_target;
insert into table source_table select * from test_external_source; {code}
Query which is under investigation -
{code:java}
select * from target_table inner join (select distinct date_col, 'pipeline'
string_col, decimal_col from source_table where coalesce(decimal_col,'') =
'50000000000000000005905545593') s on s.date_col = target_table.date_col AND
s.string_col = target_table.string_col AND s.decimal_col =
target_table.decimal_col; {code}
Expected result of the query - 2 records
{code:java}
+------------------------+--------------------------+--------------------------------+-------------+---------------+--------------------------------+
| target_table.date_col | target_table.string_col |
target_table.decimal_col | s.date_col | s.string_col |
s.decimal_col |
+------------------------+--------------------------+--------------------------------+-------------+---------------+--------------------------------+
| 2022-08-16 | pipeline |
50000000000000000005905545593 | 2022-08-16 | pipeline |
50000000000000000005905545593 |
| 2022-08-30 | pipeline |
50000000000000000005905545593 | 2022-08-30 | pipeline |
50000000000000000005905545593 |
+------------------------+--------------------------+--------------------------------+-------------+---------------+--------------------------------+
{code}
Actual result of the query - No records
{code:java}
+------------------------+--------------------------+---------------------------+-------------+---------------+----------------+
| target_table.date_col | target_table.string_col | target_table.decimal_col
| s.date_col | s.string_col | s.decimal_col |
+------------------------+--------------------------+---------------------------+-------------+---------------+----------------+
+------------------------+--------------------------+---------------------------+-------------+---------------+----------------+
{code}
The workaround which fetches the correct result here is to set the below config
to false -
{code:java}
set hive.convert.join.bucket.mapjoin.tez=false;{code}
Notes from investigation -
1. The batch containing the 2 results are forwarded correctly to the map join
operator. However, during the join comparision, the hash table is empty.
2. The problem seems to be that HashTableDummyOperator performs loading of hash
table with the records, however the map join operator does not take into
account all the hash tables from various instances of HashTableDummyOperator
(due to multiple map tasks initiated by bucket map join) but rather uses only
one hash table from one of the HashTableDummyOperator instance. In this case,
the selected instance had an empty hash table hence no records were matched in
the join operator.
3. If the table is unbucketed / 1-bucketed, then the results are correct. There
is only 1 map task which is spawned which loads the records into the hash
table. The workaround (setting *hive.convert.join.bucket.mapjoin.tez* to
{*}false{*}) also has the same effect since there is 1 map task which loads the
records into the hash table.
4. HashTableDummyOperator is created in the optimizer and is associated with
the plan, hence suspecting there is a some issue in the optimizer code.
Ideally, all hash tables from all instances of HashTableDummyOperator must be
used by the map join operator.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
