/ Check-in [236cb75b]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Do not flatten sub-queries that contain window functions.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | exp-window-functions
Files: files | file ages | folders
SHA3-256: 236cb75bd1f0d5eb86aa5f52d8d548e7263c34633833dcea9dfc934f142113b8
User & Date: dan 2018-06-08 16:11:55
Context
2018-06-08
20:58
Add support for the WINDOW clause. check-in: 19c983b5 user: dan tags: exp-window-functions
16:11
Do not flatten sub-queries that contain window functions. check-in: 236cb75b user: dan tags: exp-window-functions
11:45
Fixes to allow group_concat() to be used as a window function. check-in: 89bbc9ba user: dan tags: exp-window-functions
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

  1178   1178       if( flags&EXPRDUP_REDUCE ){
  1179   1179         nByte += dupedExprSize(p->pLeft, flags) + dupedExprSize(p->pRight, flags);
  1180   1180       }
  1181   1181     }
  1182   1182     return nByte;
  1183   1183   }
  1184   1184   
  1185         -static Window *winDup(sqlite3 *db, Window *p){
  1186         -  Window *pNew = 0;
  1187         -  if( p ){
  1188         -    pNew = sqlite3DbMallocZero(db, sizeof(Window));
  1189         -    if( pNew ){
  1190         -      pNew->pFilter = sqlite3ExprDup(db, p->pFilter, 0);
  1191         -      pNew->pPartition = sqlite3ExprListDup(db, p->pPartition, 0);
  1192         -      pNew->pOrderBy = sqlite3ExprListDup(db, p->pOrderBy, 0);
  1193         -      pNew->eType = p->eType;
  1194         -      pNew->eEnd = p->eEnd;
  1195         -      pNew->eStart = p->eStart;
  1196         -      pNew->pStart = sqlite3ExprDup(db, pNew->pStart, 0);
  1197         -      pNew->pEnd = sqlite3ExprDup(db, pNew->pEnd, 0);
  1198         -    }
  1199         -  }
  1200         -  return pNew;
  1201         -}
  1202         -
  1203   1185   /*
  1204   1186   ** This function is similar to sqlite3ExprDup(), except that if pzBuffer 
  1205   1187   ** is not NULL then *pzBuffer is assumed to point to a buffer large enough 
  1206   1188   ** to store the copy of expression p, the copies of p->u.zToken
  1207   1189   ** (if applicable), and the copies of the p->pLeft and p->pRight expressions,
  1208   1190   ** if any. Before returning, *pzBuffer is set to the first byte past the
  1209   1191   ** portion of the buffer copied into by this function.
................................................................................
  1285   1267         if( pzBuffer ){
  1286   1268           *pzBuffer = zAlloc;
  1287   1269         }
  1288   1270       }else{
  1289   1271         if( ExprHasProperty(p, EP_Reduced|EP_TokenOnly) ){
  1290   1272           pNew->pWin = 0;
  1291   1273         }else{
  1292         -        pNew->pWin = winDup(db, p->pWin);
         1274  +        pNew->pWin = sqlite3WindowDup(db, p->pWin);
  1293   1275         }
  1294   1276         if( !ExprHasProperty(p, EP_TokenOnly|EP_Leaf) ){
  1295   1277           if( pNew->op==TK_SELECT_COLUMN ){
  1296   1278             pNew->pLeft = p->pLeft;
  1297   1279             assert( p->iColumn==0 || p->pRight==0 );
  1298   1280             assert( p->pRight==0  || p->pRight==p->pLeft );
  1299   1281           }else{

Changes to src/select.c.

  3708   3708     pSrc = p->pSrc;
  3709   3709     assert( pSrc && iFrom>=0 && iFrom<pSrc->nSrc );
  3710   3710     pSubitem = &pSrc->a[iFrom];
  3711   3711     iParent = pSubitem->iCursor;
  3712   3712     pSub = pSubitem->pSelect;
  3713   3713     assert( pSub!=0 );
  3714   3714   
  3715         -  if( p->pWin ) return 0;
         3715  +  if( p->pWin || pSub->pWin ) return 0;
  3716   3716   
  3717   3717     pSubSrc = pSub->pSrc;
  3718   3718     assert( pSubSrc );
  3719   3719     /* Prior to version 3.1.2, when LIMIT and OFFSET had to be simple constants,
  3720   3720     ** not arbitrary expressions, we allowed some combining of LIMIT and OFFSET
  3721   3721     ** because they could be computed at compile-time.  But when LIMIT and OFFSET
  3722   3722     ** became arbitrary expressions, we were forced to add restrictions (13)
................................................................................
  5894   5894       if( sSort.addrSortIndex>=0 && sSort.pOrderBy==0 ){
  5895   5895         sqlite3VdbeChangeToNoop(v, sSort.addrSortIndex);
  5896   5896       }
  5897   5897   
  5898   5898       assert( p->pEList==pEList );
  5899   5899       if( pWin ){
  5900   5900         int addrGosub = sqlite3VdbeMakeLabel(v);
         5901  +      int iCont = sqlite3VdbeMakeLabel(v);
  5901   5902         int regGosub = ++pParse->nMem;
  5902   5903         int addr = 0;
  5903         -      int bLoop = 0;
  5904   5904   
  5905         -      sqlite3WindowCodeStep(pParse, p, pWInfo, regGosub, addrGosub, &bLoop);
         5905  +      sqlite3WindowCodeStep(pParse, p, pWInfo, regGosub, addrGosub);
  5906   5906   
  5907         -      sqlite3VdbeAddOp0(v, OP_Goto);
         5907  +      addr = sqlite3VdbeAddOp0(v, OP_Goto);
  5908   5908         sqlite3VdbeResolveLabel(v, addrGosub);
  5909         -      if( bLoop ){
  5910         -        addr = sqlite3VdbeAddOp1(v, OP_Rewind, pWin->iEphCsr);
  5911         -      }else{
  5912         -        addr = sqlite3VdbeCurrentAddr(v);
  5913         -      }
  5914         -      selectInnerLoop(pParse, p, -1, &sSort, &sDistinct, pDest, addr+1, 0);
  5915         -      if( bLoop ){
  5916         -        sqlite3VdbeAddOp2(v, OP_Next, pWin->iEphCsr, addr+1);
  5917         -        sqlite3VdbeJumpHere(v, addr);
  5918         -      }
         5909  +      selectInnerLoop(pParse, p, -1, &sSort, &sDistinct, pDest, iCont, 0);
         5910  +      sqlite3VdbeResolveLabel(v, iCont);
  5919   5911         sqlite3VdbeAddOp1(v, OP_Return, regGosub);
  5920         -      sqlite3VdbeJumpHere(v, addr-1);       /* OP_Goto jumps here */
         5912  +      sqlite3VdbeJumpHere(v, addr);
  5921   5913   
  5922   5914       }else{
  5923   5915         /* Use the standard inner loop. */
  5924   5916         selectInnerLoop(pParse, p, -1, &sSort, &sDistinct, pDest,
  5925   5917             sqlite3WhereContinueLabel(pWInfo),
  5926   5918             sqlite3WhereBreakLabel(pWInfo));
  5927   5919   

Changes to src/sqliteInt.h.

  3498   3498   };
  3499   3499   
  3500   3500   void sqlite3WindowDelete(sqlite3*, Window*);
  3501   3501   Window *sqlite3WindowAlloc(Parse*, int, int, Expr*, int , Expr*);
  3502   3502   void sqlite3WindowAttach(Parse*, Expr*, Window*);
  3503   3503   int sqlite3WindowCompare(Parse*, Window*, Window*);
  3504   3504   void sqlite3WindowCodeInit(Parse*, Window*);
  3505         -void sqlite3WindowCodeStep(Parse*, Select*, WhereInfo*, int, int, int*);
         3505  +void sqlite3WindowCodeStep(Parse*, Select*, WhereInfo*, int, int);
  3506   3506   int sqlite3WindowRewrite(Parse*, Select*);
  3507   3507   int sqlite3ExpandSubquery(Parse*, struct SrcList_item*);
  3508   3508   void sqlite3WindowUpdate(Parse*, Window*, FuncDef*);
         3509  +Window *sqlite3WindowDup(sqlite3 *db, Window *p);
  3509   3510   
  3510   3511   /*
  3511   3512   ** Assuming zIn points to the first byte of a UTF-8 character,
  3512   3513   ** advance zIn to point to the first byte of the next UTF-8 character.
  3513   3514   */
  3514   3515   #define SQLITE_SKIP_UTF8(zIn) {                        \
  3515   3516     if( (*(zIn++))>=0xc0 ){                              \

Changes to src/window.c.

  1556   1556         }else{
  1557   1557           addrJump = 0;
  1558   1558         }
  1559   1559         windowAggFinal(pParse, pMWin, pMWin->eStart==TK_CURRENT);
  1560   1560         if( addrGoto ) sqlite3VdbeJumpHere(v, addrGoto);
  1561   1561       }
  1562   1562   
         1563  +    sqlite3VdbeAddOp2(v, OP_Rewind, pMWin->iEphCsr,sqlite3VdbeCurrentAddr(v)+3);
  1563   1564       sqlite3VdbeAddOp2(v, OP_Gosub, regGosub, addrGosub);
         1565  +    sqlite3VdbeAddOp2(v, OP_Next, pMWin->iEphCsr, sqlite3VdbeCurrentAddr(v)-1);
         1566  +
  1564   1567       sqlite3VdbeAddOp1(v, OP_ResetSorter, pMWin->iEphCsr);
  1565   1568       sqlite3VdbeAddOp3(
  1566   1569           v, OP_Copy, reg+pMWin->nBufferCol, pMWin->regPart, nPart+nPeer-1
  1567   1570       );
  1568   1571   
  1569   1572       if( addrJump ) sqlite3VdbeJumpHere(v, addrJump);
  1570   1573     }
................................................................................
  1582   1585     sqlite3VdbeAddOp2(v, OP_NewRowid, pMWin->iEphCsr, regRowid);
  1583   1586     sqlite3VdbeAddOp3(v, OP_Insert, pMWin->iEphCsr, regRecord, regRowid);
  1584   1587   
  1585   1588     /* End the database scan loop. */
  1586   1589     sqlite3WhereEnd(pWInfo);
  1587   1590   
  1588   1591     windowAggFinal(pParse, pMWin, 1);
         1592  +  sqlite3VdbeAddOp2(v, OP_Rewind, pMWin->iEphCsr,sqlite3VdbeCurrentAddr(v)+3);
  1589   1593     sqlite3VdbeAddOp2(v, OP_Gosub, regGosub, addrGosub);
         1594  +  sqlite3VdbeAddOp2(v, OP_Next, pMWin->iEphCsr, sqlite3VdbeCurrentAddr(v)-1);
  1590   1595   }
  1591   1596   
         1597  +Window *sqlite3WindowDup(sqlite3 *db, Window *p){
         1598  +  Window *pNew = 0;
         1599  +  if( p ){
         1600  +    pNew = sqlite3DbMallocZero(db, sizeof(Window));
         1601  +    if( pNew ){
         1602  +      pNew->pFilter = sqlite3ExprDup(db, p->pFilter, 0);
         1603  +      pNew->pPartition = sqlite3ExprListDup(db, p->pPartition, 0);
         1604  +      pNew->pOrderBy = sqlite3ExprListDup(db, p->pOrderBy, 0);
         1605  +      pNew->eType = p->eType;
         1606  +      pNew->eEnd = p->eEnd;
         1607  +      pNew->eStart = p->eStart;
         1608  +      pNew->pStart = sqlite3ExprDup(db, pNew->pStart, 0);
         1609  +      pNew->pEnd = sqlite3ExprDup(db, pNew->pEnd, 0);
         1610  +    }
         1611  +  }
         1612  +  return pNew;
         1613  +}
  1592   1614   
  1593   1615   /*
  1594   1616   ** RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  1595   1617   **
  1596   1618   **   As above, except take no action for a "new peer". Invoke
  1597   1619   **   the sub-routine once only for each partition.
  1598   1620   **
................................................................................
  1645   1667   **
  1646   1668   */
  1647   1669   void sqlite3WindowCodeStep(
  1648   1670     Parse *pParse, 
  1649   1671     Select *p,
  1650   1672     WhereInfo *pWInfo,
  1651   1673     int regGosub, 
  1652         -  int addrGosub,
  1653         -  int *pbLoop
         1674  +  int addrGosub
  1654   1675   ){
  1655   1676     Window *pMWin = p->pWin;
  1656   1677     Window *pWin;
  1657   1678   
  1658         -  *pbLoop = 0;
  1659   1679     if( (pMWin->eType==TK_ROWS 
  1660   1680      && (pMWin->eStart!=TK_UNBOUNDED||pMWin->eEnd!=TK_CURRENT||!pMWin->pOrderBy))
  1661   1681      || (pMWin->eStart==TK_CURRENT&&pMWin->eEnd==TK_UNBOUNDED&&pMWin->pOrderBy)
  1662   1682     ){
  1663   1683       windowCodeRowExprStep(pParse, p, pWInfo, regGosub, addrGosub);
  1664   1684       return;
  1665   1685     }
................................................................................
  1673   1693        || (pFunc->xSFunc==lagStepFunc)
  1674   1694       ){
  1675   1695         windowCodeCacheStep(pParse, p, pWInfo, regGosub, addrGosub);
  1676   1696         return;
  1677   1697       }
  1678   1698     }
  1679   1699   
  1680         -  *pbLoop = 1;
  1681   1700     windowCodeDefaultStep(pParse, p, pWInfo, regGosub, addrGosub);
  1682   1701   }
  1683   1702   

Changes to test/window1.test.

   190    190   do_catchsql_test 5.3 {
   191    191     SELECT ntile('zbc') OVER (ORDER BY a) FROM t2;
   192    192   } {1 {argument of ntile must be a positive integer}}
   193    193   do_execsql_test 5.4 {
   194    194     CREATE TABLE t4(a, b);
   195    195     SELECT ntile(1) OVER (ORDER BY a) FROM t4;
   196    196   } {}
          197  +
          198  +#-------------------------------------------------------------------------
          199  +reset_db
          200  +do_execsql_test 6.1 {
          201  +  CREATE TABLE t1(x);
          202  +  INSERT INTO t1 VALUES(7), (6), (5), (4), (3), (2), (1);
          203  +
          204  +  CREATE TABLE t2(x);
          205  +  INSERT INTO t2 VALUES('b'), ('a');
          206  +
          207  +  SELECT x, count(*) OVER (ORDER BY x) FROM t1;
          208  +} {1 1 2 2 3 3 4 4 5 5 6 6 7 7}
          209  +
          210  +do_execsql_test 6.2 {
          211  +  SELECT * FROM t2, (SELECT x, count(*) OVER (ORDER BY x) FROM t1);
          212  +} {
          213  +  b 1 1 b 2 2 b 3 3 b 4 4 b 5 5 b 6 6 b 7 7
          214  +  a 1 1 a 2 2 a 3 3 a 4 4 a 5 5 a 6 6 a 7 7
          215  +}
   197    216   
   198    217   
   199    218   finish_test