[
https://issues.apache.org/jira/browse/HIVE-20232?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17058876#comment-17058876
]
David McGinnis commented on HIVE-20232:
---------------------------------------
[~mike_b_lee]: I've tried to replicate your issue with Hive 3.1.2, and was
unable to do so through Beeline. See below for my attempt. I'm going to try the
more complex original example you included above, and see if that also
reproduces it. In the meantime, can you verify the Hive version that reproduced
this issue, and whether my DDLs were correct? Also, can you verify you were
using Beeline, and if not, what client were you using? Thanks!
!image-2020-03-13-11-02-40-949.png!
!image-2020-03-13-11-02-16-173.png!
!image-2020-03-13-11-02-00-748.png!
!image-2020-03-13-11-01-36-250.png!
!image-2020-03-13-11-01-11-118.png!
> 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
(v8.3.4#803005)
