You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Danny Chen (Jira)" <ji...@apache.org> on 2021/02/23 07:21:00 UTC
[jira] [Comment Edited] (CALCITE-4507) 'a = 0 or a is null' should
not be rewrite to a sarg 'a in (0, null)'
[ https://issues.apache.org/jira/browse/CALCITE-4507?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17288906#comment-17288906 ]
Danny Chen edited comment on CALCITE-4507 at 2/23/21, 7:20 AM:
---------------------------------------------------------------
For the example in CALCITE-4446:
{{Sarg(10, 20, UNKNOWN AS UNKNOWN): x IN (10, 20) → UNKNOWN}}
then, what does {{x IN (10, null)}} return if x is null ? In mysql, it returns null (which i guess comes form an {{UNKNOWN}}). So mysql assumes {{x IN (10, null)}} returns {{UNKNOWN}} when x is null.
The question here is that {{IN}} compares the values using *EQUALS*, it is different with *x is NULL* statement, so i think this case is a little different, which is not a duplicate of CALCITE-4446.
{{x = 0 or x is null}} can never be rewritten to {{x IN(0, null)}} no matter what {{UNKNOWN AS}} value we have.
was (Author: danny0405):
For the example in CALCITE-4446:
{{Sarg(10, 20, UNKNOWN AS UNKNOWN): x IN (10, 20) → UNKNOWN}}
then, what does {{x IN (10, null)}} returns if x is null ? In mysql, it returns null (which i guess comes form an {{UNKNOWN}}). So mysql assumes {{x IN (10, null)}} returns {{UNKNOWN}} when x is null.
The question here is that {{IN}} compares the values using *EQUALS*, it is different with *x is NULL* statement, so i think this case is a little different, which is not a duplicate of CALCITE-4446.
{{x = 0 or x is null}} can never be written to {{x IN(0, null)}} no matter what {{UNKNOWN AS}} value we have.
> 'a = 0 or a is null' should not be rewrite to a sarg 'a in (0, null)'
> ---------------------------------------------------------------------
>
> Key: CALCITE-4507
> URL: https://issues.apache.org/jira/browse/CALCITE-4507
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.26.0
> Reporter: Danny Chen
> Priority: Major
>
> SQL statements:
> {code:sql}
> a = 0 or a is null -- returns true when a is null
> a in (0, null) -- returns null when a is null
> {code}
> have different semantics when {{a}} is null, we should not represent it as a sarg after {{RexSimplify}},
> check this test in {{RexProgramTest}}:
> {code:java}
> @Test void testSimplifyInOr() {
> or(
> gt(vInt(), literal(0)),
> isNull(vInt())),
> ">(?0.int0, 0)");
> }
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)