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 |
Timelines: | family | ancestors | descendants | both | exp-window-functions |
Files: | files | file ages | folders |
SHA3-256: |
236cb75bd1f0d5eb86aa5f52d8d548e7 |
User & Date: | dan 2018-06-08 16:11:55.013 |
Context
2018-06-08
| ||
20:58 | Add support for the WINDOW clause. (check-in: 19c983b511 user: dan tags: exp-window-functions) | |
16:11 | Do not flatten sub-queries that contain window functions. (check-in: 236cb75bd1 user: dan tags: exp-window-functions) | |
11:45 | Fixes to allow group_concat() to be used as a window function. (check-in: 89bbc9ba8f user: dan tags: exp-window-functions) | |
Changes
Changes to src/expr.c.
︙ | ︙ | |||
1178 1179 1180 1181 1182 1183 1184 | if( flags&EXPRDUP_REDUCE ){ nByte += dupedExprSize(p->pLeft, flags) + dupedExprSize(p->pRight, flags); } } return nByte; } | < < < < < < < < < < < < < < < < < < | 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 | if( flags&EXPRDUP_REDUCE ){ nByte += dupedExprSize(p->pLeft, flags) + dupedExprSize(p->pRight, flags); } } return nByte; } /* ** This function is similar to sqlite3ExprDup(), except that if pzBuffer ** is not NULL then *pzBuffer is assumed to point to a buffer large enough ** to store the copy of expression p, the copies of p->u.zToken ** (if applicable), and the copies of the p->pLeft and p->pRight expressions, ** if any. Before returning, *pzBuffer is set to the first byte past the ** portion of the buffer copied into by this function. |
︙ | ︙ | |||
1285 1286 1287 1288 1289 1290 1291 | if( pzBuffer ){ *pzBuffer = zAlloc; } }else{ if( ExprHasProperty(p, EP_Reduced|EP_TokenOnly) ){ pNew->pWin = 0; }else{ | | | 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 | if( pzBuffer ){ *pzBuffer = zAlloc; } }else{ if( ExprHasProperty(p, EP_Reduced|EP_TokenOnly) ){ pNew->pWin = 0; }else{ pNew->pWin = sqlite3WindowDup(db, p->pWin); } if( !ExprHasProperty(p, EP_TokenOnly|EP_Leaf) ){ if( pNew->op==TK_SELECT_COLUMN ){ pNew->pLeft = p->pLeft; assert( p->iColumn==0 || p->pRight==0 ); assert( p->pRight==0 || p->pRight==p->pLeft ); }else{ |
︙ | ︙ |
Changes to src/select.c.
︙ | ︙ | |||
3708 3709 3710 3711 3712 3713 3714 | pSrc = p->pSrc; assert( pSrc && iFrom>=0 && iFrom<pSrc->nSrc ); pSubitem = &pSrc->a[iFrom]; iParent = pSubitem->iCursor; pSub = pSubitem->pSelect; assert( pSub!=0 ); | | | 3708 3709 3710 3711 3712 3713 3714 3715 3716 3717 3718 3719 3720 3721 3722 | pSrc = p->pSrc; assert( pSrc && iFrom>=0 && iFrom<pSrc->nSrc ); pSubitem = &pSrc->a[iFrom]; iParent = pSubitem->iCursor; pSub = pSubitem->pSelect; assert( pSub!=0 ); if( p->pWin || pSub->pWin ) return 0; pSubSrc = pSub->pSrc; assert( pSubSrc ); /* Prior to version 3.1.2, when LIMIT and OFFSET had to be simple constants, ** not arbitrary expressions, we allowed some combining of LIMIT and OFFSET ** because they could be computed at compile-time. But when LIMIT and OFFSET ** became arbitrary expressions, we were forced to add restrictions (13) |
︙ | ︙ | |||
5894 5895 5896 5897 5898 5899 5900 5901 5902 | if( sSort.addrSortIndex>=0 && sSort.pOrderBy==0 ){ sqlite3VdbeChangeToNoop(v, sSort.addrSortIndex); } assert( p->pEList==pEList ); if( pWin ){ int addrGosub = sqlite3VdbeMakeLabel(v); int regGosub = ++pParse->nMem; int addr = 0; | > < | | < < < < < | < < | < | | 5894 5895 5896 5897 5898 5899 5900 5901 5902 5903 5904 5905 5906 5907 5908 5909 5910 5911 5912 5913 5914 5915 5916 5917 5918 5919 | if( sSort.addrSortIndex>=0 && sSort.pOrderBy==0 ){ sqlite3VdbeChangeToNoop(v, sSort.addrSortIndex); } assert( p->pEList==pEList ); if( pWin ){ int addrGosub = sqlite3VdbeMakeLabel(v); int iCont = sqlite3VdbeMakeLabel(v); int regGosub = ++pParse->nMem; int addr = 0; sqlite3WindowCodeStep(pParse, p, pWInfo, regGosub, addrGosub); addr = sqlite3VdbeAddOp0(v, OP_Goto); sqlite3VdbeResolveLabel(v, addrGosub); selectInnerLoop(pParse, p, -1, &sSort, &sDistinct, pDest, iCont, 0); sqlite3VdbeResolveLabel(v, iCont); sqlite3VdbeAddOp1(v, OP_Return, regGosub); sqlite3VdbeJumpHere(v, addr); }else{ /* Use the standard inner loop. */ selectInnerLoop(pParse, p, -1, &sSort, &sDistinct, pDest, sqlite3WhereContinueLabel(pWInfo), sqlite3WhereBreakLabel(pWInfo)); |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
3498 3499 3500 3501 3502 3503 3504 | }; void sqlite3WindowDelete(sqlite3*, Window*); Window *sqlite3WindowAlloc(Parse*, int, int, Expr*, int , Expr*); void sqlite3WindowAttach(Parse*, Expr*, Window*); int sqlite3WindowCompare(Parse*, Window*, Window*); void sqlite3WindowCodeInit(Parse*, Window*); | | > | 3498 3499 3500 3501 3502 3503 3504 3505 3506 3507 3508 3509 3510 3511 3512 3513 3514 3515 3516 | }; void sqlite3WindowDelete(sqlite3*, Window*); Window *sqlite3WindowAlloc(Parse*, int, int, Expr*, int , Expr*); void sqlite3WindowAttach(Parse*, Expr*, Window*); int sqlite3WindowCompare(Parse*, Window*, Window*); void sqlite3WindowCodeInit(Parse*, Window*); void sqlite3WindowCodeStep(Parse*, Select*, WhereInfo*, int, int); int sqlite3WindowRewrite(Parse*, Select*); int sqlite3ExpandSubquery(Parse*, struct SrcList_item*); void sqlite3WindowUpdate(Parse*, Window*, FuncDef*); Window *sqlite3WindowDup(sqlite3 *db, Window *p); /* ** Assuming zIn points to the first byte of a UTF-8 character, ** advance zIn to point to the first byte of the next UTF-8 character. */ #define SQLITE_SKIP_UTF8(zIn) { \ if( (*(zIn++))>=0xc0 ){ \ |
︙ | ︙ |
Changes to src/window.c.
︙ | ︙ | |||
1556 1557 1558 1559 1560 1561 1562 1563 1564 1565 1566 1567 1568 1569 1570 | }else{ addrJump = 0; } windowAggFinal(pParse, pMWin, pMWin->eStart==TK_CURRENT); if( addrGoto ) sqlite3VdbeJumpHere(v, addrGoto); } sqlite3VdbeAddOp2(v, OP_Gosub, regGosub, addrGosub); sqlite3VdbeAddOp1(v, OP_ResetSorter, pMWin->iEphCsr); sqlite3VdbeAddOp3( v, OP_Copy, reg+pMWin->nBufferCol, pMWin->regPart, nPart+nPeer-1 ); if( addrJump ) sqlite3VdbeJumpHere(v, addrJump); } | > > > | 1556 1557 1558 1559 1560 1561 1562 1563 1564 1565 1566 1567 1568 1569 1570 1571 1572 1573 | }else{ addrJump = 0; } windowAggFinal(pParse, pMWin, pMWin->eStart==TK_CURRENT); if( addrGoto ) sqlite3VdbeJumpHere(v, addrGoto); } sqlite3VdbeAddOp2(v, OP_Rewind, pMWin->iEphCsr,sqlite3VdbeCurrentAddr(v)+3); sqlite3VdbeAddOp2(v, OP_Gosub, regGosub, addrGosub); sqlite3VdbeAddOp2(v, OP_Next, pMWin->iEphCsr, sqlite3VdbeCurrentAddr(v)-1); sqlite3VdbeAddOp1(v, OP_ResetSorter, pMWin->iEphCsr); sqlite3VdbeAddOp3( v, OP_Copy, reg+pMWin->nBufferCol, pMWin->regPart, nPart+nPeer-1 ); if( addrJump ) sqlite3VdbeJumpHere(v, addrJump); } |
︙ | ︙ | |||
1582 1583 1584 1585 1586 1587 1588 1589 1590 1591 1592 1593 1594 1595 1596 1597 1598 | sqlite3VdbeAddOp2(v, OP_NewRowid, pMWin->iEphCsr, regRowid); sqlite3VdbeAddOp3(v, OP_Insert, pMWin->iEphCsr, regRecord, regRowid); /* End the database scan loop. */ sqlite3WhereEnd(pWInfo); windowAggFinal(pParse, pMWin, 1); sqlite3VdbeAddOp2(v, OP_Gosub, regGosub, addrGosub); } /* ** RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ** ** As above, except take no action for a "new peer". Invoke ** the sub-routine once only for each partition. ** | > > > > > > > > > > > > > > > > > > > | 1585 1586 1587 1588 1589 1590 1591 1592 1593 1594 1595 1596 1597 1598 1599 1600 1601 1602 1603 1604 1605 1606 1607 1608 1609 1610 1611 1612 1613 1614 1615 1616 1617 1618 1619 1620 | sqlite3VdbeAddOp2(v, OP_NewRowid, pMWin->iEphCsr, regRowid); sqlite3VdbeAddOp3(v, OP_Insert, pMWin->iEphCsr, regRecord, regRowid); /* End the database scan loop. */ sqlite3WhereEnd(pWInfo); windowAggFinal(pParse, pMWin, 1); sqlite3VdbeAddOp2(v, OP_Rewind, pMWin->iEphCsr,sqlite3VdbeCurrentAddr(v)+3); sqlite3VdbeAddOp2(v, OP_Gosub, regGosub, addrGosub); sqlite3VdbeAddOp2(v, OP_Next, pMWin->iEphCsr, sqlite3VdbeCurrentAddr(v)-1); } Window *sqlite3WindowDup(sqlite3 *db, Window *p){ Window *pNew = 0; if( p ){ pNew = sqlite3DbMallocZero(db, sizeof(Window)); if( pNew ){ pNew->pFilter = sqlite3ExprDup(db, p->pFilter, 0); pNew->pPartition = sqlite3ExprListDup(db, p->pPartition, 0); pNew->pOrderBy = sqlite3ExprListDup(db, p->pOrderBy, 0); pNew->eType = p->eType; pNew->eEnd = p->eEnd; pNew->eStart = p->eStart; pNew->pStart = sqlite3ExprDup(db, pNew->pStart, 0); pNew->pEnd = sqlite3ExprDup(db, pNew->pEnd, 0); } } return pNew; } /* ** RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ** ** As above, except take no action for a "new peer". Invoke ** the sub-routine once only for each partition. ** |
︙ | ︙ | |||
1645 1646 1647 1648 1649 1650 1651 | ** */ void sqlite3WindowCodeStep( Parse *pParse, Select *p, WhereInfo *pWInfo, int regGosub, | | < < | 1667 1668 1669 1670 1671 1672 1673 1674 1675 1676 1677 1678 1679 1680 1681 1682 1683 1684 1685 | ** */ void sqlite3WindowCodeStep( Parse *pParse, Select *p, WhereInfo *pWInfo, int regGosub, int addrGosub ){ Window *pMWin = p->pWin; Window *pWin; if( (pMWin->eType==TK_ROWS && (pMWin->eStart!=TK_UNBOUNDED||pMWin->eEnd!=TK_CURRENT||!pMWin->pOrderBy)) || (pMWin->eStart==TK_CURRENT&&pMWin->eEnd==TK_UNBOUNDED&&pMWin->pOrderBy) ){ windowCodeRowExprStep(pParse, p, pWInfo, regGosub, addrGosub); return; } |
︙ | ︙ | |||
1673 1674 1675 1676 1677 1678 1679 | || (pFunc->xSFunc==lagStepFunc) ){ windowCodeCacheStep(pParse, p, pWInfo, regGosub, addrGosub); return; } } | < | 1693 1694 1695 1696 1697 1698 1699 1700 1701 1702 | || (pFunc->xSFunc==lagStepFunc) ){ windowCodeCacheStep(pParse, p, pWInfo, regGosub, addrGosub); return; } } windowCodeDefaultStep(pParse, p, pWInfo, regGosub, addrGosub); } |
Changes to test/window1.test.
︙ | ︙ | |||
190 191 192 193 194 195 196 197 198 199 | do_catchsql_test 5.3 { SELECT ntile('zbc') OVER (ORDER BY a) FROM t2; } {1 {argument of ntile must be a positive integer}} do_execsql_test 5.4 { CREATE TABLE t4(a, b); SELECT ntile(1) OVER (ORDER BY a) FROM t4; } {} finish_test | > > > > > > > > > > > > > > > > > > > | 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 | do_catchsql_test 5.3 { SELECT ntile('zbc') OVER (ORDER BY a) FROM t2; } {1 {argument of ntile must be a positive integer}} do_execsql_test 5.4 { CREATE TABLE t4(a, b); SELECT ntile(1) OVER (ORDER BY a) FROM t4; } {} #------------------------------------------------------------------------- reset_db do_execsql_test 6.1 { CREATE TABLE t1(x); INSERT INTO t1 VALUES(7), (6), (5), (4), (3), (2), (1); CREATE TABLE t2(x); INSERT INTO t2 VALUES('b'), ('a'); SELECT x, count(*) OVER (ORDER BY x) FROM t1; } {1 1 2 2 3 3 4 4 5 5 6 6 7 7} do_execsql_test 6.2 { SELECT * FROM t2, (SELECT x, count(*) OVER (ORDER BY x) FROM t1); } { b 1 1 b 2 2 b 3 3 b 4 4 b 5 5 b 6 6 b 7 7 a 1 1 a 2 2 a 3 3 a 4 4 a 5 5 a 6 6 a 7 7 } finish_test |