[Test Environment]

SQLite Version : 3.47.0 OS : Ubuntu 22.04, 64bit

[Details]

Under the following conditions, filter clauses do not work properly.

1. MIN, MAX function used as window function
2. When the start of the window frame is not UNBOUND PRECEDING and,
3. When there is no EXCLUDE clause

The following queries have detected the bug.

CREATE TABLE t0 (c0 INTEGER, c1 INTEGER);
INSERT INTO t0 (c0, c1) VALUES (10, 1), (20, -1), (5, 2), (15, 0), (25, 3);
SELECT c0, c1, MIN(c0) FILTER(WHERE c1 > 0) OVER (ORDER BY c0 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS min_filtered_rows_frame FROM t0;

The correct result of this query is as follows.

5 2 5
10 1 5
15 0 10
20 -1 25
25 3 25

However, the query returns the following results.

SQLite version 3.47.0 2024-10-21 16:30:22
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE t0 (c0 INTEGER, c1 INTEGER);
INSERT INTO t0 (c0, c1) VALUES (10, 1), (20, -1), (5, 2), (15, 0), (25, 3);
SELECT c0, c1, MIN(c0) FILTER(WHERE c1 > 0) OVER (ORDER BY c0 ROWS BETWEEN 1
PRECEDING AND 1 FOLLOWING) AS min_filtered_rows_frame FROM t0;
5|2|5
10|1|5
15|0|10
20|-1|15
25|3|20

The minimized PoC is as follows:

CREATE TABLE t0 (c0 INTEGER);
INSERT INTO t0 (c0) VALUES (10), (20), (5), (15), (25);
SELECT MIN(c0) FILTER(WHERE false) OVER (ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM t0;

It does not return all NULLs despite the false condition in the FILTER cluase.

After analyzing the cause of the bug, we found a missing part of the code in the windowAggStep() function in the window.c file.

static void windowAggStep(
  WindowCodeArg *p,
  Window *pMWin,                  /* Linked list of window functions */
  int csr,                        /* Read arguments from this cursor */
  int bInverse,                   /* True to invoke xInverse instead of xStep */
  int reg                         /* Array of registers */
){
  Parse *pParse = p->pParse;
  Vdbe *v = sqlite3GetVdbe(pParse);
  Window *pWin;
  for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
    FuncDef *pFunc = pWin->pWFunc;
    ...

    if( pMWin->regStartRowid==0
     && (pFunc->funcFlags & SQLITE_FUNC_MINMAX)
     && (pWin->eStart!=TK_UNBOUNDED)
    ){
	    // HERE 
      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);
    }else if( pWin->regApp ){
      ...
    }else if( pFunc->xSFunc!=noopStepFunc ){
      ...
    }
  }
}

The code that triggers the bug is the first if statement.

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);
  }

When using MIN, MAX functions as a window function, the code that compiles bytecodes to handle the FILTER clause is missing.

Here's a simple patch:

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);
}