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

          Transition Time In Source Status Execution Times Last Executer Last Execution Date
          Open Open Resolved Resolved
          2h 34m 1 Zheng Shao 20/Aug/09 04:37
          Resolved Resolved Closed Closed
          848d 20h 28m 1 Carl Steinbach 17/Dec/11 00:06
          Carl Steinbach made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          Namit Jain made changes -
          Link This issue is related to HIVE-778 [ HIVE-778 ]
          Namit Jain made changes -
          Link This issue relates to HIVE-778 [ HIVE-778 ]
          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.
          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.
          Zheng Shao made changes -
          Link This issue is related to HIVE-776 [ HIVE-776 ]
          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 ]
          Hide
          Zheng Shao added a comment -

          Committed. Thanks Ning!

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

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Development