[ https://issues.apache.org/jira/browse/DRILL-7195?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Volodymyr Vysotskyi updated DRILL-7195: --------------------------------------- Priority: Major (was: Blocker) > 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 > Priority: Major > Fix For: 1.16.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)