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
