Mayb the hooks can help you debug what's going on? https://stackoverflow.com/questions/60091348/is-there-any-way-to-view-the-physical-sqls-executed-by-calcite-jdbc
Mihai ________________________________ From: Baca Radim <radim.b...@vsb.cz> Sent: Sunday, April 7, 2024 2:58 AM To: dev@calcite.apache.org <dev@calcite.apache.org> Subject: logical-to-logical rewrites 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