wasCSharpSQLite – Blame information for rev

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 1 # 2008 June 24
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.
12 #
13 # $Id: selectB.test,v 1.10 2009/04/02 16:59:47 drh Exp $
14  
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
17  
18 ifcapable !compound {
19 finish_test
20 return
21 }
22  
23 proc test_transform {testname sql1 sql2 results} {
24 set ::vdbe1 [list]
25 set ::vdbe2 [list]
26 db eval "explain $sql1" { lappend ::vdbe1 $opcode }
27 db eval "explain $sql2" { lappend ::vdbe2 $opcode }
28  
29 do_test $testname.transform {
30 set ::vdbe1
31 } $::vdbe2
32  
33 set ::sql1 $sql1
34 do_test $testname.sql1 {
35 execsql $::sql1
36 } $results
37  
38 set ::sql2 $sql2
39 do_test $testname.sql2 {
40 execsql $::sql2
41 } $results
42 }
43  
44 do_test selectB-1.1 {
45 execsql {
46 CREATE TABLE t1(a, b, c);
47 CREATE TABLE t2(d, e, f);
48  
49 INSERT INTO t1 VALUES( 2, 4, 6);
50 INSERT INTO t1 VALUES( 8, 10, 12);
51 INSERT INTO t1 VALUES(14, 16, 18);
52  
53 INSERT INTO t2 VALUES(3, 6, 9);
54 INSERT INTO t2 VALUES(12, 15, 18);
55 INSERT INTO t2 VALUES(21, 24, 27);
56 }
57 } {}
58  
59 for {set ii 1} {$ii <= 2} {incr ii} {
60  
61 if {$ii == 2} {
62 do_test selectB-2.1 {
63 execsql {
64 CREATE INDEX i1 ON t1(a);
65 CREATE INDEX i2 ON t2(d);
66 }
67 } {}
68 }
69  
70 test_transform selectB-$ii.2 {
71 SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
72 } {
73 SELECT a FROM t1 UNION ALL SELECT d FROM t2
74 } {2 8 14 3 12 21}
75  
76 test_transform selectB-$ii.3 {
77 SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1
78 } {
79 SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1
80 } {2 3 8 12 14 21}
81  
82 test_transform selectB-$ii.4 {
83 SELECT * FROM
84 (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
85 WHERE a>10 ORDER BY 1
86 } {
87 SELECT a FROM t1 WHERE a>10 UNION ALL SELECT d FROM t2 WHERE d>10 ORDER BY 1
88 } {12 14 21}
89  
90 test_transform selectB-$ii.5 {
91 SELECT * FROM
92 (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
93 WHERE a>10 ORDER BY a
94 } {
95 SELECT a FROM t1 WHERE a>10
96 UNION ALL
97 SELECT d FROM t2 WHERE d>10
98 ORDER BY a
99 } {12 14 21}
100  
101 test_transform selectB-$ii.6 {
102 SELECT * FROM
103 (SELECT a FROM t1 UNION ALL SELECT d FROM t2 WHERE d > 12)
104 WHERE a>10 ORDER BY a
105 } {
106 SELECT a FROM t1 WHERE a>10
107 UNION ALL
108 SELECT d FROM t2 WHERE d>12 AND d>10
109 ORDER BY a
110 } {14 21}
111  
112 test_transform selectB-$ii.7 {
113 SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1
114 LIMIT 2
115 } {
116 SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 2
117 } {2 3}
118  
119 test_transform selectB-$ii.8 {
120 SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1
121 LIMIT 2 OFFSET 3
122 } {
123 SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 2 OFFSET 3
124 } {12 14}
125  
126 test_transform selectB-$ii.9 {
127 SELECT * FROM (
128 SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
129 )
130 } {
131 SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
132 } {2 8 14 3 12 21 6 12 18}
133  
134 test_transform selectB-$ii.10 {
135 SELECT * FROM (
136 SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
137 ) ORDER BY 1
138 } {
139 SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
140 ORDER BY 1
141 } {2 3 6 8 12 12 14 18 21}
142  
143 test_transform selectB-$ii.11 {
144 SELECT * FROM (
145 SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
146 ) WHERE a>=10 ORDER BY 1 LIMIT 3
147 } {
148 SELECT a FROM t1 WHERE a>=10 UNION ALL SELECT d FROM t2 WHERE d>=10
149 UNION ALL SELECT c FROM t1 WHERE c>=10
150 ORDER BY 1 LIMIT 3
151 } {12 12 14}
152  
153 test_transform selectB-$ii.12 {
154 SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 2)
155 } {
156 SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 2
157 } {2 8}
158  
159 # An ORDER BY in a compound subqueries defeats flattening. Ticket #3773
160 # test_transform selectB-$ii.13 {
161 # SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a ASC)
162 # } {
163 # SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 ASC
164 # } {2 3 8 12 14 21}
165 #
166 # test_transform selectB-$ii.14 {
167 # SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a DESC)
168 # } {
169 # SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 DESC
170 # } {21 14 12 8 3 2}
171 #
172 # test_transform selectB-$ii.14 {
173 # SELECT * FROM (
174 # SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a DESC
175 # ) LIMIT 2 OFFSET 2
176 # } {
177 # SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 DESC
178 # LIMIT 2 OFFSET 2
179 # } {12 8}
180 #
181 # test_transform selectB-$ii.15 {
182 # SELECT * FROM (
183 # SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY a ASC, e DESC
184 # )
185 # } {
186 # SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY a ASC, e DESC
187 # } {2 4 3 6 8 10 12 15 14 16 21 24}
188 }
189  
190 do_test selectB-3.0 {
191 execsql {
192 DROP INDEX i1;
193 DROP INDEX i2;
194 }
195 } {}
196  
197 for {set ii 3} {$ii <= 4} {incr ii} {
198  
199 if {$ii == 4} {
200 do_test selectB-4.0 {
201 execsql {
202 CREATE INDEX i1 ON t1(a);
203 CREATE INDEX i2 ON t1(b);
204 CREATE INDEX i3 ON t1(c);
205 CREATE INDEX i4 ON t2(d);
206 CREATE INDEX i5 ON t2(e);
207 CREATE INDEX i6 ON t2(f);
208 }
209 } {}
210 }
211  
212 do_test selectB-$ii.1 {
213 execsql {
214 SELECT DISTINCT * FROM
215 (SELECT c FROM t1 UNION ALL SELECT e FROM t2)
216 ORDER BY 1;
217 }
218 } {6 12 15 18 24}
219  
220 do_test selectB-$ii.2 {
221 execsql {
222 SELECT c, count(*) FROM
223 (SELECT c FROM t1 UNION ALL SELECT e FROM t2)
224 GROUP BY c ORDER BY 1;
225 }
226 } {6 2 12 1 15 1 18 1 24 1}
227 do_test selectB-$ii.3 {
228 execsql {
229 SELECT c, count(*) FROM
230 (SELECT c FROM t1 UNION ALL SELECT e FROM t2)
231 GROUP BY c HAVING count(*)>1;
232 }
233 } {6 2}
234 do_test selectB-$ii.4 {
235 execsql {
236 SELECT t4.c, t3.a FROM
237 (SELECT c FROM t1 UNION ALL SELECT e FROM t2) AS t4, t1 AS t3
238 WHERE t3.a=14
239 ORDER BY 1
240 }
241 } {6 14 6 14 12 14 15 14 18 14 24 14}
242  
243 do_test selectB-$ii.5 {
244 execsql {
245 SELECT d FROM t2
246 EXCEPT
247 SELECT a FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
248 }
249 } {}
250 do_test selectB-$ii.6 {
251 execsql {
252 SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
253 EXCEPT
254 SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
255 }
256 } {}
257 do_test selectB-$ii.7 {
258 execsql {
259 SELECT c FROM t1
260 EXCEPT
261 SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
262 }
263 } {12}
264 do_test selectB-$ii.8 {
265 execsql {
266 SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
267 EXCEPT
268 SELECT c FROM t1
269 }
270 } {9 15 24 27}
271 do_test selectB-$ii.9 {
272 execsql {
273 SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
274 EXCEPT
275 SELECT c FROM t1
276 ORDER BY c DESC
277 }
278 } {27 24 15 9}
279  
280 do_test selectB-$ii.10 {
281 execsql {
282 SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
283 UNION
284 SELECT c FROM t1
285 ORDER BY c DESC
286 }
287 } {27 24 18 15 12 9 6}
288 do_test selectB-$ii.11 {
289 execsql {
290 SELECT c FROM t1
291 UNION
292 SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
293 ORDER BY c
294 }
295 } {6 9 12 15 18 24 27}
296 do_test selectB-$ii.12 {
297 execsql {
298 SELECT c FROM t1 UNION SELECT e FROM t2 UNION ALL SELECT f FROM t2
299 ORDER BY c
300 }
301 } {6 9 12 15 18 18 24 27}
302 do_test selectB-$ii.13 {
303 execsql {
304 SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
305 UNION
306 SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
307 ORDER BY 1
308 }
309 } {6 9 15 18 24 27}
310  
311 do_test selectB-$ii.14 {
312 execsql {
313 SELECT c FROM t1
314 INTERSECT
315 SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
316 ORDER BY 1
317 }
318 } {6 18}
319 do_test selectB-$ii.15 {
320 execsql {
321 SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
322 INTERSECT
323 SELECT c FROM t1
324 ORDER BY 1
325 }
326 } {6 18}
327 do_test selectB-$ii.16 {
328 execsql {
329 SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
330 INTERSECT
331 SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
332 ORDER BY 1
333 }
334 } {6 9 15 18 24 27}
335  
336 do_test selectB-$ii.17 {
337 execsql {
338 SELECT * FROM (
339 SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 4
340 ) LIMIT 2
341 }
342 } {2 8}
343  
344 do_test selectB-$ii.18 {
345 execsql {
346 SELECT * FROM (
347 SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 4 OFFSET 2
348 ) LIMIT 2
349 }
350 } {14 3}
351  
352 do_test selectB-$ii.19 {
353 execsql {
354 SELECT * FROM (
355 SELECT DISTINCT (a/10) FROM t1 UNION ALL SELECT DISTINCT(d%2) FROM t2
356 )
357 }
358 } {0 1 0 1}
359  
360 do_test selectB-$ii.20 {
361 execsql {
362 SELECT DISTINCT * FROM (
363 SELECT DISTINCT (a/10) FROM t1 UNION ALL SELECT DISTINCT(d%2) FROM t2
364 )
365 }
366 } {0 1}
367  
368 do_test selectB-$ii.21 {
369 execsql {
370 SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2) ORDER BY a+b
371 }
372 } {2 4 6 3 6 9 8 10 12 12 15 18 14 16 18 21 24 27}
373  
374 do_test selectB-$ii.21 {
375 execsql {
376 SELECT * FROM (SELECT 345 UNION ALL SELECT d FROM t2) ORDER BY 1;
377 }
378 } {3 12 21 345}
379 }
380  
381 finish_test