diff options
Diffstat (limited to 'third_party/sqlite/test/minmax3.test')
| -rwxr-xr-x | third_party/sqlite/test/minmax3.test | 217 |
1 files changed, 217 insertions, 0 deletions
diff --git a/third_party/sqlite/test/minmax3.test b/third_party/sqlite/test/minmax3.test new file mode 100755 index 0000000..8e8164a --- /dev/null +++ b/third_party/sqlite/test/minmax3.test @@ -0,0 +1,217 @@ +# 2008 January 5 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# $Id: minmax3.test,v 1.5 2008/07/12 14:52:20 drh Exp $ + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +# Do an SQL statement. Append the search count to the end of the result. +# +proc count sql { + set ::sqlite_search_count 0 + return [concat [execsql $sql] $::sqlite_search_count] +} + +# This procedure sets the value of the file-format in file 'test.db' +# to $newval. Also, the schema cookie is incremented. +# +proc set_file_format {newval} { + hexio_write test.db 44 [hexio_render_int32 $newval] + set schemacookie [hexio_get_int [hexio_read test.db 40 4]] + incr schemacookie + hexio_write test.db 40 [hexio_render_int32 $schemacookie] + return {} +} + +do_test minmax3-1.0 { + execsql { + CREATE TABLE t1(x, y, z); + } + db close + set_file_format 4 + sqlite3 db test.db + execsql { + BEGIN; + INSERT INTO t1 VALUES('1', 'I', 'one'); + INSERT INTO t1 VALUES('2', 'IV', 'four'); + INSERT INTO t1 VALUES('2', NULL, 'three'); + INSERT INTO t1 VALUES('2', 'II', 'two'); + INSERT INTO t1 VALUES('2', 'V', 'five'); + INSERT INTO t1 VALUES('3', 'VI', 'six'); + COMMIT; + } +} {} +do_test minmax3-1.1.1 { + # Linear scan. + count { SELECT max(y) FROM t1 WHERE x = '2'; } +} {V 5} +do_test minmax3-1.1.2 { + # Index optimizes the WHERE x='2' constraint. + execsql { CREATE INDEX i1 ON t1(x) } + count { SELECT max(y) FROM t1 WHERE x = '2'; } +} {V 9} +do_test minmax3-1.1.3 { + # Index optimizes the WHERE x='2' constraint and the MAX(y). + execsql { CREATE INDEX i2 ON t1(x,y) } + count { SELECT max(y) FROM t1 WHERE x = '2'; } +} {V 1} +do_test minmax3-1.1.4 { + # Index optimizes the WHERE x='2' constraint and the MAX(y). + execsql { DROP INDEX i2 ; CREATE INDEX i2 ON t1(x, y DESC) } + count { SELECT max(y) FROM t1 WHERE x = '2'; } +} {V 1} +do_test minmax3-1.1.5 { + count { SELECT max(y) FROM t1 WHERE x = '2' AND y != 'V'; } +} {IV 2} +do_test minmax3-1.1.6 { + count { SELECT max(y) FROM t1 WHERE x = '2' AND y < 'V'; } +} {IV 1} +do_test minmax3-1.1.6 { + count { SELECT max(y) FROM t1 WHERE x = '2' AND z != 'five'; } +} {IV 4} + +do_test minmax3-1.2.1 { + # Linear scan of t1. + execsql { DROP INDEX i1 ; DROP INDEX i2 } + count { SELECT min(y) FROM t1 WHERE x = '2'; } +} {II 5} +do_test minmax3-1.2.2 { + # Index i1 optimizes the WHERE x='2' constraint. + execsql { CREATE INDEX i1 ON t1(x) } + count { SELECT min(y) FROM t1 WHERE x = '2'; } +} {II 9} +do_test minmax3-1.2.3 { + # Index i2 optimizes the WHERE x='2' constraint and the min(y). + execsql { CREATE INDEX i2 ON t1(x,y) } + count { SELECT min(y) FROM t1 WHERE x = '2'; } +} {II 1} +do_test minmax3-1.2.4 { + # Index optimizes the WHERE x='2' constraint and the MAX(y). + execsql { DROP INDEX i2 ; CREATE INDEX i2 ON t1(x, y DESC) } + count { SELECT min(y) FROM t1 WHERE x = '2'; } +} {II 1} + +do_test minmax3-1.3.1 { + # Linear scan + execsql { DROP INDEX i1 ; DROP INDEX i2 } + count { SELECT min(y) FROM t1; } +} {I 5} +do_test minmax3-1.3.2 { + # Index i1 optimizes the min(y) + execsql { CREATE INDEX i1 ON t1(y) } + count { SELECT min(y) FROM t1; } +} {I 1} +do_test minmax3-1.3.3 { + # Index i1 optimizes the min(y) + execsql { DROP INDEX i1 ; CREATE INDEX i1 ON t1(y DESC) } + count { SELECT min(y) FROM t1; } +} {I 1} + +do_test minmax3-1.4.1 { + # Linear scan + execsql { DROP INDEX i1 } + count { SELECT max(y) FROM t1; } +} {VI 5} +do_test minmax3-1.4.2 { + # Index i1 optimizes the max(y) + execsql { CREATE INDEX i1 ON t1(y) } + count { SELECT max(y) FROM t1; } +} {VI 0} +do_test minmax3-1.4.3 { + # Index i1 optimizes the max(y) + execsql { DROP INDEX i1 ; CREATE INDEX i1 ON t1(y DESC) } + execsql { SELECT y from t1} + count { SELECT max(y) FROM t1; } +} {VI 0} +do_test minmax3-1.4.4 { + execsql { DROP INDEX i1 } +} {} + +do_test minmax3-2.1 { + execsql { + CREATE TABLE t2(a, b); + CREATE INDEX i3 ON t2(a, b); + INSERT INTO t2 VALUES(1, NULL); + INSERT INTO t2 VALUES(1, 1); + INSERT INTO t2 VALUES(1, 2); + INSERT INTO t2 VALUES(1, 3); + INSERT INTO t2 VALUES(2, NULL); + INSERT INTO t2 VALUES(2, 1); + INSERT INTO t2 VALUES(2, 2); + INSERT INTO t2 VALUES(2, 3); + INSERT INTO t2 VALUES(3, 1); + INSERT INTO t2 VALUES(3, 2); + INSERT INTO t2 VALUES(3, 3); + } +} {} +do_test minmax3-2.2 { + execsql { SELECT min(b) FROM t2 WHERE a = 1; } +} {1} +do_test minmax3-2.3 { + execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b>1; } +} {2} +do_test minmax3-2.4 { + execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b>-1; } +} {1} +do_test minmax3-2.5 { + execsql { SELECT min(b) FROM t2 WHERE a = 1; } +} {1} +do_test minmax3-2.6 { + execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b<2; } +} {1} +do_test minmax3-2.7 { + execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b<1; } +} {{}} +do_test minmax3-2.8 { + execsql { SELECT min(b) FROM t2 WHERE a = 3 AND b<1; } +} {{}} + +do_test minmax3-2.1 { + execsql { + DROP TABLE t2; + CREATE TABLE t2(a, b); + CREATE INDEX i3 ON t2(a, b DESC); + INSERT INTO t2 VALUES(1, NULL); + INSERT INTO t2 VALUES(1, 1); + INSERT INTO t2 VALUES(1, 2); + INSERT INTO t2 VALUES(1, 3); + INSERT INTO t2 VALUES(2, NULL); + INSERT INTO t2 VALUES(2, 1); + INSERT INTO t2 VALUES(2, 2); + INSERT INTO t2 VALUES(2, 3); + INSERT INTO t2 VALUES(3, 1); + INSERT INTO t2 VALUES(3, 2); + INSERT INTO t2 VALUES(3, 3); + } +} {} +do_test minmax3-2.2 { + execsql { SELECT min(b) FROM t2 WHERE a = 1; } +} {1} +do_test minmax3-2.3 { + execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b>1; } +} {2} +do_test minmax3-2.4 { + execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b>-1; } +} {1} +do_test minmax3-2.5 { + execsql { SELECT min(b) FROM t2 WHERE a = 1; } +} {1} +do_test minmax3-2.6 { + execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b<2; } +} {1} +do_test minmax3-2.7 { + execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b<1; } +} {{}} +do_test minmax3-2.8 { + execsql { SELECT min(b) FROM t2 WHERE a = 3 AND b<1; } +} {{}} + +finish_test |