1# 2008 January 5 2# 3# The author disclaims copyright to this source code. In place of 4# a legal notice, here is a blessing: 5# 6# May you do good and not evil. 7# May you find forgiveness for yourself and forgive others. 8# May you share freely, never taking more than you give. 9# 10#*********************************************************************** 11# $Id: minmax3.test,v 1.2 2008/03/19 00:21:31 drh Exp $ 12 13set testdir [file dirname $argv0] 14source $testdir/tester.tcl 15 16# Do an SQL statement. Append the search count to the end of the result. 17# 18proc count sql { 19 set ::sqlite_search_count 0 20 return [concat [execsql $sql] $::sqlite_search_count] 21} 22 23# This procedure sets the value of the file-format in file 'test.db' 24# to $newval. Also, the schema cookie is incremented. 25# 26proc set_file_format {newval} { 27 hexio_write test.db 44 [hexio_render_int32 $newval] 28 set schemacookie [hexio_get_int [hexio_read test.db 40 4]] 29 incr schemacookie 30 hexio_write test.db 40 [hexio_render_int32 $schemacookie] 31 return {} 32} 33 34do_test minmax3-1.0 { 35 execsql { 36 CREATE TABLE t1(x, y, z); 37 } 38 db close 39 set_file_format 4 40 sqlite3 db test.db 41 execsql { 42 BEGIN; 43 INSERT INTO t1 VALUES('1', 'I', 'one'); 44 INSERT INTO t1 VALUES('2', 'IV', 'four'); 45 INSERT INTO t1 VALUES('2', NULL, 'three'); 46 INSERT INTO t1 VALUES('2', 'II', 'two'); 47 INSERT INTO t1 VALUES('2', 'V', 'five'); 48 INSERT INTO t1 VALUES('3', 'VI', 'six'); 49 COMMIT; 50 } 51} {} 52do_test minmax3-1.1.1 { 53 # Linear scan. 54 count { SELECT max(y) FROM t1 WHERE x = '2'; } 55} {V 5} 56do_test minmax3-1.1.2 { 57 # Index optimizes the WHERE x='2' constraint. 58 execsql { CREATE INDEX i1 ON t1(x) } 59 count { SELECT max(y) FROM t1 WHERE x = '2'; } 60} {V 9} 61do_test minmax3-1.1.3 { 62 # Index optimizes the WHERE x='2' constraint and the MAX(y). 63 execsql { CREATE INDEX i2 ON t1(x,y) } 64 count { SELECT max(y) FROM t1 WHERE x = '2'; } 65} {V 1} 66do_test minmax3-1.1.4 { 67 # Index optimizes the WHERE x='2' constraint and the MAX(y). 68 execsql { DROP INDEX i2 ; CREATE INDEX i2 ON t1(x, y DESC) } 69 count { SELECT max(y) FROM t1 WHERE x = '2'; } 70} {V 1} 71do_test minmax3-1.1.5 { 72 count { SELECT max(y) FROM t1 WHERE x = '2' AND y != 'V'; } 73} {IV 2} 74do_test minmax3-1.1.6 { 75 count { SELECT max(y) FROM t1 WHERE x = '2' AND y < 'V'; } 76} {IV 1} 77do_test minmax3-1.1.6 { 78 count { SELECT max(y) FROM t1 WHERE x = '2' AND z != 'five'; } 79} {IV 4} 80 81do_test minmax3-1.2.1 { 82 # Linear scan of t1. 83 execsql { DROP INDEX i1 ; DROP INDEX i2 } 84 count { SELECT min(y) FROM t1 WHERE x = '2'; } 85} {II 5} 86do_test minmax3-1.2.2 { 87 # Index i1 optimizes the WHERE x='2' constraint. 88 execsql { CREATE INDEX i1 ON t1(x) } 89 count { SELECT min(y) FROM t1 WHERE x = '2'; } 90} {II 9} 91do_test minmax3-1.2.3 { 92 # Index i2 optimizes the WHERE x='2' constraint and the min(y). 93 execsql { CREATE INDEX i2 ON t1(x,y) } 94 count { SELECT min(y) FROM t1 WHERE x = '2'; } 95} {II 1} 96do_test minmax3-1.2.4 { 97 # Index optimizes the WHERE x='2' constraint and the MAX(y). 98 execsql { DROP INDEX i2 ; CREATE INDEX i2 ON t1(x, y DESC) } 99 count { SELECT min(y) FROM t1 WHERE x = '2'; } 100} {II 1} 101 102do_test minmax3-1.3.1 { 103 # Linear scan 104 execsql { DROP INDEX i1 ; DROP INDEX i2 } 105 count { SELECT min(y) FROM t1; } 106} {I 5} 107do_test minmax3-1.3.2 { 108 # Index i1 optimizes the min(y) 109 execsql { CREATE INDEX i1 ON t1(y) } 110 count { SELECT min(y) FROM t1; } 111} {I 1} 112do_test minmax3-1.3.3 { 113 # Index i1 optimizes the min(y) 114 execsql { DROP INDEX i1 ; CREATE INDEX i1 ON t1(y DESC) } 115 count { SELECT min(y) FROM t1; } 116} {I 1} 117 118do_test minmax3-1.4.1 { 119 # Linear scan 120 execsql { DROP INDEX i1 } 121 count { SELECT max(y) FROM t1; } 122} {VI 5} 123do_test minmax3-1.4.2 { 124 # Index i1 optimizes the max(y) 125 execsql { CREATE INDEX i1 ON t1(y) } 126 count { SELECT max(y) FROM t1; } 127} {VI 0} 128do_test minmax3-1.4.3 { 129 # Index i1 optimizes the max(y) 130 execsql { DROP INDEX i1 ; CREATE INDEX i1 ON t1(y DESC) } 131 execsql { SELECT y from t1} 132 count { SELECT max(y) FROM t1; } 133} {VI 0} 134do_test minmax3-1.4.4 { 135 execsql { DROP INDEX i1 } 136} {} 137 138 139finish_test 140