Hi Yanjing, The type derivation is different in this case indeed. However, after my fix[1], they both can be optimized to Sarg, and they have the same plan structure. (I've added tests to show this, welcome review)
In your case, if you have some different handling logic in later steps, maybe you can share with us. [1] https://github.com/apache/calcite/pull/2821 Yanjing Wang <zhuangzixiao...@gmail.com> 于2022年5月30日周一 12:14写道： > 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 <libenc...@apache.org> 于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 <libenc...@apache.org> 于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 <libenc...@apache.org> 于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 <jhyde.apa...@gmail.com> 于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 <zhuangzixiao...@gmail.com > > > > >>> 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 > > > -- Best, Benchao Li
