[HIVE-17880] hive tez get error result whith using "join" join two subquery

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved

Release Note

hive hdfs yarn versin:2.3.4.0-3485

• Assignee: Unassigned
• Reporter: 李开青
• Created: 24/Oct/17 06:38
• Updated: 30/Oct/17 14:58

Description

table broadcast_time partitioned by par_date(yyyyMMdd)
createtime:yyyy-MM-dd HH:mm:ss
1,when use alias field par_date(same name with partitioned field)
SELECT
par_date,
count(1)
FROM
(
SELECT
qid,
substr(createtime, 1, 11) par_date
FROM broadcast_time
WHERE par_date = 20171023 AND qid = 1111111
) t1
JOIN
(
SELECT qid
FROM room_info
WHERE par_date = 20171023
) r
ON t1.qid = r.qid
GROUP BY par_date;
get reuslt:
20171023 39

2,when use alias field new_par_date(different with partitioned field)
SELECT
new_par_date,
count(1)
FROM
(
SELECT
qid,
substr(createtime, 1, 11) new_par_date
FROM broadcast_time
WHERE par_date = 20171023 AND qid = 3015850
) t1
JOIN
(
SELECT qid
FROM room_info
WHERE par_date = 20171023
) r
ON t1.qid = r.qid
GROUP BY new_par_date;
get result:
2015-10-19 1
2015-10-20 5
2015-10-21 3
2015-10-25 1
2015-10-31 1
2015-11-21 2
2015-11-24 2
2016-02-29 1
2016-03-01 2
2016-03-06 1
2016-03-11 1
2016-03-15 2
2016-03-16 1
2016-03-17 1
2016-03-21 1
2016-04-16 7
2016-05-07 2
2016-09-24 1
2017-05-12 2
2017-06-19 1
2017-06-20 1