You are viewing a plain text version of this content. The canonical link for it is here.

Posted to dev@calcite.apache.org by Benchao Li <li...@apache.org> on 2022/03/03 00:52:11 UTC

Re: [Discuss] The RexSimplify about "a <> 1 and a = 2"

Hi xiong,

For the simplification result below:
# "a <> 1 or a = 1 [a is nullable]"
1. UNKNOWN AS UNKNOWN: OR(IS NOT NULL(a), null)
2. UNKNOWN AS TRUE: TRUE
3. UNKNOWN AS FALSE: IS NOT NULL(a)

# "a<>1 and a = 2 [a is nullable]"
1. UNKNOWN AS UNKNOWN: =(a, '2')
2. UNKNOWN AS TRUE: =(a, '2')
3. UNKNOWN AS FALSE: =(a, '2')

IMHO, this is expected and correct.
For the first case, the result can be simplified to:
if a is null:
  null
else:
  true
then, for each unknown value, we can evaluate to the results above
correspondingly.

For the second case, the result is also correct, we can simplify `a<>1 and
a = 2` to `a = 2` regardless of nullability of a,
and `=(a, '2')` is the simplest form.

xiong duan <no...@gmail.com> 于2022年2月28日周一 10:26写道：

> Hi followers.
>     When I learned how to simplify the Rexnode in Calcite. I meet a problem
> and need some help.
> For example:
> When we simplify  "a <> 1 or a = 1 [a is nullable]", (the result is
> different according to how to treat the UNKNOW value) :
>
> checkSimplify3_(        or(ne(vInt(0), literal(1)),        eq(vInt(0),
> literal(1))),        "OR(IS NOT NULL(?0.int0), null)",        "IS NOT
> NULL(?0.int0)",        "true");
>
> But when I try to simplify "a<>1 and a = 2 [a is nullable]":
>
>     checkSimplify3_(
>             and(ne(vInt(0), literal(1)),
>                     eq(vInt(0), literal(2))),
>             "=(?0.int0, 2)",
>             "=(?0.int0, 2)",
>             "=(?0.int0, 2)");
>
> And I think this result should be:
>  "a<>1 and a = 2 [a is nullable]":
>
> UNKNOW AS TRUE: a IS NULL or a = 2
> UNKNOW AS FALSE: a is NOT NULL and a =2
> UNKNOW AS UNKNOW: CASE(IS NULL(a), null, a = 2)
>
> So What did I misunderstand? It is a bug or intentional? Any feedback would
> be appreciated.
>


-- 

Best,
Benchao Li

Re: [Discuss] The RexSimplify about "a <> 1 and a = 2"

Posted by xiong duan <no...@gmail.com>.

Hi, Followers.
      Thanks for the feedback. Yes, I have a misunderstanding about this.
when I try to simplify "a<>1 and a = 2 [a is nullable]":

    checkSimplify3_(
            and(ne(vInt(0), literal(1)),
                    eq(vInt(0), literal(2))),
            "=(?0.int0, 2)",
            "=(?0.int0, 2)",
            "=(?0.int0, 2)");

And I think this result should be:
 "a<>1 and a = 2 [a is nullable]":

UNKNOW AS TRUE: a IS NULL or a = 2
UNKNOW AS FALSE: a is NOT NULL and a =2
UNKNOW AS UNKNOWN: CASE(IS NULL(a), null, a = 2)

This is wrong. Because I omit the a is still nullable after the
optimization.


Julian Hyde <jh...@gmail.com> 于2022年3月3日周四 17:23写道：

> Note that https://issues.apache.org/jira/browse/CALCITE-1794 <
> https://issues.apache.org/jira/browse/CALCITE-1794> deals with very
> similar expressions (albeit with CAST, and optimized using SARGs rather
> than RexSimplify) and was just fixed today.
>
> > On Mar 2, 2022, at 4:52 PM, Benchao Li <li...@apache.org> wrote:
> >
> > Hi xiong,
> >
> > For the simplification result below:
> > # "a <> 1 or a = 1 [a is nullable]"
> > 1. UNKNOWN AS UNKNOWN: OR(IS NOT NULL(a), null)
> > 2. UNKNOWN AS TRUE: TRUE
> > 3. UNKNOWN AS FALSE: IS NOT NULL(a)
> >
> > # "a<>1 and a = 2 [a is nullable]"
> > 1. UNKNOWN AS UNKNOWN: =(a, '2')
> > 2. UNKNOWN AS TRUE: =(a, '2')
> > 3. UNKNOWN AS FALSE: =(a, '2')
> >
> > IMHO, this is expected and correct.
> > For the first case, the result can be simplified to:
> > if a is null:
> >  null
> > else:
> >  true
> > then, for each unknown value, we can evaluate to the results above
> > correspondingly.
> >
> > For the second case, the result is also correct, we can simplify `a<>1
> and
> > a = 2` to `a = 2` regardless of nullability of a,
> > and `=(a, '2')` is the simplest form.
> >
> > xiong duan <no...@gmail.com> 于2022年2月28日周一 10:26写道：
> >
> >> Hi followers.
> >>    When I learned how to simplify the Rexnode in Calcite. I meet a
> problem
> >> and need some help.
> >> For example:
> >> When we simplify  "a <> 1 or a = 1 [a is nullable]", (the result is
> >> different according to how to treat the UNKNOW value) :
> >>
> >> checkSimplify3_(        or(ne(vInt(0), literal(1)),        eq(vInt(0),
> >> literal(1))),        "OR(IS NOT NULL(?0.int0), null)",        "IS NOT
> >> NULL(?0.int0)",        "true");
> >>
> >> But when I try to simplify "a<>1 and a = 2 [a is nullable]":
> >>
> >>    checkSimplify3_(
> >>            and(ne(vInt(0), literal(1)),
> >>                    eq(vInt(0), literal(2))),
> >>            "=(?0.int0, 2)",
> >>            "=(?0.int0, 2)",
> >>            "=(?0.int0, 2)");
> >>
> >> And I think this result should be:
> >> "a<>1 and a = 2 [a is nullable]":
> >>
> >> UNKNOW AS TRUE: a IS NULL or a = 2
> >> UNKNOW AS FALSE: a is NOT NULL and a =2
> >> UNKNOW AS UNKNOW: CASE(IS NULL(a), null, a = 2)
> >>
> >> So What did I misunderstand? It is a bug or intentional? Any feedback
> would
> >> be appreciated.
> >>
> >
> >
> > --
> >
> > Best,
> > Benchao Li
>
>

Re: [Discuss] The RexSimplify about "a <> 1 and a = 2"

Posted by Julian Hyde <jh...@gmail.com>.

Note that https://issues.apache.org/jira/browse/CALCITE-1794 <https://issues.apache.org/jira/browse/CALCITE-1794> deals with very similar expressions (albeit with CAST, and optimized using SARGs rather than RexSimplify) and was just fixed today.

> On Mar 2, 2022, at 4:52 PM, Benchao Li <li...@apache.org> wrote:
> 
> Hi xiong,
> 
> For the simplification result below:
> # "a <> 1 or a = 1 [a is nullable]"
> 1. UNKNOWN AS UNKNOWN: OR(IS NOT NULL(a), null)
> 2. UNKNOWN AS TRUE: TRUE
> 3. UNKNOWN AS FALSE: IS NOT NULL(a)
> 
> # "a<>1 and a = 2 [a is nullable]"
> 1. UNKNOWN AS UNKNOWN: =(a, '2')
> 2. UNKNOWN AS TRUE: =(a, '2')
> 3. UNKNOWN AS FALSE: =(a, '2')
> 
> IMHO, this is expected and correct.
> For the first case, the result can be simplified to:
> if a is null:
>  null
> else:
>  true
> then, for each unknown value, we can evaluate to the results above
> correspondingly.
> 
> For the second case, the result is also correct, we can simplify `a<>1 and
> a = 2` to `a = 2` regardless of nullability of a,
> and `=(a, '2')` is the simplest form.
> 
> xiong duan <no...@gmail.com> 于2022年2月28日周一 10:26写道：
> 
>> Hi followers.
>>    When I learned how to simplify the Rexnode in Calcite. I meet a problem
>> and need some help.
>> For example:
>> When we simplify  "a <> 1 or a = 1 [a is nullable]", (the result is
>> different according to how to treat the UNKNOW value) :
>> 
>> checkSimplify3_(        or(ne(vInt(0), literal(1)),        eq(vInt(0),
>> literal(1))),        "OR(IS NOT NULL(?0.int0), null)",        "IS NOT
>> NULL(?0.int0)",        "true");
>> 
>> But when I try to simplify "a<>1 and a = 2 [a is nullable]":
>> 
>>    checkSimplify3_(
>>            and(ne(vInt(0), literal(1)),
>>                    eq(vInt(0), literal(2))),
>>            "=(?0.int0, 2)",
>>            "=(?0.int0, 2)",
>>            "=(?0.int0, 2)");
>> 
>> And I think this result should be:
>> "a<>1 and a = 2 [a is nullable]":
>> 
>> UNKNOW AS TRUE: a IS NULL or a = 2
>> UNKNOW AS FALSE: a is NOT NULL and a =2
>> UNKNOW AS UNKNOW: CASE(IS NULL(a), null, a = 2)
>> 
>> So What did I misunderstand? It is a bug or intentional? Any feedback would
>> be appreciated.
>> 
> 
> 
> -- 
> 
> Best,
> Benchao Li