1: # 2001 September 15
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: # This file implements regression tests for SQLite library. The
12: # focus of this file is testing the SELECT statement.
13: #
14: # $Id: select1.test,v 1.1.1.1 2012/02/21 17:04:16 misho Exp $
15:
16: set testdir [file dirname $argv0]
17: source $testdir/tester.tcl
18:
19: # Try to select on a non-existant table.
20: #
21: do_test select1-1.1 {
22: set v [catch {execsql {SELECT * FROM test1}} msg]
23: lappend v $msg
24: } {1 {no such table: test1}}
25:
26:
27: execsql {CREATE TABLE test1(f1 int, f2 int)}
28:
29: do_test select1-1.2 {
30: set v [catch {execsql {SELECT * FROM test1, test2}} msg]
31: lappend v $msg
32: } {1 {no such table: test2}}
33: do_test select1-1.3 {
34: set v [catch {execsql {SELECT * FROM test2, test1}} msg]
35: lappend v $msg
36: } {1 {no such table: test2}}
37:
38: execsql {INSERT INTO test1(f1,f2) VALUES(11,22)}
39:
40:
41: # Make sure the columns are extracted correctly.
42: #
43: do_test select1-1.4 {
44: execsql {SELECT f1 FROM test1}
45: } {11}
46: do_test select1-1.5 {
47: execsql {SELECT f2 FROM test1}
48: } {22}
49: do_test select1-1.6 {
50: execsql {SELECT f2, f1 FROM test1}
51: } {22 11}
52: do_test select1-1.7 {
53: execsql {SELECT f1, f2 FROM test1}
54: } {11 22}
55: do_test select1-1.8 {
56: execsql {SELECT * FROM test1}
57: } {11 22}
58: do_test select1-1.8.1 {
59: execsql {SELECT *, * FROM test1}
60: } {11 22 11 22}
61: do_test select1-1.8.2 {
62: execsql {SELECT *, min(f1,f2), max(f1,f2) FROM test1}
63: } {11 22 11 22}
64: do_test select1-1.8.3 {
65: execsql {SELECT 'one', *, 'two', * FROM test1}
66: } {one 11 22 two 11 22}
67:
68: execsql {CREATE TABLE test2(r1 real, r2 real)}
69: execsql {INSERT INTO test2(r1,r2) VALUES(1.1,2.2)}
70:
71: do_test select1-1.9 {
72: execsql {SELECT * FROM test1, test2}
73: } {11 22 1.1 2.2}
74: do_test select1-1.9.1 {
75: execsql {SELECT *, 'hi' FROM test1, test2}
76: } {11 22 1.1 2.2 hi}
77: do_test select1-1.9.2 {
78: execsql {SELECT 'one', *, 'two', * FROM test1, test2}
79: } {one 11 22 1.1 2.2 two 11 22 1.1 2.2}
80: do_test select1-1.10 {
81: execsql {SELECT test1.f1, test2.r1 FROM test1, test2}
82: } {11 1.1}
83: do_test select1-1.11 {
84: execsql {SELECT test1.f1, test2.r1 FROM test2, test1}
85: } {11 1.1}
86: do_test select1-1.11.1 {
87: execsql {SELECT * FROM test2, test1}
88: } {1.1 2.2 11 22}
89: do_test select1-1.11.2 {
90: execsql {SELECT * FROM test1 AS a, test1 AS b}
91: } {11 22 11 22}
92: do_test select1-1.12 {
93: execsql {SELECT max(test1.f1,test2.r1), min(test1.f2,test2.r2)
94: FROM test2, test1}
95: } {11 2.2}
96: do_test select1-1.13 {
97: execsql {SELECT min(test1.f1,test2.r1), max(test1.f2,test2.r2)
98: FROM test1, test2}
99: } {1.1 22}
100:
101: set long {This is a string that is too big to fit inside a NBFS buffer}
102: do_test select1-2.0 {
103: execsql "
104: DROP TABLE test2;
105: DELETE FROM test1;
106: INSERT INTO test1 VALUES(11,22);
107: INSERT INTO test1 VALUES(33,44);
108: CREATE TABLE t3(a,b);
109: INSERT INTO t3 VALUES('abc',NULL);
110: INSERT INTO t3 VALUES(NULL,'xyz');
111: INSERT INTO t3 SELECT * FROM test1;
112: CREATE TABLE t4(a,b);
113: INSERT INTO t4 VALUES(NULL,'$long');
114: SELECT * FROM t3;
115: "
116: } {abc {} {} xyz 11 22 33 44}
117:
118: # Error messges from sqliteExprCheck
119: #
120: do_test select1-2.1 {
121: set v [catch {execsql {SELECT count(f1,f2) FROM test1}} msg]
122: lappend v $msg
123: } {1 {wrong number of arguments to function count()}}
124: do_test select1-2.2 {
125: set v [catch {execsql {SELECT count(f1) FROM test1}} msg]
126: lappend v $msg
127: } {0 2}
128: do_test select1-2.3 {
129: set v [catch {execsql {SELECT Count() FROM test1}} msg]
130: lappend v $msg
131: } {0 2}
132: do_test select1-2.4 {
133: set v [catch {execsql {SELECT COUNT(*) FROM test1}} msg]
134: lappend v $msg
135: } {0 2}
136: do_test select1-2.5 {
137: set v [catch {execsql {SELECT COUNT(*)+1 FROM test1}} msg]
138: lappend v $msg
139: } {0 3}
140: do_test select1-2.5.1 {
141: execsql {SELECT count(*),count(a),count(b) FROM t3}
142: } {4 3 3}
143: do_test select1-2.5.2 {
144: execsql {SELECT count(*),count(a),count(b) FROM t4}
145: } {1 0 1}
146: do_test select1-2.5.3 {
147: execsql {SELECT count(*),count(a),count(b) FROM t4 WHERE b=5}
148: } {0 0 0}
149: do_test select1-2.6 {
150: set v [catch {execsql {SELECT min(*) FROM test1}} msg]
151: lappend v $msg
152: } {1 {wrong number of arguments to function min()}}
153: do_test select1-2.7 {
154: set v [catch {execsql {SELECT Min(f1) FROM test1}} msg]
155: lappend v $msg
156: } {0 11}
157: do_test select1-2.8 {
158: set v [catch {execsql {SELECT MIN(f1,f2) FROM test1}} msg]
159: lappend v [lsort $msg]
160: } {0 {11 33}}
161: do_test select1-2.8.1 {
162: execsql {SELECT coalesce(min(a),'xyzzy') FROM t3}
163: } {11}
164: do_test select1-2.8.2 {
165: execsql {SELECT min(coalesce(a,'xyzzy')) FROM t3}
166: } {11}
167: do_test select1-2.8.3 {
168: execsql {SELECT min(b), min(b) FROM t4}
169: } [list $long $long]
170: do_test select1-2.9 {
171: set v [catch {execsql {SELECT MAX(*) FROM test1}} msg]
172: lappend v $msg
173: } {1 {wrong number of arguments to function MAX()}}
174: do_test select1-2.10 {
175: set v [catch {execsql {SELECT Max(f1) FROM test1}} msg]
176: lappend v $msg
177: } {0 33}
178: do_test select1-2.11 {
179: set v [catch {execsql {SELECT max(f1,f2) FROM test1}} msg]
180: lappend v [lsort $msg]
181: } {0 {22 44}}
182: do_test select1-2.12 {
183: set v [catch {execsql {SELECT MAX(f1,f2)+1 FROM test1}} msg]
184: lappend v [lsort $msg]
185: } {0 {23 45}}
186: do_test select1-2.13 {
187: set v [catch {execsql {SELECT MAX(f1)+1 FROM test1}} msg]
188: lappend v $msg
189: } {0 34}
190: do_test select1-2.13.1 {
191: execsql {SELECT coalesce(max(a),'xyzzy') FROM t3}
192: } {abc}
193: do_test select1-2.13.2 {
194: execsql {SELECT max(coalesce(a,'xyzzy')) FROM t3}
195: } {xyzzy}
196: do_test select1-2.14 {
197: set v [catch {execsql {SELECT SUM(*) FROM test1}} msg]
198: lappend v $msg
199: } {1 {wrong number of arguments to function SUM()}}
200: do_test select1-2.15 {
201: set v [catch {execsql {SELECT Sum(f1) FROM test1}} msg]
202: lappend v $msg
203: } {0 44}
204: do_test select1-2.16 {
205: set v [catch {execsql {SELECT sum(f1,f2) FROM test1}} msg]
206: lappend v $msg
207: } {1 {wrong number of arguments to function sum()}}
208: do_test select1-2.17 {
209: set v [catch {execsql {SELECT SUM(f1)+1 FROM test1}} msg]
210: lappend v $msg
211: } {0 45}
212: do_test select1-2.17.1 {
213: execsql {SELECT sum(a) FROM t3}
214: } {44.0}
215: do_test select1-2.18 {
216: set v [catch {execsql {SELECT XYZZY(f1) FROM test1}} msg]
217: lappend v $msg
218: } {1 {no such function: XYZZY}}
219: do_test select1-2.19 {
220: set v [catch {execsql {SELECT SUM(min(f1,f2)) FROM test1}} msg]
221: lappend v $msg
222: } {0 44}
223: do_test select1-2.20 {
224: set v [catch {execsql {SELECT SUM(min(f1)) FROM test1}} msg]
225: lappend v $msg
226: } {1 {misuse of aggregate function min()}}
227:
228: # Ticket #2526
229: #
230: do_test select1-2.21 {
231: catchsql {
232: SELECT min(f1) AS m FROM test1 GROUP BY f1 HAVING max(m+5)<10
233: }
234: } {1 {misuse of aliased aggregate m}}
235: do_test select1-2.22 {
236: catchsql {
237: SELECT coalesce(min(f1)+5,11) AS m FROM test1
238: GROUP BY f1
239: HAVING max(m+5)<10
240: }
241: } {1 {misuse of aliased aggregate m}}
242: do_test select1-2.23 {
243: execsql {
244: CREATE TABLE tkt2526(a,b,c PRIMARY KEY);
245: INSERT INTO tkt2526 VALUES('x','y',NULL);
246: INSERT INTO tkt2526 VALUES('x','z',NULL);
247: }
248: catchsql {
249: SELECT count(a) AS cn FROM tkt2526 GROUP BY a HAVING cn<max(cn)
250: }
251: } {1 {misuse of aliased aggregate cn}}
252:
253: # WHERE clause expressions
254: #
255: do_test select1-3.1 {
256: set v [catch {execsql {SELECT f1 FROM test1 WHERE f1<11}} msg]
257: lappend v $msg
258: } {0 {}}
259: do_test select1-3.2 {
260: set v [catch {execsql {SELECT f1 FROM test1 WHERE f1<=11}} msg]
261: lappend v $msg
262: } {0 11}
263: do_test select1-3.3 {
264: set v [catch {execsql {SELECT f1 FROM test1 WHERE f1=11}} msg]
265: lappend v $msg
266: } {0 11}
267: do_test select1-3.4 {
268: set v [catch {execsql {SELECT f1 FROM test1 WHERE f1>=11}} msg]
269: lappend v [lsort $msg]
270: } {0 {11 33}}
271: do_test select1-3.5 {
272: set v [catch {execsql {SELECT f1 FROM test1 WHERE f1>11}} msg]
273: lappend v [lsort $msg]
274: } {0 33}
275: do_test select1-3.6 {
276: set v [catch {execsql {SELECT f1 FROM test1 WHERE f1!=11}} msg]
277: lappend v [lsort $msg]
278: } {0 33}
279: do_test select1-3.7 {
280: set v [catch {execsql {SELECT f1 FROM test1 WHERE min(f1,f2)!=11}} msg]
281: lappend v [lsort $msg]
282: } {0 33}
283: do_test select1-3.8 {
284: set v [catch {execsql {SELECT f1 FROM test1 WHERE max(f1,f2)!=11}} msg]
285: lappend v [lsort $msg]
286: } {0 {11 33}}
287: do_test select1-3.9 {
288: set v [catch {execsql {SELECT f1 FROM test1 WHERE count(f1,f2)!=11}} msg]
289: lappend v $msg
290: } {1 {wrong number of arguments to function count()}}
291:
292: # ORDER BY expressions
293: #
294: do_test select1-4.1 {
295: set v [catch {execsql {SELECT f1 FROM test1 ORDER BY f1}} msg]
296: lappend v $msg
297: } {0 {11 33}}
298: do_test select1-4.2 {
299: set v [catch {execsql {SELECT f1 FROM test1 ORDER BY -f1}} msg]
300: lappend v $msg
301: } {0 {33 11}}
302: do_test select1-4.3 {
303: set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1,f2)}} msg]
304: lappend v $msg
305: } {0 {11 33}}
306: do_test select1-4.4 {
307: set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1)}} msg]
308: lappend v $msg
309: } {1 {misuse of aggregate: min()}}
310:
311: # The restriction not allowing constants in the ORDER BY clause
312: # has been removed. See ticket #1768
313: #do_test select1-4.5 {
314: # catchsql {
315: # SELECT f1 FROM test1 ORDER BY 8.4;
316: # }
317: #} {1 {ORDER BY terms must not be non-integer constants}}
318: #do_test select1-4.6 {
319: # catchsql {
320: # SELECT f1 FROM test1 ORDER BY '8.4';
321: # }
322: #} {1 {ORDER BY terms must not be non-integer constants}}
323: #do_test select1-4.7.1 {
324: # catchsql {
325: # SELECT f1 FROM test1 ORDER BY 'xyz';
326: # }
327: #} {1 {ORDER BY terms must not be non-integer constants}}
328: #do_test select1-4.7.2 {
329: # catchsql {
330: # SELECT f1 FROM test1 ORDER BY -8.4;
331: # }
332: #} {1 {ORDER BY terms must not be non-integer constants}}
333: #do_test select1-4.7.3 {
334: # catchsql {
335: # SELECT f1 FROM test1 ORDER BY +8.4;
336: # }
337: #} {1 {ORDER BY terms must not be non-integer constants}}
338: #do_test select1-4.7.4 {
339: # catchsql {
340: # SELECT f1 FROM test1 ORDER BY 4294967296; -- constant larger than 32 bits
341: # }
342: #} {1 {ORDER BY terms must not be non-integer constants}}
343:
344: do_test select1-4.5 {
345: execsql {
346: SELECT f1 FROM test1 ORDER BY 8.4
347: }
348: } {11 33}
349: do_test select1-4.6 {
350: execsql {
351: SELECT f1 FROM test1 ORDER BY '8.4'
352: }
353: } {11 33}
354:
355: do_test select1-4.8 {
356: execsql {
357: CREATE TABLE t5(a,b);
358: INSERT INTO t5 VALUES(1,10);
359: INSERT INTO t5 VALUES(2,9);
360: SELECT * FROM t5 ORDER BY 1;
361: }
362: } {1 10 2 9}
363: do_test select1-4.9.1 {
364: execsql {
365: SELECT * FROM t5 ORDER BY 2;
366: }
367: } {2 9 1 10}
368: do_test select1-4.9.2 {
369: execsql {
370: SELECT * FROM t5 ORDER BY +2;
371: }
372: } {2 9 1 10}
373: do_test select1-4.10.1 {
374: catchsql {
375: SELECT * FROM t5 ORDER BY 3;
376: }
377: } {1 {1st ORDER BY term out of range - should be between 1 and 2}}
378: do_test select1-4.10.2 {
379: catchsql {
380: SELECT * FROM t5 ORDER BY -1;
381: }
382: } {1 {1st ORDER BY term out of range - should be between 1 and 2}}
383: do_test select1-4.11 {
384: execsql {
385: INSERT INTO t5 VALUES(3,10);
386: SELECT * FROM t5 ORDER BY 2, 1 DESC;
387: }
388: } {2 9 3 10 1 10}
389: do_test select1-4.12 {
390: execsql {
391: SELECT * FROM t5 ORDER BY 1 DESC, b;
392: }
393: } {3 10 2 9 1 10}
394: do_test select1-4.13 {
395: execsql {
396: SELECT * FROM t5 ORDER BY b DESC, 1;
397: }
398: } {1 10 3 10 2 9}
399:
400:
401: # ORDER BY ignored on an aggregate query
402: #
403: do_test select1-5.1 {
404: set v [catch {execsql {SELECT max(f1) FROM test1 ORDER BY f2}} msg]
405: lappend v $msg
406: } {0 33}
407:
408: execsql {CREATE TABLE test2(t1 text, t2 text)}
409: execsql {INSERT INTO test2 VALUES('abc','xyz')}
410:
411: # Check for column naming
412: #
413: do_test select1-6.1 {
414: set v [catch {execsql2 {SELECT f1 FROM test1 ORDER BY f2}} msg]
415: lappend v $msg
416: } {0 {f1 11 f1 33}}
417: do_test select1-6.1.1 {
418: db eval {PRAGMA full_column_names=on}
419: set v [catch {execsql2 {SELECT f1 FROM test1 ORDER BY f2}} msg]
420: lappend v $msg
421: } {0 {test1.f1 11 test1.f1 33}}
422: do_test select1-6.1.2 {
423: set v [catch {execsql2 {SELECT f1 as 'f1' FROM test1 ORDER BY f2}} msg]
424: lappend v $msg
425: } {0 {f1 11 f1 33}}
426: do_test select1-6.1.3 {
427: set v [catch {execsql2 {SELECT * FROM test1 WHERE f1==11}} msg]
428: lappend v $msg
429: } {0 {f1 11 f2 22}}
430: do_test select1-6.1.4 {
431: set v [catch {execsql2 {SELECT DISTINCT * FROM test1 WHERE f1==11}} msg]
432: db eval {PRAGMA full_column_names=off}
433: lappend v $msg
434: } {0 {f1 11 f2 22}}
435: do_test select1-6.1.5 {
436: set v [catch {execsql2 {SELECT * FROM test1 WHERE f1==11}} msg]
437: lappend v $msg
438: } {0 {f1 11 f2 22}}
439: do_test select1-6.1.6 {
440: set v [catch {execsql2 {SELECT DISTINCT * FROM test1 WHERE f1==11}} msg]
441: lappend v $msg
442: } {0 {f1 11 f2 22}}
443: do_test select1-6.2 {
444: set v [catch {execsql2 {SELECT f1 as xyzzy FROM test1 ORDER BY f2}} msg]
445: lappend v $msg
446: } {0 {xyzzy 11 xyzzy 33}}
447: do_test select1-6.3 {
448: set v [catch {execsql2 {SELECT f1 as "xyzzy" FROM test1 ORDER BY f2}} msg]
449: lappend v $msg
450: } {0 {xyzzy 11 xyzzy 33}}
451: do_test select1-6.3.1 {
452: set v [catch {execsql2 {SELECT f1 as 'xyzzy ' FROM test1 ORDER BY f2}} msg]
453: lappend v $msg
454: } {0 {{xyzzy } 11 {xyzzy } 33}}
455: do_test select1-6.4 {
456: set v [catch {execsql2 {SELECT f1+F2 as xyzzy FROM test1 ORDER BY f2}} msg]
457: lappend v $msg
458: } {0 {xyzzy 33 xyzzy 77}}
459: do_test select1-6.4a {
460: set v [catch {execsql2 {SELECT f1+F2 FROM test1 ORDER BY f2}} msg]
461: lappend v $msg
462: } {0 {f1+F2 33 f1+F2 77}}
463: do_test select1-6.5 {
464: set v [catch {execsql2 {SELECT test1.f1+F2 FROM test1 ORDER BY f2}} msg]
465: lappend v $msg
466: } {0 {test1.f1+F2 33 test1.f1+F2 77}}
467: do_test select1-6.5.1 {
468: execsql2 {PRAGMA full_column_names=on}
469: set v [catch {execsql2 {SELECT test1.f1+F2 FROM test1 ORDER BY f2}} msg]
470: execsql2 {PRAGMA full_column_names=off}
471: lappend v $msg
472: } {0 {test1.f1+F2 33 test1.f1+F2 77}}
473: do_test select1-6.6 {
474: set v [catch {execsql2 {SELECT test1.f1+F2, t1 FROM test1, test2
475: ORDER BY f2}} msg]
476: lappend v $msg
477: } {0 {test1.f1+F2 33 t1 abc test1.f1+F2 77 t1 abc}}
478: do_test select1-6.7 {
479: set v [catch {execsql2 {SELECT A.f1, t1 FROM test1 as A, test2
480: ORDER BY f2}} msg]
481: lappend v $msg
482: } {0 {f1 11 t1 abc f1 33 t1 abc}}
483: do_test select1-6.8 {
484: set v [catch {execsql2 {SELECT A.f1, f1 FROM test1 as A, test1 as B
485: ORDER BY f2}} msg]
486: lappend v $msg
487: } {1 {ambiguous column name: f1}}
488: do_test select1-6.8b {
489: set v [catch {execsql2 {SELECT A.f1, B.f1 FROM test1 as A, test1 as B
490: ORDER BY f2}} msg]
491: lappend v $msg
492: } {1 {ambiguous column name: f2}}
493: do_test select1-6.8c {
494: set v [catch {execsql2 {SELECT A.f1, f1 FROM test1 as A, test1 as A
495: ORDER BY f2}} msg]
496: lappend v $msg
497: } {1 {ambiguous column name: A.f1}}
498: do_test select1-6.9.1 {
499: set v [catch {execsql {SELECT A.f1, B.f1 FROM test1 as A, test1 as B
500: ORDER BY A.f1, B.f1}} msg]
501: lappend v $msg
502: } {0 {11 11 11 33 33 11 33 33}}
503: do_test select1-6.9.2 {
504: set v [catch {execsql2 {SELECT A.f1, B.f1 FROM test1 as A, test1 as B
505: ORDER BY A.f1, B.f1}} msg]
506: lappend v $msg
507: } {0 {f1 11 f1 11 f1 33 f1 33 f1 11 f1 11 f1 33 f1 33}}
508:
509: do_test select1-6.9.3 {
510: db eval {
511: PRAGMA short_column_names=OFF;
512: PRAGMA full_column_names=OFF;
513: }
514: execsql2 {
515: SELECT test1 . f1, test1 . f2 FROM test1 LIMIT 1
516: }
517: } {{test1 . f1} 11 {test1 . f2} 22}
518: do_test select1-6.9.4 {
519: db eval {
520: PRAGMA short_column_names=OFF;
521: PRAGMA full_column_names=ON;
522: }
523: execsql2 {
524: SELECT test1 . f1, test1 . f2 FROM test1 LIMIT 1
525: }
526: } {test1.f1 11 test1.f2 22}
527: do_test select1-6.9.5 {
528: db eval {
529: PRAGMA short_column_names=OFF;
530: PRAGMA full_column_names=ON;
531: }
532: execsql2 {
533: SELECT 123.45;
534: }
535: } {123.45 123.45}
536: do_test select1-6.9.6 {
537: execsql2 {
538: SELECT * FROM test1 a, test1 b LIMIT 1
539: }
540: } {a.f1 11 a.f2 22 b.f1 11 b.f2 22}
541: do_test select1-6.9.7 {
542: set x [execsql2 {
543: SELECT * FROM test1 a, (select 5, 6) LIMIT 1
544: }]
545: regsub -all {subquery_[0-9a-fA-F]+_} $x {subquery} x
546: set x
547: } {a.f1 11 a.f2 22 sqlite_subquery.5 5 sqlite_subquery.6 6}
548: do_test select1-6.9.8 {
549: set x [execsql2 {
550: SELECT * FROM test1 a, (select 5 AS x, 6 AS y) AS b LIMIT 1
551: }]
552: regsub -all {subquery_[0-9a-fA-F]+_} $x {subquery} x
553: set x
554: } {a.f1 11 a.f2 22 b.x 5 b.y 6}
555: do_test select1-6.9.9 {
556: execsql2 {
557: SELECT a.f1, b.f2 FROM test1 a, test1 b LIMIT 1
558: }
559: } {test1.f1 11 test1.f2 22}
560: do_test select1-6.9.10 {
561: execsql2 {
562: SELECT f1, t1 FROM test1, test2 LIMIT 1
563: }
564: } {test1.f1 11 test2.t1 abc}
565: do_test select1-6.9.11 {
566: db eval {
567: PRAGMA short_column_names=ON;
568: PRAGMA full_column_names=ON;
569: }
570: execsql2 {
571: SELECT a.f1, b.f2 FROM test1 a, test1 b LIMIT 1
572: }
573: } {test1.f1 11 test1.f2 22}
574: do_test select1-6.9.12 {
575: execsql2 {
576: SELECT f1, t1 FROM test1, test2 LIMIT 1
577: }
578: } {test1.f1 11 test2.t1 abc}
579: do_test select1-6.9.13 {
580: db eval {
581: PRAGMA short_column_names=ON;
582: PRAGMA full_column_names=OFF;
583: }
584: execsql2 {
585: SELECT a.f1, b.f1 FROM test1 a, test1 b LIMIT 1
586: }
587: } {f1 11 f1 11}
588: do_test select1-6.9.14 {
589: execsql2 {
590: SELECT f1, t1 FROM test1, test2 LIMIT 1
591: }
592: } {f1 11 t1 abc}
593: do_test select1-6.9.15 {
594: db eval {
595: PRAGMA short_column_names=OFF;
596: PRAGMA full_column_names=ON;
597: }
598: execsql2 {
599: SELECT a.f1, b.f1 FROM test1 a, test1 b LIMIT 1
600: }
601: } {test1.f1 11 test1.f1 11}
602: do_test select1-6.9.16 {
603: execsql2 {
604: SELECT f1, t1 FROM test1, test2 LIMIT 1
605: }
606: } {test1.f1 11 test2.t1 abc}
607:
608:
609: db eval {
610: PRAGMA short_column_names=ON;
611: PRAGMA full_column_names=OFF;
612: }
613:
614: ifcapable compound {
615: do_test select1-6.10 {
616: set v [catch {execsql2 {
617: SELECT f1 FROM test1 UNION SELECT f2 FROM test1
618: ORDER BY f2;
619: }} msg]
620: lappend v $msg
621: } {0 {f1 11 f1 22 f1 33 f1 44}}
622: do_test select1-6.11 {
623: set v [catch {execsql2 {
624: SELECT f1 FROM test1 UNION SELECT f2+100 FROM test1
625: ORDER BY f2+101;
626: }} msg]
627: lappend v $msg
628: } {1 {1st ORDER BY term does not match any column in the result set}}
629:
630: # Ticket #2296
631: ifcapable subquery&&compound {
632: do_test select1-6.20 {
633: execsql {
634: CREATE TABLE t6(a TEXT, b TEXT);
635: INSERT INTO t6 VALUES('a','0');
636: INSERT INTO t6 VALUES('b','1');
637: INSERT INTO t6 VALUES('c','2');
638: INSERT INTO t6 VALUES('d','3');
639: SELECT a FROM t6 WHERE b IN
640: (SELECT b FROM t6 WHERE a<='b' UNION SELECT '3' AS x
641: ORDER BY 1 LIMIT 1)
642: }
643: } {a}
644: do_test select1-6.21 {
645: execsql {
646: SELECT a FROM t6 WHERE b IN
647: (SELECT b FROM t6 WHERE a<='b' UNION SELECT '3' AS x
648: ORDER BY 1 DESC LIMIT 1)
649: }
650: } {d}
651: do_test select1-6.22 {
652: execsql {
653: SELECT a FROM t6 WHERE b IN
654: (SELECT b FROM t6 WHERE a<='b' UNION SELECT '3' AS x
655: ORDER BY b LIMIT 2)
656: ORDER BY a;
657: }
658: } {a b}
659: do_test select1-6.23 {
660: execsql {
661: SELECT a FROM t6 WHERE b IN
662: (SELECT b FROM t6 WHERE a<='b' UNION SELECT '3' AS x
663: ORDER BY x DESC LIMIT 2)
664: ORDER BY a;
665: }
666: } {b d}
667: }
668:
669: } ;#ifcapable compound
670:
671: do_test select1-7.1 {
672: set v [catch {execsql {
673: SELECT f1 FROM test1 WHERE f2=;
674: }} msg]
675: lappend v $msg
676: } {1 {near ";": syntax error}}
677: ifcapable compound {
678: do_test select1-7.2 {
679: set v [catch {execsql {
680: SELECT f1 FROM test1 UNION SELECT WHERE;
681: }} msg]
682: lappend v $msg
683: } {1 {near "WHERE": syntax error}}
684: } ;# ifcapable compound
685: do_test select1-7.3 {
686: set v [catch {execsql {SELECT f1 FROM test1 as 'hi', test2 as}} msg]
687: lappend v $msg
688: } {1 {near "as": syntax error}}
689: do_test select1-7.4 {
690: set v [catch {execsql {
691: SELECT f1 FROM test1 ORDER BY;
692: }} msg]
693: lappend v $msg
694: } {1 {near ";": syntax error}}
695: do_test select1-7.5 {
696: set v [catch {execsql {
697: SELECT f1 FROM test1 ORDER BY f1 desc, f2 where;
698: }} msg]
699: lappend v $msg
700: } {1 {near "where": syntax error}}
701: do_test select1-7.6 {
702: set v [catch {execsql {
703: SELECT count(f1,f2 FROM test1;
704: }} msg]
705: lappend v $msg
706: } {1 {near "FROM": syntax error}}
707: do_test select1-7.7 {
708: set v [catch {execsql {
709: SELECT count(f1,f2+) FROM test1;
710: }} msg]
711: lappend v $msg
712: } {1 {near ")": syntax error}}
713: do_test select1-7.8 {
714: set v [catch {execsql {
715: SELECT f1 FROM test1 ORDER BY f2, f1+;
716: }} msg]
717: lappend v $msg
718: } {1 {near ";": syntax error}}
719: do_test select1-7.9 {
720: catchsql {
721: SELECT f1 FROM test1 LIMIT 5+3 OFFSET 11 ORDER BY f2;
722: }
723: } {1 {near "ORDER": syntax error}}
724:
725: do_test select1-8.1 {
726: execsql {SELECT f1 FROM test1 WHERE 4.3+2.4 OR 1 ORDER BY f1}
727: } {11 33}
728: do_test select1-8.2 {
729: execsql {
730: SELECT f1 FROM test1 WHERE ('x' || f1) BETWEEN 'x10' AND 'x20'
731: ORDER BY f1
732: }
733: } {11}
734: do_test select1-8.3 {
735: execsql {
736: SELECT f1 FROM test1 WHERE 5-3==2
737: ORDER BY f1
738: }
739: } {11 33}
740:
741: # TODO: This test is failing because f1 is now being loaded off the
742: # disk as a vdbe integer, not a string. Hence the value of f1/(f1-11)
743: # changes because of rounding. Disable the test for now.
744: if 0 {
745: do_test select1-8.4 {
746: execsql {
747: SELECT coalesce(f1/(f1-11),'x'),
748: coalesce(min(f1/(f1-11),5),'y'),
749: coalesce(max(f1/(f1-33),6),'z')
750: FROM test1 ORDER BY f1
751: }
752: } {x y 6 1.5 1.5 z}
753: }
754: do_test select1-8.5 {
755: execsql {
756: SELECT min(1,2,3), -max(1,2,3)
757: FROM test1 ORDER BY f1
758: }
759: } {1 -3 1 -3}
760:
761:
762: # Check the behavior when the result set is empty
763: #
764: # SQLite v3 always sets r(*).
765: #
766: # do_test select1-9.1 {
767: # catch {unset r}
768: # set r(*) {}
769: # db eval {SELECT * FROM test1 WHERE f1<0} r {}
770: # set r(*)
771: # } {}
772: do_test select1-9.2 {
773: execsql {PRAGMA empty_result_callbacks=on}
774: catch {unset r}
775: set r(*) {}
776: db eval {SELECT * FROM test1 WHERE f1<0} r {}
777: set r(*)
778: } {f1 f2}
779: ifcapable subquery {
780: do_test select1-9.3 {
781: set r(*) {}
782: db eval {SELECT * FROM test1 WHERE f1<(select count(*) from test2)} r {}
783: set r(*)
784: } {f1 f2}
785: }
786: do_test select1-9.4 {
787: set r(*) {}
788: db eval {SELECT * FROM test1 ORDER BY f1} r {}
789: set r(*)
790: } {f1 f2}
791: do_test select1-9.5 {
792: set r(*) {}
793: db eval {SELECT * FROM test1 WHERE f1<0 ORDER BY f1} r {}
794: set r(*)
795: } {f1 f2}
796: unset r
797:
798: # Check for ORDER BY clauses that refer to an AS name in the column list
799: #
800: do_test select1-10.1 {
801: execsql {
802: SELECT f1 AS x FROM test1 ORDER BY x
803: }
804: } {11 33}
805: do_test select1-10.2 {
806: execsql {
807: SELECT f1 AS x FROM test1 ORDER BY -x
808: }
809: } {33 11}
810: do_test select1-10.3 {
811: execsql {
812: SELECT f1-23 AS x FROM test1 ORDER BY abs(x)
813: }
814: } {10 -12}
815: do_test select1-10.4 {
816: execsql {
817: SELECT f1-23 AS x FROM test1 ORDER BY -abs(x)
818: }
819: } {-12 10}
820: do_test select1-10.5 {
821: execsql {
822: SELECT f1-22 AS x, f2-22 as y FROM test1
823: }
824: } {-11 0 11 22}
825: do_test select1-10.6 {
826: execsql {
827: SELECT f1-22 AS x, f2-22 as y FROM test1 WHERE x>0 AND y<50
828: }
829: } {11 22}
830: do_test select1-10.7 {
831: execsql {
832: SELECT f1 COLLATE nocase AS x FROM test1 ORDER BY x
833: }
834: } {11 33}
835:
836: # Check the ability to specify "TABLE.*" in the result set of a SELECT
837: #
838: do_test select1-11.1 {
839: execsql {
840: DELETE FROM t3;
841: DELETE FROM t4;
842: INSERT INTO t3 VALUES(1,2);
843: INSERT INTO t4 VALUES(3,4);
844: SELECT * FROM t3, t4;
845: }
846: } {1 2 3 4}
847: do_test select1-11.2.1 {
848: execsql {
849: SELECT * FROM t3, t4;
850: }
851: } {1 2 3 4}
852: do_test select1-11.2.2 {
853: execsql2 {
854: SELECT * FROM t3, t4;
855: }
856: } {a 3 b 4 a 3 b 4}
857: do_test select1-11.4.1 {
858: execsql {
859: SELECT t3.*, t4.b FROM t3, t4;
860: }
861: } {1 2 4}
862: do_test select1-11.4.2 {
863: execsql {
864: SELECT "t3".*, t4.b FROM t3, t4;
865: }
866: } {1 2 4}
867: do_test select1-11.5.1 {
868: execsql2 {
869: SELECT t3.*, t4.b FROM t3, t4;
870: }
871: } {a 1 b 4 b 4}
872: do_test select1-11.6 {
873: execsql2 {
874: SELECT x.*, y.b FROM t3 AS x, t4 AS y;
875: }
876: } {a 1 b 4 b 4}
877: do_test select1-11.7 {
878: execsql {
879: SELECT t3.b, t4.* FROM t3, t4;
880: }
881: } {2 3 4}
882: do_test select1-11.8 {
883: execsql2 {
884: SELECT t3.b, t4.* FROM t3, t4;
885: }
886: } {b 4 a 3 b 4}
887: do_test select1-11.9 {
888: execsql2 {
889: SELECT x.b, y.* FROM t3 AS x, t4 AS y;
890: }
891: } {b 4 a 3 b 4}
892: do_test select1-11.10 {
893: catchsql {
894: SELECT t5.* FROM t3, t4;
895: }
896: } {1 {no such table: t5}}
897: do_test select1-11.11 {
898: catchsql {
899: SELECT t3.* FROM t3 AS x, t4;
900: }
901: } {1 {no such table: t3}}
902: ifcapable subquery {
903: do_test select1-11.12 {
904: execsql2 {
905: SELECT t3.* FROM t3, (SELECT max(a), max(b) FROM t4)
906: }
907: } {a 1 b 2}
908: do_test select1-11.13 {
909: execsql2 {
910: SELECT t3.* FROM (SELECT max(a), max(b) FROM t4), t3
911: }
912: } {a 1 b 2}
913: do_test select1-11.14 {
914: execsql2 {
915: SELECT * FROM t3, (SELECT max(a), max(b) FROM t4) AS 'tx'
916: }
917: } {a 1 b 2 max(a) 3 max(b) 4}
918: do_test select1-11.15 {
919: execsql2 {
920: SELECT y.*, t3.* FROM t3, (SELECT max(a), max(b) FROM t4) AS y
921: }
922: } {max(a) 3 max(b) 4 a 1 b 2}
923: }
924: do_test select1-11.16 {
925: execsql2 {
926: SELECT y.* FROM t3 as y, t4 as z
927: }
928: } {a 1 b 2}
929:
930: # Tests of SELECT statements without a FROM clause.
931: #
932: do_test select1-12.1 {
933: execsql2 {
934: SELECT 1+2+3
935: }
936: } {1+2+3 6}
937: do_test select1-12.2 {
938: execsql2 {
939: SELECT 1,'hello',2
940: }
941: } {1 1 'hello' hello 2 2}
942: do_test select1-12.3 {
943: execsql2 {
944: SELECT 1 AS 'a','hello' AS 'b',2 AS 'c'
945: }
946: } {a 1 b hello c 2}
947: do_test select1-12.4 {
948: execsql {
949: DELETE FROM t3;
950: INSERT INTO t3 VALUES(1,2);
951: }
952: } {}
953:
954: ifcapable compound {
955: do_test select1-12.5 {
956: execsql {
957: SELECT * FROM t3 UNION SELECT 3 AS 'a', 4 ORDER BY a;
958: }
959: } {1 2 3 4}
960:
961: do_test select1-12.6 {
962: execsql {
963: SELECT 3, 4 UNION SELECT * FROM t3;
964: }
965: } {1 2 3 4}
966: } ;# ifcapable compound
967:
968: ifcapable subquery {
969: do_test select1-12.7 {
970: execsql {
971: SELECT * FROM t3 WHERE a=(SELECT 1);
972: }
973: } {1 2}
974: do_test select1-12.8 {
975: execsql {
976: SELECT * FROM t3 WHERE a=(SELECT 2);
977: }
978: } {}
979: }
980:
981: ifcapable {compound && subquery} {
982: do_test select1-12.9 {
983: execsql2 {
984: SELECT x FROM (
985: SELECT a AS x, b AS y FROM t3 UNION SELECT a,b FROM t4 ORDER BY a,b
986: ) ORDER BY x;
987: }
988: } {x 1 x 3}
989: do_test select1-12.10 {
990: execsql2 {
991: SELECT z.x FROM (
992: SELECT a AS x,b AS y FROM t3 UNION SELECT a, b FROM t4 ORDER BY a,b
993: ) AS 'z' ORDER BY x;
994: }
995: } {x 1 x 3}
996: } ;# ifcapable compound
997:
998:
999: # Check for a VDBE stack growth problem that existed at one point.
1000: #
1001: ifcapable subquery {
1002: do_test select1-13.1 {
1003: execsql {
1004: BEGIN;
1005: create TABLE abc(a, b, c, PRIMARY KEY(a, b));
1006: INSERT INTO abc VALUES(1, 1, 1);
1007: }
1008: for {set i 0} {$i<10} {incr i} {
1009: execsql {
1010: INSERT INTO abc SELECT a+(select max(a) FROM abc),
1011: b+(select max(a) FROM abc), c+(select max(a) FROM abc) FROM abc;
1012: }
1013: }
1014: execsql {COMMIT}
1015:
1016: # This used to seg-fault when the problem existed.
1017: execsql {
1018: SELECT count(
1019: (SELECT a FROM abc WHERE a = NULL AND b >= upper.c)
1020: ) FROM abc AS upper;
1021: }
1022: } {0}
1023: }
1024:
1025: foreach tab [db eval {SELECT name FROM sqlite_master WHERE type = 'table'}] {
1026: db eval "DROP TABLE $tab"
1027: }
1028: db close
1029: sqlite3 db test.db
1030:
1031: do_test select1-14.1 {
1032: execsql {
1033: SELECT * FROM sqlite_master WHERE rowid>10;
1034: SELECT * FROM sqlite_master WHERE rowid=10;
1035: SELECT * FROM sqlite_master WHERE rowid<10;
1036: SELECT * FROM sqlite_master WHERE rowid<=10;
1037: SELECT * FROM sqlite_master WHERE rowid>=10;
1038: SELECT * FROM sqlite_master;
1039: }
1040: } {}
1041: do_test select1-14.2 {
1042: execsql {
1043: SELECT 10 IN (SELECT rowid FROM sqlite_master);
1044: }
1045: } {0}
1046:
1047: if {[db one {PRAGMA locking_mode}]=="normal"} {
1048: # Check that ticket #3771 has been fixed. This test does not
1049: # work with locking_mode=EXCLUSIVE so disable in that case.
1050: #
1051: do_test select1-15.1 {
1052: execsql {
1053: CREATE TABLE t1(a);
1054: CREATE INDEX i1 ON t1(a);
1055: INSERT INTO t1 VALUES(1);
1056: INSERT INTO t1 VALUES(2);
1057: INSERT INTO t1 VALUES(3);
1058: }
1059: } {}
1060: do_test select1-15.2 {
1061: sqlite3 db2 test.db
1062: execsql { DROP INDEX i1 } db2
1063: db2 close
1064: } {}
1065: do_test select1-15.3 {
1066: execsql { SELECT 2 IN (SELECT a FROM t1) }
1067: } {1}
1068: }
1069:
1070: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to select1.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