wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
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