Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-520

ceiling/ceil and floor functions return decimal value instead of an integer

    Details

    • Type: Bug
    • Status: Open
    • Priority: Critical
    • Resolution: Unresolved
    • Affects Version/s: 1.0.0
    • Fix Version/s: Future
    • Component/s: Functions - Drill
    • Labels:
      None

      Description

      Ran the following queries in drill:
      0: jdbc:drill:schema=dfs> select ceiling(55.8) from dfs.`student` where rownum=11;
      ------------

      EXPR$0

      ------------

      56.0

      ------------

      0: jdbc:drill:schema=dfs> select floor(55.8) from dfs.`student` where rownum=11;
      ------------

      EXPR$0

      ------------

      55.0

      ------------

      The same queries executed from oracle, postgres and mysql returned integer values of 56 and 55.

      Found the following description of the two functions from http://users.atw.hu/sqlnut/sqlnut2-chp-4-sect-4.html :

      Ceil/Ceiling:
      Rounds a noninteger value upwards to the next greatest integer. Returns an integer value unchanged.
      Floor:
      Rounds a noninteger value downwards to the next least integer. Returns an integer value unchanged.

        Issue Links

          Activity

          Hide
          knguyen Krystal added a comment -

          Tried the tests on the latest 1.10 master and the results are still the same as originally reported.

          Show
          knguyen Krystal added a comment - Tried the tests on the latest 1.10 master and the results are still the same as originally reported.
          Hide
          kkhatua Kunal Khatua added a comment -

          Krystal Can this bug be verified and closed? It looks like this was not applied. Assign it to Jinfeng Ni, if we don't see the fix.

          Show
          kkhatua Kunal Khatua added a comment - Krystal Can this bug be verified and closed? It looks like this was not applied. Assign it to Jinfeng Ni , if we don't see the fix.
          Hide
          yash360@gmail.com Yash Sharma added a comment -

          It is safer to return the datatype type similar to that of passed input type to avoid loss of data on conversion to long/int.
          Fix: Future

          Show
          yash360@gmail.com Yash Sharma added a comment - It is safer to return the datatype type similar to that of passed input type to avoid loss of data on conversion to long/int. Fix: Future
          Hide
          yash360@gmail.com Yash Sharma added a comment -

          Added patch for bugfix. Please verify.

          Show
          yash360@gmail.com Yash Sharma added a comment - Added patch for bugfix. Please verify.
          Hide
          jni Jinfeng Ni added a comment -

          I will provide a fix to change the target type for ceil/floor function to integer type.

          Show
          jni Jinfeng Ni added a comment - I will provide a fix to change the target type for ceil/floor function to integer type.

            People

            • Assignee:
              jni Jinfeng Ni
              Reporter:
              knguyen Krystal
              Reviewer:
              Krystal
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:

                Development