[CALCITE-2081] Query with windowed aggregates under both sides of a JOIN throws NullPointerException

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.16.0
• Assignee: Julian Hyde
• Reporter: Luis Fernando Kauer
• Created: 08/Dec/17 19:37
• Updated: 27/Feb/24 22:23
• Resolved: 26/Feb/18 20:20

Description

NPE when joining 2 subqueries that use window functions.

Testing with a new test case in JdbcTest:

  /** Tests for a join of subqueries using Window Functions */
  @Test public void testJoinWithWinAgg() {
    final String sql = "select a.*, b.r from\n"
        + "(select \"deptno\", first_value(\"empid\") over \n"
        + "(partition by \"deptno\" order by \"commission\") as r\n"
        + "from \"hr\".\"emps\") a\n"
        + "left join\n"
        + "(select \"deptno\", last_value(\"empid\") over \n"
        + "(partition by \"deptno\" order by \"commission\") as r\n"
        + "from \"hr\".\"emps\") b\n"
        + "on a.\"deptno\" = b.\"deptno\"";
        CalciteAssert.hr()
        .query(sql)
        .runs();
  }

Debugging this I found out that the NPE occurs at Expressions.declare (line 2937) in initializer.getType() because initializer is null. The statement is

int prevStart;

This statement does not have an initializer, but the method "declare" is trying to get the initializer's type, generating NPE.
This happens when joining 2 subqueries that use window functions because they end up using the same variables. In BlockBuilder.append (line 124) it checks if the variable already exists and the problem starts there.