[
https://issues.apache.org/jira/browse/CALCITE-7722?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18106083#comment-18106083
]
Julian Hyde commented on CALCITE-7722:
--------------------------------------
It is noteworthy that "WHERE false AND (a/b) > 0" returns no rows. The Postgres
optimizer (like Calcite) is [at liberty to reorder
AND|https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-EXPRESS-EVAL],
so that expression might also throw. If you want certainty, use CASE.
> RexSimplify IS [NOT] NULL on a safe operator with Strong policy ANY and
> unsafe operands can be further simplified
> -----------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-7722
> URL: https://issues.apache.org/jira/browse/CALCITE-7722
> Project: Calcite
> Issue Type: Improvement
> Reporter: Thomas Rebele
> Assignee: Ruben Q L
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.43.0
>
>
> RexSimplify.simplifyIsNotNull / simplifyIsNull currently bail out of the
> whole simplification when the input RexCall is not fully safe (i.e.
> isSafeExpression(a) == false). This is stricter than necessary for operators
> with Strong.Policy.ANY, where IS [NOT] NULL(f(a, b, ...)) is semantically
> equivalent to IS [NOT] NULL(a) OR/AND IS [NOT] NULL(b) OR/AND ... — the
> operator itself does not need to be evaluated to compute the result.
> Example (regression for downstream projects such as Hive):
> {code}
> Before (≤ 1.34):
> IS NOT NULL(CAST(myStr AS DOUBLE) + 1.0) → IS NOT NULL(CAST(myStr AS DOUBLE))
> After (≥ 1.35):
> IS NOT NULL(CAST(myStr AS DOUBLE) + 1.0) → (unchanged)
> {code}
> The rewrite is dropped because CAST(key AS DOUBLE) + 1.0 is a non-lossless
> cast wrapped in a +, so isSafeExpression returns false, even though + is
> Strong.ANY and the distribution is a valid rewrite regardless of the outer
> call's safety.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
