[
https://issues.apache.org/jira/browse/CALCITE-5861?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17744849#comment-17744849
]
Chunwei Lei edited comment on CALCITE-5861 at 7/20/23 2:52 AM:
---------------------------------------------------------------
I tried the query mentioned in PostgreSQL and it works:
{code:sql}
postgres=# select COUNT(*) over ( ORDER BY empno ROWS BETWEEN 5 + 5 PRECEDING
AND 1 PRECEDING) AS w_avg from emp;
w_avg
-------
(0 rows)
{code}
So I agree with your proposal.
Besides, the following sql cannot run in PostgreSQL:
{code:sql}
postgres=# select COUNT(*) over ( ORDER BY empno ROWS BETWEEN 5 + empno
PRECEDING AND 1 PRECEDING) AS w_avg from emp;
ERROR: argument of ROWS must not contain variables
LINE 1: ...t COUNT(*) over ( ORDER BY empno ROWS BETWEEN 5 + empno PREC...
{code}
but it does not throw an exception in calcite. We should fix it too. WDYT?
was (Author: chunwei lei):
I tried the query mentioned in PostgreSQL and it works:
{code:sql}
postgres=# select COUNT(*) over ( ORDER BY empno ROWS BETWEEN 5 + 5 PRECEDING
AND 1 PRECEDING) AS w_avg from emp;
w_avg
-------
(0 rows)
{code}
So I agree with your proposal.
Besides, the following sql cannot run in PostgreSQL:
{code:sql}
postgres=# select COUNT(*) over ( ORDER BY empno ROWS BETWEEN 5 + empno
PRECEDING AND 1 PRECEDING) AS w_avg from emp;
ERROR: argument of ROWS must not contain variables
LINE 1: ...t COUNT(*) over ( ORDER BY empno ROWS BETWEEN 5 + empno PREC...
{code}
but it does not throw an exception in calcite.
> 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)
