[
https://issues.apache.org/jira/browse/DRILL-7195?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16824314#comment-16824314
]
Sorabh Hamirwasia commented on DRILL-7195:
------------------------------------------
Since the issue is on Calcite side the correct way to fix will be to get from
Calcite itself. Looks like there are [multiple issues reported on 1.18 which
were fixed in
1.19|https://issues.apache.org/jira/issues/?jql=project%20%3D%20CALCITE%20AND%20issuetype%20%3D%20Bug%20AND%20affectedVersion%20%3D%201.18.0%20AND%20fixVersion%20%3D%201.19.0%20ORDER%20BY%20created%20DESC]
Calcite. If we are planning an upgrade to 1.19 in Drill 1.17 timeframe, it
would be helpful to track such bugs in 1.19 branch and make an early decision
whether to upgrade to 1.19 or not.
Thanks for discussing and resolving this issue. [~vvysotskyi] - Can you please
work with [~bbevens] to document the limitation
> Query returns incorrect result or does not fail when cast with is null is
> used in filter condition
> --------------------------------------------------------------------------------------------------
>
> Key: DRILL-7195
> URL: https://issues.apache.org/jira/browse/DRILL-7195
> Project: Apache Drill
> Issue Type: Bug
> Affects Versions: 1.16.0
> Reporter: Volodymyr Vysotskyi
> Assignee: Volodymyr Vysotskyi
> Priority: Major
> Fix For: 1.17.0
>
>
> 1. For the case when a query contains filter with a {{cast}} which cannot be
> done with {{is null}}, the query does not fail:
> {code:sql}
> select * from dfs.tmp.`a.json` as t where cast(t.a as integer) is null;
> +---+
> | a |
> +---+
> +---+
> No rows selected (0.142 seconds)
> {code}
> where
> {noformat}
> cat /tmp/a.json
> {"a":"aaa"}
> {noformat}
> But for the case when this condition is specified in project, query, as it is
> expected, fails:
> {code:sql}
> select cast(t.a as integer) is null from dfs.tmp.`a.json` t;
> Error: SYSTEM ERROR: NumberFormatException: aaa
> Fragment 0:0
> Please, refer to logs for more information.
> [Error Id: ed3982ce-a12f-4d63-bc6e-cafddf28cc24 on user515050-pc:31010]
> (state=,code=0)
> {code}
> This is a regression, for Drill 1.15 the first and the second queries are
> failed:
> {code:sql}
> select * from dfs.tmp.`a.json` as t where cast(t.a as integer) is null;
> Error: SYSTEM ERROR: NumberFormatException: aaa
> Fragment 0:0
> Please, refer to logs for more information.
> [Error Id: 2f878f15-ddaa-48cd-9dfb-45c04db39048 on user515050-pc:31010]
> (state=,code=0)
> {code}
> 2. For the case when {{drill.exec.functions.cast_empty_string_to_null}} is
> enabled, this issue will cause wrong results:
> {code:sql}
> alter system set `drill.exec.functions.cast_empty_string_to_null`=true;
> select * from dfs.tmp.`a1.json` t where cast(t.a as integer) is null;
> +---+
> | a |
> +---+
> +---+
> No rows selected (1.759 seconds)
> {code}
> where
> {noformat}
> cat /tmp/a1.json
> {"a":"1"}
> {"a":""}
> {noformat}
> Result for Drill 1.15.0:
> {code:sql}
> select * from dfs.tmp.`a1.json` t where cast(t.a as integer) is null;
> +----+
> | a |
> +----+
> | |
> +----+
> 1 row selected (1.724 seconds)
> {code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
