wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2010 July 16 |
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 | # |
||
12 | # This file implements tests to verify that the "testable statements" in |
||
13 | # the lang_select.html document are correct. |
||
14 | # |
||
15 | |||
16 | set testdir [file dirname $argv0] |
||
17 | source $testdir/tester.tcl |
||
18 | |||
19 | do_execsql_test e_select-1.0 { |
||
20 | CREATE TABLE t1(a, b); |
||
21 | INSERT INTO t1 VALUES('a', 'one'); |
||
22 | INSERT INTO t1 VALUES('b', 'two'); |
||
23 | INSERT INTO t1 VALUES('c', 'three'); |
||
24 | |||
25 | CREATE TABLE t2(a, b); |
||
26 | INSERT INTO t2 VALUES('a', 'I'); |
||
27 | INSERT INTO t2 VALUES('b', 'II'); |
||
28 | INSERT INTO t2 VALUES('c', 'III'); |
||
29 | |||
30 | CREATE TABLE t3(a, c); |
||
31 | INSERT INTO t3 VALUES('a', 1); |
||
32 | INSERT INTO t3 VALUES('b', 2); |
||
33 | |||
34 | CREATE TABLE t4(a, c); |
||
35 | INSERT INTO t4 VALUES('a', NULL); |
||
36 | INSERT INTO t4 VALUES('b', 2); |
||
37 | } {} |
||
38 | set t1_cross_t2 [list \ |
||
39 | a one a I a one b II \ |
||
40 | a one c III b two a I \ |
||
41 | b two b II b two c III \ |
||
42 | c three a I c three b II \ |
||
43 | c three c III \ |
||
44 | ] |
||
45 | set t1_cross_t1 [list \ |
||
46 | a one a one a one b two \ |
||
47 | a one c three b two a one \ |
||
48 | b two b two b two c three \ |
||
49 | c three a one c three b two \ |
||
50 | c three c three \ |
||
51 | ] |
||
52 | |||
53 | |||
54 | # This proc is a specialized version of [do_execsql_test]. |
||
55 | # |
||
56 | # The second argument to this proc must be a SELECT statement that |
||
57 | # features a cross join of some time. Instead of the usual ",", |
||
58 | # "CROSS JOIN" or "INNER JOIN" join-op, the string %JOIN% must be |
||
59 | # substituted. |
||
60 | # |
||
61 | # This test runs the SELECT three times - once with: |
||
62 | # |
||
63 | # * s/%JOIN%/,/ |
||
64 | # * s/%JOIN%/JOIN/ |
||
65 | # * s/%JOIN%/INNER JOIN/ |
||
66 | # * s/%JOIN%/CROSS JOIN/ |
||
67 | # |
||
68 | # and checks that each time the results of the SELECT are $res. |
||
69 | # |
||
70 | proc do_join_test {tn select res} { |
||
71 | foreach {tn2 joinop} [list 1 , 2 "CROSS JOIN" 3 "INNER JOIN"] { |
||
72 | set S [string map [list %JOIN% $joinop] $select] |
||
73 | uplevel do_execsql_test $tn.$tn2 [list $S] [list $res] |
||
74 | } |
||
75 | } |
||
76 | |||
77 | #------------------------------------------------------------------------- |
||
78 | # The following tests check that all paths on the syntax diagrams on |
||
79 | # the lang_select.html page may be taken. |
||
80 | # |
||
81 | # EVIDENCE-OF: R-18428-22111 -- syntax diagram join-constraint |
||
82 | # |
||
83 | do_join_test e_select-0.1.1 { |
||
84 | SELECT count(*) FROM t1 %JOIN% t2 ON (t1.a=t2.a) |
||
85 | } {3} |
||
86 | do_join_test e_select-0.1.2 { |
||
87 | SELECT count(*) FROM t1 %JOIN% t2 USING (a) |
||
88 | } {3} |
||
89 | do_join_test e_select-0.1.3 { |
||
90 | SELECT count(*) FROM t1 %JOIN% t2 |
||
91 | } {9} |
||
92 | do_catchsql_test e_select-0.1.4 { |
||
93 | SELECT count(*) FROM t1, t2 ON (t1.a=t2.a) USING (a) |
||
94 | } {1 {cannot have both ON and USING clauses in the same join}} |
||
95 | do_catchsql_test e_select-0.1.5 { |
||
96 | SELECT count(*) FROM t1, t2 USING (a) ON (t1.a=t2.a) |
||
97 | } {1 {near "ON": syntax error}} |
||
98 | |||
99 | # EVIDENCE-OF: R-44854-11739 -- syntax diagram select-core |
||
100 | # |
||
101 | # 0: SELECT ... |
||
102 | # 1: SELECT DISTINCT ... |
||
103 | # 2: SELECT ALL ... |
||
104 | # |
||
105 | # 0: No FROM clause |
||
106 | # 1: Has FROM clause |
||
107 | # |
||
108 | # 0: No WHERE clause |
||
109 | # 1: Has WHERE clause |
||
110 | # |
||
111 | # 0: No GROUP BY clause |
||
112 | # 1: Has GROUP BY clause |
||
113 | # 2: Has GROUP BY and HAVING clauses |
||
114 | # |
||
115 | do_select_tests e_select-0.2 { |
||
116 | 0000.1 "SELECT 1, 2, 3 " {1 2 3} |
||
117 | 1000.1 "SELECT DISTINCT 1, 2, 3 " {1 2 3} |
||
118 | 2000.1 "SELECT ALL 1, 2, 3 " {1 2 3} |
||
119 | |||
120 | 0100.1 "SELECT a, b, a||b FROM t1 " { |
||
121 | a one aone b two btwo c three cthree |
||
122 | } |
||
123 | 1100.1 "SELECT DISTINCT a, b, a||b FROM t1 " { |
||
124 | a one aone b two btwo c three cthree |
||
125 | } |
||
126 | 1200.1 "SELECT ALL a, b, a||b FROM t1 " { |
||
127 | a one aone b two btwo c three cthree |
||
128 | } |
||
129 | |||
130 | 0010.1 "SELECT 1, 2, 3 WHERE 1 " {1 2 3} |
||
131 | 0010.2 "SELECT 1, 2, 3 WHERE 0 " {} |
||
132 | 0010.3 "SELECT 1, 2, 3 WHERE NULL " {} |
||
133 | |||
134 | 1010.1 "SELECT DISTINCT 1, 2, 3 WHERE 1 " {1 2 3} |
||
135 | |||
136 | 2010.1 "SELECT ALL 1, 2, 3 WHERE 1 " {1 2 3} |
||
137 | |||
138 | 0110.1 "SELECT a, b, a||b FROM t1 WHERE a!='x' " { |
||
139 | a one aone b two btwo c three cthree |
||
140 | } |
||
141 | 0110.2 "SELECT a, b, a||b FROM t1 WHERE a=='x'" {} |
||
142 | |||
143 | 1110.1 "SELECT DISTINCT a, b, a||b FROM t1 WHERE a!='x' " { |
||
144 | a one aone b two btwo c three cthree |
||
145 | } |
||
146 | |||
147 | 2110.0 "SELECT ALL a, b, a||b FROM t1 WHERE a=='x'" {} |
||
148 | |||
149 | 0001.1 "SELECT 1, 2, 3 GROUP BY 2" {1 2 3} |
||
150 | 0002.1 "SELECT 1, 2, 3 GROUP BY 2 HAVING count(*)=1" {1 2 3} |
||
151 | 0002.2 "SELECT 1, 2, 3 GROUP BY 2 HAVING count(*)>1" {} |
||
152 | |||
153 | 1001.1 "SELECT DISTINCT 1, 2, 3 GROUP BY 2" {1 2 3} |
||
154 | 1002.1 "SELECT DISTINCT 1, 2, 3 GROUP BY 2 HAVING count(*)=1" {1 2 3} |
||
155 | 1002.2 "SELECT DISTINCT 1, 2, 3 GROUP BY 2 HAVING count(*)>1" {} |
||
156 | |||
157 | 2001.1 "SELECT ALL 1, 2, 3 GROUP BY 2" {1 2 3} |
||
158 | 2002.1 "SELECT ALL 1, 2, 3 GROUP BY 2 HAVING count(*)=1" {1 2 3} |
||
159 | 2002.2 "SELECT ALL 1, 2, 3 GROUP BY 2 HAVING count(*)>1" {} |
||
160 | |||
161 | 0101.1 "SELECT count(*), max(a) FROM t1 GROUP BY b" {1 a 1 c 1 b} |
||
162 | 0102.1 "SELECT count(*), max(a) FROM t1 GROUP BY b HAVING count(*)=1" { |
||
163 | 1 a 1 c 1 b |
||
164 | } |
||
165 | 0102.2 "SELECT count(*), max(a) FROM t1 GROUP BY b HAVING count(*)=2" { } |
||
166 | |||
167 | 1101.1 "SELECT DISTINCT count(*), max(a) FROM t1 GROUP BY b" {1 a 1 c 1 b} |
||
168 | 1102.1 "SELECT DISTINCT count(*), max(a) FROM t1 |
||
169 | GROUP BY b HAVING count(*)=1" { |
||
170 | 1 a 1 c 1 b |
||
171 | } |
||
172 | 1102.2 "SELECT DISTINCT count(*), max(a) FROM t1 |
||
173 | GROUP BY b HAVING count(*)=2" { |
||
174 | } |
||
175 | |||
176 | 2101.1 "SELECT ALL count(*), max(a) FROM t1 GROUP BY b" {1 a 1 c 1 b} |
||
177 | 2102.1 "SELECT ALL count(*), max(a) FROM t1 |
||
178 | GROUP BY b HAVING count(*)=1" { |
||
179 | 1 a 1 c 1 b |
||
180 | } |
||
181 | 2102.2 "SELECT ALL count(*), max(a) FROM t1 |
||
182 | GROUP BY b HAVING count(*)=2" { |
||
183 | } |
||
184 | |||
185 | 0011.1 "SELECT 1, 2, 3 WHERE 1 GROUP BY 2" {1 2 3} |
||
186 | 0012.1 "SELECT 1, 2, 3 WHERE 0 GROUP BY 2 HAVING count(*)=1" {} |
||
187 | 0012.2 "SELECT 1, 2, 3 WHERE 0 GROUP BY 2 HAVING count(*)>1" {} |
||
188 | |||
189 | 1011.1 "SELECT DISTINCT 1, 2, 3 WHERE 0 GROUP BY 2" {} |
||
190 | 1012.1 "SELECT DISTINCT 1, 2, 3 WHERE 1 GROUP BY 2 HAVING count(*)=1" |
||
191 | {1 2 3} |
||
192 | 1012.2 "SELECT DISTINCT 1, 2, 3 WHERE NULL GROUP BY 2 HAVING count(*)>1" {} |
||
193 | |||
194 | 2011.1 "SELECT ALL 1, 2, 3 WHERE 1 GROUP BY 2" {1 2 3} |
||
195 | 2012.1 "SELECT ALL 1, 2, 3 WHERE 0 GROUP BY 2 HAVING count(*)=1" {} |
||
196 | 2012.2 "SELECT ALL 1, 2, 3 WHERE 'abc' GROUP BY 2 HAVING count(*)>1" {} |
||
197 | |||
198 | 0111.1 "SELECT count(*), max(a) FROM t1 WHERE a='a' GROUP BY b" {1 a} |
||
199 | 0112.1 "SELECT count(*), max(a) FROM t1 |
||
200 | WHERE a='c' GROUP BY b HAVING count(*)=1" {1 c} |
||
201 | 0112.2 "SELECT count(*), max(a) FROM t1 |
||
202 | WHERE 0 GROUP BY b HAVING count(*)=2" { } |
||
203 | 1111.1 "SELECT DISTINCT count(*), max(a) FROM t1 WHERE a<'c' GROUP BY b" |
||
204 | {1 a 1 b} |
||
205 | 1112.1 "SELECT DISTINCT count(*), max(a) FROM t1 WHERE a>'a' |
||
206 | GROUP BY b HAVING count(*)=1" { |
||
207 | 1 c 1 b |
||
208 | } |
||
209 | 1112.2 "SELECT DISTINCT count(*), max(a) FROM t1 WHERE 0 |
||
210 | GROUP BY b HAVING count(*)=2" { |
||
211 | } |
||
212 | |||
213 | 2111.1 "SELECT ALL count(*), max(a) FROM t1 WHERE b>'one' GROUP BY b" |
||
214 | {1 c 1 b} |
||
215 | 2112.1 "SELECT ALL count(*), max(a) FROM t1 WHERE a!='b' |
||
216 | GROUP BY b HAVING count(*)=1" { |
||
217 | 1 a 1 c |
||
218 | } |
||
219 | 2112.2 "SELECT ALL count(*), max(a) FROM t1 |
||
220 | WHERE 0 GROUP BY b HAVING count(*)=2" { } |
||
221 | } |
||
222 | |||
223 | |||
224 | # EVIDENCE-OF: R-23316-20169 -- syntax diagram result-column |
||
225 | # |
||
226 | do_select_tests e_select-0.3 { |
||
227 | 1 "SELECT * FROM t1" {a one b two c three} |
||
228 | 2 "SELECT t1.* FROM t1" {a one b two c three} |
||
229 | 3 "SELECT 'x'||a||'x' FROM t1" {xax xbx xcx} |
||
230 | 4 "SELECT 'x'||a||'x' alias FROM t1" {xax xbx xcx} |
||
231 | 5 "SELECT 'x'||a||'x' AS alias FROM t1" {xax xbx xcx} |
||
232 | } |
||
233 | |||
234 | # EVIDENCE-OF: R-41233-21397 -- syntax diagram join-source |
||
235 | # |
||
236 | # EVIDENCE-OF: R-45040-11121 -- syntax diagram join-op |
||
237 | # |
||
238 | do_select_tests e_select-0.4 { |
||
239 | 1 "SELECT t1.rowid FROM t1" {1 2 3} |
||
240 | 2 "SELECT t1.rowid FROM t1,t2" {1 1 1 2 2 2 3 3 3} |
||
241 | 3 "SELECT t1.rowid FROM t1,t2,t3" {1 1 1 1 1 1 2 2 2 2 2 2 3 3 3 3 3 3} |
||
242 | |||
243 | 4 "SELECT t1.rowid FROM t1" {1 2 3} |
||
244 | 5 "SELECT t1.rowid FROM t1 JOIN t2" {1 1 1 2 2 2 3 3 3} |
||
245 | 6 "SELECT t1.rowid FROM t1 JOIN t2 JOIN t3" |
||
246 | {1 1 1 1 1 1 2 2 2 2 2 2 3 3 3 3 3 3} |
||
247 | |||
248 | 7 "SELECT t1.rowid FROM t1 NATURAL JOIN t3" {1 2} |
||
249 | 8 "SELECT t1.rowid FROM t1 NATURAL LEFT OUTER JOIN t3" {1 2 3} |
||
250 | 9 "SELECT t1.rowid FROM t1 NATURAL LEFT JOIN t3" {1 2 3} |
||
251 | 10 "SELECT t1.rowid FROM t1 NATURAL INNER JOIN t3" {1 2} |
||
252 | 11 "SELECT t1.rowid FROM t1 NATURAL CROSS JOIN t3" {1 2} |
||
253 | |||
254 | 12 "SELECT t1.rowid FROM t1 JOIN t3" {1 1 2 2 3 3} |
||
255 | 13 "SELECT t1.rowid FROM t1 LEFT OUTER JOIN t3" {1 1 2 2 3 3} |
||
256 | 14 "SELECT t1.rowid FROM t1 LEFT JOIN t3" {1 1 2 2 3 3} |
||
257 | 15 "SELECT t1.rowid FROM t1 INNER JOIN t3" {1 1 2 2 3 3} |
||
258 | 16 "SELECT t1.rowid FROM t1 CROSS JOIN t3" {1 1 2 2 3 3} |
||
259 | } |
||
260 | |||
261 | # EVIDENCE-OF: R-56911-63533 -- syntax diagram compound-operator |
||
262 | # |
||
263 | do_select_tests e_select-0.5 { |
||
264 | 1 "SELECT rowid FROM t1 UNION ALL SELECT rowid+2 FROM t4" {1 2 3 3 4} |
||
265 | 2 "SELECT rowid FROM t1 UNION SELECT rowid+2 FROM t4" {1 2 3 4} |
||
266 | 3 "SELECT rowid FROM t1 INTERSECT SELECT rowid+2 FROM t4" {3} |
||
267 | 4 "SELECT rowid FROM t1 EXCEPT SELECT rowid+2 FROM t4" {1 2} |
||
268 | } |
||
269 | |||
270 | # EVIDENCE-OF: R-60388-27458 -- syntax diagram ordering-term |
||
271 | # |
||
272 | do_select_tests e_select-0.6 { |
||
273 | 1 "SELECT b||a FROM t1 ORDER BY b||a" {onea threec twob} |
||
274 | 2 "SELECT b||a FROM t1 ORDER BY (b||a) COLLATE nocase" {onea threec twob} |
||
275 | 3 "SELECT b||a FROM t1 ORDER BY (b||a) ASC" {onea threec twob} |
||
276 | 4 "SELECT b||a FROM t1 ORDER BY (b||a) DESC" {twob threec onea} |
||
277 | } |
||
278 | |||
279 | # EVIDENCE-OF: R-36494-33519 -- syntax diagram select-stmt |
||
280 | # |
||
281 | do_select_tests e_select-0.7 { |
||
282 | 1 "SELECT * FROM t1" {a one b two c three} |
||
283 | 2 "SELECT * FROM t1 ORDER BY b" {a one c three b two} |
||
284 | 3 "SELECT * FROM t1 ORDER BY b, a" {a one c three b two} |
||
285 | |||
286 | 4 "SELECT * FROM t1 LIMIT 10" {a one b two c three} |
||
287 | 5 "SELECT * FROM t1 LIMIT 10 OFFSET 5" {} |
||
288 | 6 "SELECT * FROM t1 LIMIT 10, 5" {} |
||
289 | |||
290 | 7 "SELECT * FROM t1 ORDER BY a LIMIT 10" {a one b two c three} |
||
291 | 8 "SELECT * FROM t1 ORDER BY b LIMIT 10 OFFSET 5" {} |
||
292 | 9 "SELECT * FROM t1 ORDER BY a,b LIMIT 10, 5" {} |
||
293 | |||
294 | 10 "SELECT * FROM t1 UNION SELECT b, a FROM t1" |
||
295 | {a one b two c three one a three c two b} |
||
296 | 11 "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY b" |
||
297 | {one a two b three c a one c three b two} |
||
298 | 12 "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY b, a" |
||
299 | {one a two b three c a one c three b two} |
||
300 | 13 "SELECT * FROM t1 UNION SELECT b, a FROM t1 LIMIT 10" |
||
301 | {a one b two c three one a three c two b} |
||
302 | 14 "SELECT * FROM t1 UNION SELECT b, a FROM t1 LIMIT 10 OFFSET 5" |
||
303 | {two b} |
||
304 | 15 "SELECT * FROM t1 UNION SELECT b, a FROM t1 LIMIT 10, 5" |
||
305 | {} |
||
306 | 16 "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY a LIMIT 10" |
||
307 | {a one b two c three one a three c two b} |
||
308 | 17 "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY b LIMIT 10 OFFSET 5" |
||
309 | {b two} |
||
310 | 18 "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY a,b LIMIT 10, 5" |
||
311 | {} |
||
312 | } |
||
313 | |||
314 | #------------------------------------------------------------------------- |
||
315 | # The following tests focus on FROM clause (join) processing. |
||
316 | # |
||
317 | |||
318 | # EVIDENCE-OF: R-16074-54196 If the FROM clause is omitted from a simple |
||
319 | # SELECT statement, then the input data is implicitly a single row zero |
||
320 | # columns wide |
||
321 | # |
||
322 | do_select_tests e_select-1.1 { |
||
323 | 1 "SELECT 'abc'" {abc} |
||
324 | 2 "SELECT 'abc' WHERE NULL" {} |
||
325 | 3 "SELECT NULL" {{}} |
||
326 | 4 "SELECT count(*)" {1} |
||
327 | 5 "SELECT count(*) WHERE 0" {0} |
||
328 | 6 "SELECT count(*) WHERE 1" {1} |
||
329 | } |
||
330 | |||
331 | # EVIDENCE-OF: R-48114-33255 If there is only a single table in the |
||
332 | # join-source following the FROM clause, then the input data used by the |
||
333 | # SELECT statement is the contents of the named table. |
||
334 | # |
||
335 | # The results of the SELECT queries suggest that they are operating on the |
||
336 | # contents of the table 'xx'. |
||
337 | # |
||
338 | do_execsql_test e_select-1.2.0 { |
||
339 | CREATE TABLE xx(x, y); |
||
340 | INSERT INTO xx VALUES('IiJlsIPepMuAhU', X'10B00B897A15BAA02E3F98DCE8F2'); |
||
341 | INSERT INTO xx VALUES(NULL, -16.87); |
||
342 | INSERT INTO xx VALUES(-17.89, 'linguistically'); |
||
343 | } {} |
||
344 | do_select_tests e_select-1.2 { |
||
345 | 1 "SELECT quote(x), quote(y) FROM xx" { |
||
346 | 'IiJlsIPepMuAhU' X'10B00B897A15BAA02E3F98DCE8F2' |
||
347 | NULL -16.87 |
||
348 | -17.89 'linguistically' |
||
349 | } |
||
350 | |||
351 | 2 "SELECT count(*), count(x), count(y) FROM xx" {3 2 3} |
||
352 | 3 "SELECT sum(x), sum(y) FROM xx" {-17.89 -16.87} |
||
353 | } |
||
354 | |||
355 | # EVIDENCE-OF: R-23593-12456 If there is more than one table specified |
||
356 | # as part of the join-source following the FROM keyword, then the |
||
357 | # contents of each named table are joined into a single dataset for the |
||
358 | # simple SELECT statement to operate on. |
||
359 | # |
||
360 | # There are more detailed tests for subsequent requirements that add |
||
361 | # more detail to this idea. We just add a single test that shows that |
||
362 | # data is coming from each of the three tables following the FROM clause |
||
363 | # here to show that the statement, vague as it is, is not incorrect. |
||
364 | # |
||
365 | do_select_tests e_select-1.3 { |
||
366 | 1 "SELECT * FROM t1, t2, t3" { |
||
367 | a one a I a 1 a one a I b 2 a one b II a 1 |
||
368 | a one b II b 2 a one c III a 1 a one c III b 2 |
||
369 | b two a I a 1 b two a I b 2 b two b II a 1 |
||
370 | b two b II b 2 b two c III a 1 b two c III b 2 |
||
371 | c three a I a 1 c three a I b 2 c three b II a 1 |
||
372 | c three b II b 2 c three c III a 1 c three c III b 2 |
||
373 | } |
||
374 | } |
||
375 | |||
376 | # |
||
377 | # The following block of tests - e_select-1.4.* - test that the description |
||
378 | # of cartesian joins in the SELECT documentation is consistent with SQLite. |
||
379 | # In doing so, we test the following three requirements as a side-effect: |
||
380 | # |
||
381 | # EVIDENCE-OF: R-46122-14930 If the join-op is "CROSS JOIN", "INNER |
||
382 | # JOIN", "JOIN" or a comma (",") and there is no ON or USING clause, |
||
383 | # then the result of the join is simply the cartesian product of the |
||
384 | # left and right-hand datasets. |
||
385 | # |
||
386 | # The tests are built on this assertion. Really, they test that the output |
||
387 | # of a CROSS JOIN, JOIN, INNER JOIN or "," join matches the expected result |
||
388 | # of calculating the cartesian product of the left and right-hand datasets. |
||
389 | # |
||
390 | # EVIDENCE-OF: R-46256-57243 There is no difference between the "INNER |
||
391 | # JOIN", "JOIN" and "," join operators. |
||
392 | # |
||
393 | # EVIDENCE-OF: R-07544-24155 The "CROSS JOIN" join operator produces the |
||
394 | # same data as the "INNER JOIN", "JOIN" and "," operators |
||
395 | # |
||
396 | # All tests are run 4 times, with the only difference in each run being |
||
397 | # which of the 4 equivalent cartesian product join operators are used. |
||
398 | # Since the output data is the same in all cases, we consider that this |
||
399 | # qualifies as testing the two statements above. |
||
400 | # |
||
401 | do_execsql_test e_select-1.4.0 { |
||
402 | CREATE TABLE x1(a, b); |
||
403 | CREATE TABLE x2(c, d, e); |
||
404 | CREATE TABLE x3(f, g, h, i); |
||
405 | |||
406 | -- x1: 3 rows, 2 columns |
||
407 | INSERT INTO x1 VALUES(24, 'converging'); |
||
408 | INSERT INTO x1 VALUES(NULL, X'CB71'); |
||
409 | INSERT INTO x1 VALUES('blonds', 'proprietary'); |
||
410 | |||
411 | -- x2: 2 rows, 3 columns |
||
412 | INSERT INTO x2 VALUES(-60.06, NULL, NULL); |
||
413 | INSERT INTO x2 VALUES(-58, NULL, 1.21); |
||
414 | |||
415 | -- x3: 5 rows, 4 columns |
||
416 | INSERT INTO x3 VALUES(-39.24, NULL, 'encompass', -1); |
||
417 | INSERT INTO x3 VALUES('presenting', 51, 'reformation', 'dignified'); |
||
418 | INSERT INTO x3 VALUES('conducting', -87.24, 37.56, NULL); |
||
419 | INSERT INTO x3 VALUES('coldest', -96, 'dramatists', 82.3); |
||
420 | INSERT INTO x3 VALUES('alerting', NULL, -93.79, NULL); |
||
421 | } {} |
||
422 | |||
423 | # EVIDENCE-OF: R-59089-25828 The columns of the cartesian product |
||
424 | # dataset are, in order, all the columns of the left-hand dataset |
||
425 | # followed by all the columns of the right-hand dataset. |
||
426 | # |
||
427 | do_join_test e_select-1.4.1.1 { |
||
428 | SELECT * FROM x1 %JOIN% x2 LIMIT 1 |
||
429 | } [concat {24 converging} {-60.06 {} {}}] |
||
430 | |||
431 | do_join_test e_select-1.4.1.2 { |
||
432 | SELECT * FROM x2 %JOIN% x1 LIMIT 1 |
||
433 | } [concat {-60.06 {} {}} {24 converging}] |
||
434 | |||
435 | do_join_test e_select-1.4.1.3 { |
||
436 | SELECT * FROM x3 %JOIN% x2 LIMIT 1 |
||
437 | } [concat {-39.24 {} encompass -1} {-60.06 {} {}}] |
||
438 | |||
439 | do_join_test e_select-1.4.1.4 { |
||
440 | SELECT * FROM x2 %JOIN% x3 LIMIT 1 |
||
441 | } [concat {-60.06 {} {}} {-39.24 {} encompass -1}] |
||
442 | |||
443 | # EVIDENCE-OF: R-44414-54710 There is a row in the cartesian product |
||
444 | # dataset formed by combining each unique combination of a row from the |
||
445 | # left-hand and right-hand datasets. |
||
446 | # |
||
447 | do_join_test e_select-1.4.2.1 { |
||
448 | SELECT * FROM x2 %JOIN% x3 |
||
449 | } [list -60.06 {} {} -39.24 {} encompass -1 \ |
||
450 | -60.06 {} {} presenting 51 reformation dignified \ |
||
451 | -60.06 {} {} conducting -87.24 37.56 {} \ |
||
452 | -60.06 {} {} coldest -96 dramatists 82.3 \ |
||
453 | -60.06 {} {} alerting {} -93.79 {} \ |
||
454 | -58 {} 1.21 -39.24 {} encompass -1 \ |
||
455 | -58 {} 1.21 presenting 51 reformation dignified \ |
||
456 | -58 {} 1.21 conducting -87.24 37.56 {} \ |
||
457 | -58 {} 1.21 coldest -96 dramatists 82.3 \ |
||
458 | -58 {} 1.21 alerting {} -93.79 {} \ |
||
459 | ] |
||
460 | # TODO: Come back and add a few more like the above. |
||
461 | |||
462 | # EVIDENCE-OF: R-20659-43267 In other words, if the left-hand dataset |
||
463 | # consists of Nlhs rows of Mlhs columns, and the right-hand dataset of |
||
464 | # Nrhs rows of Mrhs columns, then the cartesian product is a dataset of |
||
465 | # Nlhs.Nrhs rows, each containing Mlhs+Mrhs columns. |
||
466 | # |
||
467 | # x1, x2 (Nlhs=3, Nrhs=2) (Mlhs=2, Mrhs=3) |
||
468 | do_join_test e_select-1.4.3.1 { |
||
469 | SELECT count(*) FROM x1 %JOIN% x2 |
||
470 | } [expr 3*2] |
||
471 | do_test e_select-1.4.3.2 { |
||
472 | expr {[llength [execsql {SELECT * FROM x1, x2}]] / 6} |
||
473 | } [expr 2+3] |
||
474 | |||
475 | # x2, x3 (Nlhs=2, Nrhs=5) (Mlhs=3, Mrhs=4) |
||
476 | do_join_test e_select-1.4.3.3 { |
||
477 | SELECT count(*) FROM x2 %JOIN% x3 |
||
478 | } [expr 2*5] |
||
479 | do_test e_select-1.4.3.4 { |
||
480 | expr {[llength [execsql {SELECT * FROM x2 JOIN x3}]] / 10} |
||
481 | } [expr 3+4] |
||
482 | |||
483 | # x3, x1 (Nlhs=5, Nrhs=3) (Mlhs=4, Mrhs=2) |
||
484 | do_join_test e_select-1.4.3.5 { |
||
485 | SELECT count(*) FROM x3 %JOIN% x1 |
||
486 | } [expr 5*3] |
||
487 | do_test e_select-1.4.3.6 { |
||
488 | expr {[llength [execsql {SELECT * FROM x3 CROSS JOIN x1}]] / 15} |
||
489 | } [expr 4+2] |
||
490 | |||
491 | # x3, x3 (Nlhs=5, Nrhs=5) (Mlhs=4, Mrhs=4) |
||
492 | do_join_test e_select-1.4.3.7 { |
||
493 | SELECT count(*) FROM x3 %JOIN% x3 |
||
494 | } [expr 5*5] |
||
495 | do_test e_select-1.4.3.8 { |
||
496 | expr {[llength [execsql {SELECT * FROM x3 INNER JOIN x3 AS x4}]] / 25} |
||
497 | } [expr 4+4] |
||
498 | |||
499 | # Some extra cartesian product tests using tables t1 and t2. |
||
500 | # |
||
501 | do_execsql_test e_select-1.4.4.1 { SELECT * FROM t1, t2 } $t1_cross_t2 |
||
502 | do_execsql_test e_select-1.4.4.2 { SELECT * FROM t1 AS x, t1 AS y} $t1_cross_t1 |
||
503 | |||
504 | do_select_tests e_select-1.4.5 [list \ |
||
505 | 1 { SELECT * FROM t1 CROSS JOIN t2 } $t1_cross_t2 \ |
||
506 | 2 { SELECT * FROM t1 AS y CROSS JOIN t1 AS x } $t1_cross_t1 \ |
||
507 | 3 { SELECT * FROM t1 INNER JOIN t2 } $t1_cross_t2 \ |
||
508 | 4 { SELECT * FROM t1 AS y INNER JOIN t1 AS x } $t1_cross_t1 \ |
||
509 | ] |
||
510 | |||
511 | |||
512 | # EVIDENCE-OF: R-22775-56496 If there is an ON clause specified, then |
||
513 | # the ON expression is evaluated for each row of the cartesian product |
||
514 | # as a boolean expression. All rows for which the expression evaluates |
||
515 | # to false are excluded from the dataset. |
||
516 | # |
||
517 | foreach {tn select res} [list \ |
||
518 | 1 { SELECT * FROM t1 %JOIN% t2 ON (1) } $t1_cross_t2 \ |
||
519 | 2 { SELECT * FROM t1 %JOIN% t2 ON (0) } [list] \ |
||
520 | 3 { SELECT * FROM t1 %JOIN% t2 ON (NULL) } [list] \ |
||
521 | 4 { SELECT * FROM t1 %JOIN% t2 ON ('abc') } [list] \ |
||
522 | 5 { SELECT * FROM t1 %JOIN% t2 ON ('1ab') } $t1_cross_t2 \ |
||
523 | 6 { SELECT * FROM t1 %JOIN% t2 ON (0.9) } $t1_cross_t2 \ |
||
524 | 7 { SELECT * FROM t1 %JOIN% t2 ON ('0.9') } $t1_cross_t2 \ |
||
525 | 8 { SELECT * FROM t1 %JOIN% t2 ON (0.0) } [list] \ |
||
526 | \ |
||
527 | 9 { SELECT t1.b, t2.b FROM t1 %JOIN% t2 ON (t1.a = t2.a) } \ |
||
528 | {one I two II three III} \ |
||
529 | 10 { SELECT t1.b, t2.b FROM t1 %JOIN% t2 ON (t1.a = 'a') } \ |
||
530 | {one I one II one III} \ |
||
531 | 11 { SELECT t1.b, t2.b |
||
532 | FROM t1 %JOIN% t2 ON (CASE WHEN t1.a = 'a' THEN NULL ELSE 1 END) } \ |
||
533 | {two I two II two III three I three II three III} \ |
||
534 | ] { |
||
535 | do_join_test e_select-1.3.$tn $select $res |
||
536 | } |
||
537 | |||
538 | # EVIDENCE-OF: R-63358-54862 If there is a USING clause specified as |
||
539 | # part of the join-constraint, then each of the column names specified |
||
540 | # must exist in the datasets to both the left and right of the join-op. |
||
541 | # |
||
542 | do_select_tests e_select-1.4 -error { |
||
543 | cannot join using column %s - column not present in both tables |
||
544 | } { |
||
545 | 1 { SELECT * FROM t1, t3 USING (b) } "b" |
||
546 | 2 { SELECT * FROM t3, t1 USING (c) } "c" |
||
547 | 3 { SELECT * FROM t3, (SELECT a AS b, b AS c FROM t1) USING (a) } "a" |
||
548 | } |
||
549 | |||
550 | # EVIDENCE-OF: R-55987-04584 For each pair of namesake columns, the |
||
551 | # expression "lhs.X = rhs.X" is evaluated for each row of the cartesian |
||
552 | # product as a boolean expression. All rows for which one or more of the |
||
553 | # expressions evaluates to false are excluded from the result set. |
||
554 | # |
||
555 | do_select_tests e_select-1.5 { |
||
556 | 1 { SELECT * FROM t1, t3 USING (a) } {a one 1 b two 2} |
||
557 | 2 { SELECT * FROM t3, t4 USING (a,c) } {b 2} |
||
558 | } |
||
559 | |||
560 | # EVIDENCE-OF: R-54046-48600 When comparing values as a result of a |
||
561 | # USING clause, the normal rules for handling affinities, collation |
||
562 | # sequences and NULL values in comparisons apply. |
||
563 | # |
||
564 | # EVIDENCE-OF: R-35466-18578 The column from the dataset on the |
||
565 | # left-hand side of the join operator is considered to be on the |
||
566 | # left-hand side of the comparison operator (=) for the purposes of |
||
567 | # collation sequence and affinity precedence. |
||
568 | # |
||
569 | do_execsql_test e_select-1.6.0 { |
||
570 | CREATE TABLE t5(a COLLATE nocase, b COLLATE binary); |
||
571 | INSERT INTO t5 VALUES('AA', 'cc'); |
||
572 | INSERT INTO t5 VALUES('BB', 'dd'); |
||
573 | INSERT INTO t5 VALUES(NULL, NULL); |
||
574 | CREATE TABLE t6(a COLLATE binary, b COLLATE nocase); |
||
575 | INSERT INTO t6 VALUES('aa', 'cc'); |
||
576 | INSERT INTO t6 VALUES('bb', 'DD'); |
||
577 | INSERT INTO t6 VALUES(NULL, NULL); |
||
578 | } {} |
||
579 | foreach {tn select res} { |
||
580 | 1 { SELECT * FROM t5 %JOIN% t6 USING (a) } {AA cc cc BB dd DD} |
||
581 | 2 { SELECT * FROM t6 %JOIN% t5 USING (a) } {} |
||
582 | 3 { SELECT * FROM (SELECT a COLLATE nocase, b FROM t6) %JOIN% t5 USING (a) } |
||
583 | {aa cc cc bb DD dd} |
||
584 | 4 { SELECT * FROM t5 %JOIN% t6 USING (a,b) } {AA cc} |
||
585 | 5 { SELECT * FROM t6 %JOIN% t5 USING (a,b) } {} |
||
586 | } { |
||
587 | do_join_test e_select-1.6.$tn $select $res |
||
588 | } |
||
589 | |||
590 | # EVIDENCE-OF: R-57047-10461 For each pair of columns identified by a |
||
591 | # USING clause, the column from the right-hand dataset is omitted from |
||
592 | # the joined dataset. |
||
593 | # |
||
594 | # EVIDENCE-OF: R-56132-15700 This is the only difference between a USING |
||
595 | # clause and its equivalent ON constraint. |
||
596 | # |
||
597 | foreach {tn select res} { |
||
598 | 1a { SELECT * FROM t1 %JOIN% t2 USING (a) } |
||
599 | {a one I b two II c three III} |
||
600 | 1b { SELECT * FROM t1 %JOIN% t2 ON (t1.a=t2.a) } |
||
601 | {a one a I b two b II c three c III} |
||
602 | |||
603 | 2a { SELECT * FROM t3 %JOIN% t4 USING (a) } |
||
604 | {a 1 {} b 2 2} |
||
605 | 2b { SELECT * FROM t3 %JOIN% t4 ON (t3.a=t4.a) } |
||
606 | {a 1 a {} b 2 b 2} |
||
607 | |||
608 | 3a { SELECT * FROM t3 %JOIN% t4 USING (a,c) } {b 2} |
||
609 | 3b { SELECT * FROM t3 %JOIN% t4 ON (t3.a=t4.a AND t3.c=t4.c) } {b 2 b 2} |
||
610 | |||
611 | 4a { SELECT * FROM (SELECT a COLLATE nocase, b FROM t6) AS x |
||
612 | %JOIN% t5 USING (a) } |
||
613 | {aa cc cc bb DD dd} |
||
614 | 4b { SELECT * FROM (SELECT a COLLATE nocase, b FROM t6) AS x |
||
615 | %JOIN% t5 ON (x.a=t5.a) } |
||
616 | {aa cc AA cc bb DD BB dd} |
||
617 | } { |
||
618 | do_join_test e_select-1.7.$tn $select $res |
||
619 | } |
||
620 | |||
621 | # EVIDENCE-OF: R-41434-12448 If the join-op is a "LEFT JOIN" or "LEFT |
||
622 | # OUTER JOIN", then after the ON or USING filtering clauses have been |
||
623 | # applied, an extra row is added to the output for each row in the |
||
624 | # original left-hand input dataset that corresponds to no rows at all in |
||
625 | # the composite dataset (if any). |
||
626 | # |
||
627 | do_execsql_test e_select-1.8.0 { |
||
628 | CREATE TABLE t7(a, b, c); |
||
629 | CREATE TABLE t8(a, d, e); |
||
630 | |||
631 | INSERT INTO t7 VALUES('x', 'ex', 24); |
||
632 | INSERT INTO t7 VALUES('y', 'why', 25); |
||
633 | |||
634 | INSERT INTO t8 VALUES('x', 'abc', 24); |
||
635 | INSERT INTO t8 VALUES('z', 'ghi', 26); |
||
636 | } {} |
||
637 | |||
638 | do_select_tests e_select-1.8 { |
||
639 | 1a "SELECT count(*) FROM t7 JOIN t8 ON (t7.a=t8.a)" {1} |
||
640 | 1b "SELECT count(*) FROM t7 LEFT JOIN t8 ON (t7.a=t8.a)" {2} |
||
641 | 2a "SELECT count(*) FROM t7 JOIN t8 USING (a)" {1} |
||
642 | 2b "SELECT count(*) FROM t7 LEFT JOIN t8 USING (a)" {2} |
||
643 | } |
||
644 | |||
645 | |||
646 | # EVIDENCE-OF: R-15607-52988 The added rows contain NULL values in the |
||
647 | # columns that would normally contain values copied from the right-hand |
||
648 | # input dataset. |
||
649 | # |
||
650 | do_select_tests e_select-1.9 { |
||
651 | 1a "SELECT * FROM t7 JOIN t8 ON (t7.a=t8.a)" {x ex 24 x abc 24} |
||
652 | 1b "SELECT * FROM t7 LEFT JOIN t8 ON (t7.a=t8.a)" |
||
653 | {x ex 24 x abc 24 y why 25 {} {} {}} |
||
654 | 2a "SELECT * FROM t7 JOIN t8 USING (a)" {x ex 24 abc 24} |
||
655 | 2b "SELECT * FROM t7 LEFT JOIN t8 USING (a)" {x ex 24 abc 24 y why 25 {} {}} |
||
656 | } |
||
657 | |||
658 | # EVIDENCE-OF: R-01809-52134 If the NATURAL keyword is added to any of |
||
659 | # the join-ops, then an implicit USING clause is added to the |
||
660 | # join-constraints. The implicit USING clause contains each of the |
||
661 | # column names that appear in both the left and right-hand input |
||
662 | # datasets. |
||
663 | # |
||
664 | do_select_tests e_select-1-10 { |
||
665 | 1a "SELECT * FROM t7 JOIN t8 USING (a)" {x ex 24 abc 24} |
||
666 | 1b "SELECT * FROM t7 NATURAL JOIN t8" {x ex 24 abc 24} |
||
667 | |||
668 | 2a "SELECT * FROM t8 JOIN t7 USING (a)" {x abc 24 ex 24} |
||
669 | 2b "SELECT * FROM t8 NATURAL JOIN t7" {x abc 24 ex 24} |
||
670 | |||
671 | 3a "SELECT * FROM t7 LEFT JOIN t8 USING (a)" {x ex 24 abc 24 y why 25 {} {}} |
||
672 | 3b "SELECT * FROM t7 NATURAL LEFT JOIN t8" {x ex 24 abc 24 y why 25 {} {}} |
||
673 | |||
674 | 4a "SELECT * FROM t8 LEFT JOIN t7 USING (a)" {x abc 24 ex 24 z ghi 26 {} {}} |
||
675 | 4b "SELECT * FROM t8 NATURAL LEFT JOIN t7" {x abc 24 ex 24 z ghi 26 {} {}} |
||
676 | |||
677 | 5a "SELECT * FROM t3 JOIN t4 USING (a,c)" {b 2} |
||
678 | 5b "SELECT * FROM t3 NATURAL JOIN t4" {b 2} |
||
679 | |||
680 | 6a "SELECT * FROM t3 LEFT JOIN t4 USING (a,c)" {a 1 b 2} |
||
681 | 6b "SELECT * FROM t3 NATURAL LEFT JOIN t4" {a 1 b 2} |
||
682 | } |
||
683 | |||
684 | # EVIDENCE-OF: R-49566-01570 If the left and right-hand input datasets |
||
685 | # feature no common column names, then the NATURAL keyword has no effect |
||
686 | # on the results of the join. |
||
687 | # |
||
688 | do_execsql_test e_select-1.11.0 { |
||
689 | CREATE TABLE t10(x, y); |
||
690 | INSERT INTO t10 VALUES(1, 'true'); |
||
691 | INSERT INTO t10 VALUES(0, 'false'); |
||
692 | } {} |
||
693 | do_select_tests e_select-1-11 { |
||
694 | 1a "SELECT a, x FROM t1 CROSS JOIN t10" {a 1 a 0 b 1 b 0 c 1 c 0} |
||
695 | 1b "SELECT a, x FROM t1 NATURAL CROSS JOIN t10" {a 1 a 0 b 1 b 0 c 1 c 0} |
||
696 | } |
||
697 | |||
698 | # EVIDENCE-OF: R-39625-59133 A USING or ON clause may not be added to a |
||
699 | # join that specifies the NATURAL keyword. |
||
700 | # |
||
701 | foreach {tn sql} { |
||
702 | 1 {SELECT * FROM t1 NATURAL LEFT JOIN t2 USING (a)} |
||
703 | 2 {SELECT * FROM t1 NATURAL LEFT JOIN t2 ON (t1.a=t2.a)} |
||
704 | 3 {SELECT * FROM t1 NATURAL LEFT JOIN t2 ON (45)} |
||
705 | } { |
||
706 | do_catchsql_test e_select-1.12.$tn " |
||
707 | $sql |
||
708 | " {1 {a NATURAL join may not have an ON or USING clause}} |
||
709 | } |
||
710 | |||
711 | #------------------------------------------------------------------------- |
||
712 | # The next block of tests - e_select-3.* - concentrate on verifying |
||
713 | # statements made regarding WHERE clause processing. |
||
714 | # |
||
715 | drop_all_tables |
||
716 | do_execsql_test e_select-3.0 { |
||
717 | CREATE TABLE x1(k, x, y, z); |
||
718 | INSERT INTO x1 VALUES(1, 'relinquished', 'aphasia', 78.43); |
||
719 | INSERT INTO x1 VALUES(2, X'A8E8D66F', X'07CF', -81); |
||
720 | INSERT INTO x1 VALUES(3, -22, -27.57, NULL); |
||
721 | INSERT INTO x1 VALUES(4, NULL, 'bygone', 'picky'); |
||
722 | INSERT INTO x1 VALUES(5, NULL, 96.28, NULL); |
||
723 | INSERT INTO x1 VALUES(6, 0, 1, 2); |
||
724 | |||
725 | CREATE TABLE x2(k, x, y2); |
||
726 | INSERT INTO x2 VALUES(1, 50, X'B82838'); |
||
727 | INSERT INTO x2 VALUES(5, 84.79, 65.88); |
||
728 | INSERT INTO x2 VALUES(3, -22, X'0E1BE452A393'); |
||
729 | INSERT INTO x2 VALUES(7, 'mistrusted', 'standardized'); |
||
730 | } {} |
||
731 | |||
732 | # EVIDENCE-OF: R-06999-14330 If a WHERE clause is specified, the WHERE |
||
733 | # expression is evaluated for each row in the input data as a boolean |
||
734 | # expression. All rows for which the WHERE clause expression evaluates |
||
735 | # to false are excluded from the dataset before continuing. |
||
736 | # |
||
737 | do_execsql_test e_select-3.1.1 { SELECT k FROM x1 WHERE x } {3} |
||
738 | do_execsql_test e_select-3.1.2 { SELECT k FROM x1 WHERE y } {3 5 6} |
||
739 | do_execsql_test e_select-3.1.3 { SELECT k FROM x1 WHERE z } {1 2 6} |
||
740 | do_execsql_test e_select-3.1.4 { SELECT k FROM x1 WHERE '1'||z } {1 2 4 6} |
||
741 | do_execsql_test e_select-3.1.5 { SELECT k FROM x1 WHERE x IS NULL } {4 5} |
||
742 | do_execsql_test e_select-3.1.6 { SELECT k FROM x1 WHERE z - 78.43 } {2 4 6} |
||
743 | |||
744 | do_execsql_test e_select-3.2.1a { |
||
745 | SELECT k FROM x1 LEFT JOIN x2 USING(k) |
||
746 | } {1 2 3 4 5 6} |
||
747 | do_execsql_test e_select-3.2.1b { |
||
748 | SELECT k FROM x1 LEFT JOIN x2 USING(k) WHERE x2.k |
||
749 | } {1 3 5} |
||
750 | do_execsql_test e_select-3.2.2 { |
||
751 | SELECT k FROM x1 LEFT JOIN x2 USING(k) WHERE x2.k IS NULL |
||
752 | } {2 4 6} |
||
753 | |||
754 | do_execsql_test e_select-3.2.3 { |
||
755 | SELECT k FROM x1 NATURAL JOIN x2 WHERE x2.k |
||
756 | } {3} |
||
757 | do_execsql_test e_select-3.2.4 { |
||
758 | SELECT k FROM x1 NATURAL JOIN x2 WHERE x2.k-3 |
||
759 | } {} |
||
760 | |||
761 | #------------------------------------------------------------------------- |
||
762 | # Tests below this point are focused on verifying the testable statements |
||
763 | # related to caculating the result rows of a simple SELECT statement. |
||
764 | # |
||
765 | |||
766 | drop_all_tables |
||
767 | do_execsql_test e_select-4.0 { |
||
768 | CREATE TABLE z1(a, b, c); |
||
769 | CREATE TABLE z2(d, e); |
||
770 | CREATE TABLE z3(a, b); |
||
771 | |||
772 | INSERT INTO z1 VALUES(51.65, -59.58, 'belfries'); |
||
773 | INSERT INTO z1 VALUES(-5, NULL, 75); |
||
774 | INSERT INTO z1 VALUES(-2.2, -23.18, 'suiters'); |
||
775 | INSERT INTO z1 VALUES(NULL, 67, 'quartets'); |
||
776 | INSERT INTO z1 VALUES(-1.04, -32.3, 'aspen'); |
||
777 | INSERT INTO z1 VALUES(63, 'born', -26); |
||
778 | |||
779 | INSERT INTO z2 VALUES(NULL, 21); |
||
780 | INSERT INTO z2 VALUES(36, 6); |
||
781 | |||
782 | INSERT INTO z3 VALUES('subsistence', 'gauze'); |
||
783 | INSERT INTO z3 VALUES(49.17, -67); |
||
784 | } {} |
||
785 | |||
786 | # EVIDENCE-OF: R-36327-17224 If a result expression is the special |
||
787 | # expression "*" then all columns in the input data are substituted for |
||
788 | # that one expression. |
||
789 | # |
||
790 | # EVIDENCE-OF: R-43693-30522 If the expression is the alias of a table |
||
791 | # or subquery in the FROM clause followed by ".*" then all columns from |
||
792 | # the named table or subquery are substituted for the single expression. |
||
793 | # |
||
794 | do_select_tests e_select-4.1 { |
||
795 | 1 "SELECT * FROM z1 LIMIT 1" {51.65 -59.58 belfries} |
||
796 | 2 "SELECT * FROM z1,z2 LIMIT 1" {51.65 -59.58 belfries {} 21} |
||
797 | 3 "SELECT z1.* FROM z1,z2 LIMIT 1" {51.65 -59.58 belfries} |
||
798 | 4 "SELECT z2.* FROM z1,z2 LIMIT 1" {{} 21} |
||
799 | 5 "SELECT z2.*, z1.* FROM z1,z2 LIMIT 1" {{} 21 51.65 -59.58 belfries} |
||
800 | |||
801 | 6 "SELECT count(*), * FROM z1" {6 63 born -26} |
||
802 | 7 "SELECT max(a), * FROM z1" {63 63 born -26} |
||
803 | 8 "SELECT *, min(a) FROM z1" {63 born -26 -5} |
||
804 | |||
805 | 9 "SELECT *,* FROM z1,z2 LIMIT 1" { |
||
806 | 51.65 -59.58 belfries {} 21 51.65 -59.58 belfries {} 21 |
||
807 | } |
||
808 | 10 "SELECT z1.*,z1.* FROM z2,z1 LIMIT 1" { |
||
809 | 51.65 -59.58 belfries 51.65 -59.58 belfries |
||
810 | } |
||
811 | } |
||
812 | |||
813 | # EVIDENCE-OF: R-61869-22578 It is an error to use a "*" or "alias.*" |
||
814 | # expression in any context other than than a result expression list. |
||
815 | # |
||
816 | # EVIDENCE-OF: R-44324-41166 It is also an error to use a "*" or |
||
817 | # "alias.*" expression in a simple SELECT query that does not have a |
||
818 | # FROM clause. |
||
819 | # |
||
820 | foreach {tn select err} { |
||
821 | 1.1 "SELECT a, b, c FROM z1 WHERE *" {near "*": syntax error} |
||
822 | 1.2 "SELECT a, b, c FROM z1 GROUP BY *" {near "*": syntax error} |
||
823 | 1.3 "SELECT 1 + * FROM z1" {near "*": syntax error} |
||
824 | 1.4 "SELECT * + 1 FROM z1" {near "+": syntax error} |
||
825 | |||
826 | 2.1 "SELECT *" {no tables specified} |
||
827 | 2.2 "SELECT * WHERE 1" {no tables specified} |
||
828 | 2.3 "SELECT * WHERE 0" {no tables specified} |
||
829 | 2.4 "SELECT count(*), *" {no tables specified} |
||
830 | } { |
||
831 | do_catchsql_test e_select-4.2.$tn $select [list 1 $err] |
||
832 | } |
||
833 | |||
834 | # EVIDENCE-OF: R-08669-22397 The number of columns in the rows returned |
||
835 | # by a simple SELECT statement is equal to the number of expressions in |
||
836 | # the result expression list after substitution of * and alias.* |
||
837 | # expressions. |
||
838 | # |
||
839 | foreach {tn select nCol} { |
||
840 | 1 "SELECT * FROM z1" 3 |
||
841 | 2 "SELECT * FROM z1 NATURAL JOIN z3" 3 |
||
842 | 3 "SELECT z1.* FROM z1 NATURAL JOIN z3" 3 |
||
843 | 4 "SELECT z3.* FROM z1 NATURAL JOIN z3" 2 |
||
844 | 5 "SELECT z1.*, z3.* FROM z1 NATURAL JOIN z3" 5 |
||
845 | 6 "SELECT 1, 2, z1.* FROM z1" 5 |
||
846 | 7 "SELECT a, *, b, c FROM z1" 6 |
||
847 | } { |
||
848 | set ::stmt [sqlite3_prepare_v2 db $select -1 DUMMY] |
||
849 | do_test e_select-4.3.$tn { sqlite3_column_count $::stmt } $nCol |
||
850 | sqlite3_finalize $::stmt |
||
851 | } |
||
852 | |||
853 | |||
854 | |||
855 | # In lang_select.html, a non-aggregate query is defined as any simple SELECT |
||
856 | # that has no GROUP BY clause and no aggregate expressions in the result |
||
857 | # expression list. Other queries are aggregate queries. Test cases |
||
858 | # e_select-4.4.* through e_select-4.12.*, inclusive, which test the part of |
||
859 | # simple SELECT that is different for aggregate and non-aggregate queries |
||
860 | # verify (in a way) that these definitions are consistent: |
||
861 | # |
||
862 | # EVIDENCE-OF: R-20637-43463 A simple SELECT statement is an aggregate |
||
863 | # query if it contains either a GROUP BY clause or one or more aggregate |
||
864 | # functions in the result-set. |
||
865 | # |
||
866 | # EVIDENCE-OF: R-23155-55597 Otherwise, if a simple SELECT contains no |
||
867 | # aggregate functions or a GROUP BY clause, it is a non-aggregate query. |
||
868 | # |
||
869 | |||
870 | # EVIDENCE-OF: R-44050-47362 If the SELECT statement is a non-aggregate |
||
871 | # query, then each expression in the result expression list is evaluated |
||
872 | # for each row in the dataset filtered by the WHERE clause. |
||
873 | # |
||
874 | do_select_tests e_select-4.4 { |
||
875 | 1 "SELECT a, b FROM z1" |
||
876 | {51.65 -59.58 -5 {} -2.2 -23.18 {} 67 -1.04 -32.3 63 born} |
||
877 | |||
878 | 2 "SELECT a IS NULL, b+1, * FROM z1" { |
||
879 | |||
880 | |||
881 | |||
882 | 1 68 {} 67 quartets |
||
883 | |||
884 | |||
885 | } |
||
886 | |||
887 | 3 "SELECT 32*32, d||e FROM z2" {1024 {} 1024 366} |
||
888 | } |
||
889 | |||
890 | |||
891 | # Test cases e_select-4.5.* and e_select-4.6.* together show that: |
||
892 | # |
||
893 | # EVIDENCE-OF: R-51988-01124 The single row of result-set data created |
||
894 | # by evaluating the aggregate and non-aggregate expressions in the |
||
895 | # result-set forms the result of an aggregate query without a GROUP BY |
||
896 | # clause. |
||
897 | # |
||
898 | |||
899 | # EVIDENCE-OF: R-57629-25253 If the SELECT statement is an aggregate |
||
900 | # query without a GROUP BY clause, then each aggregate expression in the |
||
901 | # result-set is evaluated once across the entire dataset. |
||
902 | # |
||
903 | do_select_tests e_select-4.5 { |
||
904 | 1 "SELECT count(a), max(a), count(b), max(b) FROM z1" {5 63 5 born} |
||
905 | 2 "SELECT count(*), max(1)" {1 1} |
||
906 | |||
907 | 3 "SELECT sum(b+1) FROM z1 NATURAL LEFT JOIN z3" {-43.06} |
||
908 | 4 "SELECT sum(b+2) FROM z1 NATURAL LEFT JOIN z3" {-38.06} |
||
909 | 5 "SELECT sum(b IS NOT NULL) FROM z1 NATURAL LEFT JOIN z3" {5} |
||
910 | } |
||
911 | |||
912 | # EVIDENCE-OF: R-26684-40576 Each non-aggregate expression in the |
||
913 | # result-set is evaluated once for an arbitrarily selected row of the |
||
914 | # dataset. |
||
915 | # |
||
916 | # EVIDENCE-OF: R-27994-60376 The same arbitrarily selected row is used |
||
917 | # for each non-aggregate expression. |
||
918 | # |
||
919 | # Note: The results of many of the queries in this block of tests are |
||
920 | # technically undefined, as the documentation does not specify which row |
||
921 | # SQLite will arbitrarily select to use for the evaluation of the |
||
922 | # non-aggregate expressions. |
||
923 | # |
||
924 | drop_all_tables |
||
925 | do_execsql_test e_select-4.6.0 { |
||
926 | CREATE TABLE a1(one PRIMARY KEY, two); |
||
927 | INSERT INTO a1 VALUES(1, 1); |
||
928 | INSERT INTO a1 VALUES(2, 3); |
||
929 | INSERT INTO a1 VALUES(3, 6); |
||
930 | INSERT INTO a1 VALUES(4, 10); |
||
931 | |||
932 | CREATE TABLE a2(one PRIMARY KEY, three); |
||
933 | INSERT INTO a2 VALUES(1, 1); |
||
934 | INSERT INTO a2 VALUES(3, 2); |
||
935 | INSERT INTO a2 VALUES(6, 3); |
||
936 | INSERT INTO a2 VALUES(10, 4); |
||
937 | } {} |
||
938 | do_select_tests e_select-4.6 { |
||
939 | 1 "SELECT one, two, count(*) FROM a1" {4 10 4} |
||
940 | 2 "SELECT one, two, count(*) FROM a1 WHERE one<3" {2 3 2} |
||
941 | 3 "SELECT one, two, count(*) FROM a1 WHERE one>3" {4 10 1} |
||
942 | 4 "SELECT *, count(*) FROM a1 JOIN a2" {4 10 10 4 16} |
||
943 | 5 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2" {3 6 2 3} |
||
944 | 6 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2" {3 6 2 3} |
||
945 | 7 "SELECT group_concat(three, ''), a1.* FROM a1 NATURAL JOIN a2" {12 3 6} |
||
946 | } |
||
947 | |||
948 | # EVIDENCE-OF: R-04486-07266 Or, if the dataset contains zero rows, then |
||
949 | # each non-aggregate expression is evaluated against a row consisting |
||
950 | # entirely of NULL values. |
||
951 | # |
||
952 | do_select_tests e_select-4.7 { |
||
953 | 1 "SELECT one, two, count(*) FROM a1 WHERE 0" {{} {} 0} |
||
954 | 2 "SELECT sum(two), * FROM a1, a2 WHERE three>5" {{} {} {} {} {}} |
||
955 | 3 "SELECT max(one) IS NULL, one IS NULL, two IS NULL FROM a1 WHERE two=7" { |
||
956 | 1 1 1 |
||
957 | } |
||
958 | } |
||
959 | |||
960 | # EVIDENCE-OF: R-64138-28774 An aggregate query without a GROUP BY |
||
961 | # clause always returns exactly one row of data, even if there are zero |
||
962 | # rows of input data. |
||
963 | # |
||
964 | foreach {tn select} { |
||
965 | 8.1 "SELECT count(*) FROM a1" |
||
966 | 8.2 "SELECT count(*) FROM a1 WHERE 0" |
||
967 | 8.3 "SELECT count(*) FROM a1 WHERE 1" |
||
968 | 8.4 "SELECT max(a1.one)+min(two), a1.one, two, * FROM a1, a2 WHERE 1" |
||
969 | 8.5 "SELECT max(a1.one)+min(two), a1.one, two, * FROM a1, a2 WHERE 0" |
||
970 | } { |
||
971 | # Set $nRow to the number of rows returned by $select: |
||
972 | set ::stmt [sqlite3_prepare_v2 db $select -1 DUMMY] |
||
973 | set nRow 0 |
||
974 | while {"SQLITE_ROW" == [sqlite3_step $::stmt]} { incr nRow } |
||
975 | set rc [sqlite3_finalize $::stmt] |
||
976 | |||
977 | # Test that $nRow==1 and that statement execution was successful |
||
978 | # (rc==SQLITE_OK). |
||
979 | do_test e_select-4.$tn [list list $rc $nRow] {SQLITE_OK 1} |
||
980 | } |
||
981 | |||
982 | drop_all_tables |
||
983 | do_execsql_test e_select-4.9.0 { |
||
984 | CREATE TABLE b1(one PRIMARY KEY, two); |
||
985 | INSERT INTO b1 VALUES(1, 'o'); |
||
986 | INSERT INTO b1 VALUES(4, 'f'); |
||
987 | INSERT INTO b1 VALUES(3, 't'); |
||
988 | INSERT INTO b1 VALUES(2, 't'); |
||
989 | INSERT INTO b1 VALUES(5, 'f'); |
||
990 | INSERT INTO b1 VALUES(7, 's'); |
||
991 | INSERT INTO b1 VALUES(6, 's'); |
||
992 | |||
993 | CREATE TABLE b2(x, y); |
||
994 | INSERT INTO b2 VALUES(NULL, 0); |
||
995 | INSERT INTO b2 VALUES(NULL, 1); |
||
996 | INSERT INTO b2 VALUES('xyz', 2); |
||
997 | INSERT INTO b2 VALUES('abc', 3); |
||
998 | INSERT INTO b2 VALUES('xyz', 4); |
||
999 | |||
1000 | CREATE TABLE b3(a COLLATE nocase, b COLLATE binary); |
||
1001 | INSERT INTO b3 VALUES('abc', 'abc'); |
||
1002 | INSERT INTO b3 VALUES('aBC', 'aBC'); |
||
1003 | INSERT INTO b3 VALUES('Def', 'Def'); |
||
1004 | INSERT INTO b3 VALUES('dEF', 'dEF'); |
||
1005 | } {} |
||
1006 | |||
1007 | # EVIDENCE-OF: R-57754-57109 If the SELECT statement is an aggregate |
||
1008 | # query with a GROUP BY clause, then each of the expressions specified |
||
1009 | # as part of the GROUP BY clause is evaluated for each row of the |
||
1010 | # dataset. Each row is then assigned to a "group" based on the results; |
||
1011 | # rows for which the results of evaluating the GROUP BY expressions are |
||
1012 | # the same are assigned to the same group. |
||
1013 | # |
||
1014 | # These tests also show that the following is not untrue: |
||
1015 | # |
||
1016 | # EVIDENCE-OF: R-25883-55063 The expressions in the GROUP BY clause do |
||
1017 | # not have to be expressions that appear in the result. |
||
1018 | # |
||
1019 | do_select_tests e_select-4.9 { |
||
1020 | 1 "SELECT group_concat(one), two FROM b1 GROUP BY two" { |
||
1021 | 4,5 f 1 o 7,6 s 3,2 t |
||
1022 | } |
||
1023 | 2 "SELECT group_concat(one), sum(one) FROM b1 GROUP BY (one>4)" { |
||
1024 | 1,4,3,2 10 5,7,6 18 |
||
1025 | } |
||
1026 | 3 "SELECT group_concat(one) FROM b1 GROUP BY (two>'o'), one%2" { |
||
1027 | 4 1,5 2,6 3,7 |
||
1028 | } |
||
1029 | 4 "SELECT group_concat(one) FROM b1 GROUP BY (one==2 OR two=='o')" { |
||
1030 | 4,3,5,7,6 1,2 |
||
1031 | } |
||
1032 | } |
||
1033 | |||
1034 | # EVIDENCE-OF: R-14926-50129 For the purposes of grouping rows, NULL |
||
1035 | # values are considered equal. |
||
1036 | # |
||
1037 | do_select_tests e_select-4.10 { |
||
1038 | 1 "SELECT group_concat(y) FROM b2 GROUP BY x" {0,1 3 2,4} |
||
1039 | 2 "SELECT count(*) FROM b2 GROUP BY CASE WHEN y<4 THEN NULL ELSE 0 END" {4 1} |
||
1040 | } |
||
1041 | |||
1042 | # EVIDENCE-OF: R-10470-30318 The usual rules for selecting a collation |
||
1043 | # sequence with which to compare text values apply when evaluating |
||
1044 | # expressions in a GROUP BY clause. |
||
1045 | # |
||
1046 | do_select_tests e_select-4.11 { |
||
1047 | 1 "SELECT count(*) FROM b3 GROUP BY b" {1 1 1 1} |
||
1048 | 2 "SELECT count(*) FROM b3 GROUP BY a" {2 2} |
||
1049 | 3 "SELECT count(*) FROM b3 GROUP BY +b" {1 1 1 1} |
||
1050 | 4 "SELECT count(*) FROM b3 GROUP BY +a" {2 2} |
||
1051 | 5 "SELECT count(*) FROM b3 GROUP BY b||''" {1 1 1 1} |
||
1052 | 6 "SELECT count(*) FROM b3 GROUP BY a||''" {1 1 1 1} |
||
1053 | } |
||
1054 | |||
1055 | # EVIDENCE-OF: R-63573-50730 The expressions in a GROUP BY clause may |
||
1056 | # not be aggregate expressions. |
||
1057 | # |
||
1058 | foreach {tn select} { |
||
1059 | 12.1 "SELECT * FROM b3 GROUP BY count(*)" |
||
1060 | 12.2 "SELECT max(a) FROM b3 GROUP BY max(b)" |
||
1061 | 12.3 "SELECT group_concat(a) FROM b3 GROUP BY a, max(b)" |
||
1062 | } { |
||
1063 | set res {1 {aggregate functions are not allowed in the GROUP BY clause}} |
||
1064 | do_catchsql_test e_select-4.$tn $select $res |
||
1065 | } |
||
1066 | |||
1067 | # EVIDENCE-OF: R-31537-00101 If a HAVING clause is specified, it is |
||
1068 | # evaluated once for each group of rows as a boolean expression. If the |
||
1069 | # result of evaluating the HAVING clause is false, the group is |
||
1070 | # discarded. |
||
1071 | # |
||
1072 | # This requirement is tested by all e_select-4.13.* tests. |
||
1073 | # |
||
1074 | # EVIDENCE-OF: R-04132-09474 If the HAVING clause is an aggregate |
||
1075 | # expression, it is evaluated across all rows in the group. |
||
1076 | # |
||
1077 | # Tested by e_select-4.13.1.* |
||
1078 | # |
||
1079 | # EVIDENCE-OF: R-28262-47447 If a HAVING clause is a non-aggregate |
||
1080 | # expression, it is evaluated with respect to an arbitrarily selected |
||
1081 | # row from the group. |
||
1082 | # |
||
1083 | # Tested by e_select-4.13.2.* |
||
1084 | # |
||
1085 | # Tests in this block also show that this is not untrue: |
||
1086 | # |
||
1087 | # EVIDENCE-OF: R-55403-13450 The HAVING expression may refer to values, |
||
1088 | # even aggregate functions, that are not in the result. |
||
1089 | # |
||
1090 | do_execsql_test e_select-4.13.0 { |
||
1091 | CREATE TABLE c1(up, down); |
||
1092 | INSERT INTO c1 VALUES('x', 1); |
||
1093 | INSERT INTO c1 VALUES('x', 2); |
||
1094 | INSERT INTO c1 VALUES('x', 4); |
||
1095 | INSERT INTO c1 VALUES('x', 8); |
||
1096 | INSERT INTO c1 VALUES('y', 16); |
||
1097 | INSERT INTO c1 VALUES('y', 32); |
||
1098 | |||
1099 | CREATE TABLE c2(i, j); |
||
1100 | INSERT INTO c2 VALUES(1, 0); |
||
1101 | INSERT INTO c2 VALUES(2, 1); |
||
1102 | INSERT INTO c2 VALUES(3, 3); |
||
1103 | INSERT INTO c2 VALUES(4, 6); |
||
1104 | INSERT INTO c2 VALUES(5, 10); |
||
1105 | INSERT INTO c2 VALUES(6, 15); |
||
1106 | INSERT INTO c2 VALUES(7, 21); |
||
1107 | INSERT INTO c2 VALUES(8, 28); |
||
1108 | INSERT INTO c2 VALUES(9, 36); |
||
1109 | |||
1110 | CREATE TABLE c3(i PRIMARY KEY, k TEXT); |
||
1111 | INSERT INTO c3 VALUES(1, 'hydrogen'); |
||
1112 | INSERT INTO c3 VALUES(2, 'helium'); |
||
1113 | INSERT INTO c3 VALUES(3, 'lithium'); |
||
1114 | INSERT INTO c3 VALUES(4, 'beryllium'); |
||
1115 | INSERT INTO c3 VALUES(5, 'boron'); |
||
1116 | INSERT INTO c3 VALUES(94, 'plutonium'); |
||
1117 | } {} |
||
1118 | |||
1119 | do_select_tests e_select-4.13 { |
||
1120 | 1.1 "SELECT up FROM c1 GROUP BY up HAVING count(*)>3" {x} |
||
1121 | 1.2 "SELECT up FROM c1 GROUP BY up HAVING sum(down)>16" {y} |
||
1122 | 1.3 "SELECT up FROM c1 GROUP BY up HAVING sum(down)<16" {x} |
||
1123 | 1.4 "SELECT up||down FROM c1 GROUP BY (down<5) HAVING max(down)<10" {x4} |
||
1124 | |||
1125 | 2.1 "SELECT up FROM c1 GROUP BY up HAVING down>10" {y} |
||
1126 | 2.2 "SELECT up FROM c1 GROUP BY up HAVING up='y'" {y} |
||
1127 | |||
1128 | 2.3 "SELECT i, j FROM c2 GROUP BY i>4 HAVING i>6" {9 36} |
||
1129 | } |
||
1130 | |||
1131 | # EVIDENCE-OF: R-23927-54081 Each expression in the result-set is then |
||
1132 | # evaluated once for each group of rows. |
||
1133 | # |
||
1134 | # EVIDENCE-OF: R-53735-47017 If the expression is an aggregate |
||
1135 | # expression, it is evaluated across all rows in the group. |
||
1136 | # |
||
1137 | do_select_tests e_select-4.15 { |
||
1138 | 1 "SELECT sum(down) FROM c1 GROUP BY up" {15 48} |
||
1139 | 2 "SELECT sum(j), max(j) FROM c2 GROUP BY (i%3)" {54 36 27 21 39 28} |
||
1140 | 3 "SELECT sum(j), max(j) FROM c2 GROUP BY (j%2)" {80 36 40 21} |
||
1141 | 4 "SELECT 1+sum(j), max(j)+1 FROM c2 GROUP BY (j%2)" {81 37 41 22} |
||
1142 | 5 "SELECT count(*), round(avg(i),2) FROM c1, c2 ON (i=down) GROUP BY j%2" |
||
1143 | {3 4.33 1 2.0} |
||
1144 | } |
||
1145 | |||
1146 | # EVIDENCE-OF: R-62913-19830 Otherwise, it is evaluated against a single |
||
1147 | # arbitrarily chosen row from within the group. |
||
1148 | # |
||
1149 | # EVIDENCE-OF: R-53924-08809 If there is more than one non-aggregate |
||
1150 | # expression in the result-set, then all such expressions are evaluated |
||
1151 | # for the same row. |
||
1152 | # |
||
1153 | do_select_tests e_select-4.15 { |
||
1154 | 1 "SELECT i, j FROM c2 GROUP BY i%2" {8 28 9 36} |
||
1155 | 2 "SELECT i, j FROM c2 GROUP BY i%2 HAVING j<30" {8 28} |
||
1156 | 3 "SELECT i, j FROM c2 GROUP BY i%2 HAVING j>30" {9 36} |
||
1157 | 4 "SELECT i, j FROM c2 GROUP BY i%2 HAVING j>30" {9 36} |
||
1158 | 5 "SELECT count(*), i, k FROM c2 NATURAL JOIN c3 GROUP BY substr(k, 1, 1)" |
||
1159 | {2 5 boron 2 2 helium 1 3 lithium} |
||
1160 | } |
||
1161 | |||
1162 | # EVIDENCE-OF: R-19334-12811 Each group of input dataset rows |
||
1163 | # contributes a single row to the set of result rows. |
||
1164 | # |
||
1165 | # EVIDENCE-OF: R-02223-49279 Subject to filtering associated with the |
||
1166 | # DISTINCT keyword, the number of rows returned by an aggregate query |
||
1167 | # with a GROUP BY clause is the same as the number of groups of rows |
||
1168 | # produced by applying the GROUP BY and HAVING clauses to the filtered |
||
1169 | # input dataset. |
||
1170 | # |
||
1171 | do_select_tests e_select.4.16 -count { |
||
1172 | 1 "SELECT i, j FROM c2 GROUP BY i%2" 2 |
||
1173 | 2 "SELECT i, j FROM c2 GROUP BY i" 9 |
||
1174 | 3 "SELECT i, j FROM c2 GROUP BY i HAVING i<5" 4 |
||
1175 | } |
||
1176 | |||
1177 | #------------------------------------------------------------------------- |
||
1178 | # The following tests attempt to verify statements made regarding the ALL |
||
1179 | # and DISTINCT keywords. |
||
1180 | # |
||
1181 | drop_all_tables |
||
1182 | do_execsql_test e_select-5.1.0 { |
||
1183 | CREATE TABLE h1(a, b); |
||
1184 | INSERT INTO h1 VALUES(1, 'one'); |
||
1185 | INSERT INTO h1 VALUES(1, 'I'); |
||
1186 | INSERT INTO h1 VALUES(1, 'i'); |
||
1187 | INSERT INTO h1 VALUES(4, 'four'); |
||
1188 | INSERT INTO h1 VALUES(4, 'IV'); |
||
1189 | INSERT INTO h1 VALUES(4, 'iv'); |
||
1190 | |||
1191 | CREATE TABLE h2(x COLLATE nocase); |
||
1192 | INSERT INTO h2 VALUES('One'); |
||
1193 | INSERT INTO h2 VALUES('Two'); |
||
1194 | INSERT INTO h2 VALUES('Three'); |
||
1195 | INSERT INTO h2 VALUES('Four'); |
||
1196 | INSERT INTO h2 VALUES('one'); |
||
1197 | INSERT INTO h2 VALUES('two'); |
||
1198 | INSERT INTO h2 VALUES('three'); |
||
1199 | INSERT INTO h2 VALUES('four'); |
||
1200 | |||
1201 | CREATE TABLE h3(c, d); |
||
1202 | INSERT INTO h3 VALUES(1, NULL); |
||
1203 | INSERT INTO h3 VALUES(2, NULL); |
||
1204 | INSERT INTO h3 VALUES(3, NULL); |
||
1205 | INSERT INTO h3 VALUES(4, '2'); |
||
1206 | INSERT INTO h3 VALUES(5, NULL); |
||
1207 | INSERT INTO h3 VALUES(6, '2,3'); |
||
1208 | INSERT INTO h3 VALUES(7, NULL); |
||
1209 | INSERT INTO h3 VALUES(8, '2,4'); |
||
1210 | INSERT INTO h3 VALUES(9, '3'); |
||
1211 | } {} |
||
1212 | |||
1213 | # EVIDENCE-OF: R-60770-10612 One of the ALL or DISTINCT keywords may |
||
1214 | # follow the SELECT keyword in a simple SELECT statement. |
||
1215 | # |
||
1216 | do_select_tests e_select-5.1 { |
||
1217 | 1 "SELECT ALL a FROM h1" {1 1 1 4 4 4} |
||
1218 | 2 "SELECT DISTINCT a FROM h1" {1 4} |
||
1219 | } |
||
1220 | |||
1221 | # EVIDENCE-OF: R-08861-34280 If the simple SELECT is a SELECT ALL, then |
||
1222 | # the entire set of result rows are returned by the SELECT. |
||
1223 | # |
||
1224 | # EVIDENCE-OF: R-47911-02086 If neither ALL or DISTINCT are present, |
||
1225 | # then the behaviour is as if ALL were specified. |
||
1226 | # |
||
1227 | # EVIDENCE-OF: R-14442-41305 If the simple SELECT is a SELECT DISTINCT, |
||
1228 | # then duplicate rows are removed from the set of result rows before it |
||
1229 | # is returned. |
||
1230 | # |
||
1231 | # The three testable statements above are tested by e_select-5.2.*, |
||
1232 | # 5.3.* and 5.4.* respectively. |
||
1233 | # |
||
1234 | do_select_tests e_select-5 { |
||
1235 | 3.1 "SELECT ALL x FROM h2" {One Two Three Four one two three four} |
||
1236 | 3.2 "SELECT ALL x FROM h1, h2 ON (x=b)" {One one Four four} |
||
1237 | |||
1238 | 3.1 "SELECT x FROM h2" {One Two Three Four one two three four} |
||
1239 | 3.2 "SELECT x FROM h1, h2 ON (x=b)" {One one Four four} |
||
1240 | |||
1241 | 4.1 "SELECT DISTINCT x FROM h2" {four one three two} |
||
1242 | 4.2 "SELECT DISTINCT x FROM h1, h2 ON (x=b)" {four one} |
||
1243 | } |
||
1244 | |||
1245 | # EVIDENCE-OF: R-02054-15343 For the purposes of detecting duplicate |
||
1246 | # rows, two NULL values are considered to be equal. |
||
1247 | # |
||
1248 | do_select_tests e_select-5.5 { |
||
1249 | 1 "SELECT DISTINCT d FROM h3" {{} 2 2,3 2,4 3} |
||
1250 | } |
||
1251 | |||
1252 | # EVIDENCE-OF: R-58359-52112 The normal rules for selecting a collation |
||
1253 | # sequence to compare text values with apply. |
||
1254 | # |
||
1255 | do_select_tests e_select-5.6 { |
||
1256 | 1 "SELECT DISTINCT b FROM h1" {I IV four i iv one} |
||
1257 | 2 "SELECT DISTINCT b COLLATE nocase FROM h1" {four i iv one} |
||
1258 | 3 "SELECT DISTINCT x FROM h2" {four one three two} |
||
1259 | 4 "SELECT DISTINCT x COLLATE binary FROM h2" { |
||
1260 | Four One Three Two four one three two |
||
1261 | } |
||
1262 | } |
||
1263 | |||
1264 | #------------------------------------------------------------------------- |
||
1265 | # The following tests - e_select-7.* - test that statements made to do |
||
1266 | # with compound SELECT statements are correct. |
||
1267 | # |
||
1268 | |||
1269 | # EVIDENCE-OF: R-39368-64333 In a compound SELECT, all the constituent |
||
1270 | # SELECTs must return the same number of result columns. |
||
1271 | # |
||
1272 | # All the other tests in this section use compound SELECTs created |
||
1273 | # using component SELECTs that do return the same number of columns. |
||
1274 | # So the tests here just show that it is an error to attempt otherwise. |
||
1275 | # |
||
1276 | drop_all_tables |
||
1277 | do_execsql_test e_select-7.1.0 { |
||
1278 | CREATE TABLE j1(a, b, c); |
||
1279 | CREATE TABLE j2(e, f); |
||
1280 | CREATE TABLE j3(g); |
||
1281 | } {} |
||
1282 | do_select_tests e_select-7.1 -error { |
||
1283 | SELECTs to the left and right of %s do not have the same number of result columns |
||
1284 | } { |
||
1285 | 1 "SELECT a, b FROM j1 UNION ALL SELECT g FROM j3" {{UNION ALL}} |
||
1286 | 2 "SELECT * FROM j1 UNION ALL SELECT * FROM j3" {{UNION ALL}} |
||
1287 | 3 "SELECT a, b FROM j1 UNION ALL SELECT g FROM j3" {{UNION ALL}} |
||
1288 | 4 "SELECT a, b FROM j1 UNION ALL SELECT * FROM j3,j2" {{UNION ALL}} |
||
1289 | 5 "SELECT * FROM j3,j2 UNION ALL SELECT a, b FROM j1" {{UNION ALL}} |
||
1290 | |||
1291 | 6 "SELECT a, b FROM j1 UNION SELECT g FROM j3" {UNION} |
||
1292 | 7 "SELECT * FROM j1 UNION SELECT * FROM j3" {UNION} |
||
1293 | 8 "SELECT a, b FROM j1 UNION SELECT g FROM j3" {UNION} |
||
1294 | 9 "SELECT a, b FROM j1 UNION SELECT * FROM j3,j2" {UNION} |
||
1295 | 10 "SELECT * FROM j3,j2 UNION SELECT a, b FROM j1" {UNION} |
||
1296 | |||
1297 | 11 "SELECT a, b FROM j1 INTERSECT SELECT g FROM j3" {INTERSECT} |
||
1298 | 12 "SELECT * FROM j1 INTERSECT SELECT * FROM j3" {INTERSECT} |
||
1299 | 13 "SELECT a, b FROM j1 INTERSECT SELECT g FROM j3" {INTERSECT} |
||
1300 | 14 "SELECT a, b FROM j1 INTERSECT SELECT * FROM j3,j2" {INTERSECT} |
||
1301 | 15 "SELECT * FROM j3,j2 INTERSECT SELECT a, b FROM j1" {INTERSECT} |
||
1302 | |||
1303 | 16 "SELECT a, b FROM j1 EXCEPT SELECT g FROM j3" {EXCEPT} |
||
1304 | 17 "SELECT * FROM j1 EXCEPT SELECT * FROM j3" {EXCEPT} |
||
1305 | 18 "SELECT a, b FROM j1 EXCEPT SELECT g FROM j3" {EXCEPT} |
||
1306 | 19 "SELECT a, b FROM j1 EXCEPT SELECT * FROM j3,j2" {EXCEPT} |
||
1307 | 20 "SELECT * FROM j3,j2 EXCEPT SELECT a, b FROM j1" {EXCEPT} |
||
1308 | } |
||
1309 | |||
1310 | # EVIDENCE-OF: R-01450-11152 As the components of a compound SELECT must |
||
1311 | # be simple SELECT statements, they may not contain ORDER BY or LIMIT |
||
1312 | # clauses. |
||
1313 | # |
||
1314 | foreach {tn select op1 op2} { |
||
1315 | 1 "SELECT * FROM j1 ORDER BY a UNION ALL SELECT * FROM j2,j3" |
||
1316 | {ORDER BY} {UNION ALL} |
||
1317 | 2 "SELECT count(*) FROM j1 ORDER BY 1 UNION ALL SELECT max(e) FROM j2" |
||
1318 | {ORDER BY} {UNION ALL} |
||
1319 | 3 "SELECT count(*), * FROM j1 ORDER BY 1,2,3 UNION ALL SELECT *,* FROM j2" |
||
1320 | {ORDER BY} {UNION ALL} |
||
1321 | 4 "SELECT * FROM j1 LIMIT 10 UNION ALL SELECT * FROM j2,j3" |
||
1322 | LIMIT {UNION ALL} |
||
1323 | 5 "SELECT * FROM j1 LIMIT 10 OFFSET 5 UNION ALL SELECT * FROM j2,j3" |
||
1324 | LIMIT {UNION ALL} |
||
1325 | 6 "SELECT a FROM j1 LIMIT (SELECT e FROM j2) UNION ALL SELECT g FROM j2,j3" |
||
1326 | LIMIT {UNION ALL} |
||
1327 | |||
1328 | 7 "SELECT * FROM j1 ORDER BY a UNION SELECT * FROM j2,j3" |
||
1329 | {ORDER BY} {UNION} |
||
1330 | 8 "SELECT count(*) FROM j1 ORDER BY 1 UNION SELECT max(e) FROM j2" |
||
1331 | {ORDER BY} {UNION} |
||
1332 | 9 "SELECT count(*), * FROM j1 ORDER BY 1,2,3 UNION SELECT *,* FROM j2" |
||
1333 | {ORDER BY} {UNION} |
||
1334 | 10 "SELECT * FROM j1 LIMIT 10 UNION SELECT * FROM j2,j3" |
||
1335 | LIMIT {UNION} |
||
1336 | 11 "SELECT * FROM j1 LIMIT 10 OFFSET 5 UNION SELECT * FROM j2,j3" |
||
1337 | LIMIT {UNION} |
||
1338 | 12 "SELECT a FROM j1 LIMIT (SELECT e FROM j2) UNION SELECT g FROM j2,j3" |
||
1339 | LIMIT {UNION} |
||
1340 | |||
1341 | 13 "SELECT * FROM j1 ORDER BY a EXCEPT SELECT * FROM j2,j3" |
||
1342 | {ORDER BY} {EXCEPT} |
||
1343 | 14 "SELECT count(*) FROM j1 ORDER BY 1 EXCEPT SELECT max(e) FROM j2" |
||
1344 | {ORDER BY} {EXCEPT} |
||
1345 | 15 "SELECT count(*), * FROM j1 ORDER BY 1,2,3 EXCEPT SELECT *,* FROM j2" |
||
1346 | {ORDER BY} {EXCEPT} |
||
1347 | 16 "SELECT * FROM j1 LIMIT 10 EXCEPT SELECT * FROM j2,j3" |
||
1348 | LIMIT {EXCEPT} |
||
1349 | 17 "SELECT * FROM j1 LIMIT 10 OFFSET 5 EXCEPT SELECT * FROM j2,j3" |
||
1350 | LIMIT {EXCEPT} |
||
1351 | 18 "SELECT a FROM j1 LIMIT (SELECT e FROM j2) EXCEPT SELECT g FROM j2,j3" |
||
1352 | LIMIT {EXCEPT} |
||
1353 | |||
1354 | 19 "SELECT * FROM j1 ORDER BY a INTERSECT SELECT * FROM j2,j3" |
||
1355 | {ORDER BY} {INTERSECT} |
||
1356 | 20 "SELECT count(*) FROM j1 ORDER BY 1 INTERSECT SELECT max(e) FROM j2" |
||
1357 | {ORDER BY} {INTERSECT} |
||
1358 | 21 "SELECT count(*), * FROM j1 ORDER BY 1,2,3 INTERSECT SELECT *,* FROM j2" |
||
1359 | {ORDER BY} {INTERSECT} |
||
1360 | 22 "SELECT * FROM j1 LIMIT 10 INTERSECT SELECT * FROM j2,j3" |
||
1361 | LIMIT {INTERSECT} |
||
1362 | 23 "SELECT * FROM j1 LIMIT 10 OFFSET 5 INTERSECT SELECT * FROM j2,j3" |
||
1363 | LIMIT {INTERSECT} |
||
1364 | 24 "SELECT a FROM j1 LIMIT (SELECT e FROM j2) INTERSECT SELECT g FROM j2,j3" |
||
1365 | LIMIT {INTERSECT} |
||
1366 | } { |
||
1367 | set err "$op1 clause should come after $op2 not before" |
||
1368 | do_catchsql_test e_select-7.2.$tn $select [list 1 $err] |
||
1369 | } |
||
1370 | |||
1371 | # EVIDENCE-OF: R-22874-32655 ORDER BY and LIMIT clauses may only occur |
||
1372 | # at the end of the entire compound SELECT. |
||
1373 | # |
||
1374 | foreach {tn select} { |
||
1375 | 1 "SELECT * FROM j1 UNION ALL SELECT * FROM j2,j3 ORDER BY a" |
||
1376 | 2 "SELECT count(*) FROM j1 UNION ALL SELECT max(e) FROM j2 ORDER BY 1" |
||
1377 | 3 "SELECT count(*), * FROM j1 UNION ALL SELECT *,* FROM j2 ORDER BY 1,2,3" |
||
1378 | 4 "SELECT * FROM j1 UNION ALL SELECT * FROM j2,j3 LIMIT 10" |
||
1379 | 5 "SELECT * FROM j1 UNION ALL SELECT * FROM j2,j3 LIMIT 10 OFFSET 5" |
||
1380 | 6 "SELECT a FROM j1 UNION ALL SELECT g FROM j2,j3 LIMIT (SELECT 10)" |
||
1381 | |||
1382 | 7 "SELECT * FROM j1 UNION SELECT * FROM j2,j3 ORDER BY a" |
||
1383 | 8 "SELECT count(*) FROM j1 UNION SELECT max(e) FROM j2 ORDER BY 1" |
||
1384 | 9 "SELECT count(*), * FROM j1 UNION SELECT *,* FROM j2 ORDER BY 1,2,3" |
||
1385 | 10 "SELECT * FROM j1 UNION SELECT * FROM j2,j3 LIMIT 10" |
||
1386 | 11 "SELECT * FROM j1 UNION SELECT * FROM j2,j3 LIMIT 10 OFFSET 5" |
||
1387 | 12 "SELECT a FROM j1 UNION SELECT g FROM j2,j3 LIMIT (SELECT 10)" |
||
1388 | |||
1389 | 13 "SELECT * FROM j1 EXCEPT SELECT * FROM j2,j3 ORDER BY a" |
||
1390 | 14 "SELECT count(*) FROM j1 EXCEPT SELECT max(e) FROM j2 ORDER BY 1" |
||
1391 | 15 "SELECT count(*), * FROM j1 EXCEPT SELECT *,* FROM j2 ORDER BY 1,2,3" |
||
1392 | 16 "SELECT * FROM j1 EXCEPT SELECT * FROM j2,j3 LIMIT 10" |
||
1393 | 17 "SELECT * FROM j1 EXCEPT SELECT * FROM j2,j3 LIMIT 10 OFFSET 5" |
||
1394 | 18 "SELECT a FROM j1 EXCEPT SELECT g FROM j2,j3 LIMIT (SELECT 10)" |
||
1395 | |||
1396 | 19 "SELECT * FROM j1 INTERSECT SELECT * FROM j2,j3 ORDER BY a" |
||
1397 | 20 "SELECT count(*) FROM j1 INTERSECT SELECT max(e) FROM j2 ORDER BY 1" |
||
1398 | 21 "SELECT count(*), * FROM j1 INTERSECT SELECT *,* FROM j2 ORDER BY 1,2,3" |
||
1399 | 22 "SELECT * FROM j1 INTERSECT SELECT * FROM j2,j3 LIMIT 10" |
||
1400 | 23 "SELECT * FROM j1 INTERSECT SELECT * FROM j2,j3 LIMIT 10 OFFSET 5" |
||
1401 | 24 "SELECT a FROM j1 INTERSECT SELECT g FROM j2,j3 LIMIT (SELECT 10)" |
||
1402 | } { |
||
1403 | do_test e_select-7.3.$tn { catch {execsql $select} msg } 0 |
||
1404 | } |
||
1405 | |||
1406 | # EVIDENCE-OF: R-08531-36543 A compound SELECT created using UNION ALL |
||
1407 | # operator returns all the rows from the SELECT to the left of the UNION |
||
1408 | # ALL operator, and all the rows from the SELECT to the right of it. |
||
1409 | # |
||
1410 | drop_all_tables |
||
1411 | do_execsql_test e_select-7.4.0 { |
||
1412 | CREATE TABLE q1(a TEXT, b INTEGER, c); |
||
1413 | CREATE TABLE q2(d NUMBER, e BLOB); |
||
1414 | CREATE TABLE q3(f REAL, g); |
||
1415 | |||
1416 | INSERT INTO q1 VALUES(16, -87.66, NULL); |
||
1417 | INSERT INTO q1 VALUES('legible', 94, -42.47); |
||
1418 | INSERT INTO q1 VALUES('beauty', 36, NULL); |
||
1419 | |||
1420 | INSERT INTO q2 VALUES('legible', 1); |
||
1421 | INSERT INTO q2 VALUES('beauty', 2); |
||
1422 | INSERT INTO q2 VALUES(-65.91, 4); |
||
1423 | INSERT INTO q2 VALUES('emanating', -16.56); |
||
1424 | |||
1425 | INSERT INTO q3 VALUES('beauty', 2); |
||
1426 | INSERT INTO q3 VALUES('beauty', 2); |
||
1427 | } {} |
||
1428 | do_select_tests e_select-7.4 { |
||
1429 | 1 {SELECT a FROM q1 UNION ALL SELECT d FROM q2} |
||
1430 | {16 legible beauty legible beauty -65.91 emanating} |
||
1431 | |||
1432 | 2 {SELECT * FROM q1 WHERE a=16 UNION ALL SELECT 'x', * FROM q2 WHERE oid=1} |
||
1433 | {16 -87.66 {} x legible 1} |
||
1434 | |||
1435 | 3 {SELECT count(*) FROM q1 UNION ALL SELECT min(e) FROM q2} |
||
1436 | {3 -16.56} |
||
1437 | |||
1438 | 4 {SELECT * FROM q2 UNION ALL SELECT * FROM q3} |
||
1439 | {legible 1 beauty 2 -65.91 4 emanating -16.56 beauty 2 beauty 2} |
||
1440 | } |
||
1441 | |||
1442 | # EVIDENCE-OF: R-20560-39162 The UNION operator works the same way as |
||
1443 | # UNION ALL, except that duplicate rows are removed from the final |
||
1444 | # result set. |
||
1445 | # |
||
1446 | do_select_tests e_select-7.5 { |
||
1447 | 1 {SELECT a FROM q1 UNION SELECT d FROM q2} |
||
1448 | {-65.91 16 beauty emanating legible} |
||
1449 | |||
1450 | 2 {SELECT * FROM q1 WHERE a=16 UNION SELECT 'x', * FROM q2 WHERE oid=1} |
||
1451 | {16 -87.66 {} x legible 1} |
||
1452 | |||
1453 | 3 {SELECT count(*) FROM q1 UNION SELECT min(e) FROM q2} |
||
1454 | {-16.56 3} |
||
1455 | |||
1456 | 4 {SELECT * FROM q2 UNION SELECT * FROM q3} |
||
1457 | {-65.91 4 beauty 2 emanating -16.56 legible 1} |
||
1458 | } |
||
1459 | |||
1460 | # EVIDENCE-OF: R-45764-31737 The INTERSECT operator returns the |
||
1461 | # intersection of the results of the left and right SELECTs. |
||
1462 | # |
||
1463 | do_select_tests e_select-7.6 { |
||
1464 | 1 {SELECT a FROM q1 INTERSECT SELECT d FROM q2} {beauty legible} |
||
1465 | 2 {SELECT * FROM q2 INTERSECT SELECT * FROM q3} {beauty 2} |
||
1466 | } |
||
1467 | |||
1468 | # EVIDENCE-OF: R-25787-28949 The EXCEPT operator returns the subset of |
||
1469 | # rows returned by the left SELECT that are not also returned by the |
||
1470 | # right-hand SELECT. |
||
1471 | # |
||
1472 | do_select_tests e_select-7.7 { |
||
1473 | 1 {SELECT a FROM q1 EXCEPT SELECT d FROM q2} {16} |
||
1474 | |||
1475 | 2 {SELECT * FROM q2 EXCEPT SELECT * FROM q3} |
||
1476 | {-65.91 4 emanating -16.56 legible 1} |
||
1477 | } |
||
1478 | |||
1479 | # EVIDENCE-OF: R-40729-56447 Duplicate rows are removed from the results |
||
1480 | # of INTERSECT and EXCEPT operators before the result set is returned. |
||
1481 | # |
||
1482 | do_select_tests e_select-7.8 { |
||
1483 | |||
1484 | |||
1485 | 1 {SELECT * FROM q3 INTERSECT SELECT * FROM q3} {beauty 2} |
||
1486 | 2 {SELECT * FROM q3 EXCEPT SELECT a,b FROM q1} {beauty 2} |
||
1487 | } |
||
1488 | |||
1489 | # EVIDENCE-OF: R-46765-43362 For the purposes of determining duplicate |
||
1490 | # rows for the results of compound SELECT operators, NULL values are |
||
1491 | # considered equal to other NULL values and distinct from all non-NULL |
||
1492 | # values. |
||
1493 | # |
||
1494 | db nullvalue null |
||
1495 | do_select_tests e_select-7.9 { |
||
1496 | 1 {SELECT NULL UNION ALL SELECT NULL} {null null} |
||
1497 | 2 {SELECT NULL UNION SELECT NULL} {null} |
||
1498 | 3 {SELECT NULL INTERSECT SELECT NULL} {null} |
||
1499 | 4 {SELECT NULL EXCEPT SELECT NULL} {} |
||
1500 | |||
1501 | 5 {SELECT NULL UNION ALL SELECT 'ab'} {null ab} |
||
1502 | 6 {SELECT NULL UNION SELECT 'ab'} {null ab} |
||
1503 | 7 {SELECT NULL INTERSECT SELECT 'ab'} {} |
||
1504 | 8 {SELECT NULL EXCEPT SELECT 'ab'} {null} |
||
1505 | |||
1506 | 9 {SELECT NULL UNION ALL SELECT 0} {null 0} |
||
1507 | 10 {SELECT NULL UNION SELECT 0} {null 0} |
||
1508 | 11 {SELECT NULL INTERSECT SELECT 0} {} |
||
1509 | 12 {SELECT NULL EXCEPT SELECT 0} {null} |
||
1510 | |||
1511 | 13 {SELECT c FROM q1 UNION ALL SELECT g FROM q3} {null -42.47 null 2 2} |
||
1512 | 14 {SELECT c FROM q1 UNION SELECT g FROM q3} {null -42.47 2} |
||
1513 | 15 {SELECT c FROM q1 INTERSECT SELECT g FROM q3} {} |
||
1514 | 16 {SELECT c FROM q1 EXCEPT SELECT g FROM q3} {null -42.47} |
||
1515 | } |
||
1516 | db nullvalue {} |
||
1517 | |||
1518 | # EVIDENCE-OF: R-51232-50224 The collation sequence used to compare two |
||
1519 | # text values is determined as if the columns of the left and right-hand |
||
1520 | # SELECT statements were the left and right-hand operands of the equals |
||
1521 | # (=) operator, except that greater precedence is not assigned to a |
||
1522 | # collation sequence specified with the postfix COLLATE operator. |
||
1523 | # |
||
1524 | drop_all_tables |
||
1525 | do_execsql_test e_select-7.10.0 { |
||
1526 | CREATE TABLE y1(a COLLATE nocase, b COLLATE binary, c); |
||
1527 | INSERT INTO y1 VALUES('Abc', 'abc', 'aBC'); |
||
1528 | } {} |
||
1529 | do_select_tests e_select-7.10 { |
||
1530 | 1 {SELECT 'abc' UNION SELECT 'ABC'} {ABC abc} |
||
1531 | 2 {SELECT 'abc' COLLATE nocase UNION SELECT 'ABC'} {ABC} |
||
1532 | 3 {SELECT 'abc' UNION SELECT 'ABC' COLLATE nocase} {ABC} |
||
1533 | 4 {SELECT 'abc' COLLATE binary UNION SELECT 'ABC' COLLATE nocase} {ABC abc} |
||
1534 | 5 {SELECT 'abc' COLLATE nocase UNION SELECT 'ABC' COLLATE binary} {ABC} |
||
1535 | |||
1536 | 6 {SELECT a FROM y1 UNION SELECT b FROM y1} {abc} |
||
1537 | 7 {SELECT b FROM y1 UNION SELECT a FROM y1} {Abc abc} |
||
1538 | 8 {SELECT a FROM y1 UNION SELECT c FROM y1} {aBC} |
||
1539 | |||
1540 | 9 {SELECT a FROM y1 UNION SELECT c COLLATE binary FROM y1} {aBC} |
||
1541 | } |
||
1542 | |||
1543 | # EVIDENCE-OF: R-32706-07403 No affinity transformations are applied to |
||
1544 | # any values when comparing rows as part of a compound SELECT. |
||
1545 | # |
||
1546 | drop_all_tables |
||
1547 | do_execsql_test e_select-7.10.0 { |
||
1548 | CREATE TABLE w1(a TEXT, b NUMBER); |
||
1549 | CREATE TABLE w2(a, b TEXT); |
||
1550 | |||
1551 | INSERT INTO w1 VALUES('1', 4.1); |
||
1552 | INSERT INTO w2 VALUES(1, 4.1); |
||
1553 | } {} |
||
1554 | |||
1555 | do_select_tests e_select-7.11 { |
||
1556 | 1 { SELECT a FROM w1 UNION SELECT a FROM w2 } {1 1} |
||
1557 | 2 { SELECT a FROM w2 UNION SELECT a FROM w1 } {1 1} |
||
1558 | 3 { SELECT b FROM w1 UNION SELECT b FROM w2 } {4.1 4.1} |
||
1559 | 4 { SELECT b FROM w2 UNION SELECT b FROM w1 } {4.1 4.1} |
||
1560 | |||
1561 | 5 { SELECT a FROM w1 INTERSECT SELECT a FROM w2 } {} |
||
1562 | 6 { SELECT a FROM w2 INTERSECT SELECT a FROM w1 } {} |
||
1563 | 7 { SELECT b FROM w1 INTERSECT SELECT b FROM w2 } {} |
||
1564 | 8 { SELECT b FROM w2 INTERSECT SELECT b FROM w1 } {} |
||
1565 | |||
1566 | 9 { SELECT a FROM w1 EXCEPT SELECT a FROM w2 } {1} |
||
1567 | 10 { SELECT a FROM w2 EXCEPT SELECT a FROM w1 } {1} |
||
1568 | 11 { SELECT b FROM w1 EXCEPT SELECT b FROM w2 } {4.1} |
||
1569 | 12 { SELECT b FROM w2 EXCEPT SELECT b FROM w1 } {4.1} |
||
1570 | } |
||
1571 | |||
1572 | |||
1573 | # EVIDENCE-OF: R-32562-20566 When three or more simple SELECTs are |
||
1574 | # connected into a compound SELECT, they group from left to right. In |
||
1575 | # other words, if "A", "B" and "C" are all simple SELECT statements, (A |
||
1576 | # op B op C) is processed as ((A op B) op C). |
||
1577 | # |
||
1578 | # e_select-7.12.1: Precedence of UNION vs. INTERSECT |
||
1579 | # e_select-7.12.2: Precedence of UNION vs. UNION ALL |
||
1580 | # e_select-7.12.3: Precedence of UNION vs. EXCEPT |
||
1581 | # e_select-7.12.4: Precedence of INTERSECT vs. UNION ALL |
||
1582 | # e_select-7.12.5: Precedence of INTERSECT vs. EXCEPT |
||
1583 | # e_select-7.12.6: Precedence of UNION ALL vs. EXCEPT |
||
1584 | # e_select-7.12.7: Check that "a EXCEPT b EXCEPT c" is processed as |
||
1585 | # "(a EXCEPT b) EXCEPT c". |
||
1586 | # |
||
1587 | # The INTERSECT and EXCEPT operations are mutually commutative. So |
||
1588 | # the e_select-7.12.5 test cases do not prove very much. |
||
1589 | # |
||
1590 | drop_all_tables |
||
1591 | do_execsql_test e_select-7.12.0 { |
||
1592 | CREATE TABLE t1(x); |
||
1593 | INSERT INTO t1 VALUES(1); |
||
1594 | INSERT INTO t1 VALUES(2); |
||
1595 | INSERT INTO t1 VALUES(3); |
||
1596 | } {} |
||
1597 | foreach {tn select res} { |
||
1598 | 1a "(1,2) INTERSECT (1) UNION (3)" {1 3} |
||
1599 | 1b "(3) UNION (1,2) INTERSECT (1)" {1} |
||
1600 | |||
1601 | 2a "(1,2) UNION (3) UNION ALL (1)" {1 2 3 1} |
||
1602 | 2b "(1) UNION ALL (3) UNION (1,2)" {1 2 3} |
||
1603 | |||
1604 | 3a "(1,2) UNION (3) EXCEPT (1)" {2 3} |
||
1605 | 3b "(1,2) EXCEPT (3) UNION (1)" {1 2} |
||
1606 | |||
1607 | 4a "(1,2) INTERSECT (1) UNION ALL (3)" {1 3} |
||
1608 | 4b "(3) UNION (1,2) INTERSECT (1)" {1} |
||
1609 | |||
1610 | 5a "(1,2) INTERSECT (2) EXCEPT (2)" {} |
||
1611 | 5b "(2,3) EXCEPT (2) INTERSECT (2)" {} |
||
1612 | |||
1613 | 6a "(2) UNION ALL (2) EXCEPT (2)" {} |
||
1614 | 6b "(2) EXCEPT (2) UNION ALL (2)" {2} |
||
1615 | |||
1616 | 7 "(2,3) EXCEPT (2) EXCEPT (3)" {} |
||
1617 | } { |
||
1618 | set select [string map {( {SELECT x FROM t1 WHERE x IN (}} $select] |
||
1619 | do_execsql_test e_select-7.12.$tn $select [list {*}$res] |
||
1620 | } |
||
1621 | |||
1622 | |||
1623 | #------------------------------------------------------------------------- |
||
1624 | # ORDER BY clauses |
||
1625 | # |
||
1626 | |||
1627 | drop_all_tables |
||
1628 | do_execsql_test e_select-8.1.0 { |
||
1629 | CREATE TABLE d1(x, y, z); |
||
1630 | |||
1631 | INSERT INTO d1 VALUES(1, 2, 3); |
||
1632 | INSERT INTO d1 VALUES(2, 5, -1); |
||
1633 | INSERT INTO d1 VALUES(1, 2, 8); |
||
1634 | INSERT INTO d1 VALUES(1, 2, 7); |
||
1635 | INSERT INTO d1 VALUES(2, 4, 93); |
||
1636 | INSERT INTO d1 VALUES(1, 2, -20); |
||
1637 | INSERT INTO d1 VALUES(1, 4, 93); |
||
1638 | INSERT INTO d1 VALUES(1, 5, -1); |
||
1639 | |||
1640 | CREATE TABLE d2(a, b); |
||
1641 | INSERT INTO d2 VALUES('gently', 'failings'); |
||
1642 | INSERT INTO d2 VALUES('commercials', 'bathrobe'); |
||
1643 | INSERT INTO d2 VALUES('iterate', 'sexton'); |
||
1644 | INSERT INTO d2 VALUES('babied', 'charitableness'); |
||
1645 | INSERT INTO d2 VALUES('solemnness', 'annexed'); |
||
1646 | INSERT INTO d2 VALUES('rejoicing', 'liabilities'); |
||
1647 | INSERT INTO d2 VALUES('pragmatist', 'guarded'); |
||
1648 | INSERT INTO d2 VALUES('barked', 'interrupted'); |
||
1649 | INSERT INTO d2 VALUES('reemphasizes', 'reply'); |
||
1650 | INSERT INTO d2 VALUES('lad', 'relenting'); |
||
1651 | } {} |
||
1652 | |||
1653 | # EVIDENCE-OF: R-44988-41064 Rows are first sorted based on the results |
||
1654 | # of evaluating the left-most expression in the ORDER BY list, then ties |
||
1655 | # are broken by evaluating the second left-most expression and so on. |
||
1656 | # |
||
1657 | do_select_tests e_select-8.1 { |
||
1658 | 1 "SELECT * FROM d1 ORDER BY x, y, z" { |
||
1659 | 1 2 -20 1 2 3 1 2 7 1 2 8 |
||
1660 | 1 4 93 1 5 -1 2 4 93 2 5 -1 |
||
1661 | } |
||
1662 | } |
||
1663 | |||
1664 | # EVIDENCE-OF: R-06617-54588 Each ORDER BY expression may be optionally |
||
1665 | # followed by one of the keywords ASC (smaller values are returned |
||
1666 | # first) or DESC (larger values are returned first). |
||
1667 | # |
||
1668 | # Test cases e_select-8.2.* test the above. |
||
1669 | # |
||
1670 | # EVIDENCE-OF: R-18705-33393 If neither ASC or DESC are specified, rows |
||
1671 | # are sorted in ascending (smaller values first) order by default. |
||
1672 | # |
||
1673 | # Test cases e_select-8.3.* test the above. All 8.3 test cases are |
||
1674 | # copies of 8.2 test cases with the explicit "ASC" removed. |
||
1675 | # |
||
1676 | do_select_tests e_select-8 { |
||
1677 | 2.1 "SELECT * FROM d1 ORDER BY x ASC, y ASC, z ASC" { |
||
1678 | 1 2 -20 1 2 3 1 2 7 1 2 8 |
||
1679 | 1 4 93 1 5 -1 2 4 93 2 5 -1 |
||
1680 | } |
||
1681 | 2.2 "SELECT * FROM d1 ORDER BY x DESC, y DESC, z DESC" { |
||
1682 | 2 5 -1 2 4 93 1 5 -1 1 4 93 |
||
1683 | 1 2 8 1 2 7 1 2 3 1 2 -20 |
||
1684 | } |
||
1685 | 2.3 "SELECT * FROM d1 ORDER BY x DESC, y ASC, z DESC" { |
||
1686 | 2 4 93 2 5 -1 1 2 8 1 2 7 |
||
1687 | 1 2 3 1 2 -20 1 4 93 1 5 -1 |
||
1688 | } |
||
1689 | 2.4 "SELECT * FROM d1 ORDER BY x DESC, y ASC, z ASC" { |
||
1690 | 2 4 93 2 5 -1 1 2 -20 1 2 3 |
||
1691 | 1 2 7 1 2 8 1 4 93 1 5 -1 |
||
1692 | } |
||
1693 | |||
1694 | 3.1 "SELECT * FROM d1 ORDER BY x, y, z" { |
||
1695 | 1 2 -20 1 2 3 1 2 7 1 2 8 |
||
1696 | 1 4 93 1 5 -1 2 4 93 2 5 -1 |
||
1697 | } |
||
1698 | 3.3 "SELECT * FROM d1 ORDER BY x DESC, y, z DESC" { |
||
1699 | 2 4 93 2 5 -1 1 2 8 1 2 7 |
||
1700 | 1 2 3 1 2 -20 1 4 93 1 5 -1 |
||
1701 | } |
||
1702 | 3.4 "SELECT * FROM d1 ORDER BY x DESC, y, z" { |
||
1703 | 2 4 93 2 5 -1 1 2 -20 1 2 3 |
||
1704 | 1 2 7 1 2 8 1 4 93 1 5 -1 |
||
1705 | } |
||
1706 | } |
||
1707 | |||
1708 | # EVIDENCE-OF: R-29779-04281 If the ORDER BY expression is a constant |
||
1709 | # integer K then the expression is considered an alias for the K-th |
||
1710 | # column of the result set (columns are numbered from left to right |
||
1711 | # starting with 1). |
||
1712 | # |
||
1713 | do_select_tests e_select-8.4 { |
||
1714 | 1 "SELECT * FROM d1 ORDER BY 1 ASC, 2 ASC, 3 ASC" { |
||
1715 | 1 2 -20 1 2 3 1 2 7 1 2 8 |
||
1716 | 1 4 93 1 5 -1 2 4 93 2 5 -1 |
||
1717 | } |
||
1718 | 2 "SELECT * FROM d1 ORDER BY 1 DESC, 2 DESC, 3 DESC" { |
||
1719 | 2 5 -1 2 4 93 1 5 -1 1 4 93 |
||
1720 | 1 2 8 1 2 7 1 2 3 1 2 -20 |
||
1721 | } |
||
1722 | 3 "SELECT * FROM d1 ORDER BY 1 DESC, 2 ASC, 3 DESC" { |
||
1723 | 2 4 93 2 5 -1 1 2 8 1 2 7 |
||
1724 | 1 2 3 1 2 -20 1 4 93 1 5 -1 |
||
1725 | } |
||
1726 | 4 "SELECT * FROM d1 ORDER BY 1 DESC, 2 ASC, 3 ASC" { |
||
1727 | 2 4 93 2 5 -1 1 2 -20 1 2 3 |
||
1728 | 1 2 7 1 2 8 1 4 93 1 5 -1 |
||
1729 | } |
||
1730 | 5 "SELECT * FROM d1 ORDER BY 1, 2, 3" { |
||
1731 | 1 2 -20 1 2 3 1 2 7 1 2 8 |
||
1732 | 1 4 93 1 5 -1 2 4 93 2 5 -1 |
||
1733 | } |
||
1734 | 6 "SELECT * FROM d1 ORDER BY 1 DESC, 2, 3 DESC" { |
||
1735 | 2 4 93 2 5 -1 1 2 8 1 2 7 |
||
1736 | 1 2 3 1 2 -20 1 4 93 1 5 -1 |
||
1737 | } |
||
1738 | 7 "SELECT * FROM d1 ORDER BY 1 DESC, 2, 3" { |
||
1739 | 2 4 93 2 5 -1 1 2 -20 1 2 3 |
||
1740 | 1 2 7 1 2 8 1 4 93 1 5 -1 |
||
1741 | } |
||
1742 | 8 "SELECT z, x FROM d1 ORDER BY 2" { |
||
1743 | 3 1 8 1 7 1 -20 1 |
||
1744 | 93 1 -1 1 -1 2 93 2 |
||
1745 | } |
||
1746 | 9 "SELECT z, x FROM d1 ORDER BY 1" { |
||
1747 | -20 1 -1 2 -1 1 3 1 |
||
1748 | 7 1 8 1 93 2 93 1 |
||
1749 | } |
||
1750 | } |
||
1751 | |||
1752 | # EVIDENCE-OF: R-63286-51977 If the ORDER BY expression is an identifier |
||
1753 | # that corresponds to the alias of one of the output columns, then the |
||
1754 | # expression is considered an alias for that column. |
||
1755 | # |
||
1756 | do_select_tests e_select-8.5 { |
||
1757 | 1 "SELECT z+1 AS abc FROM d1 ORDER BY abc" { |
||
1758 | -19 0 0 4 8 9 94 94 |
||
1759 | } |
||
1760 | 2 "SELECT z+1 AS abc FROM d1 ORDER BY abc DESC" { |
||
1761 | 94 94 9 8 4 0 0 -19 |
||
1762 | } |
||
1763 | 3 "SELECT z AS x, x AS z FROM d1 ORDER BY z" { |
||
1764 | 3 1 8 1 7 1 -20 1 93 1 -1 1 -1 2 93 2 |
||
1765 | } |
||
1766 | 4 "SELECT z AS x, x AS z FROM d1 ORDER BY x" { |
||
1767 | -20 1 -1 2 -1 1 3 1 7 1 8 1 93 2 93 1 |
||
1768 | } |
||
1769 | } |
||
1770 | |||
1771 | # EVIDENCE-OF: R-65068-27207 Otherwise, if the ORDER BY expression is |
||
1772 | # any other expression, it is evaluated and the returned value used to |
||
1773 | # order the output rows. |
||
1774 | # |
||
1775 | # EVIDENCE-OF: R-03421-57988 If the SELECT statement is a simple SELECT, |
||
1776 | # then an ORDER BY may contain any arbitrary expressions. |
||
1777 | # |
||
1778 | do_select_tests e_select-8.6 { |
||
1779 | 1 "SELECT * FROM d1 ORDER BY x+y+z" { |
||
1780 | 1 2 -20 1 5 -1 1 2 3 2 5 -1 |
||
1781 | 1 2 7 1 2 8 1 4 93 2 4 93 |
||
1782 | } |
||
1783 | 2 "SELECT * FROM d1 ORDER BY x*z" { |
||
1784 | 1 2 -20 2 5 -1 1 5 -1 1 2 3 |
||
1785 | 1 2 7 1 2 8 1 4 93 2 4 93 |
||
1786 | } |
||
1787 | 3 "SELECT * FROM d1 ORDER BY y*z" { |
||
1788 | 1 2 -20 2 5 -1 1 5 -1 1 2 3 |
||
1789 | 1 2 7 1 2 8 2 4 93 1 4 93 |
||
1790 | } |
||
1791 | } |
||
1792 | |||
1793 | # EVIDENCE-OF: R-28853-08147 However, if the SELECT is a compound |
||
1794 | # SELECT, then ORDER BY expressions that are not aliases to output |
||
1795 | # columns must be exactly the same as an expression used as an output |
||
1796 | # column. |
||
1797 | # |
||
1798 | do_select_tests e_select-8.7.1 -error { |
||
1799 | %s ORDER BY term does not match any column in the result set |
||
1800 | } { |
||
1801 | 1 "SELECT x FROM d1 UNION ALL SELECT a FROM d2 ORDER BY x*z" 1st |
||
1802 | 2 "SELECT x,z FROM d1 UNION ALL SELECT a,b FROM d2 ORDER BY x, x/z" 2nd |
||
1803 | } |
||
1804 | |||
1805 | do_select_tests e_select-8.7.2 { |
||
1806 | 1 "SELECT x*z FROM d1 UNION ALL SELECT a FROM d2 ORDER BY x*z" { |
||
1807 | -20 -2 -1 3 7 8 93 186 babied barked commercials gently |
||
1808 | iterate lad pragmatist reemphasizes rejoicing solemnness |
||
1809 | } |
||
1810 | 2 "SELECT x, x/z FROM d1 UNION ALL SELECT a,b FROM d2 ORDER BY x, x/z" { |
||
1811 | 1 -1 1 0 1 0 1 0 1 0 1 0 2 -2 2 0 |
||
1812 | babied charitableness barked interrupted commercials bathrobe gently |
||
1813 | failings iterate sexton lad relenting pragmatist guarded reemphasizes reply |
||
1814 | rejoicing liabilities solemnness annexed |
||
1815 | } |
||
1816 | } |
||
1817 | |||
1818 | do_execsql_test e_select-8.8.0 { |
||
1819 | CREATE TABLE d3(a); |
||
1820 | INSERT INTO d3 VALUES('text'); |
||
1821 | INSERT INTO d3 VALUES(14.1); |
||
1822 | INSERT INTO d3 VALUES(13); |
||
1823 | INSERT INTO d3 VALUES(X'78787878'); |
||
1824 | INSERT INTO d3 VALUES(15); |
||
1825 | INSERT INTO d3 VALUES(12.9); |
||
1826 | INSERT INTO d3 VALUES(null); |
||
1827 | |||
1828 | CREATE TABLE d4(x COLLATE nocase); |
||
1829 | INSERT INTO d4 VALUES('abc'); |
||
1830 | INSERT INTO d4 VALUES('ghi'); |
||
1831 | INSERT INTO d4 VALUES('DEF'); |
||
1832 | INSERT INTO d4 VALUES('JKL'); |
||
1833 | } {} |
||
1834 | |||
1835 | # EVIDENCE-OF: R-10883-17697 For the purposes of sorting rows, values |
||
1836 | # are compared in the same way as for comparison expressions. |
||
1837 | # |
||
1838 | # The following tests verify that values of different types are sorted |
||
1839 | # correctly, and that mixed real and integer values are compared properly. |
||
1840 | # |
||
1841 | do_execsql_test e_select-8.8.1 { |
||
1842 | SELECT a FROM d3 ORDER BY a |
||
1843 | } {{} 12.9 13 14.1 15 text xxxx} |
||
1844 | do_execsql_test e_select-8.8.2 { |
||
1845 | SELECT a FROM d3 ORDER BY a DESC |
||
1846 | } {xxxx text 15 14.1 13 12.9 {}} |
||
1847 | |||
1848 | |||
1849 | # EVIDENCE-OF: R-64199-22471 If the ORDER BY expression is assigned a |
||
1850 | # collation sequence using the postfix COLLATE operator, then the |
||
1851 | # specified collation sequence is used. |
||
1852 | # |
||
1853 | do_execsql_test e_select-8.9.1 { |
||
1854 | SELECT x FROM d4 ORDER BY 1 COLLATE binary |
||
1855 | } {DEF JKL abc ghi} |
||
1856 | do_execsql_test e_select-8.9.2 { |
||
1857 | SELECT x COLLATE binary FROM d4 ORDER BY 1 COLLATE nocase |
||
1858 | } {abc DEF ghi JKL} |
||
1859 | |||
1860 | # EVIDENCE-OF: R-09398-26102 Otherwise, if the ORDER BY expression is |
||
1861 | # an alias to an expression that has been assigned a collation sequence |
||
1862 | # using the postfix COLLATE operator, then the collation sequence |
||
1863 | # assigned to the aliased expression is used. |
||
1864 | # |
||
1865 | # In the test 8.10.2, the only result-column expression has no alias. So the |
||
1866 | # ORDER BY expression is not a reference to it and therefore does not inherit |
||
1867 | # the collation sequence. In test 8.10.3, "x" is the alias (as well as the |
||
1868 | # column name), so the ORDER BY expression is interpreted as an alias and the |
||
1869 | # collation sequence attached to the result column is used for sorting. |
||
1870 | # |
||
1871 | do_execsql_test e_select-8.10.1 { |
||
1872 | SELECT x COLLATE binary FROM d4 ORDER BY 1 |
||
1873 | } {DEF JKL abc ghi} |
||
1874 | do_execsql_test e_select-8.10.2 { |
||
1875 | SELECT x COLLATE binary FROM d4 ORDER BY x |
||
1876 | } {abc DEF ghi JKL} |
||
1877 | do_execsql_test e_select-8.10.3 { |
||
1878 | SELECT x COLLATE binary AS x FROM d4 ORDER BY x |
||
1879 | } {DEF JKL abc ghi} |
||
1880 | |||
1881 | # EVIDENCE-OF: R-27301-09658 Otherwise, if the ORDER BY expression is a |
||
1882 | # column or an alias of an expression that is a column, then the default |
||
1883 | # collation sequence for the column is used. |
||
1884 | # |
||
1885 | do_execsql_test e_select-8.11.1 { |
||
1886 | SELECT x AS y FROM d4 ORDER BY y |
||
1887 | } {abc DEF ghi JKL} |
||
1888 | do_execsql_test e_select-8.11.2 { |
||
1889 | SELECT x||'' FROM d4 ORDER BY x |
||
1890 | } {abc DEF ghi JKL} |
||
1891 | |||
1892 | # EVIDENCE-OF: R-49925-55905 Otherwise, the BINARY collation sequence is |
||
1893 | # used. |
||
1894 | # |
||
1895 | do_execsql_test e_select-8.12.1 { |
||
1896 | SELECT x FROM d4 ORDER BY x||'' |
||
1897 | } {DEF JKL abc ghi} |
||
1898 | |||
1899 | # EVIDENCE-OF: R-44130-32593 If an ORDER BY expression is not an integer |
||
1900 | # alias, then SQLite searches the left-most SELECT in the compound for a |
||
1901 | # result column that matches either the second or third rules above. If |
||
1902 | # a match is found, the search stops and the expression is handled as an |
||
1903 | # alias for the result column that it has been matched against. |
||
1904 | # Otherwise, the next SELECT to the right is tried, and so on. |
||
1905 | # |
||
1906 | do_execsql_test e_select-8.13.0 { |
||
1907 | CREATE TABLE d5(a, b); |
||
1908 | CREATE TABLE d6(c, d); |
||
1909 | CREATE TABLE d7(e, f); |
||
1910 | |||
1911 | INSERT INTO d5 VALUES(1, 'f'); |
||
1912 | INSERT INTO d6 VALUES(2, 'e'); |
||
1913 | INSERT INTO d7 VALUES(3, 'd'); |
||
1914 | INSERT INTO d5 VALUES(4, 'c'); |
||
1915 | INSERT INTO d6 VALUES(5, 'b'); |
||
1916 | INSERT INTO d7 VALUES(6, 'a'); |
||
1917 | |||
1918 | CREATE TABLE d8(x COLLATE nocase); |
||
1919 | CREATE TABLE d9(y COLLATE nocase); |
||
1920 | |||
1921 | INSERT INTO d8 VALUES('a'); |
||
1922 | INSERT INTO d9 VALUES('B'); |
||
1923 | INSERT INTO d8 VALUES('c'); |
||
1924 | INSERT INTO d9 VALUES('D'); |
||
1925 | } {} |
||
1926 | do_select_tests e_select-8.13 { |
||
1927 | 1 { SELECT a FROM d5 UNION ALL SELECT c FROM d6 UNION ALL SELECT e FROM d7 |
||
1928 | ORDER BY a |
||
1929 | } {1 2 3 4 5 6} |
||
1930 | 2 { SELECT a FROM d5 UNION ALL SELECT c FROM d6 UNION ALL SELECT e FROM d7 |
||
1931 | ORDER BY c |
||
1932 | } {1 2 3 4 5 6} |
||
1933 | 3 { SELECT a FROM d5 UNION ALL SELECT c FROM d6 UNION ALL SELECT e FROM d7 |
||
1934 | ORDER BY e |
||
1935 | } {1 2 3 4 5 6} |
||
1936 | 4 { SELECT a FROM d5 UNION ALL SELECT c FROM d6 UNION ALL SELECT e FROM d7 |
||
1937 | ORDER BY 1 |
||
1938 | } {1 2 3 4 5 6} |
||
1939 | |||
1940 | 5 { SELECT a, b FROM d5 UNION ALL SELECT b, a FROM d5 ORDER BY b } |
||
1941 | {f 1 c 4 4 c 1 f} |
||
1942 | 6 { SELECT a, b FROM d5 UNION ALL SELECT b, a FROM d5 ORDER BY 2 } |
||
1943 | {f 1 c 4 4 c 1 f} |
||
1944 | |||
1945 | 7 { SELECT a, b FROM d5 UNION ALL SELECT b, a FROM d5 ORDER BY a } |
||
1946 | {1 f 4 c c 4 f 1} |
||
1947 | 8 { SELECT a, b FROM d5 UNION ALL SELECT b, a FROM d5 ORDER BY 1 } |
||
1948 | {1 f 4 c c 4 f 1} |
||
1949 | |||
1950 | 9 { SELECT a, b FROM d5 UNION ALL SELECT b, a+1 FROM d5 ORDER BY a+1 } |
||
1951 | {f 2 c 5 4 c 1 f} |
||
1952 | 10 { SELECT a, b FROM d5 UNION ALL SELECT b, a+1 FROM d5 ORDER BY 2 } |
||
1953 | {f 2 c 5 4 c 1 f} |
||
1954 | |||
1955 | 11 { SELECT a+1, b FROM d5 UNION ALL SELECT b, a+1 FROM d5 ORDER BY a+1 } |
||
1956 | {2 f 5 c c 5 f 2} |
||
1957 | 12 { SELECT a+1, b FROM d5 UNION ALL SELECT b, a+1 FROM d5 ORDER BY 1 } |
||
1958 | {2 f 5 c c 5 f 2} |
||
1959 | } |
||
1960 | |||
1961 | # EVIDENCE-OF: R-39265-04070 If no matching expression can be found in |
||
1962 | # the result columns of any constituent SELECT, it is an error. |
||
1963 | # |
||
1964 | do_select_tests e_select-8.14 -error { |
||
1965 | %s ORDER BY term does not match any column in the result set |
||
1966 | } { |
||
1967 | 1 { SELECT a FROM d5 UNION SELECT c FROM d6 ORDER BY a+1 } 1st |
||
1968 | 2 { SELECT a FROM d5 UNION SELECT c FROM d6 ORDER BY a, a+1 } 2nd |
||
1969 | 3 { SELECT * FROM d5 INTERSECT SELECT * FROM d6 ORDER BY 'hello' } 1st |
||
1970 | 4 { SELECT * FROM d5 INTERSECT SELECT * FROM d6 ORDER BY blah } 1st |
||
1971 | 5 { SELECT * FROM d5 INTERSECT SELECT * FROM d6 ORDER BY c,d,c+d } 3rd |
||
1972 | 6 { SELECT * FROM d5 EXCEPT SELECT * FROM d7 ORDER BY 1,2,b,a/b } 4th |
||
1973 | } |
||
1974 | |||
1975 | # EVIDENCE-OF: R-03407-11483 Each term of the ORDER BY clause is |
||
1976 | # processed separately and may be matched against result columns from |
||
1977 | # different SELECT statements in the compound. |
||
1978 | # |
||
1979 | do_select_tests e_select-8.15 { |
||
1980 | 1 { SELECT a, b FROM d5 UNION ALL SELECT c-1, d FROM d6 ORDER BY a, d } |
||
1981 | {1 e 1 f 4 b 4 c} |
||
1982 | 2 { SELECT a, b FROM d5 UNION ALL SELECT c-1, d FROM d6 ORDER BY c-1, b } |
||
1983 | {1 e 1 f 4 b 4 c} |
||
1984 | 3 { SELECT a, b FROM d5 UNION ALL SELECT c-1, d FROM d6 ORDER BY 1, 2 } |
||
1985 | {1 e 1 f 4 b 4 c} |
||
1986 | } |
||
1987 | |||
1988 | |||
1989 | #------------------------------------------------------------------------- |
||
1990 | # Tests related to statements made about the LIMIT/OFFSET clause. |
||
1991 | # |
||
1992 | do_execsql_test e_select-9.0 { |
||
1993 | CREATE TABLE f1(a, b); |
||
1994 | INSERT INTO f1 VALUES(26, 'z'); |
||
1995 | INSERT INTO f1 VALUES(25, 'y'); |
||
1996 | INSERT INTO f1 VALUES(24, 'x'); |
||
1997 | INSERT INTO f1 VALUES(23, 'w'); |
||
1998 | INSERT INTO f1 VALUES(22, 'v'); |
||
1999 | INSERT INTO f1 VALUES(21, 'u'); |
||
2000 | INSERT INTO f1 VALUES(20, 't'); |
||
2001 | INSERT INTO f1 VALUES(19, 's'); |
||
2002 | INSERT INTO f1 VALUES(18, 'r'); |
||
2003 | INSERT INTO f1 VALUES(17, 'q'); |
||
2004 | INSERT INTO f1 VALUES(16, 'p'); |
||
2005 | INSERT INTO f1 VALUES(15, 'o'); |
||
2006 | INSERT INTO f1 VALUES(14, 'n'); |
||
2007 | INSERT INTO f1 VALUES(13, 'm'); |
||
2008 | INSERT INTO f1 VALUES(12, 'l'); |
||
2009 | INSERT INTO f1 VALUES(11, 'k'); |
||
2010 | INSERT INTO f1 VALUES(10, 'j'); |
||
2011 | INSERT INTO f1 VALUES(9, 'i'); |
||
2012 | INSERT INTO f1 VALUES(8, 'h'); |
||
2013 | INSERT INTO f1 VALUES(7, 'g'); |
||
2014 | INSERT INTO f1 VALUES(6, 'f'); |
||
2015 | INSERT INTO f1 VALUES(5, 'e'); |
||
2016 | INSERT INTO f1 VALUES(4, 'd'); |
||
2017 | INSERT INTO f1 VALUES(3, 'c'); |
||
2018 | INSERT INTO f1 VALUES(2, 'b'); |
||
2019 | INSERT INTO f1 VALUES(1, 'a'); |
||
2020 | } {} |
||
2021 | |||
2022 | # EVIDENCE-OF: R-30481-56627 Any scalar expression may be used in the |
||
2023 | # LIMIT clause, so long as it evaluates to an integer or a value that |
||
2024 | # can be losslessly converted to an integer. |
||
2025 | # |
||
2026 | do_select_tests e_select-9.1 { |
||
2027 | 1 { SELECT b FROM f1 ORDER BY a LIMIT 5 } {a b c d e} |
||
2028 | 2 { SELECT b FROM f1 ORDER BY a LIMIT 2+3 } {a b c d e} |
||
2029 | 3 { SELECT b FROM f1 ORDER BY a LIMIT (SELECT a FROM f1 WHERE b = 'e') } |
||
2030 | {a b c d e} |
||
2031 | 4 { SELECT b FROM f1 ORDER BY a LIMIT 5.0 } {a b c d e} |
||
2032 | 5 { SELECT b FROM f1 ORDER BY a LIMIT '5' } {a b c d e} |
||
2033 | } |
||
2034 | |||
2035 | # EVIDENCE-OF: R-46155-47219 If the expression evaluates to a NULL value |
||
2036 | # or any other value that cannot be losslessly converted to an integer, |
||
2037 | # an error is returned. |
||
2038 | # |
||
2039 | |||
2040 | do_select_tests e_select-9.2 -error "datatype mismatch" { |
||
2041 | 1 { SELECT b FROM f1 ORDER BY a LIMIT 'hello' } {} |
||
2042 | 2 { SELECT b FROM f1 ORDER BY a LIMIT NULL } {} |
||
2043 | 3 { SELECT b FROM f1 ORDER BY a LIMIT X'ABCD' } {} |
||
2044 | 4 { SELECT b FROM f1 ORDER BY a LIMIT 5.1 } {} |
||
2045 | 5 { SELECT b FROM f1 ORDER BY a LIMIT (SELECT group_concat(b) FROM f1) } {} |
||
2046 | } |
||
2047 | |||
2048 | # EVIDENCE-OF: R-03014-26414 If the LIMIT expression evaluates to a |
||
2049 | # negative value, then there is no upper bound on the number of rows |
||
2050 | # returned. |
||
2051 | # |
||
2052 | do_select_tests e_select-9.4 { |
||
2053 | 1 { SELECT b FROM f1 ORDER BY a LIMIT -1 } |
||
2054 | {a b c d e f g h i j k l m n o p q r s t u v w x y z} |
||
2055 | 2 { SELECT b FROM f1 ORDER BY a LIMIT length('abc')-100 } |
||
2056 | {a b c d e f g h i j k l m n o p q r s t u v w x y z} |
||
2057 | 3 { SELECT b FROM f1 ORDER BY a LIMIT (SELECT count(*) FROM f1)/2 - 14 } |
||
2058 | {a b c d e f g h i j k l m n o p q r s t u v w x y z} |
||
2059 | } |
||
2060 | |||
2061 | # EVIDENCE-OF: R-33750-29536 Otherwise, the SELECT returns the first N |
||
2062 | # rows of its result set only, where N is the value that the LIMIT |
||
2063 | # expression evaluates to. |
||
2064 | # |
||
2065 | do_select_tests e_select-9.5 { |
||
2066 | 1 { SELECT b FROM f1 ORDER BY a LIMIT 0 } {} |
||
2067 | 2 { SELECT b FROM f1 ORDER BY a DESC LIMIT 4 } {z y x w} |
||
2068 | 3 { SELECT b FROM f1 ORDER BY a DESC LIMIT 8 } {z y x w v u t s} |
||
2069 | 4 { SELECT b FROM f1 ORDER BY a DESC LIMIT '12.0' } {z y x w v u t s r q p o} |
||
2070 | } |
||
2071 | |||
2072 | # EVIDENCE-OF: R-54935-19057 Or, if the SELECT statement would return |
||
2073 | # less than N rows without a LIMIT clause, then the entire result set is |
||
2074 | # returned. |
||
2075 | # |
||
2076 | do_select_tests e_select-9.6 { |
||
2077 | 1 { SELECT b FROM f1 WHERE a>21 ORDER BY a LIMIT 10 } {v w x y z} |
||
2078 | 2 { SELECT count(*) FROM f1 GROUP BY a/5 ORDER BY 1 LIMIT 10 } {2 4 5 5 5 5} |
||
2079 | } |
||
2080 | |||
2081 | |||
2082 | # EVIDENCE-OF: R-24188-24349 The expression attached to the optional |
||
2083 | # OFFSET clause that may follow a LIMIT clause must also evaluate to an |
||
2084 | # integer, or a value that can be losslessly converted to an integer. |
||
2085 | # |
||
2086 | foreach {tn select} { |
||
2087 | 1 { SELECT b FROM f1 ORDER BY a LIMIT 2 OFFSET 'hello' } |
||
2088 | 2 { SELECT b FROM f1 ORDER BY a LIMIT 2 OFFSET NULL } |
||
2089 | 3 { SELECT b FROM f1 ORDER BY a LIMIT 2 OFFSET X'ABCD' } |
||
2090 | 4 { SELECT b FROM f1 ORDER BY a LIMIT 2 OFFSET 5.1 } |
||
2091 | 5 { SELECT b FROM f1 ORDER BY a |
||
2092 | LIMIT 2 OFFSET (SELECT group_concat(b) FROM f1) |
||
2093 | } |
||
2094 | } { |
||
2095 | do_catchsql_test e_select-9.7.$tn $select {1 {datatype mismatch}} |
||
2096 | } |
||
2097 | |||
2098 | # EVIDENCE-OF: R-20467-43422 If an expression has an OFFSET clause, then |
||
2099 | # the first M rows are omitted from the result set returned by the |
||
2100 | # SELECT statement and the next N rows are returned, where M and N are |
||
2101 | # the values that the OFFSET and LIMIT clauses evaluate to, |
||
2102 | # respectively. |
||
2103 | # |
||
2104 | do_select_tests e_select-9.8 { |
||
2105 | 1 { SELECT b FROM f1 ORDER BY a LIMIT 10 OFFSET 5} {f g h i j k l m n o} |
||
2106 | 2 { SELECT b FROM f1 ORDER BY a LIMIT 2+3 OFFSET 10} {k l m n o} |
||
2107 | 3 { SELECT b FROM f1 ORDER BY a |
||
2108 | LIMIT (SELECT a FROM f1 WHERE b='j') |
||
2109 | OFFSET (SELECT a FROM f1 WHERE b='b') |
||
2110 | } {c d e f g h i j k l} |
||
2111 | 4 { SELECT b FROM f1 ORDER BY a LIMIT '5' OFFSET 3.0 } {d e f g h} |
||
2112 | 5 { SELECT b FROM f1 ORDER BY a LIMIT '5' OFFSET 0 } {a b c d e} |
||
2113 | 6 { SELECT b FROM f1 ORDER BY a LIMIT 0 OFFSET 10 } {} |
||
2114 | 7 { SELECT b FROM f1 ORDER BY a LIMIT 3 OFFSET '1'||'5' } {p q r} |
||
2115 | } |
||
2116 | |||
2117 | # EVIDENCE-OF: R-34648-44875 Or, if the SELECT would return less than |
||
2118 | # M+N rows if it did not have a LIMIT clause, then the first M rows are |
||
2119 | # skipped and the remaining rows (if any) are returned. |
||
2120 | # |
||
2121 | do_select_tests e_select-9.9 { |
||
2122 | 1 { SELECT b FROM f1 ORDER BY a LIMIT 10 OFFSET 20} {u v w x y z} |
||
2123 | 2 { SELECT a FROM f1 ORDER BY a DESC LIMIT 100 OFFSET 18+4} {4 3 2 1} |
||
2124 | } |
||
2125 | |||
2126 | |||
2127 | # EVIDENCE-OF: R-23293-62447 If the OFFSET clause evaluates to a |
||
2128 | # negative value, the results are the same as if it had evaluated to |
||
2129 | # zero. |
||
2130 | # |
||
2131 | do_select_tests e_select-9.10 { |
||
2132 | 1 { SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET -1 } {a b c d e} |
||
2133 | 2 { SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET -500 } {a b c d e} |
||
2134 | 3 { SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET 0 } {a b c d e} |
||
2135 | } |
||
2136 | |||
2137 | # EVIDENCE-OF: R-19509-40356 Instead of a separate OFFSET clause, the |
||
2138 | # LIMIT clause may specify two scalar expressions separated by a comma. |
||
2139 | # |
||
2140 | # EVIDENCE-OF: R-33788-46243 In this case, the first expression is used |
||
2141 | # as the OFFSET expression and the second as the LIMIT expression. |
||
2142 | # |
||
2143 | do_select_tests e_select-9.11 { |
||
2144 | 1 { SELECT b FROM f1 ORDER BY a LIMIT 5, 10 } {f g h i j k l m n o} |
||
2145 | 2 { SELECT b FROM f1 ORDER BY a LIMIT 10, 2+3 } {k l m n o} |
||
2146 | 3 { SELECT b FROM f1 ORDER BY a |
||
2147 | LIMIT (SELECT a FROM f1 WHERE b='b'), (SELECT a FROM f1 WHERE b='j') |
||
2148 | } {c d e f g h i j k l} |
||
2149 | 4 { SELECT b FROM f1 ORDER BY a LIMIT 3.0, '5' } {d e f g h} |
||
2150 | 5 { SELECT b FROM f1 ORDER BY a LIMIT 0, '5' } {a b c d e} |
||
2151 | 6 { SELECT b FROM f1 ORDER BY a LIMIT 10, 0 } {} |
||
2152 | 7 { SELECT b FROM f1 ORDER BY a LIMIT '1'||'5', 3 } {p q r} |
||
2153 | |||
2154 | 8 { SELECT b FROM f1 ORDER BY a LIMIT 20, 10 } {u v w x y z} |
||
2155 | 9 { SELECT a FROM f1 ORDER BY a DESC LIMIT 18+4, 100 } {4 3 2 1} |
||
2156 | |||
2157 | 10 { SELECT b FROM f1 ORDER BY a LIMIT -1, 5 } {a b c d e} |
||
2158 | 11 { SELECT b FROM f1 ORDER BY a LIMIT -500, 5 } {a b c d e} |
||
2159 | 12 { SELECT b FROM f1 ORDER BY a LIMIT 0, 5 } {a b c d e} |
||
2160 | } |
||
2161 | |||
2162 | finish_test |