Re: [DISCUSS] not equal operator vs less than combining greater than

Yanjing, Yes, I know. But I don't think this is a problem. Yanjing Wang 于2022年6月2日周四 17:54写道： > Benchao, > > I mean that "name" <> '3' will have '3' to be varchar(10) type, but "name" > > '3' or "name" < '3' will have '3' to be char(1) type, I noticed that your > change will make "name" > '3'

Re: [DISCUSS] not equal operator vs less than combining greater than

Benchao, I mean that "name" <> '3' will have '3' to be varchar(10) type, but "name" > '3' or "name" < '3' will have '3' to be char(1) type, I noticed that your change will make "name" > '3' or "name" < '3' to be "name" <> '3', but the '3' is still char(1) type. should the '3' type be consistent

Re: [DISCUSS] not equal operator vs less than combining greater than

Big thanks to you, Benchao, I also noticed that SubstitutionVisitor#isEquivalent method evaluates equivalence without types and RexCall#computeDigest(boolean withType) with false argument . Now we are aware that the type difference doesn't affect materialization applications and expression

Re: [DISCUSS] not equal operator vs less than combining greater than

Yanjing, Generally, the default HEP_PROGRAM will translate Filter and Project into Calc, and later it will be matched via Substitution#CalcToCalcUnifyRule. If you only got FILTER_REDUCE_EXPRESSIONS in the program, then it will be matched via Substitution#TrivialRule. I think the TrivialRule is

Re: [DISCUSS] not equal operator vs less than combining greater than

Thanks Benchao, I changed the canonizing HEP_PROGRAM to only FILTER_REDUCE_EXPRESSIONS rule so that the materialized view can't be applied, the original HEP_PROGRAM is ok. I don't know if the original HEP_PROGRAM is a must and I expect it should be applied without the original HEP_PROGRAM in this

Re: [DISCUSS] not equal operator vs less than combining greater than

Hi Yanjing, I know your concerns, and I know the difference between the inferred types. However, my point is that this does not affect the materialization substitution. My branch is: https://github.com/libenchao/calcite/tree/5169-simplification-improvement You can try your test case on this

Re: [DISCUSS] not equal operator vs less than combining greater than

Benchao, I noticed your Sargs in RelOptRulesTest.xml, you can see LogicalFilter(condition=[SEARCH($1, Sarg[(-∞..'':VARCHAR(20)), ('':VARCHAR(20)..'3':VARCHAR(20)), ('3':VARCHAR(20)..+∞)]:VARCHAR(20))]) vs LogicalFilter(condition=[SEARCH($1, Sarg[(-∞..'':CHAR(1)), ('':CHAR(1)..'3'),

Re: [DISCUSS] not equal operator vs less than combining greater than

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 于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

Re: [DISCUSS] not equal operator vs less than combining greater than

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

Re: [DISCUSS] not equal operator vs less than combining greater than

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

Re: [DISCUSS] not equal operator vs less than combining greater than

Thanks Julian, Benchao, This is not only a problem about expression simplification, also type consistency in conversion[1]. As Xiong said in CALCITE-4993 , "EQUALS and NOT-EQUALS comparison. Because they use the same LEAST_RESTRICTIVE strategy

Re: [DISCUSS] not equal operator vs less than combining greater than

I've filed an issue[1] to track this. [1] https://issues.apache.org/jira/browse/CALCITE-5169 Benchao Li 于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: >

Re: [DISCUSS] not equal operator vs less than combining greater than

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

Re: [DISCUSS] not equal operator vs less than combining greater than

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

Re: [DISCUSS] not equal operator vs less than combining greater than

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 wrote: > > Hi community, > > I have this sql: select

[DISCUSS] not equal operator vs less than combining greater than

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