[CALCITE-2078] Aggregate functions in OVER clause

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.15.0
• Assignee: Julian Hyde
• Reporter: Liao Xintao
• Created: 05/Dec/17 07:47
• Updated: 09/Dec/17 18:21
• Resolved: 07/Dec/17 09:33

Description

When window functions use aggregate functions in PARTITION BY or ORDER BY sub-clause of the OVER clause, Calcite gives a NullPointerException.

Currently the scene of window functions on top of nested aggregations is already in consideration, however, that one as follow is not:

select
  sum(sal),
  rank() over (order by sum(sal))
from emp
group by deptno

An NPE from SqlToRelConverter:

java.lang.NullPointerException
	at com.google.common.base.Preconditions.checkNotNull(Preconditions.java:212)
	at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:4470)
	at org.apache.calcite.sql2rel.SqlToRelConverter.convertOver(SqlToRelConverter.java:1839)
	at org.apache.calcite.sql2rel.SqlToRelConverter.access$1500(SqlToRelConverter.java:213)
	at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:4462)
	at org.apache.calcite.sql2rel.SqlToRelConverter.createAggImpl(SqlToRelConverter.java:2857)
	at org.apache.calcite.sql2rel.SqlToRelConverter.convertAgg(SqlToRelConverter.java:2675)
	at org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:660)
	at org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:622)
	at org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3085)
	at org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:558)
	at org.apache.calcite.test.SqlToRelTestBase$TesterImpl.convertSqlToRel(SqlToRelTestBase.java:574)
	at org.apache.calcite.test.RelOptTestBase.checkPlanning(RelOptTestBase.java:137)
	at org.apache.calcite.test.RelOptTestBase.checkPlanning(RelOptTestBase.java:120)
	at org.apache.calcite.test.RelOptTestBase.checkPlanning(RelOptTestBase.java:95)
	at org.apache.calcite.test.RelOptTestBase.checkPlanning(RelOptTestBase.java:84)
	at org.apache.calcite.test.RelOptRulesTest.testWindowAggregatesWithOrderByAgg(RelOptRulesTest.java:2722)