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 |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
13e3bd3de6b434b6182ef36be108d7ee |
User & Date: | drh 2016-08-04 09:09:44.760 |
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: b7f30a9ff2 user: drh tags: trunk) | |
09:13 | Merge the ORDER BY LIMIT fix, and other enhancements, from trunk. (check-in: bf5a9542d0 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: 13e3bd3de6 user: drh tags: trunk) | |
2016-08-03
| ||
14:51 | Fix a typo in a comment in btree.c. No changes to code. (check-in: 722c128163 user: drh tags: trunk) | |
Changes
Changes to src/where.c.
︙ | ︙ | |||
3967 3968 3969 3970 3971 3972 3973 | pWInfo->eDistinct = WHERE_DISTINCT_ORDERED; } }else{ pWInfo->nOBSat = pFrom->isOrdered; pWInfo->revMask = pFrom->revLoop; if( pWInfo->nOBSat<=0 ){ pWInfo->nOBSat = 0; | | | 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 |