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

Config option to cast empty string to null does not cast empty string to null

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Won't Fix
    • Affects Version/s: 1.0.0
    • Fix Version/s: 1.0.0
    • Component/s: Functions - Drill
    • Labels:
      None
    • Environment:

      faec150598840c40827e6493992d81209aa936da

      Description

      Config option drill.exec.functions.cast_empty_string_to_null does not seem to be working as designed.

      Disable casting of empty strings to null.

      0: jdbc:drill:schema=dfs.tmp> alter session set `drill.exec.functions.cast_empty_string_to_null` = false;
      +-------+----------------------------------------------------------+
      |  ok   |                         summary                          |
      +-------+----------------------------------------------------------+
      | true  | drill.exec.functions.cast_empty_string_to_null updated.  |
      +-------+----------------------------------------------------------+
      1 row selected (0.078 seconds)
      

      In this query we see empty strings are retained in query output in columns[1].

      0: jdbc:drill:schema=dfs.tmp> SELECT columns[0], columns[1], columns[2] from `threeColsDouble.csv`;
      +----------+---------+---------+
      |  EXPR$0  | EXPR$1  | EXPR$2  |
      +----------+---------+---------+
      | 156      | 234     | 12222   |
      | 2653543  | 434     | 0       |
      | 367345   | 567567  | 23      |
      | 34554    | 1234    | 45      |
      | 4345     | 567678  | 19876   |
      | 34556    | 0       | 1109    |
      | 5456     | -1      | 1098    |
      | 6567     |         | 34534   |
      | 7678     | 1       | 6       |
      | 8798     | 456     | 243     |
      | 265354   | 234     | 123     |
      | 367345   |         | 234     |
      | 34554    | 1       | 2       |
      | 4345     | 0       | 10      |
      | 34556    | -1      | 19      |
      | 5456     | 23423   | 345     |
      | 6567     | 0       | 2348    |
      | 7678     | 1       | 2       |
      | 8798     |         | 45      |
      | 099      | 19      | 17      |
      +----------+---------+---------+
      20 rows selected (0.13 seconds)
      

      Casting empty strings to integer leads to NumberFormatException

      0: jdbc:drill:schema=dfs.tmp> SELECT columns[0], cast(columns[1] as int), columns[2] from `threeColsDouble.csv`;
      Error: SYSTEM ERROR: java.lang.NumberFormatException: 
      
      Fragment 0:0
      
      [Error Id: b08f4247-263a-460d-b37b-91a70375f7ba on centos-03.qa.lab:31010] (state=,code=0)
      

      Enable casting empty string to null.

      0: jdbc:drill:schema=dfs.tmp> alter session set `drill.exec.functions.cast_empty_string_to_null` = true;
      +-------+----------------------------------------------------------+
      |  ok   |                         summary                          |
      +-------+----------------------------------------------------------+
      | true  | drill.exec.functions.cast_empty_string_to_null updated.  |
      +-------+----------------------------------------------------------+
      1 row selected (0.077 seconds)
      

      Run query

      0: jdbc:drill:schema=dfs.tmp> SELECT columns[0], cast(columns[1] as int), columns[2] from `threeColsDouble.csv`;
      Error: SYSTEM ERROR: java.lang.NumberFormatException: 
      
      Fragment 0:0
      
      [Error Id: de633399-15f9-4a79-a21f-262bd5551207 on centos-03.qa.lab:31010] (state=,code=0)
      

      Note from the output of below query that the empty strings are not casted to null, although drill.exec.functions.cast_empty_string_to_null was set to true.

      0: jdbc:drill:schema=dfs.tmp> SELECT columns[0], columns[1], columns[2] from `threeColsDouble.csv`;
      +----------+---------+---------+
      |  EXPR$0  | EXPR$1  | EXPR$2  |
      +----------+---------+---------+
      | 156      | 234     | 12222   |
      | 2653543  | 434     | 0       |
      | 367345   | 567567  | 23      |
      | 34554    | 1234    | 45      |
      | 4345     | 567678  | 19876   |
      | 34556    | 0       | 1109    |
      | 5456     | -1      | 1098    |
      | 6567     |         | 34534   |
      | 7678     | 1       | 6       |
      | 8798     | 456     | 243     |
      | 265354   | 234     | 123     |
      | 367345   |         | 234     |
      | 34554    | 1       | 2       |
      | 4345     | 0       | 10      |
      | 34556    | -1      | 19      |
      | 5456     | 23423   | 345     |
      | 6567     | 0       | 2348    |
      | 7678     | 1       | 2       |
      | 8798     |         | 45      |
      | 099      | 19      | 17      |
      +----------+---------+---------+
      20 rows selected (0.125 seconds)
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                vitalii Vitalii Diravka
                Reporter:
                khfaraaz Khurram Faraaz
              • Votes:
                2 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: