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
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
....
3499
3500
3501
3502
3503
3504
3505
3506
3507
3508
3509
3510
3511
3512
|
sqlite3ExprDelete(db, p->pHaving);
sqlite3ExprListDelete(db, p->pOrderBy);
sqlite3ExprDelete(db, p->pLimit);
#ifndef SQLITE_OMIT_WINDOWFUNC
if( OK_IF_ALWAYS_TRUE(p->pWinDefn) ){
sqlite3WindowListDelete(db, p->pWinDefn);
}
#endif
if( OK_IF_ALWAYS_TRUE(p->pWith) ) sqlite3WithDelete(db, p->pWith);
assert( p->pWin==0 );
if( bFree ) sqlite3DbFreeNN(db, p);
p = pPrior;
bFree = 1;
}
}
/*
................................................................................
pExpr->pLeft = substExpr(pSubst, pExpr->pLeft);
pExpr->pRight = substExpr(pSubst, pExpr->pRight);
if( ExprHasProperty(pExpr, EP_xIsSelect) ){
substSelect(pSubst, pExpr->x.pSelect, 1);
}else{
substExprList(pSubst, pExpr->x.pList);
}
}
return pExpr;
}
static void substExprList(
SubstContext *pSubst, /* Description of the substitution */
ExprList *pList /* List to scan and in which to make substitutes */
){
|
>
<
>
>
>
>
>
>
>
>
|
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
....
3499
3500
3501
3502
3503
3504
3505
3506
3507
3508
3509
3510
3511
3512
3513
3514
3515
3516
3517
3518
3519
3520
|
sqlite3ExprDelete(db, p->pHaving);
sqlite3ExprListDelete(db, p->pOrderBy);
sqlite3ExprDelete(db, p->pLimit);
#ifndef SQLITE_OMIT_WINDOWFUNC
if( OK_IF_ALWAYS_TRUE(p->pWinDefn) ){
sqlite3WindowListDelete(db, p->pWinDefn);
}
assert( p->pWin==0 );
#endif
if( OK_IF_ALWAYS_TRUE(p->pWith) ) sqlite3WithDelete(db, p->pWith);
if( bFree ) sqlite3DbFreeNN(db, p);
p = pPrior;
bFree = 1;
}
}
/*
................................................................................
pExpr->pLeft = substExpr(pSubst, pExpr->pLeft);
pExpr->pRight = substExpr(pSubst, pExpr->pRight);
if( ExprHasProperty(pExpr, EP_xIsSelect) ){
substSelect(pSubst, pExpr->x.pSelect, 1);
}else{
substExprList(pSubst, pExpr->x.pList);
}
#ifndef SQLITE_OMIT_WINDOWFUNC
if( ExprHasProperty(pExpr, EP_WinFunc) ){
Window *pWin = pExpr->y.pWin;
pWin->pFilter = substExpr(pSubst, pWin->pFilter);
substExprList(pSubst, pWin->pPartition);
substExprList(pSubst, pWin->pOrderBy);
}
#endif
}
return pExpr;
}
static void substExprList(
SubstContext *pSubst, /* Description of the substitution */
ExprList *pList /* List to scan and in which to make substitutes */
){
|
Changes to src/sqliteInt.h.
2581 2582 2583 2584 2585 2586 2587 2588 2589 2590 2591 2592 2593 2594 2595 2596 2597 |
*/
#define EXPRDUP_REDUCE 0x0001 /* Used reduced-size Expr nodes */
/*
** True if the expression passed as an argument was a function with
** an OVER() clause (a window function).
*/
#define IsWindowFunc(p) ( \
ExprHasProperty((p), EP_WinFunc) && p->y.pWin->eFrmType!=TK_FILTER \
)
/*
** A list of expressions. Each expression may optionally have a
** name. An expr/name combination can be used in several ways, such
** as the list of "expr AS ID" fields following a "SELECT" or in the
** list of "ID = expr" items in an UPDATE. A list of expressions can
** also be used as the argument to a function, in which case the a.zName
|
> > > | | > |
2581 2582 2583 2584 2585 2586 2587 2588 2589 2590 2591 2592 2593 2594 2595 2596 2597 2598 2599 2600 2601 |
*/ #define EXPRDUP_REDUCE 0x0001 /* Used reduced-size Expr nodes */ /* ** True if the expression passed as an argument was a function with ** an OVER() clause (a window function). */ #ifdef SQLITE_OMIT_WINDOWFUNC # define IsWindowFunc(p) 0 #else # define IsWindowFunc(p) ( \ ExprHasProperty((p), EP_WinFunc) && p->y.pWin->eFrmType!=TK_FILTER \ ) #endif /* ** A list of expressions. Each expression may optionally have a ** name. An expr/name combination can be used in several ways, such ** as the list of "expr AS ID" fields following a "SELECT" or in the ** list of "ID = expr" items in an UPDATE. A list of expressions can ** also be used as the argument to a function, in which case the a.zName |
Changes to test/filter1.test.
159 160 161 162 163 164 165 166 167 168 |
} {2.0 5.0 10.0}
do_execsql_test 4.3 {
SELECT a, avg(c) FILTER (WHERE b!=1) AS h FROM t1 GROUP BY a ORDER BY avg(c);
} {c 2.0 a 10.0 b 5.0}
do_execsql_test 4.4 {
SELECT a, avg(c) FILTER (WHERE b!=1) FROM t1 GROUP BY a ORDER BY 2
} {c 2.0 b 5.0 a 10.0}
finish_test
|
> > > > > > > > > > > > > > > > > > > > |
159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 |
} {2.0 5.0 10.0}
do_execsql_test 4.3 {
SELECT a, avg(c) FILTER (WHERE b!=1) AS h FROM t1 GROUP BY a ORDER BY avg(c);
} {c 2.0 a 10.0 b 5.0}
do_execsql_test 4.4 {
SELECT a, avg(c) FILTER (WHERE b!=1) FROM t1 GROUP BY a ORDER BY 2
} {c 2.0 b 5.0 a 10.0}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 5.0 {
CREATE TABLE t1(a, b);
INSERT INTO t1 VALUES(1, 2);
INSERT INTO t1 VALUES(1, 3);
}
do_execsql_test 5.1 {
SELECT count(*) FILTER (WHERE b>2) FROM (SELECT * FROM t1)
} {1}
do_execsql_test 5.2 {
SELECT count(*) FILTER (WHERE b>2) OVER () FROM (SELECT * FROM t1)
} {1 1}
do_execsql_test 5.3 {
SELECT count(*) FILTER (WHERE b>2) OVER (ORDER BY b) FROM (SELECT * FROM t1)
} {0 1}
finish_test
|
Changes to test/window1.test.
1185 1186 1187 1188 1189 1190 1191 1192 1193 |
count () OVER win3
FROM t1
WINDOW win1 AS (ORDER BY a GROUPS BETWEEN 4 PRECEDING AND 1 FOLLOWING
EXCLUDE CURRENT ROW),
win2 AS (PARTITION BY b ORDER BY a),
win3 AS (win2 RANGE BETWEEN 5.2 PRECEDING AND true PRECEDING );
} {1 1}
finish_test
|
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
1185 1186 1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 1237 |
count () OVER win3
FROM t1
WINDOW win1 AS (ORDER BY a GROUPS BETWEEN 4 PRECEDING AND 1 FOLLOWING
EXCLUDE CURRENT ROW),
win2 AS (PARTITION BY b ORDER BY a),
win3 AS (win2 RANGE BETWEEN 5.2 PRECEDING AND true PRECEDING );
} {1 1}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 31.1 {
CREATE TABLE t1(a, b);
CREATE TABLE t2(c, d);
CREATE TABLE t3(e, f);
INSERT INTO t1 VALUES(1, 1);
INSERT INTO t2 VALUES(1, 1);
INSERT INTO t3 VALUES(1, 1);
}
do_execsql_test 31.2 {
SELECT d IN (SELECT sum(c) OVER (ORDER BY e+c) FROM t3) FROM (
SELECT * FROM t2
);
} {1}
do_execsql_test 31.3 {
SELECT d IN (SELECT sum(c) OVER (PARTITION BY d ORDER BY e+c) FROM t3) FROM (
SELECT * FROM t2
);
} {1}
do_catchsql_test 31.3 {
SELECT d IN (
SELECT sum(c) OVER ( ROWS BETWEEN d FOLLOWING AND UNBOUNDED FOLLOWING)
FROM t3
)
FROM (
SELECT * FROM t2
);
} {1 {frame starting offset must be a non-negative integer}}
do_catchsql_test 31.3 {
SELECT d IN (
SELECT sum(c) OVER ( ROWS BETWEEN CURRENT ROW AND c FOLLOWING)
FROM t3
)
FROM (
SELECT * FROM t2
);
} {1 {frame ending offset must be a non-negative integer}}
finish_test
|