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.5 2008/07/12 14:52:20 drh Exp $ 12 13set testdir [file dirname $argv0] 14source $testdir/tester.tcl 15 16# Do not use a codec for tests in this file, as the database file is 17# manipulated directly using tcl scripts (using the [hexio_write] command). 18# 19do_not_use_codec 20 21# Do an SQL statement. Append the search count to the end of the result. 22# 23proc count sql { 24 set ::sqlite_search_count 0 25 return [concat [execsql $sql] $::sqlite_search_count] 26} 27 28# This procedure sets the value of the file-format in file 'test.db' 29# to $newval. Also, the schema cookie is incremented. 30# 31proc set_file_format {newval} { 32 hexio_write test.db 44 [hexio_render_int32 $newval] 33 set schemacookie [hexio_get_int [hexio_read test.db 40 4]] 34 incr schemacookie 35 hexio_write test.db 40 [hexio_render_int32 $schemacookie] 36 return {} 37} 38 39do_test minmax3-1.0 { 40 execsql { 41 CREATE TABLE t1(x, y, z); 42 } 43 db close 44 set_file_format 4 45 sqlite3 db test.db 46 execsql { 47 BEGIN; 48 INSERT INTO t1 VALUES('1', 'I', 'one'); 49 INSERT INTO t1 VALUES('2', 'IV', 'four'); 50 INSERT INTO t1 VALUES('2', NULL, 'three'); 51 INSERT INTO t1 VALUES('2', 'II', 'two'); 52 INSERT INTO t1 VALUES('2', 'V', 'five'); 53 INSERT INTO t1 VALUES('3', 'VI', 'six'); 54 COMMIT; 55 } 56} {} 57do_test minmax3-1.1.1 { 58 # Linear scan. 59 count { SELECT max(y) FROM t1 WHERE x = '2'; } 60} {V 5} 61do_test minmax3-1.1.2 { 62 # Index optimizes the WHERE x='2' constraint. 63 execsql { CREATE INDEX i1 ON t1(x) } 64 count { SELECT max(y) FROM t1 WHERE x = '2'; } 65} {V 9} 66do_test minmax3-1.1.3 { 67 # Index optimizes the WHERE x='2' constraint and the MAX(y). 68 execsql { CREATE INDEX i2 ON t1(x,y) } 69 count { SELECT max(y) FROM t1 WHERE x = '2'; } 70} {V 1} 71do_test minmax3-1.1.4 { 72 # Index optimizes the WHERE x='2' constraint and the MAX(y). 73 execsql { DROP INDEX i2 ; CREATE INDEX i2 ON t1(x, y DESC) } 74 count { SELECT max(y) FROM t1 WHERE x = '2'; } 75} {V 1} 76do_test minmax3-1.1.5 { 77 count { SELECT max(y) FROM t1 WHERE x = '2' AND y != 'V'; } 78} {IV 2} 79do_test minmax3-1.1.6 { 80 count { SELECT max(y) FROM t1 WHERE x = '2' AND y < 'V'; } 81} {IV 1} 82do_test minmax3-1.1.6 { 83 count { SELECT max(y) FROM t1 WHERE x = '2' AND z != 'five'; } 84} {IV 4} 85 86do_test minmax3-1.2.1 { 87 # Linear scan of t1. 88 execsql { DROP INDEX i1 ; DROP INDEX i2 } 89 count { SELECT min(y) FROM t1 WHERE x = '2'; } 90} {II 5} 91do_test minmax3-1.2.2 { 92 # Index i1 optimizes the WHERE x='2' constraint. 93 execsql { CREATE INDEX i1 ON t1(x) } 94 count { SELECT min(y) FROM t1 WHERE x = '2'; } 95} {II 9} 96do_test minmax3-1.2.3 { 97 # Index i2 optimizes the WHERE x='2' constraint and the min(y). 98 execsql { CREATE INDEX i2 ON t1(x,y) } 99 count { SELECT min(y) FROM t1 WHERE x = '2'; } 100} {II 1} 101do_test minmax3-1.2.4 { 102 # Index optimizes the WHERE x='2' constraint and the MAX(y). 103 execsql { DROP INDEX i2 ; CREATE INDEX i2 ON t1(x, y DESC) } 104 count { SELECT min(y) FROM t1 WHERE x = '2'; } 105} {II 1} 106 107do_test minmax3-1.3.1 { 108 # Linear scan 109 execsql { DROP INDEX i1 ; DROP INDEX i2 } 110 count { SELECT min(y) FROM t1; } 111} {I 5} 112do_test minmax3-1.3.2 { 113 # Index i1 optimizes the min(y) 114 execsql { CREATE INDEX i1 ON t1(y) } 115 count { SELECT min(y) FROM t1; } 116} {I 1} 117do_test minmax3-1.3.3 { 118 # Index i1 optimizes the min(y) 119 execsql { DROP INDEX i1 ; CREATE INDEX i1 ON t1(y DESC) } 120 count { SELECT min(y) FROM t1; } 121} {I 1} 122 123do_test minmax3-1.4.1 { 124 # Linear scan 125 execsql { DROP INDEX i1 } 126 count { SELECT max(y) FROM t1; } 127} {VI 5} 128do_test minmax3-1.4.2 { 129 # Index i1 optimizes the max(y) 130 execsql { CREATE INDEX i1 ON t1(y) } 131 count { SELECT max(y) FROM t1; } 132} {VI 0} 133do_test minmax3-1.4.3 { 134 # Index i1 optimizes the max(y) 135 execsql { DROP INDEX i1 ; CREATE INDEX i1 ON t1(y DESC) } 136 execsql { SELECT y from t1} 137 count { SELECT max(y) FROM t1; } 138} {VI 0} 139do_test minmax3-1.4.4 { 140 execsql { DROP INDEX i1 } 141} {} 142 143do_test minmax3-2.1 { 144 execsql { 145 CREATE TABLE t2(a, b); 146 CREATE INDEX i3 ON t2(a, b); 147 INSERT INTO t2 VALUES(1, NULL); 148 INSERT INTO t2 VALUES(1, 1); 149 INSERT INTO t2 VALUES(1, 2); 150 INSERT INTO t2 VALUES(1, 3); 151 INSERT INTO t2 VALUES(2, NULL); 152 INSERT INTO t2 VALUES(2, 1); 153 INSERT INTO t2 VALUES(2, 2); 154 INSERT INTO t2 VALUES(2, 3); 155 INSERT INTO t2 VALUES(3, 1); 156 INSERT INTO t2 VALUES(3, 2); 157 INSERT INTO t2 VALUES(3, 3); 158 } 159} {} 160do_test minmax3-2.2 { 161 execsql { SELECT min(b) FROM t2 WHERE a = 1; } 162} {1} 163do_test minmax3-2.3 { 164 execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b>1; } 165} {2} 166do_test minmax3-2.4 { 167 execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b>-1; } 168} {1} 169do_test minmax3-2.5 { 170 execsql { SELECT min(b) FROM t2 WHERE a = 1; } 171} {1} 172do_test minmax3-2.6 { 173 execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b<2; } 174} {1} 175do_test minmax3-2.7 { 176 execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b<1; } 177} {{}} 178do_test minmax3-2.8 { 179 execsql { SELECT min(b) FROM t2 WHERE a = 3 AND b<1; } 180} {{}} 181 182do_test minmax3-3.1 { 183 execsql { 184 DROP TABLE t2; 185 CREATE TABLE t2(a, b); 186 CREATE INDEX i3 ON t2(a, b DESC); 187 INSERT INTO t2 VALUES(1, NULL); 188 INSERT INTO t2 VALUES(1, 1); 189 INSERT INTO t2 VALUES(1, 2); 190 INSERT INTO t2 VALUES(1, 3); 191 INSERT INTO t2 VALUES(2, NULL); 192 INSERT INTO t2 VALUES(2, 1); 193 INSERT INTO t2 VALUES(2, 2); 194 INSERT INTO t2 VALUES(2, 3); 195 INSERT INTO t2 VALUES(3, 1); 196 INSERT INTO t2 VALUES(3, 2); 197 INSERT INTO t2 VALUES(3, 3); 198 } 199} {} 200do_test minmax3-3.2 { 201 execsql { SELECT min(b) FROM t2 WHERE a = 1; } 202} {1} 203do_test minmax3-3.3 { 204 execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b>1; } 205} {2} 206do_test minmax3-3.4 { 207 execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b>-1; } 208} {1} 209do_test minmax3-3.5 { 210 execsql { SELECT min(b) FROM t2 WHERE a = 1; } 211} {1} 212do_test minmax3-3.6 { 213 execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b<2; } 214} {1} 215do_test minmax3-3.7 { 216 execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b<1; } 217} {{}} 218do_test minmax3-3.8 { 219 execsql { SELECT min(b) FROM t2 WHERE a = 3 AND b<1; } 220} {{}} 221 222do_test minmax3-4.1 { 223 execsql { 224 CREATE TABLE t4(x); 225 INSERT INTO t4 VALUES('abc'); 226 INSERT INTO t4 VALUES('BCD'); 227 SELECT max(x) FROM t4; 228 } 229} {abc} 230do_test minmax3-4.2 { 231 execsql { 232 SELECT max(x COLLATE nocase) FROM t4; 233 } 234} {BCD} 235do_test minmax3-4.3 { 236 execsql { 237 SELECT max(x), max(x COLLATE nocase) FROM t4; 238 } 239} {abc BCD} 240do_test minmax3-4.4 { 241 execsql { 242 SELECT max(x COLLATE binary), max(x COLLATE nocase) FROM t4; 243 } 244} {abc BCD} 245do_test minmax3-4.5 { 246 execsql { 247 SELECT max(x COLLATE nocase), max(x COLLATE rtrim) FROM t4; 248 } 249} {BCD abc} 250do_test minmax3-4.6 { 251 execsql { 252 SELECT max(x COLLATE nocase), max(x) FROM t4; 253 } 254} {BCD abc} 255do_test minmax3-4.10 { 256 execsql { 257 SELECT min(x) FROM t4; 258 } 259} {BCD} 260do_test minmax3-4.11 { 261 execsql { 262 SELECT min(x COLLATE nocase) FROM t4; 263 } 264} {abc} 265do_test minmax3-4.12 { 266 execsql { 267 SELECT min(x), min(x COLLATE nocase) FROM t4; 268 } 269} {BCD abc} 270do_test minmax3-4.13 { 271 execsql { 272 SELECT min(x COLLATE binary), min(x COLLATE nocase) FROM t4; 273 } 274} {BCD abc} 275do_test minmax3-4.14 { 276 execsql { 277 SELECT min(x COLLATE nocase), min(x COLLATE rtrim) FROM t4; 278 } 279} {abc BCD} 280do_test minmax3-4.15 { 281 execsql { 282 SELECT min(x COLLATE nocase), min(x) FROM t4; 283 } 284} {abc BCD} 285 286 287finish_test 288