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.1.1.1 2012/02/21 17:04:16 misho 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 <= 6} {incr ii} {
198:
199: switch $ii {
200: 4 {
201: optimization_control db query-flattener off
202: }
203: 5 {
204: optimization_control db query-flattener on
205: do_test selectB-5.0 {
206: execsql {
207: CREATE INDEX i1 ON t1(a);
208: CREATE INDEX i2 ON t1(b);
209: CREATE INDEX i3 ON t1(c);
210: CREATE INDEX i4 ON t2(d);
211: CREATE INDEX i5 ON t2(e);
212: CREATE INDEX i6 ON t2(f);
213: }
214: } {}
215: }
216: 6 {
217: optimization_control db query-flattener off
218: }
219: }
220:
221: do_test selectB-$ii.1 {
222: execsql {
223: SELECT DISTINCT * FROM
224: (SELECT c FROM t1 UNION ALL SELECT e FROM t2)
225: ORDER BY 1;
226: }
227: } {6 12 15 18 24}
228:
229: do_test selectB-$ii.2 {
230: execsql {
231: SELECT c, count(*) FROM
232: (SELECT c FROM t1 UNION ALL SELECT e FROM t2)
233: GROUP BY c ORDER BY 1;
234: }
235: } {6 2 12 1 15 1 18 1 24 1}
236: do_test selectB-$ii.3 {
237: execsql {
238: SELECT c, count(*) FROM
239: (SELECT c FROM t1 UNION ALL SELECT e FROM t2)
240: GROUP BY c HAVING count(*)>1;
241: }
242: } {6 2}
243: do_test selectB-$ii.4 {
244: execsql {
245: SELECT t4.c, t3.a FROM
246: (SELECT c FROM t1 UNION ALL SELECT e FROM t2) AS t4, t1 AS t3
247: WHERE t3.a=14
248: ORDER BY 1
249: }
250: } {6 14 6 14 12 14 15 14 18 14 24 14}
251:
252: do_test selectB-$ii.5 {
253: execsql {
254: SELECT d FROM t2
255: EXCEPT
256: SELECT a FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
257: }
258: } {}
259: do_test selectB-$ii.6 {
260: execsql {
261: SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
262: EXCEPT
263: SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
264: }
265: } {}
266: do_test selectB-$ii.7 {
267: execsql {
268: SELECT c FROM t1
269: EXCEPT
270: SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
271: }
272: } {12}
273: do_test selectB-$ii.8 {
274: execsql {
275: SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
276: EXCEPT
277: SELECT c FROM t1
278: }
279: } {9 15 24 27}
280: do_test selectB-$ii.9 {
281: execsql {
282: SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
283: EXCEPT
284: SELECT c FROM t1
285: ORDER BY c DESC
286: }
287: } {27 24 15 9}
288:
289: do_test selectB-$ii.10 {
290: execsql {
291: SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
292: UNION
293: SELECT c FROM t1
294: ORDER BY c DESC
295: }
296: } {27 24 18 15 12 9 6}
297: do_test selectB-$ii.11 {
298: execsql {
299: SELECT c FROM t1
300: UNION
301: SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
302: ORDER BY c
303: }
304: } {6 9 12 15 18 24 27}
305: do_test selectB-$ii.12 {
306: execsql {
307: SELECT c FROM t1 UNION SELECT e FROM t2 UNION ALL SELECT f FROM t2
308: ORDER BY c
309: }
310: } {6 9 12 15 18 18 24 27}
311: do_test selectB-$ii.13 {
312: execsql {
313: SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
314: UNION
315: SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
316: ORDER BY 1
317: }
318: } {6 9 15 18 24 27}
319:
320: do_test selectB-$ii.14 {
321: execsql {
322: SELECT c FROM t1
323: INTERSECT
324: SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
325: ORDER BY 1
326: }
327: } {6 18}
328: do_test selectB-$ii.15 {
329: execsql {
330: SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
331: INTERSECT
332: SELECT c FROM t1
333: ORDER BY 1
334: }
335: } {6 18}
336: do_test selectB-$ii.16 {
337: execsql {
338: SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
339: INTERSECT
340: SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
341: ORDER BY 1
342: }
343: } {6 9 15 18 24 27}
344:
345: do_test selectB-$ii.17 {
346: execsql {
347: SELECT * FROM (
348: SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 4
349: ) LIMIT 2
350: }
351: } {2 8}
352:
353: do_test selectB-$ii.18 {
354: execsql {
355: SELECT * FROM (
356: SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 4 OFFSET 2
357: ) LIMIT 2
358: }
359: } {14 3}
360:
361: do_test selectB-$ii.19 {
362: execsql {
363: SELECT * FROM (
364: SELECT DISTINCT (a/10) FROM t1 UNION ALL SELECT DISTINCT(d%2) FROM t2
365: )
366: }
367: } {0 1 1 0}
368:
369: do_test selectB-$ii.20 {
370: execsql {
371: SELECT DISTINCT * FROM (
372: SELECT DISTINCT (a/10) FROM t1 UNION ALL SELECT DISTINCT(d%2) FROM t2
373: )
374: }
375: } {0 1}
376:
377: do_test selectB-$ii.21 {
378: execsql {
379: SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2) ORDER BY a+b
380: }
381: } {2 4 6 3 6 9 8 10 12 12 15 18 14 16 18 21 24 27}
382:
383: do_test selectB-$ii.22 {
384: execsql {
385: SELECT * FROM (SELECT 345 UNION ALL SELECT d FROM t2) ORDER BY 1;
386: }
387: } {3 12 21 345}
388:
389: do_test selectB-$ii.23 {
390: execsql {
391: SELECT x, y FROM (
392: SELECT a AS x, b AS y FROM t1
393: UNION ALL
394: SELECT a*10 + 0.1, f*10 + 0.1 FROM t1 JOIN t2 ON (c=d)
395: UNION ALL
396: SELECT a*100, b*100 FROM t1
397: ) ORDER BY 1;
398: }
399: } {2 4 8 10 14 16 80.1 180.1 200 400 800 1000 1400 1600}
400:
401: do_test selectB-$ii.24 {
402: execsql {
403: SELECT x, y FROM (
404: SELECT a AS x, b AS y FROM t1
405: UNION ALL
406: SELECT a*10 + 0.1, f*10 + 0.1 FROM t1 LEFT JOIN t2 ON (c=d)
407: UNION ALL
408: SELECT a*100, b*100 FROM t1
409: ) ORDER BY 1;
410: }
411: } {2 4 8 10 14 16 20.1 {} 80.1 180.1 140.1 {} 200 400 800 1000 1400 1600}
412:
413: do_test selectB-$ii.25 {
414: execsql {
415: SELECT x+y FROM (
416: SELECT a AS x, b AS y FROM t1
417: UNION ALL
418: SELECT a*10 + 0.1, f*10 + 0.1 FROM t1 LEFT JOIN t2 ON (c=d)
419: UNION ALL
420: SELECT a*100, b*100 FROM t1
421: ) WHERE y+x NOT NULL ORDER BY 1;
422: }
423: } {6 18 30 260.2 600 1800 3000}
424: }
425:
426: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to selectB.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