You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@pinot.apache.org by "yashmayya (via GitHub)" <gi...@apache.org> on 2024/10/21 16:45:46 UTC

[PR] Window function ignore nulls [pinot]

yashmayya opened a new pull request, #14264:
URL: https://github.com/apache/pinot/pull/14264

   - The SQL standard defines a `RESPECT NULLS` or `IGNORE NULLS` option for the window functions `LEAD`, `LAG`, `FIRST_VALUE`, `LAST_VALUE`, and `NTH_VALUE` (although Pinot currently doesn't support this function). The default behavior is `RESPECT NULLS`.
   - This patch adds support for these options on the `FIRST_VALUE` and `LAST_VALUE` window functions (`LEAD` / `LAG` can be added in a subsequent patch). As the name suggests, the `IGNORE NULLS` option makes it so that the `FIRST_VALUE` and `LAST_VALUE` window functions compute the first and last non-null values respectively for each window frame.
   - If `IGNORE NULLS` is specified like `LAST_VALUE(col1) IGNORE NULLS OVER (ORDER BY ts)`, it can effectively be used to gapfill data (see this article for example - https://learn.microsoft.com/en-us/azure/azure-sql-edge/imputing-missing-values).
   - Calcite has validation to ensure that the `IGNORE NULLS` / `RESPECT NULLS` operators are only used with window functions that they are applicable to as per standard SQL. This patch also updates the operators being registered in Pinot's operator table for `LEAD` / `LAG` since we don't currently support the null related options for those functions (this way, we fail during query planning rather than at runtime).
   - There are also some minor changes to the query plan serde here to hold the `IGNORE NULLS` option for a window function call.
   - The changes in this patch are built on top of https://github.com/apache/pinot/pull/14249. This PR will be rebased once that one is merged. Until then, the diff between the two can be reviewed more easily here - https://github.com/yashmayya/pinot/pull/1.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org


Re: [PR] Add IGNORE NULLS option to FIRST_VALUE and LAST_VALUE window functions [pinot]

Posted by "gortiz (via GitHub)" <gi...@apache.org>.
gortiz commented on code in PR #14264:
URL: https://github.com/apache/pinot/pull/14264#discussion_r1818835138


##########
pinot-query-planner/src/main/java/org/apache/pinot/query/planner/serde/RexExpressionToProtoExpression.java:
##########
@@ -64,9 +64,15 @@ public static Expressions.FunctionCall convertFunctionCall(RexExpression.Functio
     for (RexExpression operand : operands) {
       protoOperands.add(convertExpression(operand));
     }
-    return Expressions.FunctionCall.newBuilder().setDataType(convertColumnDataType(functionCall.getDataType()))
-        .setFunctionName(functionCall.getFunctionName()).addAllFunctionOperands(protoOperands)
-        .setIsDistinct(functionCall.isDistinct()).build();
+    //@formatter:off

Review Comment:
   nit: you shouldn't need to add this now



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org


Re: [PR] Add IGNORE NULLS option to FIRST_VALUE and LAST_VALUE window functions [pinot]

Posted by "gortiz (via GitHub)" <gi...@apache.org>.
gortiz commented on code in PR #14264:
URL: https://github.com/apache/pinot/pull/14264#discussion_r1818880977


##########
pinot-query-runtime/src/main/java/org/apache/pinot/query/runtime/operator/window/value/FirstValueWindowFunction.java:
##########
@@ -113,12 +187,115 @@ private List<Object> processRangeWindow(List<Object[]> rows) {
     return result;
   }
 
-  private List<Object> processUnboundedPreceding(List<Object[]> rows) {
+  private List<Object> processRangeWindowIgnoreNulls(List<Object[]> rows) {
     int numRows = rows.size();
-    assert numRows > 0;
-    Object value = extractValueFromRow(rows.get(0));
-    Object[] result = new Object[numRows];
-    Arrays.fill(result, value);
-    return Arrays.asList(result);
+
+    if (_windowFrame.isUnboundedPreceding() && _windowFrame.isUnboundedFollowing()) {
+      // Find the first non-null value and fill it in all rows
+      for (int i = 0; i < numRows; i++) {
+        Object[] row = rows.get(i);
+        Object value = extractValueFromRow(row);
+        if (value != null) {
+          return fillAllWithValue(rows, value);
+        }
+      }
+      // There's no non-null value
+      return Collections.nCopies(numRows, null);
+    }
+
+    if (_windowFrame.isUnboundedPreceding() && _windowFrame.isUpperBoundCurrentRow()) {
+      // Find the first non-null value and fill it in all rows starting from the first row of the peer group of the row
+      // with the first non-null value
+      int firstNonNullValueIndex = -1;
+      Key firstNonNullValueKey = null;
+      for (int i = 0; i < numRows; i++) {
+        Object[] row = rows.get(i);
+        Object value = extractValueFromRow(row);
+        if (value != null) {
+          firstNonNullValueIndex = i;
+          firstNonNullValueKey = AggregationUtils.extractRowKey(row, _orderKeys);
+          break;
+        }
+      }
+
+      // No non-null values
+      if (firstNonNullValueIndex == -1) {
+        return Collections.nCopies(numRows, null);
+      }
+
+      List<Object> result = new ArrayList<>(numRows);
+      // Find the start of the peer group of the row with the first non-null value
+      int i;
+      for (i = 0; i < numRows; i++) {
+        Object[] row = rows.get(i);
+        Key orderKey = AggregationUtils.extractRowKey(row, _orderKeys);
+        if (orderKey.equals(firstNonNullValueKey)) {
+          break;
+        } else {
+          result.add(null);
+        }
+      }
+
+      Object firstNonNullValue = extractValueFromRow(rows.get(firstNonNullValueIndex));
+      for (; i < numRows; i++) {
+        result.add(firstNonNullValue);
+      }
+
+      return result;

Review Comment:
   nit: Here we could reduce allocation cost if using a custom list that could combine two lists. I'm shocked about the lack of a list like that in Guava, but shouldn't be difficult to create one. The idea would be to create two lists like Collections.nCopies. The first would contain just nulls and the second just the first non null value. Finally we wrap these two instances in a view whose get delegates on either the first or the second list depending on whether the index is greater or smaller than firstNonNullValueIndex.
   
   If this pattern is seen in more window functions, to create this combine list view would worth the effort



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org


Re: [PR] Add IGNORE NULLS option to FIRST_VALUE and LAST_VALUE window functions [pinot]

Posted by "gortiz (via GitHub)" <gi...@apache.org>.
gortiz commented on code in PR #14264:
URL: https://github.com/apache/pinot/pull/14264#discussion_r1818853675


##########
pinot-query-runtime/src/main/java/org/apache/pinot/query/runtime/operator/window/value/FirstValueWindowFunction.java:
##########
@@ -78,12 +86,78 @@ private List<Object> processRowsWindow(List<Object[]> rows) {
       lowerBound++;
       upperBound = Math.min(upperBound + 1, numRows - 1);
     }
+
+    return result;
+  }
+
+  private List<Object> processRowsWindowIgnoreNulls(List<Object[]> rows) {
+    int numRows = rows.size();
+    int lowerBound = _windowFrame.getLowerBound();
+    int upperBound = Math.min(_windowFrame.getUpperBound(), numRows - 1);
+
+    int indexOfFirstNonNullValue = -1;
+    // Find first non-null value in the first window
+    for (int i = Math.max(lowerBound, 0); i <= upperBound; i++) {
+      Object value = extractValueFromRow(rows.get(i));
+      if (value != null) {
+        indexOfFirstNonNullValue = i;
+        break;
+      }
+    }
+
+    List<Object> result = new ArrayList<>(numRows);
+
+    for (int i = 0; i < numRows; i++) {
+      if (lowerBound >= numRows) {
+        // Fill the remaining rows with null since all subsequent windows will be out of bounds
+        for (int j = i; j < numRows; j++) {
+          result.add(null);
+        }
+        break;
+      }
+
+      if (indexOfFirstNonNullValue != -1) {
+        result.add(extractValueFromRow(rows.get(indexOfFirstNonNullValue)));
+      } else {
+        result.add(null);
+      }
+
+      // Slide the window forward by one row; check if indexOfFirstNonNullValue is the lower bound which will not be in
+      // the next window. If so, find the next non-null value.
+      if (lowerBound >= 0 && indexOfFirstNonNullValue == lowerBound) {
+        indexOfFirstNonNullValue = -1;
+        // Find first non-null value for the next window
+        for (int j = lowerBound + 1; j <= Math.min(upperBound + 1, numRows - 1); j++) {
+          Object value = extractValueFromRow(rows.get(j));
+          if (value != null) {
+            indexOfFirstNonNullValue = j;
+            break;
+          }
+        }
+      }
+      lowerBound++;

Review Comment:
   This is assuming lowerBound is Integer.MIN_VALUE if unbounded, right? Although it can never turn into 0 due to the fact that maxRows is bound to Integer.MAX_VALUE... don't you think it is a bit difficult to understand?



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org


Re: [PR] Add IGNORE NULLS option to FIRST_VALUE and LAST_VALUE window functions [pinot]

Posted by "Jackie-Jiang (via GitHub)" <gi...@apache.org>.
Jackie-Jiang merged PR #14264:
URL: https://github.com/apache/pinot/pull/14264


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org


Re: [PR] Add IGNORE NULLS option to FIRST_VALUE and LAST_VALUE window functions [pinot]

Posted by "Jackie-Jiang (via GitHub)" <gi...@apache.org>.
Jackie-Jiang commented on code in PR #14264:
URL: https://github.com/apache/pinot/pull/14264#discussion_r1819891616


##########
pinot-query-runtime/src/main/java/org/apache/pinot/query/runtime/operator/window/value/FirstValueWindowFunction.java:
##########
@@ -78,12 +86,73 @@ private List<Object> processRowsWindow(List<Object[]> rows) {
       lowerBound++;
       upperBound = Math.min(upperBound + 1, numRows - 1);
     }
+
+    return result;
+  }
+
+  private List<Object> processRowsWindowIgnoreNulls(List<Object[]> rows) {

Review Comment:
   We can replace the for loop to nCopy, and also short-circuit all if checks. The total cost of the query is just finding the first/last non-null value, so the save could potentially be relatively significant.
   
   From high level, for `UNBOUNDED PROCEEDING, CURRENT ROW, UNBOUNDED FOLLOWING`, the behavior of `ROWS` and `RANGES` should be the same. So we could potentially split the handling into 3 cases:
   - Without bounded proceeding/following
   - ROWS with bounded proceeding/following
   - RANGE with bounded proceeding/following - not supported
   
   We can discuss and address this in a separate PR



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org


Re: [PR] Add IGNORE NULLS option to FIRST_VALUE and LAST_VALUE window functions [pinot]

Posted by "yashmayya (via GitHub)" <gi...@apache.org>.
yashmayya commented on code in PR #14264:
URL: https://github.com/apache/pinot/pull/14264#discussion_r1818981343


##########
pinot-query-runtime/src/main/java/org/apache/pinot/query/runtime/operator/window/value/FirstValueWindowFunction.java:
##########
@@ -78,12 +86,78 @@ private List<Object> processRowsWindow(List<Object[]> rows) {
       lowerBound++;
       upperBound = Math.min(upperBound + 1, numRows - 1);
     }
+
+    return result;
+  }
+
+  private List<Object> processRowsWindowIgnoreNulls(List<Object[]> rows) {
+    int numRows = rows.size();
+    int lowerBound = _windowFrame.getLowerBound();
+    int upperBound = Math.min(_windowFrame.getUpperBound(), numRows - 1);
+
+    int indexOfFirstNonNullValue = -1;
+    // Find first non-null value in the first window
+    for (int i = Math.max(lowerBound, 0); i <= upperBound; i++) {
+      Object value = extractValueFromRow(rows.get(i));
+      if (value != null) {
+        indexOfFirstNonNullValue = i;
+        break;
+      }
+    }
+
+    List<Object> result = new ArrayList<>(numRows);
+
+    for (int i = 0; i < numRows; i++) {
+      if (lowerBound >= numRows) {
+        // Fill the remaining rows with null since all subsequent windows will be out of bounds
+        for (int j = i; j < numRows; j++) {
+          result.add(null);
+        }
+        break;
+      }
+
+      if (indexOfFirstNonNullValue != -1) {
+        result.add(extractValueFromRow(rows.get(indexOfFirstNonNullValue)));
+      } else {
+        result.add(null);
+      }
+
+      // Slide the window forward by one row; check if indexOfFirstNonNullValue is the lower bound which will not be in
+      // the next window. If so, find the next non-null value.
+      if (lowerBound >= 0 && indexOfFirstNonNullValue == lowerBound) {
+        indexOfFirstNonNullValue = -1;
+        // Find first non-null value for the next window
+        for (int j = lowerBound + 1; j <= Math.min(upperBound + 1, numRows - 1); j++) {
+          Object value = extractValueFromRow(rows.get(j));
+          if (value != null) {
+            indexOfFirstNonNullValue = j;
+            break;
+          }
+        }
+      }
+      lowerBound++;

Review Comment:
   Yep, this convention was added in https://github.com/apache/pinot/pull/14273, and was chosen since it was the least invasive given the existing framework for window functions. I've documented this in a couple of places - https://github.com/apache/pinot/blob/0f984e81d3fce0d9d5bafb950cbbcf4b4dbc6763/pinot-query-planner/src/main/java/org/apache/pinot/query/planner/plannode/WindowNode.java#L33-L37
   
   https://github.com/apache/pinot/blob/0f984e81d3fce0d9d5bafb950cbbcf4b4dbc6763/pinot-query-runtime/src/main/java/org/apache/pinot/query/runtime/operator/window/WindowFrame.java#L31-L35
   
   > Although it can never turn into 0 due to the fact that maxRows is bound to Integer.MAX_VALUE... don't you think it is a bit difficult to understand?
   
   The logic here doesn't take that assumption into account though, we're handling all cases of `lowerBound` / `upperBound` (-ve / +ve). This way is also convenient because we don't need to worry about handling overflows everywhere.



##########
pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/NullHandlingIntegrationTest.java:
##########
@@ -326,6 +326,31 @@ public void testAggregateServerReturnFinalResult(boolean useMultiStageQueryEngin
     assertTrue(response.get("resultTable").get("rows").get(0).get(0).isNull());
   }
 
+  @Test
+  public void testWindowFunctionIgnoreNulls()
+      throws Exception {
+    // Window functions are only supported in the multi-stage query engine
+    setUseMultiStageQueryEngine(true);
+    String sqlQuery =
+        "SELECT salary, LAST_VALUE(salary) IGNORE NULLS OVER (ORDER BY DaysSinceEpoch) AS gapfilledSalary from "

Review Comment:
   Yes, actually this does look like a legitimate bug in Calcite on taking a second look. The issue is that the inferred return type for the window function in the parsed `SqlNode` is `INTEGER NOT NULL` when `IGNORE NULLS` option is used (assuming the column input to `FIRST_VALUE` / `LAST_VALUE` is `INTEGER`) and the converted return type is `INTEGER` (nullable) because offset based window frame bounds means that the result can be `null` when the window frame is out of bounds - which can't be the case when using `RANGE` window frames or `ROWS` window frames with `UNBOUNDED PRECEDING` / `UNBOUNDED FOLLOWING` / `CURRENT ROW`. When `IGNORE NULLS` option is not provided, the inferred return type for the window function in the parsed `SqlNode` is also `INTEGER` (nullable) which is why the issue doesn't occur there. Same when `IGNORE NULLS` option is used but input column is nullable. 
   
   Interestingly, the same error and issue also occurs when the `RESPECT NULLS` option is explicitly provided.
   
   I'll create a  bug tracking Jira in the Calcite project and link it here.



##########
pinot-query-runtime/src/main/java/org/apache/pinot/query/runtime/operator/window/value/FirstValueWindowFunction.java:
##########
@@ -113,12 +187,115 @@ private List<Object> processRangeWindow(List<Object[]> rows) {
     return result;
   }
 
-  private List<Object> processUnboundedPreceding(List<Object[]> rows) {
+  private List<Object> processRangeWindowIgnoreNulls(List<Object[]> rows) {
     int numRows = rows.size();
-    assert numRows > 0;
-    Object value = extractValueFromRow(rows.get(0));
-    Object[] result = new Object[numRows];
-    Arrays.fill(result, value);
-    return Arrays.asList(result);
+
+    if (_windowFrame.isUnboundedPreceding() && _windowFrame.isUnboundedFollowing()) {
+      // Find the first non-null value and fill it in all rows
+      for (int i = 0; i < numRows; i++) {
+        Object[] row = rows.get(i);
+        Object value = extractValueFromRow(row);
+        if (value != null) {
+          return fillAllWithValue(rows, value);
+        }
+      }
+      // There's no non-null value
+      return Collections.nCopies(numRows, null);
+    }
+
+    if (_windowFrame.isUnboundedPreceding() && _windowFrame.isUpperBoundCurrentRow()) {
+      // Find the first non-null value and fill it in all rows starting from the first row of the peer group of the row
+      // with the first non-null value
+      int firstNonNullValueIndex = -1;
+      Key firstNonNullValueKey = null;
+      for (int i = 0; i < numRows; i++) {
+        Object[] row = rows.get(i);
+        Object value = extractValueFromRow(row);
+        if (value != null) {
+          firstNonNullValueIndex = i;
+          firstNonNullValueKey = AggregationUtils.extractRowKey(row, _orderKeys);
+          break;
+        }
+      }
+
+      // No non-null values
+      if (firstNonNullValueIndex == -1) {
+        return Collections.nCopies(numRows, null);
+      }
+
+      List<Object> result = new ArrayList<>(numRows);
+      // Find the start of the peer group of the row with the first non-null value
+      int i;
+      for (i = 0; i < numRows; i++) {
+        Object[] row = rows.get(i);
+        Key orderKey = AggregationUtils.extractRowKey(row, _orderKeys);
+        if (orderKey.equals(firstNonNullValueKey)) {
+          break;
+        } else {
+          result.add(null);
+        }
+      }
+
+      Object firstNonNullValue = extractValueFromRow(rows.get(firstNonNullValueIndex));
+      for (; i < numRows; i++) {
+        result.add(firstNonNullValue);
+      }
+
+      return result;

Review Comment:
   Nice suggestion! I couldn't find any such off the shelf implementation either so I've created a small new class called `DualValueList` and added it to `pinot-common` in case we find use cases elsewhere in the codebase.



##########
pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/NullHandlingIntegrationTest.java:
##########
@@ -326,6 +326,31 @@ public void testAggregateServerReturnFinalResult(boolean useMultiStageQueryEngin
     assertTrue(response.get("resultTable").get("rows").get(0).get(0).isNull());
   }
 
+  @Test
+  public void testWindowFunctionIgnoreNulls()

Review Comment:
   I'm not sure I follow your suggestion - there's a single query here.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org


Re: [PR] Add IGNORE NULLS option to FIRST_VALUE and LAST_VALUE window functions [pinot]

Posted by "Jackie-Jiang (via GitHub)" <gi...@apache.org>.
Jackie-Jiang commented on code in PR #14264:
URL: https://github.com/apache/pinot/pull/14264#discussion_r1818208332


##########
pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/NullHandlingIntegrationTest.java:
##########
@@ -326,6 +326,31 @@ public void testAggregateServerReturnFinalResult(boolean useMultiStageQueryEngin
     assertTrue(response.get("resultTable").get("rows").get(0).get(0).isNull());
   }
 
+  @Test
+  public void testWindowFunctionIgnoreNulls()
+      throws Exception {
+    // Window functions are only supported in the multi-stage query engine
+    setUseMultiStageQueryEngine(true);
+    String sqlQuery =
+        "SELECT salary, LAST_VALUE(salary) IGNORE NULLS OVER (ORDER BY DaysSinceEpoch) AS gapfilledSalary from "

Review Comment:
   I guess we want to document it. Users might enable table level nullability (v1 engine nullability) and expect v2 engine to pick it up



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org


Re: [PR] Add IGNORE NULLS option to FIRST_VALUE and LAST_VALUE window functions [pinot]

Posted by "yashmayya (via GitHub)" <gi...@apache.org>.
yashmayya commented on code in PR #14264:
URL: https://github.com/apache/pinot/pull/14264#discussion_r1816148251


##########
pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/NullHandlingIntegrationTest.java:
##########
@@ -326,6 +326,31 @@ public void testAggregateServerReturnFinalResult(boolean useMultiStageQueryEngin
     assertTrue(response.get("resultTable").get("rows").get(0).get(0).isNull());
   }
 
+  @Test
+  public void testWindowFunctionIgnoreNulls()
+      throws Exception {
+    // Window functions are only supported in the multi-stage query engine
+    setUseMultiStageQueryEngine(true);
+    String sqlQuery =
+        "SELECT salary, LAST_VALUE(salary) IGNORE NULLS OVER (ORDER BY DaysSinceEpoch) AS gapfilledSalary from "

Review Comment:
   Yes, it works with bounded preceding / following as well. 
   
   > I remember running into some exception when trying it out
   
   Was it something like this error during query planning:
   
   ```
   Caused by: java.lang.RuntimeException: Failed to convert query to relational expression:
   ...
   Caused by: java.lang.AssertionError: Conversion to relational algebra failed to preserve datatypes:
   ```
   
   Interestingly, it looks like Calcite throws this error if the window function's input column is not nullable in the table schema (i.e., if `enableColumnBasedNullHandling` is `false` or the column has `"notNull": true`) and `IGNORE NULLS` or `RESPECT NULLS` option is used. While the error message is not the most clear, I don't think this is an actual bug because it doesn't really make sense to use those null handling related options if the window function's input column is not nullable.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org


Re: [PR] Add IGNORE NULLS option to FIRST_VALUE and LAST_VALUE window functions [pinot]

Posted by "codecov-commenter (via GitHub)" <gi...@apache.org>.
codecov-commenter commented on PR #14264:
URL: https://github.com/apache/pinot/pull/14264#issuecomment-2427304736

   ## [Codecov](https://app.codecov.io/gh/apache/pinot/pull/14264?dropdown=coverage&src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) Report
   All modified and coverable lines are covered by tests :white_check_mark:
   > Project coverage is 0.00%. Comparing base [(`59551e4`)](https://app.codecov.io/gh/apache/pinot/commit/59551e45224f1535c4863fd577622b37366ccc97?dropdown=coverage&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) to head [(`7579999`)](https://app.codecov.io/gh/apache/pinot/commit/75799995d80f3ec951888891f2e5d734edef802c?dropdown=coverage&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache).
   > Report is 1208 commits behind head on master.
   
   > :exclamation:  There is a different number of reports uploaded between BASE (59551e4) and HEAD (7579999). Click for more details.
   > 
   > <details><summary>HEAD has 53 uploads less than BASE</summary>
   >
   >| Flag | BASE (59551e4) | HEAD (7579999) |
   >|------|------|------|
   >|integration|7|1|
   >|integration2|3|1|
   >|temurin|12|1|
   >|java-21|7|1|
   >|skip-bytebuffers-true|3|0|
   >|skip-bytebuffers-false|7|1|
   >|unittests|5|0|
   >|unittests1|2|0|
   >|java-11|5|0|
   >|unittests2|3|0|
   >|integration1|2|0|
   >|custom-integration1|2|0|
   ></details>
   
   <details><summary>Additional details and impacted files</summary>
   
   
   ```diff
   @@              Coverage Diff              @@
   ##             master   #14264       +/-   ##
   =============================================
   - Coverage     61.75%    0.00%   -61.76%     
   =============================================
     Files          2436        3     -2433     
     Lines        133233        6   -133227     
     Branches      20636        0    -20636     
   =============================================
   - Hits          82274        0    -82274     
   + Misses        44911        6    -44905     
   + Partials       6048        0     -6048     
   ```
   
   | [Flag](https://app.codecov.io/gh/apache/pinot/pull/14264/flags?src=pr&el=flags&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | Coverage Δ | |
   |---|---|---|
   | [custom-integration1](https://app.codecov.io/gh/apache/pinot/pull/14264/flags?src=pr&el=flag&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | `?` | |
   | [integration](https://app.codecov.io/gh/apache/pinot/pull/14264/flags?src=pr&el=flag&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | `0.00% <ø> (-0.01%)` | :arrow_down: |
   | [integration1](https://app.codecov.io/gh/apache/pinot/pull/14264/flags?src=pr&el=flag&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | `?` | |
   | [integration2](https://app.codecov.io/gh/apache/pinot/pull/14264/flags?src=pr&el=flag&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | `0.00% <ø> (ø)` | |
   | [java-11](https://app.codecov.io/gh/apache/pinot/pull/14264/flags?src=pr&el=flag&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | `?` | |
   | [java-21](https://app.codecov.io/gh/apache/pinot/pull/14264/flags?src=pr&el=flag&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | `0.00% <ø> (-61.63%)` | :arrow_down: |
   | [skip-bytebuffers-false](https://app.codecov.io/gh/apache/pinot/pull/14264/flags?src=pr&el=flag&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | `0.00% <ø> (-61.75%)` | :arrow_down: |
   | [skip-bytebuffers-true](https://app.codecov.io/gh/apache/pinot/pull/14264/flags?src=pr&el=flag&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | `?` | |
   | [temurin](https://app.codecov.io/gh/apache/pinot/pull/14264/flags?src=pr&el=flag&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | `0.00% <ø> (-61.76%)` | :arrow_down: |
   | [unittests](https://app.codecov.io/gh/apache/pinot/pull/14264/flags?src=pr&el=flag&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | `?` | |
   | [unittests1](https://app.codecov.io/gh/apache/pinot/pull/14264/flags?src=pr&el=flag&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | `?` | |
   | [unittests2](https://app.codecov.io/gh/apache/pinot/pull/14264/flags?src=pr&el=flag&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | `?` | |
   
   Flags with carried forward coverage won't be shown. [Click here](https://docs.codecov.io/docs/carryforward-flags?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache#carryforward-flags-in-the-pull-request-comment) to find out more.
   
   </details>
   
   [:umbrella: View full report in Codecov by Sentry](https://app.codecov.io/gh/apache/pinot/pull/14264?dropdown=coverage&src=pr&el=continue&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache).   
   :loudspeaker: Have feedback on the report? [Share it here](https://about.codecov.io/codecov-pr-comment-feedback/?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache).
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org


Re: [PR] Add IGNORE NULLS option to FIRST_VALUE and LAST_VALUE window functions [pinot]

Posted by "gortiz (via GitHub)" <gi...@apache.org>.
gortiz commented on code in PR #14264:
URL: https://github.com/apache/pinot/pull/14264#discussion_r1818856485


##########
pinot-query-runtime/src/main/java/org/apache/pinot/query/runtime/operator/window/value/FirstValueWindowFunction.java:
##########
@@ -78,12 +86,78 @@ private List<Object> processRowsWindow(List<Object[]> rows) {
       lowerBound++;
       upperBound = Math.min(upperBound + 1, numRows - 1);
     }
+
+    return result;
+  }
+
+  private List<Object> processRowsWindowIgnoreNulls(List<Object[]> rows) {
+    int numRows = rows.size();
+    int lowerBound = _windowFrame.getLowerBound();
+    int upperBound = Math.min(_windowFrame.getUpperBound(), numRows - 1);
+
+    int indexOfFirstNonNullValue = -1;
+    // Find first non-null value in the first window
+    for (int i = Math.max(lowerBound, 0); i <= upperBound; i++) {
+      Object value = extractValueFromRow(rows.get(i));
+      if (value != null) {
+        indexOfFirstNonNullValue = i;
+        break;
+      }

Review Comment:
   This code is also repeated when the lower bound is moved. I think it is worth it to move the code to its own function. Something like `findFirstNotNullInWindow(rows, lowerBound, upperBound)`. Not only makes the code easier to read but also makes the job easier for the jit trying to optimize the loop.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org


Re: [PR] Add IGNORE NULLS option to FIRST_VALUE and LAST_VALUE window functions [pinot]

Posted by "yashmayya (via GitHub)" <gi...@apache.org>.
yashmayya commented on code in PR #14264:
URL: https://github.com/apache/pinot/pull/14264#discussion_r1818944382


##########
pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/NullHandlingIntegrationTest.java:
##########
@@ -326,6 +326,31 @@ public void testAggregateServerReturnFinalResult(boolean useMultiStageQueryEngin
     assertTrue(response.get("resultTable").get("rows").get(0).get(0).isNull());
   }
 
+  @Test
+  public void testWindowFunctionIgnoreNulls()
+      throws Exception {
+    // Window functions are only supported in the multi-stage query engine
+    setUseMultiStageQueryEngine(true);
+    String sqlQuery =
+        "SELECT salary, LAST_VALUE(salary) IGNORE NULLS OVER (ORDER BY DaysSinceEpoch) AS gapfilledSalary from "

Review Comment:
   Yes, actually this does look like a legitimate bug in Calcite on taking a second look. The issue is that the inferred return type for the window function in the parsed `SqlNode` is `INTEGER NOT NULL` when `IGNORE NULLS` option is used (assuming the column input to `FIRST_VALUE` / `LAST_VALUE` is `INTEGER`) and the converted return type is `INTEGER` (nullable) because offset based window frame bounds means that the result can be `null` when the window frame is out of bounds - which can't be the case when using `RANGE` window frames or `ROWS` window frames with `UNBOUNDED PRECEDING` / `UNBOUNDED FOLLOWING` / `CURRENT ROW`. When `IGNORE NULLS` option is not provided, the inferred return type for the window function in the parsed `SqlNode` is also `INTEGER` (nullable) which is why the issue doesn't occur there. Same when `IGNORE NULLS` option is used but input column is nullable. 
   
   Interestingly, the same error and issue also occurs when the `RESPECT NULLS` option is explicitly provided.
   
   I'll create a  bug tracking Jira in the Calcite project and link it here.
   
   Edit: https://issues.apache.org/jira/browse/CALCITE-6648



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org


Re: [PR] Add IGNORE NULLS option to FIRST_VALUE and LAST_VALUE window functions [pinot]

Posted by "gortiz (via GitHub)" <gi...@apache.org>.
gortiz commented on code in PR #14264:
URL: https://github.com/apache/pinot/pull/14264#discussion_r1818819771


##########
pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/NullHandlingIntegrationTest.java:
##########
@@ -326,6 +326,31 @@ public void testAggregateServerReturnFinalResult(boolean useMultiStageQueryEngin
     assertTrue(response.get("resultTable").get("rows").get(0).get(0).isNull());
   }
 
+  @Test
+  public void testWindowFunctionIgnoreNulls()
+      throws Exception {
+    // Window functions are only supported in the multi-stage query engine
+    setUseMultiStageQueryEngine(true);
+    String sqlQuery =
+        "SELECT salary, LAST_VALUE(salary) IGNORE NULLS OVER (ORDER BY DaysSinceEpoch) AS gapfilledSalary from "

Review Comment:
   I don't think we should delegate this into something we document. We should also report the issue in Calcite Jira/email list and/or create a PR to fix it ourselfs. Same happened with the reserved keyword PR.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org


Re: [PR] Add IGNORE NULLS option to FIRST_VALUE and LAST_VALUE window functions [pinot]

Posted by "yashmayya (via GitHub)" <gi...@apache.org>.
yashmayya commented on code in PR #14264:
URL: https://github.com/apache/pinot/pull/14264#discussion_r1820124388


##########
pinot-query-runtime/src/main/java/org/apache/pinot/query/runtime/operator/window/value/FirstValueWindowFunction.java:
##########
@@ -78,12 +86,73 @@ private List<Object> processRowsWindow(List<Object[]> rows) {
       lowerBound++;
       upperBound = Math.min(upperBound + 1, numRows - 1);
     }
+
+    return result;
+  }
+
+  private List<Object> processRowsWindowIgnoreNulls(List<Object[]> rows) {

Review Comment:
   > From high level, for UNBOUNDED PROCEEDING, CURRENT ROW, UNBOUNDED FOLLOWING, the behavior of ROWS and RANGES should be the same
   
   The behavior will only be the same for `UNBOUNDED PRECEDING TO UNBOUNDED FOLLOWING` right? With `CURRENT ROW` as either lower or upper bound or both, the behavior and logic are both significantly different due to the need to consider peer groups (rows before and after with the same order key) along with current row with `RANGE` type window frames. 
   
   > We can replace the for loop to nCopy, and also short-circuit all if checks. The total cost of the query is just finding the first/last non-null value, so the save could potentially be relatively significant.
   
   This makes sense for true unbounded case though (`UNBOUNDED PRECEDING TO UNBOUNDED FOLLOWING`, `CURRENT ROW` is not really unbounded), I've raised a small follow-up PR - https://github.com/apache/pinot/pull/14324.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org


Re: [PR] Add IGNORE NULLS option to FIRST_VALUE and LAST_VALUE window functions [pinot]

Posted by "Jackie-Jiang (via GitHub)" <gi...@apache.org>.
Jackie-Jiang commented on code in PR #14264:
URL: https://github.com/apache/pinot/pull/14264#discussion_r1818210808


##########
pinot-query-runtime/src/main/java/org/apache/pinot/query/runtime/operator/window/value/FirstValueWindowFunction.java:
##########
@@ -78,12 +86,73 @@ private List<Object> processRowsWindow(List<Object[]> rows) {
       lowerBound++;
       upperBound = Math.min(upperBound + 1, numRows - 1);
     }
+
+    return result;
+  }
+
+  private List<Object> processRowsWindowIgnoreNulls(List<Object[]> rows) {
+    int numRows = rows.size();
+    int lowerBound = _windowFrame.getLowerBound();
+    int upperBound = Math.min(_windowFrame.getUpperBound(), numRows - 1);
+
+    int indexOfFirstNonNullValue = -1;
+    // Find first non-null value in the first window
+    for (int i = Math.max(lowerBound, 0); i <= upperBound; i++) {
+      Object value = extractValueFromRow(rows.get(i));
+      if (value != null) {
+        indexOfFirstNonNullValue = i;
+        break;
+      }
+    }
+
+    List<Object> result = new ArrayList<>();

Review Comment:
   ```suggestion
       List<Object> result = new ArrayList<>(numRows);
   ```



##########
pinot-query-runtime/src/main/java/org/apache/pinot/query/runtime/operator/window/value/FirstValueWindowFunction.java:
##########
@@ -78,12 +86,73 @@ private List<Object> processRowsWindow(List<Object[]> rows) {
       lowerBound++;
       upperBound = Math.min(upperBound + 1, numRows - 1);
     }
+
+    return result;
+  }
+
+  private List<Object> processRowsWindowIgnoreNulls(List<Object[]> rows) {

Review Comment:
   Should we short circuit the unbounded case?



##########
pinot-query-runtime/src/main/java/org/apache/pinot/query/runtime/operator/window/value/FirstValueWindowFunction.java:
##########
@@ -78,12 +86,73 @@ private List<Object> processRowsWindow(List<Object[]> rows) {
       lowerBound++;
       upperBound = Math.min(upperBound + 1, numRows - 1);
     }
+
+    return result;
+  }
+
+  private List<Object> processRowsWindowIgnoreNulls(List<Object[]> rows) {
+    int numRows = rows.size();
+    int lowerBound = _windowFrame.getLowerBound();
+    int upperBound = Math.min(_windowFrame.getUpperBound(), numRows - 1);
+
+    int indexOfFirstNonNullValue = -1;
+    // Find first non-null value in the first window
+    for (int i = Math.max(lowerBound, 0); i <= upperBound; i++) {
+      Object value = extractValueFromRow(rows.get(i));
+      if (value != null) {
+        indexOfFirstNonNullValue = i;
+        break;
+      }
+    }
+
+    List<Object> result = new ArrayList<>();
+
+    for (int i = 0; i < numRows; i++) {
+      if (lowerBound >= numRows) {
+        // Fill the remaining rows with null
+        for (int j = i; j < numRows; j++) {
+          result.add(null);
+        }
+        break;
+      }
+
+      if (indexOfFirstNonNullValue != -1) {
+        result.add(extractValueFromRow(rows.get(indexOfFirstNonNullValue)));
+      } else {
+        result.add(null);
+      }
+
+      // Slide the window forward by one row
+      if (indexOfFirstNonNullValue == lowerBound) {

Review Comment:
   Should we do this check only if `indexOfFirstNonNullValue != -1`?



##########
pinot-query-runtime/src/main/java/org/apache/pinot/query/runtime/operator/window/value/FirstValueWindowFunction.java:
##########
@@ -78,12 +86,73 @@ private List<Object> processRowsWindow(List<Object[]> rows) {
       lowerBound++;
       upperBound = Math.min(upperBound + 1, numRows - 1);
     }
+
+    return result;
+  }
+
+  private List<Object> processRowsWindowIgnoreNulls(List<Object[]> rows) {
+    int numRows = rows.size();
+    int lowerBound = _windowFrame.getLowerBound();
+    int upperBound = Math.min(_windowFrame.getUpperBound(), numRows - 1);
+
+    int indexOfFirstNonNullValue = -1;
+    // Find first non-null value in the first window
+    for (int i = Math.max(lowerBound, 0); i <= upperBound; i++) {
+      Object value = extractValueFromRow(rows.get(i));
+      if (value != null) {
+        indexOfFirstNonNullValue = i;
+        break;
+      }
+    }
+
+    List<Object> result = new ArrayList<>();
+
+    for (int i = 0; i < numRows; i++) {
+      if (lowerBound >= numRows) {
+        // Fill the remaining rows with null
+        for (int j = i; j < numRows; j++) {
+          result.add(null);
+        }
+        break;
+      }
+
+      if (indexOfFirstNonNullValue != -1) {
+        result.add(extractValueFromRow(rows.get(indexOfFirstNonNullValue)));
+      } else {
+        result.add(null);
+      }
+
+      // Slide the window forward by one row
+      if (indexOfFirstNonNullValue == lowerBound) {
+        indexOfFirstNonNullValue = -1;
+        // Find first non-null value for the next window
+        for (int j = Math.max(lowerBound + 1, 0); j <= Math.min(upperBound + 1, numRows - 1); j++) {
+          Object value = extractValueFromRow(rows.get(j));
+          if (value != null) {
+            indexOfFirstNonNullValue = j;
+            break;
+          }
+        }
+      }
+      lowerBound++;
+
+      if (upperBound < numRows - 1) {

Review Comment:
   Some javadoc would help explain the logic here



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org


Re: [PR] Add IGNORE NULLS option to FIRST_VALUE and LAST_VALUE window functions [pinot]

Posted by "gortiz (via GitHub)" <gi...@apache.org>.
gortiz commented on code in PR #14264:
URL: https://github.com/apache/pinot/pull/14264#discussion_r1818829059


##########
pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/NullHandlingIntegrationTest.java:
##########
@@ -326,6 +326,31 @@ public void testAggregateServerReturnFinalResult(boolean useMultiStageQueryEngin
     assertTrue(response.get("resultTable").get("rows").get(0).get(0).isNull());
   }
 
+  @Test
+  public void testWindowFunctionIgnoreNulls()

Review Comment:
   Can we divide this test method into two different ones?



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org


Re: [PR] Add IGNORE NULLS option to FIRST_VALUE and LAST_VALUE window functions [pinot]

Posted by "yashmayya (via GitHub)" <gi...@apache.org>.
yashmayya commented on code in PR #14264:
URL: https://github.com/apache/pinot/pull/14264#discussion_r1816148251


##########
pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/NullHandlingIntegrationTest.java:
##########
@@ -326,6 +326,31 @@ public void testAggregateServerReturnFinalResult(boolean useMultiStageQueryEngin
     assertTrue(response.get("resultTable").get("rows").get(0).get(0).isNull());
   }
 
+  @Test
+  public void testWindowFunctionIgnoreNulls()
+      throws Exception {
+    // Window functions are only supported in the multi-stage query engine
+    setUseMultiStageQueryEngine(true);
+    String sqlQuery =
+        "SELECT salary, LAST_VALUE(salary) IGNORE NULLS OVER (ORDER BY DaysSinceEpoch) AS gapfilledSalary from "

Review Comment:
   Yes, it works with bounded preceding / following as well. 
   
   > I remember running into some exception when trying it out
   
   Was it something like this error during query planning:
   
   ```
   Caused by: java.lang.RuntimeException: Failed to convert query to relational expression:
   ...
   Caused by: java.lang.AssertionError: Conversion to relational algebra failed to preserve datatypes:
   ```
   
   Interestingly, it looks like Calcite throws this error if the window function's input column is not nullable in the table schema (i.e., if `enableColumnBasedNullHandling` is `false` or the column has `"notNull": true`) and `IGNORE NULLS` or `RESPECT NULLS` option is used. While the error message is not the most clear, I don't think this is ~an actual bug~ a major issue because it doesn't really make sense to use those null handling related options if the window function's input column is not nullable.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org


Re: [PR] Add IGNORE NULLS option to FIRST_VALUE and LAST_VALUE window functions [pinot]

Posted by "Jackie-Jiang (via GitHub)" <gi...@apache.org>.
Jackie-Jiang commented on code in PR #14264:
URL: https://github.com/apache/pinot/pull/14264#discussion_r1815544184


##########
pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/NullHandlingIntegrationTest.java:
##########
@@ -326,6 +326,31 @@ public void testAggregateServerReturnFinalResult(boolean useMultiStageQueryEngin
     assertTrue(response.get("resultTable").get("rows").get(0).get(0).isNull());
   }
 
+  @Test
+  public void testWindowFunctionIgnoreNulls()
+      throws Exception {
+    // Window functions are only supported in the multi-stage query engine
+    setUseMultiStageQueryEngine(true);
+    String sqlQuery =
+        "SELECT salary, LAST_VALUE(salary) IGNORE NULLS OVER (ORDER BY DaysSinceEpoch) AS gapfilledSalary from "

Review Comment:
   Does it work with bounded preceding/following? I remember running into some exception when trying it out



##########
pinot-query-planner/src/main/java/org/apache/pinot/calcite/sql/fun/PinotOperatorTable.java:
##########
@@ -152,7 +149,17 @@ public static PinotOperatorTable instance() {
       SqlStdOperatorTable.RANK,
       SqlStdOperatorTable.ROW_NUMBER,
 
+      // WINDOW Functions (non-aggregate)
+      SqlStdOperatorTable.LAST_VALUE,
+      SqlStdOperatorTable.FIRST_VALUE,
+      // TODO: Replace these with SqlStdOperatorTable.LEAD and SqlStdOperatorTable.LAG when the function implementations

Review Comment:
   Does this mean `IGNORE NULLS` are simply ignored?
   I'd suggest using the standard operator, and throw exception when `IGNORE NULLS` is specified but cannot be supported to make the behavior more explicit



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org


Re: [PR] Add IGNORE NULLS option to FIRST_VALUE and LAST_VALUE window functions [pinot]

Posted by "Jackie-Jiang (via GitHub)" <gi...@apache.org>.
Jackie-Jiang commented on code in PR #14264:
URL: https://github.com/apache/pinot/pull/14264#discussion_r1821370446


##########
pinot-query-runtime/src/main/java/org/apache/pinot/query/runtime/operator/window/value/FirstValueWindowFunction.java:
##########
@@ -78,12 +86,73 @@ private List<Object> processRowsWindow(List<Object[]> rows) {
       lowerBound++;
       upperBound = Math.min(upperBound + 1, numRows - 1);
     }
+
+    return result;
+  }
+
+  private List<Object> processRowsWindowIgnoreNulls(List<Object[]> rows) {

Review Comment:
   I see. Good point on the peer group difference



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org


Re: [PR] Add IGNORE NULLS option to FIRST_VALUE and LAST_VALUE window functions [pinot]

Posted by "yashmayya (via GitHub)" <gi...@apache.org>.
yashmayya commented on code in PR #14264:
URL: https://github.com/apache/pinot/pull/14264#discussion_r1818438677


##########
pinot-query-runtime/src/main/java/org/apache/pinot/query/runtime/operator/window/value/FirstValueWindowFunction.java:
##########
@@ -78,12 +86,73 @@ private List<Object> processRowsWindow(List<Object[]> rows) {
       lowerBound++;
       upperBound = Math.min(upperBound + 1, numRows - 1);
     }
+
+    return result;
+  }
+
+  private List<Object> processRowsWindowIgnoreNulls(List<Object[]> rows) {
+    int numRows = rows.size();
+    int lowerBound = _windowFrame.getLowerBound();
+    int upperBound = Math.min(_windowFrame.getUpperBound(), numRows - 1);
+
+    int indexOfFirstNonNullValue = -1;
+    // Find first non-null value in the first window
+    for (int i = Math.max(lowerBound, 0); i <= upperBound; i++) {
+      Object value = extractValueFromRow(rows.get(i));
+      if (value != null) {
+        indexOfFirstNonNullValue = i;
+        break;
+      }
+    }
+
+    List<Object> result = new ArrayList<>();
+
+    for (int i = 0; i < numRows; i++) {
+      if (lowerBound >= numRows) {
+        // Fill the remaining rows with null
+        for (int j = i; j < numRows; j++) {
+          result.add(null);
+        }
+        break;
+      }
+
+      if (indexOfFirstNonNullValue != -1) {
+        result.add(extractValueFromRow(rows.get(indexOfFirstNonNullValue)));
+      } else {
+        result.add(null);
+      }
+
+      // Slide the window forward by one row
+      if (indexOfFirstNonNullValue == lowerBound) {

Review Comment:
   Good catch, we'd be doing a pointless iteration from 0 to upper bound when we're at `lowerBound = -1` 👍



##########
pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/NullHandlingIntegrationTest.java:
##########
@@ -326,6 +326,31 @@ public void testAggregateServerReturnFinalResult(boolean useMultiStageQueryEngin
     assertTrue(response.get("resultTable").get("rows").get(0).get(0).isNull());
   }
 
+  @Test
+  public void testWindowFunctionIgnoreNulls()
+      throws Exception {
+    // Window functions are only supported in the multi-stage query engine
+    setUseMultiStageQueryEngine(true);
+    String sqlQuery =
+        "SELECT salary, LAST_VALUE(salary) IGNORE NULLS OVER (ORDER BY DaysSinceEpoch) AS gapfilledSalary from "

Review Comment:
   Yeah, good point, I'll make sure to add a note about this to the documentation. I plan to raise one consolidated documentation PR with changes from https://github.com/apache/pinot/pull/14273 and here.



##########
pinot-query-runtime/src/main/java/org/apache/pinot/query/runtime/operator/window/value/FirstValueWindowFunction.java:
##########
@@ -78,12 +86,73 @@ private List<Object> processRowsWindow(List<Object[]> rows) {
       lowerBound++;
       upperBound = Math.min(upperBound + 1, numRows - 1);
     }
+
+    return result;
+  }
+
+  private List<Object> processRowsWindowIgnoreNulls(List<Object[]> rows) {

Review Comment:
   I don't think there's much benefit to that here? In the unbounded case for `FIRST_VALUE` / `LAST_VALUE` with `IGNORE NULLS`, we'll find the first / last non-null value in the first window (which will encompass all rows) at the beginning and then in each iteration there's only some simple boolean checks which will all be `false` in every iteration of the loop and we'll simply keep adding the same value to the result list.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org


Re: [PR] Add IGNORE NULLS option to FIRST_VALUE and LAST_VALUE window functions [pinot]

Posted by "yashmayya (via GitHub)" <gi...@apache.org>.
yashmayya commented on code in PR #14264:
URL: https://github.com/apache/pinot/pull/14264#discussion_r1816156864


##########
pinot-query-planner/src/main/java/org/apache/pinot/calcite/sql/fun/PinotOperatorTable.java:
##########
@@ -152,7 +149,17 @@ public static PinotOperatorTable instance() {
       SqlStdOperatorTable.RANK,
       SqlStdOperatorTable.ROW_NUMBER,
 
+      // WINDOW Functions (non-aggregate)
+      SqlStdOperatorTable.LAST_VALUE,
+      SqlStdOperatorTable.FIRST_VALUE,
+      // TODO: Replace these with SqlStdOperatorTable.LEAD and SqlStdOperatorTable.LAG when the function implementations

Review Comment:
   > Does this mean IGNORE NULLS are simply ignored?
   
   Nope, using `IGNORE NULLS` with `LAG` / `LEAD` will lead to a clear error like this during query planning - `From line 1, column 43 to line 1, column 60: Cannot specify IGNORE NULLS or RESPECT NULLS following 'LAG'`. This is because the custom operators we defined return `false` for `allowsNullTreatment` which means this Calcite validation will fail - https://github.com/apache/calcite/blob/ef1a83f659e8771c65c2541b92d2ef9cc2a05bea/core/src/main/java/org/apache/calcite/sql/SqlNullTreatmentOperator.java#L69-L74.
   
   I'd initially gone with simply throwing a runtime exception in Pinot's `LagValueWindowFunction` / `LeadValueWindowFunction` runtime operators, but the alternative chosen here suggested by @gortiz (defining our own custom `SqlAggFunction`s) is much better because we fail fast during query planning instead of query execution and the error is also clear.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org