Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix the ORDER BY LIMIT optimization backport so that it works when the ORDER BY uses the DESC direction. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | branch-3.8.9 |
| Files: | files | file ages | folders |
| SHA1: |
0c3cafb7ebb887dbfa2652f8bf69b52e |
| User & Date: | drh 2016-09-23 18:06:22 |
Context
|
2016-11-02
| ||
| 16:29 | When the block sorting optimization is used in a scalar subquery, be sure to exit the loop as soon as the first valid output row is received. Fix for ticket [cb3aa0641d9a4] backported to the 3.8.9 branch. check-in: 8e4ba115 user: drh tags: branch-3.8.9 | |
|
2016-09-23
| ||
| 18:06 | Fix the ORDER BY LIMIT optimization backport so that it works when the ORDER BY uses the DESC direction. check-in: 0c3cafb7 user: drh tags: branch-3.8.9 | |
|
2016-09-14
| ||
| 01:43 | Backport the ORDER BY LIMIT optimization to version 3.8.9. check-in: db361482 user: drh tags: branch-3.8.9 | |
Changes
Changes to src/where.c.
6268 6269 6270 6271 6272 6273 6274 6275 6276 6277 6278 6279 6280 6281 6282 6283 6284 6285 6286 6287 6288 6289 6290 6291 |
}
}else{
pWInfo->nOBSat = pFrom->isOrdered;
pWInfo->revMask = pFrom->revLoop;
if( pWInfo->nOBSat<=0 ){
pWInfo->nOBSat = 0;
if( nLoop>0 && (pFrom->aLoop[nLoop-1]->wsFlags & WHERE_ONEROW)==0 ){
Bitmask m;
int rc = wherePathSatisfiesOrderBy(pWInfo, pWInfo->pOrderBy, pFrom,
WHERE_ORDERBY_LIMIT, nLoop-1, pFrom->aLoop[nLoop-1], &m);
if( rc==pWInfo->pOrderBy->nExpr ){
pWInfo->bOrderedInnerLoop = 1;
pWInfo->revMask = m;
}
}
}
pWInfo->revMask = pFrom->revLoop;
}
if( (pWInfo->wctrlFlags & WHERE_SORTBYGROUP)
&& pWInfo->nOBSat==pWInfo->pOrderBy->nExpr
){
Bitmask revMask = 0;
int nOrder = wherePathSatisfiesOrderBy(pWInfo, pWInfo->pOrderBy,
pFrom, 0, nLoop-1, pFrom->aLoop[nLoop-1], &revMask
|
| < |
6268 6269 6270 6271 6272 6273 6274 6275 6276 6277 6278 6279 6280 6281 6282 6283 6284 6285 6286 6287 6288 6289 6290 |
}
}else{
pWInfo->nOBSat = pFrom->isOrdered;
pWInfo->revMask = pFrom->revLoop;
if( pWInfo->nOBSat<=0 ){
pWInfo->nOBSat = 0;
if( nLoop>0 && (pFrom->aLoop[nLoop-1]->wsFlags & WHERE_ONEROW)==0 ){
Bitmask m = 0;
int rc = wherePathSatisfiesOrderBy(pWInfo, pWInfo->pOrderBy, pFrom,
WHERE_ORDERBY_LIMIT, nLoop-1, pFrom->aLoop[nLoop-1], &m);
if( rc==pWInfo->pOrderBy->nExpr ){
pWInfo->bOrderedInnerLoop = 1;
pWInfo->revMask = m;
}
}
}
}
if( (pWInfo->wctrlFlags & WHERE_SORTBYGROUP)
&& pWInfo->nOBSat==pWInfo->pOrderBy->nExpr
){
Bitmask revMask = 0;
int nOrder = wherePathSatisfiesOrderBy(pWInfo, pWInfo->pOrderBy,
pFrom, 0, nLoop-1, pFrom->aLoop[nLoop-1], &revMask
|
Changes to test/limit2.test.
105 106 107 108 109 110 111 112 113 114 115 |
INSERT INTO t300 VALUES(0,1,99),(0,1,0),(0,0,0);
SELECT *,'.' FROM t300 WHERE a=0 AND (c=0 OR c=99) ORDER BY c DESC;
} {0 1 99 . 0 0 0 . 0 1 0 .}
do_execsql_test limit2-310 {
SELECT *,'.' FROM t300 WHERE a=0 AND (c=0 OR c=99) ORDER BY c DESC LIMIT 1;
} {0 1 99 .}
finish_test
|
> > | > > > > > > > < |
105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 |
INSERT INTO t300 VALUES(0,1,99),(0,1,0),(0,0,0);
SELECT *,'.' FROM t300 WHERE a=0 AND (c=0 OR c=99) ORDER BY c DESC;
} {0 1 99 . 0 0 0 . 0 1 0 .}
do_execsql_test limit2-310 {
SELECT *,'.' FROM t300 WHERE a=0 AND (c=0 OR c=99) ORDER BY c DESC LIMIT 1;
} {0 1 99 .}
# Make sure the SELECT loop is ordered correctly for the direction of
# the ORDER BY
#
do_execsql_test limit2-400 {
CREATE TABLE t400(a,b);
CREATE INDEX t400_ab ON t400(a,b);
INSERT INTO t400(a,b) VALUES(1,90),(1,40),(2,80),(2,30),(3,70),(3,20);
SELECT *,'x' FROM t400 WHERE a IN (1,2,3) ORDER BY b DESC LIMIT 3;
SELECT *,'y' FROM t400 WHERE a IN (1,2,3) ORDER BY +b DESC LIMIT 3;
} {1 90 x 2 80 x 3 70 x 1 90 y 2 80 y 3 70 y}
finish_test
|