You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@hive.apache.org by "Mahesh Raju Somalaraju (Jira)" <ji...@apache.org> on 2023/04/27 14:53:00 UTC
[jira] [Created] (HIVE-27303) select query result is different when enable/disable mapjoin with UNION ALL
Mahesh Raju Somalaraju created HIVE-27303:
---------------------------------------------
Summary: select query result is different when enable/disable mapjoin with UNION ALL
Key: HIVE-27303
URL: https://issues.apache.org/jira/browse/HIVE-27303
Project: Hive
Issue Type: Bug
Reporter: Mahesh Raju Somalaraju
Assignee: Mahesh Raju Somalaraju
select query result is different when enable/disable mapjoin with UNION ALL
Below are the reproduce steps.
As per query when map.join is disabled it should not give rows(duplicate). Same is working fine with map.join=true.
Expected result: Empty rows.
Problem: returning duplicate rows.
Steps:
----------
SET hive.server2.tez.queue.access.check=true;
SET tez.queue.name=default
SET hive.query.results.cache.enabled=false;
SET hive.fetch.task.conversion=none;
SET hive.execution.engine=tez;
SET hive.stats.autogather=true;
SET hive.server2.enable.doAs=false;
SET hive.auto.convert.join=true;
drop table if exists hive1_tbl_data;
drop table if exists hive2_tbl_data;
drop table if exists hive3_tbl_data;
drop table if exists hive4_tbl_data;
CREATE EXTERNAL TABLE hive1_tbl_data (COLUMID string,COLUMN_FN string,COLUMN_LN string,EMAIL string,COL_UPDATED_DATE timestamp, PK_COLUM string)
ROW FORMAT SERDE
'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
TBLPROPERTIES (
'TRANSLATED_TO_EXTERNAL'='true',
'bucketing_version'='2',
'external.table.purge'='true',
'parquet.compression'='SNAPPY');
CREATE EXTERNAL TABLE hive2_tbl_data (COLUMID string,COLUMN_FN string,COLUMN_LN string,EMAIL string,COL_UPDATED_DATE timestamp, PK_COLUM string)
ROW FORMAT SERDE
'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
TBLPROPERTIES (
'TRANSLATED_TO_EXTERNAL'='true',
'bucketing_version'='2',
'external.table.purge'='true',
'parquet.compression'='SNAPPY');
CREATE EXTERNAL TABLE hive3_tbl_data (COLUMID string,COLUMN_FN string,COLUMN_LN string,EMAIL string,COL_UPDATED_DATE timestamp, PK_COLUM string)
ROW FORMAT SERDE
'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
TBLPROPERTIES (
'TRANSLATED_TO_EXTERNAL'='true',
'bucketing_version'='2',
'external.table.purge'='true',
'parquet.compression'='SNAPPY');
CREATE EXTERNAL TABLE hive4_tbl_data (COLUMID string,COLUMN_FN string,COLUMN_LN string,EMAIL string,COL_UPDATED_DATE timestamp, PK_COLUM string)
ROW FORMAT SERDE
'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
TBLPROPERTIES (
'TRANSLATED_TO_EXTERNAL'='true',
'bucketing_version'='2',
'external.table.purge'='true',
'parquet.compression'='SNAPPY');
insert into table hive1_tbl_data select '00001','john','doe','john@hotmail.com','2014-01-01 12:01:02','4000-10000';
insert into table hive1_tbl_data select '00002','john','doe','john@hotmail.com','2014-01-01 12:01:02','4000-10000';insert into table hive2_tbl_data select '00001','john','doe','john@hotmail.com','2014-01-01 12:01:02','00001';
insert into table hive2_tbl_data select '00002','john','doe','john@hotmail.com','2014-01-01 12:01:02','00001';
select
t.COLUMID
from (
select distinct
t.COLUMID as COLUMID
from (SELECT COLUMID FROM hive3_tbl_data UNION ALL SELECT COLUMID FROM hive1_tbl_data) t
) t
left join (
select
distinct t.COLUMID
from (SELECT COLUMID FROM hive4_tbl_data UNION ALL SELECT COLUMID FROM hive2_tbl_data) t
) t1 on t.COLUMID = t1.COLUMID
where t1.COLUMID is null;
--
This message was sent by Atlassian Jira
(v8.20.10#820010)