Annotation of embedaddon/sqlite3/test/select2.test, revision 1.1
1.1 ! misho 1: # 2001 September 15
! 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: # This file implements regression tests for SQLite library. The
! 12: # focus of this file is testing the SELECT statement.
! 13: #
! 14: # $Id: select2.test,v 1.28 2009/01/15 15:23:59 drh Exp $
! 15:
! 16: set testdir [file dirname $argv0]
! 17: source $testdir/tester.tcl
! 18:
! 19: # Create a table with some data
! 20: #
! 21: execsql {CREATE TABLE tbl1(f1 int, f2 int)}
! 22: execsql {BEGIN}
! 23: for {set i 0} {$i<=30} {incr i} {
! 24: execsql "INSERT INTO tbl1 VALUES([expr {$i%9}],[expr {$i%10}])"
! 25: }
! 26: execsql {COMMIT}
! 27:
! 28: # Do a second query inside a first.
! 29: #
! 30: do_test select2-1.1 {
! 31: set sql {SELECT DISTINCT f1 FROM tbl1 ORDER BY f1}
! 32: set r {}
! 33: catch {unset data}
! 34: db eval $sql data {
! 35: set f1 $data(f1)
! 36: lappend r $f1:
! 37: set sql2 "SELECT f2 FROM tbl1 WHERE f1=$f1 ORDER BY f2"
! 38: db eval $sql2 d2 {
! 39: lappend r $d2(f2)
! 40: }
! 41: }
! 42: set r
! 43: } {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}
! 44:
! 45: do_test select2-1.2 {
! 46: set sql {SELECT DISTINCT f1 FROM tbl1 WHERE f1>3 AND f1<5}
! 47: set r {}
! 48: db eval $sql data {
! 49: set f1 $data(f1)
! 50: lappend r $f1:
! 51: set sql2 "SELECT f2 FROM tbl1 WHERE f1=$f1 ORDER BY f2"
! 52: db eval $sql2 d2 {
! 53: lappend r $d2(f2)
! 54: }
! 55: }
! 56: set r
! 57: } {4: 2 3 4}
! 58: unset data
! 59:
! 60: # Create a largish table. Do this twice, once using the TCL cache and once
! 61: # without. Compare the performance to make sure things go faster with the
! 62: # cache turned on.
! 63: #
! 64: ifcapable tclvar {
! 65: do_test select2-2.0.1 {
! 66: set t1 [time {
! 67: execsql {CREATE TABLE tbl2(f1 int, f2 int, f3 int); BEGIN;}
! 68: for {set i 1} {$i<=30000} {incr i} {
! 69: set i2 [expr {$i*2}]
! 70: set i3 [expr {$i*3}]
! 71: db eval {INSERT INTO tbl2 VALUES($i,$i2,$i3)}
! 72: }
! 73: execsql {COMMIT}
! 74: }]
! 75: list
! 76: } {}
! 77: puts "time with cache: $::t1"
! 78: }
! 79: catch {execsql {DROP TABLE tbl2}}
! 80: do_test select2-2.0.2 {
! 81: set t2 [time {
! 82: execsql {CREATE TABLE tbl2(f1 int, f2 int, f3 int); BEGIN;}
! 83: for {set i 1} {$i<=30000} {incr i} {
! 84: set i2 [expr {$i*2}]
! 85: set i3 [expr {$i*3}]
! 86: execsql "INSERT INTO tbl2 VALUES($i,$i2,$i3)"
! 87: }
! 88: execsql {COMMIT}
! 89: }]
! 90: list
! 91: } {}
! 92: puts "time without cache: $t2"
! 93: #ifcapable tclvar {
! 94: # do_test select2-2.0.3 {
! 95: # expr {[lindex $t1 0]<[lindex $t2 0]}
! 96: # } 1
! 97: #}
! 98:
! 99: do_test select2-2.1 {
! 100: execsql {SELECT count(*) FROM tbl2}
! 101: } {30000}
! 102: do_test select2-2.2 {
! 103: execsql {SELECT count(*) FROM tbl2 WHERE f2>1000}
! 104: } {29500}
! 105:
! 106: do_test select2-3.1 {
! 107: execsql {SELECT f1 FROM tbl2 WHERE 1000=f2}
! 108: } {500}
! 109:
! 110: do_test select2-3.2a {
! 111: execsql {CREATE INDEX idx1 ON tbl2(f2)}
! 112: } {}
! 113: do_test select2-3.2b {
! 114: execsql {SELECT f1 FROM tbl2 WHERE 1000=f2}
! 115: } {500}
! 116: do_test select2-3.2c {
! 117: execsql {SELECT f1 FROM tbl2 WHERE f2=1000}
! 118: } {500}
! 119: do_test select2-3.2d {
! 120: set sqlite_search_count 0
! 121: execsql {SELECT * FROM tbl2 WHERE 1000=f2}
! 122: set sqlite_search_count
! 123: } {3}
! 124: do_test select2-3.2e {
! 125: set sqlite_search_count 0
! 126: execsql {SELECT * FROM tbl2 WHERE f2=1000}
! 127: set sqlite_search_count
! 128: } {3}
! 129:
! 130: # Make sure queries run faster with an index than without
! 131: #
! 132: do_test select2-3.3 {
! 133: execsql {DROP INDEX idx1}
! 134: set sqlite_search_count 0
! 135: execsql {SELECT f1 FROM tbl2 WHERE f2==2000}
! 136: set sqlite_search_count
! 137: } {29999}
! 138:
! 139: # Make sure we can optimize functions in the WHERE clause that
! 140: # use fields from two or more different table. (Bug #6)
! 141: #
! 142: do_test select2-4.1 {
! 143: execsql {
! 144: CREATE TABLE aa(a);
! 145: CREATE TABLE bb(b);
! 146: INSERT INTO aa VALUES(1);
! 147: INSERT INTO aa VALUES(3);
! 148: INSERT INTO bb VALUES(2);
! 149: INSERT INTO bb VALUES(4);
! 150: SELECT * FROM aa, bb WHERE max(a,b)>2;
! 151: }
! 152: } {1 4 3 2 3 4}
! 153: do_test select2-4.2 {
! 154: execsql {
! 155: INSERT INTO bb VALUES(0);
! 156: SELECT * FROM aa CROSS JOIN bb WHERE b;
! 157: }
! 158: } {1 2 1 4 3 2 3 4}
! 159: do_test select2-4.3 {
! 160: execsql {
! 161: SELECT * FROM aa CROSS JOIN bb WHERE NOT b;
! 162: }
! 163: } {1 0 3 0}
! 164: do_test select2-4.4 {
! 165: execsql {
! 166: SELECT * FROM aa, bb WHERE min(a,b);
! 167: }
! 168: } {1 2 1 4 3 2 3 4}
! 169: do_test select2-4.5 {
! 170: execsql {
! 171: SELECT * FROM aa, bb WHERE NOT min(a,b);
! 172: }
! 173: } {1 0 3 0}
! 174: do_test select2-4.6 {
! 175: execsql {
! 176: SELECT * FROM aa, bb WHERE CASE WHEN a=b-1 THEN 1 END;
! 177: }
! 178: } {1 2 3 4}
! 179: do_test select2-4.7 {
! 180: execsql {
! 181: SELECT * FROM aa, bb WHERE CASE WHEN a=b-1 THEN 0 ELSE 1 END;
! 182: }
! 183: } {1 4 1 0 3 2 3 0}
! 184:
! 185: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to select2.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test