Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Be sure to rewrite column references inside FILTER clauses and window frame definitions when flattening queries. Fix for [1079ad19]. |
---|---|
Downloads: | Tarball | ZIP archive | SQL archive |
Timelines: | family | ancestors | descendants | both | branch-3.30 |
Files: | files | file ages | folders |
SHA3-256: |
df2060f34fcb2a38c016df92079df5b6 |
User & Date: | drh 2019-10-10 15:48:16 |
Context
2019-10-10
| ||
15:57 | Fix the OP_SeekRowid opcode so that it works correctly with a Real argument without damaging the value in the register that is the argument. Ticket [b2d4edaffdc156cc]. Test cases in TH3. check-in: b02630fe user: drh tags: branch-3.30 | |
15:48 | Be sure to rewrite column references inside FILTER clauses and window frame definitions when flattening queries. Fix for [1079ad19]. check-in: df2060f3 user: drh tags: branch-3.30 | |
15:42 | Version number to 3.30.1. check-in: 12e28cc7 user: drh tags: branch-3.30 | |
15:17 | Be sure to rewrite column references inside FILTER clauses and window frame definitions when flattening queries. Fix for [1079ad19]. check-in: ccba7202 user: dan tags: trunk | |
Changes
Changes to src/select.c.
96 96 sqlite3ExprDelete(db, p->pHaving); 97 97 sqlite3ExprListDelete(db, p->pOrderBy); 98 98 sqlite3ExprDelete(db, p->pLimit); 99 99 #ifndef SQLITE_OMIT_WINDOWFUNC 100 100 if( OK_IF_ALWAYS_TRUE(p->pWinDefn) ){ 101 101 sqlite3WindowListDelete(db, p->pWinDefn); 102 102 } 103 -#endif 104 - if( OK_IF_ALWAYS_TRUE(p->pWith) ) sqlite3WithDelete(db, p->pWith); 105 103 assert( p->pWin==0 ); 104 +#endif 105 + if( OK_IF_ALWAYS_TRUE(p->pWith) ) sqlite3WithDelete(db, p->pWith); 106 106 if( bFree ) sqlite3DbFreeNN(db, p); 107 107 p = pPrior; 108 108 bFree = 1; 109 109 } 110 110 } 111 111 112 112 /* ................................................................................ 3499 3499 pExpr->pLeft = substExpr(pSubst, pExpr->pLeft); 3500 3500 pExpr->pRight = substExpr(pSubst, pExpr->pRight); 3501 3501 if( ExprHasProperty(pExpr, EP_xIsSelect) ){ 3502 3502 substSelect(pSubst, pExpr->x.pSelect, 1); 3503 3503 }else{ 3504 3504 substExprList(pSubst, pExpr->x.pList); 3505 3505 } 3506 +#ifndef SQLITE_OMIT_WINDOWFUNC 3507 + if( ExprHasProperty(pExpr, EP_WinFunc) ){ 3508 + Window *pWin = pExpr->y.pWin; 3509 + pWin->pFilter = substExpr(pSubst, pWin->pFilter); 3510 + substExprList(pSubst, pWin->pPartition); 3511 + substExprList(pSubst, pWin->pOrderBy); 3512 + } 3513 +#endif 3506 3514 } 3507 3515 return pExpr; 3508 3516 } 3509 3517 static void substExprList( 3510 3518 SubstContext *pSubst, /* Description of the substitution */ 3511 3519 ExprList *pList /* List to scan and in which to make substitutes */ 3512 3520 ){
Changes to src/sqliteInt.h.
2581 2581 */ 2582 2582 #define EXPRDUP_REDUCE 0x0001 /* Used reduced-size Expr nodes */ 2583 2583 2584 2584 /* 2585 2585 ** True if the expression passed as an argument was a function with 2586 2586 ** an OVER() clause (a window function). 2587 2587 */ 2588 -#define IsWindowFunc(p) ( \ 2588 +#ifdef SQLITE_OMIT_WINDOWFUNC 2589 +# define IsWindowFunc(p) 0 2590 +#else 2591 +# define IsWindowFunc(p) ( \ 2589 2592 ExprHasProperty((p), EP_WinFunc) && p->y.pWin->eFrmType!=TK_FILTER \ 2590 -) 2593 + ) 2594 +#endif 2591 2595 2592 2596 /* 2593 2597 ** A list of expressions. Each expression may optionally have a 2594 2598 ** name. An expr/name combination can be used in several ways, such 2595 2599 ** as the list of "expr AS ID" fields following a "SELECT" or in the 2596 2600 ** list of "ID = expr" items in an UPDATE. A list of expressions can 2597 2601 ** also be used as the argument to a function, in which case the a.zName
Changes to test/filter1.test.
159 159 } {2.0 5.0 10.0} 160 160 do_execsql_test 4.3 { 161 161 SELECT a, avg(c) FILTER (WHERE b!=1) AS h FROM t1 GROUP BY a ORDER BY avg(c); 162 162 } {c 2.0 a 10.0 b 5.0} 163 163 do_execsql_test 4.4 { 164 164 SELECT a, avg(c) FILTER (WHERE b!=1) FROM t1 GROUP BY a ORDER BY 2 165 165 } {c 2.0 b 5.0 a 10.0} 166 + 167 +#------------------------------------------------------------------------- 168 +reset_db 169 +do_execsql_test 5.0 { 170 + CREATE TABLE t1(a, b); 171 + INSERT INTO t1 VALUES(1, 2); 172 + INSERT INTO t1 VALUES(1, 3); 173 +} 174 + 175 +do_execsql_test 5.1 { 176 + SELECT count(*) FILTER (WHERE b>2) FROM (SELECT * FROM t1) 177 +} {1} 178 + 179 +do_execsql_test 5.2 { 180 + SELECT count(*) FILTER (WHERE b>2) OVER () FROM (SELECT * FROM t1) 181 +} {1 1} 182 + 183 +do_execsql_test 5.3 { 184 + SELECT count(*) FILTER (WHERE b>2) OVER (ORDER BY b) FROM (SELECT * FROM t1) 185 +} {0 1} 166 186 167 187 finish_test 168 188
Changes to test/window1.test.
1185 1185 count () OVER win3 1186 1186 FROM t1 1187 1187 WINDOW win1 AS (ORDER BY a GROUPS BETWEEN 4 PRECEDING AND 1 FOLLOWING 1188 1188 EXCLUDE CURRENT ROW), 1189 1189 win2 AS (PARTITION BY b ORDER BY a), 1190 1190 win3 AS (win2 RANGE BETWEEN 5.2 PRECEDING AND true PRECEDING ); 1191 1191 } {1 1} 1192 + 1193 +#------------------------------------------------------------------------- 1194 +reset_db 1195 +do_execsql_test 31.1 { 1196 + CREATE TABLE t1(a, b); 1197 + CREATE TABLE t2(c, d); 1198 + CREATE TABLE t3(e, f); 1199 + 1200 + INSERT INTO t1 VALUES(1, 1); 1201 + INSERT INTO t2 VALUES(1, 1); 1202 + INSERT INTO t3 VALUES(1, 1); 1203 +} 1204 + 1205 +do_execsql_test 31.2 { 1206 + SELECT d IN (SELECT sum(c) OVER (ORDER BY e+c) FROM t3) FROM ( 1207 + SELECT * FROM t2 1208 + ); 1209 +} {1} 1210 + 1211 +do_execsql_test 31.3 { 1212 + SELECT d IN (SELECT sum(c) OVER (PARTITION BY d ORDER BY e+c) FROM t3) FROM ( 1213 + SELECT * FROM t2 1214 + ); 1215 +} {1} 1216 + 1217 +do_catchsql_test 31.3 { 1218 + SELECT d IN ( 1219 + SELECT sum(c) OVER ( ROWS BETWEEN d FOLLOWING AND UNBOUNDED FOLLOWING) 1220 + FROM t3 1221 + ) 1222 + FROM ( 1223 + SELECT * FROM t2 1224 + ); 1225 +} {1 {frame starting offset must be a non-negative integer}} 1226 + 1227 +do_catchsql_test 31.3 { 1228 + SELECT d IN ( 1229 + SELECT sum(c) OVER ( ROWS BETWEEN CURRENT ROW AND c FOLLOWING) 1230 + FROM t3 1231 + ) 1232 + FROM ( 1233 + SELECT * FROM t2 1234 + ); 1235 +} {1 {frame ending offset must be a non-negative integer}} 1192 1236 1193 1237 finish_test