[ https://issues.apache.org/jira/browse/CALCITE-5861?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17747629#comment-17747629 ]
Julian Hyde commented on CALCITE-5861: -------------------------------------- The SQL spec doesn't 'require' that bounds are constant. It says that a conforming implementation shall allow bounds that are constant. It doesn't say that a conforming implementation shall not allow bounds that are not constant. (The SQL spec 'requires' that you only include column references in GROUP BY, but every useful implementation I know allows expressions.) If I recall correctly, Oracle allows expressions. It's a nice feature. We should continue to allow expressions, even if Postgres doesn't. So I think this is just a feature request, for the constant folding rule to do better. > Optimization 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 > 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)