/ Check-in [7d66cd2013]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add things to this branch that will be required to support the EXCLUDE clause.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | window-functions
Files: files | file ages | folders
SHA3-256: 7d66cd2013206ebad50c7cdb7dab9211fa8b47f5cb7067dcb314b3e0180875f8
User & Date: dan 2019-03-14 16:36:20
Wiki:window-functions
Context
2019-03-14
20:53
Parse EXCLUDE clauses in window frames. They do not yet work. check-in: d03c7533a1 user: dan tags: window-functions
16:36
Add things to this branch that will be required to support the EXCLUDE clause. check-in: 7d66cd2013 user: dan tags: window-functions
2019-03-13
17:31
Merge latest trunk changes into this branch. check-in: 0b904517bd user: dan tags: window-functions
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/sqliteInt.h.

  3557   3557     char *zBase;            /* Name of base window for chaining (may be NULL) */
  3558   3558     ExprList *pPartition;   /* PARTITION BY clause */
  3559   3559     ExprList *pOrderBy;     /* ORDER BY clause */
  3560   3560     u8 eType;               /* TK_RANGE or TK_ROWS */
  3561   3561     u8 eStart;              /* UNBOUNDED, CURRENT, PRECEDING or FOLLOWING */
  3562   3562     u8 eEnd;                /* UNBOUNDED, CURRENT, PRECEDING or FOLLOWING */
  3563   3563     u8 bImplicitFrame;      /* True if frame was implicitly specified */
         3564  +  u8 eExclude;
  3564   3565     Expr *pStart;           /* Expression for "<expr> PRECEDING" */
  3565   3566     Expr *pEnd;             /* Expression for "<expr> FOLLOWING" */
  3566   3567     Window *pNextWin;       /* Next window function belonging to this SELECT */
  3567   3568     Expr *pFilter;          /* The FILTER expression */
  3568   3569     FuncDef *pFunc;         /* The function */
  3569   3570     int iEphCsr;            /* Partition buffer or Peer buffer */
  3570   3571     int regAccum;
................................................................................
  3572   3573     int csrApp;             /* Function cursor (used by min/max) */
  3573   3574     int regApp;             /* Function register (also used by min/max) */
  3574   3575     int regPart;            /* Array of registers for PARTITION BY values */
  3575   3576     Expr *pOwner;           /* Expression object this window is attached to */
  3576   3577     int nBufferCol;         /* Number of columns in buffer table */
  3577   3578     int iArgCol;            /* Offset of first argument for this function */
  3578   3579     int regFirst;
         3580  +
         3581  +  int regStartRowid;
         3582  +  int regEndRowid;
  3579   3583   };
  3580   3584   
  3581   3585   #ifndef SQLITE_OMIT_WINDOWFUNC
  3582   3586   void sqlite3WindowDelete(sqlite3*, Window*);
  3583   3587   void sqlite3WindowListDelete(sqlite3 *db, Window *p);
  3584   3588   Window *sqlite3WindowAlloc(Parse*, int, int, Expr*, int , Expr*);
  3585   3589   void sqlite3WindowAttach(Parse*, Expr*, Window*);

Changes to src/vdbe.c.

  3608   3608     pCx = allocateCursor(p, pOp->p1, pOrig->nField, -1, CURTYPE_BTREE);
  3609   3609     if( pCx==0 ) goto no_mem;
  3610   3610     pCx->nullRow = 1;
  3611   3611     pCx->isEphemeral = 1;
  3612   3612     pCx->pKeyInfo = pOrig->pKeyInfo;
  3613   3613     pCx->isTable = pOrig->isTable;
  3614   3614     pCx->pgnoRoot = pOrig->pgnoRoot;
         3615  +  pCx->isOrdered = pOrig->isOrdered;
  3615   3616     rc = sqlite3BtreeCursor(pOrig->pBtx, pCx->pgnoRoot, BTREE_WRCSR,
  3616   3617                             pCx->pKeyInfo, pCx->uc.pCursor);
  3617   3618     /* The sqlite3BtreeCursor() routine can only fail for the first cursor
  3618   3619     ** opened for a database.  Since there is already an open cursor when this
  3619   3620     ** opcode is run, the sqlite3BtreeCursor() cannot fail */
  3620   3621     assert( rc==SQLITE_OK );
  3621   3622     break;

Changes to src/window.c.

   194    194         p->nValue++;
   195    195         p->nStep = 0;
   196    196       }
   197    197       sqlite3_result_int64(pCtx, p->nValue);
   198    198     }
   199    199   }
   200    200   
          201  +/*
          202  +** Implementation of built-in window function nth_value(). This
          203  +** implementation is used in "slow mode" only - when the EXCLUDE clause
          204  +** is not set to the default value "NO OTHERS".
          205  +*/
          206  +struct NthValueCtx {
          207  +  i64 nStep;
          208  +  sqlite3_value *pValue;
          209  +};
          210  +static void nth_valueStepFunc(
          211  +  sqlite3_context *pCtx, 
          212  +  int nArg,
          213  +  sqlite3_value **apArg
          214  +){
          215  +  struct NthValueCtx *p;
          216  +  p = (struct NthValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p));
          217  +  if( p ){
          218  +    i64 iVal = sqlite3_value_int64(apArg[1]);
          219  +    p->nStep++;
          220  +    if( iVal==p->nStep ){
          221  +      p->pValue = sqlite3_value_dup(apArg[0]);
          222  +    }
          223  +  }
          224  +  UNUSED_PARAMETER(nArg);
          225  +  UNUSED_PARAMETER(apArg);
          226  +}
          227  +static void nth_valueValueFunc(sqlite3_context *pCtx){
          228  +  struct NthValueCtx *p;
          229  +  p = (struct NthValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p));
          230  +  if( p && p->pValue ){
          231  +    sqlite3_result_value(pCtx, p->pValue);
          232  +  }
          233  +}
          234  +static void nth_valueFinalizeFunc(sqlite3_context *pCtx){
          235  +  struct NthValueCtx *p;
          236  +  p = (struct NthValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p));
          237  +  if( p && p->pValue ){
          238  +    sqlite3_result_value(pCtx, p->pValue);
          239  +    sqlite3_value_free(p->pValue);
          240  +    p->pValue = 0;
          241  +  }
          242  +}
          243  +#define nth_valueInvFunc noopStepFunc
          244  +
   201    245   /*
   202    246   ** Implementation of built-in window function rank(). Assumes that
   203    247   ** the window frame has been set to:
   204    248   **
   205    249   **   RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 
   206    250   */
   207    251   static void rankStepFunc(
................................................................................
   513    557   ** Register those built-in window functions that are not also aggregates.
   514    558   */
   515    559   void sqlite3WindowFunctions(void){
   516    560     static FuncDef aWindowFuncs[] = {
   517    561       WINDOWFUNCX(row_number, 0, 0),
   518    562       WINDOWFUNCX(dense_rank, 0, 0),
   519    563       WINDOWFUNCX(rank, 0, 0),
   520         -    // WINDOWFUNCX(percent_rank, 0, SQLITE_FUNC_WINDOW_SIZE),
   521    564       WINDOWFUNCALL(percent_rank, 0, 0),
   522    565       WINDOWFUNCALL(cume_dist, 0, 0),
   523    566       WINDOWFUNCALL(ntile, 1, 0),
   524         -    // WINDOWFUNCX(cume_dist, 0, SQLITE_FUNC_WINDOW_SIZE),
   525         -    // WINDOWFUNCX(ntile, 1, SQLITE_FUNC_WINDOW_SIZE),
   526    567       WINDOWFUNCALL(last_value, 1, 0),
   527         -    WINDOWFUNCNOOP(nth_value, 2, 0),
          568  +    WINDOWFUNCALL(nth_value, 2, 0),
   528    569       WINDOWFUNCNOOP(first_value, 1, 0),
   529    570       WINDOWFUNCNOOP(lead, 1, 0),
   530    571       WINDOWFUNCNOOP(lead, 2, 0),
   531    572       WINDOWFUNCNOOP(lead, 3, 0),
   532    573       WINDOWFUNCNOOP(lag, 1, 0),
   533    574       WINDOWFUNCNOOP(lag, 2, 0),
   534    575       WINDOWFUNCNOOP(lag, 3, 0),
................................................................................
   618    659           if( pFunc->zName==aUp[i].zFunc ){
   619    660             sqlite3ExprDelete(db, pWin->pStart);
   620    661             sqlite3ExprDelete(db, pWin->pEnd);
   621    662             pWin->pEnd = pWin->pStart = 0;
   622    663             pWin->eType = aUp[i].eType;
   623    664             pWin->eStart = aUp[i].eStart;
   624    665             pWin->eEnd = aUp[i].eEnd;
          666  +          pWin->eExclude = 0;
   625    667             if( pWin->eStart==TK_FOLLOWING ){
   626    668               pWin->pStart = sqlite3Expr(db, TK_INTEGER, "1");
   627    669             }
   628    670             break;
   629    671           }
   630    672         }
   631    673       }
................................................................................
   996   1038     if( pWin==0 ) goto windowAllocErr;
   997   1039     pWin->eType = eType;
   998   1040     pWin->eStart = eStart;
   999   1041     pWin->eEnd = eEnd;
  1000   1042     pWin->bImplicitFrame = bImplicitFrame;
  1001   1043     pWin->pEnd = sqlite3WindowOffsetExpr(pParse, pEnd);
  1002   1044     pWin->pStart = sqlite3WindowOffsetExpr(pParse, pStart);
         1045  +  /* pWin->eExclude = 1; */
  1003   1046     return pWin;
  1004   1047   
  1005   1048   windowAllocErr:
  1006   1049     sqlite3ExprDelete(pParse->db, pEnd);
  1007   1050     sqlite3ExprDelete(pParse->db, pStart);
  1008   1051     return 0;
  1009   1052   }
................................................................................
  1098   1141   ** Return 0 if the two window objects are identical, or non-zero otherwise.
  1099   1142   ** Identical window objects can be processed in a single scan.
  1100   1143   */
  1101   1144   int sqlite3WindowCompare(Parse *pParse, Window *p1, Window *p2){
  1102   1145     if( p1->eType!=p2->eType ) return 1;
  1103   1146     if( p1->eStart!=p2->eStart ) return 1;
  1104   1147     if( p1->eEnd!=p2->eEnd ) return 1;
         1148  +  if( p1->eExclude!=p2->eExclude ) return 1;
  1105   1149     if( sqlite3ExprCompare(pParse, p1->pStart, p2->pStart, -1) ) return 1;
  1106   1150     if( sqlite3ExprCompare(pParse, p1->pEnd, p2->pEnd, -1) ) return 1;
  1107   1151     if( sqlite3ExprListCompare(p1->pPartition, p2->pPartition, -1) ) return 1;
  1108   1152     if( sqlite3ExprListCompare(p1->pOrderBy, p2->pOrderBy, -1) ) return 1;
  1109   1153     return 0;
  1110   1154   }
  1111   1155   
................................................................................
  1126   1170       pMWin->regPart = pParse->nMem+1;
  1127   1171       pParse->nMem += nExpr;
  1128   1172       sqlite3VdbeAddOp3(v, OP_Null, 0, pMWin->regPart, pMWin->regPart+nExpr-1);
  1129   1173     }
  1130   1174   
  1131   1175     pMWin->regFirst = ++pParse->nMem;
  1132   1176     sqlite3VdbeAddOp2(v, OP_Integer, 1, pMWin->regFirst);
         1177  +
         1178  +  if( pMWin->eExclude ){
         1179  +    pMWin->regStartRowid = ++pParse->nMem;
         1180  +    pMWin->regEndRowid = ++pParse->nMem;
         1181  +    pMWin->csrApp = pParse->nTab++;
         1182  +    sqlite3VdbeAddOp2(v, OP_Integer, 1, pMWin->regStartRowid);
         1183  +    sqlite3VdbeAddOp2(v, OP_Integer, 0, pMWin->regEndRowid);
         1184  +    sqlite3VdbeAddOp2(v, OP_OpenDup, pMWin->csrApp, pMWin->iEphCsr);
         1185  +    return;
         1186  +  }
  1133   1187   
  1134   1188     for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
  1135   1189       FuncDef *p = pWin->pFunc;
  1136   1190       if( (p->funcFlags & SQLITE_FUNC_MINMAX) && pWin->eStart!=TK_UNBOUNDED ){
  1137   1191         /* The inline versions of min() and max() require a single ephemeral
  1138   1192         ** table and 3 registers. The registers are used as follows:
  1139   1193         **
................................................................................
  1241   1295   ** number of rows in the current partition.
  1242   1296   */
  1243   1297   static void windowAggStep(
  1244   1298     Parse *pParse, 
  1245   1299     Window *pMWin,                  /* Linked list of window functions */
  1246   1300     int csr,                        /* Read arguments from this cursor */
  1247   1301     int bInverse,                   /* True to invoke xInverse instead of xStep */
  1248         -  int reg,                        /* Array of registers */
  1249         -  int regPartSize                 /* Register containing size of partition */
         1302  +  int reg                         /* Array of registers */
  1250   1303   ){
  1251   1304     Vdbe *v = sqlite3GetVdbe(pParse);
  1252   1305     Window *pWin;
  1253   1306     for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
         1307  +    FuncDef *pFunc = pWin->pFunc;
  1254   1308       int regArg;
  1255   1309       int nArg = windowArgCount(pWin);
         1310  +    int i;
  1256   1311   
  1257         -    if( csr>=0 ){
  1258         -      int i;
  1259         -      for(i=0; i<nArg; i++){
  1260         -        sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol+i, reg+i);
  1261         -      }
  1262         -      regArg = reg;
  1263         -    }else{
  1264         -      regArg = reg + pWin->iArgCol;
         1312  +    for(i=0; i<nArg; i++){
         1313  +      sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol+i, reg+i);
  1265   1314       }
         1315  +    regArg = reg;
  1266   1316   
  1267         -    if( (pWin->pFunc->funcFlags & SQLITE_FUNC_MINMAX) 
  1268         -      && pWin->eStart!=TK_UNBOUNDED 
         1317  +    if( pMWin->regStartRowid==0
         1318  +     && (pFunc->funcFlags & SQLITE_FUNC_MINMAX) 
         1319  +     && (pWin->eStart!=TK_UNBOUNDED)
  1269   1320       ){
  1270   1321         int addrIsNull = sqlite3VdbeAddOp1(v, OP_IsNull, regArg);
  1271   1322         VdbeCoverage(v);
  1272   1323         if( bInverse==0 ){
  1273   1324           sqlite3VdbeAddOp2(v, OP_AddImm, pWin->regApp+1, 1);
  1274   1325           sqlite3VdbeAddOp2(v, OP_SCopy, regArg, pWin->regApp);
  1275   1326           sqlite3VdbeAddOp3(v, OP_MakeRecord, pWin->regApp, 2, pWin->regApp+2);
................................................................................
  1278   1329           sqlite3VdbeAddOp4Int(v, OP_SeekGE, pWin->csrApp, 0, regArg, 1);
  1279   1330           VdbeCoverageNeverTaken(v);
  1280   1331           sqlite3VdbeAddOp1(v, OP_Delete, pWin->csrApp);
  1281   1332           sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2);
  1282   1333         }
  1283   1334         sqlite3VdbeJumpHere(v, addrIsNull);
  1284   1335       }else if( pWin->regApp ){
  1285         -      assert( pWin->pFunc->zName==nth_valueName
  1286         -           || pWin->pFunc->zName==first_valueName
         1336  +      assert( pFunc->zName==nth_valueName
         1337  +           || pFunc->zName==first_valueName
  1287   1338         );
  1288   1339         assert( bInverse==0 || bInverse==1 );
  1289   1340         sqlite3VdbeAddOp2(v, OP_AddImm, pWin->regApp+1-bInverse, 1);
  1290         -    }else if( pWin->pFunc->zName==leadName
  1291         -           || pWin->pFunc->zName==lagName
  1292         -    ){
         1341  +    }else if( pFunc->zName==leadName || pFunc->zName==lagName ){
  1293   1342         /* no-op */
  1294         -    }else{
         1343  +    }else if( pFunc->xSFunc!=noopStepFunc ){
  1295   1344         int addrIf = 0;
  1296   1345         if( pWin->pFilter ){
  1297   1346           int regTmp;
  1298   1347           assert( nArg==0 || nArg==pWin->pOwner->x.pList->nExpr );
  1299   1348           assert( nArg || pWin->pOwner->x.pList==0 );
  1300         -        if( csr>0 ){
  1301         -          regTmp = sqlite3GetTempReg(pParse);
  1302         -          sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol+nArg,regTmp);
  1303         -        }else{
  1304         -          regTmp = regArg + nArg;
  1305         -        }
         1349  +        regTmp = sqlite3GetTempReg(pParse);
         1350  +        sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol+nArg,regTmp);
  1306   1351           addrIf = sqlite3VdbeAddOp3(v, OP_IfNot, regTmp, 0, 1);
  1307   1352           VdbeCoverage(v);
  1308         -        if( csr>0 ){
  1309         -          sqlite3ReleaseTempReg(pParse, regTmp);
  1310         -        }
         1353  +        sqlite3ReleaseTempReg(pParse, regTmp);
  1311   1354         }
  1312         -      if( pWin->pFunc->funcFlags & SQLITE_FUNC_NEEDCOLL ){
         1355  +      if( pFunc->funcFlags & SQLITE_FUNC_NEEDCOLL ){
  1313   1356           CollSeq *pColl;
  1314   1357           assert( nArg>0 );
  1315   1358           pColl = sqlite3ExprNNCollSeq(pParse, pWin->pOwner->x.pList->a[0].pExpr);
  1316   1359           sqlite3VdbeAddOp4(v, OP_CollSeq, 0,0,0, (const char*)pColl, P4_COLLSEQ);
  1317   1360         }
  1318   1361         sqlite3VdbeAddOp3(v, bInverse? OP_AggInverse : OP_AggStep, 
  1319   1362                           bInverse, regArg, pWin->regAccum);
  1320         -      sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
         1363  +      sqlite3VdbeAppendP4(v, pFunc, P4_FUNCDEF);
  1321   1364         sqlite3VdbeChangeP5(v, (u8)nArg);
  1322   1365         if( addrIf ) sqlite3VdbeJumpHere(v, addrIf);
  1323   1366       }
  1324   1367     }
  1325   1368   }
  1326   1369   
  1327   1370   /*
  1328         -** Generate VM code to invoke either xValue() (bFinal==0) or xFinalize()
  1329         -** (bFinal==1) for each window function in the linked list starting at
         1371  +** Generate VM code to invoke either xValue() (bFin==0) or xFinalize()
         1372  +** (bFin==1) for each window function in the linked list starting at
  1330   1373   ** pMWin. Or, for built-in window-functions that do not use the standard
  1331   1374   ** API, generate the equivalent VM code.
  1332   1375   */
  1333         -static void windowAggFinal(Parse *pParse, Window *pMWin, int bFinal){
         1376  +static void windowAggFinal(Parse *pParse, Window *pMWin, int regArg, int bFin){
  1334   1377     Vdbe *v = sqlite3GetVdbe(pParse);
  1335   1378     Window *pWin;
         1379  +
         1380  +  if( pMWin->regStartRowid ){
         1381  +    int addrNext;
         1382  +    int regRowid = sqlite3GetTempReg(pParse);
         1383  +    assert( pMWin->csrApp );
         1384  +    for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
         1385  +      sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regAccum);
         1386  +    }
         1387  +    sqlite3VdbeAddOp3(v, OP_SeekGE, pMWin->csrApp, 0, pMWin->regStartRowid);
         1388  +    addrNext = sqlite3VdbeCurrentAddr(v);
         1389  +    sqlite3VdbeAddOp2(v, OP_Rowid, pMWin->csrApp, regRowid);
         1390  +    sqlite3VdbeAddOp3(v, OP_Gt, pMWin->regEndRowid, 0, regRowid);
         1391  +    windowAggStep(pParse, pMWin, pMWin->csrApp, 0, regArg);
         1392  +    sqlite3VdbeAddOp2(v, OP_Next, pMWin->csrApp, addrNext);
         1393  +    sqlite3VdbeJumpHere(v, addrNext-1);
         1394  +    sqlite3VdbeJumpHere(v, addrNext+1);
         1395  +    sqlite3ReleaseTempReg(pParse, regRowid);
         1396  +  }
  1336   1397   
  1337   1398     for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
  1338         -    if( (pWin->pFunc->funcFlags & SQLITE_FUNC_MINMAX) 
  1339         -     && pWin->eStart!=TK_UNBOUNDED 
         1399  +    if( pMWin->regStartRowid==0
         1400  +     && (pWin->pFunc->funcFlags & SQLITE_FUNC_MINMAX) 
         1401  +     && (pWin->eStart!=TK_UNBOUNDED)
  1340   1402       ){
  1341   1403         sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regResult);
  1342   1404         sqlite3VdbeAddOp1(v, OP_Last, pWin->csrApp);
  1343   1405         VdbeCoverage(v);
  1344   1406         sqlite3VdbeAddOp3(v, OP_Column, pWin->csrApp, 0, pWin->regResult);
  1345   1407         sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2);
  1346         -      if( bFinal ){
         1408  +      if( bFin ){
  1347   1409           sqlite3VdbeAddOp1(v, OP_ResetSorter, pWin->csrApp);
  1348   1410         }
  1349   1411       }else if( pWin->regApp ){
         1412  +      assert( pMWin->regStartRowid==0 );
  1350   1413       }else{
  1351         -      if( bFinal ){
  1352         -        sqlite3VdbeAddOp2(v, OP_AggFinal, pWin->regAccum, windowArgCount(pWin));
         1414  +      int nArg = windowArgCount(pWin);
         1415  +      if( bFin ){
         1416  +        sqlite3VdbeAddOp2(v, OP_AggFinal, pWin->regAccum, nArg);
  1353   1417           sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
  1354   1418           sqlite3VdbeAddOp2(v, OP_Copy, pWin->regAccum, pWin->regResult);
  1355   1419           sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regAccum);
  1356   1420         }else{
  1357         -        sqlite3VdbeAddOp3(v, OP_AggValue, pWin->regAccum, windowArgCount(pWin),
  1358         -                             pWin->regResult);
         1421  +        sqlite3VdbeAddOp3(v, OP_AggValue,pWin->regAccum,nArg,pWin->regResult);
  1359   1422           sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
  1360   1423         }
  1361   1424       }
  1362   1425     }
  1363   1426   }
  1364   1427   
  1365   1428   /*
................................................................................
  1384   1447     Vdbe *v = sqlite3GetVdbe(pParse);
  1385   1448     Window *pWin;
  1386   1449     for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
  1387   1450       FuncDef *pFunc = pWin->pFunc;
  1388   1451       if( pFunc->zName==nth_valueName
  1389   1452        || pFunc->zName==first_valueName
  1390   1453       ){
  1391         -      int csr = pWin->csrApp;
  1392   1454         int lbl = sqlite3VdbeMakeLabel(pParse);
  1393   1455         int tmpReg = sqlite3GetTempReg(pParse);
  1394   1456         sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regResult);
  1395   1457   
  1396   1458         if( pFunc->zName==nth_valueName ){
  1397         -        sqlite3VdbeAddOp3(v, OP_Column, pMWin->iEphCsr, pWin->iArgCol+1,tmpReg);
         1459  +        sqlite3VdbeAddOp3(v, OP_Column,pMWin->iEphCsr,pWin->iArgCol+1,tmpReg);
  1398   1460           windowCheckValue(pParse, tmpReg, 2);
  1399   1461         }else{
  1400   1462           sqlite3VdbeAddOp2(v, OP_Integer, 1, tmpReg);
  1401   1463         }
  1402         -      sqlite3VdbeAddOp3(v, OP_Add, tmpReg, pWin->regApp, tmpReg);
  1403         -      sqlite3VdbeAddOp3(v, OP_Gt, pWin->regApp+1, lbl, tmpReg);
  1404         -      VdbeCoverageNeverNull(v);
  1405         -      sqlite3VdbeAddOp3(v, OP_SeekRowid, csr, 0, tmpReg);
  1406         -      VdbeCoverageNeverTaken(v);
  1407         -      sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol, pWin->regResult);
         1464  +      if( pWin->eExclude==0 ){
         1465  +        int csr = pWin->csrApp;
         1466  +        sqlite3VdbeAddOp3(v, OP_Add, tmpReg, pWin->regApp, tmpReg);
         1467  +        sqlite3VdbeAddOp3(v, OP_Gt, pWin->regApp+1, lbl, tmpReg);
         1468  +        VdbeCoverageNeverNull(v);
         1469  +        sqlite3VdbeAddOp3(v, OP_SeekRowid, csr, 0, tmpReg);
         1470  +        VdbeCoverageNeverTaken(v);
         1471  +        sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol, pWin->regResult);
         1472  +      }else{
         1473  +        int regRowid = sqlite3GetTempReg(pParse);
         1474  +        int csr = pMWin->csrApp;
         1475  +        int addrNext;
         1476  +        sqlite3VdbeAddOp3(v, OP_IfPos, tmpReg, sqlite3VdbeCurrentAddr(v)+1, 1);
         1477  +        sqlite3VdbeAddOp3(v, OP_SeekGE, csr, lbl, pMWin->regStartRowid);
         1478  +        addrNext = sqlite3VdbeAddOp2(v, OP_Rowid, csr, regRowid);
         1479  +        sqlite3VdbeAddOp3(v, OP_Gt, pMWin->regEndRowid, lbl, regRowid);
         1480  +        sqlite3VdbeAddOp3(v, OP_IfPos, tmpReg, sqlite3VdbeCurrentAddr(v)+3, 1);
         1481  +        sqlite3VdbeAddOp3(v, OP_Column, csr, pMWin->iArgCol, pWin->regResult);
         1482  +        sqlite3VdbeAddOp2(v, OP_Goto, 0, lbl);
         1483  +        sqlite3VdbeAddOp2(v, OP_Next, csr, addrNext);
         1484  +        sqlite3ReleaseTempReg(pParse, regRowid);
         1485  +      }
  1408   1486         sqlite3VdbeResolveLabel(v, lbl);
  1409   1487         sqlite3ReleaseTempReg(pParse, tmpReg);
  1410   1488       }
  1411   1489       else if( pFunc->zName==leadName || pFunc->zName==lagName ){
  1412   1490         int nArg = pWin->pOwner->x.pList->nExpr;
  1413   1491         int csr = pWin->csrApp;
  1414   1492         int lbl = sqlite3VdbeMakeLabel(pParse);
................................................................................
  1453   1531     int regArg;
  1454   1532     int nArg = 0;
  1455   1533     Window *pWin;
  1456   1534     for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
  1457   1535       FuncDef *pFunc = pWin->pFunc;
  1458   1536       sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regAccum);
  1459   1537       nArg = MAX(nArg, windowArgCount(pWin));
  1460         -    if( pFunc->zName==nth_valueName
  1461         -     || pFunc->zName==first_valueName
  1462         -    ){
  1463         -      sqlite3VdbeAddOp2(v, OP_Integer, 0, pWin->regApp);
  1464         -      sqlite3VdbeAddOp2(v, OP_Integer, 0, pWin->regApp+1);
  1465         -    }
         1538  +    if( pWin->eExclude==0 ){
         1539  +      if( pFunc->zName==nth_valueName || pFunc->zName==first_valueName ){
         1540  +        sqlite3VdbeAddOp2(v, OP_Integer, 0, pWin->regApp);
         1541  +        sqlite3VdbeAddOp2(v, OP_Integer, 0, pWin->regApp+1);
         1542  +      }
  1466   1543   
  1467         -    if( (pFunc->funcFlags & SQLITE_FUNC_MINMAX) && pWin->csrApp ){
  1468         -      assert( pWin->eStart!=TK_UNBOUNDED );
  1469         -      sqlite3VdbeAddOp1(v, OP_ResetSorter, pWin->csrApp);
  1470         -      sqlite3VdbeAddOp2(v, OP_Integer, 0, pWin->regApp+1);
         1544  +      if( (pFunc->funcFlags & SQLITE_FUNC_MINMAX) && pWin->csrApp ){
         1545  +        assert( pWin->eStart!=TK_UNBOUNDED );
         1546  +        sqlite3VdbeAddOp1(v, OP_ResetSorter, pWin->csrApp);
         1547  +        sqlite3VdbeAddOp2(v, OP_Integer, 0, pWin->regApp+1);
         1548  +      }
  1471   1549       }
  1472   1550     }
  1473   1551     regArg = pParse->nMem+1;
  1474   1552     pParse->nMem += nArg;
  1475   1553     return regArg;
  1476   1554   }
  1477   1555   
  1478   1556   /* 
  1479   1557   ** Return true if the current frame should be cached in the ephemeral table,
  1480   1558   ** even if there are no xInverse() calls required.
  1481   1559   */
  1482   1560   static int windowCacheFrame(Window *pMWin){
  1483   1561     Window *pWin;
         1562  +  if( pMWin->regStartRowid ) return 1;
  1484   1563     for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
  1485   1564       FuncDef *pFunc = pWin->pFunc;
  1486   1565       if( (pFunc->zName==nth_valueName)
  1487   1566        || (pFunc->zName==first_valueName)
  1488   1567        || (pFunc->zName==leadName)
  1489   1568        || (pFunc->zName==lagName)
  1490   1569       ){
................................................................................
  1680   1759   
  1681   1760       }else{
  1682   1761         addrIf = sqlite3VdbeAddOp3(v, OP_IfPos, regCountdown, 0, 1);
  1683   1762       }
  1684   1763     }
  1685   1764   
  1686   1765     if( op==WINDOW_RETURN_ROW ){
  1687         -    windowAggFinal(pParse, pMWin, 0);
         1766  +    windowAggFinal(pParse, pMWin, p->regArg, 0);
  1688   1767     }
  1689   1768     addrContinue = sqlite3VdbeCurrentAddr(v);
  1690   1769     switch( op ){
  1691   1770       case WINDOW_RETURN_ROW:
  1692   1771         csr = p->current.csr;
  1693   1772         reg = p->current.reg;
  1694   1773         windowReturnOneRow(pParse, pMWin, p->regGosub, p->addrGosub);
  1695   1774         break;
  1696   1775   
  1697   1776       case WINDOW_AGGINVERSE:
  1698   1777         csr = p->start.csr;
  1699   1778         reg = p->start.reg;
  1700         -      windowAggStep(pParse, pMWin, csr, 1, p->regArg, 0);
         1779  +      if( pMWin->regStartRowid ){
         1780  +        assert( pMWin->regEndRowid );
         1781  +        sqlite3VdbeAddOp2(v, OP_AddImm, pMWin->regStartRowid, 1);
         1782  +      }else{
         1783  +        windowAggStep(pParse, pMWin, csr, 1, p->regArg);
         1784  +      }
  1701   1785         break;
  1702   1786   
  1703   1787       case WINDOW_AGGSTEP:
  1704   1788         csr = p->end.csr;
  1705   1789         reg = p->end.reg;
  1706         -      windowAggStep(pParse, pMWin, csr, 0, p->regArg, 0);
         1790  +      if( pMWin->regStartRowid ){
         1791  +        assert( pMWin->regEndRowid );
         1792  +        sqlite3VdbeAddOp2(v, OP_AddImm, pMWin->regEndRowid, 1);
         1793  +      }else{
         1794  +        windowAggStep(pParse, pMWin, csr, 0, p->regArg);
         1795  +      }
  1707   1796         break;
  1708   1797     }
  1709   1798   
  1710   1799     if( op==p->eDelete ){
  1711   1800       sqlite3VdbeAddOp1(v, OP_Delete, csr);
  1712   1801       sqlite3VdbeChangeP5(v, OPFLAG_SAVEPOSITION);
  1713   1802     }
................................................................................
  2275   2364       sqlite3ExprCode(pParse, pMWin->pEnd, regEnd);
  2276   2365       windowCheckValue(pParse, regEnd, 1 + (pMWin->eType==TK_RANGE ? 3 : 0));
  2277   2366     }
  2278   2367   
  2279   2368     if( pMWin->eStart==pMWin->eEnd && regStart && regEnd ){
  2280   2369       int op = ((pMWin->eStart==TK_FOLLOWING) ? OP_Ge : OP_Le);
  2281   2370       int addrGe = sqlite3VdbeAddOp3(v, op, regStart, 0, regEnd);
  2282         -    windowAggFinal(pParse, pMWin, 0);
         2371  +    windowAggFinal(pParse, pMWin, s.regArg, 0);
  2283   2372       sqlite3VdbeAddOp2(v, OP_Rewind, s.current.csr, 1);
  2284   2373       windowReturnOneRow(pParse, pMWin, regGosub, addrGosub);
  2285   2374       sqlite3VdbeAddOp1(v, OP_ResetSorter, s.current.csr);
  2286   2375       sqlite3VdbeAddOp2(v, OP_Goto, 0, lblWhereEnd);
  2287   2376       sqlite3VdbeJumpHere(v, addrGe);
  2288   2377     }
  2289   2378     if( pMWin->eStart==TK_FOLLOWING && pMWin->eType!=TK_RANGE && regEnd ){
................................................................................
  2410   2499       sqlite3VdbeJumpHere(v, addrBreak);
  2411   2500     }
  2412   2501     sqlite3VdbeJumpHere(v, addrEmpty);
  2413   2502   
  2414   2503     sqlite3VdbeAddOp1(v, OP_ResetSorter, s.current.csr);
  2415   2504     sqlite3VdbeAddOp2(v, OP_Integer, 1, pMWin->regFirst);
  2416   2505     if( pMWin->pPartition ){
         2506  +    if( pMWin->regStartRowid ){
         2507  +      sqlite3VdbeAddOp2(v, OP_Integer, 1, pMWin->regStartRowid);
         2508  +      sqlite3VdbeAddOp2(v, OP_Integer, 0, pMWin->regEndRowid);
         2509  +    }
  2417   2510       sqlite3VdbeChangeP1(v, addrInteger, sqlite3VdbeCurrentAddr(v));
  2418   2511       sqlite3VdbeAddOp1(v, OP_Return, regFlushPart);
  2419   2512     }
  2420   2513   }
  2421   2514   
  2422   2515   #endif /* SQLITE_OMIT_WINDOWFUNC */

Changes to test/window8.tcl.

    85     85       SELECT a, b, max(c) OVER (ORDER BY a,b $frame) FROM t3 ORDER BY 1, 2, 3;
    86     86     "
    87     87     execsql_test 1.$tn.5 "
    88     88       SELECT a, b, min(c) OVER (ORDER BY a,b $frame) FROM t3 ORDER BY 1, 2, 3;
    89     89     "
    90     90   }
    91     91   
           92  +
           93  +foreach {tn ex} {
           94  +  1  { EXCLUDE NO OTHERS }
           95  +  2  { EXCLUDE CURRENT ROW }
           96  +  3  { EXCLUDE GROUP }
           97  +  4  { EXCLUDE TIES }
           98  +} {
           99  +  execsql_test 2.$tn.1 "
          100  +    SELECT row_number() OVER win 
          101  +    FROM t3
          102  +    WINDOW win AS (
          103  +      ORDER BY c, b, a
          104  +      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING $ex
          105  +    )
          106  +  "
          107  +
          108  +  execsql_test 2.$tn.2 "
          109  +    SELECT nth_value(c, 14) OVER win 
          110  +    FROM t3
          111  +    WINDOW win AS (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING $ex)
          112  +  "
          113  +}
          114  +
    92    115   ==========
    93    116   
    94         -execsql_test 2.0 {
          117  +execsql_test 3.0 {
    95    118     DROP TABLE IF EXISTS t1;
    96    119     CREATE TABLE t1(a REAL, b INTEGER);
    97    120     INSERT INTO t1 VALUES
    98    121         (5, 10), (10, 20), (13, 26), (13, 26), 
    99    122         (15, 30), (20, 40), (22,80), (30, 90);
   100    123   }
   101    124   
................................................................................
   110    133     7  { ORDER BY a RANGE BETWEEN 5.1 PRECEDING AND 5.3 FOLLOWING }
   111    134     8  { ORDER BY a RANGE BETWEEN 10.2 PRECEDING AND 5.4 PRECEDING }
   112    135     9  { ORDER BY a RANGE BETWEEN 2.6 FOLLOWING AND 3.5 FOLLOWING }
   113    136     10 { ORDER BY a DESC RANGE BETWEEN 5.7 PRECEDING AND 5.8 FOLLOWING }
   114    137     11 { ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND 5.9 PRECEDING }
   115    138     12 { ORDER BY a DESC RANGE BETWEEN 2.1 FOLLOWING AND UNBOUNDED FOLLOWING }
   116    139   } {
   117         -  execsql_test 2.$tn "
          140  +  execsql_test 3.$tn "
   118    141       SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ($frame)
   119    142     "
   120    143   }
   121    144   
   122    145   
   123    146   finish_test
   124    147   
   125    148   

Changes to test/window8.test.

   429    429     SELECT a, b, max(c) OVER (ORDER BY a,b  GROUPS BETWEEN 1 FOLLOWING         AND UNBOUNDED FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
   430    430   } {AA aa 979   AA aa 979   AA aa 979   AA aa 979   AA bb 979   AA bb 979   AA bb 979   AA bb 979   BB aa 979   BB aa 979   BB aa 979   BB aa 979   BB aa 979   BB aa 979   BB bb 979   BB bb 979   BB bb 979   BB bb 979   BB bb 979   BB bb 979   CC aa 979   CC aa 979   CC aa 979   CC aa 979   CC bb 979   CC bb 979   DD aa 979   DD aa 979   DD aa 979   DD bb 979   DD bb 979   DD bb 979   DD bb 979   EE aa 979   EE aa 979   EE bb 979   EE bb 979   EE bb 979   FF aa 979   FF aa 979   FF aa 979   FF aa 979   FF bb 979   FF bb 979   FF bb 979   FF bb 979   FF bb 979   FF bb 979   GG aa 979   GG aa 979   GG aa 979   GG aa 979   GG bb 979   GG bb 979   GG bb 979   GG bb 979   HH aa 963   HH aa 963   HH aa 963   HH bb 899   HH bb 899   HH bb 899   HH bb 899   HH bb 899   HH bb 899   II aa 899   II aa 899   II bb 899   II bb 899   II bb 899   II bb 899   II bb 899   JJ aa 839   JJ aa 839   JJ aa 839   JJ aa 839   JJ bb {}   JJ bb {}   JJ bb {}   JJ bb {}}
   431    431   
   432    432   do_execsql_test 1.19.5 {
   433    433     SELECT a, b, min(c) OVER (ORDER BY a,b  GROUPS BETWEEN 1 FOLLOWING         AND UNBOUNDED FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
   434    434   } {AA aa 102   AA aa 102   AA aa 102   AA aa 102   AA bb 102   AA bb 102   AA bb 102   AA bb 102   BB aa 102   BB aa 102   BB aa 102   BB aa 102   BB aa 102   BB aa 102   BB bb 102   BB bb 102   BB bb 102   BB bb 102   BB bb 102   BB bb 102   CC aa 102   CC aa 102   CC aa 102   CC aa 102   CC bb 102   CC bb 102   DD aa 102   DD aa 102   DD aa 102   DD bb 102   DD bb 102   DD bb 102   DD bb 102   EE aa 102   EE aa 102   EE bb 102   EE bb 102   EE bb 102   FF aa 102   FF aa 102   FF aa 102   FF aa 102   FF bb 113   FF bb 113   FF bb 113   FF bb 113   FF bb 113   FF bb 113   GG aa 113   GG aa 113   GG aa 113   GG aa 113   GG bb 113   GG bb 113   GG bb 113   GG bb 113   HH aa 113   HH aa 113   HH aa 113   HH bb 113   HH bb 113   HH bb 113   HH bb 113   HH bb 113   HH bb 113   II aa 113   II aa 113   II bb 113   II bb 113   II bb 113   II bb 113   II bb 113   JJ aa 257   JJ aa 257   JJ aa 257   JJ aa 257   JJ bb {}   JJ bb {}   JJ bb {}   JJ bb {}}
   435    435   
          436  +do_execsql_test 2.1.1 {
          437  +  SELECT row_number() OVER win 
          438  +    FROM t3
          439  +    WINDOW win AS (
          440  +      ORDER BY c, b, a
          441  +      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  EXCLUDE NO OTHERS 
          442  +    )
          443  +} {1   2   3   4   5   6   7   8   9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   32   33   34   35   36   37   38   39   40   41   42   43   44   45   46   47   48   49   50   51   52   53   54   55   56   57   58   59   60   61   62   63   64   65   66   67   68   69   70   71   72   73   74   75   76   77   78   79   80}
          444  +
          445  +do_execsql_test 2.1.2 {
          446  +  SELECT nth_value(c, 14) OVER win 
          447  +    FROM t3
          448  +    WINDOW win AS (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  EXCLUDE NO OTHERS )
          449  +} {938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938}
          450  +
          451  +do_execsql_test 2.2.1 {
          452  +  SELECT row_number() OVER win 
          453  +    FROM t3
          454  +    WINDOW win AS (
          455  +      ORDER BY c, b, a
          456  +      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  EXCLUDE CURRENT ROW 
          457  +    )
          458  +} {1   2   3   4   5   6   7   8   9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   32   33   34   35   36   37   38   39   40   41   42   43   44   45   46   47   48   49   50   51   52   53   54   55   56   57   58   59   60   61   62   63   64   65   66   67   68   69   70   71   72   73   74   75   76   77   78   79   80}
          459  +
          460  +do_execsql_test 2.2.2 {
          461  +  SELECT nth_value(c, 14) OVER win 
          462  +    FROM t3
          463  +    WINDOW win AS (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  EXCLUDE CURRENT ROW )
          464  +} {660   660   660   660   660   660   660   660   660   660   660   660   660   660   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938}
          465  +
          466  +do_execsql_test 2.3.1 {
          467  +  SELECT row_number() OVER win 
          468  +    FROM t3
          469  +    WINDOW win AS (
          470  +      ORDER BY c, b, a
          471  +      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  EXCLUDE GROUP 
          472  +    )
          473  +} {1   2   3   4   5   6   7   8   9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   32   33   34   35   36   37   38   39   40   41   42   43   44   45   46   47   48   49   50   51   52   53   54   55   56   57   58   59   60   61   62   63   64   65   66   67   68   69   70   71   72   73   74   75   76   77   78   79   80}
          474  +
          475  +do_execsql_test 2.3.2 {
          476  +  SELECT nth_value(c, 14) OVER win 
          477  +    FROM t3
          478  +    WINDOW win AS (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  EXCLUDE GROUP )
          479  +} {{}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}}
          480  +
          481  +do_execsql_test 2.4.1 {
          482  +  SELECT row_number() OVER win 
          483  +    FROM t3
          484  +    WINDOW win AS (
          485  +      ORDER BY c, b, a
          486  +      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  EXCLUDE TIES 
          487  +    )
          488  +} {1   2   3   4   5   6   7   8   9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   32   33   34   35   36   37   38   39   40   41   42   43   44   45   46   47   48   49   50   51   52   53   54   55   56   57   58   59   60   61   62   63   64   65   66   67   68   69   70   71   72   73   74   75   76   77   78   79   80}
          489  +
          490  +do_execsql_test 2.4.2 {
          491  +  SELECT nth_value(c, 14) OVER win 
          492  +    FROM t3
          493  +    WINDOW win AS (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  EXCLUDE TIES )
          494  +} {{}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}}
          495  +
   436    496   #==========================================================================
   437    497   
   438         -do_execsql_test 2.0 {
          498  +do_execsql_test 3.0 {
   439    499     DROP TABLE IF EXISTS t1;
   440    500     CREATE TABLE t1(a REAL, b INTEGER);
   441    501     INSERT INTO t1 VALUES
   442    502         (5, 10), (10, 20), (13, 26), (13, 26), 
   443    503         (15, 30), (20, 40), (22,80), (30, 90);
   444    504   } {}
   445    505   
   446         -do_execsql_test 2.1 {
          506  +do_execsql_test 3.1 {
   447    507     SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING )
   448    508   } {5 30   10 112   13 102   13 102   15 142   20 150   22 120   30 90}
   449    509   
   450         -do_execsql_test 2.2 {
          510  +do_execsql_test 3.2 {
   451    511     SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING )
   452    512   } {5 {}   10 10   13 10   13 10   15 30   20 102   22 82   30 120}
   453    513   
   454         -do_execsql_test 2.3 {
          514  +do_execsql_test 3.3 {
   455    515     SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING )
   456    516   } {5 {}   10 52   13 30   13 30   15 {}   20 80   22 {}   30 {}}
   457    517   
   458         -do_execsql_test 2.4 {
          518  +do_execsql_test 3.4 {
   459    519     SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING )
   460    520   } {30 90   22 120   20 150   15 142   13 102   13 102   10 112   5 30}
   461    521   
   462         -do_execsql_test 2.5 {
          522  +do_execsql_test 3.5 {
   463    523     SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING )
   464    524   } {30 {}   22 90   20 90   15 120   13 120   13 120   10 70   5 102}
   465    525   
   466         -do_execsql_test 2.6 {
          526  +do_execsql_test 3.6 {
   467    527     SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING )
   468    528   } {30 {}   22 40   20 {}   15 52   13 20   13 20   10 {}   5 {}}
   469    529   
   470         -do_execsql_test 2.7 {
          530  +do_execsql_test 3.7 {
   471    531     SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 5.1 PRECEDING AND 5.3 FOLLOWING )
   472    532   } {5 30   10 112   13 102   13 102   15 142   20 150   22 120   30 90}
   473    533   
   474         -do_execsql_test 2.8 {
          534  +do_execsql_test 3.8 {
   475    535     SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 10.2 PRECEDING AND 5.4 PRECEDING )
   476    536   } {5 {}   10 {}   13 10   13 10   15 10   20 72   22 82   30 120}
   477    537   
   478         -do_execsql_test 2.9 {
          538  +do_execsql_test 3.9 {
   479    539     SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 2.6 FOLLOWING AND 3.5 FOLLOWING )
   480    540   } {5 {}   10 52   13 {}   13 {}   15 {}   20 {}   22 {}   30 {}}
   481    541   
   482         -do_execsql_test 2.10 {
          542  +do_execsql_test 3.10 {
   483    543     SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 5.7 PRECEDING AND 5.8 FOLLOWING )
   484    544   } {30 90   22 120   20 150   15 142   13 102   13 102   10 112   5 30}
   485    545   
   486         -do_execsql_test 2.11 {
          546  +do_execsql_test 3.11 {
   487    547     SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND 5.9 PRECEDING )
   488    548   } {30 {}   22 90   20 90   15 170   13 210   13 210   10 210   5 292}
   489    549   
   490         -do_execsql_test 2.12 {
          550  +do_execsql_test 3.12 {
   491    551     SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 2.1 FOLLOWING AND UNBOUNDED FOLLOWING )
   492    552   } {30 232   22 112   20 112   15 30   13 30   13 30   10 10   5 {}}
   493    553   
   494    554   finish_test