/ Check-in [f2d5f7a24c]
Login

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

Overview
Comment:In order to identify the first row of each partition, check if the rowid in the ephemeral table is 1 instead of using a dedicated flag register.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | window-functions
Files: files | file ages | folders
SHA3-256: f2d5f7a24c7aa483c579706c5bd7268a74da6d53025d78fa8642908c2aed1707
User & Date: dan 2019-03-16 10:15:24
Wiki:window-functions
Context
2019-03-16
20:29
Fix problems with RANGE windows and string, blob and NULL values. check-in: cebe09e11c user: dan tags: window-functions
10:15
In order to identify the first row of each partition, check if the rowid in the ephemeral table is 1 instead of using a dedicated flag register. check-in: f2d5f7a24c user: dan tags: window-functions
2019-03-15
20:46
Implement the EXCLUDE clause for window frames. check-in: 9b43c3ee2e user: dan tags: window-functions
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/sqliteInt.h.

  3572   3572     int regResult;
  3573   3573     int csrApp;             /* Function cursor (used by min/max) */
  3574   3574     int regApp;             /* Function register (also used by min/max) */
  3575   3575     int regPart;            /* Array of registers for PARTITION BY values */
  3576   3576     Expr *pOwner;           /* Expression object this window is attached to */
  3577   3577     int nBufferCol;         /* Number of columns in buffer table */
  3578   3578     int iArgCol;            /* Offset of first argument for this function */
  3579         -  int regFirst;
  3580         -
         3579  +  int regOne;             /* Register containing constant value 1 */
  3581   3580     int regStartRowid;
  3582   3581     int regEndRowid;
  3583   3582   };
  3584   3583   
  3585   3584   #ifndef SQLITE_OMIT_WINDOWFUNC
  3586   3585   void sqlite3WindowDelete(sqlite3*, Window*);
  3587   3586   void sqlite3WindowListDelete(sqlite3 *db, Window *p);

Changes to src/window.c.

  1202   1202     if( pMWin->pPartition ){
  1203   1203       int nExpr = pMWin->pPartition->nExpr;
  1204   1204       pMWin->regPart = pParse->nMem+1;
  1205   1205       pParse->nMem += nExpr;
  1206   1206       sqlite3VdbeAddOp3(v, OP_Null, 0, pMWin->regPart, pMWin->regPart+nExpr-1);
  1207   1207     }
  1208   1208   
  1209         -  pMWin->regFirst = ++pParse->nMem;
  1210         -  sqlite3VdbeAddOp2(v, OP_Integer, 1, pMWin->regFirst);
         1209  +  pMWin->regOne = ++pParse->nMem;
         1210  +  sqlite3VdbeAddOp2(v, OP_Integer, 1, pMWin->regOne);
  1211   1211   
  1212   1212     if( pMWin->eExclude ){
  1213   1213       pMWin->regStartRowid = ++pParse->nMem;
  1214   1214       pMWin->regEndRowid = ++pParse->nMem;
  1215   1215       pMWin->csrApp = pParse->nTab++;
  1216   1216       sqlite3VdbeAddOp2(v, OP_Integer, 1, pMWin->regStartRowid);
  1217   1217       sqlite3VdbeAddOp2(v, OP_Integer, 0, pMWin->regEndRowid);
................................................................................
  2300   2300     Window *pMWin = p->pWin;
  2301   2301     ExprList *pOrderBy = pMWin->pOrderBy;
  2302   2302     Vdbe *v = sqlite3GetVdbe(pParse);
  2303   2303     int csrWrite;                   /* Cursor used to write to eph. table */
  2304   2304     int csrInput = p->pSrc->a[0].iCursor;     /* Cursor of sub-select */
  2305   2305     int nInput = p->pSrc->a[0].pTab->nCol;    /* Number of cols returned by sub */
  2306   2306     int iInput;                               /* To iterate through sub cols */
  2307         -  int addrIfNot;                  /* Address of OP_IfNot */
         2307  +  int addrNe;                     /* Address of OP_Ne */
  2308   2308     int addrGosubFlush;             /* Address of OP_Gosub to flush: */
  2309   2309     int addrInteger;                /* Address of OP_Integer */
  2310   2310     int addrEmpty;                  /* Address of OP_Rewind in flush: */
  2311   2311     int regStart = 0;               /* Value of <expr> PRECEDING */
  2312   2312     int regEnd = 0;                 /* Value of <expr> FOLLOWING */
  2313   2313     int regNew;                     /* Array of registers holding new input row */
  2314   2314     int regRecord;                  /* regNew array in record form */
................................................................................
  2431   2431       VdbeComment((v, "call flush_partition"));
  2432   2432       sqlite3VdbeAddOp3(v, OP_Copy, regNewPart, pMWin->regPart, nPart-1);
  2433   2433     }
  2434   2434   
  2435   2435     /* Insert the new row into the ephemeral table */
  2436   2436     sqlite3VdbeAddOp2(v, OP_NewRowid, csrWrite, regRowid);
  2437   2437     sqlite3VdbeAddOp3(v, OP_Insert, csrWrite, regRecord, regRowid);
  2438         -
  2439         -  addrIfNot = sqlite3VdbeAddOp1(v, OP_IfNot, pMWin->regFirst);
         2438  +  addrNe = sqlite3VdbeAddOp3(v, OP_Ne, pMWin->regOne, 0, regRowid);
  2440   2439   
  2441   2440     /* This block is run for the first row of each partition */
  2442   2441     s.regArg = windowInitAccum(pParse, pMWin);
  2443   2442   
  2444   2443     if( regStart ){
  2445   2444       sqlite3ExprCode(pParse, pMWin->pStart, regStart);
  2446   2445       windowCheckValue(pParse, regStart, 0 + (pMWin->eType==TK_RANGE ? 3 : 0));
................................................................................
  2473   2472     if( regPeer && pOrderBy ){
  2474   2473       sqlite3VdbeAddOp3(v, OP_Copy, regNewPeer, regPeer, pOrderBy->nExpr-1);
  2475   2474       sqlite3VdbeAddOp3(v, OP_Copy, regPeer, s.start.reg, pOrderBy->nExpr-1);
  2476   2475       sqlite3VdbeAddOp3(v, OP_Copy, regPeer, s.current.reg, pOrderBy->nExpr-1);
  2477   2476       sqlite3VdbeAddOp3(v, OP_Copy, regPeer, s.end.reg, pOrderBy->nExpr-1);
  2478   2477     }
  2479   2478   
  2480         -  sqlite3VdbeAddOp2(v, OP_Integer, 0, pMWin->regFirst);
  2481   2479     sqlite3VdbeAddOp2(v, OP_Goto, 0, lblWhereEnd);
  2482   2480   
  2483         -  sqlite3VdbeJumpHere(v, addrIfNot);
         2481  +  sqlite3VdbeJumpHere(v, addrNe);
  2484   2482     if( regPeer ){
  2485   2483       windowIfNewPeer(pParse, pOrderBy, regNewPeer, regPeer, lblWhereEnd);
  2486   2484     }
  2487   2485     if( pMWin->eStart==TK_FOLLOWING ){
  2488   2486       windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0);
  2489   2487       if( pMWin->eEnd!=TK_UNBOUNDED ){
  2490   2488         if( pMWin->eType==TK_RANGE ){
................................................................................
  2582   2580       windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
  2583   2581       sqlite3VdbeAddOp2(v, OP_Goto, 0, addrStart);
  2584   2582       sqlite3VdbeJumpHere(v, addrBreak);
  2585   2583     }
  2586   2584     sqlite3VdbeJumpHere(v, addrEmpty);
  2587   2585   
  2588   2586     sqlite3VdbeAddOp1(v, OP_ResetSorter, s.current.csr);
  2589         -  sqlite3VdbeAddOp2(v, OP_Integer, 1, pMWin->regFirst);
  2590   2587     if( pMWin->pPartition ){
  2591   2588       if( pMWin->regStartRowid ){
  2592   2589         sqlite3VdbeAddOp2(v, OP_Integer, 1, pMWin->regStartRowid);
  2593   2590         sqlite3VdbeAddOp2(v, OP_Integer, 0, pMWin->regEndRowid);
  2594   2591       }
  2595   2592       sqlite3VdbeChangeP1(v, addrInteger, sqlite3VdbeCurrentAddr(v));
  2596   2593       sqlite3VdbeAddOp1(v, OP_Return, regFlushPart);
  2597   2594     }
  2598   2595   }
  2599   2596   
  2600   2597   #endif /* SQLITE_OMIT_WINDOWFUNC */