[jira] [Created] (CALCITE-5861) Optimization rules do not constant-fold expressions in window bounds

["Mihai Budiu (Jira)" <ji...@apache.org>]

Mihai Budiu created CALCITE-5861:
------------------------------------

             Summary: Optimization rules do not constant-fold expressions in window bounds
                 Key: CALCITE-5861
                 URL: https://issues.apache.org/jira/browse/CALCITE-5861
             Project: Calcite
          Issue Type: Bug
          Components: core
    Affects Versions: 1.34.0
            Reporter: Mihai Budiu


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:
{code:java}
@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();
  }
{code}
The plan before looks like this:
{code:java}
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]])
{code}
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.)
 



--
This message was sent by Atlassian Jira
(v8.20.10#820010)