From 083310dfcc6e590031c4462c9f3c5fbfe89effc1 Mon Sep 17 00:00:00 2001 From: drh Date: Fri, 28 Jan 2011 01:57:41 +0000 Subject: [PATCH] Change the weighting of binary searches on tables to 1/10th the cost of a search on an index. Change the assumed reduction in search space from a indexed range constraint from 1/3rd to 1/4th. Do not let the estimated number of rows drop below 1. FossilOrigin-Name: 4847c6cb71423248b186ab7842b97c83e2f5fefd --- manifest | 36 ++++++------- manifest.uuid | 2 +- src/where.c | 113 +++++++++++++++++++++++++++------------- test/analyze2.test | 12 +++-- test/analyze3.test | 2 +- test/e_createtable.test | 2 +- test/eqp.test | 4 +- test/indexedby.test | 4 +- test/like.test | 38 +++++++------- test/minmax3.test | 1 + test/where3.test | 5 +- test/where9.test | 2 +- 12 files changed, 133 insertions(+), 88 deletions(-) diff --git a/manifest b/manifest index 2793b4be74..d4624a6e59 100644 --- a/manifest +++ b/manifest @@ -1,8 +1,8 @@ -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 -C Restructuring\sand\sgeneralizing\sanalyze5.test.\s\s\sThe\swhole\sscript\sis\ncurrently\sdisabled\sand\swill\sneed\sto\sbe\sreenabled\sprior\sto\smerging\swith\strunk. -D 2011-01-24T17:46:35.872 +C Change\sthe\sweighting\sof\sbinary\ssearches\son\stables\sto\s1/10th\sthe\scost\sof\sa\nsearch\son\san\sindex.\s\sChange\sthe\sassumed\sreduction\sin\ssearch\sspace\sfrom\sa\nindexed\srange\sconstraint\sfrom\s1/3rd\sto\s1/4th.\s\sDo\snot\slet\sthe\sestimated\s\nnumber\sof\srows\sdrop\sbelow\s1. +D 2011-01-28T01:57:41.767 F Makefile.arm-wince-mingw32ce-gcc d6df77f1f48d690bd73162294bbba7f59507c72f F Makefile.in de6498556d536ae60bb8bb10e8c1ba011448658c F Makefile.linux-gcc 91d710bdc4998cb015f39edf3cb314ec4f4d7e23 @@ -243,7 +243,7 @@ F src/vtab.c b297e8fa656ab5e66244ab15680d68db0adbec30 F src/wal.c dbca424f71678f663a286ab2a98f947af1d412a7 F src/wal.h c1aac6593a0b02b15dc625987e619edeab39292e F src/walker.c 3112bb3afe1d85dc52317cb1d752055e9a781f8f -F src/where.c 87de2616150606fd1b61d7c88afdb90089ddd53d +F src/where.c f4915ac03e5e42c8416b35ca3ba34af841c00d12 F test/aggerror.test a867e273ef9e3d7919f03ef4f0e8c0d2767944f2 F test/alias.test 4529fbc152f190268a15f9384a5651bbbabc9d87 F test/all.test 51756962d522e474338e9b2ebb26e7364d4aa125 @@ -253,8 +253,8 @@ F test/alter3.test 8677e48d95536f7a6ed86a1a774744dadcc22b07 F test/alter4.test 1e5dd6b951e9f65ca66422edff02e56df82dd403 F test/altermalloc.test e81ac9657ed25c6c5bb09bebfa5a047cd8e4acfc F test/analyze.test c1eb87067fc16ece7c07e823d6395fd831b270c5 -F test/analyze2.test f45ac8d54bdad822139e53fc6307fc6b5ee41c69 -F test/analyze3.test 820ddfb7591b49607fbaf77240c7955ac3cabb04 +F test/analyze2.test 8f2b1534d43f5547ce9a6b736c021d4192c75be3 +F test/analyze3.test d61f55d8b472fc6e713160b1e577f7a68e63f38b F test/analyze4.test 757b37875cf9bb528d46f74497bc789c88365045 F test/analyze5.test 12df6def85e21971888f8be3c1867a505f1bf587 F test/async.test ad4ba51b77cd118911a3fe1356b0809da9c108c3 @@ -357,7 +357,7 @@ F test/descidx2.test 9f1a0c83fd57f8667c82310ca21b30a350888b5d F test/descidx3.test fe720e8b37d59f4cef808b0bf4e1b391c2e56b6f F test/diskfull.test 0cede7ef9d8f415d9d3944005c76be7589bb5ebb F test/distinctagg.test 1a6ef9c87a58669438fc771450d7a72577417376 -F test/e_createtable.test b8f5286879315d5b7f4cc5ead1afda4846f0c0bb +F test/e_createtable.test b40fc61bc4f1ad2a3c84590bd1d711507263d921 F test/e_delete.test 55d868b647acc091c261a10b9b0cb0ab660a6acb F test/e_droptrigger.test ddd4b28ed8a3d81bd5153fa0ab7559529a2ca03a F test/e_dropview.test b347bab30fc8de67b131594b3cd6f3d3bdaa753d @@ -375,7 +375,7 @@ F test/enc.test e54531cd6bf941ee6760be041dff19a104c7acea F test/enc2.test 6d91a5286f59add0cfcbb2d0da913b76f2242398 F test/enc3.test 5c550d59ff31dccdba5d1a02ae11c7047d77c041 F test/enc4.test 4b575ef09e0eff896e73bd24076f96c2aa6a42de -F test/eqp.test 69670e7919030f21de29fb99bf1d68f97aedcbdb +F test/eqp.test 8f535d902b2df780d22edb95113880480664f976 F test/eval.test bc269c365ba877554948441e91ad5373f9f91be3 F test/exclusive.test 53e1841b422e554cecf0160f937c473d6d0e3062 F test/exclusive2.test c2f2b2242dc436a12df8dd531e06843053bd3b9a @@ -486,7 +486,7 @@ F test/incrvacuum_ioerr.test 57d2f5777ab13fa03b87b262a4ea1bad5cfc0291 F test/index.test df7c00c6edd9504ab71c83a9514f1c5ca0fa54d8 F test/index2.test ee83c6b5e3173a3d7137140d945d9a5d4fdfb9d6 F test/index3.test 423a25c789fc8cc51aaf2a4370bbdde2d9e9eed7 -F test/indexedby.test d7367c5a0e8ed8db642824a68126753e0808c706 +F test/indexedby.test be501e381b82b2f8ab406309ba7aac46e221f4ad F test/init.test 15c823093fdabbf7b531fe22cf037134d09587a7 F test/insert.test aef273dd1cee84cc92407469e6bd1b3cdcb76908 F test/insert2.test 4f3a04d168c728ed5ec2c88842e772606c7ce435 @@ -517,7 +517,7 @@ F test/jrnlmode3.test c6522b276ba315fd1416198de6fc1da9e72409fb F test/keyword1.test a2400977a2e4fde43bf33754c2929fda34dbca05 F test/lastinsert.test 474d519c68cb79d07ecae56a763aa7f322c72f51 F test/laststmtchanges.test ae613f53819206b3222771828d024154d51db200 -F test/like.test 0f64aeaed50b6e3ebaef3af0b3b8f894aed5acca +F test/like.test a47f52692aac96ba82508efba74819214cdebc17 F test/like2.test 3b2ee13149ba4a8a60b59756f4e5d345573852da F test/limit.test 2db7b3b34fb925b8e847d583d2eb67531d0ce67e F test/loadext.test 0393ce12d9616aa87597dd0ec88181de181f6db0 @@ -561,7 +561,7 @@ F test/memsubsys1.test 66bf4e153e0eccc08c3b6af3641eb4f42d391c32 F test/memsubsys2.test 72a731225997ad5e8df89fdbeae9224616b6aecc F test/minmax.test 722d80816f7e096bf2c04f4111f1a6c1ba65453d F test/minmax2.test 33504c01a03bd99226144e4b03f7631a274d66e0 -F test/minmax3.test 66a60eb0f20281b0753249d347c5de0766954cee +F test/minmax3.test cc1e8b010136db0d01a6f2a29ba5a9f321034354 F test/misc1.test e56baf44656dd68d6475a4b44521045a60241e9b F test/misc2.test a628db7b03e18973e5d446c67696b03de718c9fd F test/misc3.test 72c5dc87a78e7865c5ec7a969fc572913dbe96b6 @@ -856,14 +856,14 @@ F test/walslow.test d21625e2e99e11c032ce949e8a94661576548933 F test/walthread.test a25a393c068a2b42b44333fa3fdaae9072f1617c F test/where.test de337a3fe0a459ec7c93db16a519657a90552330 F test/where2.test 43d4becaf5a5df854e6c21d624a1cb84c6904554 -F test/where3.test 8ebedae552e13fc7f2b4e8df6cbe72a095347400 +F test/where3.test 858d089cc4bf9aa0b06917a02abd5fd198655c96 F test/where4.test e9b9e2f2f98f00379e6031db6a6fca29bae782a2 F test/where5.test fdf66f96d29a064b63eb543e28da4dfdccd81ad2 F test/where6.test 5da5a98cec820d488e82708301b96cb8c18a258b F test/where7.test aa4cfcd6f66e2a4ef87b6717327325bf4d547502 F test/where8.test a6c740fd286d7883e274e17b6230a9d672a7ab1f F test/where8m.test da346596e19d54f0aba35ebade032a7c47d79739 -F test/where9.test 7ee38c3fd67e76789a6ec769f62f6433d3d4a5cf +F test/where9.test 24f19ad14bb1b831564ced5273e681e495662848 F test/whereA.test 24c234263c8fe358f079d5e57d884fb569d2da0a F test/whereB.test 0def95db3bdec220a731c7e4bec5930327c1d8c5 F test/wherelimit.test 5e9fd41e79bb2b2d588ed999d641d9c965619b31 @@ -900,14 +900,14 @@ F tool/speedtest2.tcl ee2149167303ba8e95af97873c575c3e0fab58ff F tool/speedtest8.c 2902c46588c40b55661e471d7a86e4dd71a18224 F tool/speedtest8inst1.c 293327bc76823f473684d589a8160bde1f52c14e F tool/vdbe-compress.tcl d70ea6d8a19e3571d7ab8c9b75cba86d1173ff0f -P b442525b0ba642bb8d57b87b7b9e373b6046454a -R ebf83ed63e8d1b48962a32de01a8b2cc +P 31fcc7067bd76da4bf19232811b90cf8b76eed74 +R f9a9d7b39b29b332b353f1913f9309e4 U drh -Z a63bcf5b3583a55e26e7d3f132337aba +Z 1ad30d06ebdb0226c333d2dcf85b95e8 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) -iD8DBQFNPbr/oxKgR168RlERAhb1AJ99UR+RTzr0SNXjxuLAr4iQrdLWlQCcC8Ik -EMLXFqheBOQt775+utT5Fjc= -=NdWh +iD8DBQFNQiKZoxKgR168RlERAjJeAJ9DU+63Qt3QOlsjnHQ7MP5GX/m8egCfe8c1 +SQn1lZ3E7QkvjZ3JOaQppDw= +=0Mzf -----END PGP SIGNATURE----- diff --git a/manifest.uuid b/manifest.uuid index 0a3f6d961e..50652bbd99 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -31fcc7067bd76da4bf19232811b90cf8b76eed74 \ No newline at end of file +4847c6cb71423248b186ab7842b97c83e2f5fefd \ No newline at end of file diff --git a/src/where.c b/src/where.c index d0540a4903..6d660e8cc0 100644 --- a/src/where.c +++ b/src/where.c @@ -2421,9 +2421,9 @@ static int valueFromExpr( ** constraints. ** ** In the absence of sqlite_stat2 ANALYZE data, each range inequality -** reduces the search space by 2/3rds. Hence a single constraint (x>?) -** results in a return of 33 and a range constraint (x>? AND x?) +** results in a return of 25 and a range constraint (x>? AND xwtFlags & TERM_VNULL)==0 ) *piEst /= 3; - if( pUpper ) *piEst /= 3; + if( pLower && (pLower->wtFlags & TERM_VNULL)==0 ) *piEst /= 4; + if( pUpper ) *piEst /= 4; return rc; } @@ -2636,12 +2636,12 @@ int whereInScanEst( /* -** Find the query plan for accessing a particular table. Write the +** Find the best query plan for accessing a particular table. Write the ** best query plan and its cost into the WhereCost object supplied as the ** last parameter. ** ** The lowest cost plan wins. The cost is an estimate of the amount of -** CPU and disk I/O need to process the request using the selected plan. +** CPU and disk I/O needed to process the requested result. ** Factors that influence cost include: ** ** * The estimated number of rows that will be retrieved. (The @@ -2660,7 +2660,7 @@ int whereInScanEst( ** ** If a NOT INDEXED clause (pSrc->notIndexed!=0) was attached to the table ** in the SELECT statement, then no indexes are considered. However, the -** selected plan may still take advantage of the tables built-in rowid +** selected plan may still take advantage of the built-in rowid primary key ** index. */ static void bestBtreeIndex( @@ -2703,9 +2703,11 @@ static void bestBtreeIndex( wsFlagMask = ~(WHERE_ROWID_EQ|WHERE_ROWID_RANGE); eqTermMask = idxEqTermMask; }else{ - /* There is no INDEXED BY clause. Create a fake Index object to - ** represent the primary key */ - Index *pFirst; /* Any other index on the table */ + /* There is no INDEXED BY clause. Create a fake Index object in local + ** variable sPk to represent the rowid primary key index. Make this + ** fake index the first in a chain of Index objects with all of the real + ** indices to follow */ + Index *pFirst; /* First of real indices on the table */ memset(&sPk, 0, sizeof(Index)); sPk.nColumn = 1; sPk.aiColumn = &aiColumnPk; @@ -2716,6 +2718,8 @@ static void bestBtreeIndex( aiRowEstPk[1] = 1; pFirst = pSrc->pTab->pIndex; if( pSrc->notIndexed==0 ){ + /* The real indices of the table are only considered if the + ** NOT INDEXED qualifier is omitted from the FROM clause */ sPk.pNext = pFirst; } pProbe = &sPk; @@ -2733,15 +2737,18 @@ static void bestBtreeIndex( double cost; /* Cost of using pProbe */ double nRow; /* Estimated number of rows in result set */ int rev; /* True to scan in reverse order */ + double nSearch; /* Estimated number of binary searches */ int wsFlags = 0; Bitmask used = 0; /* The following variables are populated based on the properties of - ** scan being evaluated. They are then used to determine the expected + ** index being evaluated. They are then used to determine the expected ** cost and number of rows returned. ** ** nEq: ** Number of equality terms that can be implemented using the index. + ** In other words, the number of initial fields in the index that + ** are used in == or IN or NOT NULL constraints of the WHERE clause. ** ** nInMul: ** The "in-multiplier". This is an estimate of how many seek operations @@ -2765,7 +2772,9 @@ static void bestBtreeIndex( ** ** bInEst: ** Set to true if there was at least one "x IN (SELECT ...)" term used - ** in determining the value of nInMul. + ** in determining the value of nInMul. Note that the RHS of the + ** IN operator must be a SELECT, not a value list, for this variable + ** to be true. ** ** estBound: ** An estimate on the amount of the table that must be searched. A @@ -2773,8 +2782,8 @@ static void bestBtreeIndex( ** might reduce this to a value less than 100 to indicate that only ** a fraction of the table needs searching. In the absence of ** sqlite_stat2 ANALYZE data, a single inequality reduces the search - ** space to 1/3rd its original size. So an x>? constraint reduces - ** estBound to 33. Two constraints (x>? AND x? constraint reduces + ** estBound to 25. Two constraints (x>? AND xx.pList) ){ + }else if( ALWAYS(pExpr->x.pList && pExpr->x.pList->nExpr) ){ /* "x IN (value, value, ...)" */ - nInMul *= pExpr->x.pList->nExpr + 1; + nInMul *= pExpr->x.pList->nExpr; } }else if( pTerm->eOperator & WO_ISNULL ){ wsFlags |= WHERE_COLUMN_NULL; @@ -2923,16 +2935,41 @@ static void bestBtreeIndex( ** that are excluded by range constraints. */ nRow = (nRow * (double)estBound) / (double)100; + if( nRow<1 ) nRow = 1; - /* Assume constant cost to access a row and logarithmic cost to - ** do a binary search. Hence, the initial cost is the number of output - ** rows plus log2(table-size) times the number of binary searches. + /* Assume constant cost to advance from one row to the next and + ** logarithmic cost to do a binary search. Hence, the initial cost + ** is the number of output rows plus log2(table-size) times the + ** number of binary searches. + ** + ** Because fan-out on tables is so much higher than the fan-out on + ** indices (because table btrees contain only integer keys in non-leaf + ** nodes) we weight the cost of a table binary search as 1/10th the + ** cost of an index binary search. */ - if( pIdx && bLookup ){ - cost = nRow + (nInMul+nRow)*estLog(aiRowEst[0]); + if( pIdx ){ + if( bLookup ){ + /* For an index lookup followed by a table lookup: + ** nInMul index searches to find the start of each index range + ** + nRow steps through the index + ** + nRow table searches to lookup the table entry using the rowid + */ + nSearch = nInMul + nRow/10; + }else{ + /* For a covering index: + ** nInMul binary searches to find the initial entry + ** + nRow steps through the index + */ + nSearch = nInMul; + } }else{ - cost = nRow + nInMul*estLog(aiRowEst[0]); + /* For a rowid primary key lookup: + ** nInMult binary searches to find the initial entry scaled by 1/10th + ** + nRow steps through the table + */ + nSearch = nInMul/10; } + cost = nRow + nSearch*estLog(aiRowEst[0]); /* Add in the estimated cost of sorting the result. This cost is expanded ** by a fudge factor of 3.0 to account for the fact that a sorting step @@ -2987,7 +3024,11 @@ static void bestBtreeIndex( nSkipRange--; }else{ /* Assume each additional range constraint reduces the result - ** set size by a factor of 3 */ + ** set size by a factor of 3. Indexed range constraints reduce + ** the search space by a larger factor: 4. We make indexed range + ** more selective intentionally because of the subjective + ** observation that indexed range constraints really are more + ** selective in practice, on average. */ nRow /= 3; } }else if( pTerm->eOperator!=WO_NOOP ){ diff --git a/test/analyze2.test b/test/analyze2.test index add73af43a..de2567bb6f 100644 --- a/test/analyze2.test +++ b/test/analyze2.test @@ -242,10 +242,12 @@ do_test analyze2-4.1 { } {} do_test analyze2-4.2 { execsql { + PRAGMA automatic_index=OFF; SELECT tbl,idx,group_concat(sample,' ') FROM sqlite_stat2 WHERE idx = 't3a' - GROUP BY tbl,idx + GROUP BY tbl,idx; + PRAGMA automatic_index=ON; } } {t3 t3a {AfA bEj CEj dEj EEj fEj GEj hEj IEj jEj}} do_test analyze2-4.3 { @@ -408,7 +410,7 @@ do_test analyze2-6.2.1 { t5.a>1 AND t5.a<15 AND t6.a>1 } -} {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a>? AND a? AND a1 AND t5.a<15 AND t6.a>1 } -} {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a>? AND a? AND a1 AND t5.a<15 AND t6.a>1 } -} {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a>? AND a? AND a1 AND t5.a<15 AND t6.a>1 } db1 - } {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a>? AND a? AND a? AND b? AND b10" - {0 0 0 {SEARCH TABLE t2 USING INDEX sqlite_autoindex_t2_1 (b=? AND c>?) (~3 rows)}} + {0 0 0 {SEARCH TABLE t2 USING INDEX sqlite_autoindex_t2_1 (b=? AND c>?) (~2 rows)}} } # EVIDENCE-OF: R-45493-35653 A CHECK constraint may be attached to a diff --git a/test/eqp.test b/test/eqp.test index cd08b8ff16..ca7544b41b 100644 --- a/test/eqp.test +++ b/test/eqp.test @@ -392,7 +392,7 @@ det 5.3.1 "SELECT a, b FROM t1 WHERE a=1" { # (~1000000 rows) do_execsql_test 5.4.0 {CREATE TABLE t2(c, d)} det 5.4.1 "SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2" { - 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) (~3 rows)} + 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) (~2 rows)} 0 1 1 {SCAN TABLE t2 (~1000000 rows)} } @@ -401,7 +401,7 @@ det 5.4.1 "SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2" { # USING COVERING INDEX i2 (a=? AND b>?) (~3 rows) 0|1|0|SCAN TABLE t2 # (~1000000 rows) det 5.5 "SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2" { - 0 0 1 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) (~3 rows)} + 0 0 1 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) (~2 rows)} 0 1 0 {SCAN TABLE t2 (~1000000 rows)} } diff --git a/test/indexedby.test b/test/indexedby.test index b8b5be63b6..7ccc4de24a 100644 --- a/test/indexedby.test +++ b/test/indexedby.test @@ -154,10 +154,10 @@ do_test indexedby-4.4 { do_execsql_test indexedby-5.1 { CREATE VIEW v2 AS SELECT * FROM t1 INDEXED BY i1 WHERE a > 5; EXPLAIN QUERY PLAN SELECT * FROM v2 -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?) (~330000 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?) (~250000 rows)}} do_execsql_test indexedby-5.2 { EXPLAIN QUERY PLAN SELECT * FROM v2 WHERE b = 10 -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?) (~33000 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?) (~25000 rows)}} do_test indexedby-5.3 { execsql { DROP INDEX i1 } catchsql { SELECT * FROM v2 } diff --git a/test/like.test b/test/like.test index e60d89bdc3..bd9a6c39c1 100644 --- a/test/like.test +++ b/test/like.test @@ -707,32 +707,32 @@ ifcapable like_opt&&!icu { INSERT INTO t10 VALUES(45,45,45,45,45,45); } count { - SELECT a FROM t10 WHERE b LIKE '12%' ORDER BY a; + SELECT a FROM t10 WHERE b LIKE '12%' ORDER BY +a; } } {12 123 scan 5 like 6} do_test like-10.2 { count { - SELECT a FROM t10 WHERE c LIKE '12%' ORDER BY a; + SELECT a FROM t10 WHERE c LIKE '12%' ORDER BY +a; } } {12 123 scan 5 like 6} do_test like-10.3 { count { - SELECT a FROM t10 WHERE d LIKE '12%' ORDER BY a; + SELECT a FROM t10 WHERE d LIKE '12%' ORDER BY +a; } } {12 123 scan 5 like 6} do_test like-10.4 { count { - SELECT a FROM t10 WHERE e LIKE '12%' ORDER BY a; + SELECT a FROM t10 WHERE e LIKE '12%' ORDER BY +a; } } {12 123 scan 5 like 6} do_test like-10.5 { count { - SELECT a FROM t10 WHERE f LIKE '12%' ORDER BY a; + SELECT a FROM t10 WHERE f LIKE '12%' ORDER BY +a; } } {12 123 scan 3 like 0} do_test like-10.6 { count { - SELECT a FROM t10 WHERE a LIKE '12%' ORDER BY a; + SELECT a FROM t10 WHERE a LIKE '12%' ORDER BY +a; } } {12 123 scan 5 like 6} do_test like-10.10 { @@ -748,32 +748,32 @@ ifcapable like_opt&&!icu { INSERT INTO t10b SELECT * FROM t10; } count { - SELECT a FROM t10b WHERE b GLOB '12*' ORDER BY a; + SELECT a FROM t10b WHERE b GLOB '12*' ORDER BY +a; } } {12 123 scan 5 like 6} do_test like-10.11 { count { - SELECT a FROM t10b WHERE c GLOB '12*' ORDER BY a; + SELECT a FROM t10b WHERE c GLOB '12*' ORDER BY +a; } } {12 123 scan 5 like 6} do_test like-10.12 { count { - SELECT a FROM t10b WHERE d GLOB '12*' ORDER BY a; + SELECT a FROM t10b WHERE d GLOB '12*' ORDER BY +a; } } {12 123 scan 5 like 6} do_test like-10.13 { count { - SELECT a FROM t10b WHERE e GLOB '12*' ORDER BY a; + SELECT a FROM t10b WHERE e GLOB '12*' ORDER BY +a; } } {12 123 scan 5 like 6} do_test like-10.14 { count { - SELECT a FROM t10b WHERE f GLOB '12*' ORDER BY a; + SELECT a FROM t10b WHERE f GLOB '12*' ORDER BY +a; } } {12 123 scan 3 like 0} do_test like-10.15 { count { - SELECT a FROM t10b WHERE a GLOB '12*' ORDER BY a; + SELECT a FROM t10b WHERE a GLOB '12*' ORDER BY +a; } } {12 123 scan 5 like 6} } @@ -819,7 +819,7 @@ do_test like-11.3 { queryplan { PRAGMA case_sensitive_like=OFF; CREATE INDEX t11b ON t11(b); - SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY a; + SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY +a; } } {abc abcd ABC ABCD sort {} t11b} do_test like-11.4 { @@ -833,37 +833,37 @@ do_test like-11.5 { PRAGMA case_sensitive_like=OFF; DROP INDEX t11b; CREATE INDEX t11bnc ON t11(b COLLATE nocase); - SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY a; + SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY +a; } } {abc abcd ABC ABCD sort {} t11bnc} do_test like-11.6 { queryplan { CREATE INDEX t11bb ON t11(b COLLATE binary); - SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY a; + SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY +a; } } {abc abcd ABC ABCD sort {} t11bnc} do_test like-11.7 { queryplan { PRAGMA case_sensitive_like=ON; - SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY a; + SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY +a; } } {abc abcd sort {} t11bb} do_test like-11.8 { queryplan { PRAGMA case_sensitive_like=OFF; - SELECT b FROM t11 WHERE b GLOB 'abc*' ORDER BY a; + SELECT b FROM t11 WHERE b GLOB 'abc*' ORDER BY +a; } } {abc abcd sort {} t11bb} do_test like-11.9 { queryplan { CREATE INDEX t11cnc ON t11(c COLLATE nocase); CREATE INDEX t11cb ON t11(c COLLATE binary); - SELECT c FROM t11 WHERE c LIKE 'abc%' ORDER BY a; + SELECT c FROM t11 WHERE c LIKE 'abc%' ORDER BY +a; } } {abc abcd ABC ABCD sort {} t11cnc} do_test like-11.10 { queryplan { - SELECT c FROM t11 WHERE c GLOB 'abc*' ORDER BY a; + SELECT c FROM t11 WHERE c GLOB 'abc*' ORDER BY +a; } } {abc abcd sort {} t11cb} diff --git a/test/minmax3.test b/test/minmax3.test index 8e8a0a5256..483fa612d2 100644 --- a/test/minmax3.test +++ b/test/minmax3.test @@ -52,6 +52,7 @@ do_test minmax3-1.0 { INSERT INTO t1 VALUES('2', 'V', 'five'); INSERT INTO t1 VALUES('3', 'VI', 'six'); COMMIT; + PRAGMA automatic_index=OFF; } } {} do_test minmax3-1.1.1 { diff --git a/test/where3.test b/test/where3.test index c032f1f0ea..29ba9e33f0 100644 --- a/test/where3.test +++ b/test/where3.test @@ -225,14 +225,15 @@ do_execsql_test where3-3.0 { ANALYZE; explain query plan SELECT * FROM t302, t301 WHERE t302.x=5 AND t301.a=t302.y; } { - 0 0 0 {SCAN TABLE t302 (~0 rows)} + 0 0 0 {SCAN TABLE t302 (~1 rows)} 0 1 1 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} } +exit do_execsql_test where3-3.1 { explain query plan SELECT * FROM t301, t302 WHERE t302.x=5 AND t301.a=t302.y; } { - 0 0 1 {SCAN TABLE t302 (~0 rows)} + 0 0 1 {SCAN TABLE t302 (~1 rows)} 0 1 0 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} } diff --git a/test/where9.test b/test/where9.test index 1ffc8ef9ea..9a180116a8 100644 --- a/test/where9.test +++ b/test/where9.test @@ -472,7 +472,7 @@ ifcapable explain { do_execsql_test where9-5.3 { EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c>=31031 OR d IS NULL) } { - 0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>?) (~165000 rows)} + 0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>?) (~125000 rows)} } } -- 2.47.2