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)