>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, Matt <bsg...@gmail.com> wrote: > TRIM() did not help, and I visually verified there is no whitespace around > the number: The entire test data file: > > ~~~ > > date_tm,id_1,id_2,id_3,b_total,t_total,h_total,b_small,t_small,h_small,b_18000,t_18000,h_18000,b_12000,t_12000,h_12000,b_6000,t_6000,h_6000,b_3000,t_3000,h_3000,b_2000,t_2000,h_2000,b_1500,t_1500,h_1500,b_1250,t_1250,h_1250,b_1000,t_1000,h_1000,b_750,t_750,h_750,b_500,t_500,h_500,b_0,t_0,h_0 > 2015-10-17 > 00:00:00,f5e9v8u2,err,mi1,268918254,140.755,23519,268918254,140.755,23519,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, > ~~~ > > Including a trim resulted in the same: > > ~~~ > Error: SYSTEM ERROR: NumberFormatException: 140.755 > > Fragment 0:0 > > [Error Id: 01d368ee-6c06-476c-a553-5eb5baea7c3f on es07:31010] > > (java.lang.NumberFormatException) 140.755 > org.apache.drill.exec.expr.fn.impl.StringFunctionHelpers.nfeI():95 > > org.apache.drill.exec.expr.fn.impl.StringFunctionHelpers.varCharToInt():120 > org.apache.drill.exec.test.generated.ProjectorGen122.doEval():467 > > org.apache.drill.exec.test.generated.ProjectorGen122.projectRecords():62 > > org.apache.drill.exec.physical.impl.project.ProjectRecordBatch.doWork():175 > org.apache.drill.exec.record.AbstractSingleRecordBatch.innerNext():93 > > org.apache.drill.exec.physical.impl.project.ProjectRecordBatch.innerNext():132 > org.apache.drill.exec.record.AbstractRecordBatch.next():162 > org.apache.drill.exec.physical.impl.BaseRootExec.next():104 > > org.apache.drill.exec.physical.impl.ScreenCreator$ScreenRoot.innerNext():81 > org.apache.drill.exec.physical.impl.BaseRootExec.next():94 > org.apache.drill.exec.work.fragment.FragmentExecutor$1.run():256 > org.apache.drill.exec.work.fragment.FragmentExecutor$1.run():250 > java.security.AccessController.doPrivileged():-2 > javax.security.auth.Subject.doAs():415 > org.apache.hadoop.security.UserGroupInformation.doAs():1657 > org.apache.drill.exec.work.fragment.FragmentExecutor.run():250 > org.apache.drill.common.SelfCleaningRunnable.run():38 > java.util.concurrent.ThreadPoolExecutor.runWorker():1145 > java.util.concurrent.ThreadPoolExecutor$Worker.run():615 > java.lang.Thread.run():745 (state=,code=0) > ~~~ > > On 10 Mar 2016, at 17:49, Jason Altekruse wrote: > > 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 >> and provides your default value of 0 in that case. >> >> - Jason >> >> Jason Altekruse >> Software Engineer at Dremio >> Apache Drill Committer >> >> On Thu, Mar 10, 2016 at 2:32 PM, Matt <bsg...@gmail.com> wrote: >> >> Have some CSV data that Drill 1.5 selects as-is without any problems, >>> until I attempt to CAST columns in a CTAS or plain SELECT: >>> >>> Error: SYSTEM ERROR: NumberFormatException: 140.755 >>> >>> The data is unquoted CSV, and column in question does have the value >>> "140.755" (unquoted). As the column can be empty, I am using the >>> following >>> transform: >>> >>> CAST(COALESCE(t_total, 0) AS double) AS t_total >>> >>> And on the first data row: >>> >>> Fragment 1:0 >>> >>> [Error Id: 4b4d83cf-a87d-451e-9cfa-4280e5adf64f on es08:31010] >>> >>> (java.lang.NumberFormatException) 140.755 >>> >>> What could be causing this error? >>> >>> >>>
