You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Yanjing Wang <zh...@gmail.com> on 2021/07/29 08:58:56 UTC
does Calcite have the mathematical equation transformation such a + b
= c to a = c - b
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.
Re: does Calcite have the mathematical equation transformation such a
+ b = c to a = c - b
Posted by Yanjing Wang <zh...@gmail.com>.
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 <jh...@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 <zh...@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.
>
>
Re: does Calcite have the mathematical equation transformation such a
+ b = c to a = c - b
Posted by Julian Hyde <jh...@gmail.com>.
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 <zh...@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.