Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Have the tokenizer handle fallback for tokens "OVER" and "FILTER" in the same way as it does for "WINDOW". |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | weak-fallback |
Files: | files | file ages | folders |
SHA3-256: |
12d819e1c17d8036900352b0989c4bfc |
User & Date: | dan 2018-06-30 18:54:56.070 |
Context
2018-06-30
| ||
19:12 | Merge all changes from the weak-fallback branch except those related to the weak-fallback feature itself. (check-in: aad718fb1a user: dan tags: exp-window-functions) | |
18:54 | Have the tokenizer handle fallback for tokens "OVER" and "FILTER" in the same way as it does for "WINDOW". (Leaf check-in: 12d819e1c1 user: dan tags: weak-fallback) | |
2018-06-29
| ||
20:43 | Further performance related tweaks for sqlite3RunParser(). (check-in: 5eb4776598 user: dan tags: weak-fallback) | |
Changes
Changes to src/parse.y.
︙ | ︙ | |||
213 214 215 216 217 218 219 | IGNORE IMMEDIATE INITIALLY INSTEAD LIKE_KW MATCH NO PLAN QUERY KEY OF OFFSET PRAGMA RAISE RECURSIVE RELEASE REPLACE RESTRICT ROW ROWS ROLLBACK SAVEPOINT TEMP TRIGGER VACUUM VIEW VIRTUAL WITH WITHOUT %ifdef SQLITE_OMIT_COMPOUND_SELECT EXCEPT INTERSECT UNION %endif SQLITE_OMIT_COMPOUND_SELECT %ifndef SQLITE_OMIT_WINDOWFUNC | < | | 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 | IGNORE IMMEDIATE INITIALLY INSTEAD LIKE_KW MATCH NO PLAN QUERY KEY OF OFFSET PRAGMA RAISE RECURSIVE RELEASE REPLACE RESTRICT ROW ROWS ROLLBACK SAVEPOINT TEMP TRIGGER VACUUM VIEW VIRTUAL WITH WITHOUT %ifdef SQLITE_OMIT_COMPOUND_SELECT EXCEPT INTERSECT UNION %endif SQLITE_OMIT_COMPOUND_SELECT %ifndef SQLITE_OMIT_WINDOWFUNC CURRENT FOLLOWING PARTITION PRECEDING RANGE UNBOUNDED %endif SQLITE_OMIT_WINDOWFUNC REINDEX RENAME CTIME_KW IF . %wildcard ANY. // Define operator precedence early so that this is the first occurrence // of the operator tokens in the grammer. Keeping the operators together |
︙ | ︙ | |||
1595 1596 1597 1598 1599 1600 1601 | } wqlist(A) ::= wqlist(A) COMMA nm(X) eidlist_opt(Y) AS LP select(Z) RP. { A = sqlite3WithAdd(pParse, A, &X, Y, Z); } %endif SQLITE_OMIT_CTE //////////////////////// WINDOW FUNCTION EXPRESSIONS ///////////////////////// | | | | | < < < | 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 1621 1622 1623 1624 1625 1626 1627 1628 1629 1630 | } wqlist(A) ::= wqlist(A) COMMA nm(X) eidlist_opt(Y) AS LP select(Z) RP. { A = sqlite3WithAdd(pParse, A, &X, Y, Z); } %endif SQLITE_OMIT_CTE //////////////////////// WINDOW FUNCTION EXPRESSIONS ///////////////////////// // These must be at the end of this file. Specifically, the rules that // introduce tokens WINDOW, OVER and FILTER must appear last. This causes // the integer values assigned to these tokens to be larger than all other // tokens that may be output by the tokenizer except TK_SPACE and TK_ILLEGAL. // %ifndef SQLITE_OMIT_WINDOWFUNC %type windowdefn_list {Window*} %destructor windowdefn_list {sqlite3WindowDelete(pParse->db, $$);} windowdefn_list(A) ::= windowdefn(Z). { A = Z; } windowdefn_list(A) ::= windowdefn_list(Y) COMMA windowdefn(Z). { if( Z ) Z->pNextWin = Y; A = Z; } %type windowdefn {Window*} %destructor windowdefn {sqlite3WindowDelete(pParse->db, $$);} windowdefn(A) ::= nm(X) AS window(Y). { if( Y ){ Y->zName = sqlite3DbStrNDup(pParse->db, X.z, X.n); } A = Y; } %type window {Window*} %destructor window {sqlite3WindowDelete(pParse->db, $$);} %type frame_opt {Window*} %destructor frame_opt {sqlite3WindowDelete(pParse->db, $$);} %type window_or_nm {Window*} |
︙ | ︙ | |||
1642 1643 1644 1645 1646 1647 1648 | %destructor filter_opt {sqlite3ExprDelete(pParse->db, $$);} %type range_or_rows {int} %type frame_bound {struct FrameBound} %destructor frame_bound {sqlite3ExprDelete(pParse->db, $$.pExpr);} | < < < < < < < < | 1638 1639 1640 1641 1642 1643 1644 1645 1646 1647 1648 1649 1650 1651 1652 1653 1654 1655 1656 1657 1658 1659 1660 1661 1662 1663 1664 1665 1666 1667 1668 1669 | %destructor filter_opt {sqlite3ExprDelete(pParse->db, $$);} %type range_or_rows {int} %type frame_bound {struct FrameBound} %destructor frame_bound {sqlite3ExprDelete(pParse->db, $$.pExpr);} window_or_nm(A) ::= window(Z). {A = Z;} window_or_nm(A) ::= nm(Z). { A = (Window*)sqlite3DbMallocZero(pParse->db, sizeof(Window)); if( A ){ A->zName = sqlite3DbStrNDup(pParse->db, Z.z, Z.n); } } window(A) ::= LP part_opt(X) orderby_opt(Y) frame_opt(Z) RP. { A = Z; if( A ){ A->pPartition = X; A->pOrderBy = Y; } } part_opt(A) ::= PARTITION BY exprlist(X). { A = X; } part_opt(A) ::= . { A = 0; } frame_opt(A) ::= . { A = sqlite3WindowAlloc(pParse, TK_RANGE, TK_UNBOUNDED, 0, TK_CURRENT, 0); } frame_opt(A) ::= range_or_rows(X) frame_bound(Y). { A = sqlite3WindowAlloc(pParse, X, Y.eType, Y.pExpr, TK_CURRENT, 0); } |
︙ | ︙ | |||
1692 1693 1694 1695 1696 1697 1698 1699 1700 | frame_bound(A) ::= expr(X) FOLLOWING. { A.eType = TK_FOLLOWING; A.pExpr = X; } frame_bound(A) ::= UNBOUNDED FOLLOWING. { A.eType = TK_UNBOUNDED; A.pExpr = 0; } %type windowdefn_opt {Window*} %destructor windowdefn_opt {sqlite3WindowDelete(pParse->db, $$);} windowdefn_opt(A) ::= . { A = 0; } windowdefn_opt(A) ::= WINDOW windowdefn_list(B). { A = B; } %endif // SQLITE_OMIT_WINDOWFUNC | > > > > > > > > > > > | 1680 1681 1682 1683 1684 1685 1686 1687 1688 1689 1690 1691 1692 1693 1694 1695 1696 1697 1698 1699 | frame_bound(A) ::= expr(X) FOLLOWING. { A.eType = TK_FOLLOWING; A.pExpr = X; } frame_bound(A) ::= UNBOUNDED FOLLOWING. { A.eType = TK_UNBOUNDED; A.pExpr = 0; } %type windowdefn_opt {Window*} %destructor windowdefn_opt {sqlite3WindowDelete(pParse->db, $$);} windowdefn_opt(A) ::= . { A = 0; } windowdefn_opt(A) ::= WINDOW windowdefn_list(B). { A = B; } %type over_opt {Window*} %destructor over_opt {sqlite3WindowDelete(pParse->db, $$);} over_opt(A) ::= . { A = 0; } over_opt(A) ::= filter_opt(W) OVER window_or_nm(Z). { A = Z; if( A ) A->pFilter = W; } filter_opt(A) ::= . { A = 0; } filter_opt(A) ::= FILTER LP WHERE expr(X) RP. { A = X; } %endif // SQLITE_OMIT_WINDOWFUNC |
Changes to src/tokenize.c.
︙ | ︙ | |||
188 189 190 191 192 193 194 | int sqlite3IsIdChar(u8 c){ return IdChar(c); } #endif #ifndef SQLITE_OMIT_WINDOWFUNC /* ** Return the id of the next token in string (*pz). Before returning, set ** (*pz) to point to the byte following the parsed token. | < < < | < < > > > | > > > > > > > | < < | < < > | | > > > > > > > > | > > > | > > | > | | < < | > > > < | | < < < < | | > > > > > > > | | > > > | | 188 189 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 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 | int sqlite3IsIdChar(u8 c){ return IdChar(c); } #endif #ifndef SQLITE_OMIT_WINDOWFUNC /* ** Return the id of the next token in string (*pz). Before returning, set ** (*pz) to point to the byte following the parsed token. */ static int getToken(const unsigned char **pz){ const unsigned char *z = *pz; int t; /* Token type to return */ do { z += sqlite3GetToken(z, &t); }while( t==TK_SPACE ); if( t==TK_ID || t==TK_STRING || t==TK_JOIN_KW || t==TK_WINDOW || t==TK_OVER || sqlite3ParserFallback(t)==TK_ID ){ t = TK_ID; } *pz = z; return t; } /* ** The following three functions are called immediately after the tokenizer ** reads the keywords WINDOW, OVER and FILTER, respectively, to determine ** whether the token should be treated as a keyword or an SQL identifier. ** This cannot be handled by the usual lemon %fallback method, due to ** the ambiguity in some constructions. e.g. ** ** SELECT sum(x) OVER ... ** ** In the above, "OVER" might be a keyword, or it might be an alias for the ** sum(x) expression. If a "%fallback ID OVER" directive were added to ** grammar, then SQLite would always treat "OVER" as an alias, making it ** impossible to call a window-function without a FILTER clause. ** ** WINDOW is treated as a keyword if: ** ** * the following token is an identifier, or a keyword that can fallback ** to being an identifier, and ** * the token after than one is TK_AS. ** ** OVER is a keyword if: ** ** * the previous token was TK_RP, and ** * the next token is either TK_LP or an identifier. ** ** FILTER is a keyword if: ** ** * the previous token was TK_RP, and ** * the next token is TK_LP. */ static int analyzeWindowKeyword(const unsigned char *z){ int t; t = getToken(&z); if( t!=TK_ID ) return TK_ID; t = getToken(&z); if( t!=TK_AS ) return TK_ID; return TK_WINDOW; } static int analyzeOverKeyword(const unsigned char *z, int lastToken){ if( lastToken==TK_RP ){ int t = getToken(&z); if( t==TK_LP || t==TK_ID ) return TK_OVER; } return TK_ID; } static int analyzeFilterKeyword(const unsigned char *z, int lastToken){ if( lastToken==TK_RP && getToken(&z)==TK_LP ){ return TK_FILTER; } return TK_ID; } #endif // SQLITE_OMIT_WINDOWFUNC /* ** Return the length (in bytes) of the token that begins at z[0]. ** Store the token type in *tokenType before returning. */ |
︙ | ︙ | |||
567 568 569 570 571 572 573 | mxSqlLen -= n; if( mxSqlLen<0 ){ pParse->rc = SQLITE_TOOBIG; break; } #ifndef SQLITE_OMIT_WINDOWFUNC if( tokenType>=TK_WINDOW ){ | | | 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 | mxSqlLen -= n; if( mxSqlLen<0 ){ pParse->rc = SQLITE_TOOBIG; break; } #ifndef SQLITE_OMIT_WINDOWFUNC if( tokenType>=TK_WINDOW ){ assert( tokenType==TK_SPACE || tokenType==TK_OVER || tokenType==TK_FILTER || tokenType==TK_ILLEGAL || tokenType==TK_WINDOW ); #else if( tokenType>=TK_SPACE ){ assert( tokenType==TK_SPACE || tokenType==TK_ILLEGAL ); #endif // SQLITE_OMIT_WINDOWFUNC if( db->u1.isInterrupted ){ |
︙ | ︙ | |||
595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 | break; }else{ tokenType = TK_SEMI; } n = 0; #ifndef SQLITE_OMIT_WINDOWFUNC }else if( tokenType==TK_WINDOW ){ tokenType = analyzeWindowKeyword((const u8*)&zSql[6]); #endif // SQLITE_OMIT_WINDOWFUNC }else{ sqlite3ErrorMsg(pParse, "unrecognized token: \"%.*s\"", n, zSql); break; } } pParse->sLastToken.z = zSql; | > > > > > > > | 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 | break; }else{ tokenType = TK_SEMI; } n = 0; #ifndef SQLITE_OMIT_WINDOWFUNC }else if( tokenType==TK_WINDOW ){ assert( n==6 ); tokenType = analyzeWindowKeyword((const u8*)&zSql[6]); }else if( tokenType==TK_OVER ){ assert( n==4 ); tokenType = analyzeOverKeyword((const u8*)&zSql[4], lastTokenParsed); }else if( tokenType==TK_FILTER ){ assert( n==6 ); tokenType = analyzeFilterKeyword((const u8*)&zSql[6], lastTokenParsed); #endif // SQLITE_OMIT_WINDOWFUNC }else{ sqlite3ErrorMsg(pParse, "unrecognized token: \"%.*s\"", n, zSql); break; } } pParse->sLastToken.z = zSql; |
︙ | ︙ |
Changes to test/window6.test.
︙ | ︙ | |||
18 19 20 21 22 23 24 | ifcapable !windowfunc { finish_test return } set setup { | | | | | | | | | | | | | | | | | | 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 | ifcapable !windowfunc { finish_test return } set setup { CREATE TABLE %t1(%x, %y %typename); INSERT INTO %t1 VALUES(1, 'a'); INSERT INTO %t1 VALUES(2, 'b'); INSERT INTO %t1 VALUES(3, 'c'); INSERT INTO %t1 VALUES(4, 'd'); INSERT INTO %t1 VALUES(5, 'e'); } foreach {tn vars} { 1 {} 2 { set A(%t1) over } 3 { set A(%x) over } 4 { set A(%alias) over set A(%x) following set A(%y) over } 5 { set A(%t1) over set A(%x) following set A(%y) preceding set A(%w) current set A(%alias) filter set A(%typename) window } 6 { set A(%x) window } } { set A(%t1) t1 set A(%x) x set A(%y) y set A(%w) w set A(%alias) alias set A(%typename) integer eval $vars set MAP [array get A] set setup_sql [string map $MAP $setup] reset_db execsql $setup_sql do_execsql_test 1.$tn.1 [string map $MAP { SELECT group_concat(%x, '.') OVER (ORDER BY %y) FROM %t1 }] {1 1.2 1.2.3 1.2.3.4 1.2.3.4.5} do_execsql_test 1.$tn.2 [string map $MAP { SELECT sum(%x) OVER %w FROM %t1 WINDOW %w AS (ORDER BY %y) }] {1 3 6 10 15} do_execsql_test 1.$tn.3 [string map $MAP { SELECT sum(%alias.%x) OVER %w FROM %t1 %alias WINDOW %w AS (ORDER BY %y) }] {1 3 6 10 15} do_execsql_test 1.$tn.4 [string map $MAP { SELECT sum(%x) %alias FROM %t1 }] {15} } proc winproc {args} { return "window: $args" } db func window winproc do_execsql_test 2.0 { |
︙ | ︙ | |||
108 109 110 111 112 113 114 115 116 117 | do_execsql_test 4.0 { CREATE TABLE t4(x, y); } # do_execsql_test 4.1 { PRAGMA parser_trace = 1 } do_execsql_test 4.1 { SELECT * FROM t4 window, t4; } finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > | 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 | do_execsql_test 4.0 { CREATE TABLE t4(x, y); } # do_execsql_test 4.1 { PRAGMA parser_trace = 1 } do_execsql_test 4.1 { SELECT * FROM t4 window, t4; } #------------------------------------------------------------------------- reset_db do_execsql_test 5.0 { CREATE TABLE over(x, over); CREATE TABLE window(x, window); INSERT INTO over VALUES(1, 2), (3, 4), (5, 6); INSERT INTO window VALUES(1, 2), (3, 4), (5, 6); SELECT sum(x) over FROM over } {9} do_execsql_test 5.1 { SELECT sum(x) over over FROM over WINDOW over AS () } {9 9 9} do_execsql_test 5.2 { SELECT sum(over) over over over FROM over over WINDOW over AS (ORDER BY over) } {2 6 12} do_execsql_test 5.3 { SELECT sum(over) over over over FROM over over WINDOW over AS (ORDER BY over); } {2 6 12} do_execsql_test 5.4 { SELECT sum(window) OVER window window FROM window window window window AS (ORDER BY window); } {2 6 12} finish_test |