Hi Julian, thanks for your reply. Could you help me understand how such a rule should be implemented and what the match operands should be. I understand I am not supposed to just match on LogicalProject and then traverse but instead match on smth like op(LP, op(LA, op(LP))) ? Is it possible to have a rule match on multiple different patterns? Is it possible or considered Ok to traverse the tree using get input and casting to HepRelVertex?
Thanks On Fri, 24 Aug 2018, 19:45 Julian Hyde, <[email protected]> wrote: > In relational algebra > > select sum(x + y) > from (select x, y from t) > > is identical to > > select sum(xy) > from (select x + y as xy from t) > > because relational algebra does not have “sub-queries”. So, the one thing > your rewrite rule is accomplishing is converting > > case when product = ‘foo’ when sum(units) else 0 end > > into > > sum(case when product = ‘foo’ then 0 else units end) > > That rewrite seems valid, as long as units is not null and groups are > never empty. (If a group is empty, as can happen in windowed aggregates, or > all of its units values are null, then sum would return null not 0.) > > In general, the rewrite seems to be to convert f(agg(x)) into agg(g(x)). > > For example if f is “2 *” and agg is “sum”, then g would be “2 *”. Thus “2 > * sum(units)” becomes “sum(2 * units)”. But as you see, after the rewrite > we are doing more work (because we are multiplying more values by 2). So > it’s valid but probably not beneficial. We’d have to be careful how we > apply this rule. > > I’d name this rule “ProjectAggregateTransposeRule”. To make get it into > Calcite, you would need to log a JIRA case, convert your code from Kotlin > into Java, write some test cases in RelOptRulesTest (including a case where > the rule does NOT fire because null values would make it invalid), and > create a PR. I’d want the code to be able to handle a bit more than “CASE … > ELSE 0 END” applied to “SUM”. We want the rule to be extensible to handle > other patterns. > > Julian > > > > On Aug 24, 2018, at 7:09 AM, Z. S. <[email protected]> wrote: > > > > Hi, > > > > I'm interested in writing a rule to push certain aggregate rexes to a > lower > > subqueries to be able to remove not needed group by statements. In SQL > this > > would look by transforming: > > > > select t2.id, sum(case when product = 'foo' then sum_units else 0 end) > > from ( select id, product, sum(units) AS sum_units from orders group by > id, > > product ) t2 > > group by t2.id > > > > Into: > > select t2.id, sum(sum_units) > > from ( select id, sum(case when product = 'foo' then sum_units else 0 > end) > > as sum_units from orders group by id ) t2 group by t2.id > > > > I've been able to implement a rule for this specific example so I know in > > theory it's possible to make the rule generic. You can see the rule here: > > https://pastebin.com/G3x9CdAW > > > > My question is if there's a better way to solve this? Are there any > > existing calcite tools that could be used? I tried looking at > PushProjector > > class but it doesn't seem to be for this purpose? Is it OK to traverse > the > > tree like I'm doing it by calling the getInput method and casting nodes > to > > HepRelVertex or is there a better way to traverse the tree? How would you > > suggest implementing such a rule? > > > > Thanks > >
