blob: c90bf7c3168f8579c179c45e3c7bf2edcf17d5f1 [file] [log] [blame]
drh92dba242000-06-08 00:28:51 +00001# Copyright (c) 1999, 2000 D. Richard Hipp
2#
3# This program is free software; you can redistribute it and/or
4# modify it under the terms of the GNU General Public
5# License as published by the Free Software Foundation; either
6# version 2 of the License, or (at your option) any later version.
7#
8# This program is distributed in the hope that it will be useful,
9# but WITHOUT ANY WARRANTY; without even the implied warranty of
10# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
11# General Public License for more details.
12#
13# You should have received a copy of the GNU General Public
14# License along with this library; if not, write to the
15# Free Software Foundation, Inc., 59 Temple Place - Suite 330,
16# Boston, MA 02111-1307, USA.
17#
18# Author contact information:
19# drh@hwaci.com
20# http://www.hwaci.com/drh/
21#
22#***********************************************************************
23# This file implements regression tests for SQLite library. The
24# focus of this file is testing UNION, INTERSECT and EXCEPT operators
25# in SELECT statements.
26#
drh4cfa7932000-06-08 15:10:46 +000027# $Id: select4.test,v 1.3 2000/06/08 15:10:48 drh Exp $
drh92dba242000-06-08 00:28:51 +000028
29set testdir [file dirname $argv0]
30source $testdir/tester.tcl
31
32# Build some test data
33#
drh92cd52f2000-06-08 01:55:29 +000034set fd [open data1.txt w]
35for {set i 1} {$i<32} {incr i} {
36 for {set j 0} {pow(2,$j)<$i} {incr j} {}
37 puts $fd "$i\t$j"
38}
39close $fd
40execsql {
41 CREATE TABLE t1(n int, log int);
42 COPY t1 FROM 'data1.txt'
43}
44file delete data1.txt
45
drh92dba242000-06-08 00:28:51 +000046do_test select4-1.0 {
drh92dba242000-06-08 00:28:51 +000047 execsql {SELECT DISTINCT log FROM t1 ORDER BY log}
48} {0 1 2 3 4 5}
49
50# Union All operator
51#
52do_test select4-1.1a {
53 lsort [execsql {SELECT DISTINCT log FROM t1}]
54} {0 1 2 3 4 5}
55do_test select4-1.1b {
56 lsort [execsql {SELECT n FROM t1 WHERE log=3}]
57} {5 6 7 8}
58do_test select4-1.1c {
59 execsql {
60 SELECT DISTINCT log FROM t1
61 UNION ALL
62 SELECT n FROM t1 WHERE log=3
63 ORDER BY log;
64 }
65} {0 1 2 3 4 5 5 6 7 8}
66do_test select4-1.2 {
67 execsql {
68 SELECT log FROM t1 WHERE n IN
69 (SELECT DISTINCT log FROM t1 UNION ALL
70 SELECT n FROM t1 WHERE log=3)
71 ORDER BY log;
72 }
73} {0 1 2 2 3 3 3 3}
drh92cd52f2000-06-08 01:55:29 +000074do_test select4-1.3 {
75 set v [catch {execsql {
76 SELECT DISTINCT log FROM t1 ORDER BY log
77 UNION ALL
78 SELECT n FROM t1 WHERE log=3
79 ORDER BY log;
80 }} msg]
81 lappend v $msg
82} {1 {ORDER BY clause should come after UNION ALL not before}}
drh92dba242000-06-08 00:28:51 +000083
84# Union operator
85#
86do_test select4-2.1 {
87 execsql {
88 SELECT DISTINCT log FROM t1
89 UNION
90 SELECT n FROM t1 WHERE log=3
91 ORDER BY log;
92 }
93} {0 1 2 3 4 5 6 7 8}
94do_test select4-2.2 {
95 execsql {
96 SELECT log FROM t1 WHERE n IN
97 (SELECT DISTINCT log FROM t1 UNION
98 SELECT n FROM t1 WHERE log=3)
99 ORDER BY log;
100 }
101} {0 1 2 2 3 3 3 3}
drh92cd52f2000-06-08 01:55:29 +0000102do_test select4-2.3 {
103 set v [catch {execsql {
104 SELECT DISTINCT log FROM t1 ORDER BY log
105 UNION
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 not before}}
drh92dba242000-06-08 00:28:51 +0000111
112# Except operator
113#
114do_test select4-3.1 {
115 execsql {
116 SELECT DISTINCT log FROM t1
117 EXCEPT
118 SELECT n FROM t1 WHERE log=3
119 ORDER BY log;
120 }
121} {0 1 2 3 4}
122do_test select4-3.2 {
123 execsql {
124 SELECT log FROM t1 WHERE n IN
125 (SELECT DISTINCT log FROM t1 EXCEPT
126 SELECT n FROM t1 WHERE log=3)
127 ORDER BY log;
128 }
129} {0 1 2 2}
drh92cd52f2000-06-08 01:55:29 +0000130do_test select4-3.3 {
131 set v [catch {execsql {
132 SELECT DISTINCT log FROM t1 ORDER BY log
133 EXCEPT
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 EXCEPT not before}}
drh92dba242000-06-08 00:28:51 +0000139
140# Intersect operator
141#
142do_test select4-4.1 {
143 execsql {
144 SELECT DISTINCT log FROM t1
145 INTERSECT
146 SELECT n FROM t1 WHERE log=3
147 ORDER BY log;
148 }
149} {5}
150do_test select4-4.2 {
151 execsql {
152 SELECT log FROM t1 WHERE n IN
153 (SELECT DISTINCT log FROM t1 INTERSECT
154 SELECT n FROM t1 WHERE log=3)
155 ORDER BY log;
156 }
157} {3}
drh92cd52f2000-06-08 01:55:29 +0000158do_test select4-4.3 {
159 set v [catch {execsql {
160 SELECT DISTINCT log FROM t1 ORDER BY log
161 INTERSECT
162 SELECT n FROM t1 WHERE log=3
163 ORDER BY log;
164 }} msg]
165 lappend v $msg
166} {1 {ORDER BY clause should come after INTERSECT not before}}
167
168# Various error messages while processing UNION or INTERSECT
169#
170do_test select4-5.1 {
171 set v [catch {execsql {
172 SELECT DISTINCT log FROM t2
173 UNION ALL
174 SELECT n FROM t1 WHERE log=3
175 ORDER BY log;
176 }} msg]
177 lappend v $msg
178} {1 {no such table: t2}}
179do_test select4-5.2 {
180 set v [catch {execsql {
181 SELECT DISTINCT log AS "xyzzy" FROM t1
182 UNION ALL
183 SELECT n FROM t1 WHERE log=3
184 ORDER BY xyzzy;
185 }} msg]
186 lappend v $msg
187} {0 {0 1 2 3 4 5 5 6 7 8}}
188do_test select4-5.2b {
189 set v [catch {execsql {
190 SELECT DISTINCT log xyzzy FROM t1
191 UNION ALL
192 SELECT n FROM t1 WHERE log=3
193 ORDER BY 'xyzzy';
194 }} msg]
195 lappend v $msg
196} {0 {0 1 2 3 4 5 5 6 7 8}}
197do_test select4-5.2c {
198 set v [catch {execsql {
199 SELECT DISTINCT log FROM t1
200 UNION ALL
201 SELECT n FROM t1 WHERE log=3
202 ORDER BY 'xyzzy';
203 }} msg]
204 lappend v $msg
205} {1 {ORDER BY term number 1 does not match any result column}}
206do_test select4-5.2d {
207 set v [catch {execsql {
208 SELECT DISTINCT log FROM t1
209 INTERSECT
210 SELECT n FROM t1 WHERE log=3
211 ORDER BY 'xyzzy';
212 }} msg]
213 lappend v $msg
214} {1 {ORDER BY term number 1 does not match any result column}}
215do_test select4-5.2e {
216 set v [catch {execsql {
217 SELECT DISTINCT log FROM t1
218 UNION ALL
219 SELECT n FROM t1 WHERE log=3
220 ORDER BY n;
221 }} msg]
222 lappend v $msg
223} {0 {0 1 2 3 4 5 5 6 7 8}}
224do_test select4-5.3 {
225 set v [catch {execsql {
226 SELECT DISTINCT log, n FROM t1
227 UNION ALL
228 SELECT n FROM t1 WHERE log=3
229 ORDER BY log;
230 }} msg]
231 lappend v $msg
232} {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
233do_test select4-5.4 {
234 set v [catch {execsql {
235 SELECT log FROM t1 WHERE n=2
236 UNION ALL
237 SELECT log FROM t1 WHERE n=3
238 UNION ALL
239 SELECT log FROM t1 WHERE n=4
240 UNION ALL
241 SELECT log FROM t1 WHERE n=5
242 ORDER BY log;
243 }} msg]
244 lappend v $msg
245} {0 {1 2 2 3}}
drh92dba242000-06-08 00:28:51 +0000246
drh4cfa7932000-06-08 15:10:46 +0000247do_test select4-6.1 {
248 execsql {
249 SELECT log, count(*) as cnt FROM t1 GROUP BY log
250 UNION
251 SELECT log, n FROM t1 WHERE n=7
252 ORDER BY cnt, log;
253 }
254} {0 1 1 1 2 2 3 4 3 7 4 8 5 15}
255do_test select4-6.2 {
256 execsql {
257 SELECT log, count(*) FROM t1 GROUP BY log
258 UNION
259 SELECT log, n FROM t1 WHERE n=7
260 ORDER BY count(*), log;
261 }
262} {0 1 1 1 2 2 3 4 3 7 4 8 5 15}
263
drh92dba242000-06-08 00:28:51 +0000264finish_test