SQLite

** of setting and clearing regAcc.
*/
static void updateAccumulator(Parse *pParse, int regAcc, AggInfo *pAggInfo){
  Vdbe *v = pParse->pVdbe;
  int i;
  int regHit = 0;
  int addrHitTest = 0;
  int bFilterMinMax = 0;
  struct AggInfo_func *pF;
  struct AggInfo_col *pC;

  pAggInfo->directMode = 1;
  for(i=0, pF=pAggInfo->aFunc; i<pAggInfo->nFunc; i++, pF++){
    int nArg;
    int addrNext = 0;
    int regAgg;
    ExprList *pList = pF->pExpr->x.pList;
    assert( !ExprHasProperty(pF->pExpr, EP_xIsSelect) );
    assert( !IsWindowFunc(pF->pExpr) );
    if( ExprHasProperty(pF->pExpr, EP_WinFunc) ){
      Expr *pFilter = pF->pExpr->y.pWin->pFilter;
      if( pAggInfo->nAccumulator 
       && (pF->pFunc->funcFlags & SQLITE_FUNC_NEEDCOLL) 
      ){
        bFilterMinMax = 1;
        if( regHit==0 ) regHit = ++pParse->nMem;
        /* If this is the first row of the group (regAcc==0), clear the
        ** "magnet" register regHit so that the accumulator registers
        ** are populated even if the FILTER clause causes control to
        ** skip over the invocation of min() or max() altogether */
        sqlite3VdbeAddOp2(v, OP_Copy, regAcc, regHit);
      }
      addrNext = sqlite3VdbeMakeLabel(pParse);
      sqlite3ExprIfFalse(pParse, pFilter, addrNext, SQLITE_JUMPIFNULL);
    }
    if( pList ){
      nArg = pList->nExpr;
      regAgg = sqlite3GetTempRange(pParse, nArg);
      sqlite3ExprCodeExprList(pParse, pList, regAgg, 0, SQLITE_ECEL_DUP);

  }
  if( regHit ){
    addrHitTest = sqlite3VdbeAddOp1(v, OP_If, regHit); VdbeCoverage(v);
  }
  for(i=0, pC=pAggInfo->aCol; i<pAggInfo->nAccumulator; i++, pC++){
    sqlite3ExprCode(pParse, pC->pExpr, pC->iMem);
  }
  if( bFilterMinMax ){
    /* If there is a min() or max() with a FILTER clause, then ensure that
    ** the "magnet" register is set to indicate "do not attract" after
    ** loading column values into the accumulator registers */
    sqlite3VdbeAddOp2(v, OP_Integer, 1, regHit);
  }

  pAggInfo->directMode = 0;
  if( addrHitTest ){
    sqlite3VdbeJumpHere(v, addrHitTest);
  }
}

/*

        sqlite3VdbeAddOp1(v, OP_Close, iCsr);
        explainSimpleCount(pParse, pTab, pBest);
      }else
#endif /* SQLITE_OMIT_BTREECOUNT */
      {
        int regAcc = 0;           /* "populate accumulators" flag */

        /* If there are accumulator registers but no min() or max() functions
        ** without FILTER clauses, allocate register regAcc. Register regAcc
        ** will contain 0 the first time the inner loop runs, and 1 thereafter.
        ** The code generated by updateAccumulator() uses this to ensure
        ** that the accumulator registers are (a) updated only once if
        ** there are no min() or max functions or (b) always updated for the
        ** first row visited by the aggregate, so that they are updated at
        ** least once even if the FILTER clause means the min() or max() 
        ** function visits zero rows.  */
        if( sAggInfo.nAccumulator ){
          for(i=0; i<sAggInfo.nFunc; i++){
            if( ExprHasProperty(sAggInfo.aFunc[i].pExpr, EP_WinFunc) ) continue;
            if( sAggInfo.aFunc[i].pFunc->funcFlags&SQLITE_FUNC_NEEDCOLL ) break;
          }
          if( i==sAggInfo.nFunc ){
            regAcc = ++pParse->nMem;
            sqlite3VdbeAddOp2(v, OP_Integer, 0, regAcc);
          }
        }

do_catchsql_test 2.2 {
  SELECT sum(a) FILTER (WHERE 1 - max(a) OVER () > 0) FROM t1
} {1 {misuse of window function max()}}

do_catchsql_test 2.3 {
  SELECT sum(a) FILTER (WHERE 1 - count(a)) FROM t1
} {1 {misuse of aggregate function count()}}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 3.0 {
  CREATE TABLE t1(a,b);
  INSERT INTO t1 VALUES(1, 1);
}
do_execsql_test 3.1 {
  SELECT b, max(a) FILTER (WHERE b='x') FROM t1;
} {1 {}}

do_execsql_test 3.2 {
  CREATE TABLE t2(a, b, c);
  INSERT INTO t2 VALUES(1, 2, 3);
  INSERT INTO t2 VALUES(1, 3, 4);
  INSERT INTO t2 VALUES(2, 5, 6);
  INSERT INTO t2 VALUES(2, 7, 8);
}
do_execsql_test 3.3 {
  SELECT a, c, max(b) FILTER (WHERE c='x') FROM t2 GROUP BY a;
} {1 3 {} 2 6 {}}

do_execsql_test 3.4 {
  DELETE FROM t2;
  INSERT INTO t2 VALUES(1, 5, 'x');
  INSERT INTO t2 VALUES(1, 2, 3);
  INSERT INTO t2 VALUES(1, 4, 'x');
  INSERT INTO t2 VALUES(2, 5, 6);
  INSERT INTO t2 VALUES(2, 7, 8);
}
do_execsql_test 3.5 {
  SELECT a, c, max(b) FILTER (WHERE c='x') FROM t2 GROUP BY a;
} {1 x 5 2 6 {}}

finish_test