[DRILL-3214] Config option to cast empty string to null does not cast empty string to null

• 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

Environment

faec150598840c40827e6493992d81209aa936da

• Assignee: Vitalii Diravka
• Reporter: Khurram Faraaz
• Created: 29/May/15 01:33
• Updated: 09/Nov/18 14:44
• Resolved: 11/Oct/18 14:57

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)

Issue Links

• relates to: DRILL-3259 cast_empty_string_to_null only works when set at SYSTEM level
• relates to: DRILL-3363 Give user exception when users are trying to set system-wide-variable with "alter session set"