Hive
  1. Hive
  2. HIVE-774

Fix the behavior of "/" and add "DIV"

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 0.4.0, 0.5.0
    • Fix Version/s: 0.4.0, 0.5.0
    • Component/s: None
    • Labels:
      None
    • Hadoop Flags:
      Incompatible change, Reviewed
    • Release Note:
      HIVE-774. Fix the behavior of / and add DIV. (Ning Zhang via zshao)

      Description

      In hive, "select 3/2" will return 1 while MySQL returns 1.5.

      See http://dev.mysql.com/doc/refman/5.0/en/arithmetic-functions.html#operator_div for details.

      mysql> select 3/2;
      +--------+
      | 3/2    |
      +--------+
      | 1.5000 |
      +--------+
      1 row in set (0.00 sec)
      
      mysql> select 3 div 2;
      +---------+
      | 3 div 2 |
      +---------+
      |       1 |
      +---------+
      1 row in set (0.00 sec)
      
      mysql> select -3 div 2;
      +----------+
      | -3 div 2 |
      +----------+
      |       -1 |
      +----------+
      1 row in set (0.00 sec)
      
      mysql> select -3 div -2;
      +-----------+
      | -3 div -2 |
      +-----------+
      |         1 |
      +-----------+
      1 row in set (0.00 sec)
      
      mysql> select 3 div -2;
      +----------+
      | 3 div -2 |
      +----------+
      |       -1 |
      +----------+
      1 row in set (0.00 sec)
      

        Issue Links

          Activity

          Zheng Shao created issue -
          Zheng Shao made changes -
          Field Original Value New Value
          Assignee Ning Zhang [ nzhang ]
          Ning Zhang made changes -
          Attachment hive-774.patch [ 12417092 ]
          Hide
          Zheng Shao added a comment -

          Committed. Thanks Ning!

          Show
          Zheng Shao added a comment - Committed. Thanks Ning!
          Zheng Shao made changes -
          Status Open [ 1 ] Resolved [ 5 ]
          Hadoop Flags [Incompatible change] [Incompatible change, Reviewed]
          Release Note HIVE-774. Fix the behavior of / and add DIV. (Ning Zhang via zshao)
          Fix Version/s 0.4.0 [ 12313714 ]
          Fix Version/s 0.5.0 [ 12314156 ]
          Resolution Fixed [ 1 ]
          Zheng Shao made changes -
          Link This issue is related to HIVE-776 [ HIVE-776 ]
          Hide
          Edward Capriolo added a comment -

          I have not had to deal with many hive precision issues, also I do not really have an opinion on how things should work. But I think we might want to consider deferring to how Java does things rather then how MySQL handles things.
          On one had its nice to handle things like the mysql reference says to handle things. Of course the mysql reference will have from subtle to not so subtle changes between versions.

          While I think it is great to have all the UDF's that mysql may have, I don't think we should always defer to doing something as mysql would, unless mysql is doing it to match some larger SQL standard.

          Show
          Edward Capriolo added a comment - I have not had to deal with many hive precision issues, also I do not really have an opinion on how things should work. But I think we might want to consider deferring to how Java does things rather then how MySQL handles things. On one had its nice to handle things like the mysql reference says to handle things. Of course the mysql reference will have from subtle to not so subtle changes between versions. While I think it is great to have all the UDF's that mysql may have, I don't think we should always defer to doing something as mysql would, unless mysql is doing it to match some larger SQL standard.
          Hide
          Ning Zhang added a comment -

          It's a good point Edward. In fact SQL does not standardize numeric operators such as '/'. Different DBMS vendors may adopt different semantics (e.g, MySQL vs. PostgresSQL). I agree we should be carefully define and document the semantics of these operators since they could be used extensively in analytic queries.

          Show
          Ning Zhang added a comment - It's a good point Edward. In fact SQL does not standardize numeric operators such as '/'. Different DBMS vendors may adopt different semantics (e.g, MySQL vs. PostgresSQL). I agree we should be carefully define and document the semantics of these operators since they could be used extensively in analytic queries.
          Namit Jain made changes -
          Link This issue relates to HIVE-778 [ HIVE-778 ]
          Namit Jain made changes -
          Link This issue is related to HIVE-778 [ HIVE-778 ]
          Carl Steinbach made changes -
          Status Resolved [ 5 ] Closed [ 6 ]

            People

            • Assignee:
              Ning Zhang
              Reporter:
              Zheng Shao
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development