Thanks Julian, Benchao,
This is not only a problem about expression simplification, also type
consistency in conversion[1].
As Xiong said in CALCITE-4993
<https://issues.apache.org/jira/browse/CALCITE-4993> , "EQUALS and
NOT-EQUALS comparison. Because they use the same LEAST_RESTRICTIVE
strategy to validate the parameter. Other comparators use the COMPARE
strategy."
This will cause NOT-EQUALS comparison will use different strategy from
other compare operator like LESS THAN to generate literal operand's type.
In my case, if "name" column has a type varchar(256), "name" <> '' and
"name" <> '3' will cause '' and '3' have type varchar(256), but ("name" >
'' or "name" < '') and ("name" > '3' or "name" < '3') will cause '' and '3'
has type char(0) and char(1) respectively.
[1]
https://github.com/apache/calcite/blob/f278efb0411ba29c7bd167f5d02e566bf542acb3/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java#L993
Benchao Li <[email protected]> 于2022年5月29日周日 15:35写道：
> I've filed an issue[1] to track this.
>
> [1] https://issues.apache.org/jira/browse/CALCITE-5169
>
> Benchao Li <[email protected]> 于2022年5月29日周日 11:19写道：
>
> > Hi all,
> >
> > I've confirmed it.
> > The reason why different plans for queries:
> > query 1:
> > select * from "emps" where "name" <> '' and "name" <> '3'
> > query 2:
> > select * from "emps" where ("name" > '' or "name" < '') and ("name" > '3'
> > or "name" < '3')
> > is not from the operator consistency.
> > It's just because the expression: ("name" > '' or "name" < '') and
> ("name"
> > > '3'
> > or "name" < '3') cannot be translated into Sarg for now.
> >
> > I'll file a Jira issue to track and improve this.
> >
> > Benchao Li <[email protected]> 于2022年5月28日周六 09:30写道：
> >
> >> FYI, the issue might be this one:
> >> https://issues.apache.org/jira/browse/CALCITE-4993
> >>
> >> I also looked into this in this direction yesterday, however, I didn't
> >> confirm it yet.
> >> That's why I didn't reply to this email before. I will do further
> >> verifications and
> >> post the result here later.
> >>
> >>
> >>
> >> Julian Hyde <[email protected]> 于2022年5月27日周五 23:52写道：
> >>
> >>> I think there’s a JIRA case for this. The implicit casts prevent SARG
> >>> simplification from kicking in. In SARG representation the expressions
> >>> would be the same. Which is why we love SARGs.
> >>>
> >>> Julian
> >>>
> >>> > On May 26, 2022, at 17:49, Yanjing Wang <[email protected]>
> >>> wrote:
> >>> >
> >>> > Hi community,
> >>> >
> >>> > I have this sql: select * from "emps" where "name" <> '' and "name"
> <>
> >>> '3'
> >>> >
> >>> > I thought it would generate the same plan with
> >>> > select * from "emps" where ("name" > '' or "name" < '') and ("name" >
> >>> '3'
> >>> > or "name" < '3')
> >>> >
> >>> > but not, the not equal operator consistency is different with less
> >>> than and
> >>> > greater than operator,
> >>> >
> >>> > which will cause the literal '' and '3' have different data type in
> >>> plans
> >>> > of the above sqls.
> >>> >
> >>> > That behavior maybe cause some queries will not hit the
> >>> materialization.
> >>> >
> >>> > should we canonize ("name" > '' or "name" < '') and ("name" > '3' or
> >>> > "name" < '3') to not equal or vice versa as RelToSql
> >>> > <
> >>>
> https://github.com/apache/calcite/blob/9bdfd9a178f493b235d8785afd94fd0c998e8cce/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java#L870
> >>> >
> >>> > behaves?
> >>>
> >>
> >>
> >> --
> >>
> >> Best,
> >> Benchao Li
> >>
> >
> >
> > --
> >
> > Best,
> > Benchao Li
> >
>
>
> --
>
> Best,
> Benchao Li
>
