166f0dcb7Sdan# 2011 April 9 266f0dcb7Sdan# 366f0dcb7Sdan# The author disclaims copyright to this source code. In place of 466f0dcb7Sdan# a legal notice, here is a blessing: 566f0dcb7Sdan# 666f0dcb7Sdan# May you do good and not evil. 766f0dcb7Sdan# May you find forgiveness for yourself and forgive others. 866f0dcb7Sdan# May you share freely, never taking more than you give. 966f0dcb7Sdan# 1066f0dcb7Sdan#*********************************************************************** 1166f0dcb7Sdan# This file implements regression tests for SQLite library. 1266f0dcb7Sdan# 1366f0dcb7Sdan 1466f0dcb7Sdanset testdir [file dirname $argv0] 1566f0dcb7Sdansource $testdir/tester.tcl 1666f0dcb7Sdan 1766f0dcb7Sdanset testprefix unordered 1866f0dcb7Sdan 1966f0dcb7Sdando_execsql_test 1.0 { 2066f0dcb7Sdan CREATE TABLE t1(a, b); 2166f0dcb7Sdan CREATE INDEX i1 ON t1(a); 2266f0dcb7Sdan INSERT INTO t1 VALUES(1, 'xxx'); 2366f0dcb7Sdan INSERT INTO t1 SELECT a+1, b FROM t1; 2466f0dcb7Sdan INSERT INTO t1 SELECT a+2, b FROM t1; 2566f0dcb7Sdan INSERT INTO t1 SELECT a+4, b FROM t1; 2666f0dcb7Sdan INSERT INTO t1 SELECT a+8, b FROM t1; 2766f0dcb7Sdan INSERT INTO t1 SELECT a+16, b FROM t1; 2866f0dcb7Sdan INSERT INTO t1 SELECT a+32, b FROM t1; 2966f0dcb7Sdan INSERT INTO t1 SELECT a+64, b FROM t1; 3066f0dcb7Sdan ANALYZE; 3166f0dcb7Sdan} {} 3266f0dcb7Sdan 3366f0dcb7Sdanforeach idxmode {ordered unordered} { 344e50c5ecSdrh catchsql { DELETE FROM sqlite_stat2 } 354e50c5ecSdrh catchsql { DELETE FROM sqlite_stat3 } 3666f0dcb7Sdan if {$idxmode == "unordered"} { 3766f0dcb7Sdan execsql { UPDATE sqlite_stat1 SET stat = stat || ' unordered' } 384e50c5ecSdrh } 3966f0dcb7Sdan db close 4066f0dcb7Sdan sqlite3 db test.db 4166f0dcb7Sdan foreach {tn sql r(ordered) r(unordered)} { 4266f0dcb7Sdan 1 "SELECT * FROM t1 ORDER BY a" 43*8210233cSdrh {SCAN t1 USING INDEX i1} 44*8210233cSdrh {SCAN t1*USE TEMP B-TREE FOR ORDER BY} 456b682862Sdan 2 "SELECT * FROM t1 WHERE a > 100" 46*8210233cSdrh {SEARCH t1 USING INDEX i1 (a>?)} 47*8210233cSdrh {SCAN t1} 4866f0dcb7Sdan 3 "SELECT * FROM t1 WHERE a = ? ORDER BY rowid" 49*8210233cSdrh {SEARCH t1 USING INDEX i1 (a=?)} 50*8210233cSdrh {SEARCH t1 USING INDEX i1 (a=?)*USE TEMP B-TREE FOR ORDER BY} 5166f0dcb7Sdan 4 "SELECT max(a) FROM t1" 52*8210233cSdrh {SEARCH t1 USING COVERING INDEX i1} 53*8210233cSdrh {SEARCH t1} 5466f0dcb7Sdan 5 "SELECT group_concat(b) FROM t1 GROUP BY a" 55*8210233cSdrh {SCAN t1 USING INDEX i1} 56*8210233cSdrh {SCAN t1*USE TEMP B-TREE FOR GROUP BY} 5766f0dcb7Sdan 5866f0dcb7Sdan 6 "SELECT * FROM t1 WHERE a = ?" 59*8210233cSdrh {SEARCH t1 USING INDEX i1 (a=?)} 60*8210233cSdrh {SEARCH t1 USING INDEX i1 (a=?)} 613e9548b3Sdrh 7 "SELECT count(*) FROM t1" 62*8210233cSdrh {SCAN t1 USING COVERING INDEX i1} 63*8210233cSdrh {SCAN t1} 6466f0dcb7Sdan } { 6566f0dcb7Sdan do_eqp_test 1.$idxmode.$tn $sql $r($idxmode) 6666f0dcb7Sdan } 6766f0dcb7Sdan} 6866f0dcb7Sdan 6966f0dcb7Sdanfinish_test 70