[jira] [Commented] (DRILL-7195) Query returns incorrect result or does not fail when cast with is null is used in filter condition
[
https://issues.apache.org/jira/browse/DRILL-7195?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16825306#comment-16825306
]
Sorabh Hamirwasia commented on DRILL-7195:
--
Looks like I should have been more explicit. When I say regarding making a
decision to upgrade Calcite version or not thats considering if any bugs in
Calcite 1.19 is causing regressions in Drill, not just postponing the upgrade
based on any bugs.
> 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)
[jira] [Commented] (DRILL-7195) Query returns incorrect result or does not fail when cast with is null is used in filter condition
[
https://issues.apache.org/jira/browse/DRILL-7195?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16825071#comment-16825071
]
Volodymyr Vysotskyi commented on DRILL-7195:
{quote}
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.
{quote}
I disagree with this statement, we should always update Calcite, but ideally,
before every Calcite release, we should verify Calcite that it does not cause
any regressions for Drill. Postponing the upgrade will bring more harm in the
long term perspective.
> 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)
[jira] [Commented] (DRILL-7195) Query returns incorrect result or does not fail when cast with is null is used in filter condition
[
https://issues.apache.org/jira/browse/DRILL-7195?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=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)
[jira] [Commented] (DRILL-7195) Query returns incorrect result or does not fail when cast with is null is used in filter condition
[
https://issues.apache.org/jira/browse/DRILL-7195?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16824287#comment-16824287
]
Volodymyr Vysotskyi commented on DRILL-7195:
[~amansinha100], I've started looking into this issue, and agree that other
commits should be also cherry-picked to fix this issue to avoid failing tests
in Calcite, and as you have pointed, it may cause other problems.
I don't mind documenting the behavior and fixing the issue after the release
(perhaps with Calcite upgrade).
> 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: Blocker
> 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)
[jira] [Commented] (DRILL-7195) Query returns incorrect result or does not fail when cast with is null is used in filter condition
[
https://issues.apache.org/jira/browse/DRILL-7195?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16824243#comment-16824243
]
Aman Sinha commented on DRILL-7195:
---
[~vvysotskyi] regarding this issue, it is worth debating whether or not it
really is a blocker. The first scenario where no error is shown does not seem
like a blocker. The second case with wrong result occurs only when the
cast_empty_string_to_null is set to True, which is not the default setting.
Looking at the history of IS NULL simplification in Calcite, it is somewhat
complex issue and I feel that any fix we do could potentially introduce other
side effects which will prolong the release. Can we document this for 1.16 and
fix it in master ?
> 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: Blocker
> 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)
