/ Check-in [3781e520]
Login

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

Overview
Comment:Start of experimental implementation of SQL window functions. Does not yet work.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | exp-window-functions
Files: files | file ages | folders
SHA3-256:3781e520854808fe02ad3fe77dd11fc917448c58ff1fd79123289dd91937decd
User & Date: dan 2018-05-16 20:58:07
Context
2018-05-17
14:26
Handle multiple window-functions in a single query. check-in: 35af0b75 user: dan tags: exp-window-functions
2018-05-16
20:58
Start of experimental implementation of SQL window functions. Does not yet work. check-in: 3781e520 user: dan tags: exp-window-functions
15:35
Enhance the sqlite3_str_new() interface so that it always returns a valid and non-NULL pointer even in an OOM condition. check-in: ed5b0968 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to main.mk.

    71     71            pager.o pcache.o pcache1.o pragma.o prepare.o printf.o \
    72     72            random.o resolve.o rowset.o rtree.o \
    73     73            select.o sqlite3rbu.o status.o stmt.o \
    74     74            table.o threads.o tokenize.o treeview.o trigger.o \
    75     75            update.o upsert.o userauth.o util.o vacuum.o \
    76     76            vdbeapi.o vdbeaux.o vdbeblob.o vdbemem.o vdbesort.o \
    77     77   	 vdbetrace.o wal.o walker.o where.o wherecode.o whereexpr.o \
    78         -         utf.o vtab.o
           78  +         utf.o vtab.o window.o
    79     79   
    80     80   LIBOBJ += sqlite3session.o
    81     81   
    82     82   # All of the source code files.
    83     83   #
    84     84   SRC = \
    85     85     $(TOP)/src/alter.c \

Changes to src/analyze.c.

   481    481   static const FuncDef statInitFuncdef = {
   482    482     2+IsStat34,      /* nArg */
   483    483     SQLITE_UTF8,     /* funcFlags */
   484    484     0,               /* pUserData */
   485    485     0,               /* pNext */
   486    486     statInit,        /* xSFunc */
   487    487     0,               /* xFinalize */
          488  +  0, 0,
   488    489     "stat_init",     /* zName */
   489    490     {0}
   490    491   };
   491    492   
   492    493   #ifdef SQLITE_ENABLE_STAT4
   493    494   /*
   494    495   ** pNew and pOld are both candidate non-periodic samples selected for 
................................................................................
   797    798   static const FuncDef statPushFuncdef = {
   798    799     2+IsStat34,      /* nArg */
   799    800     SQLITE_UTF8,     /* funcFlags */
   800    801     0,               /* pUserData */
   801    802     0,               /* pNext */
   802    803     statPush,        /* xSFunc */
   803    804     0,               /* xFinalize */
          805  +  0, 0,
   804    806     "stat_push",     /* zName */
   805    807     {0}
   806    808   };
   807    809   
   808    810   #define STAT_GET_STAT1 0          /* "stat" column of stat1 table */
   809    811   #define STAT_GET_ROWID 1          /* "rowid" column of stat[34] entry */
   810    812   #define STAT_GET_NEQ   2          /* "neq" column of stat[34] entry */
................................................................................
   948    950   static const FuncDef statGetFuncdef = {
   949    951     1+IsStat34,      /* nArg */
   950    952     SQLITE_UTF8,     /* funcFlags */
   951    953     0,               /* pUserData */
   952    954     0,               /* pNext */
   953    955     statGet,         /* xSFunc */
   954    956     0,               /* xFinalize */
          957  +  0, 0,
   955    958     "stat_get",      /* zName */
   956    959     {0}
   957    960   };
   958    961   
   959    962   static void callStatGet(Vdbe *v, int regStat4, int iParam, int regOut){
   960    963     assert( regOut!=regStat4 && regOut!=regStat4+1 );
   961    964   #ifdef SQLITE_ENABLE_STAT3_OR_STAT4

Changes to src/attach.c.

   410    410     static const FuncDef detach_func = {
   411    411       1,                /* nArg */
   412    412       SQLITE_UTF8,      /* funcFlags */
   413    413       0,                /* pUserData */
   414    414       0,                /* pNext */
   415    415       detachFunc,       /* xSFunc */
   416    416       0,                /* xFinalize */
          417  +    0, 0,
   417    418       "sqlite_detach",  /* zName */
   418    419       {0}
   419    420     };
   420    421     codeAttach(pParse, SQLITE_DETACH, &detach_func, pDbname, 0, 0, pDbname);
   421    422   }
   422    423   
   423    424   /*
................................................................................
   429    430     static const FuncDef attach_func = {
   430    431       3,                /* nArg */
   431    432       SQLITE_UTF8,      /* funcFlags */
   432    433       0,                /* pUserData */
   433    434       0,                /* pNext */
   434    435       attachFunc,       /* xSFunc */
   435    436       0,                /* xFinalize */
          437  +    0, 0,
   436    438       "sqlite_attach",  /* zName */
   437    439       {0}
   438    440     };
   439    441     codeAttach(pParse, SQLITE_ATTACH, &attach_func, p, p, pDbname, pKey);
   440    442   }
   441    443   #endif /* SQLITE_OMIT_ATTACH */
   442    444   

Changes to src/expr.c.

  1059   1059       if( p->pRight ){
  1060   1060         sqlite3ExprDeleteNN(db, p->pRight);
  1061   1061       }else if( ExprHasProperty(p, EP_xIsSelect) ){
  1062   1062         sqlite3SelectDelete(db, p->x.pSelect);
  1063   1063       }else{
  1064   1064         sqlite3ExprListDelete(db, p->x.pList);
  1065   1065       }
         1066  +    if( !ExprHasProperty(p, EP_Reduced) ){
         1067  +      sqlite3WindowDelete(db, p->pWin);
         1068  +    }
  1066   1069     }
  1067   1070     if( ExprHasProperty(p, EP_MemToken) ) sqlite3DbFree(db, p->u.zToken);
  1068   1071     if( !ExprHasProperty(p, EP_Static) ){
  1069   1072       sqlite3DbFreeNN(db, p);
  1070   1073     }
  1071   1074   }
  1072   1075   void sqlite3ExprDelete(sqlite3 *db, Expr *p){
................................................................................
  1300   1303       }
  1301   1304     }
  1302   1305     return pRet;
  1303   1306   }
  1304   1307   #else
  1305   1308   # define withDup(x,y) 0
  1306   1309   #endif
         1310  +
         1311  +static Window *winDup(sqlite3 *db, Window *p){
         1312  +  Window *pNew = 0;
         1313  +  if( p ){
         1314  +    pNew = sqlite3DbMallocZero(db, sizeof(Window));
         1315  +    if( pNew ){
         1316  +      pNew->pFilter = sqlite3ExprDup(db, p->pFilter, 0);
         1317  +      pNew->pPartition = sqlite3ExprListDup(db, p->pPartition, 0);
         1318  +      pNew->pOrderBy = sqlite3ExprListDup(db, p->pOrderBy, 0);
         1319  +      pNew->eType = p->eType;
         1320  +      pNew->eEnd = p->eEnd;
         1321  +      pNew->eStart = p->eStart;
         1322  +      pNew->pStart = sqlite3ExprDup(db, pNew->pStart, 0);
         1323  +      pNew->pEnd = sqlite3ExprDup(db, pNew->pEnd, 0);
         1324  +    }
         1325  +  }
         1326  +  return pNew;
         1327  +}
  1307   1328   
  1308   1329   /*
  1309   1330   ** The following group of routines make deep copies of expressions,
  1310   1331   ** expression lists, ID lists, and select statements.  The copies can
  1311   1332   ** be deleted (by being passed to their respective ...Delete() routines)
  1312   1333   ** without effecting the originals.
  1313   1334   **
................................................................................
  1465   1486       pNew->iLimit = 0;
  1466   1487       pNew->iOffset = 0;
  1467   1488       pNew->selFlags = p->selFlags & ~SF_UsesEphemeral;
  1468   1489       pNew->addrOpenEphm[0] = -1;
  1469   1490       pNew->addrOpenEphm[1] = -1;
  1470   1491       pNew->nSelectRow = p->nSelectRow;
  1471   1492       pNew->pWith = withDup(db, p->pWith);
         1493  +    pNew->pWin = winDup(db, p->pWin);
  1472   1494       sqlite3SelectSetName(pNew, p->zSelName);
  1473   1495       *pp = pNew;
  1474   1496       pp = &pNew->pPrior;
  1475   1497       pNext = pNew;
  1476   1498     }
  1477   1499   
  1478   1500     return pRet;
................................................................................
  3773   3795         FuncDef *pDef;         /* The function definition object */
  3774   3796         const char *zId;       /* The function name */
  3775   3797         u32 constMask = 0;     /* Mask of function arguments that are constant */
  3776   3798         int i;                 /* Loop counter */
  3777   3799         sqlite3 *db = pParse->db;  /* The database connection */
  3778   3800         u8 enc = ENC(db);      /* The text encoding used by this database */
  3779   3801         CollSeq *pColl = 0;    /* A collating sequence */
         3802  +
         3803  +      if( !ExprHasProperty(pExpr, EP_TokenOnly|EP_Reduced) && pExpr->pWin ){
         3804  +        return pExpr->pWin->regResult;
         3805  +      }
  3780   3806   
  3781   3807         if( ConstFactorOk(pParse) && sqlite3ExprIsConstantNotJoin(pExpr) ){
  3782   3808           /* SQL functions can be expensive. So try to move constant functions
  3783   3809           ** out of the inner loop, even if that means an extra OP_Copy. */
  3784   3810           return sqlite3ExprCodeAtInit(pParse, pExpr, -1);
  3785   3811         }
  3786   3812         assert( !ExprHasProperty(pExpr, EP_xIsSelect) );
................................................................................
  3794   3820         zId = pExpr->u.zToken;
  3795   3821         pDef = sqlite3FindFunction(db, zId, nFarg, enc, 0);
  3796   3822   #ifdef SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION
  3797   3823         if( pDef==0 && pParse->explain ){
  3798   3824           pDef = sqlite3FindFunction(db, "unknown", nFarg, enc, 0);
  3799   3825         }
  3800   3826   #endif
  3801         -      if( pDef==0 || pDef->xFinalize!=0 ){
         3827  +      if( pDef==0 /* || pDef->xFinalize!=0 */ ){
  3802   3828           sqlite3ErrorMsg(pParse, "unknown function: %s()", zId);
  3803   3829           break;
  3804   3830         }
  3805   3831   
  3806   3832         /* Attempt a direct implementation of the built-in COALESCE() and
  3807   3833         ** IFNULL() functions.  This avoids unnecessary evaluation of
  3808   3834         ** arguments past the first non-NULL argument.

Changes to src/func.c.

  1855   1855       VFUNCTION(last_insert_rowid, 0, 0, 0, last_insert_rowid),
  1856   1856       VFUNCTION(changes,           0, 0, 0, changes          ),
  1857   1857       VFUNCTION(total_changes,     0, 0, 0, total_changes    ),
  1858   1858       FUNCTION(replace,            3, 0, 0, replaceFunc      ),
  1859   1859       FUNCTION(zeroblob,           1, 0, 0, zeroblobFunc     ),
  1860   1860       FUNCTION(substr,             2, 0, 0, substrFunc       ),
  1861   1861       FUNCTION(substr,             3, 0, 0, substrFunc       ),
  1862         -    AGGREGATE(sum,               1, 0, 0, sumStep,         sumFinalize    ),
         1862  +    WFUNCTION(sum,               1, 0, sumStep, sumFinalize, sumFinalize, 0),
  1863   1863       AGGREGATE(total,             1, 0, 0, sumStep,         totalFinalize    ),
  1864   1864       AGGREGATE(avg,               1, 0, 0, sumStep,         avgFinalize    ),
  1865   1865       AGGREGATE2(count,            0, 0, 0, countStep,       countFinalize,
  1866   1866                  SQLITE_FUNC_COUNT  ),
  1867   1867       AGGREGATE(count,             1, 0, 0, countStep,       countFinalize  ),
  1868   1868       AGGREGATE(group_concat,      1, 0, 0, groupConcatStep, groupConcatFinalize),
  1869   1869       AGGREGATE(group_concat,      2, 0, 0, groupConcatStep, groupConcatFinalize),

Changes to src/parse.y.

    95     95   **
    96     96   **      UPDATE ON (a,b,c)
    97     97   **
    98     98   ** Then the "b" IdList records the list "a,b,c".
    99     99   */
   100    100   struct TrigEvent { int a; IdList * b; };
   101    101   
          102  +struct FrameBound     { int eType; Expr *pExpr; };
          103  +
   102    104   /*
   103    105   ** Disable lookaside memory allocation for objects that might be
   104    106   ** shared across database connections.
   105    107   */
   106    108   static void disableLookaside(Parse *pParse){
   107    109     pParse->disableLookaside++;
   108    110     pParse->db->lookaside.bDisable++;
................................................................................
   205    207   // This obviates the need for the "id" nonterminal.
   206    208   //
   207    209   %fallback ID
   208    210     ABORT ACTION AFTER ANALYZE ASC ATTACH BEFORE BEGIN BY CASCADE CAST COLUMNKW
   209    211     CONFLICT DATABASE DEFERRED DESC DETACH DO
   210    212     EACH END EXCLUSIVE EXPLAIN FAIL FOR
   211    213     IGNORE IMMEDIATE INITIALLY INSTEAD LIKE_KW MATCH NO PLAN
   212         -  QUERY KEY OF OFFSET PRAGMA RAISE RECURSIVE RELEASE REPLACE RESTRICT ROW
          214  +  QUERY KEY OF OFFSET PRAGMA RAISE RECURSIVE RELEASE REPLACE RESTRICT ROW ROWS
   213    215     ROLLBACK SAVEPOINT TEMP TRIGGER VACUUM VIEW VIRTUAL WITH WITHOUT
   214    216   %ifdef SQLITE_OMIT_COMPOUND_SELECT
   215    217     EXCEPT INTERSECT UNION
   216    218   %endif SQLITE_OMIT_COMPOUND_SELECT
   217    219     REINDEX RENAME CTIME_KW IF
   218    220     .
   219    221   %wildcard ANY.
................................................................................
   997    999   }
   998   1000   %ifndef SQLITE_OMIT_CAST
   999   1001   expr(A) ::= CAST LP expr(E) AS typetoken(T) RP. {
  1000   1002     A = sqlite3ExprAlloc(pParse->db, TK_CAST, &T, 1);
  1001   1003     sqlite3ExprAttachSubtrees(pParse->db, A, E, 0);
  1002   1004   }
  1003   1005   %endif  SQLITE_OMIT_CAST
  1004         -expr(A) ::= id(X) LP distinct(D) exprlist(Y) RP. {
         1006  +expr(A) ::= id(X) LP distinct(D) exprlist(Y) RP window(Z). {
  1005   1007     if( Y && Y->nExpr>pParse->db->aLimit[SQLITE_LIMIT_FUNCTION_ARG] ){
  1006   1008       sqlite3ErrorMsg(pParse, "too many arguments on function %T", &X);
  1007   1009     }
  1008   1010     A = sqlite3ExprFunction(pParse, Y, &X);
         1011  +  sqlite3WindowAttach(pParse, A, Z);
  1009   1012     if( D==SF_Distinct && A ){
  1010   1013       A->flags |= EP_Distinct;
  1011   1014     }
  1012   1015   }
  1013   1016   expr(A) ::= id(X) LP STAR RP. {
  1014   1017     A = sqlite3ExprFunction(pParse, 0, &X);
  1015   1018   }
  1016   1019   term(A) ::= CTIME_KW(OP). {
  1017   1020     A = sqlite3ExprFunction(pParse, 0, &OP);
  1018   1021   }
         1022  +
         1023  +
         1024  +%type window {Window*}
         1025  +%destructor window {sqlite3WindowDelete(pParse->db, $$);}
         1026  +
         1027  +%type frame_opt {Window*}
         1028  +%destructor frame_opt {sqlite3WindowDelete(pParse->db, $$);}
         1029  +
         1030  +%type part_opt {ExprList*}
         1031  +%destructor part_opt {sqlite3ExprListDelete(pParse->db, $$);}
         1032  +
         1033  +%type filter_opt {Expr*}
         1034  +%destructor filter_opt {sqlite3ExprDelete(pParse->db, $$);}
         1035  +
         1036  +%type range_or_rows {int}
         1037  +
         1038  +%type frame_bound {struct FrameBound}
         1039  +%destructor frame_bound {sqlite3ExprDelete(pParse->db, $$.pExpr);}
         1040  +
         1041  +window(A) ::= . { A = 0; }
         1042  +window(A) ::= filter_opt(W) OVER LP part_opt(X) orderby_opt(Y) frame_opt(Z) RP.{
         1043  +  if( Z ){
         1044  +    A = Z;
         1045  +    A->pFilter = W;
         1046  +    A->pPartition = X;
         1047  +    A->pOrderBy = Y;
         1048  +  }
         1049  +}
         1050  +
         1051  +part_opt(A) ::= PARTITION BY exprlist(X). { A = X; }
         1052  +part_opt(A) ::= .                         { A = 0; }
         1053  +filter_opt(A) ::= .                            { A = 0; }
         1054  +filter_opt(A) ::= FILTER LP WHERE expr(X) RP.  { A = X; }
         1055  +
         1056  +frame_opt(A) ::= .                             { 
         1057  +  A = sqlite3WindowAlloc(pParse, TK_RANGE, TK_UNBOUNDED, 0, TK_CURRENT, 0);
         1058  +}
         1059  +frame_opt(A) ::= range_or_rows(X) frame_bound(Y). { 
         1060  +  A = sqlite3WindowAlloc(pParse, X, Y.eType, Y.pExpr, TK_CURRENT, 0);
         1061  +}
         1062  +frame_opt(A) ::= range_or_rows(X) BETWEEN frame_bound(Y) AND frame_bound(Z). { 
         1063  +  A = sqlite3WindowAlloc(pParse, X, Y.eType, Y.pExpr, Z.eType, Z.pExpr);
         1064  +}
         1065  +
         1066  +range_or_rows(A) ::= RANGE.   { A = TK_RANGE; }
         1067  +range_or_rows(A) ::= ROWS.    { A = TK_ROWS;  }
         1068  +
         1069  +frame_bound(A) ::= UNBOUNDED PRECEDING. { A.eType = TK_UNBOUNDED; A.pExpr = 0; }
         1070  +frame_bound(A) ::= expr(X) PRECEDING.   { A.eType = TK_PRECEDING; A.pExpr = X; }
         1071  +frame_bound(A) ::= CURRENT ROW.         { A.eType = TK_CURRENT  ; A.pExpr = 0; }
         1072  +frame_bound(A) ::= expr(X) FOLLOWING.   { A.eType = TK_FOLLOWING; A.pExpr = X; }
         1073  +frame_bound(A) ::= UNBOUNDED FOLLOWING. { A.eType = TK_UNBOUNDED; A.pExpr = 0; }
         1074  +
  1019   1075   
  1020   1076   expr(A) ::= LP nexprlist(X) COMMA expr(Y) RP. {
  1021   1077     ExprList *pList = sqlite3ExprListAppend(pParse, X, Y);
  1022   1078     A = sqlite3PExpr(pParse, TK_VECTOR, 0, 0);
  1023   1079     if( A ){
  1024   1080       A->x.pList = pList;
  1025   1081     }else{

Changes to src/resolve.c.

   749    749             /* Date/time functions that use 'now', and other functions like
   750    750             ** sqlite_version() that might change over time cannot be used
   751    751             ** in an index. */
   752    752             notValid(pParse, pNC, "non-deterministic functions",
   753    753                      NC_IdxExpr|NC_PartIdx);
   754    754           }
   755    755         }
   756         -      if( is_agg && (pNC->ncFlags & NC_AllowAgg)==0 ){
   757         -        sqlite3ErrorMsg(pParse, "misuse of aggregate function %.*s()", nId,zId);
          756  +      if( (is_agg && (pNC->ncFlags & NC_AllowAgg)==0) 
          757  +       || (pExpr->pWin && (pNC->ncFlags & NC_AllowWin)==0)
          758  +      ){
          759  +        const char *zType = pExpr->pWin ? "window" : "aggregate";
          760  +        sqlite3ErrorMsg(pParse, "misuse of %s function %.*s()",zType,nId,zId);
   758    761           pNC->nErr++;
   759    762           is_agg = 0;
   760    763         }else if( no_such_func && pParse->db->init.busy==0
   761    764   #ifdef SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION
   762    765                   && pParse->explain==0
   763    766   #endif
   764    767         ){
................................................................................
   768    771           sqlite3ErrorMsg(pParse,"wrong number of arguments to function %.*s()",
   769    772                nId, zId);
   770    773           pNC->nErr++;
   771    774         }
   772    775         if( is_agg ) pNC->ncFlags &= ~NC_AllowAgg;
   773    776         sqlite3WalkExprList(pWalker, pList);
   774    777         if( is_agg ){
          778  +        if( pExpr->pWin ){
          779  +          pExpr->pWin->pNextWin = pNC->pWin;
          780  +          pNC->pWin = pExpr->pWin;
          781  +          pExpr->pWin->pFunc = pDef;
          782  +          pExpr->pWin->nArg = pExpr->x.pList->nExpr;
          783  +        }
          784  +        else
          785  +        {
   775    786           NameContext *pNC2 = pNC;
   776    787           pExpr->op = TK_AGG_FUNCTION;
   777    788           pExpr->op2 = 0;
   778    789           while( pNC2 && !sqlite3FunctionUsesThisSrc(pExpr, pNC2->pSrcList) ){
   779    790             pExpr->op2++;
   780    791             pNC2 = pNC2->pNext;
   781    792           }
................................................................................
   782    793           assert( pDef!=0 );
   783    794           if( pNC2 ){
   784    795             assert( SQLITE_FUNC_MINMAX==NC_MinMaxAgg );
   785    796             testcase( (pDef->funcFlags & SQLITE_FUNC_MINMAX)!=0 );
   786    797             pNC2->ncFlags |= NC_HasAgg | (pDef->funcFlags & SQLITE_FUNC_MINMAX);
   787    798   
   788    799           }
          800  +        }
   789    801           pNC->ncFlags |= NC_AllowAgg;
   790    802         }
   791    803         /* FIX ME:  Compute pExpr->affinity based on the expected return
   792    804         ** type of the function 
   793    805         */
   794    806         return WRC_Prune;
   795    807       }
................................................................................
  1230   1242       return (pParse->nErr || db->mallocFailed) ? WRC_Abort : WRC_Prune;
  1231   1243     }
  1232   1244   
  1233   1245     isCompound = p->pPrior!=0;
  1234   1246     nCompound = 0;
  1235   1247     pLeftmost = p;
  1236   1248     while( p ){
         1249  +    assert( p->pWin==0 );
  1237   1250       assert( (p->selFlags & SF_Expanded)!=0 );
  1238   1251       assert( (p->selFlags & SF_Resolved)==0 );
  1239   1252       p->selFlags |= SF_Resolved;
  1240   1253   
  1241   1254       /* Resolve the expressions in the LIMIT and OFFSET clauses. These
  1242   1255       ** are not allowed to refer to any names, so pass an empty NameContext.
  1243   1256       */
................................................................................
  1287   1300           pItem->fg.isCorrelated = (nRef!=0);
  1288   1301         }
  1289   1302       }
  1290   1303     
  1291   1304       /* Set up the local name-context to pass to sqlite3ResolveExprNames() to
  1292   1305       ** resolve the result-set expression list.
  1293   1306       */
  1294         -    sNC.ncFlags = NC_AllowAgg;
         1307  +    sNC.ncFlags = NC_AllowAgg|NC_AllowWin;
  1295   1308       sNC.pSrcList = p->pSrc;
  1296   1309       sNC.pNext = pOuterNC;
  1297   1310     
  1298   1311       /* Resolve names in the result set. */
  1299   1312       if( sqlite3ResolveExprListNames(&sNC, p->pEList) ) return WRC_Abort;
         1313  +    sNC.ncFlags &= ~NC_AllowWin;
  1300   1314     
  1301   1315       /* If there are no aggregate functions in the result-set, and no GROUP BY 
  1302   1316       ** expression, do not allow aggregates in any of the other expressions.
  1303   1317       */
  1304   1318       assert( (p->selFlags & SF_Aggregate)==0 );
  1305   1319       pGroupBy = p->pGroupBy;
  1306   1320       if( pGroupBy || (sNC.ncFlags & NC_HasAgg)!=0 ){
................................................................................
  1341   1355         }
  1342   1356       }
  1343   1357   
  1344   1358       /* The ORDER BY and GROUP BY clauses may not refer to terms in
  1345   1359       ** outer queries 
  1346   1360       */
  1347   1361       sNC.pNext = 0;
  1348         -    sNC.ncFlags |= NC_AllowAgg;
         1362  +    sNC.ncFlags |= NC_AllowAgg|NC_AllowWin;
  1349   1363   
  1350   1364       /* If this is a converted compound query, move the ORDER BY clause from 
  1351   1365       ** the sub-query back to the parent query. At this point each term
  1352   1366       ** within the ORDER BY clause has been transformed to an integer value.
  1353   1367       ** These integers will be replaced by copies of the corresponding result
  1354   1368       ** set expressions by the call to resolveOrderGroupBy() below.  */
  1355   1369       if( p->selFlags & SF_Converted ){
................................................................................
  1372   1386        && resolveOrderGroupBy(&sNC, p, p->pOrderBy, "ORDER")
  1373   1387       ){
  1374   1388         return WRC_Abort;
  1375   1389       }
  1376   1390       if( db->mallocFailed ){
  1377   1391         return WRC_Abort;
  1378   1392       }
         1393  +    sNC.ncFlags &= ~NC_AllowWin;
  1379   1394     
  1380   1395       /* Resolve the GROUP BY clause.  At the same time, make sure 
  1381   1396       ** the GROUP BY clause does not contain aggregate functions.
  1382   1397       */
  1383   1398       if( pGroupBy ){
  1384   1399         struct ExprList_item *pItem;
  1385   1400       
................................................................................
  1397   1412   
  1398   1413       /* If this is part of a compound SELECT, check that it has the right
  1399   1414       ** number of expressions in the select list. */
  1400   1415       if( p->pNext && p->pEList->nExpr!=p->pNext->pEList->nExpr ){
  1401   1416         sqlite3SelectWrongNumTermsError(pParse, p->pNext);
  1402   1417         return WRC_Abort;
  1403   1418       }
         1419  +
         1420  +    p->pWin = sNC.pWin;
         1421  +    sNC.pWin = 0;
  1404   1422   
  1405   1423       /* Advance to the next term of the compound
  1406   1424       */
  1407   1425       p = p->pPrior;
  1408   1426       nCompound++;
  1409   1427     }
  1410   1428   

Changes to src/select.c.

   158    158     pNew->pGroupBy = pGroupBy;
   159    159     pNew->pHaving = pHaving;
   160    160     pNew->pOrderBy = pOrderBy;
   161    161     pNew->pPrior = 0;
   162    162     pNew->pNext = 0;
   163    163     pNew->pLimit = pLimit;
   164    164     pNew->pWith = 0;
          165  +  pNew->pWin = 0;
   165    166     if( pParse->db->mallocFailed ) {
   166    167       clearSelect(pParse->db, pNew, pNew!=&standin);
   167    168       pNew = 0;
   168    169     }else{
   169    170       assert( pNew->pSrc!=0 || pParse->nErr>0 );
   170    171     }
   171    172     assert( pNew!=&standin );
................................................................................
  3715   3716     pSrc = p->pSrc;
  3716   3717     assert( pSrc && iFrom>=0 && iFrom<pSrc->nSrc );
  3717   3718     pSubitem = &pSrc->a[iFrom];
  3718   3719     iParent = pSubitem->iCursor;
  3719   3720     pSub = pSubitem->pSelect;
  3720   3721     assert( pSub!=0 );
  3721   3722   
         3723  +  if( p->pWin ) return 0;
         3724  +
  3722   3725     pSubSrc = pSub->pSrc;
  3723   3726     assert( pSubSrc );
  3724   3727     /* Prior to version 3.1.2, when LIMIT and OFFSET had to be simple constants,
  3725   3728     ** not arbitrary expressions, we allowed some combining of LIMIT and OFFSET
  3726   3729     ** because they could be computed at compile-time.  But when LIMIT and OFFSET
  3727   3730     ** became arbitrary expressions, we were forced to add restrictions (13)
  3728   3731     ** and (14). */
................................................................................
  4583   4586         pParse->pWith = pWith->pOuter;
  4584   4587       }
  4585   4588     }
  4586   4589   }
  4587   4590   #else
  4588   4591   #define selectPopWith 0
  4589   4592   #endif
         4593  +
         4594  +static int selectExpandSubquery(Parse *pParse, struct SrcList_item *pFrom){
         4595  +  Select *pSel = pFrom->pSelect;
         4596  +  Table *pTab;
         4597  +
         4598  +  pFrom->pTab = pTab = sqlite3DbMallocZero(pParse->db, sizeof(Table));
         4599  +  if( pTab==0 ) return WRC_Abort;
         4600  +  pTab->nTabRef = 1;
         4601  +  if( pFrom->zAlias ){
         4602  +    pTab->zName = sqlite3DbStrDup(pParse->db, pFrom->zAlias);
         4603  +  }else{
         4604  +    pTab->zName = sqlite3MPrintf(pParse->db, "subquery_%p", (void*)pTab);
         4605  +  }
         4606  +  while( pSel->pPrior ){ pSel = pSel->pPrior; }
         4607  +  sqlite3ColumnsFromExprList(pParse, pSel->pEList,&pTab->nCol,&pTab->aCol);
         4608  +  pTab->iPKey = -1;
         4609  +  pTab->nRowLogEst = 200; assert( 200==sqlite3LogEst(1048576) );
         4610  +  pTab->tabFlags |= TF_Ephemeral;
         4611  +
         4612  +  return WRC_Continue;
         4613  +}
  4590   4614   
  4591   4615   /*
  4592   4616   ** This routine is a Walker callback for "expanding" a SELECT statement.
  4593   4617   ** "Expanding" means to do the following:
  4594   4618   **
  4595   4619   **    (1)  Make sure VDBE cursor numbers have been assigned to every
  4596   4620   **         element of the FROM clause.
................................................................................
  4656   4680       if( pFrom->zName==0 ){
  4657   4681   #ifndef SQLITE_OMIT_SUBQUERY
  4658   4682         Select *pSel = pFrom->pSelect;
  4659   4683         /* A sub-query in the FROM clause of a SELECT */
  4660   4684         assert( pSel!=0 );
  4661   4685         assert( pFrom->pTab==0 );
  4662   4686         if( sqlite3WalkSelect(pWalker, pSel) ) return WRC_Abort;
         4687  +      if( selectExpandSubquery(pParse, pFrom) ) return WRC_Abort;
         4688  +#if 0
  4663   4689         pFrom->pTab = pTab = sqlite3DbMallocZero(db, sizeof(Table));
  4664   4690         if( pTab==0 ) return WRC_Abort;
  4665   4691         pTab->nTabRef = 1;
  4666   4692         if( pFrom->zAlias ){
  4667   4693           pTab->zName = sqlite3DbStrDup(db, pFrom->zAlias);
  4668   4694         }else{
  4669   4695           pTab->zName = sqlite3MPrintf(db, "subquery_%p", (void*)pTab);
  4670   4696         }
  4671   4697         while( pSel->pPrior ){ pSel = pSel->pPrior; }
  4672   4698         sqlite3ColumnsFromExprList(pParse, pSel->pEList,&pTab->nCol,&pTab->aCol);
  4673   4699         pTab->iPKey = -1;
  4674   4700         pTab->nRowLogEst = 200; assert( 200==sqlite3LogEst(1048576) );
  4675   4701         pTab->tabFlags |= TF_Ephemeral;
         4702  +#endif
  4676   4703   #endif
  4677   4704       }else{
  4678   4705         /* An ordinary table or view name in the FROM clause */
  4679   4706         assert( pFrom->pTab==0 );
  4680   4707         pFrom->pTab = pTab = sqlite3LocateTableItem(pParse, 0, pFrom);
  4681   4708         if( pTab==0 ) return WRC_Abort;
  4682   4709         if( pTab->nTabRef>=0xffff ){
................................................................................
  5370   5397       SELECTTRACE(0x400,pParse,p,("After count-of-view optimization:\n"));
  5371   5398       sqlite3TreeViewSelect(0, p, 0);
  5372   5399     }
  5373   5400   #endif
  5374   5401     return 1;
  5375   5402   }
  5376   5403   #endif /* SQLITE_COUNTOFVIEW_OPTIMIZATION */
         5404  +
         5405  +typedef struct WindowRewrite WindowRewrite;
         5406  +struct WindowRewrite {
         5407  +  Window *pWin;
         5408  +  ExprList *pSub;
         5409  +};
         5410  +
         5411  +static int selectWindowRewriteSelectCb(Walker *pWalker, Select *pSelect){
         5412  +  return WRC_Prune;
         5413  +}
         5414  +
         5415  +static int selectWindowRewriteExprCb(Walker *pWalker, Expr *pExpr){
         5416  +  struct WindowRewrite *p = pWalker->u.pRewrite;
         5417  +  Parse *pParse = pWalker->pParse;
         5418  +  int rc = WRC_Continue;
         5419  +
         5420  +  switch( pExpr->op ){
         5421  +    case TK_COLUMN: {
         5422  +      Expr *pDup = sqlite3ExprDup(pParse->db, pExpr, 0);
         5423  +      p->pSub = sqlite3ExprListAppend(pParse, p->pSub, pDup);
         5424  +      if( p->pSub ){
         5425  +        assert( ExprHasProperty(pExpr, EP_Static)==0 );
         5426  +        ExprSetProperty(pExpr, EP_Static);
         5427  +        sqlite3ExprDelete(pParse->db, pExpr);
         5428  +        ExprClearProperty(pExpr, EP_Static);
         5429  +        memset(pExpr, 0, sizeof(Expr));
         5430  +
         5431  +        pExpr->op = TK_COLUMN;
         5432  +        pExpr->iColumn = p->pSub->nExpr-1;
         5433  +        pExpr->iTable = p->pWin->iEphCsr;
         5434  +      }
         5435  +
         5436  +      break;
         5437  +    }
         5438  +
         5439  +    case TK_FUNCTION:
         5440  +      if( pExpr->pWin ){
         5441  +        rc = WRC_Prune;
         5442  +        pExpr->pWin->pOwner = pExpr;
         5443  +      }
         5444  +      break;
         5445  +
         5446  +    default: /* no-op */
         5447  +      break;
         5448  +  }
         5449  +
         5450  +  return rc;
         5451  +}
         5452  +
         5453  +static int selectWindowRewriteEList(
         5454  +  Parse *pParse, 
         5455  +  Window *pWin,
         5456  +  ExprList *pEList,               /* Rewrite expressions in this list */
         5457  +  ExprList **ppSub                /* IN/OUT: Sub-select expression-list */
         5458  +){
         5459  +  Walker sWalker;
         5460  +  WindowRewrite sRewrite;
         5461  +  int rc;
         5462  +
         5463  +  memset(&sWalker, 0, sizeof(Walker));
         5464  +  memset(&sRewrite, 0, sizeof(WindowRewrite));
         5465  +
         5466  +  sRewrite.pSub = *ppSub;
         5467  +  sRewrite.pWin = pWin;
         5468  +
         5469  +  sWalker.pParse = pParse;
         5470  +  sWalker.xExprCallback = selectWindowRewriteExprCb;
         5471  +  sWalker.xSelectCallback = selectWindowRewriteSelectCb;
         5472  +  sWalker.u.pRewrite = &sRewrite;
         5473  +
         5474  +  rc = sqlite3WalkExprList(&sWalker, pEList);
         5475  +
         5476  +  *ppSub = sRewrite.pSub;
         5477  +  return rc;
         5478  +}
         5479  +
         5480  +static ExprList *exprListAppendList(
         5481  +  Parse *pParse,          /* Parsing context */
         5482  +  ExprList *pList,        /* List to which to append. Might be NULL */
         5483  +  ExprList *pAppend       /* List of values to append. Might be NULL */
         5484  +){
         5485  +  if( pAppend ){
         5486  +    int i;
         5487  +    int nInit = pList ? pList->nExpr : 0;
         5488  +    for(i=0; i<pAppend->nExpr; i++){
         5489  +      Expr *pDup = sqlite3ExprDup(pParse->db, pAppend->a[i].pExpr, 0);
         5490  +      pList = sqlite3ExprListAppend(pParse, pList, pDup);
         5491  +      if( pList ) pList->a[nInit+i].sortOrder = pAppend->a[i].sortOrder;
         5492  +    }
         5493  +  }
         5494  +  return pList;
         5495  +}
         5496  +
         5497  +/*
         5498  +** If the SELECT statement passed as the second argument does not invoke
         5499  +** any SQL window functions, this function is a no-op. Otherwise, it 
         5500  +** rewrites the SELECT statement so that window function xStep functions
         5501  +** are invoked in the correct order. The simplest version of the 
         5502  +** transformation is:
         5503  +**
         5504  +**   SELECT win(args...) OVER (<list1>) FROM <src> ORDER BY <list2>
         5505  +**
         5506  +** to
         5507  +**
         5508  +**   SELECT win(args...) FROM (
         5509  +**     SELECT args... FROM <src> ORDER BY <list1>
         5510  +**   ) ORDER BY <list2>
         5511  +**
         5512  +** where <src> may contain WHERE, GROUP BY and HAVING clauses, and <list1>
         5513  +** is the concatenation of the PARTITION BY and ORDER BY clauses in the
         5514  +** OVER clause.
         5515  +**
         5516  +*/
         5517  +static int selectWindowRewrite(Parse *pParse, Select *p){
         5518  +  int rc = SQLITE_OK;
         5519  +  if( p->pWin ){
         5520  +    Vdbe *v = sqlite3GetVdbe(pParse);
         5521  +    int i;
         5522  +    sqlite3 *db = pParse->db;
         5523  +    Select *pSub = 0;             /* The subquery */
         5524  +    SrcList *pSrc = p->pSrc;
         5525  +    Expr *pWhere = p->pWhere;
         5526  +    ExprList *pGroupBy = p->pGroupBy;
         5527  +    Expr *pHaving = p->pHaving;
         5528  +    ExprList *pSort = 0;
         5529  +
         5530  +    ExprList *pSublist = 0;       /* Expression list for sub-query */
         5531  +    Window *pWin = p->pWin;
         5532  +
         5533  +    /* TODO: This is of course temporary requirements */
         5534  +    assert( pWin->pNextWin==0 );
         5535  +
         5536  +    p->pSrc = 0;
         5537  +    p->pWhere = 0;
         5538  +    p->pGroupBy = 0;
         5539  +    p->pHaving = 0;
         5540  +
         5541  +    pWin->regAccum = ++pParse->nMem;
         5542  +    pWin->regResult = ++pParse->nMem;
         5543  +
         5544  +    /* Assign a cursor number for the ephemeral table used to buffer rows.
         5545  +    ** The OpenEphemeral instruction is coded later, after it is known how
         5546  +    ** many columns the table will have.  */
         5547  +    pWin->iEphCsr = pParse->nTab++;
         5548  +
         5549  +    rc = selectWindowRewriteEList(pParse, pWin, p->pEList, &pSublist);
         5550  +    if( rc ) return rc;
         5551  +    rc = selectWindowRewriteEList(pParse, pWin, p->pOrderBy, &pSublist);
         5552  +    if( rc ) return rc;
         5553  +    pWin->nBufferCol = (pSublist ? pSublist->nExpr : 0);
         5554  +
         5555  +    /* Create the ORDER BY clause for the sub-select. This is the concatenation
         5556  +    ** of the window PARTITION and ORDER BY clauses. Append the same 
         5557  +    ** expressions to the sub-select expression list. They are required to
         5558  +    ** figure out where boundaries for partitions and sets of peer rows.  */
         5559  +    pSort = sqlite3ExprListDup(db, pWin->pPartition, 0);
         5560  +    if( pWin->pOrderBy ){
         5561  +      pSort = exprListAppendList(pParse, pSort, pWin->pOrderBy);
         5562  +    }
         5563  +    pSublist = exprListAppendList(pParse, pSublist, pSort);
         5564  +
         5565  +    /* Also append the arguments passed to the window function to the
         5566  +    ** sub-select expression list. */
         5567  +    pWin->iArgCol = (pSublist ? pSublist->nExpr : 0);
         5568  +    pSublist = exprListAppendList(pParse, pSublist, pWin->pOwner->x.pList);
         5569  +
         5570  +    pSub = sqlite3SelectNew(
         5571  +        pParse, pSublist, pSrc, pWhere, pGroupBy, pHaving, pSort, 0, 0
         5572  +    );
         5573  +    p->pSrc = sqlite3SrcListAppend(db, 0, 0, 0);
         5574  +    if( p->pSrc ){
         5575  +      int iTab;
         5576  +      ExprList *pList = 0;
         5577  +      p->pSrc->a[0].pSelect = pSub;
         5578  +      sqlite3SrcListAssignCursors(pParse, p->pSrc);
         5579  +      if( selectExpandSubquery(pParse, &p->pSrc->a[0]) ){
         5580  +        rc = SQLITE_NOMEM;
         5581  +      }else{
         5582  +        pSub->selFlags |= SF_Expanded;
         5583  +      }
         5584  +    }
         5585  +
         5586  +#if SELECTTRACE_ENABLED
         5587  +    if( sqlite3SelectTrace & 0x108 ){
         5588  +      SELECTTRACE(0x104,pParse,p, ("after window rewrite:\n"));
         5589  +      sqlite3TreeViewSelect(0, p, 0);
         5590  +    }
         5591  +#endif
         5592  +
         5593  +    sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pWin->iEphCsr, pWin->nBufferCol);
         5594  +    sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regAccum);
         5595  +  }
         5596  +
         5597  +  return rc;
         5598  +}
  5377   5599   
  5378   5600   /*
  5379   5601   ** Generate code for the SELECT statement given in the p argument.  
  5380   5602   **
  5381   5603   ** The results are returned according to the SelectDest structure.
  5382   5604   ** See comments in sqliteInt.h for further information.
  5383   5605   **
................................................................................
  5439   5661       sqlite3ExprListDelete(db, p->pOrderBy);
  5440   5662       p->pOrderBy = 0;
  5441   5663       p->selFlags &= ~SF_Distinct;
  5442   5664     }
  5443   5665     sqlite3SelectPrep(pParse, p, 0);
  5444   5666     memset(&sSort, 0, sizeof(sSort));
  5445   5667     sSort.pOrderBy = p->pOrderBy;
  5446         -  pTabList = p->pSrc;
  5447   5668     if( pParse->nErr || db->mallocFailed ){
  5448   5669       goto select_end;
  5449   5670     }
  5450   5671     assert( p->pEList!=0 );
  5451   5672     isAgg = (p->selFlags & SF_Aggregate)!=0;
  5452   5673   #if SELECTTRACE_ENABLED
  5453   5674     if( sqlite3SelectTrace & 0x104 ){
................................................................................
  5455   5676       sqlite3TreeViewSelect(0, p, 0);
  5456   5677     }
  5457   5678   #endif
  5458   5679   
  5459   5680     if( pDest->eDest==SRT_Output ){
  5460   5681       generateColumnNames(pParse, p);
  5461   5682     }
         5683  +
         5684  +  if( (rc = selectWindowRewrite(pParse, p)) ){
         5685  +    goto select_end;
         5686  +  }
         5687  +  pTabList = p->pSrc;
  5462   5688   
  5463   5689     /* Try to various optimizations (flattening subqueries, and strength
  5464   5690     ** reduction of join operators) in the FROM clause up into the main query
  5465   5691     */
  5466   5692   #if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
  5467   5693     for(i=0; !p->pPrior && i<pTabList->nSrc; i++){
  5468   5694       struct SrcList_item *pItem = &pTabList->a[i];
................................................................................
  5829   6055       sqlite3VdbeChangeP5(v, BTREE_UNORDERED);
  5830   6056       sDistinct.eTnctType = WHERE_DISTINCT_UNORDERED;
  5831   6057     }else{
  5832   6058       sDistinct.eTnctType = WHERE_DISTINCT_NOOP;
  5833   6059     }
  5834   6060   
  5835   6061     if( !isAgg && pGroupBy==0 ){
         6062  +    Window *pWin = p->pWin;
         6063  +    int regPart = 0;
         6064  +
  5836   6065       /* No aggregate functions and no GROUP BY clause */
  5837   6066       u16 wctrlFlags = (sDistinct.isTnct ? WHERE_WANT_DISTINCT : 0);
  5838   6067       assert( WHERE_USE_LIMIT==SF_FixedLimit );
  5839   6068       wctrlFlags |= p->selFlags & SF_FixedLimit;
         6069  +
         6070  +    if( pWin ){
         6071  +      int nPart = (pWin->pPartition ? pWin->pPartition->nExpr : 0);
         6072  +      nPart += (pWin->pOrderBy ? pWin->pOrderBy->nExpr : 0);
         6073  +      if( nPart ){
         6074  +        regPart = pParse->nMem+1;
         6075  +        pParse->nMem += nPart;
         6076  +        sqlite3VdbeAddOp3(v, OP_Null, 0, regPart, regPart+nPart-1);
         6077  +      }
         6078  +    }
  5840   6079   
  5841   6080       /* Begin the database scan. */
  5842   6081       SELECTTRACE(1,pParse,p,("WhereBegin\n"));
  5843   6082       pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, sSort.pOrderBy,
  5844   6083                                  p->pEList, wctrlFlags, p->nSelectRow);
  5845   6084       if( pWInfo==0 ) goto select_end;
  5846   6085       if( sqlite3WhereOutputRowCount(pWInfo) < p->nSelectRow ){
................................................................................
  5861   6100       ** instruction ended up not being needed, then change the OP_OpenEphemeral
  5862   6101       ** into an OP_Noop.
  5863   6102       */
  5864   6103       if( sSort.addrSortIndex>=0 && sSort.pOrderBy==0 ){
  5865   6104         sqlite3VdbeChangeToNoop(v, sSort.addrSortIndex);
  5866   6105       }
  5867   6106   
  5868         -    /* Use the standard inner loop. */
  5869   6107       assert( p->pEList==pEList );
         6108  +    if( p->pWin ){
         6109  +      int k;
         6110  +      int iSubCsr = p->pSrc->a[0].iCursor;
         6111  +      int nSub = p->pSrc->a[0].pTab->nCol;
         6112  +      int reg = pParse->nMem+1;
         6113  +      int regRecord = reg+nSub;
         6114  +      int regRowid = regRecord+1;
         6115  +      int regGosub = regRowid+1;
         6116  +      int addr;
         6117  +      int addrGosub;
         6118  +
         6119  +      pParse->nMem += nSub + 3;
         6120  +
         6121  +      /* Martial the row returned by the sub-select into an array of 
         6122  +      ** registers. */
         6123  +      for(k=0; k<nSub; k++){
         6124  +        sqlite3VdbeAddOp3(v, OP_Column, iSubCsr, k, reg+k);
         6125  +      }
         6126  +
         6127  +      /* Check if this is the start of a new partition or peer group. */
         6128  +      if( regPart ){
         6129  +        ExprList *pPart = pWin->pPartition;
         6130  +        int nPart = (pPart ? pPart->nExpr : 0);
         6131  +        ExprList *pOrderBy = pWin->pOrderBy;
         6132  +        int nPeer = (pOrderBy ? pOrderBy->nExpr : 0);
         6133  +        int addrGoto = 0;
         6134  +        int addrJump = 0;
         6135  +
         6136  +        if( pPart ){
         6137  +          int regNewPart = reg + pWin->nBufferCol;
         6138  +          KeyInfo *pKeyInfo = keyInfoFromExprList(pParse, pPart, 0, 0);
         6139  +          addr = sqlite3VdbeAddOp3(v, OP_Compare, regNewPart, regPart, nPart);
         6140  +          sqlite3VdbeAppendP4(v, (void*)pKeyInfo, P4_KEYINFO);
         6141  +          addrJump = sqlite3VdbeAddOp3(v, OP_Jump, addr+2, 0, addr+2);
         6142  +          sqlite3VdbeAddOp2(v, OP_AggFinal, pWin->regAccum, pWin->nArg);
         6143  +          sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
         6144  +          sqlite3VdbeAddOp2(v, OP_Copy, pWin->regAccum, pWin->regResult);
         6145  +          if( pOrderBy ){
         6146  +            addrGoto = sqlite3VdbeAddOp0(v, OP_Goto);
         6147  +          }
         6148  +        }
         6149  +
         6150  +        if( pOrderBy ){
         6151  +          int regNewPeer = reg + pWin->nBufferCol + nPart;
         6152  +          int regPeer = regPart + nPart;
         6153  +
         6154  +          KeyInfo *pKeyInfo = keyInfoFromExprList(pParse, pOrderBy, 0, 0);
         6155  +          if( addrJump ) sqlite3VdbeJumpHere(v, addrJump);
         6156  +          addr = sqlite3VdbeAddOp3(v, OP_Compare, regNewPeer, regPeer, nPeer);
         6157  +          sqlite3VdbeAppendP4(v, (void*)pKeyInfo, P4_KEYINFO);
         6158  +          addrJump = sqlite3VdbeAddOp3(v, OP_Jump, addr+2, 0, addr+2);
         6159  +          sqlite3VdbeAddOp3(v, 
         6160  +              OP_AggFinal, pWin->regAccum, pWin->nArg, pWin->regResult
         6161  +          );
         6162  +          sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
         6163  +
         6164  +          if( addrGoto ) sqlite3VdbeJumpHere(v, addrGoto);
         6165  +        }
         6166  +
         6167  +        addrGosub = sqlite3VdbeAddOp1(v, OP_Gosub, regGosub);
         6168  +        sqlite3VdbeAddOp1(v, OP_ResetSorter, pWin->iEphCsr);
         6169  +        sqlite3VdbeAddOp3(v,OP_Copy,reg+pWin->nBufferCol,regPart,nPart+nPeer-1);
         6170  +
         6171  +        sqlite3VdbeJumpHere(v, addrJump);
         6172  +      }
         6173  +
         6174  +      /* Invoke step function for window functions */
         6175  +      sqlite3VdbeAddOp3(v, OP_AggStep0, 0, reg+pWin->iArgCol, pWin->regAccum);
         6176  +      sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
         6177  +      sqlite3VdbeChangeP5(v, (u8)pWin->nArg);
         6178  +
         6179  +      /* Buffer the current row in the ephemeral table. */
         6180  +      if( pWin->nBufferCol>0 ){
         6181  +        sqlite3VdbeAddOp3(v, OP_MakeRecord, reg, pWin->nBufferCol, regRecord);
         6182  +      }else{
         6183  +        sqlite3VdbeAddOp2(v, OP_Blob, 0, regRecord);
         6184  +        sqlite3VdbeAppendP4(v, (void*)"", 0);
         6185  +      }
         6186  +      sqlite3VdbeAddOp2(v, OP_NewRowid, pWin->iEphCsr, regRowid);
         6187  +      sqlite3VdbeAddOp3(v, OP_Insert, pWin->iEphCsr, regRecord, regRowid);
         6188  +
         6189  +      /* End the database scan loop. */
         6190  +      sqlite3WhereEnd(pWInfo);
         6191  +
         6192  +      sqlite3VdbeAddOp2(v, OP_AggFinal, pWin->regAccum, pWin->nArg);
         6193  +      sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
         6194  +      sqlite3VdbeAddOp2(v, OP_Copy, pWin->regAccum, pWin->regResult);
         6195  +      sqlite3VdbeAddOp2(v, OP_Gosub, regGosub, sqlite3VdbeCurrentAddr(v)+2);
         6196  +
         6197  +      sqlite3VdbeAddOp0(v, OP_Goto);
         6198  +      if( regPart ){
         6199  +        sqlite3VdbeJumpHere(v, addrGosub);
         6200  +      }
         6201  +      addr = sqlite3VdbeAddOp1(v, OP_Rewind, pWin->iEphCsr);
         6202  +      selectInnerLoop(pParse, p, -1, &sSort, &sDistinct, pDest, addr+1, 0);
         6203  +      sqlite3VdbeAddOp2(v, OP_Next, pWin->iEphCsr, addr+1);
         6204  +      sqlite3VdbeJumpHere(v, addr);
         6205  +      sqlite3VdbeAddOp1(v, OP_Return, regGosub);
         6206  +      sqlite3VdbeJumpHere(v, addr-1);       /* OP_Goto jumps here */
         6207  +
         6208  +    }else{
         6209  +      /* Use the standard inner loop. */
  5870   6210       selectInnerLoop(pParse, p, -1, &sSort, &sDistinct, pDest,
  5871   6211                       sqlite3WhereContinueLabel(pWInfo),
  5872   6212                       sqlite3WhereBreakLabel(pWInfo));
  5873   6213   
  5874   6214       /* End the database scan loop.
  5875   6215       */
  5876   6216       sqlite3WhereEnd(pWInfo);
         6217  +    }
  5877   6218     }else{
  5878   6219       /* This case when there exist aggregate functions or a GROUP BY clause
  5879   6220       ** or both */
  5880   6221       NameContext sNC;    /* Name context for processing aggregate information */
  5881   6222       int iAMem;          /* First Mem address for storing current GROUP BY */
  5882   6223       int iBMem;          /* First Mem address for previous GROUP BY */
  5883   6224       int iUseFlag;       /* Mem address holding flag indicating that at least

Changes to src/sqliteInt.h.

  1103   1103   typedef struct TriggerStep TriggerStep;
  1104   1104   typedef struct UnpackedRecord UnpackedRecord;
  1105   1105   typedef struct Upsert Upsert;
  1106   1106   typedef struct VTable VTable;
  1107   1107   typedef struct VtabCtx VtabCtx;
  1108   1108   typedef struct Walker Walker;
  1109   1109   typedef struct WhereInfo WhereInfo;
         1110  +typedef struct Window Window;
  1110   1111   typedef struct With With;
  1111   1112   
  1112   1113   /* A VList object records a mapping between parameters/variables/wildcards
  1113   1114   ** in the SQL statement (such as $abc, @pqr, or :xyz) and the integer
  1114   1115   ** variable number associated with that parameter.  See the format description
  1115   1116   ** on the sqlite3VListAdd() routine for more information.  A VList is really
  1116   1117   ** just an array of integers.
................................................................................
  1584   1585   struct FuncDef {
  1585   1586     i8 nArg;             /* Number of arguments.  -1 means unlimited */
  1586   1587     u16 funcFlags;       /* Some combination of SQLITE_FUNC_* */
  1587   1588     void *pUserData;     /* User data parameter */
  1588   1589     FuncDef *pNext;      /* Next function with same name */
  1589   1590     void (*xSFunc)(sqlite3_context*,int,sqlite3_value**); /* func or agg-step */
  1590   1591     void (*xFinalize)(sqlite3_context*);                  /* Agg finalizer */
         1592  +  void (*xValue)(sqlite3_context*);                     /* Current agg value */
         1593  +  void (*xInverse)(sqlite3_context*,int,sqlite3_value**); /* inverse agg-step */
  1591   1594     const char *zName;   /* SQL name of the function. */
  1592   1595     union {
  1593   1596       FuncDef *pHash;      /* Next with a different name but the same hash */
  1594   1597       FuncDestructor *pDestructor;   /* Reference counted destructor function */
  1595   1598     } u;
  1596   1599   };
  1597   1600   
................................................................................
  1673   1676   **     arbitrary non-NULL pointer.  The bNC parameter is not used.
  1674   1677   **
  1675   1678   **   AGGREGATE(zName, nArg, iArg, bNC, xStep, xFinal)
  1676   1679   **     Used to create an aggregate function definition implemented by
  1677   1680   **     the C functions xStep and xFinal. The first four parameters
  1678   1681   **     are interpreted in the same way as the first 4 parameters to
  1679   1682   **     FUNCTION().
         1683  +**
         1684  +**   WFUNCTION(zName, nArg, iArg, xStep, xFinal, xValue, xInverse)
         1685  +**     Used to create an aggregate function definition implemented by
         1686  +**     the C functions xStep and xFinal. The first four parameters
         1687  +**     are interpreted in the same way as the first 4 parameters to
         1688  +**     FUNCTION().
  1680   1689   **
  1681   1690   **   LIKEFUNC(zName, nArg, pArg, flags)
  1682   1691   **     Used to create a scalar function definition of a function zName
  1683   1692   **     that accepts nArg arguments and is implemented by a call to C
  1684   1693   **     function likeFunc. Argument pArg is cast to a (void *) and made
  1685   1694   **     available as the function user-data (sqlite3_user_data()). The
  1686   1695   **     FuncDef.flags variable is set to the value passed as the flags
  1687   1696   **     parameter.
  1688   1697   */
  1689   1698   #define FUNCTION(zName, nArg, iArg, bNC, xFunc) \
  1690   1699     {nArg, SQLITE_FUNC_CONSTANT|SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL), \
  1691         -   SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, #zName, {0} }
         1700  +   SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, 0, 0, #zName, {0} }
  1692   1701   #define VFUNCTION(zName, nArg, iArg, bNC, xFunc) \
  1693   1702     {nArg, SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL), \
  1694         -   SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, #zName, {0} }
         1703  +   SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, 0, 0, #zName, {0} }
  1695   1704   #define DFUNCTION(zName, nArg, iArg, bNC, xFunc) \
  1696   1705     {nArg, SQLITE_FUNC_SLOCHNG|SQLITE_UTF8, \
  1697         -   0, 0, xFunc, 0, #zName, {0} }
         1706  +   0, 0, xFunc, 0, 0, 0, #zName, {0} }
  1698   1707   #define PURE_DATE(zName, nArg, iArg, bNC, xFunc) \
  1699   1708     {nArg, SQLITE_FUNC_SLOCHNG|SQLITE_UTF8|SQLITE_FUNC_CONSTANT, \
  1700         -   (void*)&sqlite3Config, 0, xFunc, 0, #zName, {0} }
         1709  +   (void*)&sqlite3Config, 0, xFunc, 0, 0, 0, #zName, {0} }
  1701   1710   #define FUNCTION2(zName, nArg, iArg, bNC, xFunc, extraFlags) \
  1702   1711     {nArg,SQLITE_FUNC_CONSTANT|SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL)|extraFlags,\
  1703         -   SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, #zName, {0} }
         1712  +   SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, 0, 0, #zName, {0} }
  1704   1713   #define STR_FUNCTION(zName, nArg, pArg, bNC, xFunc) \
  1705   1714     {nArg, SQLITE_FUNC_SLOCHNG|SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL), \
  1706         -   pArg, 0, xFunc, 0, #zName, }
         1715  +   pArg, 0, xFunc, 0, 0, 0, #zName, }
  1707   1716   #define LIKEFUNC(zName, nArg, arg, flags) \
  1708   1717     {nArg, SQLITE_FUNC_CONSTANT|SQLITE_UTF8|flags, \
  1709         -   (void *)arg, 0, likeFunc, 0, #zName, {0} }
         1718  +   (void *)arg, 0, likeFunc, 0, 0, 0, #zName, {0} }
  1710   1719   #define AGGREGATE(zName, nArg, arg, nc, xStep, xFinal) \
  1711   1720     {nArg, SQLITE_UTF8|(nc*SQLITE_FUNC_NEEDCOLL), \
  1712         -   SQLITE_INT_TO_PTR(arg), 0, xStep,xFinal,#zName, {0}}
         1721  +   SQLITE_INT_TO_PTR(arg), 0, xStep,xFinal,0,0,#zName, {0}}
  1713   1722   #define AGGREGATE2(zName, nArg, arg, nc, xStep, xFinal, extraFlags) \
  1714   1723     {nArg, SQLITE_UTF8|(nc*SQLITE_FUNC_NEEDCOLL)|extraFlags, \
  1715         -   SQLITE_INT_TO_PTR(arg), 0, xStep,xFinal,#zName, {0}}
         1724  +   SQLITE_INT_TO_PTR(arg), 0, xStep,xFinal,0,0,#zName, {0}}
         1725  +
         1726  +#define WFUNCTION(zName, nArg, arg, xStep, xFinal, xValue, xInverse) \
         1727  +  {nArg, SQLITE_UTF8, \
         1728  +   SQLITE_INT_TO_PTR(arg), 0, xStep,xFinal,xValue,xInverse,#zName, {0}}
  1716   1729   
  1717   1730   /*
  1718   1731   ** All current savepoints are stored in a linked list starting at
  1719   1732   ** sqlite3.pSavepoint. The first element in the list is the most recently
  1720   1733   ** opened savepoint. Savepoints are added to the list by the vdbe
  1721   1734   ** OP_Savepoint instruction.
  1722   1735   */
................................................................................
  2409   2422     i16 iRightJoinTable;   /* If EP_FromJoin, the right table of the join */
  2410   2423     u8 op2;                /* TK_REGISTER: original value of Expr.op
  2411   2424                            ** TK_COLUMN: the value of p5 for OP_Column
  2412   2425                            ** TK_AGG_FUNCTION: nesting depth */
  2413   2426     AggInfo *pAggInfo;     /* Used by TK_AGG_COLUMN and TK_AGG_FUNCTION */
  2414   2427     Table *pTab;           /* Table for TK_COLUMN expressions.  Can be NULL
  2415   2428                            ** for a column of an index on an expression */
         2429  +  Window *pWin;          /* Window definition for window functions */
  2416   2430   };
  2417   2431   
  2418   2432   /*
  2419   2433   ** The following are the meanings of bits in the Expr.flags field.
  2420   2434   */
  2421   2435   #define EP_FromJoin  0x000001 /* Originates in ON/USING clause of outer join */
  2422   2436   #define EP_Agg       0x000002 /* Contains one or more aggregate functions */
................................................................................
  2695   2709       AggInfo *pAggInfo;   /* Information about aggregates at this level */
  2696   2710       Upsert *pUpsert;     /* ON CONFLICT clause information from an upsert */
  2697   2711     } uNC;
  2698   2712     NameContext *pNext;  /* Next outer name context.  NULL for outermost */
  2699   2713     int nRef;            /* Number of names resolved by this context */
  2700   2714     int nErr;            /* Number of errors encountered while resolving names */
  2701   2715     u16 ncFlags;         /* Zero or more NC_* flags defined below */
         2716  +  Window *pWin;        /* List of window functions in this context */
  2702   2717   };
  2703   2718   
  2704   2719   /*
  2705   2720   ** Allowed values for the NameContext, ncFlags field.
  2706   2721   **
  2707   2722   ** Value constraints (all checked via assert()):
  2708   2723   **    NC_HasAgg    == SF_HasAgg
................................................................................
  2717   2732   #define NC_IdxExpr   0x0020  /* True if resolving columns of CREATE INDEX */
  2718   2733   #define NC_VarSelect 0x0040  /* A correlated subquery has been seen */
  2719   2734   #define NC_UEList    0x0080  /* True if uNC.pEList is used */
  2720   2735   #define NC_UAggInfo  0x0100  /* True if uNC.pAggInfo is used */
  2721   2736   #define NC_UUpsert   0x0200  /* True if uNC.pUpsert is used */
  2722   2737   #define NC_MinMaxAgg 0x1000  /* min/max aggregates seen.  See note above */
  2723   2738   #define NC_Complex   0x2000  /* True if a function or subquery seen */
         2739  +#define NC_AllowWin  0x4000  /* Window functions are allowed here */
  2724   2740   
  2725   2741   /*
  2726   2742   ** An instance of the following object describes a single ON CONFLICT
  2727   2743   ** clause in an upsert.
  2728   2744   **
  2729   2745   ** The pUpsertTarget field is only set if the ON CONFLICT clause includes
  2730   2746   ** conflict-target clause.  (In "ON CONFLICT(a,b)" the "(a,b)" is the
................................................................................
  2784   2800     ExprList *pGroupBy;    /* The GROUP BY clause */
  2785   2801     Expr *pHaving;         /* The HAVING clause */
  2786   2802     ExprList *pOrderBy;    /* The ORDER BY clause */
  2787   2803     Select *pPrior;        /* Prior select in a compound select statement */
  2788   2804     Select *pNext;         /* Next select to the left in a compound */
  2789   2805     Expr *pLimit;          /* LIMIT expression. NULL means not used. */
  2790   2806     With *pWith;           /* WITH clause attached to this select. Or NULL. */
         2807  +  Window *pWin;          /* List of window functions */
  2791   2808   };
  2792   2809   
  2793   2810   /*
  2794   2811   ** Allowed values for Select.selFlags.  The "SF" prefix stands for
  2795   2812   ** "Select Flag".
  2796   2813   **
  2797   2814   ** Value constraints (all checked via assert())
................................................................................
  3397   3414       struct SrcCount *pSrcCount;               /* Counting column references */
  3398   3415       struct CCurHint *pCCurHint;               /* Used by codeCursorHint() */
  3399   3416       int *aiCol;                               /* array of column indexes */
  3400   3417       struct IdxCover *pIdxCover;               /* Check for index coverage */
  3401   3418       struct IdxExprTrans *pIdxTrans;           /* Convert idxed expr to column */
  3402   3419       ExprList *pGroupBy;                       /* GROUP BY clause */
  3403   3420       Select *pSelect;                          /* HAVING to WHERE clause ctx */
         3421  +    struct WindowRewrite *pRewrite;           /* Window rewrite context */
  3404   3422     } u;
  3405   3423   };
  3406   3424   
  3407   3425   /* Forward declarations */
  3408   3426   int sqlite3WalkExpr(Walker*, Expr*);
  3409   3427   int sqlite3WalkExprList(Walker*, ExprList*);
  3410   3428   int sqlite3WalkSelect(Walker*, Select*);
................................................................................
  3447   3465   */
  3448   3466   struct TreeView {
  3449   3467     int iLevel;             /* Which level of the tree we are on */
  3450   3468     u8  bLine[100];         /* Draw vertical in column i if bLine[i] is true */
  3451   3469   };
  3452   3470   #endif /* SQLITE_DEBUG */
  3453   3471   
         3472  +struct Window {
         3473  +  Expr *pFilter;
         3474  +  ExprList *pPartition;
         3475  +  ExprList *pOrderBy;
         3476  +  u8 eType;               /* TK_RANGE or TK_ROWS */
         3477  +  u8 eStart;              /* UNBOUNDED, CURRENT, PRECEDING or FOLLOWING */
         3478  +  u8 eEnd;                /* UNBOUNDED, CURRENT, PRECEDING or FOLLOWING */
         3479  +  Expr *pStart;           /* Expression for "<expr> PRECEDING" */
         3480  +  Expr *pEnd;             /* Expression for "<expr> FOLLOWING" */
         3481  +  Window *pNextWin;       /* Next window function belonging to this SELECT */
         3482  +  int iEphCsr;            /* Temp table used by this window */
         3483  +  int regAccum;
         3484  +  int regResult;
         3485  +  FuncDef *pFunc;
         3486  +  int nArg;
         3487  +
         3488  +  Expr *pOwner;           /* Expression object this window is attached to */
         3489  +  int nBufferCol;         /* Number of columns in buffer table */
         3490  +  int iArgCol;            /* Offset of first argument for this function */
         3491  +};
         3492  +
         3493  +void sqlite3WindowDelete(sqlite3*, Window*);
         3494  +Window *sqlite3WindowAlloc(Parse*, int, int, Expr*, int , Expr*);
         3495  +void sqlite3WindowAttach(Parse*, Expr*, Window*);
         3496  +
  3454   3497   /*
  3455   3498   ** Assuming zIn points to the first byte of a UTF-8 character,
  3456   3499   ** advance zIn to point to the first byte of the next UTF-8 character.
  3457   3500   */
  3458   3501   #define SQLITE_SKIP_UTF8(zIn) {                        \
  3459   3502     if( (*(zIn++))>=0xc0 ){                              \
  3460   3503       while( (*zIn & 0xc0)==0x80 ){ zIn++; }             \

Changes to src/test1.c.

  7795   7795     extern int sqlite3WalTrace;
  7796   7796   #endif
  7797   7797   #ifdef SQLITE_TEST
  7798   7798   #ifdef SQLITE_ENABLE_FTS3
  7799   7799     extern int sqlite3_fts3_enable_parentheses;
  7800   7800   #endif
  7801   7801   #endif
         7802  +#if defined(SQLITE_ENABLE_SELECTTRACE)
         7803  +  extern int sqlite3SelectTrace;
         7804  +#endif
  7802   7805   
  7803   7806     for(i=0; i<sizeof(aCmd)/sizeof(aCmd[0]); i++){
  7804   7807       Tcl_CreateCommand(interp, aCmd[i].zName, aCmd[i].xProc, 0, 0);
  7805   7808     }
  7806   7809     for(i=0; i<sizeof(aObjCmd)/sizeof(aObjCmd[0]); i++){
  7807   7810       Tcl_CreateObjCommand(interp, aObjCmd[i].zName, 
  7808   7811           aObjCmd[i].xProc, aObjCmd[i].clientData, 0);
................................................................................
  7880   7883         (char*)&bitmask_size, TCL_LINK_INT|TCL_LINK_READ_ONLY);
  7881   7884     Tcl_LinkVar(interp, "longdouble_size",
  7882   7885         (char*)&longdouble_size, TCL_LINK_INT|TCL_LINK_READ_ONLY);
  7883   7886     Tcl_LinkVar(interp, "sqlite_sync_count",
  7884   7887         (char*)&sqlite3_sync_count, TCL_LINK_INT);
  7885   7888     Tcl_LinkVar(interp, "sqlite_fullsync_count",
  7886   7889         (char*)&sqlite3_fullsync_count, TCL_LINK_INT);
         7890  +#if defined(SQLITE_ENABLE_SELECTTRACE)
         7891  +  Tcl_LinkVar(interp, "sqlite3SelectTrace",
         7892  +      (char*)&sqlite3SelectTrace, TCL_LINK_INT);
         7893  +#endif
  7887   7894   #if defined(SQLITE_ENABLE_FTS3) && defined(SQLITE_TEST)
  7888   7895     Tcl_LinkVar(interp, "sqlite_fts3_enable_parentheses",
  7889   7896         (char*)&sqlite3_fts3_enable_parentheses, TCL_LINK_INT);
  7890   7897   #endif
  7891   7898     return TCL_OK;
  7892   7899   }

Changes to src/vdbe.c.

  6309   6309   ** the step function was not previously called.
  6310   6310   */
  6311   6311   case OP_AggFinal: {
  6312   6312     Mem *pMem;
  6313   6313     assert( pOp->p1>0 && pOp->p1<=(p->nMem+1 - p->nCursor) );
  6314   6314     pMem = &aMem[pOp->p1];
  6315   6315     assert( (pMem->flags & ~(MEM_Null|MEM_Agg))==0 );
         6316  +  if( pOp->p3 ){
         6317  +    rc = sqlite3VdbeMemAggValue(pMem, &aMem[pOp->p3], pOp->p4.pFunc);
         6318  +  }else{
  6316   6319     rc = sqlite3VdbeMemFinalize(pMem, pOp->p4.pFunc);
         6320  +  }
  6317   6321     if( rc ){
  6318   6322       sqlite3VdbeError(p, "%s", sqlite3_value_text(pMem));
  6319   6323       goto abort_due_to_error;
  6320   6324     }
  6321   6325     sqlite3VdbeChangeEncoding(pMem, encoding);
  6322   6326     UPDATE_MAX_BLOBSIZE(pMem);
  6323   6327     if( sqlite3VdbeMemTooBig(pMem) ){

Changes to src/vdbeInt.h.

   490    490   void sqlite3VdbeIntegerAffinity(Mem*);
   491    491   int sqlite3VdbeMemRealify(Mem*);
   492    492   int sqlite3VdbeMemNumerify(Mem*);
   493    493   void sqlite3VdbeMemCast(Mem*,u8,u8);
   494    494   int sqlite3VdbeMemFromBtree(BtCursor*,u32,u32,Mem*);
   495    495   void sqlite3VdbeMemRelease(Mem *p);
   496    496   int sqlite3VdbeMemFinalize(Mem*, FuncDef*);
          497  +int sqlite3VdbeMemAggValue(Mem*, Mem*, FuncDef*);
   497    498   const char *sqlite3OpcodeName(int);
   498    499   int sqlite3VdbeMemGrow(Mem *pMem, int n, int preserve);
   499    500   int sqlite3VdbeMemClearAndResize(Mem *pMem, int n);
   500    501   int sqlite3VdbeCloseStatement(Vdbe *, int);
   501    502   void sqlite3VdbeFrameDelete(VdbeFrame*);
   502    503   int sqlite3VdbeFrameRestore(VdbeFrame *);
   503    504   #ifdef SQLITE_ENABLE_PREUPDATE_HOOK

Changes to src/vdbemem.c.

   411    411     pFunc->xFinalize(&ctx); /* IMP: R-24505-23230 */
   412    412     assert( (pMem->flags & MEM_Dyn)==0 );
   413    413     if( pMem->szMalloc>0 ) sqlite3DbFreeNN(pMem->db, pMem->zMalloc);
   414    414     memcpy(pMem, &t, sizeof(t));
   415    415     return ctx.isError;
   416    416   }
   417    417   
          418  +int sqlite3VdbeMemAggValue(Mem *pAccum, Mem *pOut, FuncDef *pFunc){
          419  +  sqlite3_context ctx;
          420  +  Mem t;
          421  +  assert( pFunc!=0 );
          422  +  assert( pFunc->xValue!=0 );
          423  +  assert( (pAccum->flags & MEM_Null)!=0 || pFunc==pAccum->u.pDef );
          424  +  assert( pAccum->db==0 || sqlite3_mutex_held(pAccum->db->mutex) );
          425  +  memset(&ctx, 0, sizeof(ctx));
          426  +  memset(&t, 0, sizeof(t));
          427  +  t.flags = MEM_Null;
          428  +  t.db = pAccum->db;
          429  +  ctx.pOut = pOut;
          430  +  ctx.pMem = pAccum;
          431  +  ctx.pFunc = pFunc;
          432  +  pFunc->xValue(&ctx);
          433  +  return ctx.isError;
          434  +}
   418    435   /*
   419    436   ** If the memory cell contains a value that must be freed by
   420    437   ** invoking the external callback in Mem.xDel, then this routine
   421    438   ** will free that value.  It also sets Mem.flags to MEM_Null.
   422    439   **
   423    440   ** This is a helper routine for sqlite3VdbeMemSetNull() and
   424    441   ** for sqlite3VdbeMemRelease().  Use those other routines as the

Added src/window.c.

            1  +/*
            2  +**
            3  +** The author disclaims copyright to this source code.  In place of
            4  +** a legal notice, here is a blessing:
            5  +**
            6  +**    May you do good and not evil.
            7  +**    May you find forgiveness for yourself and forgive others.
            8  +**    May you share freely, never taking more than you give.
            9  +**
           10  +*************************************************************************
           11  +*/
           12  +#include "sqliteInt.h"
           13  +
           14  +void sqlite3WindowDelete(sqlite3 *db, Window *p){
           15  +  if( p ){
           16  +    sqlite3ExprDelete(db, p->pFilter);
           17  +    sqlite3ExprListDelete(db, p->pPartition);
           18  +    sqlite3ExprListDelete(db, p->pOrderBy);
           19  +    sqlite3ExprDelete(db, p->pEnd);
           20  +    sqlite3ExprDelete(db, p->pStart);
           21  +    sqlite3DbFree(db, p);
           22  +  }
           23  +}
           24  +
           25  +Window *sqlite3WindowAlloc(
           26  +  Parse *pParse, 
           27  +  int eType,
           28  +  int eEnd, Expr *pEnd, 
           29  +  int eStart, Expr *pStart
           30  +){
           31  +  Window *pWin = (Window*)sqlite3DbMallocZero(pParse->db, sizeof(Window));
           32  +
           33  +  if( pWin ){
           34  +    pWin->eType = eType;
           35  +    pWin->eStart = eStart;
           36  +    pWin->eEnd = eEnd;
           37  +    pWin->pEnd = pEnd;
           38  +    pWin->pStart = pStart;
           39  +  }else{
           40  +    sqlite3ExprDelete(pParse->db, pEnd);
           41  +    sqlite3ExprDelete(pParse->db, pStart);
           42  +  }
           43  +
           44  +  return pWin;
           45  +}
           46  +
           47  +void sqlite3WindowAttach(Parse *pParse, Expr *p, Window *pWin){
           48  +  if( p ){
           49  +    p->pWin = pWin;
           50  +  }else{
           51  +    sqlite3WindowDelete(pParse->db, pWin);
           52  +  }
           53  +}

Changes to test/permutations.test.

   163    163   
   164    164   test_suite "veryquick" -prefix "" -description {
   165    165     "Very" quick test suite. Runs in minutes on a workstation.
   166    166     This test suite is the same as the "quick" tests, except that some files
   167    167     that test malloc and IO errors are omitted.
   168    168   } -files [
   169    169     test_set $allquicktests -exclude *malloc* *ioerr* *fault* *bigfile* *_err* \
   170         -      *fts5corrupt* *fts5big* *fts5aj*
          170  +      *fts5corrupt* *fts5big* *fts5aj* *expert* table.test
   171    171   ]
   172    172   
   173    173   test_suite "extraquick" -prefix "" -description {
   174    174     "Extra" quick test suite. Runs in a few minutes on a workstation.
   175    175     This test suite is the same as the "veryquick" tests, except that
   176    176     slower tests are omitted.
   177    177   } -files [

Added test/window1.test.

            1  +# 2018 May 8
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library.
           12  +#
           13  +
           14  +set testdir [file dirname $argv0]
           15  +source $testdir/tester.tcl
           16  +set testprefix window1
           17  +
           18  +
           19  +do_execsql_test 1.0 {
           20  +  CREATE TABLE t1(a, b, c, d);
           21  +  INSERT INTO t1 VALUES(1, 2, 3, 4);
           22  +  INSERT INTO t1 VALUES(5, 6, 7, 8);
           23  +  INSERT INTO t1 VALUES(9, 10, 11, 12);
           24  +}
           25  +
           26  +do_execsql_test 1.1 {
           27  +  SELECT sum(b) OVER () FROM t1
           28  +} {18 18 18}
           29  +
           30  +do_execsql_test 1.2 {
           31  +  SELECT a, sum(b) OVER () FROM t1
           32  +} {1 18 5 18 9 18}
           33  +
           34  +do_execsql_test 1.3 {
           35  +  SELECT a, 4 + sum(b) OVER () FROM t1
           36  +} {1 22 5 22 9 22}
           37  +
           38  +do_execsql_test 1.4 {
           39  +  SELECT a + 4 + sum(b) OVER () FROM t1
           40  +} {23 27 31}
           41  +
           42  +do_execsql_test 1.5 {
           43  +  SELECT a, sum(b) OVER (PARTITION BY c) FROM t1
           44  +} {1 2 5 6 9 10}
           45  +
           46  +foreach {tn sql} {
           47  +  1 "SELECT sum(b) OVER () FROM t1"
           48  +  2 "SELECT sum(b) OVER (PARTITION BY c) FROM t1"
           49  +  3 "SELECT sum(b) OVER (ORDER BY c) FROM t1"
           50  +  4 "SELECT sum(b) OVER (PARTITION BY d ORDER BY c) FROM t1"
           51  +  5 "SELECT sum(b) FILTER (WHERE a>0) OVER (PARTITION BY d ORDER BY c) FROM t1"
           52  +  6 "SELECT sum(b) OVER (ORDER BY c RANGE UNBOUNDED PRECEDING) FROM t1"
           53  +  7 "SELECT sum(b) OVER (ORDER BY c ROWS 45 PRECEDING) FROM t1"
           54  +  8 "SELECT sum(b) OVER (ORDER BY c RANGE CURRENT ROW) FROM t1"
           55  +  9 "SELECT sum(b) OVER (ORDER BY c RANGE BETWEEN UNBOUNDED PRECEDING 
           56  +     AND CURRENT ROW) FROM t1"
           57  + 10 "SELECT sum(b) OVER (ORDER BY c ROWS BETWEEN UNBOUNDED PRECEDING 
           58  +     AND UNBOUNDED FOLLOWING) FROM t1"
           59  +} {
           60  +  do_test 2.$tn { lindex [catchsql $sql] 0 } 0
           61  +}
           62  +
           63  +foreach {tn sql} {
           64  +  1 "SELECT * FROM t1 WHERE sum(b) OVER ()"
           65  +  2 "SELECT * FROM t1 GROUP BY sum(b) OVER ()"
           66  +  3 "SELECT * FROM t1 GROUP BY a HAVING sum(b) OVER ()"
           67  +} {
           68  +  do_catchsql_test 3.$tn $sql {1 {misuse of window function sum()}}
           69  +}
           70  +
           71  +do_execsql_test 4.0 {
           72  +  CREATE TABLE t2(a, b, c);
           73  +  INSERT INTO t2 VALUES(0, 0, 0);
           74  +  INSERT INTO t2 VALUES(1, 1, 1);
           75  +  INSERT INTO t2 VALUES(2, 0, 2);
           76  +  INSERT INTO t2 VALUES(3, 1, 0);
           77  +  INSERT INTO t2 VALUES(4, 0, 1);
           78  +  INSERT INTO t2 VALUES(5, 1, 2);
           79  +  INSERT INTO t2 VALUES(6, 0, 0);
           80  +}
           81  +
           82  +do_execsql_test 4.1 {
           83  +  SELECT a, sum(a) OVER (PARTITION BY b) FROM t2;
           84  +} {
           85  +  0 12  2 12  4 12  6 12   1  9  3  9  5  9 
           86  +}
           87  +
           88  +do_execsql_test 4.2 {
           89  +  SELECT a, sum(a) OVER (PARTITION BY b) FROM t2 ORDER BY a;
           90  +} {
           91  +  0 12  1  9  2 12  3  9  4 12  5  9 6 12   
           92  +}
           93  +
           94  +do_execsql_test 4.3 {
           95  +  SELECT a, sum(a) OVER () FROM t2 ORDER BY a;
           96  +} {
           97  +  0 21  1  21  2 21  3  21  4 21  5  21 6 21   
           98  +}
           99  +
          100  +do_execsql_test 4.4 {
          101  +  SELECT a, sum(a) OVER (ORDER BY a) FROM t2;
          102  +} {
          103  +  0 0  1 1  2 3  3 6  4 10  5 15  6 21
          104  +}
          105  +
          106  +do_execsql_test 4.5 {
          107  +  SELECT a, sum(a) OVER (PARTITION BY b ORDER BY a) FROM t2 ORDER BY a
          108  +} {
          109  +  0 0  1 1  2 2  3 4  4 6  5 9  6 12
          110  +}
          111  +
          112  +finish_test

Changes to tool/mkkeywordhash.c.

   176    176     { "COLLATE",          "TK_COLLATE",      ALWAYS                 },
   177    177     { "COLUMN",           "TK_COLUMNKW",     ALTER                  },
   178    178     { "COMMIT",           "TK_COMMIT",       ALWAYS                 },
   179    179     { "CONFLICT",         "TK_CONFLICT",     CONFLICT               },
   180    180     { "CONSTRAINT",       "TK_CONSTRAINT",   ALWAYS                 },
   181    181     { "CREATE",           "TK_CREATE",       ALWAYS                 },
   182    182     { "CROSS",            "TK_JOIN_KW",      ALWAYS                 },
          183  +  { "CURRENT",          "TK_CURRENT",      ALWAYS                 },
   183    184     { "CURRENT_DATE",     "TK_CTIME_KW",     ALWAYS                 },
   184    185     { "CURRENT_TIME",     "TK_CTIME_KW",     ALWAYS                 },
   185    186     { "CURRENT_TIMESTAMP","TK_CTIME_KW",     ALWAYS                 },
   186    187     { "DATABASE",         "TK_DATABASE",     ATTACH                 },
   187    188     { "DEFAULT",          "TK_DEFAULT",      ALWAYS                 },
   188    189     { "DEFERRED",         "TK_DEFERRED",     ALWAYS                 },
   189    190     { "DEFERRABLE",       "TK_DEFERRABLE",   FKEY                   },
................................................................................
   198    199     { "ELSE",             "TK_ELSE",         ALWAYS                 },
   199    200     { "ESCAPE",           "TK_ESCAPE",       ALWAYS                 },
   200    201     { "EXCEPT",           "TK_EXCEPT",       COMPOUND               },
   201    202     { "EXCLUSIVE",        "TK_EXCLUSIVE",    ALWAYS                 },
   202    203     { "EXISTS",           "TK_EXISTS",       ALWAYS                 },
   203    204     { "EXPLAIN",          "TK_EXPLAIN",      EXPLAIN                },
   204    205     { "FAIL",             "TK_FAIL",         CONFLICT|TRIGGER       },
          206  +  { "FILTER",           "TK_FILTER",       ALWAYS                 },
          207  +  { "FOLLOWING",        "TK_FOLLOWING",    ALWAYS                 },
   205    208     { "FOR",              "TK_FOR",          TRIGGER                },
   206    209     { "FOREIGN",          "TK_FOREIGN",      FKEY                   },
   207    210     { "FROM",             "TK_FROM",         ALWAYS                 },
   208    211     { "FULL",             "TK_JOIN_KW",      ALWAYS                 },
   209    212     { "GLOB",             "TK_LIKE_KW",      ALWAYS                 },
   210    213     { "GROUP",            "TK_GROUP",        ALWAYS                 },
   211    214     { "HAVING",           "TK_HAVING",       ALWAYS                 },
................................................................................
   237    240     { "NULL",             "TK_NULL",         ALWAYS                 },
   238    241     { "OF",               "TK_OF",           ALWAYS                 },
   239    242     { "OFFSET",           "TK_OFFSET",       ALWAYS                 },
   240    243     { "ON",               "TK_ON",           ALWAYS                 },
   241    244     { "OR",               "TK_OR",           ALWAYS                 },
   242    245     { "ORDER",            "TK_ORDER",        ALWAYS                 },
   243    246     { "OUTER",            "TK_JOIN_KW",      ALWAYS                 },
          247  +  { "OVER",             "TK_OVER",         ALWAYS                 },
          248  +  { "PARTITION",        "TK_PARTITION",    ALWAYS                 },
   244    249     { "PLAN",             "TK_PLAN",         EXPLAIN                },
   245    250     { "PRAGMA",           "TK_PRAGMA",       PRAGMA                 },
          251  +  { "PRECEDING",        "TK_PRECEDING",    ALWAYS                 },
   246    252     { "PRIMARY",          "TK_PRIMARY",      ALWAYS                 },
   247    253     { "QUERY",            "TK_QUERY",        EXPLAIN                },
   248    254     { "RAISE",            "TK_RAISE",        TRIGGER                },
          255  +  { "RANGE",            "TK_RANGE",        ALWAYS                 },
   249    256     { "RECURSIVE",        "TK_RECURSIVE",    CTE                    },
   250    257     { "REFERENCES",       "TK_REFERENCES",   FKEY                   },
   251    258     { "REGEXP",           "TK_LIKE_KW",      ALWAYS                 },
   252    259     { "REINDEX",          "TK_REINDEX",      REINDEX                },
   253    260     { "RELEASE",          "TK_RELEASE",      ALWAYS                 },
   254    261     { "RENAME",           "TK_RENAME",       ALTER                  },
   255    262     { "REPLACE",          "TK_REPLACE",      CONFLICT               },
   256    263     { "RESTRICT",         "TK_RESTRICT",     FKEY                   },
   257    264     { "RIGHT",            "TK_JOIN_KW",      ALWAYS                 },
   258    265     { "ROLLBACK",         "TK_ROLLBACK",     ALWAYS                 },
   259    266     { "ROW",              "TK_ROW",          TRIGGER                },
          267  +  { "ROWS",             "TK_ROWS",         ALWAYS                 },
   260    268     { "SAVEPOINT",        "TK_SAVEPOINT",    ALWAYS                 },
   261    269     { "SELECT",           "TK_SELECT",       ALWAYS                 },
   262    270     { "SET",              "TK_SET",          ALWAYS                 },
   263    271     { "TABLE",            "TK_TABLE",        ALWAYS                 },
   264    272     { "TEMP",             "TK_TEMP",         ALWAYS                 },
   265    273     { "TEMPORARY",        "TK_TEMP",         ALWAYS                 },
   266    274     { "THEN",             "TK_THEN",         ALWAYS                 },
   267    275     { "TO",               "TK_TO",           ALWAYS                 },
   268    276     { "TRANSACTION",      "TK_TRANSACTION",  ALWAYS                 },
   269    277     { "TRIGGER",          "TK_TRIGGER",      TRIGGER                },
          278  +  { "UNBOUNDED",        "TK_UNBOUNDED",    ALWAYS                 },
   270    279     { "UNION",            "TK_UNION",        COMPOUND               },
   271    280     { "UNIQUE",           "TK_UNIQUE",       ALWAYS                 },
   272    281     { "UPDATE",           "TK_UPDATE",       ALWAYS                 },
   273    282     { "USING",            "TK_USING",        ALWAYS                 },
   274    283     { "VACUUM",           "TK_VACUUM",       VACUUM                 },
   275    284     { "VALUES",           "TK_VALUES",       ALWAYS                 },
   276    285     { "VIEW",             "TK_VIEW",         VIEW                   },