Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Internally, remove all references to a Window object that belongs to an expression in an ORDER BY clause if that expression is converted to an alias of a result-set expression. |
|---|---|
| Downloads: | Tarball | ZIP archive |
| Timelines: | family | ancestors | descendants | both | branch-3.27 |
| Files: | files | file ages | folders |
| SHA3-256: |
a21ffcd8176672e791c11e7d3869878b |
| User & Date: | drh 2019-02-25 15:55:53.511 |
Context
|
2019-02-25
| ||
| 16:06 | Version 3.27.2 (check-in: bd49a8271d user: drh tags: release, branch-3.27, version-3.27.2) | |
| 15:55 | Internally, remove all references to a Window object that belongs to an expression in an ORDER BY clause if that expression is converted to an alias of a result-set expression. (check-in: a21ffcd817 user: drh tags: branch-3.27) | |
| 14:25 | Fix a typo in the documentation for sqlite3_total_changes(). (check-in: 4febdfb37b user: drh tags: branch-3.27) | |
|
2019-02-22
| ||
| 19:24 | Internally, remove all references to a Window object that belongs to an expression in an ORDER BY clause if that expression is converted to an alias of a result-set expression. Fix for [4feb3159c6]. (check-in: 579b66eaa0 user: dan tags: trunk) | |
Changes
Changes to src/resolve.c.
| ︙ | ︙ | |||
1239 1240 1241 1242 1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 |
resolveAlias(pParse, pEList, pItem->u.x.iOrderByCol-1, pItem->pExpr,
zType,0);
}
}
return 0;
}
/*
** pOrderBy is an ORDER BY or GROUP BY clause in SELECT statement pSelect.
** The Name context of the SELECT statement is pNC. zType is either
** "ORDER" or "GROUP" depending on which type of clause pOrderBy is.
**
** This routine resolves each term of the clause into an expression.
** If the order-by term is an integer I between 1 and N (where N is the
| > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1239 1240 1241 1242 1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 |
resolveAlias(pParse, pEList, pItem->u.x.iOrderByCol-1, pItem->pExpr,
zType,0);
}
}
return 0;
}
#ifndef SQLITE_OMIT_WINDOWFUNC
/*
** Walker callback for resolveRemoveWindows().
*/
static int resolveRemoveWindowsCb(Walker *pWalker, Expr *pExpr){
if( ExprHasProperty(pExpr, EP_WinFunc) ){
Window **pp;
for(pp=&pWalker->u.pSelect->pWin; *pp; pp=&(*pp)->pNextWin){
if( *pp==pExpr->y.pWin ){
*pp = (*pp)->pNextWin;
break;
}
}
}
return WRC_Continue;
}
/*
** Remove any Window objects owned by the expression pExpr from the
** Select.pWin list of Select object pSelect.
*/
static void resolveRemoveWindows(Select *pSelect, Expr *pExpr){
Walker sWalker;
memset(&sWalker, 0, sizeof(Walker));
sWalker.xExprCallback = resolveRemoveWindowsCb;
sWalker.u.pSelect = pSelect;
sqlite3WalkExpr(&sWalker, pExpr);
}
#else
# define resolveRemoveWindows(x,y)
#endif
/*
** pOrderBy is an ORDER BY or GROUP BY clause in SELECT statement pSelect.
** The Name context of the SELECT statement is pNC. zType is either
** "ORDER" or "GROUP" depending on which type of clause pOrderBy is.
**
** This routine resolves each term of the clause into an expression.
** If the order-by term is an integer I between 1 and N (where N is the
|
| ︙ | ︙ | |||
1305 1306 1307 1308 1309 1310 1311 |
/* Otherwise, treat the ORDER BY term as an ordinary expression */
pItem->u.x.iOrderByCol = 0;
if( sqlite3ResolveExprNames(pNC, pE) ){
return 1;
}
for(j=0; j<pSelect->pEList->nExpr; j++){
if( sqlite3ExprCompare(0, pE, pSelect->pEList->a[j].pExpr, -1)==0 ){
| < < | | | | < < < < < < < | 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 |
/* Otherwise, treat the ORDER BY term as an ordinary expression */
pItem->u.x.iOrderByCol = 0;
if( sqlite3ResolveExprNames(pNC, pE) ){
return 1;
}
for(j=0; j<pSelect->pEList->nExpr; j++){
if( sqlite3ExprCompare(0, pE, pSelect->pEList->a[j].pExpr, -1)==0 ){
/* Since this expresion is being changed into a reference
** to an identical expression in the result set, remove all Window
** objects belonging to the expression from the Select.pWin list. */
resolveRemoveWindows(pSelect, pE);
pItem->u.x.iOrderByCol = j+1;
}
}
}
return sqlite3ResolveOrderGroupBy(pParse, pSelect, pOrderBy, zType);
}
|
| ︙ | ︙ |
Changes to test/window1.test.
| ︙ | ︙ | |||
695 696 697 698 699 700 701 702 703 704 |
SELECT rowid, sum(a) OVER w1 FROM t7
WINDOW w1 AS (PARTITION BY b IN (SELECT rowid FROM t7));
} {
2 10
1 101
3 101
}
finish_test
| > > > > > > > > > > > > > > > > > > > > | 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 |
SELECT rowid, sum(a) OVER w1 FROM t7
WINDOW w1 AS (PARTITION BY b IN (SELECT rowid FROM t7));
} {
2 10
1 101
3 101
}
#-------------------------------------------------------------------------
do_execsql_test 17.0 {
CREATE TABLE t8(a);
INSERT INTO t8 VALUES(1), (2), (3);
}
do_execsql_test 17.1 {
SELECT +sum(0) OVER () ORDER BY +sum(0) OVER ();
} {0}
do_execsql_test 17.2 {
select +sum(a) OVER () FROM t8 ORDER BY +sum(a) OVER () DESC;
} {6 6 6}
do_execsql_test 17.3 {
SELECT 10+sum(a) OVER (ORDER BY a)
FROM t8
ORDER BY 10+sum(a) OVER (ORDER BY a) DESC;
} {16 13 11}
finish_test
|