drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jason Altekruse (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DRILL-1430) sum(cast(columns[0] as int)) fails if column contains empty value
Date Wed, 17 Sep 2014 19:08:33 GMT

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

Jason Altekruse commented on DRILL-1430:
----------------------------------------

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)

Mime
View raw message