Uploaded image for project: 'Hive'
  1. Hive
  2. HIVE-20232

Basic division operator not working for select statement with join

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Blocker
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Operators
    • Labels:
      None

      Description

      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 
      5279 2018-07-01 2018-05-30 32 11158  10 
      5279 2018-07-01 2018-06-02  29  11158 
      5279 2018-07-02 2018-06-29  11158 

       

      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

       

      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

       

       

       

       

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              mike_b_lee Michael Lee
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated: