Hi Yanjing, Your test case passed on my branch. Can you try it with my improvement in https://github.com/apache/calcite/pull/2821?
Yanjing Wang <[email protected]> 于2022年5月31日周二 16:06写道： > Hi, Benchao, > > The pr is good, and I noticed that the two sarg types is not same. > > Add the following test in MaterializedViewSubstitutionVisitorTest, you will > see the substitution fails and sarg types is not same. > > @Test void testFilter2() { > sql("select * from \"EMP\" where (\"ENAME\" > '' or \"ENAME\" < '') " > + "and (\"ENAME\" > '3' or \"ENAME\" < '3')", > "select * from \"EMP\" where \"ENAME\" <> '' and \"ENAME\" <> '3'") > .withDefaultSchemaSpec(CalciteAssert.SchemaSpec.SCOTT) > .ok(); > } > > Benchao Li <[email protected]> 于2022年5月30日周一 14:47写道： > > > 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 <[email protected]> 于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 <[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 > > > > > > > > > > > > > -- > > > > Best, > > Benchao Li > > > -- Best, Benchao Li
