diff options
Diffstat (limited to 'usr/src/lib/libsqlite/test/select2.test')
| -rw-r--r-- | usr/src/lib/libsqlite/test/select2.test | 165 |
1 files changed, 165 insertions, 0 deletions
diff --git a/usr/src/lib/libsqlite/test/select2.test b/usr/src/lib/libsqlite/test/select2.test new file mode 100644 index 0000000000..446f1b3e7b --- /dev/null +++ b/usr/src/lib/libsqlite/test/select2.test @@ -0,0 +1,165 @@ + +#pragma ident "%Z%%M% %I% %E% SMI" + +# 2001 September 15 +# +# 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. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. The +# focus of this file is testing the SELECT statement. +# +# $Id: select2.test,v 1.18 2002/04/02 13:26:11 drh Exp $ + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +# Create a table with some data +# +execsql {CREATE TABLE tbl1(f1 int, f2 int)} +set f [open ./testdata1.txt w] +for {set i 0} {$i<=30} {incr i} { + puts $f "[expr {$i%9}]\t[expr {$i%10}]" +} +close $f +execsql {COPY tbl1 FROM './testdata1.txt'} +file delete -force ./testdata1.txt +catch {unset data} + +# Do a second query inside a first. +# +do_test select2-1.1 { + set sql {SELECT DISTINCT f1 FROM tbl1 ORDER BY f1} + set r {} + db eval $sql data { + set f1 $data(f1) + lappend r $f1: + set sql2 "SELECT f2 FROM tbl1 WHERE f1=$f1 ORDER BY f2" + db eval $sql2 d2 { + lappend r $d2(f2) + } + } + set r +} {0: 0 7 8 9 1: 0 1 8 9 2: 0 1 2 9 3: 0 1 2 3 4: 2 3 4 5: 3 4 5 6: 4 5 6 7: 5 6 7 8: 6 7 8} + +do_test select2-1.2 { + set sql {SELECT DISTINCT f1 FROM tbl1 WHERE f1>3 AND f1<5} + set r {} + db eval $sql data { + set f1 $data(f1) + lappend r $f1: + set sql2 "SELECT f2 FROM tbl1 WHERE f1=$f1 ORDER BY f2" + db eval $sql2 d2 { + lappend r $d2(f2) + } + } + set r +} {4: 2 3 4} + +# Create a largish table +# +do_test select2-2.0 { + execsql {CREATE TABLE tbl2(f1 int, f2 int, f3 int)} + set f [open ./testdata1.txt w] + for {set i 1} {$i<=30000} {incr i} { + puts $f "$i\t[expr {$i*2}]\t[expr {$i*3}]" + } + close $f + # execsql {--vdbe-trace-on--} + execsql {COPY tbl2 FROM './testdata1.txt'} + file delete -force ./testdata1.txt +} {} + +do_test select2-2.1 { + execsql {SELECT count(*) FROM tbl2} +} {30000} +do_test select2-2.2 { + execsql {SELECT count(*) FROM tbl2 WHERE f2>1000} +} {29500} + +do_test select2-3.1 { + execsql {SELECT f1 FROM tbl2 WHERE 1000=f2} +} {500} + +do_test select2-3.2a { + execsql {CREATE INDEX idx1 ON tbl2(f2)} +} {} + +do_test select2-3.2b { + execsql {SELECT f1 FROM tbl2 WHERE 1000=f2} +} {500} +do_test select2-3.2c { + execsql {SELECT f1 FROM tbl2 WHERE f2=1000} +} {500} +do_test select2-3.2d { + set sqlite_search_count 0 + execsql {SELECT * FROM tbl2 WHERE 1000=f2} + set sqlite_search_count +} {3} +do_test select2-3.2e { + set sqlite_search_count 0 + execsql {SELECT * FROM tbl2 WHERE f2=1000} + set sqlite_search_count +} {3} + +# Make sure queries run faster with an index than without +# +do_test select2-3.3 { + execsql {DROP INDEX idx1} + set sqlite_search_count 0 + execsql {SELECT f1 FROM tbl2 WHERE f2==2000} + set sqlite_search_count +} {29999} + +# Make sure we can optimize functions in the WHERE clause that +# use fields from two or more different table. (Bug #6) +# +do_test select2-4.1 { + execsql { + CREATE TABLE aa(a); + CREATE TABLE bb(b); + INSERT INTO aa VALUES(1); + INSERT INTO aa VALUES(3); + INSERT INTO bb VALUES(2); + INSERT INTO bb VALUES(4); + SELECT * FROM aa, bb WHERE max(a,b)>2; + } +} {1 4 3 2 3 4} +do_test select2-4.2 { + execsql { + INSERT INTO bb VALUES(0); + SELECT * FROM aa, bb WHERE b; + } +} {1 2 1 4 3 2 3 4} +do_test select2-4.3 { + execsql { + SELECT * FROM aa, bb WHERE NOT b; + } +} {1 0 3 0} +do_test select2-4.4 { + execsql { + SELECT * FROM aa, bb WHERE min(a,b); + } +} {1 2 1 4 3 2 3 4} +do_test select2-4.5 { + execsql { + SELECT * FROM aa, bb WHERE NOT min(a,b); + } +} {1 0 3 0} +do_test select2-4.6 { + execsql { + SELECT * FROM aa, bb WHERE CASE WHEN a=b-1 THEN 1 END; + } +} {1 2 3 4} +do_test select2-4.7 { + execsql { + SELECT * FROM aa, bb WHERE CASE WHEN a=b-1 THEN 0 ELSE 1 END; + } +} {1 4 1 0 3 2 3 0} + +finish_test |