Thanks Julian. My original idea is moving all input refs to the left to the comparison operator and moving constants to the right. for example, variable1 >= variable2 + monthDiff(20210801, 20210101)
results variable1 - variable2 >= monthDiff(20210801, 20210101) = 7. Julian Hyde <jhyde.apa...@gmail.com> 于2021年7月31日周六 上午6:55写道： > Calcite doesn’t transform > > a + b = c” to “a = c - b > > for good reason… someone would also want to transform > > a - b = c” to “a = c + b > > and the two transforms would cycle. > > To put it another way: Calcite’s expression simplification is not > smart/powerful enough to do theorem proving. Our rule of thumb is that > simplification rules must convert to something simpler. If it’s equivalent > but not (by some measure) simpler, we don’t do it. > > That said, I think there’s a way we could handle your case. We could have > a rule that recognizes > > variable1 - constant1 = constant2 > > and converts to > > variable1 = constant2 + constant1 > > (which matches your date_sub case). If someone were to add a rule that > recognizes > > variable1 + constant1 = constant2 > > and converts to > > variable1 = constant2 + constant1 > > it wouldn’t form a cycle. > > Can you please add a JIRA case for this? You should cover all forms of > “+”: arithmetic plus, DATETIME_PLUS (e.g. date + interval), and interval + > interval. > > One thing to watch out for (and test carefully) is arithmetic near the > limits of data types. If a, b and c are SQL INTEGER values then “a + b = c” > is always equivalent to “a = c - b”, but I don’t think that’s the case if > they are DOUBLE values. > > By the way, I am working on making RexSimplifier more pluggable (via a new > interface RexRule) [1] and this would be a nice implementation of RexRule. > > Julian > > [1] https://issues.apache.org/jira/browse/CALCITE-4559 > > > > On Jul 29, 2021, at 1:58 AM, Yanjing Wang <zhuangzixiao...@gmail.com> > wrote: > > > > Hi, guys > > I have a scenario to calculate the input ref in an expression, for > > example: > > date_sub(dt, 1) = '2021-07-29' to dt = date_add('2021-07-29', 1) > > thus I can conclude that dt = '2021-07-30'. > > > > I start from the most simplest form dt + 1 = 3. but it can't be > > simplified or reduced. > > I tried dt = 3 - 1 and it can be reduced to dt = 2. > > I wonder if I can transform dt + 1 = 3 to dt = 3 - 1, the dt will be > > calculated. > > > > So I think if we need a transformation rule to implement this kind of > > commute for expression, > > or we have other existing solution. > > > > Thanks for any suggestions. > >
