lkl created HIVE-26088:
--------------------------
Summary: a left join b left join c when b is null ，then b join c
is null , in this case when set hive.auto.convert.join=true ; map join is not
used ,because nullpointexecption
Key: HIVE-26088
URL: https://issues.apache.org/jira/browse/HIVE-26088
Project: Hive
Issue Type: Bug
Affects Versions: 3.1.2
Reporter: lkl
create database pr;
use pr;
create table t1(id int,name string) PARTITIONED BY (cls string) stored as orc;
create table t2(id int,name string) PARTITIONED BY (cls string) stored as orc;
create table t3(id int,name string) PARTITIONED BY (cls string) stored as orc;
insert into table t1 partition(cls=1) values(1,"zzz");
insert into table t3 partition(cls=1) values(1,"zzz");
sql:
select y1.id,y1.name,nvl(y2.id,"其他") as y2id ,nvl(y2.name,"其他") as y2name from
(select t1.id,t1.name from t1) y1
left join
(
select a.id,a.name ,b.id as bid,b.name as bname from
(select t2.id,t2.name from t2) a
left join
(select t3.id,t3.name from t3) b
on a.id = b.id
)y2
on y1.id=y2.id;
log msg：
No Stats for mapjoin@t1, Columns: name, id
No Stats for mapjoin@t3, Columns: id
Query ID = hadoop_20220330164204_2a822fd5-7630-43f9-929a-b95086df04bf
Total jobs = 3
2022-03-30 16:42:11 Starting to launch local task to process map join;
maximum memory = 3817865216
2022-03-30 16:42:12 Uploaded 1 File to:
file:/data/emr/hive/tmp/23201dda-9c15-4d6e-9904-12ae28d4dccb/hive_2022-03-30_16-42-04_837_944561186926371473-2/-local-10007/HashTable-Stage-6/MapJoin-mapfile30--.hashtable
(278 bytes)
Execution completed successfully
MapredLocal task succeeded
Launching Job 1 out of 3
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1646126523394_0063, Tracking URL =
http://172.21.64.46:5004/proxy/application_1646126523394_0063/
Kill Command = /usr/local/service/hadoop/bin/mapred job -kill
job_1646126523394_0063
Hadoop job information for Stage-6: number of mappers: 0; number of reducers: 0
2022-03-30 16:42:18,781 Stage-6 map = 0%, reduce = 0%
Ended Job = job_1646126523394_0063
Stage-7 is filtered out by condition resolver.
Stage-1 is selected by condition resolver.
Launching Job 2 out of 3
Number of reduce tasks not specified. Estimated from input data size: 1
In order to change the average load for a reducer (in bytes):
set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
set mapreduce.job.reduces=<number>
Starting Job = job_1646126523394_0064, Tracking URL =
http://172.21.64.46:5004/proxy/application_1646126523394_0064/
Kill Command = /usr/local/service/hadoop/bin/mapred job -kill
job_1646126523394_0064
Hadoop job information for Stage-1: number of mappers: 2; number of reducers: 1
2022-03-30 16:42:26,673 Stage-1 map = 0%, reduce = 0%
2022-03-30 16:42:30,768 Stage-1 map = 50%, reduce = 0%, Cumulative CPU 1.39 sec
2022-03-30 16:42:31,792 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 2.92
sec
2022-03-30 16:42:36,886 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 4.83
sec
MapReduce Total cumulative CPU time: 4 seconds 830 msec
Ended Job = job_1646126523394_0064
MapReduce Jobs Launched:
Stage-Stage-6: HDFS Read: 0 HDFS Write: 0 SUCCESS
Stage-Stage-1: Map: 2 Reduce: 1 Cumulative CPU: 4.83 sec HDFS Read: 15362
HDFS Write: 131 SUCCESS
Total MapReduce CPU Time Spent: 4 seconds 830 msec
OK
1 zzz 其他 其他
Time taken: 33.102 seconds, Fetched: 1 row(s)
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
