Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-5861

ReduceExpressionsRule rules should constant-fold expressions in window bounds

    XMLWordPrintableJSON

Details

    • Improvement
    • Status: Closed
    • Minor
    • Resolution: Fixed
    • 1.34.0, 1.35.0
    • 1.36.0
    • core

    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.)
       

      Attachments

        Issue Links

          Activity

            People

              mbudiu Mihai Budiu
              mbudiu Mihai Budiu
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: