Hi everyone!
I'm testing the Calcite heuristic optimizer and would like to prepare
some code samples to demonstrate logical-to-logical rewrites in Calcite.
However, none of the rewrites are applied to my SQL. I'm doing something
wrong. Here is the complete test project:
https://github.com/RadimBaca/Calcite_csv
I'll start with a query
SELECT `D`.`ID`, COUNT(*)
FROM (SELECT *
FROM `PERSON`) AS `P`
INNER JOIN `DEPARTMENT` AS `D` ON `P`.`DEPARTMENT` = `D`.`ID`
WHERE `D`.`CITY` = 'Ostrava'
GROUP BY `D`.`ID`, 23
and the plan
LogicalProject(ID=[$0], EXPR$1=[$2])
LogicalAggregate(group=[{0, 1}], EXPR$1=[COUNT()])
LogicalProject(ID=[$4], $f1=[23])
LogicalFilter(condition=[=($6, 'Ostrava')])
LogicalJoin(condition=[=($3, $4)], joinType=[inner])
LogicalProject(ID=[$0], Name=[$1], Salary=[$2], DeparmentID=[$3])
LogicalTableScan(table=[[Person]])
LogicalTableScan(table=[[Department]])
I'm using the following list of rules:
planner.addRule(CoreRules.*/PROJECT_TO_CALC/*);
planner.addRule(CoreRules.*/FILTER_TO_CALC/*);
planner.addRule(CoreRules.*/PROJECT_JOIN_TRANSPOSE/*);
planner.addRule(CoreRules.*/FILTER_REDUCE_EXPRESSIONS/*);
planner.addRule(CoreRules.*/CALC_REDUCE_EXPRESSIONS/*);
planner.addRule(CoreRules.*/PROJECT_REMOVE/*);
planner.addRule(CoreRules.*/FILTER_AGGREGATE_TRANSPOSE/*);
planner.addRule(CoreRules.*/AGGREGATE_PROJECT_PULL_UP_CONSTANTS/*);
But the logical plan does not change. I expect it to remove the
unnecessary projection LogicalProject(ID=[$0], Name=[$1], Salary=[$2],
DeparmentID=[$3]) and maybe also the constant 23 in the aggregation. I
would also like to do an Aggregation pushdown; however, I don't know
which rule corresponds to it.
I would be glad for any help.
Thanks,
Radim
