Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Fix problems with using window functions in CREATE VIEW statements. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | exp-window-functions |
Files: | files | file ages | folders |
SHA3-256: |
943bccd2a6bd4cf3e0534c1fa46885bf |
User & Date: | dan 2018-06-18 20:34:43.117 |
Context
2018-06-19
| ||
17:13 | Fix a problem with using min() or max() as a window function. (check-in: 801074ce63 user: dan tags: exp-window-functions) | |
2018-06-18
| ||
20:34 | Fix problems with using window functions in CREATE VIEW statements. (check-in: 943bccd2a6 user: dan tags: exp-window-functions) | |
17:36 | Ensure that all four callbacks are provided when registering a window function (otherwise SQLITE_MISUSE is returned). (check-in: 5720dcd8b1 user: dan tags: exp-window-functions) | |
Changes
Changes to src/expr.c.
︙ | ︙ | |||
1112 1113 1114 1115 1116 1117 1118 | ** The size of the structure can be found by masking the return value ** of this routine with 0xfff. The flags can be found by masking the ** return value with EP_Reduced|EP_TokenOnly. ** ** Note that with flags==EXPRDUP_REDUCE, this routines works on full-size ** (unreduced) Expr objects as they or originally constructed by the parser. ** During expression analysis, extra information is computed and moved into | | | | 1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 | ** The size of the structure can be found by masking the return value ** of this routine with 0xfff. The flags can be found by masking the ** return value with EP_Reduced|EP_TokenOnly. ** ** Note that with flags==EXPRDUP_REDUCE, this routines works on full-size ** (unreduced) Expr objects as they or originally constructed by the parser. ** During expression analysis, extra information is computed and moved into ** later parts of the Expr object and that extra information might get chopped ** off if the expression is reduced. Note also that it does not work to ** make an EXPRDUP_REDUCE copy of a reduced expression. It is only legal ** to reduce a pristine expression tree from the parser. The implementation ** of dupedExprStructSize() contain multiple assert() statements that attempt ** to enforce this constraint. */ static int dupedExprStructSize(Expr *p, int flags){ int nSize; assert( flags==EXPRDUP_REDUCE || flags==0 ); /* Only one flag value allowed */ assert( EXPR_FULLSIZE<=0xfff ); assert( (0xfff & (EP_Reduced|EP_TokenOnly))==0 ); if( 0==flags || p->op==TK_SELECT_COLUMN || p->pWin ){ nSize = EXPR_FULLSIZE; }else{ assert( !ExprHasProperty(p, EP_TokenOnly|EP_Reduced) ); assert( !ExprHasProperty(p, EP_FromJoin) ); assert( !ExprHasProperty(p, EP_MemToken) ); assert( !ExprHasProperty(p, EP_NoReduce) ); if( p->pLeft || p->x.pList ){ |
︙ | ︙ | |||
1476 1477 1478 1479 1480 1481 1482 | pNew->iOffset = 0; pNew->selFlags = p->selFlags & ~SF_UsesEphemeral; pNew->addrOpenEphm[0] = -1; pNew->addrOpenEphm[1] = -1; pNew->nSelectRow = p->nSelectRow; pNew->pWith = withDup(db, p->pWith); pNew->pWin = 0; | | | 1476 1477 1478 1479 1480 1481 1482 1483 1484 1485 1486 1487 1488 1489 1490 | pNew->iOffset = 0; pNew->selFlags = p->selFlags & ~SF_UsesEphemeral; pNew->addrOpenEphm[0] = -1; pNew->addrOpenEphm[1] = -1; pNew->nSelectRow = p->nSelectRow; pNew->pWith = withDup(db, p->pWith); pNew->pWin = 0; pNew->pWinDefn = sqlite3WindowListDup(db, p->pWinDefn); sqlite3SelectSetName(pNew, p->zSelName); *pp = pNew; pp = &pNew->pPrior; pNext = pNew; } return pRet; |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
3524 3525 3526 3527 3528 3529 3530 3531 3532 3533 3534 3535 3536 3537 | 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*, Window*, FuncDef*); Window *sqlite3WindowDup(sqlite3 *db, Expr *pOwner, Window *p); void sqlite3WindowFunctions(void); /* ** 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) { \ | > | 3524 3525 3526 3527 3528 3529 3530 3531 3532 3533 3534 3535 3536 3537 3538 | 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*, Window*, FuncDef*); Window *sqlite3WindowDup(sqlite3 *db, Expr *pOwner, Window *p); Window *sqlite3WindowListDup(sqlite3 *db, Window *p); void sqlite3WindowFunctions(void); /* ** 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) { \ |
︙ | ︙ |
Changes to src/window.c.
︙ | ︙ | |||
499 500 501 502 503 504 505 | */ void sqlite3WindowUpdate( Parse *pParse, Window *pList, /* List of named windows for this SELECT */ Window *pWin, /* Window frame to update */ FuncDef *pFunc /* Window function definition */ ){ | | > | 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 | */ void sqlite3WindowUpdate( Parse *pParse, Window *pList, /* List of named windows for this SELECT */ Window *pWin, /* Window frame to update */ FuncDef *pFunc /* Window function definition */ ){ if( pWin->zName && pWin->eType==0 ){ Window *p; for(p=pList; p; p=p->pNextWin){ if( sqlite3StrICmp(p->zName, pWin->zName)==0 ) break; } if( p==0 ){ sqlite3ErrorMsg(pParse, "no such window: %s", pWin->zName); return; } pWin->pPartition = sqlite3ExprListDup(pParse->db, p->pPartition, 0); pWin->pOrderBy = sqlite3ExprListDup(pParse->db, p->pOrderBy, 0); pWin->pStart = sqlite3ExprDup(pParse->db, p->pStart, 0); pWin->pEnd = sqlite3ExprDup(pParse->db, p->pEnd, 0); pWin->eStart = p->eStart; pWin->eEnd = p->eEnd; pWin->eType = p->eType; } if( pFunc->funcFlags & SQLITE_FUNC_WINDOW ){ sqlite3 *db = pParse->db; if( pWin->pFilter ){ sqlite3ErrorMsg(pParse, "FILTER clause may only be used with aggregate window functions" ); |
︙ | ︙ | |||
796 797 798 799 800 801 802 803 804 805 806 807 808 809 | int eType, int eStart, Expr *pStart, int eEnd, Expr *pEnd ){ Window *pWin = (Window*)sqlite3DbMallocZero(pParse->db, sizeof(Window)); if( pWin ){ pWin->eType = eType; pWin->eStart = eStart; pWin->eEnd = eEnd; pWin->pEnd = pEnd; pWin->pStart = pStart; }else{ sqlite3ExprDelete(pParse->db, pEnd); | > | 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 | int eType, int eStart, Expr *pStart, int eEnd, Expr *pEnd ){ Window *pWin = (Window*)sqlite3DbMallocZero(pParse->db, sizeof(Window)); if( pWin ){ assert( eType ); pWin->eType = eType; pWin->eStart = eStart; pWin->eEnd = eEnd; pWin->pEnd = pEnd; pWin->pStart = pStart; }else{ sqlite3ExprDelete(pParse->db, pEnd); |
︙ | ︙ | |||
1914 1915 1916 1917 1918 1919 1920 1921 1922 1923 1924 1925 1926 1927 1928 1929 1930 1931 1932 1933 1934 1935 1936 1937 1938 1939 1940 | ** pOwner. */ Window *sqlite3WindowDup(sqlite3 *db, Expr *pOwner, 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, p->pStart, 0); pNew->pEnd = sqlite3ExprDup(db, p->pEnd, 0); pNew->pOwner = pOwner; } } return pNew; } /* ** sqlite3WhereBegin() has already been called for the SELECT statement ** passed as the second argument when this function is invoked. It generates ** code to populate the Window.regResult register for each window function and ** invoke the sub-routine at instruction addrGosub once for each row. ** This function calls sqlite3WhereEnd() before returning. | > > > > > > > > > > > > > > > > > > > | 1916 1917 1918 1919 1920 1921 1922 1923 1924 1925 1926 1927 1928 1929 1930 1931 1932 1933 1934 1935 1936 1937 1938 1939 1940 1941 1942 1943 1944 1945 1946 1947 1948 1949 1950 1951 1952 1953 1954 1955 1956 1957 1958 1959 1960 1961 | ** pOwner. */ Window *sqlite3WindowDup(sqlite3 *db, Expr *pOwner, Window *p){ Window *pNew = 0; if( p ){ pNew = sqlite3DbMallocZero(db, sizeof(Window)); if( pNew ){ pNew->zName = sqlite3DbStrDup(db, p->zName); 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, p->pStart, 0); pNew->pEnd = sqlite3ExprDup(db, p->pEnd, 0); pNew->pOwner = pOwner; } } return pNew; } /* ** Return a copy of the linked list of Window objects passed as the ** second argument. */ Window *sqlite3WindowListDup(sqlite3 *db, Window *p){ Window *pWin; Window *pRet = 0; Window **pp = &pRet; for(pWin=p; pWin; pWin=pWin->pNextWin){ *pp = sqlite3WindowDup(db, 0, pWin); if( *pp==0 ) break; pp = &((*pp)->pNextWin); } return pRet; } /* ** sqlite3WhereBegin() has already been called for the SELECT statement ** passed as the second argument when this function is invoked. It generates ** code to populate the Window.regResult register for each window function and ** invoke the sub-routine at instruction addrGosub once for each row. ** This function calls sqlite3WhereEnd() before returning. |
︙ | ︙ |
Changes to test/window1.test.
︙ | ︙ | |||
265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 | 4 6 8 6 8 10 8 10 default 10 {} default {} {} default } do_execsql_test 7.3 { SELECT row_number() OVER (ORDER BY x) FROM t1 } {1 2 3 4 5} do_execsql_test 7.4 { SELECT row_number() OVER win, lead(x) OVER win FROM t1 WINDOW win AS (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) } {1 3 2 5 3 7 4 9 5 {}} finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 | 4 6 8 6 8 10 8 10 default 10 {} default {} {} default } do_execsql_test 7.3 { SELECT row_number() OVER (ORDER BY x) FROM t1 } {1 2 3 4 5} breakpoint do_execsql_test 7.4 { SELECT row_number() OVER win, lead(x) OVER win FROM t1 WINDOW win AS (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) } {1 3 2 5 3 7 4 9 5 {}} #------------------------------------------------------------------------- # Attempt to use a window function in a view. # do_execsql_test 8.0 { CREATE TABLE t3(a, b, c); WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<6 ) INSERT INTO t3 SELECT i, i, i FROM s; CREATE VIEW v1 AS SELECT sum(b) OVER (ORDER BY c), min(b) OVER (ORDER BY c), max(b) OVER (ORDER BY c) FROM t3; CREATE VIEW v2 AS SELECT sum(b) OVER win, min(b) OVER win, max(b) OVER win FROM t3 WINDOW win AS (ORDER BY c); } do_execsql_test 8.1.1 { SELECT * FROM v1 } {1 1 1 3 1 2 6 1 3 10 1 4 15 1 5 21 1 6} do_execsql_test 8.1.2 { SELECT * FROM v2 } {1 1 1 3 1 2 6 1 3 10 1 4 15 1 5 21 1 6} db close sqlite3 db test.db do_execsql_test 8.2.1 { SELECT * FROM v1 } {1 1 1 3 1 2 6 1 3 10 1 4 15 1 5 21 1 6} do_execsql_test 8.2.2 { SELECT * FROM v2 } {1 1 1 3 1 2 6 1 3 10 1 4 15 1 5 21 1 6} finish_test |