Re: NumberFormatException with cast to double?

2016-03-13 Thread Hsuan Yi Chu
If you like to treat "" as NULL, please set this option "drill.exec.functions.cast_empty_string_to_null" as true in the system level. Meaning, alter system set `drill.exec.functions.cast_empty_string_to_null` = true On Sun, Mar 13, 2016 at 3:53 PM, Matt wrote: > That does

Re: NumberFormatException with cast to double?

2016-03-13 Thread Matt
That does indeed remove the NumberFormatException, replacing it with the same error on "empty String". As indicated by Jason in this thread I may have mistakenly believed that empty columns would be treated as NULLs. Is there a setting to set the NULL value in the data, or should all of

Re: NumberFormatException with cast to double?

2016-03-10 Thread Abdel Hakim Deneche
Looks like the COALESCE function is the source of the problem. Passing a double (0.0) instead of an int (0) as a second expression solved the problem for me: CAST(COALESCE(t_total, 0.0) AS double) On Fri, Mar 11, 2016 at 12:45 AM, Matt wrote: > ~~~ > 00-01

Re: NumberFormatException with cast to double?

2016-03-10 Thread Matt
~~~ 00-01 Project(date_tm=[CAST($23):TIMESTAMP(0)], id_1=[CAST($11):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], id_2=[CAST($15):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], id_3=[CAST($33):VARCHAR(1) CHARACTER SET "ISO-8859-1"

Re: NumberFormatException with cast to double?

2016-03-10 Thread Hsuan Yi Chu
>From the log, I see this one: StringFunctionHelpers.varCharToInt(): It seems like there might be an implicit casting (to integer) be inserted into your expression. Can you do EXPLAIN PLAN FOR "your query" (not double quoted needed) and share what you see? On Thu, Mar 10, 2016 at 3:11 PM,

Re: NumberFormatException with cast to double?

2016-03-10 Thread Matt
TRIM() did not help, and I visually verified there is no whitespace around the number: The entire test data file: ~~~

Re: NumberFormatException with cast to double?

2016-03-10 Thread Jason Altekruse
Could you try throwing a trim() in before the cast? Might just be whitespace. There shouldn't be a need to add the COALESCE statement, as we only ever read defined values (which may be empty string) out of a CSV file. You should instead write out a full case statement that checks for empty string