Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-2081

Query with windowed aggregates under both sides of a JOIN throws NullPointerException

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.16.0
    • Component/s: None
    • Labels:
      None

      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.

        Attachments

          Activity

            People

            • Assignee:
              julianhyde Julian Hyde
              Reporter:
              lfkauer Luis Fernando Kauer
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: