| blob | 875c87c84ab5f92d8363a2a1f7543f4468cc45b9 |
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.70 2009/05/28 01:00:56 drh Exp $
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
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}}
27 execsql {CREATE TABLE test1(f1 int, f2 int)}
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}}
38 execsql {INSERT INTO test1(f1,f2) VALUES(11,22)}
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}
68 execsql {CREATE TABLE test2(r1 real, r2 real)}
69 execsql {INSERT INTO test2(r1,r2) VALUES(1.1,2.2)}
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}
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}
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()}}
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}}
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()}}
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()}}
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}}
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}
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}
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}
408 execsql {CREATE TABLE test2(t1 text, t2 text)}
409 execsql {INSERT INTO test2 VALUES('abc','xyz')}
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}}
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 {sq_[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}
609 db eval {
610 PRAGMA short_column_names=ON;
611 PRAGMA full_column_names=OFF;
612 }
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}}
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 }
669 } ;#ifcapable compound
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}}
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}
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}
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
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}
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}
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 } {}
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}
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
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 }
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
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}
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 }
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
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}
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 }
1070 # Crash bug reported on the mailing list on 2012-02-23
1071 #
1072 do_test select1-16.1 {
1073 catchsql {SELECT 1 FROM (SELECT *)}
1074 } {1 {no tables specified}}
1076 finish_test