wasCSharpSQLite – Blame information for rev 4

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 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 SELECT statements that are part of
13 # expressions.
14 #
15 # $Id: subselect.test,v 1.16 2008/08/04 03:51:24 danielk1977 Exp $
16  
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
19  
20 # Omit this whole file if the library is build without subquery support.
21 ifcapable !subquery {
22 finish_test
23 return
24 }
25  
26 # Basic sanity checking. Try a simple subselect.
27 #
28 do_test subselect-1.1 {
29 execsql {
30 CREATE TABLE t1(a int, b int);
31 INSERT INTO t1 VALUES(1,2);
32 INSERT INTO t1 VALUES(3,4);
33 INSERT INTO t1 VALUES(5,6);
34 }
35 execsql {SELECT * FROM t1 WHERE a = (SELECT count(*) FROM t1)}
36 } {3 4}
37  
38 # Try a select with more than one result column.
39 #
40 do_test subselect-1.2 {
41 set v [catch {execsql {SELECT * FROM t1 WHERE a = (SELECT * FROM t1)}} msg]
42 lappend v $msg
43 } {1 {only a single result allowed for a SELECT that is part of an expression}}
44  
45 # A subselect without an aggregate.
46 #
47 do_test subselect-1.3a {
48 execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=2)}
49 } {2}
50 do_test subselect-1.3b {
51 execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=4)}
52 } {4}
53 do_test subselect-1.3c {
54 execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=6)}
55 } {6}
56 do_test subselect-1.3d {
57 execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=8)}
58 } {}
59 ifcapable compound {
60 do_test subselect-1.3e {
61 execsql {
62 SELECT b FROM t1
63 WHERE a = (SELECT a FROM t1 UNION SELECT b FROM t1 ORDER BY 1);
64 }
65 } {2}
66 }
67  
68 # What if the subselect doesn't return any value. We should get
69 # NULL as the result. Check it out.
70 #
71 do_test subselect-1.4 {
72 execsql {SELECT b from t1 where a = coalesce((SELECT a FROM t1 WHERE b=5),1)}
73 } {2}
74  
75 # Try multiple subselects within a single expression.
76 #
77 do_test subselect-1.5 {
78 execsql {
79 CREATE TABLE t2(x int, y int);
80 INSERT INTO t2 VALUES(1,2);
81 INSERT INTO t2 VALUES(2,4);
82 INSERT INTO t2 VALUES(3,8);
83 INSERT INTO t2 VALUES(4,16);
84 }
85 execsql {
86 SELECT y from t2
87 WHERE x = (SELECT sum(b) FROM t1 where a notnull) - (SELECT sum(a) FROM t1)
88 }
89 } {8}
90  
91 # Try something useful. Delete every entry from t2 where the
92 # x value is less than half of the maximum.
93 #
94 do_test subselect-1.6 {
95 execsql {DELETE FROM t2 WHERE x < 0.5*(SELECT max(x) FROM t2)}
96 execsql {SELECT x FROM t2 ORDER BY x}
97 } {2 3 4}
98  
99 # Make sure sorting works for SELECTs there used as a scalar expression.
100 #
101 do_test subselect-2.1 {
102 execsql {
103 SELECT (SELECT a FROM t1 ORDER BY a), (SELECT a FROM t1 ORDER BY a DESC)
104 }
105 } {1 5}
106 do_test subselect-2.2 {
107 execsql {
108 SELECT 1 IN (SELECT a FROM t1 ORDER BY a);
109 }
110 } {1}
111 do_test subselect-2.3 {
112 execsql {
113 SELECT 2 IN (SELECT a FROM t1 ORDER BY a DESC);
114 }
115 } {0}
116  
117 # Verify that the ORDER BY clause is honored in a subquery.
118 #
119 ifcapable compound {
120 do_test subselect-3.1 {
121 execsql {
122 CREATE TABLE t3(x int);
123 INSERT INTO t3 SELECT a FROM t1 UNION ALL SELECT b FROM t1;
124 SELECT * FROM t3 ORDER BY x;
125 }
126 } {1 2 3 4 5 6}
127 } ;# ifcapable compound
128 ifcapable !compound {
129 do_test subselect-3.1 {
130 execsql {
131 CREATE TABLE t3(x int);
132 INSERT INTO t3 SELECT a FROM t1;
133 INSERT INTO t3 SELECT b FROM t1;
134 SELECT * FROM t3 ORDER BY x;
135 }
136 } {1 2 3 4 5 6}
137 } ;# ifcapable !compound
138  
139 do_test subselect-3.2 {
140 execsql {
141 SELECT sum(x) FROM (SELECT x FROM t3 ORDER BY x LIMIT 2);
142 }
143 } {3}
144 do_test subselect-3.3 {
145 execsql {
146 SELECT sum(x) FROM (SELECT x FROM t3 ORDER BY x DESC LIMIT 2);
147 }
148 } {11}
149 do_test subselect-3.4 {
150 execsql {
151 SELECT (SELECT x FROM t3 ORDER BY x);
152 }
153 } {1}
154 do_test subselect-3.5 {
155 execsql {
156 SELECT (SELECT x FROM t3 ORDER BY x DESC);
157 }
158 } {6}
159 do_test subselect-3.6 {
160 execsql {
161 SELECT (SELECT x FROM t3 ORDER BY x LIMIT 1);
162 }
163 } {1}
164 do_test subselect-3.7 {
165 execsql {
166 SELECT (SELECT x FROM t3 ORDER BY x DESC LIMIT 1);
167 }
168 } {6}
169 do_test subselect-3.8 {
170 execsql {
171 SELECT (SELECT x FROM t3 ORDER BY x LIMIT 1 OFFSET 2);
172 }
173 } {3}
174 do_test subselect-3.9 {
175 execsql {
176 SELECT (SELECT x FROM t3 ORDER BY x DESC LIMIT 1 OFFSET 2);
177 }
178 } {4}
179 do_test subselect-3.10 {
180 execsql {
181 SELECT x FROM t3 WHERE x IN
182 (SELECT x FROM t3 ORDER BY x DESC LIMIT 1 OFFSET 2);
183 }
184 } {4}
185  
186 # Ticket #2295.
187 # Make sure type affinities work correctly on subqueries with
188 # an ORDER BY clause.
189 #
190 do_test subselect-4.1 {
191 execsql {
192 CREATE TABLE t4(a TEXT, b TEXT);
193 INSERT INTO t4 VALUES('a','1');
194 INSERT INTO t4 VALUES('b','2');
195 INSERT INTO t4 VALUES('c','3');
196 SELECT a FROM t4 WHERE b IN (SELECT b FROM t4 ORDER BY b);
197 }
198 } {a b c}
199 do_test subselect-4.2 {
200 execsql {
201 SELECT a FROM t4 WHERE b IN (SELECT b FROM t4 ORDER BY b LIMIT 1);
202 }
203 } {a}
204 do_test subselect-4.3 {
205 execsql {
206 SELECT a FROM t4 WHERE b IN (SELECT b FROM t4 ORDER BY b DESC LIMIT 1);
207 }
208 } {c}
209  
210 finish_test