The following query
*SELECT COUNT(**CNTRn**) FROM incorta.SLS.CNTR*
Is translated to the following
*SELECT COUNT("**CNTRn**") AS "EXPR$0"*
*FROM (SELECT "**CNTRn**" FROM "**SLS**"."**CNTR**") AS "t"*
But I want to pushdown the whole aggregation so the output query would be
*SELECT COUNT("**CNTRn**") FROM "**SLS**"."**CNTR**"*
To do so, I wrote a rule that matches *LogicalAggregate* with input
*JdbcProject*, and transformed the *LogicalAggregate* to a *JdbcAggregate*
node with it's input being the *JdbcProject*.
public void onMatch(RelOptRuleCall call) {
> LogicalAggregate aggregate = call.rel(0);
> RelNode input = ((HepRelVertex) aggregate.getInput()).getCurrentRel();
> JdbcAggregate newAggregate;
> try {
> newAggregate = new JdbcRules.JdbcAggregate(aggregate.getCluster(),
> aggregate.getTraitSet().replace(this.outConvention), *input*,
> aggregate.indicator, aggregate.getGroupSet(), aggregate.getGroupSets(),
> aggregate.getAggCallList());
> call.transformTo(newAggregate);
> } catch (InvalidRelException e) {
> e.printStackTrace();
> }
> }
But that threw the following exception because the aggregation class type
of the aggregate call is of type *DrillCalciteSqlAggFunctionWrapper* while
*JdbcRules.AGG_FUNCS* (i.e. *ImmutableList*) doesn't contain this type
(Check
*org.apache.calcite.adapter.jdbc.JdbcRules.JdbcAggregate.canImplement(SqlAggFunction,
SqlDialect)* for details)
org.apache.calcite.rel.InvalidRelException: cannot implement aggregate
> function COUNT
> at
> org.apache.calcite.adapter.jdbc.JdbcRules$JdbcAggregate.<init>(JdbcRules.java:710)
So I tried providing a different aggregate call list *manually*:
public void onMatch(RelOptRuleCall call) {
> LogicalAggregate aggregate = call.rel(0);
> RelNode input = ((HepRelVertex) aggregate.getInput()).getCurrentRel();
> JdbcAggregate newAggregate;
> try {
> AggregateCall agg = aggregate.getAggCallList().get(0);
> AggregateCall newAgg =
> AggregateCall.create(SqlStdOperatorTable.COUNT, agg.isDistinct(),
> agg.getArgList(), agg.filterArg, agg.type, agg.name);
> List<AggregateCall> *sqlAggCallList* = Arrays.asList(newAgg);
> newAggregate = new JdbcRules.JdbcAggregate(aggregate.getCluster(),
> aggregate.getTraitSet().replace(this.outConvention), input,
> aggregate.indicator, aggregate.getGroupSet(), aggregate.getGroupSets(),
> *sqlAggCallList*);
> call.transformTo(newAggregate);
> } catch (InvalidRelException e) {
> e.printStackTrace();
> }
> }
And this output the following query
*SELECT COUNT("**CNTRn**") AS "EXPR$0"*
*FROM (SELECT "**CNTRn**" FROM "**SLS**"."**CNTR**") AS "t"*
But this isn't the syntax I was hoping for because the datasource I'm using
doesn't support subqueries.
Again, this is what I'm aiming for
*SELECT COUNT("**CNTRn**") FROM "**SLS**"."**CNTR**"*
*---------------------*
*Muhammad Gelbana*
http://www.linkedin.com/in/mgelbana
