[jira] [Comment Edited] (DRILL-1430) sum(cast(columns[0] as int)) fails if column contains empty value

["Jason Altekruse (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DRILL-1430?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14137765#comment-14137765 ] 

Jason Altekruse edited comment on DRILL-1430 at 9/17/14 7:09 PM:
-----------------------------------------------------------------

This is expected behavior. If you want to interpret empty string as 0 or null you currently need to use a case statement. We are considering adding an option to enable these types of conversions implicitly to allow cleaner queries, but this behavior is consistent with the SQL standard, as an empty string is not a valid number.


was (Author: jaltekruse):
This is expected behavior. If you want to interpret empty string as 0 or null you currently need to use a case statement. We are considering adding an option to enable these types of conversions implicitly to allow cleaner queries, but this behavior is consistent with the SQL standard empty string is not a valid number.

> sum(cast(columns[0] as int)) fails if column contains empty value
> -----------------------------------------------------------------
>
>                 Key: DRILL-1430
>                 URL: https://issues.apache.org/jira/browse/DRILL-1430
>             Project: Apache Drill
>          Issue Type: Bug
>          Components: Execution - Data Types
>    Affects Versions: 0.6.0
>            Reporter: Chun Chang
>
> code base
> #Fri Sep 12 14:08:02 PDT 2014
> git.commit.id.abbrev=9e16466
> I have a simple tsv file that contains an empty row in one of the columns:
> [root@qa-node120 tmp]# hadoop fs -cat /user/root/mondrian/tmp.tbl
> 1|1
> 2|2
> |3
> 4|4
> 0: jdbc:drill:schema=dfs> select * from `tmp.tbl`;
> +------------+
> |  columns   |
> +------------+
> | ["1","1"]  |
> | ["2","2"]  |
> | ["","3"]   |
> | ["4","4"]  |
> +------------+
> 4 rows selected (0.183 seconds)
> 0: jdbc:drill:schema=dfs> select columns[0], columns[1] from `tmp.tbl`;
> +------------+------------+
> |   EXPR$0   |   EXPR$1   |
> +------------+------------+
> | 1          | 1          |
> | 2          | 2          |
> |            | 3          |
> | 4          | 4          |
> +------------+------------+
> 4 rows selected (0.217 seconds)
> sum over the column that is full works fine, but sum over the column that contains empty row fails:
> 0: jdbc:drill:schema=dfs> select sum(cast(columns[1] as int)) from `tmp.tbl`;
> +------------+
> |   EXPR$0   |
> +------------+
> | 10         |
> +------------+
> 1 row selected (0.283 seconds)
> 0: jdbc:drill:schema=dfs> select sum(cast(columns[0] as int)) from `tmp.tbl`;
> Query failed: Failure while running fragment.  [a9d8771f-aeb3-4226-9623-fc2b635365f8]
> Error: exception while executing query: Failure while trying to get next result batch. (state=,code=0)
> 0: jdbc:drill:schema=dfs>
> drillbit.log
> 11:44:19.450 [9d14d90c-128c-4549-b77f-9b0051bc3d5c:frag:0:0] DEBUG o.a.d.e.w.fragment.FragmentExecutor - Error while initializing or executing fragment
> java.lang.NumberFormatException:
>         at org.apache.drill.exec.test.generated.ProjectorGen18.doEval(ProjectorTemplate.java:40) ~[na:na]
>         at org.apache.drill.exec.test.generated.ProjectorGen18.projectRecords(ProjectorTemplate.java:64) ~[na:na]



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)