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.
