Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Make sure the ORDER BY LIMIT optimization is not applied if the inner-most loop can only have a single iteration and is hence not really a loop. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
13e3bd3de6b434b6182ef36be108d7ee |
| User & Date: | drh 2016-08-04 09:09:44 |
Context
|
2016-08-04
| ||
| 12:35 | Add the experimental SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION compile-time option. When enabled, the "unknown function" error is suppressed for EXPLAIN and a no-op function named "unknown()" is substituted. This facilitiates using the command-line shell to analyze queries from applications that contain many application-defined functions that are not normally available to the shell. check-in: b7f30a9f user: drh tags: trunk | |
| 09:13 | Merge the ORDER BY LIMIT fix, and other enhancements, from trunk. check-in: bf5a9542 user: drh tags: apple-osx | |
| 09:09 | Make sure the ORDER BY LIMIT optimization is not applied if the inner-most loop can only have a single iteration and is hence not really a loop. check-in: 13e3bd3d user: drh tags: trunk | |
|
2016-08-03
| ||
| 14:51 | Fix a typo in a comment in btree.c. No changes to code. check-in: 722c1281 user: drh tags: trunk | |
Changes
Changes to src/where.c.
3967 3968 3969 3970 3971 3972 3973 3974 3975 3976 3977 3978 3979 3980 3981 |
pWInfo->eDistinct = WHERE_DISTINCT_ORDERED;
}
}else{
pWInfo->nOBSat = pFrom->isOrdered;
pWInfo->revMask = pFrom->revLoop;
if( pWInfo->nOBSat<=0 ){
pWInfo->nOBSat = 0;
if( nLoop>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;
}
|
| |
3967 3968 3969 3970 3971 3972 3973 3974 3975 3976 3977 3978 3979 3980 3981 |
pWInfo->eDistinct = WHERE_DISTINCT_ORDERED;
}
}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;
}
|
Changes to test/limit2.test.
72 73 74 75 76 77 78 79 80 81 82 |
do_execsql_test limit2-120.2 {
SELECT a, b, '|' FROM t1 WHERE a IN (2,4,5,3,1) ORDER BY +b DESC LIMIT 5;
} {5 9999 | 1 1999 | 1 1998 | 1 1997 | 1 1996 |}
do_test limit2-120.3 {
set slow_count $sqlite_search_count
expr {$fast_count < 0.02*$slow_count}
} {1}
finish_test
|
> > > > > > > > > > > > > > > > > > > > > > |
72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 |
do_execsql_test limit2-120.2 {
SELECT a, b, '|' FROM t1 WHERE a IN (2,4,5,3,1) ORDER BY +b DESC LIMIT 5;
} {5 9999 | 1 1999 | 1 1998 | 1 1997 | 1 1996 |}
do_test limit2-120.3 {
set slow_count $sqlite_search_count
expr {$fast_count < 0.02*$slow_count}
} {1}
# Bug report against the new ORDER BY LIMIT optimization just prior to
# release. (Unreleased so there is no ticket).
#
# Make sure the optimization is not applied if the inner loop can only
# provide a single row of output.
#
do_execsql_test limit2-200 {
CREATE TABLE t200(a, b);
WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<1000)
INSERT INTO t200(a,b) SELECT x, x FROM c;
CREATE TABLE t201(x INTEGER PRIMARY KEY, y);
INSERT INTO t201(x,y) VALUES(2,12345);
SELECT *, '|' FROM t200, t201 WHERE x=b ORDER BY y LIMIT 3;
} {2 2 2 12345 |}
do_execsql_test limit2-210 {
SELECT *, '|' FROM t200 LEFT JOIN t201 ON x=b ORDER BY y LIMIT 3;
} {1 1 {} {} | 3 3 {} {} | 4 4 {} {} |}
finish_test
|