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