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

          Hide
          Zheng Shao added a comment -

          Committed. Thanks Ning!

          Show
          Zheng Shao added a comment - Committed. Thanks Ning!
          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.

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Development