Michael Lee created HIVE-20232:
----------------------------------
Summary: 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
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)
