wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 1 # 2008 September 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 # This file implements regression tests for SQLite library.
12 #
13 # $Id: selectC.test,v 1.5 2009/05/17 15:26:21 drh Exp $
14  
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
17  
18 # Ticket #
19 do_test selectC-1.1 {
20 execsql {
21 CREATE TABLE t1(a, b, c);
22 INSERT INTO t1 VALUES(1,'aaa','bbb');
23 INSERT INTO t1 SELECT * FROM t1;
24 INSERT INTO t1 VALUES(2,'ccc','ddd');
25  
26 SELECT DISTINCT a AS x, b||c AS y
27 FROM t1
28 WHERE y IN ('aaabbb','xxx');
29 }
30 } {1 aaabbb}
31 do_test selectC-1.2 {
32 execsql {
33 SELECT DISTINCT a AS x, b||c AS y
34 FROM t1
35 WHERE b||c IN ('aaabbb','xxx');
36 }
37 } {1 aaabbb}
38 do_test selectC-1.3 {
39 execsql {
40 SELECT DISTINCT a AS x, b||c AS y
41 FROM t1
42 WHERE y='aaabbb'
43 }
44 } {1 aaabbb}
45 do_test selectC-1.4 {
46 execsql {
47 SELECT DISTINCT a AS x, b||c AS y
48 FROM t1
49 WHERE b||c='aaabbb'
50 }
51 } {1 aaabbb}
52 do_test selectC-1.5 {
53 execsql {
54 SELECT DISTINCT a AS x, b||c AS y
55 FROM t1
56 WHERE x=2
57 }
58 } {2 cccddd}
59 do_test selectC-1.6 {
60 execsql {
61 SELECT DISTINCT a AS x, b||c AS y
62 FROM t1
63 WHERE a=2
64 }
65 } {2 cccddd}
66 do_test selectC-1.7 {
67 execsql {
68 SELECT DISTINCT a AS x, b||c AS y
69 FROM t1
70 WHERE +y='aaabbb'
71 }
72 } {1 aaabbb}
73 do_test selectC-1.8 {
74 execsql {
75 SELECT a AS x, b||c AS y
76 FROM t1
77 GROUP BY x, y
78 HAVING y='aaabbb'
79 }
80 } {1 aaabbb}
81 do_test selectC-1.9 {
82 execsql {
83 SELECT a AS x, b||c AS y
84 FROM t1
85 GROUP BY x, y
86 HAVING b||c='aaabbb'
87 }
88 } {1 aaabbb}
89 do_test selectC-1.10 {
90 execsql {
91 SELECT a AS x, b||c AS y
92 FROM t1
93 WHERE y='aaabbb'
94 GROUP BY x, y
95 }
96 } {1 aaabbb}
97 do_test selectC-1.11 {
98 execsql {
99 SELECT a AS x, b||c AS y
100 FROM t1
101 WHERE b||c='aaabbb'
102 GROUP BY x, y
103 }
104 } {1 aaabbb}
105 proc longname_toupper x {return [string toupper $x]}
106 db function uppercaseconversionfunctionwithaverylongname longname_toupper
107 do_test selectC-1.12.1 {
108 execsql {
109 SELECT DISTINCT upper(b) AS x
110 FROM t1
111 ORDER BY x
112 }
113 } {AAA CCC}
114 do_test selectC-1.12.2 {
115 execsql {
116 SELECT DISTINCT uppercaseconversionfunctionwithaverylongname(b) AS x
117 FROM t1
118 ORDER BY x
119 }
120 } {AAA CCC}
121 do_test selectC-1.13.1 {
122 execsql {
123 SELECT upper(b) AS x
124 FROM t1
125 GROUP BY x
126 ORDER BY x
127 }
128 } {AAA CCC}
129 do_test selectC-1.13.2 {
130 execsql {
131 SELECT uppercaseconversionfunctionwithaverylongname(b) AS x
132 FROM t1
133 GROUP BY x
134 ORDER BY x
135 }
136 } {AAA CCC}
137 do_test selectC-1.14.1 {
138 execsql {
139 SELECT upper(b) AS x
140 FROM t1
141 ORDER BY x DESC
142 }
143 } {CCC AAA AAA}
144 do_test selectC-1.14.2 {
145 execsql {
146 SELECT uppercaseconversionfunctionwithaverylongname(b) AS x
147 FROM t1
148 ORDER BY x DESC
149 }
150 } {CCC AAA AAA}
151  
152 # The following query used to leak memory. Verify that has been fixed.
153 #
154 ifcapable trigger {
155 do_test selectC-2.1 {
156 catchsql {
157 CREATE TABLE t21a(a,b);
158 INSERT INTO t21a VALUES(1,2);
159 CREATE TABLE t21b(n);
160 CREATE TRIGGER r21 AFTER INSERT ON t21b BEGIN
161 SELECT a FROM t21a WHERE a>new.x UNION ALL
162 SELECT b FROM t21a WHERE b>new.x ORDER BY 1 LIMIT 2;
163 END;
164 INSERT INTO t21b VALUES(6);
165 }
166 } {1 {no such column: new.x}}
167 }
168  
169 # Check that ticket [883034dcb5] is fixed.
170 #
171 do_test selectC-3.1 {
172 execsql {
173 CREATE TABLE person (
174 org_id TEXT NOT NULL,
175 nickname TEXT NOT NULL,
176 license TEXT,
177 CONSTRAINT person_pk PRIMARY KEY (org_id, nickname),
178 CONSTRAINT person_license_uk UNIQUE (license)
179 );
180 INSERT INTO person VALUES('meyers', 'jack', '2GAT123');
181 INSERT INTO person VALUES('meyers', 'hill', 'V345FMP');
182 INSERT INTO person VALUES('meyers', 'jim', '2GAT138');
183 INSERT INTO person VALUES('smith', 'maggy', '');
184 INSERT INTO person VALUES('smith', 'jose', 'JJZ109');
185 INSERT INTO person VALUES('smith', 'jack', 'THX138');
186 INSERT INTO person VALUES('lakeside', 'dave', '953OKG');
187 INSERT INTO person VALUES('lakeside', 'amy', NULL);
188 INSERT INTO person VALUES('lake-apts', 'tom', NULL);
189 INSERT INTO person VALUES('acorn', 'hideo', 'CQB421');
190  
191 SELECT
192 org_id,
193 count((NOT (org_id IS NULL)) AND (NOT (nickname IS NULL)))
194 FROM person
195 WHERE (CASE WHEN license != '' THEN 1 ELSE 0 END)
196 GROUP BY 1;
197 }
198 } {acorn 1 lakeside 1 meyers 3 smith 2}
199 do_test selectC-3.2 {
200 execsql {
201 CREATE TABLE t2(a PRIMARY KEY, b);
202 INSERT INTO t2 VALUES('abc', 'xxx');
203 INSERT INTO t2 VALUES('def', 'yyy');
204 SELECT a, max(b || a) FROM t2 WHERE (b||b||b)!='value' GROUP BY a;
205 }
206 } {abc xxxabc def yyydef}
207 do_test selectC-3.3 {
208 execsql {
209 SELECT b, max(a || b) FROM t2 WHERE (b||b||b)!='value' GROUP BY a;
210 }
211 } {xxx abcxxx yyy defyyy}
212  
213  
214 proc udf {} { incr ::udf }
215 set ::udf 0
216 db function udf udf
217  
218 do_execsql_test selectC-4.1 {
219 create table t_distinct_bug (a, b, c);
220 insert into t_distinct_bug values ('1', '1', 'a');
221 insert into t_distinct_bug values ('1', '2', 'b');
222 insert into t_distinct_bug values ('1', '3', 'c');
223 insert into t_distinct_bug values ('1', '1', 'd');
224 insert into t_distinct_bug values ('1', '2', 'e');
225 insert into t_distinct_bug values ('1', '3', 'f');
226 } {}
227  
228 do_execsql_test selectC-4.2 {
229 select a from (select distinct a, b from t_distinct_bug)
230 } {1 1 1}
231  
232 do_execsql_test selectC-4.3 {
233 select a, udf() from (select distinct a, b from t_distinct_bug)
234 } {1 1 1 2 1 3}
235  
236 finish_test