[CALCITE-5861] ReduceExpressionsRule rules should constant-fold expressions in window bounds

• Type: Improvement
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 1.34.0, 1.35.0
• Fix Version/s: 1.36.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Mihai Budiu
• Reporter: Mihai Budiu
• Created: 19/Jul/23 07:08
• Updated: 10/Nov/23 11:34
• Resolved: 01/Sep/23 17:48

Description

This bug is a bit more complicated to describe, and there is a possibility that I am doing something wrong.

Consider the following test that can be added to RelOptRulesTest:

@Test void testExpressionPreceding() {
    HepProgramBuilder preBuilder = new HepProgramBuilder();
    preBuilder.addRuleInstance(CoreRules.WINDOW_REDUCE_EXPRESSIONS);
    preBuilder.addRuleInstance(CoreRules.PROJECT_TO_LOGICAL_PROJECT_AND_WINDOW);

    HepProgramBuilder builder = new HepProgramBuilder();
    builder.addRuleInstance(CoreRules.PROJECT_REDUCE_EXPRESSIONS);
    HepPlanner hepPlanner = new HepPlanner(builder.build());

    final String sql =
        "select COUNT(*) over (\n"
            + "ORDER BY empno\n"
            + "ROWS BETWEEN 5 + 5 PRECEDING AND 1 PRECEDING) AS w_avg\n"
            + "  from emp\n";
    sql(sql)
        .withPre(preBuilder.build())
        .withPlanner(hepPlanner)
        .check();
  }

The plan before looks like this:

LogicalProject($0=[$2])
  LogicalWindow(window#0=[window(order by [0] rows between $1 PRECEDING and $2 PRECEDING aggs [COUNT()])])
    LogicalProject(EMPNO=[$0], $1=[+(5, 5)])
      LogicalTableScan(table=[[CATALOG, SALES, EMP]])

The problem is that the expression "5+5", which appears in the window bounds, has not been reduced to a constant by the rule WINDOW_REDUCE_EXPRESSIONS. Moreover, the next optimization rule PROJECT_TO_LOGICAL_PROJECT_AND_WINDOW has pushed this expression into the LogicalProject. So it appears locally that the LogicalWindow no longer has a constant bound, which is required by the SQL language spec (it is constant, but that is no longer apparent in the query). (At least our code generator is upset by this state of affairs.)
 

Issue Links

• links to: GitHub Pull Request #3328