[
https://issues.apache.org/jira/browse/CALCITE-5861?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17747141#comment-17747141
]
Julian Hyde commented on CALCITE-5861:
--------------------------------------
It's helpful to change "do not" to "should" in the summary. It makes clear what
the intended behavior is (and that this is a feature request).
Whether an expression is constant should be checked by the validator (does not
need the optimizer). Reducing the constants is more complicated (because
expression evaluation can have rounding effects) and should be done at planning
time. I think the reduce-expressions rule just does not look inside windows
currently.
> 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
> Priority: Minor
> Labels: pull-request-available
>
> 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)
