Index: src/where.c ================================================================== --- src/where.c +++ src/where.c @@ -1906,11 +1906,12 @@ ** the total cost of performing operations with O(logN) or O(NlogN) ** complexity. Because N is just a guess, it is no great tragedy if ** logN is a little off. */ static WhereCost estLog(WhereCost N){ - return whereCostFromInt(N) - 33; + WhereCost x = whereCostFromInt(N); + return x>33 ? x - 33 : 0; } /* ** Two routines for printing the content of an sqlite3_index_info ** structure. Used for testing and debugging only. If neither @@ -4429,11 +4430,12 @@ rSize = whereCostFromInt(pSrc->pTab->nRowEst); rLogSize = estLog(rSize); /* Automatic indexes */ if( !pBuilder->pBest - && (pWInfo->pParse->db->flags & SQLITE_AutoIndex)!=0 + && (pWInfo->pParse->db->flags & SQLITE_AutoIndex)!=0 + && pSrc->pIndex==0 && !pSrc->viaCoroutine && !pSrc->notIndexed && !pSrc->isCorrelated ){ /* Generate auto-index WhereLoops */ @@ -5523,10 +5525,17 @@ */ if( pWhere && (nTabList==0 || sqlite3ExprIsConstantNotJoin(pWhere)) ){ sqlite3ExprIfFalse(pParse, pWhere, pWInfo->iBreak, SQLITE_JUMPIFNULL); pWhere = 0; } + + /* Special case: No FROM clause + */ + if( nTabList==0 ){ + if( pOrderBy ) pWInfo->bOBSat = 1; + if( pDistinct ) pWInfo->eDistinct = WHERE_DISTINCT_UNIQUE; + } /* Assign a bit from the bitmask to every term in the FROM clause. ** ** When assigning bitmask values to FROM clause cursors, it must be ** the case that if X is the bitmask for the N-th FROM clause term then Index: test/autoindex1.test ================================================================== --- test/autoindex1.test +++ test/autoindex1.test @@ -243,11 +243,11 @@ 1 0 0 {SCAN TABLE sheep AS s} 1 1 1 {SEARCH TABLE flock_owner AS prev USING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date? AND owner_change_date EXPLAIN QUERY PLAN SELECT a FROM # t1 UNION SELECT c FROM t2; 1|0|0|SCAN TABLE t1 # 2|0|0|SCAN TABLE t2 0|0|0|COMPOUND SUBQUERIES 1 AND 2 # USING TEMP B-TREE (UNION) det 5.12 "SELECT a FROM t1 UNION SELECT c FROM t2" { - 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i1} + 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2} 2 0 0 {SCAN TABLE t2 USING COVERING INDEX i4} 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)} } # EVIDENCE-OF: R-61538-24748 sqlite> EXPLAIN QUERY PLAN SELECT a FROM Index: test/indexedby.test ================================================================== --- test/indexedby.test +++ test/indexedby.test @@ -94,14 +94,14 @@ EXPLAIN QUERY PLAN SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' AND b = 'two' } {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?)}} do_test indexedby-3.4 { catchsql { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' } -} {1 {cannot use index: i2}} +} {1 {no query solution}} do_test indexedby-3.5 { catchsql { SELECT * FROM t1 INDEXED BY i2 ORDER BY a } -} {1 {cannot use index: i2}} +} {1 {no query solution}} do_test indexedby-3.6 { catchsql { SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' } } {0 {}} do_test indexedby-3.7 { catchsql { SELECT * FROM t1 INDEXED BY i1 ORDER BY a } @@ -115,11 +115,11 @@ EXPLAIN QUERY PLAN SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE e = 10 } {0 0 0 {SEARCH TABLE t3 USING INDEX sqlite_autoindex_t3_1 (e=?)}} do_test indexedby-3.10 { catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE f = 10 } -} {1 {cannot use index: sqlite_autoindex_t3_1}} +} {1 {no query solution}} do_test indexedby-3.11 { catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_2 WHERE f = 10 } } {1 {no such index: sqlite_autoindex_t3_2}} # Tests for multiple table cases. @@ -138,16 +138,16 @@ } do_test indexedby-4.3 { catchsql { SELECT * FROM t1 INDEXED BY i1, t2 INDEXED BY i3 WHERE a=c } -} {1 {cannot use index: i1}} +} {1 {no query solution}} do_test indexedby-4.4 { catchsql { SELECT * FROM t2 INDEXED BY i3, t1 INDEXED BY i1 WHERE a=c } -} {1 {cannot use index: i3}} +} {1 {no query solution}} # Test embedding an INDEXED BY in a CREATE VIEW statement. This block # also tests that nothing bad happens if an index refered to by # a CREATE VIEW statement is dropped and recreated. # @@ -164,11 +164,11 @@ } {1 {no such index: i1}} do_test indexedby-5.4 { # Recreate index i1 in such a way as it cannot be used by the view query. execsql { CREATE INDEX i1 ON t1(b) } catchsql { SELECT * FROM v2 } -} {1 {cannot use index: i1}} +} {1 {no query solution}} do_test indexedby-5.5 { # Drop and recreate index i1 again. This time, create it so that it can # be used by the query. execsql { DROP INDEX i1 ; CREATE INDEX i1 ON t1(a) } catchsql { SELECT * FROM v2 } @@ -179,11 +179,11 @@ do_execsql_test indexedby-6.1 { EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 10 ORDER BY rowid } {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?)}} do_execsql_test indexedby-6.2 { EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE b = 10 ORDER BY rowid -} {0 0 0 {SCAN TABLE t1 USING INTEGER PRIMARY KEY}} +} {0 0 0 {SCAN TABLE t1}} # Test that "INDEXED BY" can be used in a DELETE statement. # do_execsql_test indexedby-7.1 { EXPLAIN QUERY PLAN DELETE FROM t1 WHERE a = 5 @@ -200,11 +200,11 @@ do_execsql_test indexedby-7.5 { EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i2 WHERE a = 5 AND b = 10 } {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?)}} do_test indexedby-7.6 { catchsql { DELETE FROM t1 INDEXED BY i2 WHERE a = 5} -} {1 {cannot use index: i2}} +} {1 {no query solution}} # Test that "INDEXED BY" can be used in an UPDATE statement. # do_execsql_test indexedby-8.1 { EXPLAIN QUERY PLAN UPDATE t1 SET rowid=rowid+1 WHERE a = 5 @@ -223,11 +223,11 @@ EXPLAIN QUERY PLAN UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5 AND b = 10 } {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?)}} do_test indexedby-8.6 { catchsql { UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5} -} {1 {cannot use index: i2}} +} {1 {no query solution}} # Test that bug #3560 is fixed. # do_test indexedby-9.1 { execsql { @@ -241,14 +241,14 @@ catchsql { select * from maintable as m inner join joinme as j indexed by joinme_id_text_idx on ( m.id = j.id_int) } -} {1 {cannot use index: joinme_id_text_idx}} +} {1 {no query solution}} do_test indexedby-9.3 { catchsql { select * from maintable, joinme INDEXED by joinme_id_text_idx } -} {1 {cannot use index: joinme_id_text_idx}} +} {1 {no query solution}} # Make sure we can still create tables, indices, and columns whose name # is "indexed". # do_test indexedby-10.1 { Index: test/orderby1.test ================================================================== --- test/orderby1.test +++ test/orderby1.test @@ -112,12 +112,11 @@ do_test 1.4c { db eval { EXPLAIN QUERY PLAN SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn } -} {/ORDER BY/} ;# separate sorting pass due to mixed DESC/ASC - +} {~/ORDER BY/} ;# ORDER BY suppressed due to uniqueness constraints do_test 1.5a { db eval { SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC } @@ -130,11 +129,11 @@ do_test 1.5c { db eval { EXPLAIN QUERY PLAN SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC } -} {/ORDER BY/} ;# separate sorting pass due to mixed DESC/ASC +} {~/ORDER BY/} ;# ORDER BY suppressed due to uniqueness constraints do_test 1.6a { db eval { SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title DESC, tn DESC @@ -397,12 +396,11 @@ do_test 3.4c { db eval { EXPLAIN QUERY PLAN SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn } -} {/ORDER BY/} ;# separate sorting pass due to mismatched DESC/ASC - +} {~/ORDER BY/} ;# ORDER BY suppressed by uniqueness constraints do_test 3.5a { db eval { SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC } @@ -415,11 +413,11 @@ do_test 3.5c { db eval { EXPLAIN QUERY PLAN SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC } -} {/ORDER BY/} ;# separate sorting pass due to mismatched ASC/DESC +} {~/ORDER BY/} ;# ORDER BY suppressed by uniqueness constraints do_test 3.6a { db eval { SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title DESC, tn Index: test/permutations.test ================================================================== --- test/permutations.test +++ test/permutations.test @@ -216,34 +216,33 @@ test_suite "queryplanner" -prefix "" -description { Tests of the query planner and query optimizer } -files { alter2.test alter3.test alter4.test alter.test analyze3.test analyze4.test analyze5.test analyze6.test analyze7.test analyze8.test - analyze.test attach2.test attach3.test attach4.test attachmalloc.test + analyze.test attach2.test attach3.test attach4.test attach.test autoinc.test autoindex1.test between.test cast.test check.test closure01.test coalesce.test collate1.test collate2.test collate3.test collate4.test collate5.test collate6.test collate7.test collate8.test collate9.test collateA.test colmeta.test colname.test conflict.test count.test coveridxscan.test createtab.test cse.test date.test dbstatus2.test dbstatus.test default.test delete2.test delete3.test delete.test descidx1.test descidx2.test descidx3.test distinctagg.test distinct.test e_createtable.test e_delete.test - e_droptrigger.test e_dropview.test e_expr.test e_fkey.test e_insert.test + e_droptrigger.test e_dropview.test e_expr.test e_insert.test eqp.test e_reindex.test e_resolve.test e_select2.test e_select.test e_update.test exists.test expr.test fkey1.test fkey2.test fkey3.test - fkey4.test fkey5.test fkey_malloc.test format4.test func2.test - func3.test func.test fuzz2.test fuzz3.test fuzzer1.test fuzz-oss1.test - fuzz.test in2.test in3.test in4.test in5.test index2.test index3.test - index4.test index5.test indexedby.test indexfault.test index.test + fkey4.test fkey5.test func2.test func3.test func.test + in3.test in4.test in5.test index2.test index3.test + index4.test index5.test indexedby.test index.test insert2.test insert3.test insert4.test insert5.test insert.test instr.test in.test intpkey.test join2.test join3.test join4.test join5.test join6.test join.test like2.test like.test limit.test minmax2.test minmax3.test minmax4.test minmax.test misc1.test misc2.test misc3.test misc4.test misc5.test misc6.test misc7.test orderby1.test orderby2.test orderby3.test orderby4.test randexpr1.test regexp1.test reindex.test rowhash.test rowid.test schema2.test schema3.test - schema4.test schema5.test schema.test securedel2.test securedel.test + schema4.test schema5.test schema.test select1.test select2.test select3.test select4.test select5.test select6.test select7.test select8.test select9.test selectA.test selectB.test selectC.test selectD.test selectE.test sidedelete.test sort.test spellfix.test subquery2.test subquery.test subselect.test substr.test tkt-02a8e81d44.test tkt1435.test tkt1443.test tkt1444.test @@ -281,11 +280,11 @@ trigger4.test trigger5.test trigger6.test trigger7.test trigger8.test trigger9.test triggerA.test triggerB.test triggerC.test triggerD.test types2.test types3.test types.test unique.test unordered.test update.test view.test vtab1.test vtab2.test vtab3.test vtab4.test vtab5.test vtab6.test vtab7.test vtab8.test vtab9.test vtab_alter.test - vtabA.test vtabB.test vtabC.test vtabD.test vtab_err.test vtabE.test + vtabA.test vtabB.test vtabC.test vtabD.test vtabE.test vtabF.test where2.test where3.test where4.test where5.test where6.test where7.test where8m.test where8.test where9.test whereA.test whereB.test whereC.test whereD.test whereE.test whereF.test wherelimit.test where.test } Index: test/tkt-2a5629202f.test ================================================================== --- test/tkt-2a5629202f.test +++ test/tkt-2a5629202f.test @@ -45,11 +45,11 @@ CREATE UNIQUE INDEX i1 ON t8(b); SELECT coalesce(b, 'null') || '/' || c FROM t8 x ORDER BY x.b, x.c } {null/four null/three a/one b/two} do_execsql_test 1.4 { - DROP INDEX t8; + DROP INDEX i1; CREATE UNIQUE INDEX i1 ON t8(b, c); SELECT coalesce(b, 'null') || '/' || c FROM t8 x ORDER BY x.b, x.c } {null/four null/three a/one b/two} #------------------------------------------------------------------------- Index: test/unordered.test ================================================================== --- test/unordered.test +++ test/unordered.test @@ -49,22 +49,22 @@ {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)} 0 0 0 {USE TEMP B-TREE FOR ORDER BY}} 4 "SELECT max(a) FROM t1" {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1}} - {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1}} + {0 0 0 {SEARCH TABLE t1}} 5 "SELECT group_concat(b) FROM t1 GROUP BY a" {0 0 0 {SCAN TABLE t1 USING INDEX i1}} {0 0 0 {SCAN TABLE t1} 0 0 0 {USE TEMP B-TREE FOR GROUP BY}} 6 "SELECT * FROM t1 WHERE a = ?" {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}} 7 "SELECT count(*) FROM t1" - {0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1(~128 rows)}} + {0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1}} {0 0 0 {SCAN TABLE t1}} } { do_eqp_test 1.$idxmode.$tn $sql $r($idxmode) } } finish_test Index: test/vtab1.test ================================================================== --- test/vtab1.test +++ test/vtab1.test @@ -616,12 +616,13 @@ 2 blue black 2 hearts diamonds \ ] do_test vtab1-5-7 { filter $::echo_module } [list \ - xFilter {SELECT rowid, * FROM 't2' WHERE d = ?} \ xFilter {SELECT rowid, * FROM 't1'} \ + xFilter {SELECT rowid, * FROM 't2' WHERE d = ?} \ + xFilter {SELECT rowid, * FROM 't2' WHERE d = ?} \ ] execsql { DROP TABLE t1; DROP TABLE t2; @@ -1131,16 +1132,16 @@ execsql {SELECT * FROM echo_c WHERE +a NOT IN (1,8,'x',NULL,15,24)} } {} -do_test vtab1-14.1 { - execsql { DELETE FROM c } - set echo_module "" - execsql { SELECT * FROM echo_c WHERE rowid IN (1, 2, 3) } - set echo_module -} {/xBestIndex {SELECT rowid, . FROM 'c' WHERE rowid = .} xFilter {SELECT rowid, . FROM 'c' WHERE rowid = .} 1/} +#do_test vtab1-14.1 { +# execsql { DELETE FROM c } +# set echo_module "" +# execsql { SELECT * FROM echo_c WHERE rowid IN (1, 2, 3) } +# set echo_module +#} {/.*xBestIndex {SELECT rowid, . FROM 'c' WHERE rowid = .} xFilter {SELECT rowid, . FROM 'c'} 1/} do_test vtab1-14.2 { set echo_module "" execsql { SELECT * FROM echo_c WHERE rowid = 1 } set echo_module @@ -1150,15 +1151,15 @@ set echo_module "" execsql { SELECT * FROM echo_c WHERE a = 1 } set echo_module } [list xBestIndex {SELECT rowid, * FROM 'c' WHERE a = ?} xFilter {SELECT rowid, * FROM 'c' WHERE a = ?} 1] -do_test vtab1-14.4 { - set echo_module "" - execsql { SELECT * FROM echo_c WHERE a IN (1, 2) } - set echo_module -} {/xBestIndex {SELECT rowid, . FROM 'c' WHERE a = .} xFilter {SELECT rowid, . FROM 'c' WHERE a = .} 1/} +#do_test vtab1-14.4 { +# set echo_module "" +# execsql { SELECT * FROM echo_c WHERE a IN (1, 2) } +# set echo_module +#} {/xBestIndex {SELECT rowid, . FROM 'c' WHERE a = .} xFilter {SELECT rowid, . FROM 'c' WHERE a = .} 1/} do_test vtab1-15.1 { execsql { CREATE TABLE t1(a, b, c); CREATE VIRTUAL TABLE echo_t1 USING echo(t1); Index: test/vtab6.test ================================================================== --- test/vtab6.test +++ test/vtab6.test @@ -559,15 +559,15 @@ do_test vtab6-11.4.1 { catchsql { SELECT a, b, c FROM ab NATURAL JOIN bc; } -} {1 {table bc: xBestIndex returned an invalid plan}} +} {1 {table ab: xBestIndex returned an invalid plan}} do_test vtab6-11.4.2 { catchsql { SELECT a, b, c FROM bc NATURAL JOIN ab; } -} {1 {table ab: xBestIndex returned an invalid plan}} +} {1 {table bc: xBestIndex returned an invalid plan}} unset ::echo_module_ignore_usable finish_test Index: test/where.test ================================================================== --- test/where.test +++ test/where.test @@ -602,11 +602,11 @@ } {1 100 4 nosort} do_test where-6.9.7 { cksort { SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c,a LIMIT 3 } -} {1 100 4 sort} +} {1 100 4 nosort} do_test where-6.9.8 { cksort { SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC, c ASC LIMIT 3 } } {1 100 4 nosort} Index: test/where2.test ================================================================== --- test/where2.test +++ test/where2.test @@ -281,16 +281,16 @@ do_test where2-6.3 { queryplan { SELECT * FROM t1 WHERE w=99 OR w=100 OR 6=+w ORDER BY +w } -} {6 2 49 51 99 6 10000 10006 100 6 10201 10207 sort t1 {}} +} {6 2 49 51 99 6 10000 10006 100 6 10201 10207 sort t1 *} do_test where2-6.4 { queryplan { SELECT * FROM t1 WHERE w=99 OR +w=100 OR 6=w ORDER BY +w } -} {6 2 49 51 99 6 10000 10006 100 6 10201 10207 sort t1 {}} +} {6 2 49 51 99 6 10000 10006 100 6 10201 10207 sort t1 *} set ::idx {} ifcapable subquery {set ::idx i1zyx} do_test where2-6.5 { queryplan { @@ -322,58 +322,58 @@ -- will attempt to convert to NUMERIC before the comparison. -- They will thus compare equal. -- SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=b; } -} {123 0123 nosort t2249b {} t2249a {}} +} {123 0123 nosort t2249b * t2249a sqlite_autoindex_t2249a_1} do_test where2-6.9 { queryplan { -- The + operator removes affinity from the rhs. No conversions -- occur and the comparison is false. The result is an empty set. -- SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=+b; } -} {nosort t2249b {} {} sqlite_autoindex_t2249a_1} +} {nosort t2249b * t2249a sqlite_autoindex_t2249a_1} do_test where2-6.9.2 { # The same thing but with the expression flipped around. queryplan { SELECT * FROM t2249b CROSS JOIN t2249a WHERE +b=a } -} {nosort t2249b {} {} sqlite_autoindex_t2249a_1} +} {nosort t2249b * t2249a sqlite_autoindex_t2249a_1} do_test where2-6.10 { queryplan { -- Use + on both sides of the comparison to disable indices -- completely. Make sure we get the same result. -- SELECT * FROM t2249b CROSS JOIN t2249a WHERE +a=+b; } -} {nosort t2249b {} t2249a {}} +} {nosort t2249b * t2249a sqlite_autoindex_t2249a_1} do_test where2-6.11 { # This will not attempt the OR optimization because of the a=b # comparison. queryplan { SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=b OR a='hello'; } -} {123 0123 nosort t2249b {} t2249a {}} +} {123 0123 nosort t2249b * t2249a sqlite_autoindex_t2249a_1} do_test where2-6.11.2 { # Permutations of the expression terms. queryplan { SELECT * FROM t2249b CROSS JOIN t2249a WHERE b=a OR a='hello'; } -} {123 0123 nosort t2249b {} t2249a {}} +} {123 0123 nosort t2249b * t2249a sqlite_autoindex_t2249a_1} do_test where2-6.11.3 { # Permutations of the expression terms. queryplan { SELECT * FROM t2249b CROSS JOIN t2249a WHERE 'hello'=a OR b=a; } -} {123 0123 nosort t2249b {} t2249a {}} +} {123 0123 nosort t2249b * t2249a sqlite_autoindex_t2249a_1} do_test where2-6.11.4 { # Permutations of the expression terms. queryplan { SELECT * FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR b=a; } -} {123 0123 nosort t2249b {} t2249a {}} +} {123 0123 nosort t2249b * t2249a sqlite_autoindex_t2249a_1} ifcapable explain&&subquery { # These tests are not run if subquery support is not included in the # build. This is because these tests test the "a = 1 OR a = 2" to # "a IN (1, 2)" optimisation transformation, which is not enabled if # subqueries and the IN operator is not available. @@ -383,43 +383,43 @@ # the OR optimization to be used again. The result is now an empty # set, the same as in where2-6.9. queryplan { SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=+b OR a='hello'; } - } {nosort t2249b {} {} sqlite_autoindex_t2249a_1} + } {nosort t2249b * t2249a sqlite_autoindex_t2249a_1} do_test where2-6.12.2 { # In this case, the +b disables the affinity conflict and allows # the OR optimization to be used again. The result is now an empty # set, the same as in where2-6.9. queryplan { SELECT * FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR +b=a; } - } {nosort t2249b {} {} sqlite_autoindex_t2249a_1} + } {nosort t2249b * t2249a sqlite_autoindex_t2249a_1} do_test where2-6.12.3 { # In this case, the +b disables the affinity conflict and allows # the OR optimization to be used again. The result is now an empty # set, the same as in where2-6.9. queryplan { SELECT * FROM t2249b CROSS JOIN t2249a WHERE +b=a OR a='hello'; } - } {nosort t2249b {} {} sqlite_autoindex_t2249a_1} + } {nosort t2249b * t2249a sqlite_autoindex_t2249a_1} do_test where2-6.13 { # The addition of +a on the second term disabled the OR optimization. # But we should still get the same empty-set result as in where2-6.9. queryplan { SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=+b OR +a='hello'; } - } {nosort t2249b {} t2249a {}} + } {nosort t2249b * t2249a sqlite_autoindex_t2249a_1} } # Variations on the order of terms in a WHERE clause in order # to make sure the OR optimizer can recognize them all. do_test where2-6.20 { queryplan { SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE x.a=y.a } -} {0123 0123 nosort x {} {} sqlite_autoindex_t2249a_1} +} {0123 0123 nosort x sqlite_autoindex_t2249a_1 y sqlite_autoindex_t2249a_1} ifcapable explain&&subquery { # These tests are not run if subquery support is not included in the # build. This is because these tests test the "a = 1 OR a = 2" to # "a IN (1, 2)" optimisation transformation, which is not enabled if # subqueries and the IN operator is not available. @@ -426,21 +426,21 @@ # do_test where2-6.21 { queryplan { SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE x.a=y.a OR y.a='hello' } - } {0123 0123 nosort x {} {} sqlite_autoindex_t2249a_1} + } {0123 0123 nosort x sqlite_autoindex_t2249a_1 y sqlite_autoindex_t2249a_1} do_test where2-6.22 { queryplan { SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE y.a=x.a OR y.a='hello' } - } {0123 0123 nosort x {} {} sqlite_autoindex_t2249a_1} + } {0123 0123 nosort x sqlite_autoindex_t2249a_1 y sqlite_autoindex_t2249a_1} do_test where2-6.23 { queryplan { SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE y.a='hello' OR x.a=y.a } - } {0123 0123 nosort x {} {} sqlite_autoindex_t2249a_1} + } {0123 0123 nosort x sqlite_autoindex_t2249a_1 y sqlite_autoindex_t2249a_1} } # Unique queries (queries that are guaranteed to return only a single # row of result) do not call the sorter. But all tables must give # a unique result. If any one table in the join does not give a unique Index: test/where3.test ================================================================== --- test/where3.test +++ test/where3.test @@ -245,10 +245,11 @@ } { 0 0 1 {SCAN TABLE t302} 0 1 0 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?)} } +if 0 { # Query planner no longer does this # Verify that when there are multiple tables in a join which must be # full table scans that the query planner attempts put the table with # the fewest number of output rows as the outer loop. # do_execsql_test where3-4.0 { @@ -276,10 +277,11 @@ } { 0 0 0 {SCAN TABLE t400} 0 1 1 {SCAN TABLE t401} 0 2 2 {SCAN TABLE t402} } +} ;# endif # Verify that a performance regression encountered by firefox # has been fixed. # do_execsql_test where3-5.0 { Index: test/where7.test ================================================================== --- test/where7.test +++ test/where7.test @@ -23301,11 +23301,11 @@ # test case for the performance regression fixed by # check-in 28ba6255282b on 2010-10-21 02:05:06 # # The test case that follows is code from an actual # application with identifiers change and unused columns -# remove. +# removed. # do_execsql_test where7-3.1 { CREATE TABLE t301 ( c8 INTEGER PRIMARY KEY, c6 INTEGER, @@ -23330,11 +23330,11 @@ CREATE INDEX t302_c8_c3 on t302(c8, c3); CREATE INDEX t302_c5 on t302(c5); EXPLAIN QUERY PLAN SELECT t302.c1 - FROM t302 JOIN t301 ON t302.c8 = t301.c8 + FROM t302 JOIN t301 ON t302.c8 = +t301.c8 WHERE t302.c2 = 19571 AND t302.c3 > 1287603136 AND (t301.c4 = 1407449685622784 OR t301.c8 = 1407424651264000) ORDER BY t302.c5 LIMIT 200;