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?
