[
https://issues.apache.org/jira/browse/HIVE-20232?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16564023#comment-16564023
]
Zheng Shao commented on HIVE-20232:
-----------------------------------
Can you simplify your example to a single row in table A and a single row in
table B?
> Basic division operator not working for select statement with join
> ------------------------------------------------------------------
>
> Key: HIVE-20232
> URL: https://issues.apache.org/jira/browse/HIVE-20232
> Project: Hive
> Issue Type: Bug
> Components: Operators
> Reporter: Michael Lee
> Priority: Blocker
>
> Hello,
> I am trying to divide the values of two fields that have been joined together
> on multiple criteria (offerlevelid, visit_date, days_to_action). For some
> rows, the quotient is correct, but for other rows, the result is zero. See
> below:
> TABLE A: mlee.mit_test1
> select * from mlee.mit_test1 limit 5;
>
> ||offerlevelid||action_date||visit_date||days_to_action||cluster||cnt||
> |29992|_2018-07-11_|_2018-06-28_|13|11158|1|
> |_29991_|_2018-07-12_|_2018-06-18_|24 |11158 |0 |
> |_5279_|_2018-07-01_|_2018-05-30_|32|11158 |10 |
> |_5279_|_2018-07-01_|_2018-06-02_ |29 |11158 |1 |
> |_5279_|_2018-07-02_|_2018-06-29_ |3 |11158 |3 |
>
> TABLE B: mlee_p2p.num_at_visit_vd
> select * from mlee_p2p.num_at_visit_vd limit 5;
> ||offerlevelid||action_date||visit_date||days_to_action||cnt||
> |5279|2018-07-06|_2018-06-17_| 19|1696 |
> |_5279_|_2018-07-07_|_2018-06-07_| 30|2072 |
> |_29991_|_2018-07-11_|_2018-07-09_| 2|361|
> |_29991_|_2018-07-10_|_2018-06-10_| 30|116|
> |29992 |_2018-07-02_|_2018-06-27_| 5|0 |
>
> When I attempt to perform division on a.cnt / b.cnt, the results do not make
> sense. Specifically, there are results of zero where a.cnt and b.cnt are
> integer values. I tried casting both as doubles, but that did not work
> either. See below, where I've bolded the "prob" values that do not make
> sense. Please advise!
>
> select
> a.offerlevelid,
> a.days_to_action,
> a.visit_date,
> a.cluster,
> a.cnt at_cluster_vd_dta_cnt,
> b.cnt at_vd_dta_cnt,
> a.cnt/b.cnt prob
> from mlee.mit_test1 a
> join mlee_p2p.num_at_visit_vd b on a.offerlevelid=b.offerlevelid
> and a.visit_date = b.visit_date
> and a.days_to_action = b.days_to_action
> order by a.days_to_action,a.visit_date
> limit 2000;
> ||offerlevelid||days_to_action||visit_date||cluster||at_cluster_vd_dta_cnt||at_vd_dta_cnt||prob||
> |29991|0|2018-07-01 |11158|1|111|.009009009009009009|
> |5279|0|2018-07-01|11158|8|3255|_0.002457757296466974_|
> |_29992_|0|_2018-07-02_ |11158|0|1|0.0|
> |_29991_|0|_2018-07-02_ |11158|2|247|*0.0*|
> |_5279_|0|_2018-07-02_ |11158|3|2268|_0.0013227513227513227_|
> |_5279_|0|_2018-07-03_|11158|4|3206|_0.0012476606363069245_|
> |_29991_|0|_2018-07-03_|11158|1|293|*0.0*|
> |_5279_|0|_2018-07-04_|11158|4|3523|_0.0011353959693443088_|
> |_29991_|0|_2018-07-04_|11158|2|203|_0.009852216748768473_|
> |_29992_|0|_2018-07-05_|11158|0|2|*0.0*|
>
>
>
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
