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