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.1.1.1 2012/02/21 17:04:16 misho 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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to selectC.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