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