Apache Drill
  1. Apache Drill
  2. DRILL-910

to_number returns a float when when integer is expected.

    Details

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

      Description

      git.commit.id.abbrev=8490d74
      git.build.time=02.06.2014 @ 10\:11\:56 PDT

      0: jdbc:drill:schema=dfs> select to_number('$987,966', '$###,###') from voter where voter_id=10;
      ------------

      EXPR$0

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

      987966.0

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

      0: jdbc:drill:schema=dfs> select to_number('2,900.55', '#,###') from voter where voter_id=10;
      ------------

      EXPR$0

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

      2901.0

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

      In the first example, an int is passed to_number so we expect an in to return. In the second example, the format in the second argument is to return an int.
      In both cases, postgres and oracle return an int.

        Activity

        Hide
        Cliff Buchanan added a comment -

        Just for clarification, to_number always has the same return type--a numeric. When the format specifier has no fractional part, the numeric with be scale 0 and will not display a decimal point.

        example behavior:

        rbuchanan=# select * from bar;
                foo        |     bar     
        -------------------+-------------
         12,454.987654321- | 999G999S
         12,454.987654321- | 999G999D99S
        (2 rows)
        
        rbuchanan=# select to_number(foo, bar) into results from bar;
        SELECT 2
        rbuchanan=# \d results
             Table "public.results"
          Column   |  Type   | Modifiers 
        -----------+---------+-----------
         to_number | numeric | 
        
        rbuchanan=# select * from results;
         to_number 
        -----------
              1254
           1254.98
        (2 rows)
        
        Show
        Cliff Buchanan added a comment - Just for clarification, to_number always has the same return type--a numeric. When the format specifier has no fractional part, the numeric with be scale 0 and will not display a decimal point. example behavior: rbuchanan=# select * from bar; foo | bar -------------------+------------- 12,454.987654321- | 999G999S 12,454.987654321- | 999G999D99S (2 rows) rbuchanan=# select to_number(foo, bar) into results from bar; SELECT 2 rbuchanan=# \d results Table " public .results" Column | Type | Modifiers -----------+---------+----------- to_number | numeric | rbuchanan=# select * from results; to_number ----------- 1254 1254.98 (2 rows)
        Hide
        Jacques Nadeau added a comment -

        The problem here is that this requires us binding to different function implementations and data types depending on the format string. We do this as part of the CONVERT_TO/CONVERT_FROM functions but nowhere else. As such, we don't have a generalized infrastructure to support this behavior. Moving to future.

        Show
        Jacques Nadeau added a comment - The problem here is that this requires us binding to different function implementations and data types depending on the format string. We do this as part of the CONVERT_TO/CONVERT_FROM functions but nowhere else. As such, we don't have a generalized infrastructure to support this behavior. Moving to future.

          People

          • Assignee:
            Daniel Barclay (Drill)
            Reporter:
            Krystal
          • Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

            • Created:
              Updated:

              Development