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 | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
ccba7202b69d239724c3b589439c9c3b |
| User & Date: | dan 2019-10-10 15:17:09 |
Context
|
2019-10-10
| ||
| 16:41 | Fix a problem in the fts3 integrity-check routine causing it to report as corrupt tables that contain values with embedded 0x00 bytes. Ticket [278ac142625e3dcd] (check-in: 629e20c9 user: dan tags: trunk) | |
| 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: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) | |
|
2019-10-09
| ||
| 21:14 | Avoid assuming that an expression that contains the sub-expression (? IS FALSE) or (? IS TRUE) may only be true if ? is non-null. Fix for [a976c487]. (check-in: eb7ed90b 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.
2592 2593 2594 2595 2596 2597 2598 2599 2600 2601 2602 2603 2604 2605 2606 2607 2608 |
*/
#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
|
> > > | | > |
2592 2593 2594 2595 2596 2597 2598 2599 2600 2601 2602 2603 2604 2605 2606 2607 2608 2609 2610 2611 2612 |
*/ #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
|