[CALCITE-5502] RelToSql converter generates where clause with window expression

• Type: Bug
• Status: In Progress
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.32.0
• Component/s: jdbc-adapter
• Assignee: Leonid Chistov
• Reporter: Leonid Chistov
• Created: 26/Jan/23 12:31
• Updated: 10/Sep/23 11:04

Description

Wrong SQL code is generated when Filter (or Calc) node contains window expression.

Example can be demonstrated by adding following code to RelToSqlConverterTest.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));
} 

Generated SQL code will look like:

SELECT *
FROM \"scott\".\"DEPT\"
WHERE (MAX(\"DEPTNO\") OVER (PARTITION BY \"DNAME\" RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) < 1 

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: 

Windowed aggregate expression is illegal in WHERE clause 

 

Issue Links

• is related to: CALCITE-5523 RelToSql converter generates GROUP BY clause with window expression