xref: /illumos-gate/usr/src/lib/libsqlite/test/select1.test (revision 1da57d551424de5a9d469760be7c4b4d4f10a755)
1#
2# 2001 September 15
3#
4# The author disclaims copyright to this source code.  In place of
5# a legal notice, here is a blessing:
6#
7#    May you do good and not evil.
8#    May you find forgiveness for yourself and forgive others.
9#    May you share freely, never taking more than you give.
10#
11#***********************************************************************
12# This file implements regression tests for SQLite library.  The
13# focus of this file is testing the SELECT statement.
14#
15# $Id: select1.test,v 1.30.2.3 2004/07/20 01:45:49 drh Exp $
16
17set testdir [file dirname $argv0]
18source $testdir/tester.tcl
19
20# Try to select on a non-existant table.
21#
22do_test select1-1.1 {
23  set v [catch {execsql {SELECT * FROM test1}} msg]
24  lappend v $msg
25} {1 {no such table: test1}}
26
27execsql {CREATE TABLE test1(f1 int, f2 int)}
28
29do_test select1-1.2 {
30  set v [catch {execsql {SELECT * FROM test1, test2}} msg]
31  lappend v $msg
32} {1 {no such table: test2}}
33do_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
38execsql {INSERT INTO test1(f1,f2) VALUES(11,22)}
39
40
41# Make sure the columns are extracted correctly.
42#
43do_test select1-1.4 {
44  execsql {SELECT f1 FROM test1}
45} {11}
46do_test select1-1.5 {
47  execsql {SELECT f2 FROM test1}
48} {22}
49do_test select1-1.6 {
50  execsql {SELECT f2, f1 FROM test1}
51} {22 11}
52do_test select1-1.7 {
53  execsql {SELECT f1, f2 FROM test1}
54} {11 22}
55do_test select1-1.8 {
56  execsql {SELECT * FROM test1}
57} {11 22}
58do_test select1-1.8.1 {
59  execsql {SELECT *, * FROM test1}
60} {11 22 11 22}
61do_test select1-1.8.2 {
62  execsql {SELECT *, min(f1,f2), max(f1,f2) FROM test1}
63} {11 22 11 22}
64do_test select1-1.8.3 {
65  execsql {SELECT 'one', *, 'two', * FROM test1}
66} {one 11 22 two 11 22}
67
68execsql {CREATE TABLE test2(r1 real, r2 real)}
69execsql {INSERT INTO test2(r1,r2) VALUES(1.1,2.2)}
70
71do_test select1-1.9 {
72  execsql {SELECT * FROM test1, test2}
73} {11 22 1.1 2.2}
74do_test select1-1.9.1 {
75  execsql {SELECT *, 'hi' FROM test1, test2}
76} {11 22 1.1 2.2 hi}
77do_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}
80do_test select1-1.10 {
81  execsql {SELECT test1.f1, test2.r1 FROM test1, test2}
82} {11 1.1}
83do_test select1-1.11 {
84  execsql {SELECT test1.f1, test2.r1 FROM test2, test1}
85} {11 1.1}
86do_test select1-1.11.1 {
87  execsql {SELECT * FROM test2, test1}
88} {1.1 2.2 11 22}
89do_test select1-1.11.2 {
90  execsql {SELECT * FROM test1 AS a, test1 AS b}
91} {11 22 11 22}
92do_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}
96do_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
101set long {This is a string that is too big to fit inside a NBFS buffer}
102do_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#
120do_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()}}
124do_test select1-2.2 {
125  set v [catch {execsql {SELECT count(f1) FROM test1}} msg]
126  lappend v $msg
127} {0 2}
128do_test select1-2.3 {
129  set v [catch {execsql {SELECT Count() FROM test1}} msg]
130  lappend v $msg
131} {0 2}
132do_test select1-2.4 {
133  set v [catch {execsql {SELECT COUNT(*) FROM test1}} msg]
134  lappend v $msg
135} {0 2}
136do_test select1-2.5 {
137  set v [catch {execsql {SELECT COUNT(*)+1 FROM test1}} msg]
138  lappend v $msg
139} {0 3}
140do_test select1-2.5.1 {
141  execsql {SELECT count(*),count(a),count(b) FROM t3}
142} {4 3 3}
143do_test select1-2.5.2 {
144  execsql {SELECT count(*),count(a),count(b) FROM t4}
145} {1 0 1}
146do_test select1-2.5.3 {
147  execsql {SELECT count(*),count(a),count(b) FROM t4 WHERE b=5}
148} {0 0 0}
149do_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()}}
153do_test select1-2.7 {
154  set v [catch {execsql {SELECT Min(f1) FROM test1}} msg]
155  lappend v $msg
156} {0 11}
157do_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}}
161do_test select1-2.8.1 {
162  execsql {SELECT coalesce(min(a),'xyzzy') FROM t3}
163} {11}
164do_test select1-2.8.2 {
165  execsql {SELECT min(coalesce(a,'xyzzy')) FROM t3}
166} {11}
167do_test select1-2.8.3 {
168  execsql {SELECT min(b), min(b) FROM t4}
169} [list $long $long]
170do_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()}}
174do_test select1-2.10 {
175  set v [catch {execsql {SELECT Max(f1) FROM test1}} msg]
176  lappend v $msg
177} {0 33}
178do_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}}
182do_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}}
186do_test select1-2.13 {
187  set v [catch {execsql {SELECT MAX(f1)+1 FROM test1}} msg]
188  lappend v $msg
189} {0 34}
190do_test select1-2.13.1 {
191  execsql {SELECT coalesce(max(a),'xyzzy') FROM t3}
192} {abc}
193do_test select1-2.13.2 {
194  execsql {SELECT max(coalesce(a,'xyzzy')) FROM t3}
195} {xyzzy}
196do_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()}}
200do_test select1-2.15 {
201  set v [catch {execsql {SELECT Sum(f1) FROM test1}} msg]
202  lappend v $msg
203} {0 44}
204do_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()}}
208do_test select1-2.17 {
209  set v [catch {execsql {SELECT SUM(f1)+1 FROM test1}} msg]
210  lappend v $msg
211} {0 45}
212do_test select1-2.17.1 {
213  execsql {SELECT sum(a) FROM t3}
214} {44}
215do_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}}
219do_test select1-2.19 {
220  set v [catch {execsql {SELECT SUM(min(f1,f2)) FROM test1}} msg]
221  lappend v $msg
222} {0 44}
223do_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# WHERE clause expressions
229#
230do_test select1-3.1 {
231  set v [catch {execsql {SELECT f1 FROM test1 WHERE f1<11}} msg]
232  lappend v $msg
233} {0 {}}
234do_test select1-3.2 {
235  set v [catch {execsql {SELECT f1 FROM test1 WHERE f1<=11}} msg]
236  lappend v $msg
237} {0 11}
238do_test select1-3.3 {
239  set v [catch {execsql {SELECT f1 FROM test1 WHERE f1=11}} msg]
240  lappend v $msg
241} {0 11}
242do_test select1-3.4 {
243  set v [catch {execsql {SELECT f1 FROM test1 WHERE f1>=11}} msg]
244  lappend v [lsort $msg]
245} {0 {11 33}}
246do_test select1-3.5 {
247  set v [catch {execsql {SELECT f1 FROM test1 WHERE f1>11}} msg]
248  lappend v [lsort $msg]
249} {0 33}
250do_test select1-3.6 {
251  set v [catch {execsql {SELECT f1 FROM test1 WHERE f1!=11}} msg]
252  lappend v [lsort $msg]
253} {0 33}
254do_test select1-3.7 {
255  set v [catch {execsql {SELECT f1 FROM test1 WHERE min(f1,f2)!=11}} msg]
256  lappend v [lsort $msg]
257} {0 33}
258do_test select1-3.8 {
259  set v [catch {execsql {SELECT f1 FROM test1 WHERE max(f1,f2)!=11}} msg]
260  lappend v [lsort $msg]
261} {0 {11 33}}
262do_test select1-3.9 {
263  set v [catch {execsql {SELECT f1 FROM test1 WHERE count(f1,f2)!=11}} msg]
264  lappend v $msg
265} {1 {wrong number of arguments to function count()}}
266
267# ORDER BY expressions
268#
269do_test select1-4.1 {
270  set v [catch {execsql {SELECT f1 FROM test1 ORDER BY f1}} msg]
271  lappend v $msg
272} {0 {11 33}}
273do_test select1-4.2 {
274  set v [catch {execsql {SELECT f1 FROM test1 ORDER BY -f1}} msg]
275  lappend v $msg
276} {0 {33 11}}
277do_test select1-4.3 {
278  set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1,f2)}} msg]
279  lappend v $msg
280} {0 {11 33}}
281do_test select1-4.4 {
282  set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1)}} msg]
283  lappend v $msg
284} {1 {misuse of aggregate function min()}}
285do_test select1-4.5 {
286  catchsql {
287    SELECT f1 FROM test1 ORDER BY 8.4;
288  }
289} {1 {ORDER BY terms must not be non-integer constants}}
290do_test select1-4.6 {
291  catchsql {
292    SELECT f1 FROM test1 ORDER BY '8.4';
293  }
294} {1 {ORDER BY terms must not be non-integer constants}}
295do_test select1-4.7 {
296  catchsql {
297    SELECT f1 FROM test1 ORDER BY 'xyz';
298  }
299} {1 {ORDER BY terms must not be non-integer constants}}
300do_test select1-4.8 {
301  execsql {
302    CREATE TABLE t5(a,b);
303    INSERT INTO t5 VALUES(1,10);
304    INSERT INTO t5 VALUES(2,9);
305    SELECT * FROM t5 ORDER BY 1;
306  }
307} {1 10 2 9}
308do_test select1-4.9 {
309  execsql {
310    SELECT * FROM t5 ORDER BY 2;
311  }
312} {2 9 1 10}
313do_test select1-4.10 {
314  catchsql {
315    SELECT * FROM t5 ORDER BY 3;
316  }
317} {1 {ORDER BY column number 3 out of range - should be between 1 and 2}}
318do_test select1-4.11 {
319  execsql {
320    INSERT INTO t5 VALUES(3,10);
321    SELECT * FROM t5 ORDER BY 2, 1 DESC;
322  }
323} {2 9 3 10 1 10}
324do_test select1-4.12 {
325  execsql {
326    SELECT * FROM t5 ORDER BY 1 DESC, b;
327  }
328} {3 10 2 9 1 10}
329do_test select1-4.13 {
330  execsql {
331    SELECT * FROM t5 ORDER BY b DESC, 1;
332  }
333} {1 10 3 10 2 9}
334
335
336# ORDER BY ignored on an aggregate query
337#
338do_test select1-5.1 {
339  set v [catch {execsql {SELECT max(f1) FROM test1 ORDER BY f2}} msg]
340  lappend v $msg
341} {0 33}
342
343execsql {CREATE TABLE test2(t1 test, t2 text)}
344execsql {INSERT INTO test2 VALUES('abc','xyz')}
345
346# Check for column naming
347#
348do_test select1-6.1 {
349  set v [catch {execsql2 {SELECT f1 FROM test1 ORDER BY f2}} msg]
350  lappend v $msg
351} {0 {f1 11 f1 33}}
352do_test select1-6.1.1 {
353  execsql {PRAGMA full_column_names=on}
354  set v [catch {execsql2 {SELECT f1 FROM test1 ORDER BY f2}} msg]
355  lappend v $msg
356} {0 {test1.f1 11 test1.f1 33}}
357do_test select1-6.1.2 {
358  set v [catch {execsql2 {SELECT f1 as 'f1' FROM test1 ORDER BY f2}} msg]
359  lappend v $msg
360} {0 {f1 11 f1 33}}
361do_test select1-6.1.3 {
362  set v [catch {execsql2 {SELECT * FROM test1 WHERE f1==11}} msg]
363  lappend v $msg
364} {0 {test1.f1 11 test1.f2 22}}
365do_test select1-6.1.4 {
366  set v [catch {execsql2 {SELECT DISTINCT * FROM test1 WHERE f1==11}} msg]
367  execsql {PRAGMA full_column_names=off}
368  lappend v $msg
369} {0 {test1.f1 11 test1.f2 22}}
370do_test select1-6.1.5 {
371  set v [catch {execsql2 {SELECT * FROM test1 WHERE f1==11}} msg]
372  lappend v $msg
373} {0 {f1 11 f2 22}}
374do_test select1-6.1.6 {
375  set v [catch {execsql2 {SELECT DISTINCT * FROM test1 WHERE f1==11}} msg]
376  lappend v $msg
377} {0 {f1 11 f2 22}}
378do_test select1-6.2 {
379  set v [catch {execsql2 {SELECT f1 as xyzzy FROM test1 ORDER BY f2}} msg]
380  lappend v $msg
381} {0 {xyzzy 11 xyzzy 33}}
382do_test select1-6.3 {
383  set v [catch {execsql2 {SELECT f1 as "xyzzy" FROM test1 ORDER BY f2}} msg]
384  lappend v $msg
385} {0 {xyzzy 11 xyzzy 33}}
386do_test select1-6.3.1 {
387  set v [catch {execsql2 {SELECT f1 as 'xyzzy ' FROM test1 ORDER BY f2}} msg]
388  lappend v $msg
389} {0 {{xyzzy } 11 {xyzzy } 33}}
390do_test select1-6.4 {
391  set v [catch {execsql2 {SELECT f1+F2 as xyzzy FROM test1 ORDER BY f2}} msg]
392  lappend v $msg
393} {0 {xyzzy 33 xyzzy 77}}
394do_test select1-6.4a {
395  set v [catch {execsql2 {SELECT f1+F2 FROM test1 ORDER BY f2}} msg]
396  lappend v $msg
397} {0 {f1+F2 33 f1+F2 77}}
398do_test select1-6.5 {
399  set v [catch {execsql2 {SELECT test1.f1+F2 FROM test1 ORDER BY f2}} msg]
400  lappend v $msg
401} {0 {test1.f1+F2 33 test1.f1+F2 77}}
402do_test select1-6.5.1 {
403  execsql2 {PRAGMA full_column_names=on}
404  set v [catch {execsql2 {SELECT test1.f1+F2 FROM test1 ORDER BY f2}} msg]
405  execsql2 {PRAGMA full_column_names=off}
406  lappend v $msg
407} {0 {test1.f1+F2 33 test1.f1+F2 77}}
408do_test select1-6.6 {
409  set v [catch {execsql2 {SELECT test1.f1+F2, t1 FROM test1, test2
410         ORDER BY f2}} msg]
411  lappend v $msg
412} {0 {test1.f1+F2 33 t1 abc test1.f1+F2 77 t1 abc}}
413do_test select1-6.7 {
414  set v [catch {execsql2 {SELECT A.f1, t1 FROM test1 as A, test2
415         ORDER BY f2}} msg]
416  lappend v $msg
417} {0 {A.f1 11 t1 abc A.f1 33 t1 abc}}
418do_test select1-6.8 {
419  set v [catch {execsql2 {SELECT A.f1, f1 FROM test1 as A, test1 as B
420         ORDER BY f2}} msg]
421  lappend v $msg
422} {1 {ambiguous column name: f1}}
423do_test select1-6.8b {
424  set v [catch {execsql2 {SELECT A.f1, B.f1 FROM test1 as A, test1 as B
425         ORDER BY f2}} msg]
426  lappend v $msg
427} {1 {ambiguous column name: f2}}
428do_test select1-6.8c {
429  set v [catch {execsql2 {SELECT A.f1, f1 FROM test1 as A, test1 as A
430         ORDER BY f2}} msg]
431  lappend v $msg
432} {1 {ambiguous column name: A.f1}}
433do_test select1-6.9 {
434  set v [catch {execsql2 {SELECT A.f1, B.f1 FROM test1 as A, test1 as B
435         ORDER BY A.f1, B.f1}} msg]
436  lappend v $msg
437} {0 {A.f1 11 B.f1 11 A.f1 11 B.f1 33 A.f1 33 B.f1 11 A.f1 33 B.f1 33}}
438do_test select1-6.10 {
439  set v [catch {execsql2 {
440    SELECT f1 FROM test1 UNION SELECT f2 FROM test1
441    ORDER BY f2;
442  }} msg]
443  lappend v $msg
444} {0 {f2 11 f2 22 f2 33 f2 44}}
445do_test select1-6.11 {
446  set v [catch {execsql2 {
447    SELECT f1 FROM test1 UNION SELECT f2+100 FROM test1
448    ORDER BY f2+100;
449  }} msg]
450  lappend v $msg
451} {0 {f2+100 11 f2+100 33 f2+100 122 f2+100 144}}
452
453do_test select1-7.1 {
454  set v [catch {execsql {
455     SELECT f1 FROM test1 WHERE f2=;
456  }} msg]
457  lappend v $msg
458} {1 {near ";": syntax error}}
459do_test select1-7.2 {
460  set v [catch {execsql {
461     SELECT f1 FROM test1 UNION SELECT WHERE;
462  }} msg]
463  lappend v $msg
464} {1 {near "WHERE": syntax error}}
465do_test select1-7.3 {
466  set v [catch {execsql {SELECT f1 FROM test1 as 'hi', test2 as}} msg]
467  lappend v $msg
468} {1 {near "as": syntax error}}
469do_test select1-7.4 {
470  set v [catch {execsql {
471     SELECT f1 FROM test1 ORDER BY;
472  }} msg]
473  lappend v $msg
474} {1 {near ";": syntax error}}
475do_test select1-7.5 {
476  set v [catch {execsql {
477     SELECT f1 FROM test1 ORDER BY f1 desc, f2 where;
478  }} msg]
479  lappend v $msg
480} {1 {near "where": syntax error}}
481do_test select1-7.6 {
482  set v [catch {execsql {
483     SELECT count(f1,f2 FROM test1;
484  }} msg]
485  lappend v $msg
486} {1 {near "FROM": syntax error}}
487do_test select1-7.7 {
488  set v [catch {execsql {
489     SELECT count(f1,f2+) FROM test1;
490  }} msg]
491  lappend v $msg
492} {1 {near ")": syntax error}}
493do_test select1-7.8 {
494  set v [catch {execsql {
495     SELECT f1 FROM test1 ORDER BY f2, f1+;
496  }} msg]
497  lappend v $msg
498} {1 {near ";": syntax error}}
499
500do_test select1-8.1 {
501  execsql {SELECT f1 FROM test1 WHERE 4.3+2.4 OR 1 ORDER BY f1}
502} {11 33}
503do_test select1-8.2 {
504  execsql {
505    SELECT f1 FROM test1 WHERE ('x' || f1) BETWEEN 'x10' AND 'x20'
506    ORDER BY f1
507  }
508} {11}
509do_test select1-8.3 {
510  execsql {
511    SELECT f1 FROM test1 WHERE 5-3==2
512    ORDER BY f1
513  }
514} {11 33}
515do_test select1-8.4 {
516  execsql {
517    SELECT coalesce(f1/(f1-11),'x'),
518           coalesce(min(f1/(f1-11),5),'y'),
519           coalesce(max(f1/(f1-33),6),'z')
520    FROM test1 ORDER BY f1
521  }
522} {x y 6 1.5 1.5 z}
523do_test select1-8.5 {
524  execsql {
525    SELECT min(1,2,3), -max(1,2,3)
526    FROM test1 ORDER BY f1
527  }
528} {1 -3 1 -3}
529
530
531# Check the behavior when the result set is empty
532#
533do_test select1-9.1 {
534  catch {unset r}
535  set r(*) {}
536  db eval {SELECT * FROM test1 WHERE f1<0} r {}
537  set r(*)
538} {}
539do_test select1-9.2 {
540  execsql {PRAGMA empty_result_callbacks=on}
541  set r(*) {}
542  db eval {SELECT * FROM test1 WHERE f1<0} r {}
543  set r(*)
544} {f1 f2}
545do_test select1-9.3 {
546  set r(*) {}
547  db eval {SELECT * FROM test1 WHERE f1<(select count(*) from test2)} r {}
548  set r(*)
549} {f1 f2}
550do_test select1-9.4 {
551  set r(*) {}
552  db eval {SELECT * FROM test1 ORDER BY f1} r {}
553  set r(*)
554} {f1 f2}
555do_test select1-9.5 {
556  set r(*) {}
557  db eval {SELECT * FROM test1 WHERE f1<0 ORDER BY f1} r {}
558  set r(*)
559} {f1 f2}
560unset r
561
562# Check for ORDER BY clauses that refer to an AS name in the column list
563#
564do_test select1-10.1 {
565  execsql {
566    SELECT f1 AS x FROM test1 ORDER BY x
567  }
568} {11 33}
569do_test select1-10.2 {
570  execsql {
571    SELECT f1 AS x FROM test1 ORDER BY -x
572  }
573} {33 11}
574do_test select1-10.3 {
575  execsql {
576    SELECT f1-23 AS x FROM test1 ORDER BY abs(x)
577  }
578} {10 -12}
579do_test select1-10.4 {
580  execsql {
581    SELECT f1-23 AS x FROM test1 ORDER BY -abs(x)
582  }
583} {-12 10}
584do_test select1-10.5 {
585  execsql {
586    SELECT f1-22 AS x, f2-22 as y FROM test1
587  }
588} {-11 0 11 22}
589do_test select1-10.6 {
590  execsql {
591    SELECT f1-22 AS x, f2-22 as y FROM test1 WHERE x>0 AND y<50
592  }
593} {11 22}
594
595# Check the ability to specify "TABLE.*" in the result set of a SELECT
596#
597do_test select1-11.1 {
598  execsql {
599    DELETE FROM t3;
600    DELETE FROM t4;
601    INSERT INTO t3 VALUES(1,2);
602    INSERT INTO t4 VALUES(3,4);
603    SELECT * FROM t3, t4;
604  }
605} {1 2 3 4}
606do_test select1-11.2 {
607  execsql2 {
608    SELECT * FROM t3, t4;
609  }
610} {t3.a 1 t3.b 2 t4.a 3 t4.b 4}
611do_test select1-11.3 {
612  execsql2 {
613    SELECT * FROM t3 AS x, t4 AS y;
614  }
615} {x.a 1 x.b 2 y.a 3 y.b 4}
616do_test select1-11.4.1 {
617  execsql {
618    SELECT t3.*, t4.b FROM t3, t4;
619  }
620} {1 2 4}
621do_test select1-11.4.2 {
622  execsql {
623    SELECT "t3".*, t4.b FROM t3, t4;
624  }
625} {1 2 4}
626do_test select1-11.5 {
627  execsql2 {
628    SELECT t3.*, t4.b FROM t3, t4;
629  }
630} {t3.a 1 t3.b 2 t4.b 4}
631do_test select1-11.6 {
632  execsql2 {
633    SELECT x.*, y.b FROM t3 AS x, t4 AS y;
634  }
635} {x.a 1 x.b 2 y.b 4}
636do_test select1-11.7 {
637  execsql {
638    SELECT t3.b, t4.* FROM t3, t4;
639  }
640} {2 3 4}
641do_test select1-11.8 {
642  execsql2 {
643    SELECT t3.b, t4.* FROM t3, t4;
644  }
645} {t3.b 2 t4.a 3 t4.b 4}
646do_test select1-11.9 {
647  execsql2 {
648    SELECT x.b, y.* FROM t3 AS x, t4 AS y;
649  }
650} {x.b 2 y.a 3 y.b 4}
651do_test select1-11.10 {
652  catchsql {
653    SELECT t5.* FROM t3, t4;
654  }
655} {1 {no such table: t5}}
656do_test select1-11.11 {
657  catchsql {
658    SELECT t3.* FROM t3 AS x, t4;
659  }
660} {1 {no such table: t3}}
661do_test select1-11.12 {
662  execsql2 {
663    SELECT t3.* FROM t3, (SELECT max(a), max(b) FROM t4)
664  }
665} {t3.a 1 t3.b 2}
666do_test select1-11.13 {
667  execsql2 {
668    SELECT t3.* FROM (SELECT max(a), max(b) FROM t4), t3
669  }
670} {t3.a 1 t3.b 2}
671do_test select1-11.14 {
672  execsql2 {
673    SELECT * FROM t3, (SELECT max(a), max(b) FROM t4) AS 'tx'
674  }
675} {t3.a 1 t3.b 2 tx.max(a) 3 tx.max(b) 4}
676do_test select1-11.15 {
677  execsql2 {
678    SELECT y.*, t3.* FROM t3, (SELECT max(a), max(b) FROM t4) AS y
679  }
680} {y.max(a) 3 y.max(b) 4 t3.a 1 t3.b 2}
681do_test select1-11.16 {
682  execsql2 {
683    SELECT y.* FROM t3 as y, t4 as z
684  }
685} {y.a 1 y.b 2}
686
687# Tests of SELECT statements without a FROM clause.
688#
689do_test select1-12.1 {
690  execsql2 {
691    SELECT 1+2+3
692  }
693} {1+2+3 6}
694do_test select1-12.2 {
695  execsql2 {
696    SELECT 1,'hello',2
697  }
698} {1 1 'hello' hello 2 2}
699do_test select1-12.3 {
700  execsql2 {
701    SELECT 1 AS 'a','hello' AS 'b',2 AS 'c'
702  }
703} {a 1 b hello c 2}
704do_test select1-12.4 {
705  execsql {
706    DELETE FROM t3;
707    INSERT INTO t3 VALUES(1,2);
708    SELECT * FROM t3 UNION SELECT 3 AS 'a', 4 ORDER BY a;
709  }
710} {1 2 3 4}
711do_test select1-12.5 {
712  execsql {
713    SELECT 3, 4 UNION SELECT * FROM t3;
714  }
715} {1 2 3 4}
716do_test select1-12.6 {
717  execsql {
718    SELECT * FROM t3 WHERE a=(SELECT 1);
719  }
720} {1 2}
721do_test select1-12.7 {
722  execsql {
723    SELECT * FROM t3 WHERE a=(SELECT 2);
724  }
725} {}
726do_test select1-12.8 {
727  execsql2 {
728    SELECT x FROM (
729      SELECT a,b FROM t3 UNION SELECT a AS 'x', b AS 'y' FROM t4 ORDER BY a,b
730    ) ORDER BY x;
731  }
732} {x 1 x 3}
733do_test select1-12.9 {
734  execsql2 {
735    SELECT z.x FROM (
736      SELECT a,b FROM t3 UNION SELECT a AS 'x', b AS 'y' FROM t4 ORDER BY a,b
737    ) AS 'z' ORDER BY x;
738  }
739} {z.x 1 z.x 3}
740
741
742finish_test
743