SQLite

/*
** The following structure keeps track of state information for the
** count() aggregate function.
*/
typedef struct CountCtx CountCtx;
struct CountCtx {
  i64 n;
#ifdef SQLITE_DEBUG
  int bInverse;                   /* True if xInverse() ever called */
#endif
};

/*
** Routines to implement the count() aggregate function.
*/
static void countStep(sqlite3_context *context, int argc, sqlite3_value **argv){
  CountCtx *p;
  p = sqlite3_aggregate_context(context, sizeof(*p));
  if( (argc==0 || SQLITE_NULL!=sqlite3_value_type(argv[0])) && p ){
    p->n++;
  }

#ifndef SQLITE_OMIT_DEPRECATED
  /* The sqlite3_aggregate_count() function is deprecated.  But just to make
  ** sure it still operates correctly, verify that its count agrees with our 
  ** internal count when using count(*) and when the total count can be
  ** expressed as a 32-bit integer. */
  assert( argc==1 || p==0 || p->n>0x7fffffff || p->bInverse
          || p->n==sqlite3_aggregate_count(context) );
#endif
}   
static void countFinalize(sqlite3_context *context){
  CountCtx *p;
  p = sqlite3_aggregate_context(context, 0);
  sqlite3_result_int64(context, p ? p->n : 0);
}
#ifndef SQLITE_OMIT_WINDOWFUNC
static void countInverse(sqlite3_context *ctx, int argc, sqlite3_value **argv){
  CountCtx *p;
  p = sqlite3_aggregate_context(ctx, sizeof(*p));
  if( (argc==0 || SQLITE_NULL!=sqlite3_value_type(argv[0])) && p ){
    p->n--;
#ifdef SQLITE_DEBUG
    p->bInverse = 1;
#endif
  }
}   
#endif

/*
** Routines to implement min() and max() aggregate functions.
*/
static void minmaxStep(
  sqlite3_context *context, 
  int NotUsed, 

    FUNCTION(replace,            3, 0, 0, replaceFunc      ),
    FUNCTION(zeroblob,           1, 0, 0, zeroblobFunc     ),
    FUNCTION(substr,             2, 0, 0, substrFunc       ),
    FUNCTION(substr,             3, 0, 0, substrFunc       ),
    WAGGREGATE(sum,   1,0,0, sumStep, sumFinalize, sumFinalize, sumInverse, 0),
    WAGGREGATE(total, 1,0,0, sumStep,totalFinalize,totalFinalize,sumInverse, 0),
    WAGGREGATE(avg,   1,0,0, sumStep, avgFinalize, avgFinalize, sumInverse, 0),
    WAGGREGATE(count, 0,0,0, countStep, 
        countFinalize, countFinalize, countInverse, SQLITE_FUNC_COUNT  ),
    WAGGREGATE(count, 1,0,0, countStep, 
        countFinalize, countFinalize, countInverse, 0  ),
    WAGGREGATE(group_concat, 1, 0, 0, groupConcatStep, 
        groupConcatFinalize, groupConcatValue, groupConcatInverse, 0),
    WAGGREGATE(group_concat, 2, 0, 0, groupConcatStep, 
        groupConcatFinalize, groupConcatValue, groupConcatInverse, 0),
  
    LIKEFUNC(glob, 2, &globInfo, SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE),
#ifdef SQLITE_CASE_SENSITIVE_LIKE

          ** in an index. */
          notValid(pParse, pNC, "non-deterministic functions",
                   NC_IdxExpr|NC_PartIdx);
        }
      }

#ifndef SQLITE_OMIT_WINDOWFUNC
      assert( is_agg==0 || (pDef->funcFlags & SQLITE_FUNC_MINMAX)
          || (pDef->xValue==0 && pDef->xInverse==0)
          || (pDef->xValue && pDef->xInverse && pDef->xSFunc && pDef->xFinalize)
      );
      if( pDef && pDef->xValue==0 && pExpr->pWin ){
        sqlite3ErrorMsg(pParse, 
            "%.*s() may not be used as a window function", nId, zId
        );
        pNC->nErr++;
      }else if( 
            (is_agg && (pNC->ncFlags & NC_AllowAgg)==0)
         || (is_agg && (pDef->funcFlags & SQLITE_FUNC_WINDOW) && !pExpr->pWin)

  if( rc!=SQLITE_OK ){
    Tcl_SetObjResult(interp, Tcl_NewStringObj(sqlite3ErrName(rc), -1));
    return TCL_ERROR;
  }
  return TCL_OK;
}

static int SQLITE_TCLAPI test_override_sum(
  void * clientData,
  Tcl_Interp *interp,
  int objc,
  Tcl_Obj *CONST objv[]
){
  sqlite3 *db;
  int rc;

  if( objc!=2 ){
    Tcl_WrongNumArgs(interp, 1, objv, "DB");
    return TCL_ERROR;
  }
  if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR;

  rc = sqlite3_create_function(db, "sum", -1, SQLITE_UTF8, 0,
      0, sumintStep, sumintFinal
  );

  if( rc!=SQLITE_OK ){
    Tcl_SetObjResult(interp, Tcl_NewStringObj(sqlite3ErrName(rc), -1));
    return TCL_ERROR;
  }
  return TCL_OK;
}

int Sqlitetest_window_Init(Tcl_Interp *interp){
  static struct {
     char *zName;
     Tcl_ObjCmdProc *xProc;
     int clientData;
  } aObjCmd[] = {
     { "sqlite3_create_window_function", test_create_window, 0 },
     { "test_create_window_function_misuse", test_create_window_misuse, 0 },
     { "test_create_sumint", test_create_sumint, 0 },
     { "test_override_sum", test_override_sum, 0 },
  };
  int i;
  for(i=0; i<sizeof(aObjCmd)/sizeof(aObjCmd[0]); i++){
    ClientData c = (ClientData)SQLITE_INT_TO_PTR(aObjCmd[i].clientData);
    Tcl_CreateObjCommand(interp, aObjCmd[i].zName, aObjCmd[i].xProc, c, 0);
  }
  return TCL_OK;

    sqlite3VdbeJumpHere(v, addrIfPos2);
  }

  if( pMWin->eStart==TK_CURRENT 
   || pMWin->eStart==TK_PRECEDING 
   || pMWin->eStart==TK_FOLLOWING 
  ){
    int lblSkipInverse = sqlite3VdbeMakeLabel(v);;
    if( pMWin->eStart==TK_PRECEDING ){
      sqlite3VdbeAddOp3(v, OP_IfPos, regStart, lblSkipInverse, 1);
      VdbeCoverage(v);
    }
    if( pMWin->eStart==TK_FOLLOWING ){
      sqlite3VdbeAddOp2(v, OP_Next, csrStart, sqlite3VdbeCurrentAddr(v)+2);
      VdbeCoverage(v);
      sqlite3VdbeAddOp2(v, OP_Goto, 0, lblSkipInverse);
    }else{
      sqlite3VdbeAddOp2(v, OP_Next, csrStart, sqlite3VdbeCurrentAddr(v)+1);
      VdbeCoverage(v);
    }
    windowAggStep(pParse, pMWin, csrStart, 1, regArg, regSize);

    sqlite3VdbeResolveLabel(v, lblSkipInverse);

  }
  if( pMWin->eEnd==TK_FOLLOWING ){
    sqlite3VdbeJumpHere(v, addrIfPos1);
  }
  sqlite3VdbeAddOp2(v, OP_Goto, 0, addrTop);

  /* flush_partition_done: */

    SELECT string_agg(CAST(b AS TEXT), '.') OVER ( ORDER BY b%10,a $window ) FROM t2
  "
  execsql_test 1.$tn.14.6 "
    SELECT string_agg(CAST(b AS TEXT), '.') OVER (PARTITION BY b%2,a ORDER BY b%10 $window) FROM t2
  "

  execsql_test 1.$tn.15.1 "
    SELECT count(*) OVER win, string_agg(CAST(b AS TEXT), '.') 
    FILTER (WHERE a%2=0) OVER win FROM t2
    WINDOW win AS (ORDER BY a $window)
  "

  execsql_test 1.$tn.15.2 "
    SELECT count(*) OVER win, string_agg(CAST(b AS TEXT), '.') 
    FILTER (WHERE 0=1) OVER win FROM t2
    WINDOW win AS (ORDER BY a $window)
  "

  execsql_test 1.$tn.15.3 "
    SELECT count(*) OVER win, string_agg(CAST(b AS TEXT), '.') 
    FILTER (WHERE 1=0) OVER win FROM t2
    WINDOW win AS (PARTITION BY (a%10) ORDER BY a $window)
  "

  execsql_test 1.$tn.15.4 "
    SELECT count(*) OVER win, string_agg(CAST(b AS TEXT), '.') 
    FILTER (WHERE a%2=0) OVER win FROM t2
    WINDOW win AS (PARTITION BY (a%10) ORDER BY a $window)
  "

}

finish_test

do_execsql_test 2.0 {
  SELECT sumint(a) OVER (ORDER BY rowid) FROM t1 ORDER BY rowid;
} {4 10 11 16 18 21}

do_execsql_test 2.1 {
  SELECT sumint(a) OVER (ORDER BY rowid ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM t1 ORDER BY rowid;
} {10 11 12 8 10 5}

test_override_sum db
do_catchsql_test 3.0 {
  SELECT sum(a) OVER 
  (ORDER BY b ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 
  FROM t1;
} {1 {sum() may not be used as a window function}}
do_execsql_test 3.1 {
  SELECT sum(a) FROM t1;
} {21}


finish_test

  SELECT sum(over) over over over FROM over over WINDOW over AS (ORDER BY over);
} {2 6 12}

do_execsql_test 5.4 {
  SELECT sum(window) OVER window window FROM window window window window AS (ORDER BY window);
} {2 6 12}

do_execsql_test 5.5 {
  SELECT count(*) OVER win FROM over
  WINDOW win AS (ORDER BY x ROWS BETWEEN +2 FOLLOWING AND +3 FOLLOWING)
} {1 0 0}

#-------------------------------------------------------------------------
#
do_execsql_test 6.0 {
  SELECT LIKE("!","","!")""WHeRE"";
} {1}
do_catchsql_test 6.1 {
  SELECT LIKE("!","","!")""window"";
} {1 {near "window": syntax error}}

finish_test