Annotation of embedaddon/sqlite3/test/e_select.test, revision 1.1.1.1
1.1 misho 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-11353-33501 -- 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-40919-40941 -- 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-41378-26734 -- 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-43129-35648 -- syntax diagram join-source
235: #
236: # EVIDENCE-OF: R-36683-37460 -- 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-28308-37813 -- 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-06480-34950 -- 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-23926-36668 -- 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: 0 -58.58 51.65 -59.58 belfries
880: 0 {} -5 {} 75
881: 0 -22.18 -2.2 -23.18 suiters
882: 1 68 {} 67 quartets
883: 0 -31.3 -1.04 -32.3 aspen
884: 0 1 63 born -26
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" {One Two Three Four}
1242: 4.2 "SELECT DISTINCT x FROM h1, h2 ON (x=b)" {One Four}
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" {one I i four IV iv}
1257: 2 "SELECT DISTINCT b COLLATE nocase FROM h1" {one I four IV}
1258: 3 "SELECT DISTINCT x FROM h2" {One Two Three Four}
1259: 4 "SELECT DISTINCT x COLLATE binary FROM h2" {
1260: One Two Three Four one two three four
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: 0 {SELECT * FROM q3} {beauty 2 beauty 2}
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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to e_select.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test