FILTER Clause Ignored in SQLite Window Functions with MIN/MAX and Non-Unbounded Frames

Issue Overview: FILTER Clause Ignored in MIN/MAX Window Functions with Non-Unbounded Frames

The core issue revolves around the incorrect handling of the FILTER clause in SQLite when used with window functions, specifically MIN and MAX, under certain conditions. The problem manifests when the following three conditions are met:

1. The MIN or MAX function is used as a window function.
2. The window frame does not start with UNBOUNDED PRECEDING.
3. There is no EXCLUDE clause in the window function definition.

When these conditions are satisfied, the FILTER clause is effectively ignored, leading to incorrect query results. This behavior is particularly problematic because the FILTER clause is designed to restrict the rows considered by the window function based on a specified condition. When the clause is ignored, the window function processes rows that should have been excluded, resulting in inaccurate calculations.

The issue was identified through a test case involving a simple table t0 with two columns, c0 and c1. The query attempts to calculate the minimum value of c0 within a sliding window frame of one preceding and one following row, but only for rows where c1 is greater than zero. The expected result is a filtered minimum value for each row, but the query returns incorrect values due to the FILTER clause being ignored.

The root cause of the issue lies in the windowAggStep function within SQLite’s source code. Specifically, the function fails to properly handle the FILTER clause when the window frame does not start with UNBOUNDED PRECEDING. This omission causes the FILTER condition to be bypassed, leading to the observed incorrect behavior.

Possible Causes: Missing FILTER Clause Handling in windowAggStep Function

The primary cause of this issue is a missing implementation in the windowAggStep function, which is responsible for generating the bytecode that handles window function operations. The function does not account for the FILTER clause when the window frame starts with a non-UNBOUNDED PRECEDING boundary, leading to the clause being ignored.

The windowAggStep function processes window functions by iterating through the list of window definitions and generating the appropriate bytecode for each function. However, the function contains a critical oversight in the following block of code:

if( pMWin->regStartRowid==0
  && (pFunc->funcFlags & SQLITE_FUNC_MINMAX)
  && (pWin->eStart!=TK_UNBOUNDED)
){
  int addrIsNull = sqlite3VdbeAddOp1(v, OP_IsNull, regArg);
  VdbeCoverage(v);
  if( bInverse==0 ){
   sqlite3VdbeAddOp2(v, OP_AddImm, pWin->regApp+1, 1);
   sqlite3VdbeAddOp2(v, OP_SCopy, regArg, pWin->regApp);
   sqlite3VdbeAddOp3(v, OP_MakeRecord, pWin->regApp, 2, pWin->regApp+2);
   sqlite3VdbeAddOp2(v, OP_IdxInsert, pWin->csrApp, pWin->regApp+2);
  }else{
   sqlite3VdbeAddOp4Int(v, OP_SeekGE, pWin->csrApp, 0, regArg, 1);
   VdbeCoverageNeverTaken(v);
   sqlite3VdbeAddOp1(v, OP_Delete, pWin->csrApp);
   sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2);
  }
  sqlite3VdbeJumpHere(v, addrIsNull);
}

This block of code is responsible for handling MIN and MAX window functions when the window frame does not start with UNBOUNDED PRECEDING. However, it does not include any logic to process the FILTER clause, which is why the clause is ignored under these conditions.

The absence of FILTER clause handling in this context is a significant oversight, as it prevents the window function from correctly filtering rows based on the specified condition. This leads to incorrect results, as demonstrated in the test case provided.

Troubleshooting Steps, Solutions & Fixes: Implementing FILTER Clause Handling in windowAggStep

To resolve this issue, the windowAggStep function must be modified to include logic for handling the FILTER clause when the window frame does not start with UNBOUNDED PRECEDING. The following steps outline the necessary changes to the function:

1. Add a Check for the FILTER Clause: Before processing the MIN or MAX window function, the function should check whether a FILTER clause is present. If a FILTER clause exists, the function should generate bytecode to evaluate the filter condition and skip the row if the condition is not met.

2. Generate Bytecode for the FILTER Condition: If a FILTER clause is present, the function should generate bytecode to evaluate the filter condition. This involves loading the filter expression into a temporary register, evaluating the condition, and jumping to the end of the block if the condition is not satisfied.

3. Insert the FILTER Logic into the Existing Code: The FILTER logic should be inserted into the existing block of code that handles MIN and MAX window functions with non-UNBOUNDED PRECEDING frames. This ensures that the FILTER clause is processed before the window function is applied.

The following code snippet demonstrates the necessary changes to the windowAggStep function:

if( pMWin->regStartRowid==0
 && (pFunc->funcFlags & SQLITE_FUNC_MINMAX)
 && (pWin->eStart!=TK_UNBOUNDED)
){
  // ---PATCH 1 START----
 int addrIf = 0;
 if( pWin->pFilter ){
  int regTmp;
  assert( ExprUseXList(pWin->pOwner) );
  assert( pWin->bExprArgs || !nArg ||nArg==pWin->pOwner->x.pList->nExpr );
  assert( pWin->bExprArgs || nArg ||pWin->pOwner->x.pList==0 );
  regTmp = sqlite3GetTempReg(pParse);
  sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol+nArg,regTmp);
  addrIf = sqlite3VdbeAddOp3(v, OP_IfNot, regTmp, 0, 1);
  VdbeCoverage(v);
  sqlite3ReleaseTempReg(pParse, regTmp);
 }
  // ---PATCH 1 END----
 int addrIsNull = sqlite3VdbeAddOp1(v, OP_IsNull, regArg);
 VdbeCoverage(v);
 if( bInverse==0 ){
  sqlite3VdbeAddOp2(v, OP_AddImm, pWin->regApp+1, 1);
  sqlite3VdbeAddOp2(v, OP_SCopy, regArg, pWin->regApp);
  sqlite3VdbeAddOp3(v, OP_MakeRecord, pWin->regApp, 2, pWin->regApp+2);
  sqlite3VdbeAddOp2(v, OP_IdxInsert, pWin->csrApp, pWin->regApp+2);
 }else{
  sqlite3VdbeAddOp4Int(v, OP_SeekGE, pWin->csrApp, 0, regArg, 1);
  VdbeCoverageNeverTaken(v);
  sqlite3VdbeAddOp1(v, OP_Delete, pWin->csrApp);
  sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2);
 }
 // ---PATCH 2 START----
 if( addrIf ) sqlite3VdbeJumpHere(v, addrIf);
 // ---PATCH 2 END----
 sqlite3VdbeJumpHere(v, addrIsNull);
}

This patch introduces two key changes:

1. Patch 1: This section adds a check for the FILTER clause and generates bytecode to evaluate the filter condition. If the condition is not met, the function skips the row by jumping to the end of the block.

2. Patch 2: This section ensures that the jump address for the FILTER condition is properly set, allowing the function to correctly handle rows that do not meet the filter condition.

By implementing these changes, the windowAggStep function will correctly handle the FILTER clause in MIN and MAX window functions with non-UNBOUNDED PRECEDING frames, ensuring that the query results are accurate and consistent with the specified filter conditions.

Conclusion

The issue of the FILTER clause being ignored in SQLite window functions with MIN and MAX under specific conditions is a significant bug that can lead to incorrect query results. The root cause lies in the windowAggStep function’s failure to properly handle the FILTER clause when the window frame does not start with UNBOUNDED PRECEDING. By implementing the necessary changes to the function, as outlined in the patch above, this issue can be resolved, ensuring that the FILTER clause is correctly processed and applied in all relevant scenarios. This fix will improve the accuracy and reliability of SQLite’s window function capabilities, particularly in cases where filtering is required.