| blob | 8fb34a21564c4c386b8130b4cfd97298720c8051 |
2 #pragma ident "%Z%%M% %I% %E% SMI"
4 # 2001 September 15
5 #
6 # The author disclaims copyright to this source code. In place of
7 # a legal notice, here is a blessing:
8 #
9 # May you do good and not evil.
10 # May you find forgiveness for yourself and forgive others.
11 # May you share freely, never taking more than you give.
12 #
13 #***********************************************************************
14 # This file implements regression tests for SQLite library. The
15 # focus of this file is testing UNION, INTERSECT and EXCEPT operators
16 # in SELECT statements.
17 #
18 # $Id: select4.test,v 1.13 2003/02/02 12:41:27 drh Exp $
20 set testdir [file dirname $argv0]
21 source $testdir/tester.tcl
23 # Build some test data
24 #
25 set fd [open data1.txt w]
26 for {set i 1} {$i<32} {incr i} {
27 for {set j 0} {pow(2,$j)<$i} {incr j} {}
28 puts $fd "$i\t$j"
29 }
30 close $fd
31 execsql {
32 CREATE TABLE t1(n int, log int);
33 COPY t1 FROM 'data1.txt'
34 }
35 file delete data1.txt
37 do_test select4-1.0 {
38 execsql {SELECT DISTINCT log FROM t1 ORDER BY log}
39 } {0 1 2 3 4 5}
41 # Union All operator
42 #
43 do_test select4-1.1a {
44 lsort [execsql {SELECT DISTINCT log FROM t1}]
45 } {0 1 2 3 4 5}
46 do_test select4-1.1b {
47 lsort [execsql {SELECT n FROM t1 WHERE log=3}]
48 } {5 6 7 8}
49 do_test select4-1.1c {
50 execsql {
51 SELECT DISTINCT log FROM t1
52 UNION ALL
53 SELECT n FROM t1 WHERE log=3
54 ORDER BY log;
55 }
56 } {0 1 2 3 4 5 5 6 7 8}
57 do_test select4-1.1d {
58 execsql {
59 CREATE TABLE t2 AS
60 SELECT DISTINCT log FROM t1
61 UNION ALL
62 SELECT n FROM t1 WHERE log=3
63 ORDER BY log;
64 SELECT * FROM t2;
65 }
66 } {0 1 2 3 4 5 5 6 7 8}
67 execsql {DROP TABLE t2}
68 do_test select4-1.1e {
69 execsql {
70 CREATE TABLE t2 AS
71 SELECT DISTINCT log FROM t1
72 UNION ALL
73 SELECT n FROM t1 WHERE log=3
74 ORDER BY log DESC;
75 SELECT * FROM t2;
76 }
77 } {8 7 6 5 5 4 3 2 1 0}
78 execsql {DROP TABLE t2}
79 do_test select4-1.1f {
80 execsql {
81 SELECT DISTINCT log FROM t1
82 UNION ALL
83 SELECT n FROM t1 WHERE log=2
84 }
85 } {0 1 2 3 4 5 3 4}
86 do_test select4-1.1g {
87 execsql {
88 CREATE TABLE t2 AS
89 SELECT DISTINCT log FROM t1
90 UNION ALL
91 SELECT n FROM t1 WHERE log=2;
92 SELECT * FROM t2;
93 }
94 } {0 1 2 3 4 5 3 4}
95 execsql {DROP TABLE t2}
96 do_test select4-1.2 {
97 execsql {
98 SELECT log FROM t1 WHERE n IN
99 (SELECT DISTINCT log FROM t1 UNION ALL
100 SELECT n FROM t1 WHERE log=3)
101 ORDER BY log;
102 }
103 } {0 1 2 2 3 3 3 3}
104 do_test select4-1.3 {
105 set v [catch {execsql {
106 SELECT DISTINCT log FROM t1 ORDER BY log
107 UNION ALL
108 SELECT n FROM t1 WHERE log=3
109 ORDER BY log;
110 }} msg]
111 lappend v $msg
112 } {1 {ORDER BY clause should come after UNION ALL not before}}
114 # Union operator
115 #
116 do_test select4-2.1 {
117 execsql {
118 SELECT DISTINCT log FROM t1
119 UNION
120 SELECT n FROM t1 WHERE log=3
121 ORDER BY log;
122 }
123 } {0 1 2 3 4 5 6 7 8}
124 do_test select4-2.2 {
125 execsql {
126 SELECT log FROM t1 WHERE n IN
127 (SELECT DISTINCT log FROM t1 UNION
128 SELECT n FROM t1 WHERE log=3)
129 ORDER BY log;
130 }
131 } {0 1 2 2 3 3 3 3}
132 do_test select4-2.3 {
133 set v [catch {execsql {
134 SELECT DISTINCT log FROM t1 ORDER BY log
135 UNION
136 SELECT n FROM t1 WHERE log=3
137 ORDER BY log;
138 }} msg]
139 lappend v $msg
140 } {1 {ORDER BY clause should come after UNION not before}}
142 # Except operator
143 #
144 do_test select4-3.1.1 {
145 execsql {
146 SELECT DISTINCT log FROM t1
147 EXCEPT
148 SELECT n FROM t1 WHERE log=3
149 ORDER BY log;
150 }
151 } {0 1 2 3 4}
152 do_test select4-3.1.2 {
153 execsql {
154 CREATE TABLE t2 AS
155 SELECT DISTINCT log FROM t1
156 EXCEPT
157 SELECT n FROM t1 WHERE log=3
158 ORDER BY log;
159 SELECT * FROM t2;
160 }
161 } {0 1 2 3 4}
162 execsql {DROP TABLE t2}
163 do_test select4-3.1.3 {
164 execsql {
165 CREATE TABLE t2 AS
166 SELECT DISTINCT log FROM t1
167 EXCEPT
168 SELECT n FROM t1 WHERE log=3
169 ORDER BY log DESC;
170 SELECT * FROM t2;
171 }
172 } {4 3 2 1 0}
173 execsql {DROP TABLE t2}
174 do_test select4-3.2 {
175 execsql {
176 SELECT log FROM t1 WHERE n IN
177 (SELECT DISTINCT log FROM t1 EXCEPT
178 SELECT n FROM t1 WHERE log=3)
179 ORDER BY log;
180 }
181 } {0 1 2 2}
182 do_test select4-3.3 {
183 set v [catch {execsql {
184 SELECT DISTINCT log FROM t1 ORDER BY log
185 EXCEPT
186 SELECT n FROM t1 WHERE log=3
187 ORDER BY log;
188 }} msg]
189 lappend v $msg
190 } {1 {ORDER BY clause should come after EXCEPT not before}}
192 # Intersect operator
193 #
194 do_test select4-4.1.1 {
195 execsql {
196 SELECT DISTINCT log FROM t1
197 INTERSECT
198 SELECT n FROM t1 WHERE log=3
199 ORDER BY log;
200 }
201 } {5}
202 do_test select4-4.1.2 {
203 execsql {
204 SELECT DISTINCT log FROM t1 UNION ALL SELECT 6
205 INTERSECT
206 SELECT n FROM t1 WHERE log=3
207 ORDER BY log;
208 }
209 } {5 6}
210 do_test select4-4.1.3 {
211 execsql {
212 CREATE TABLE t2 AS
213 SELECT DISTINCT log FROM t1 UNION ALL SELECT 6
214 INTERSECT
215 SELECT n FROM t1 WHERE log=3
216 ORDER BY log;
217 SELECT * FROM t2;
218 }
219 } {5 6}
220 execsql {DROP TABLE t2}
221 do_test select4-4.1.4 {
222 execsql {
223 CREATE TABLE t2 AS
224 SELECT DISTINCT log FROM t1 UNION ALL SELECT 6
225 INTERSECT
226 SELECT n FROM t1 WHERE log=3
227 ORDER BY log DESC;
228 SELECT * FROM t2;
229 }
230 } {6 5}
231 execsql {DROP TABLE t2}
232 do_test select4-4.2 {
233 execsql {
234 SELECT log FROM t1 WHERE n IN
235 (SELECT DISTINCT log FROM t1 INTERSECT
236 SELECT n FROM t1 WHERE log=3)
237 ORDER BY log;
238 }
239 } {3}
240 do_test select4-4.3 {
241 set v [catch {execsql {
242 SELECT DISTINCT log FROM t1 ORDER BY log
243 INTERSECT
244 SELECT n FROM t1 WHERE log=3
245 ORDER BY log;
246 }} msg]
247 lappend v $msg
248 } {1 {ORDER BY clause should come after INTERSECT not before}}
250 # Various error messages while processing UNION or INTERSECT
251 #
252 do_test select4-5.1 {
253 set v [catch {execsql {
254 SELECT DISTINCT log FROM t2
255 UNION ALL
256 SELECT n FROM t1 WHERE log=3
257 ORDER BY log;
258 }} msg]
259 lappend v $msg
260 } {1 {no such table: t2}}
261 do_test select4-5.2 {
262 set v [catch {execsql {
263 SELECT DISTINCT log AS "xyzzy" FROM t1
264 UNION ALL
265 SELECT n FROM t1 WHERE log=3
266 ORDER BY xyzzy;
267 }} msg]
268 lappend v $msg
269 } {0 {0 1 2 3 4 5 5 6 7 8}}
270 do_test select4-5.2b {
271 set v [catch {execsql {
272 SELECT DISTINCT log AS xyzzy FROM t1
273 UNION ALL
274 SELECT n FROM t1 WHERE log=3
275 ORDER BY 'xyzzy';
276 }} msg]
277 lappend v $msg
278 } {0 {0 1 2 3 4 5 5 6 7 8}}
279 do_test select4-5.2c {
280 set v [catch {execsql {
281 SELECT DISTINCT log FROM t1
282 UNION ALL
283 SELECT n FROM t1 WHERE log=3
284 ORDER BY 'xyzzy';
285 }} msg]
286 lappend v $msg
287 } {1 {ORDER BY term number 1 does not match any result column}}
288 do_test select4-5.2d {
289 set v [catch {execsql {
290 SELECT DISTINCT log FROM t1
291 INTERSECT
292 SELECT n FROM t1 WHERE log=3
293 ORDER BY 'xyzzy';
294 }} msg]
295 lappend v $msg
296 } {1 {ORDER BY term number 1 does not match any result column}}
297 do_test select4-5.2e {
298 set v [catch {execsql {
299 SELECT DISTINCT log FROM t1
300 UNION ALL
301 SELECT n FROM t1 WHERE log=3
302 ORDER BY n;
303 }} msg]
304 lappend v $msg
305 } {0 {0 1 2 3 4 5 5 6 7 8}}
306 do_test select4-5.2f {
307 catchsql {
308 SELECT DISTINCT log FROM t1
309 UNION ALL
310 SELECT n FROM t1 WHERE log=3
311 ORDER BY log;
312 }
313 } {0 {0 1 2 3 4 5 5 6 7 8}}
314 do_test select4-5.2g {
315 catchsql {
316 SELECT DISTINCT log FROM t1
317 UNION ALL
318 SELECT n FROM t1 WHERE log=3
319 ORDER BY 1;
320 }
321 } {0 {0 1 2 3 4 5 5 6 7 8}}
322 do_test select4-5.2h {
323 catchsql {
324 SELECT DISTINCT log FROM t1
325 UNION ALL
326 SELECT n FROM t1 WHERE log=3
327 ORDER BY 2;
328 }
329 } {1 {ORDER BY position 2 should be between 1 and 1}}
330 do_test select4-5.2i {
331 catchsql {
332 SELECT DISTINCT 1, log FROM t1
333 UNION ALL
334 SELECT 2, n FROM t1 WHERE log=3
335 ORDER BY 2, 1;
336 }
337 } {0 {1 0 1 1 1 2 1 3 1 4 1 5 2 5 2 6 2 7 2 8}}
338 do_test select4-5.2j {
339 catchsql {
340 SELECT DISTINCT 1, log FROM t1
341 UNION ALL
342 SELECT 2, n FROM t1 WHERE log=3
343 ORDER BY 1, 2 DESC;
344 }
345 } {0 {1 5 1 4 1 3 1 2 1 1 1 0 2 8 2 7 2 6 2 5}}
346 do_test select4-5.2k {
347 catchsql {
348 SELECT DISTINCT 1, log FROM t1
349 UNION ALL
350 SELECT 2, n FROM t1 WHERE log=3
351 ORDER BY n, 1;
352 }
353 } {0 {1 0 1 1 1 2 1 3 1 4 1 5 2 5 2 6 2 7 2 8}}
354 do_test select4-5.3 {
355 set v [catch {execsql {
356 SELECT DISTINCT log, n FROM t1
357 UNION ALL
358 SELECT n FROM t1 WHERE log=3
359 ORDER BY log;
360 }} msg]
361 lappend v $msg
362 } {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
363 do_test select4-5.4 {
364 set v [catch {execsql {
365 SELECT log FROM t1 WHERE n=2
366 UNION ALL
367 SELECT log FROM t1 WHERE n=3
368 UNION ALL
369 SELECT log FROM t1 WHERE n=4
370 UNION ALL
371 SELECT log FROM t1 WHERE n=5
372 ORDER BY log;
373 }} msg]
374 lappend v $msg
375 } {0 {1 2 2 3}}
377 do_test select4-6.1 {
378 execsql {
379 SELECT log, count(*) as cnt FROM t1 GROUP BY log
380 UNION
381 SELECT log, n FROM t1 WHERE n=7
382 ORDER BY cnt, log;
383 }
384 } {0 1 1 1 2 2 3 4 3 7 4 8 5 15}
385 do_test select4-6.2 {
386 execsql {
387 SELECT log, count(*) FROM t1 GROUP BY log
388 UNION
389 SELECT log, n FROM t1 WHERE n=7
390 ORDER BY count(*), log;
391 }
392 } {0 1 1 1 2 2 3 4 3 7 4 8 5 15}
394 # NULLs are indistinct for the UNION operator.
395 # Make sure the UNION operator recognizes this
396 #
397 do_test select4-6.3 {
398 execsql {
399 SELECT NULL UNION SELECT NULL UNION
400 SELECT 1 UNION SELECT 2 AS 'x'
401 ORDER BY x;
402 }
403 } {{} 1 2}
404 do_test select4-6.3.1 {
405 execsql {
406 SELECT NULL UNION ALL SELECT NULL UNION ALL
407 SELECT 1 UNION ALL SELECT 2 AS 'x'
408 ORDER BY x;
409 }
410 } {{} {} 1 2}
412 # Make sure the DISTINCT keyword treats NULLs as indistinct.
413 #
414 do_test select4-6.4 {
415 execsql {
416 SELECT * FROM (
417 SELECT NULL, 1 UNION ALL SELECT NULL, 1
418 );
419 }
420 } {{} 1 {} 1}
421 do_test select4-6.5 {
422 execsql {
423 SELECT DISTINCT * FROM (
424 SELECT NULL, 1 UNION ALL SELECT NULL, 1
425 );
426 }
427 } {{} 1}
428 do_test select4-6.6 {
429 execsql {
430 SELECT DISTINCT * FROM (
431 SELECT 1,2 UNION ALL SELECT 1,2
432 );
433 }
434 } {1 2}
436 # Test distinctness of NULL in other ways.
437 #
438 do_test select4-6.7 {
439 execsql {
440 SELECT NULL EXCEPT SELECT NULL
441 }
442 } {}
445 # Make sure column names are correct when a compound select appears as
446 # an expression in the WHERE clause.
447 #
448 do_test select4-7.1 {
449 execsql {
450 CREATE TABLE t2 AS SELECT log AS 'x', count(*) AS 'y' FROM t1 GROUP BY log;
451 SELECT * FROM t2 ORDER BY x;
452 }
453 } {0 1 1 1 2 2 3 4 4 8 5 15}
454 do_test select4-7.2 {
455 execsql2 {
456 SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 INTERSECT SELECT x FROM t2)
457 ORDER BY n
458 }
459 } {n 1 log 0 n 2 log 1 n 3 log 2 n 4 log 2 n 5 log 3}
460 do_test select4-7.3 {
461 execsql2 {
462 SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 EXCEPT SELECT x FROM t2)
463 ORDER BY n LIMIT 2
464 }
465 } {n 6 log 3 n 7 log 3}
466 do_test select4-7.4 {
467 execsql2 {
468 SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 UNION SELECT x FROM t2)
469 ORDER BY n LIMIT 2
470 }
471 } {n 1 log 0 n 2 log 1}
473 # Make sure DISTINCT works appropriately on TEXT and NUMERIC columns.
474 #
475 do_test select4-8.1 {
476 execsql {
477 BEGIN;
478 CREATE TABLE t3(a text, b float, c text);
479 INSERT INTO t3 VALUES(1, 1.1, '1.1');
480 INSERT INTO t3 VALUES(2, 1.10, '1.10');
481 INSERT INTO t3 VALUES(3, 1.10, '1.1');
482 INSERT INTO t3 VALUES(4, 1.1, '1.10');
483 INSERT INTO t3 VALUES(5, 1.2, '1.2');
484 INSERT INTO t3 VALUES(6, 1.3, '1.3');
485 COMMIT;
486 }
487 execsql {
488 SELECT DISTINCT b FROM t3 ORDER BY c;
489 }
490 } {1.1 1.2 1.3}
491 do_test select4-8.2 {
492 execsql {
493 SELECT DISTINCT c FROM t3 ORDER BY c;
494 }
495 } {1.1 1.10 1.2 1.3}
498 finish_test