Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Eliminate some more cases of redundant sorting in window-function queries. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
8158d2aca68c5a253054376fdf1b8eaa |
| User & Date: | dan 2019-08-07 17:45:37 |
Context
|
2019-08-07
| ||
| 18:34 | Add "set TMP=%CD%" to the start of each msvc script output by releasetest_data.tcl. Otherwise, since binaries compiled with SQLITE_TEST all choose the same sequence of pseudo-random numbers, collisions between temp file names cause errors when running multiple tests in parallel. (check-in: f5d0436d user: dan tags: trunk) | |
| 17:45 | Eliminate some more cases of redundant sorting in window-function queries. (check-in: 8158d2ac user: dan tags: trunk) | |
| 13:25 | Do not make SQLITE_READ authorizer calls for tables without names, as all such tables will be internal-use-only tables for subqueries and whatnot. (check-in: 193c87fc user: drh tags: trunk) | |
Changes
Changes to src/window.c.
930 931 932 933 934 935 936 937 938 939 940 941 942 943 944 945 946 947 948 |
p->selFlags &= ~SF_Aggregate;
/* Create the ORDER BY clause for the sub-select. This is the concatenation
** of the window PARTITION and ORDER BY clauses. Then, if this makes it
** redundant, remove the ORDER BY from the parent SELECT. */
pSort = sqlite3ExprListDup(db, pMWin->pPartition, 0);
pSort = exprListAppendList(pParse, pSort, pMWin->pOrderBy, 1);
if( pSort && p->pOrderBy ){
if( sqlite3ExprListCompare(pSort, p->pOrderBy, -1)==0 ){
sqlite3ExprListDelete(db, p->pOrderBy);
p->pOrderBy = 0;
}
}
/* Assign a cursor number for the ephemeral table used to buffer rows.
** The OpenEphemeral instruction is coded later, after it is known how
** many columns the table will have. */
pMWin->iEphCsr = pParse->nTab++;
pParse->nTab += 3;
|
| > > > |
930 931 932 933 934 935 936 937 938 939 940 941 942 943 944 945 946 947 948 949 950 951 |
p->selFlags &= ~SF_Aggregate;
/* Create the ORDER BY clause for the sub-select. This is the concatenation
** of the window PARTITION and ORDER BY clauses. Then, if this makes it
** redundant, remove the ORDER BY from the parent SELECT. */
pSort = sqlite3ExprListDup(db, pMWin->pPartition, 0);
pSort = exprListAppendList(pParse, pSort, pMWin->pOrderBy, 1);
if( pSort && p->pOrderBy && p->pOrderBy->nExpr<=pSort->nExpr ){
int nSave = pSort->nExpr;
pSort->nExpr = p->pOrderBy->nExpr;
if( sqlite3ExprListCompare(pSort, p->pOrderBy, -1)==0 ){
sqlite3ExprListDelete(db, p->pOrderBy);
p->pOrderBy = 0;
}
pSort->nExpr = nSave;
}
/* Assign a cursor number for the ephemeral table used to buffer rows.
** The OpenEphemeral instruction is coded later, after it is known how
** many columns the table will have. */
pMWin->iEphCsr = pParse->nTab++;
pParse->nTab += 3;
|
Changes to test/window9.test.
140 141 142 143 144 145 146 147 148 149 150 151 |
do_execsql_test 4.1.1 {
SELECT b, b=count(*), '1,2' FROM t1 GROUP BY b;
} {1 0 1,2 2 1 1,2}
do_execsql_test 4.1.2 {
SELECT b, b=count(*), group_concat(b) OVER () FROM t1 GROUP BY b;
} {1 0 1,2 2 1 1,2}
finish_test
|
> > > > > > > > > > > > > > > > > > > > > > > > > > |
140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 |
do_execsql_test 4.1.1 {
SELECT b, b=count(*), '1,2' FROM t1 GROUP BY b;
} {1 0 1,2 2 1 1,2}
do_execsql_test 4.1.2 {
SELECT b, b=count(*), group_concat(b) OVER () FROM t1 GROUP BY b;
} {1 0 1,2 2 1 1,2}
#--------------------------------------------------------------------------
reset_db
do_execsql_test 5.0 {
CREATE TABLE t1(a, b, c, d, e);
CREATE INDEX i1 ON t1(a, b, c, d, e);
}
foreach {tn sql} {
1 {
SELECT
sum(e) OVER (),
sum(e) OVER (ORDER BY a),
sum(e) OVER (PARTITION BY a ORDER BY b),
sum(e) OVER (PARTITION BY a, b ORDER BY c),
sum(e) OVER (PARTITION BY a, b, c ORDER BY d)
FROM t1;
}
2 {
SELECT sum(e) OVER (PARTITION BY a ORDER BY b) FROM t1 ORDER BY a;
}
} {
do_test 5.1.$tn {
execsql "EXPLAIN QUERY PLAN $sql"
} {~/ORDER/}
}
finish_test
|