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
