[
https://issues.apache.org/jira/browse/CALCITE-5861?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Alessandro Solimando resolved CALCITE-5861.
-------------------------------------------
Resolution: Fixed
Merged via
[{{cbf857e}}|https://github.com/apache/calcite/commit/cbf857e319a695fe96b1f3f5f38a779821c92cb3],
thanks [~mbudiu] for your contribution, and thanks to [~nobigo] and
[~julianhyde] for reviewing!
> ReduceExpressionsRule rules should constant-fold expressions in window bounds
> -----------------------------------------------------------------------------
>
> Key: CALCITE-5861
> URL: https://issues.apache.org/jira/browse/CALCITE-5861
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Affects Versions: 1.34.0, 1.35.0
> Reporter: Mihai Budiu
> Assignee: Mihai Budiu
> Priority: Minor
> Labels: pull-request-available
> Fix For: 1.36.0
>
>
> 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)
