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

["Zoltan Haindrich (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/HIVE-17880?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16225072#comment-16225072 ] 

Zoltan Haindrich commented on HIVE-17880:
-----------------------------------------


[~qf@1314] I was unable to reproduce your problem on current master; could you give the schemas of the tables; or try executing the following example; (note: it drops the tables; and recreates them)

{code:sql}
-- 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)

drop table if exists broadcast_time;
drop table if exists room_info;

-- create table broadcast_time (createtime string,par_date string,qid string);
create table broadcast_time (createtime string,qid string) partitioned by (par_date string);
create table room_info (par_date string,qid string);

insert into room_info values ('20171023','13');
insert into room_info values ('20171022','13');

insert into broadcast_time partition (par_date='20171023') values
	('20171023 A','13'),
	('20171023 B','13'),
	('20171023 C','13');

SELECT
  par_date,
  count(1)
FROM
  (
    SELECT
      qid,
      substr(createtime, 1, 11) par_date
    FROM broadcast_time
    WHERE par_date = 20171023 AND qid = 13
  ) 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 = 13
  ) t1
 JOIN
  (
    SELECT qid
    FROM room_info
    WHERE par_date = 20171023
  ) r
    ON t1.qid = r.qid
GROUP BY new_par_date;

-- more results :)
{code}

> hive tez get error result whith using  "join" join two subquery
> ---------------------------------------------------------------
>
>                 Key: HIVE-17880
>                 URL: https://issues.apache.org/jira/browse/HIVE-17880
>             Project: Hive
>          Issue Type: Bug
>            Reporter: 李开青
>
> 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



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)