[ https://issues.apache.org/jira/browse/CALCITE-5502?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Leonid Chistov reassigned CALCITE-5502: --------------------------------------- Assignee: Leonid Chistov > RelToSql converter generates where clause with window expression > ---------------------------------------------------------------- > > Key: CALCITE-5502 > URL: https://issues.apache.org/jira/browse/CALCITE-5502 > Project: Calcite > Issue Type: Bug > Components: jdbc-adapter > Affects Versions: 1.32.0 > Reporter: Leonid Chistov > Assignee: Leonid Chistov > Priority: Major > > Wrong SQL code is generated when Filter (or Calc) node contains window > expression. > Example can be demonstrated by adding following code to > RelToSqlConverterTest.java: > {code:java} > @Test void testWindowedFilter() { > final RelBuilder builder = relBuilder(); > final RelNode root = builder > .scan("DEPT") > .filter( > builder.lessThan( > builder.aggregateCall(SqlStdOperatorTable.MAX, > builder.field("DEPTNO")) > .over() > .partitionBy(builder.field("DNAME")) > .toRex(), > builder.literal(1) > ) > ) > .build(); > final String expectedSql = "?"; > assertThat(toSql(root), isLinux(expectedSql)); > } {code} > Generated SQL code will look like: > {code:java} > SELECT * > FROM \"scott\".\"DEPT\" > WHERE (MAX(\"DEPTNO\") OVER (PARTITION BY \"DNAME\" RANGE BETWEEN UNBOUNDED > PRECEDING AND UNBOUNDED FOLLOWING)) < 1 {code} > This is incorrect - window expressions are not allowed in WHERE clause by SQL > standard and Calcite itself would produce following error message if this SQL > code would be passed as input: > {code:java} > Windowed aggregate expression is illegal in WHERE clause {code} > -- This message was sent by Atlassian Jira (v8.20.10#820010)