[CALCITE-5348] When translating ORDER BY in OVER, use the session's default null collation (e.g. NULLS LAST)

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.32.0
• Fix Version/s: 1.33.0
• Component/s: core
• Assignee: Julian Hyde
• Reporter: shanpo liu
• Created: 26/Oct/22 12:47
• Updated: 07/Feb/23 22:33
• Resolved: 06/Nov/22 01:06

Description

The connection configuration  string defaultNullCollation=LOW can't take effect for order by in over clause.  When translating ORDER BY in OVER, Calcite should use the session's default null collation (e.g. NULLS LAST).

After debug, I found that the  convertSortExpression in SqlToRelConverter.convertOver was changed to bb::sortToRex and the bb::sortToRex didn't read default configuration.

https://github.com/apache/calcite/blob/c945b7f49b99538748c871557f6ac80957be2b6e/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java#L2247

Also, The corresponding test case is incorrect at  org.apache.calcite.test.SqlToRelConverterTest#testUserDefinedOrderByOver .

sql :

select deptno,rank() over(partition by empno order by deptno) 
  from emp 
  order by row_number() over(partition by empno order by deptno) 

correct result

LogicalProject(DEPTNO=[$0], EXPR$1=[$1])
  LogicalSort(sort0=[$2], dir0=[ASC-nulls-first])
    LogicalProject(DEPTNO=[$7], EXPR$1=[RANK() OVER (PARTITION BY $0 ORDER BY $7 NULLS FIRST)], EXPR$2=[ROW_NUMBER() OVER (PARTITION BY $0 ORDER BY $7 NULLS FIRST)])
      LogicalTableScan(table=[[CATALOG, SALES, EMP]]) 

but was 

LogicalProject(DEPTNO=[$0], EXPR$1=[$1])
  LogicalSort(sort0=[$2], dir0=[ASC-nulls-first])
    LogicalProject(DEPTNO=[$7], EXPR$1=[RANK() OVER (PARTITION BY $0 ORDER BY $7)], EXPR$2=[ROW_NUMBER() OVER (PARTITION BY $0 ORDER BY $7)])
      LogicalTableScan(table=[[CATALOG, SALES, EMP]]) 

Issue Links

• is related to: CALCITE-4497 In RelBuilder, support windowed aggregate functions (OVER)
• relates to: CALCITE-2323 The configuration parameter "defaultNullCollation" which enum NullCollation { LOW, HIGH, FIRST, LAST } to allow alternative policies, but it can't take effect for over();