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)
