|
sl@0
|
1 |
# 2001 September 15
|
|
sl@0
|
2 |
#
|
|
sl@0
|
3 |
# The author disclaims copyright to this source code. In place of
|
|
sl@0
|
4 |
# a legal notice, here is a blessing:
|
|
sl@0
|
5 |
#
|
|
sl@0
|
6 |
# May you do good and not evil.
|
|
sl@0
|
7 |
# May you find forgiveness for yourself and forgive others.
|
|
sl@0
|
8 |
# May you share freely, never taking more than you give.
|
|
sl@0
|
9 |
#
|
|
sl@0
|
10 |
#***********************************************************************
|
|
sl@0
|
11 |
# This file implements regression tests for SQLite library. The
|
|
sl@0
|
12 |
# focus of this file is testing built-in functions.
|
|
sl@0
|
13 |
#
|
|
sl@0
|
14 |
# $Id: func.test,v 1.86 2008/08/04 03:51:24 danielk1977 Exp $
|
|
sl@0
|
15 |
|
|
sl@0
|
16 |
set testdir [file dirname $argv0]
|
|
sl@0
|
17 |
source $testdir/tester.tcl
|
|
sl@0
|
18 |
|
|
sl@0
|
19 |
# Create a table to work with.
|
|
sl@0
|
20 |
#
|
|
sl@0
|
21 |
do_test func-0.0 {
|
|
sl@0
|
22 |
execsql {CREATE TABLE tbl1(t1 text)}
|
|
sl@0
|
23 |
foreach word {this program is free software} {
|
|
sl@0
|
24 |
execsql "INSERT INTO tbl1 VALUES('$word')"
|
|
sl@0
|
25 |
}
|
|
sl@0
|
26 |
execsql {SELECT t1 FROM tbl1 ORDER BY t1}
|
|
sl@0
|
27 |
} {free is program software this}
|
|
sl@0
|
28 |
do_test func-0.1 {
|
|
sl@0
|
29 |
execsql {
|
|
sl@0
|
30 |
CREATE TABLE t2(a);
|
|
sl@0
|
31 |
INSERT INTO t2 VALUES(1);
|
|
sl@0
|
32 |
INSERT INTO t2 VALUES(NULL);
|
|
sl@0
|
33 |
INSERT INTO t2 VALUES(345);
|
|
sl@0
|
34 |
INSERT INTO t2 VALUES(NULL);
|
|
sl@0
|
35 |
INSERT INTO t2 VALUES(67890);
|
|
sl@0
|
36 |
SELECT * FROM t2;
|
|
sl@0
|
37 |
}
|
|
sl@0
|
38 |
} {1 {} 345 {} 67890}
|
|
sl@0
|
39 |
|
|
sl@0
|
40 |
# Check out the length() function
|
|
sl@0
|
41 |
#
|
|
sl@0
|
42 |
do_test func-1.0 {
|
|
sl@0
|
43 |
execsql {SELECT length(t1) FROM tbl1 ORDER BY t1}
|
|
sl@0
|
44 |
} {4 2 7 8 4}
|
|
sl@0
|
45 |
do_test func-1.1 {
|
|
sl@0
|
46 |
set r [catch {execsql {SELECT length(*) FROM tbl1 ORDER BY t1}} msg]
|
|
sl@0
|
47 |
lappend r $msg
|
|
sl@0
|
48 |
} {1 {wrong number of arguments to function length()}}
|
|
sl@0
|
49 |
do_test func-1.2 {
|
|
sl@0
|
50 |
set r [catch {execsql {SELECT length(t1,5) FROM tbl1 ORDER BY t1}} msg]
|
|
sl@0
|
51 |
lappend r $msg
|
|
sl@0
|
52 |
} {1 {wrong number of arguments to function length()}}
|
|
sl@0
|
53 |
do_test func-1.3 {
|
|
sl@0
|
54 |
execsql {SELECT length(t1), count(*) FROM tbl1 GROUP BY length(t1)
|
|
sl@0
|
55 |
ORDER BY length(t1)}
|
|
sl@0
|
56 |
} {2 1 4 2 7 1 8 1}
|
|
sl@0
|
57 |
do_test func-1.4 {
|
|
sl@0
|
58 |
execsql {SELECT coalesce(length(a),-1) FROM t2}
|
|
sl@0
|
59 |
} {1 -1 3 -1 5}
|
|
sl@0
|
60 |
|
|
sl@0
|
61 |
# Check out the substr() function
|
|
sl@0
|
62 |
#
|
|
sl@0
|
63 |
do_test func-2.0 {
|
|
sl@0
|
64 |
execsql {SELECT substr(t1,1,2) FROM tbl1 ORDER BY t1}
|
|
sl@0
|
65 |
} {fr is pr so th}
|
|
sl@0
|
66 |
do_test func-2.1 {
|
|
sl@0
|
67 |
execsql {SELECT substr(t1,2,1) FROM tbl1 ORDER BY t1}
|
|
sl@0
|
68 |
} {r s r o h}
|
|
sl@0
|
69 |
do_test func-2.2 {
|
|
sl@0
|
70 |
execsql {SELECT substr(t1,3,3) FROM tbl1 ORDER BY t1}
|
|
sl@0
|
71 |
} {ee {} ogr ftw is}
|
|
sl@0
|
72 |
do_test func-2.3 {
|
|
sl@0
|
73 |
execsql {SELECT substr(t1,-1,1) FROM tbl1 ORDER BY t1}
|
|
sl@0
|
74 |
} {e s m e s}
|
|
sl@0
|
75 |
do_test func-2.4 {
|
|
sl@0
|
76 |
execsql {SELECT substr(t1,-1,2) FROM tbl1 ORDER BY t1}
|
|
sl@0
|
77 |
} {e s m e s}
|
|
sl@0
|
78 |
do_test func-2.5 {
|
|
sl@0
|
79 |
execsql {SELECT substr(t1,-2,1) FROM tbl1 ORDER BY t1}
|
|
sl@0
|
80 |
} {e i a r i}
|
|
sl@0
|
81 |
do_test func-2.6 {
|
|
sl@0
|
82 |
execsql {SELECT substr(t1,-2,2) FROM tbl1 ORDER BY t1}
|
|
sl@0
|
83 |
} {ee is am re is}
|
|
sl@0
|
84 |
do_test func-2.7 {
|
|
sl@0
|
85 |
execsql {SELECT substr(t1,-4,2) FROM tbl1 ORDER BY t1}
|
|
sl@0
|
86 |
} {fr {} gr wa th}
|
|
sl@0
|
87 |
do_test func-2.8 {
|
|
sl@0
|
88 |
execsql {SELECT t1 FROM tbl1 ORDER BY substr(t1,2,20)}
|
|
sl@0
|
89 |
} {this software free program is}
|
|
sl@0
|
90 |
do_test func-2.9 {
|
|
sl@0
|
91 |
execsql {SELECT substr(a,1,1) FROM t2}
|
|
sl@0
|
92 |
} {1 {} 3 {} 6}
|
|
sl@0
|
93 |
do_test func-2.10 {
|
|
sl@0
|
94 |
execsql {SELECT substr(a,2,2) FROM t2}
|
|
sl@0
|
95 |
} {{} {} 45 {} 78}
|
|
sl@0
|
96 |
|
|
sl@0
|
97 |
# Only do the following tests if TCL has UTF-8 capabilities
|
|
sl@0
|
98 |
#
|
|
sl@0
|
99 |
if {"\u1234"!="u1234"} {
|
|
sl@0
|
100 |
|
|
sl@0
|
101 |
# Put some UTF-8 characters in the database
|
|
sl@0
|
102 |
#
|
|
sl@0
|
103 |
do_test func-3.0 {
|
|
sl@0
|
104 |
execsql {DELETE FROM tbl1}
|
|
sl@0
|
105 |
foreach word "contains UTF-8 characters hi\u1234ho" {
|
|
sl@0
|
106 |
execsql "INSERT INTO tbl1 VALUES('$word')"
|
|
sl@0
|
107 |
}
|
|
sl@0
|
108 |
execsql {SELECT t1 FROM tbl1 ORDER BY t1}
|
|
sl@0
|
109 |
} "UTF-8 characters contains hi\u1234ho"
|
|
sl@0
|
110 |
do_test func-3.1 {
|
|
sl@0
|
111 |
execsql {SELECT length(t1) FROM tbl1 ORDER BY t1}
|
|
sl@0
|
112 |
} {5 10 8 5}
|
|
sl@0
|
113 |
do_test func-3.2 {
|
|
sl@0
|
114 |
execsql {SELECT substr(t1,1,2) FROM tbl1 ORDER BY t1}
|
|
sl@0
|
115 |
} {UT ch co hi}
|
|
sl@0
|
116 |
do_test func-3.3 {
|
|
sl@0
|
117 |
execsql {SELECT substr(t1,1,3) FROM tbl1 ORDER BY t1}
|
|
sl@0
|
118 |
} "UTF cha con hi\u1234"
|
|
sl@0
|
119 |
do_test func-3.4 {
|
|
sl@0
|
120 |
execsql {SELECT substr(t1,2,2) FROM tbl1 ORDER BY t1}
|
|
sl@0
|
121 |
} "TF ha on i\u1234"
|
|
sl@0
|
122 |
do_test func-3.5 {
|
|
sl@0
|
123 |
execsql {SELECT substr(t1,2,3) FROM tbl1 ORDER BY t1}
|
|
sl@0
|
124 |
} "TF- har ont i\u1234h"
|
|
sl@0
|
125 |
do_test func-3.6 {
|
|
sl@0
|
126 |
execsql {SELECT substr(t1,3,2) FROM tbl1 ORDER BY t1}
|
|
sl@0
|
127 |
} "F- ar nt \u1234h"
|
|
sl@0
|
128 |
do_test func-3.7 {
|
|
sl@0
|
129 |
execsql {SELECT substr(t1,4,2) FROM tbl1 ORDER BY t1}
|
|
sl@0
|
130 |
} "-8 ra ta ho"
|
|
sl@0
|
131 |
do_test func-3.8 {
|
|
sl@0
|
132 |
execsql {SELECT substr(t1,-1,1) FROM tbl1 ORDER BY t1}
|
|
sl@0
|
133 |
} "8 s s o"
|
|
sl@0
|
134 |
do_test func-3.9 {
|
|
sl@0
|
135 |
execsql {SELECT substr(t1,-3,2) FROM tbl1 ORDER BY t1}
|
|
sl@0
|
136 |
} "F- er in \u1234h"
|
|
sl@0
|
137 |
do_test func-3.10 {
|
|
sl@0
|
138 |
execsql {SELECT substr(t1,-4,3) FROM tbl1 ORDER BY t1}
|
|
sl@0
|
139 |
} "TF- ter ain i\u1234h"
|
|
sl@0
|
140 |
do_test func-3.99 {
|
|
sl@0
|
141 |
execsql {DELETE FROM tbl1}
|
|
sl@0
|
142 |
foreach word {this program is free software} {
|
|
sl@0
|
143 |
execsql "INSERT INTO tbl1 VALUES('$word')"
|
|
sl@0
|
144 |
}
|
|
sl@0
|
145 |
execsql {SELECT t1 FROM tbl1}
|
|
sl@0
|
146 |
} {this program is free software}
|
|
sl@0
|
147 |
|
|
sl@0
|
148 |
} ;# End \u1234!=u1234
|
|
sl@0
|
149 |
|
|
sl@0
|
150 |
# Test the abs() and round() functions.
|
|
sl@0
|
151 |
#
|
|
sl@0
|
152 |
do_test func-4.1 {
|
|
sl@0
|
153 |
execsql {
|
|
sl@0
|
154 |
CREATE TABLE t1(a,b,c);
|
|
sl@0
|
155 |
INSERT INTO t1 VALUES(1,2,3);
|
|
sl@0
|
156 |
INSERT INTO t1 VALUES(2,1.2345678901234,-12345.67890);
|
|
sl@0
|
157 |
INSERT INTO t1 VALUES(3,-2,-5);
|
|
sl@0
|
158 |
}
|
|
sl@0
|
159 |
catchsql {SELECT abs(a,b) FROM t1}
|
|
sl@0
|
160 |
} {1 {wrong number of arguments to function abs()}}
|
|
sl@0
|
161 |
do_test func-4.2 {
|
|
sl@0
|
162 |
catchsql {SELECT abs() FROM t1}
|
|
sl@0
|
163 |
} {1 {wrong number of arguments to function abs()}}
|
|
sl@0
|
164 |
do_test func-4.3 {
|
|
sl@0
|
165 |
catchsql {SELECT abs(b) FROM t1 ORDER BY a}
|
|
sl@0
|
166 |
} {0 {2 1.2345678901234 2}}
|
|
sl@0
|
167 |
do_test func-4.4 {
|
|
sl@0
|
168 |
catchsql {SELECT abs(c) FROM t1 ORDER BY a}
|
|
sl@0
|
169 |
} {0 {3 12345.6789 5}}
|
|
sl@0
|
170 |
do_test func-4.4.1 {
|
|
sl@0
|
171 |
execsql {SELECT abs(a) FROM t2}
|
|
sl@0
|
172 |
} {1 {} 345 {} 67890}
|
|
sl@0
|
173 |
do_test func-4.4.2 {
|
|
sl@0
|
174 |
execsql {SELECT abs(t1) FROM tbl1}
|
|
sl@0
|
175 |
} {0.0 0.0 0.0 0.0 0.0}
|
|
sl@0
|
176 |
|
|
sl@0
|
177 |
do_test func-4.5 {
|
|
sl@0
|
178 |
catchsql {SELECT round(a,b,c) FROM t1}
|
|
sl@0
|
179 |
} {1 {wrong number of arguments to function round()}}
|
|
sl@0
|
180 |
do_test func-4.6 {
|
|
sl@0
|
181 |
catchsql {SELECT round(b,2) FROM t1 ORDER BY b}
|
|
sl@0
|
182 |
} {0 {-2.0 1.23 2.0}}
|
|
sl@0
|
183 |
do_test func-4.7 {
|
|
sl@0
|
184 |
catchsql {SELECT round(b,0) FROM t1 ORDER BY a}
|
|
sl@0
|
185 |
} {0 {2.0 1.0 -2.0}}
|
|
sl@0
|
186 |
do_test func-4.8 {
|
|
sl@0
|
187 |
catchsql {SELECT round(c) FROM t1 ORDER BY a}
|
|
sl@0
|
188 |
} {0 {3.0 -12346.0 -5.0}}
|
|
sl@0
|
189 |
do_test func-4.9 {
|
|
sl@0
|
190 |
catchsql {SELECT round(c,a) FROM t1 ORDER BY a}
|
|
sl@0
|
191 |
} {0 {3.0 -12345.68 -5.0}}
|
|
sl@0
|
192 |
do_test func-4.10 {
|
|
sl@0
|
193 |
catchsql {SELECT 'x' || round(c,a) || 'y' FROM t1 ORDER BY a}
|
|
sl@0
|
194 |
} {0 {x3.0y x-12345.68y x-5.0y}}
|
|
sl@0
|
195 |
do_test func-4.11 {
|
|
sl@0
|
196 |
catchsql {SELECT round() FROM t1 ORDER BY a}
|
|
sl@0
|
197 |
} {1 {wrong number of arguments to function round()}}
|
|
sl@0
|
198 |
do_test func-4.12 {
|
|
sl@0
|
199 |
execsql {SELECT coalesce(round(a,2),'nil') FROM t2}
|
|
sl@0
|
200 |
} {1.0 nil 345.0 nil 67890.0}
|
|
sl@0
|
201 |
do_test func-4.13 {
|
|
sl@0
|
202 |
execsql {SELECT round(t1,2) FROM tbl1}
|
|
sl@0
|
203 |
} {0.0 0.0 0.0 0.0 0.0}
|
|
sl@0
|
204 |
do_test func-4.14 {
|
|
sl@0
|
205 |
execsql {SELECT typeof(round(5.1,1));}
|
|
sl@0
|
206 |
} {real}
|
|
sl@0
|
207 |
do_test func-4.15 {
|
|
sl@0
|
208 |
execsql {SELECT typeof(round(5.1));}
|
|
sl@0
|
209 |
} {real}
|
|
sl@0
|
210 |
|
|
sl@0
|
211 |
|
|
sl@0
|
212 |
# Test the upper() and lower() functions
|
|
sl@0
|
213 |
#
|
|
sl@0
|
214 |
do_test func-5.1 {
|
|
sl@0
|
215 |
execsql {SELECT upper(t1) FROM tbl1}
|
|
sl@0
|
216 |
} {THIS PROGRAM IS FREE SOFTWARE}
|
|
sl@0
|
217 |
do_test func-5.2 {
|
|
sl@0
|
218 |
execsql {SELECT lower(upper(t1)) FROM tbl1}
|
|
sl@0
|
219 |
} {this program is free software}
|
|
sl@0
|
220 |
do_test func-5.3 {
|
|
sl@0
|
221 |
execsql {SELECT upper(a), lower(a) FROM t2}
|
|
sl@0
|
222 |
} {1 1 {} {} 345 345 {} {} 67890 67890}
|
|
sl@0
|
223 |
ifcapable !icu {
|
|
sl@0
|
224 |
do_test func-5.4 {
|
|
sl@0
|
225 |
catchsql {SELECT upper(a,5) FROM t2}
|
|
sl@0
|
226 |
} {1 {wrong number of arguments to function upper()}}
|
|
sl@0
|
227 |
}
|
|
sl@0
|
228 |
do_test func-5.5 {
|
|
sl@0
|
229 |
catchsql {SELECT upper(*) FROM t2}
|
|
sl@0
|
230 |
} {1 {wrong number of arguments to function upper()}}
|
|
sl@0
|
231 |
|
|
sl@0
|
232 |
# Test the coalesce() and nullif() functions
|
|
sl@0
|
233 |
#
|
|
sl@0
|
234 |
do_test func-6.1 {
|
|
sl@0
|
235 |
execsql {SELECT coalesce(a,'xyz') FROM t2}
|
|
sl@0
|
236 |
} {1 xyz 345 xyz 67890}
|
|
sl@0
|
237 |
do_test func-6.2 {
|
|
sl@0
|
238 |
execsql {SELECT coalesce(upper(a),'nil') FROM t2}
|
|
sl@0
|
239 |
} {1 nil 345 nil 67890}
|
|
sl@0
|
240 |
do_test func-6.3 {
|
|
sl@0
|
241 |
execsql {SELECT coalesce(nullif(1,1),'nil')}
|
|
sl@0
|
242 |
} {nil}
|
|
sl@0
|
243 |
do_test func-6.4 {
|
|
sl@0
|
244 |
execsql {SELECT coalesce(nullif(1,2),'nil')}
|
|
sl@0
|
245 |
} {1}
|
|
sl@0
|
246 |
do_test func-6.5 {
|
|
sl@0
|
247 |
execsql {SELECT coalesce(nullif(1,NULL),'nil')}
|
|
sl@0
|
248 |
} {1}
|
|
sl@0
|
249 |
|
|
sl@0
|
250 |
|
|
sl@0
|
251 |
# Test the last_insert_rowid() function
|
|
sl@0
|
252 |
#
|
|
sl@0
|
253 |
do_test func-7.1 {
|
|
sl@0
|
254 |
execsql {SELECT last_insert_rowid()}
|
|
sl@0
|
255 |
} [db last_insert_rowid]
|
|
sl@0
|
256 |
|
|
sl@0
|
257 |
# Tests for aggregate functions and how they handle NULLs.
|
|
sl@0
|
258 |
#
|
|
sl@0
|
259 |
do_test func-8.1 {
|
|
sl@0
|
260 |
ifcapable explain {
|
|
sl@0
|
261 |
execsql {EXPLAIN SELECT sum(a) FROM t2;}
|
|
sl@0
|
262 |
}
|
|
sl@0
|
263 |
execsql {
|
|
sl@0
|
264 |
SELECT sum(a), count(a), round(avg(a),2), min(a), max(a), count(*) FROM t2;
|
|
sl@0
|
265 |
}
|
|
sl@0
|
266 |
} {68236 3 22745.33 1 67890 5}
|
|
sl@0
|
267 |
do_test func-8.2 {
|
|
sl@0
|
268 |
execsql {
|
|
sl@0
|
269 |
SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t2;
|
|
sl@0
|
270 |
}
|
|
sl@0
|
271 |
} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
|
|
sl@0
|
272 |
|
|
sl@0
|
273 |
ifcapable tempdb {
|
|
sl@0
|
274 |
do_test func-8.3 {
|
|
sl@0
|
275 |
execsql {
|
|
sl@0
|
276 |
CREATE TEMP TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;
|
|
sl@0
|
277 |
SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
|
|
sl@0
|
278 |
}
|
|
sl@0
|
279 |
} {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
|
|
sl@0
|
280 |
} else {
|
|
sl@0
|
281 |
do_test func-8.3 {
|
|
sl@0
|
282 |
execsql {
|
|
sl@0
|
283 |
CREATE TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;
|
|
sl@0
|
284 |
SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
|
|
sl@0
|
285 |
}
|
|
sl@0
|
286 |
} {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
|
|
sl@0
|
287 |
}
|
|
sl@0
|
288 |
do_test func-8.4 {
|
|
sl@0
|
289 |
execsql {
|
|
sl@0
|
290 |
SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
|
|
sl@0
|
291 |
}
|
|
sl@0
|
292 |
} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
|
|
sl@0
|
293 |
ifcapable compound {
|
|
sl@0
|
294 |
do_test func-8.5 {
|
|
sl@0
|
295 |
execsql {
|
|
sl@0
|
296 |
SELECT sum(x) FROM (SELECT '9223372036' || '854775807' AS x
|
|
sl@0
|
297 |
UNION ALL SELECT -9223372036854775807)
|
|
sl@0
|
298 |
}
|
|
sl@0
|
299 |
} {0}
|
|
sl@0
|
300 |
do_test func-8.6 {
|
|
sl@0
|
301 |
execsql {
|
|
sl@0
|
302 |
SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775807' AS x
|
|
sl@0
|
303 |
UNION ALL SELECT -9223372036854775807)
|
|
sl@0
|
304 |
}
|
|
sl@0
|
305 |
} {integer}
|
|
sl@0
|
306 |
do_test func-8.7 {
|
|
sl@0
|
307 |
execsql {
|
|
sl@0
|
308 |
SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775808' AS x
|
|
sl@0
|
309 |
UNION ALL SELECT -9223372036854775807)
|
|
sl@0
|
310 |
}
|
|
sl@0
|
311 |
} {real}
|
|
sl@0
|
312 |
do_test func-8.8 {
|
|
sl@0
|
313 |
execsql {
|
|
sl@0
|
314 |
SELECT sum(x)>0.0 FROM (SELECT '9223372036' || '854775808' AS x
|
|
sl@0
|
315 |
UNION ALL SELECT -9223372036850000000)
|
|
sl@0
|
316 |
}
|
|
sl@0
|
317 |
} {1}
|
|
sl@0
|
318 |
}
|
|
sl@0
|
319 |
|
|
sl@0
|
320 |
# How do you test the random() function in a meaningful, deterministic way?
|
|
sl@0
|
321 |
#
|
|
sl@0
|
322 |
do_test func-9.1 {
|
|
sl@0
|
323 |
execsql {
|
|
sl@0
|
324 |
SELECT random() is not null;
|
|
sl@0
|
325 |
}
|
|
sl@0
|
326 |
} {1}
|
|
sl@0
|
327 |
do_test func-9.2 {
|
|
sl@0
|
328 |
execsql {
|
|
sl@0
|
329 |
SELECT typeof(random());
|
|
sl@0
|
330 |
}
|
|
sl@0
|
331 |
} {integer}
|
|
sl@0
|
332 |
do_test func-9.3 {
|
|
sl@0
|
333 |
execsql {
|
|
sl@0
|
334 |
SELECT randomblob(32) is not null;
|
|
sl@0
|
335 |
}
|
|
sl@0
|
336 |
} {1}
|
|
sl@0
|
337 |
do_test func-9.4 {
|
|
sl@0
|
338 |
execsql {
|
|
sl@0
|
339 |
SELECT typeof(randomblob(32));
|
|
sl@0
|
340 |
}
|
|
sl@0
|
341 |
} {blob}
|
|
sl@0
|
342 |
do_test func-9.5 {
|
|
sl@0
|
343 |
execsql {
|
|
sl@0
|
344 |
SELECT length(randomblob(32)), length(randomblob(-5)),
|
|
sl@0
|
345 |
length(randomblob(2000))
|
|
sl@0
|
346 |
}
|
|
sl@0
|
347 |
} {32 1 2000}
|
|
sl@0
|
348 |
|
|
sl@0
|
349 |
# The "hex()" function was added in order to be able to render blobs
|
|
sl@0
|
350 |
# generated by randomblob(). So this seems like a good place to test
|
|
sl@0
|
351 |
# hex().
|
|
sl@0
|
352 |
#
|
|
sl@0
|
353 |
ifcapable bloblit {
|
|
sl@0
|
354 |
do_test func-9.10 {
|
|
sl@0
|
355 |
execsql {SELECT hex(x'00112233445566778899aAbBcCdDeEfF')}
|
|
sl@0
|
356 |
} {00112233445566778899AABBCCDDEEFF}
|
|
sl@0
|
357 |
}
|
|
sl@0
|
358 |
set encoding [db one {PRAGMA encoding}]
|
|
sl@0
|
359 |
if {$encoding=="UTF-16le"} {
|
|
sl@0
|
360 |
do_test func-9.11-utf16le {
|
|
sl@0
|
361 |
execsql {SELECT hex(replace('abcdefg','ef','12'))}
|
|
sl@0
|
362 |
} {6100620063006400310032006700}
|
|
sl@0
|
363 |
do_test func-9.12-utf16le {
|
|
sl@0
|
364 |
execsql {SELECT hex(replace('abcdefg','','12'))}
|
|
sl@0
|
365 |
} {{}}
|
|
sl@0
|
366 |
do_test func-9.13-utf16le {
|
|
sl@0
|
367 |
execsql {SELECT hex(replace('aabcdefg','a','aaa'))}
|
|
sl@0
|
368 |
} {610061006100610061006100620063006400650066006700}
|
|
sl@0
|
369 |
} elseif {$encoding=="UTF-8"} {
|
|
sl@0
|
370 |
do_test func-9.11-utf8 {
|
|
sl@0
|
371 |
execsql {SELECT hex(replace('abcdefg','ef','12'))}
|
|
sl@0
|
372 |
} {61626364313267}
|
|
sl@0
|
373 |
do_test func-9.12-utf8 {
|
|
sl@0
|
374 |
execsql {SELECT hex(replace('abcdefg','','12'))}
|
|
sl@0
|
375 |
} {{}}
|
|
sl@0
|
376 |
do_test func-9.13-utf8 {
|
|
sl@0
|
377 |
execsql {SELECT hex(replace('aabcdefg','a','aaa'))}
|
|
sl@0
|
378 |
} {616161616161626364656667}
|
|
sl@0
|
379 |
}
|
|
sl@0
|
380 |
|
|
sl@0
|
381 |
# Use the "sqlite_register_test_function" TCL command which is part of
|
|
sl@0
|
382 |
# the text fixture in order to verify correct operation of some of
|
|
sl@0
|
383 |
# the user-defined SQL function APIs that are not used by the built-in
|
|
sl@0
|
384 |
# functions.
|
|
sl@0
|
385 |
#
|
|
sl@0
|
386 |
set ::DB [sqlite3_connection_pointer db]
|
|
sl@0
|
387 |
sqlite_register_test_function $::DB testfunc
|
|
sl@0
|
388 |
do_test func-10.1 {
|
|
sl@0
|
389 |
catchsql {
|
|
sl@0
|
390 |
SELECT testfunc(NULL,NULL);
|
|
sl@0
|
391 |
}
|
|
sl@0
|
392 |
} {1 {first argument should be one of: int int64 string double null value}}
|
|
sl@0
|
393 |
do_test func-10.2 {
|
|
sl@0
|
394 |
execsql {
|
|
sl@0
|
395 |
SELECT testfunc(
|
|
sl@0
|
396 |
'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
|
|
sl@0
|
397 |
'int', 1234
|
|
sl@0
|
398 |
);
|
|
sl@0
|
399 |
}
|
|
sl@0
|
400 |
} {1234}
|
|
sl@0
|
401 |
do_test func-10.3 {
|
|
sl@0
|
402 |
execsql {
|
|
sl@0
|
403 |
SELECT testfunc(
|
|
sl@0
|
404 |
'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
|
|
sl@0
|
405 |
'string', NULL
|
|
sl@0
|
406 |
);
|
|
sl@0
|
407 |
}
|
|
sl@0
|
408 |
} {{}}
|
|
sl@0
|
409 |
do_test func-10.4 {
|
|
sl@0
|
410 |
execsql {
|
|
sl@0
|
411 |
SELECT testfunc(
|
|
sl@0
|
412 |
'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
|
|
sl@0
|
413 |
'double', 1.234
|
|
sl@0
|
414 |
);
|
|
sl@0
|
415 |
}
|
|
sl@0
|
416 |
} {1.234}
|
|
sl@0
|
417 |
do_test func-10.5 {
|
|
sl@0
|
418 |
execsql {
|
|
sl@0
|
419 |
SELECT testfunc(
|
|
sl@0
|
420 |
'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
|
|
sl@0
|
421 |
'int', 1234,
|
|
sl@0
|
422 |
'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
|
|
sl@0
|
423 |
'string', NULL,
|
|
sl@0
|
424 |
'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
|
|
sl@0
|
425 |
'double', 1.234,
|
|
sl@0
|
426 |
'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
|
|
sl@0
|
427 |
'int', 1234,
|
|
sl@0
|
428 |
'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
|
|
sl@0
|
429 |
'string', NULL,
|
|
sl@0
|
430 |
'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
|
|
sl@0
|
431 |
'double', 1.234
|
|
sl@0
|
432 |
);
|
|
sl@0
|
433 |
}
|
|
sl@0
|
434 |
} {1.234}
|
|
sl@0
|
435 |
|
|
sl@0
|
436 |
# Test the built-in sqlite_version(*) SQL function.
|
|
sl@0
|
437 |
#
|
|
sl@0
|
438 |
do_test func-11.1 {
|
|
sl@0
|
439 |
execsql {
|
|
sl@0
|
440 |
SELECT sqlite_version(*);
|
|
sl@0
|
441 |
}
|
|
sl@0
|
442 |
} [sqlite3 -version]
|
|
sl@0
|
443 |
|
|
sl@0
|
444 |
# Test that destructors passed to sqlite3 by calls to sqlite3_result_text()
|
|
sl@0
|
445 |
# etc. are called. These tests use two special user-defined functions
|
|
sl@0
|
446 |
# (implemented in func.c) only available in test builds.
|
|
sl@0
|
447 |
#
|
|
sl@0
|
448 |
# Function test_destructor() takes one argument and returns a copy of the
|
|
sl@0
|
449 |
# text form of that argument. A destructor is associated with the return
|
|
sl@0
|
450 |
# value. Function test_destructor_count() returns the number of outstanding
|
|
sl@0
|
451 |
# destructor calls for values returned by test_destructor().
|
|
sl@0
|
452 |
#
|
|
sl@0
|
453 |
if {[db eval {PRAGMA encoding}]=="UTF-8"} {
|
|
sl@0
|
454 |
do_test func-12.1-utf8 {
|
|
sl@0
|
455 |
execsql {
|
|
sl@0
|
456 |
SELECT test_destructor('hello world'), test_destructor_count();
|
|
sl@0
|
457 |
}
|
|
sl@0
|
458 |
} {{hello world} 1}
|
|
sl@0
|
459 |
} else {
|
|
sl@0
|
460 |
ifcapable {utf16} {
|
|
sl@0
|
461 |
do_test func-12.1-utf16 {
|
|
sl@0
|
462 |
execsql {
|
|
sl@0
|
463 |
SELECT test_destructor16('hello world'), test_destructor_count();
|
|
sl@0
|
464 |
}
|
|
sl@0
|
465 |
} {{hello world} 1}
|
|
sl@0
|
466 |
}
|
|
sl@0
|
467 |
}
|
|
sl@0
|
468 |
do_test func-12.2 {
|
|
sl@0
|
469 |
execsql {
|
|
sl@0
|
470 |
SELECT test_destructor_count();
|
|
sl@0
|
471 |
}
|
|
sl@0
|
472 |
} {0}
|
|
sl@0
|
473 |
do_test func-12.3 {
|
|
sl@0
|
474 |
execsql {
|
|
sl@0
|
475 |
SELECT test_destructor('hello')||' world'
|
|
sl@0
|
476 |
}
|
|
sl@0
|
477 |
} {{hello world}}
|
|
sl@0
|
478 |
do_test func-12.4 {
|
|
sl@0
|
479 |
execsql {
|
|
sl@0
|
480 |
SELECT test_destructor_count();
|
|
sl@0
|
481 |
}
|
|
sl@0
|
482 |
} {0}
|
|
sl@0
|
483 |
do_test func-12.5 {
|
|
sl@0
|
484 |
execsql {
|
|
sl@0
|
485 |
CREATE TABLE t4(x);
|
|
sl@0
|
486 |
INSERT INTO t4 VALUES(test_destructor('hello'));
|
|
sl@0
|
487 |
INSERT INTO t4 VALUES(test_destructor('world'));
|
|
sl@0
|
488 |
SELECT min(test_destructor(x)), max(test_destructor(x)) FROM t4;
|
|
sl@0
|
489 |
}
|
|
sl@0
|
490 |
} {hello world}
|
|
sl@0
|
491 |
do_test func-12.6 {
|
|
sl@0
|
492 |
execsql {
|
|
sl@0
|
493 |
SELECT test_destructor_count();
|
|
sl@0
|
494 |
}
|
|
sl@0
|
495 |
} {0}
|
|
sl@0
|
496 |
do_test func-12.7 {
|
|
sl@0
|
497 |
execsql {
|
|
sl@0
|
498 |
DROP TABLE t4;
|
|
sl@0
|
499 |
}
|
|
sl@0
|
500 |
} {}
|
|
sl@0
|
501 |
|
|
sl@0
|
502 |
|
|
sl@0
|
503 |
# Test that the auxdata API for scalar functions works. This test uses
|
|
sl@0
|
504 |
# a special user-defined function only available in test builds,
|
|
sl@0
|
505 |
# test_auxdata(). Function test_auxdata() takes any number of arguments.
|
|
sl@0
|
506 |
do_test func-13.1 {
|
|
sl@0
|
507 |
execsql {
|
|
sl@0
|
508 |
SELECT test_auxdata('hello world');
|
|
sl@0
|
509 |
}
|
|
sl@0
|
510 |
} {0}
|
|
sl@0
|
511 |
|
|
sl@0
|
512 |
do_test func-13.2 {
|
|
sl@0
|
513 |
execsql {
|
|
sl@0
|
514 |
CREATE TABLE t4(a, b);
|
|
sl@0
|
515 |
INSERT INTO t4 VALUES('abc', 'def');
|
|
sl@0
|
516 |
INSERT INTO t4 VALUES('ghi', 'jkl');
|
|
sl@0
|
517 |
}
|
|
sl@0
|
518 |
} {}
|
|
sl@0
|
519 |
do_test func-13.3 {
|
|
sl@0
|
520 |
execsql {
|
|
sl@0
|
521 |
SELECT test_auxdata('hello world') FROM t4;
|
|
sl@0
|
522 |
}
|
|
sl@0
|
523 |
} {0 1}
|
|
sl@0
|
524 |
do_test func-13.4 {
|
|
sl@0
|
525 |
execsql {
|
|
sl@0
|
526 |
SELECT test_auxdata('hello world', 123) FROM t4;
|
|
sl@0
|
527 |
}
|
|
sl@0
|
528 |
} {{0 0} {1 1}}
|
|
sl@0
|
529 |
do_test func-13.5 {
|
|
sl@0
|
530 |
execsql {
|
|
sl@0
|
531 |
SELECT test_auxdata('hello world', a) FROM t4;
|
|
sl@0
|
532 |
}
|
|
sl@0
|
533 |
} {{0 0} {1 0}}
|
|
sl@0
|
534 |
do_test func-13.6 {
|
|
sl@0
|
535 |
execsql {
|
|
sl@0
|
536 |
SELECT test_auxdata('hello'||'world', a) FROM t4;
|
|
sl@0
|
537 |
}
|
|
sl@0
|
538 |
} {{0 0} {1 0}}
|
|
sl@0
|
539 |
|
|
sl@0
|
540 |
# Test that auxilary data is preserved between calls for SQL variables.
|
|
sl@0
|
541 |
do_test func-13.7 {
|
|
sl@0
|
542 |
set DB [sqlite3_connection_pointer db]
|
|
sl@0
|
543 |
set sql "SELECT test_auxdata( ? , a ) FROM t4;"
|
|
sl@0
|
544 |
set STMT [sqlite3_prepare $DB $sql -1 TAIL]
|
|
sl@0
|
545 |
sqlite3_bind_text $STMT 1 hello\000 -1
|
|
sl@0
|
546 |
set res [list]
|
|
sl@0
|
547 |
while { "SQLITE_ROW"==[sqlite3_step $STMT] } {
|
|
sl@0
|
548 |
lappend res [sqlite3_column_text $STMT 0]
|
|
sl@0
|
549 |
}
|
|
sl@0
|
550 |
lappend res [sqlite3_finalize $STMT]
|
|
sl@0
|
551 |
} {{0 0} {1 0} SQLITE_OK}
|
|
sl@0
|
552 |
|
|
sl@0
|
553 |
# Make sure that a function with a very long name is rejected
|
|
sl@0
|
554 |
do_test func-14.1 {
|
|
sl@0
|
555 |
catch {
|
|
sl@0
|
556 |
db function [string repeat X 254] {return "hello"}
|
|
sl@0
|
557 |
}
|
|
sl@0
|
558 |
} {0}
|
|
sl@0
|
559 |
do_test func-14.2 {
|
|
sl@0
|
560 |
catch {
|
|
sl@0
|
561 |
db function [string repeat X 256] {return "hello"}
|
|
sl@0
|
562 |
}
|
|
sl@0
|
563 |
} {1}
|
|
sl@0
|
564 |
|
|
sl@0
|
565 |
do_test func-15.1 {
|
|
sl@0
|
566 |
catchsql {select test_error(NULL)}
|
|
sl@0
|
567 |
} {1 {}}
|
|
sl@0
|
568 |
do_test func-15.2 {
|
|
sl@0
|
569 |
catchsql {select test_error('this is the error message')}
|
|
sl@0
|
570 |
} {1 {this is the error message}}
|
|
sl@0
|
571 |
do_test func-15.3 {
|
|
sl@0
|
572 |
catchsql {select test_error('this is the error message',12)}
|
|
sl@0
|
573 |
} {1 {this is the error message}}
|
|
sl@0
|
574 |
do_test func-15.4 {
|
|
sl@0
|
575 |
db errorcode
|
|
sl@0
|
576 |
} {12}
|
|
sl@0
|
577 |
|
|
sl@0
|
578 |
# Test the quote function for BLOB and NULL values.
|
|
sl@0
|
579 |
do_test func-16.1 {
|
|
sl@0
|
580 |
execsql {
|
|
sl@0
|
581 |
CREATE TABLE tbl2(a, b);
|
|
sl@0
|
582 |
}
|
|
sl@0
|
583 |
set STMT [sqlite3_prepare $::DB "INSERT INTO tbl2 VALUES(?, ?)" -1 TAIL]
|
|
sl@0
|
584 |
sqlite3_bind_blob $::STMT 1 abc 3
|
|
sl@0
|
585 |
sqlite3_step $::STMT
|
|
sl@0
|
586 |
sqlite3_finalize $::STMT
|
|
sl@0
|
587 |
execsql {
|
|
sl@0
|
588 |
SELECT quote(a), quote(b) FROM tbl2;
|
|
sl@0
|
589 |
}
|
|
sl@0
|
590 |
} {X'616263' NULL}
|
|
sl@0
|
591 |
|
|
sl@0
|
592 |
# Correctly handle function error messages that include %. Ticket #1354
|
|
sl@0
|
593 |
#
|
|
sl@0
|
594 |
do_test func-17.1 {
|
|
sl@0
|
595 |
proc testfunc1 args {error "Error %d with %s percents %p"}
|
|
sl@0
|
596 |
db function testfunc1 ::testfunc1
|
|
sl@0
|
597 |
catchsql {
|
|
sl@0
|
598 |
SELECT testfunc1(1,2,3);
|
|
sl@0
|
599 |
}
|
|
sl@0
|
600 |
} {1 {Error %d with %s percents %p}}
|
|
sl@0
|
601 |
|
|
sl@0
|
602 |
# The SUM function should return integer results when all inputs are integer.
|
|
sl@0
|
603 |
#
|
|
sl@0
|
604 |
do_test func-18.1 {
|
|
sl@0
|
605 |
execsql {
|
|
sl@0
|
606 |
CREATE TABLE t5(x);
|
|
sl@0
|
607 |
INSERT INTO t5 VALUES(1);
|
|
sl@0
|
608 |
INSERT INTO t5 VALUES(-99);
|
|
sl@0
|
609 |
INSERT INTO t5 VALUES(10000);
|
|
sl@0
|
610 |
SELECT sum(x) FROM t5;
|
|
sl@0
|
611 |
}
|
|
sl@0
|
612 |
} {9902}
|
|
sl@0
|
613 |
do_test func-18.2 {
|
|
sl@0
|
614 |
execsql {
|
|
sl@0
|
615 |
INSERT INTO t5 VALUES(0.0);
|
|
sl@0
|
616 |
SELECT sum(x) FROM t5;
|
|
sl@0
|
617 |
}
|
|
sl@0
|
618 |
} {9902.0}
|
|
sl@0
|
619 |
|
|
sl@0
|
620 |
# The sum of nothing is NULL. But the sum of all NULLs is NULL.
|
|
sl@0
|
621 |
#
|
|
sl@0
|
622 |
# The TOTAL of nothing is 0.0.
|
|
sl@0
|
623 |
#
|
|
sl@0
|
624 |
do_test func-18.3 {
|
|
sl@0
|
625 |
execsql {
|
|
sl@0
|
626 |
DELETE FROM t5;
|
|
sl@0
|
627 |
SELECT sum(x), total(x) FROM t5;
|
|
sl@0
|
628 |
}
|
|
sl@0
|
629 |
} {{} 0.0}
|
|
sl@0
|
630 |
do_test func-18.4 {
|
|
sl@0
|
631 |
execsql {
|
|
sl@0
|
632 |
INSERT INTO t5 VALUES(NULL);
|
|
sl@0
|
633 |
SELECT sum(x), total(x) FROM t5
|
|
sl@0
|
634 |
}
|
|
sl@0
|
635 |
} {{} 0.0}
|
|
sl@0
|
636 |
do_test func-18.5 {
|
|
sl@0
|
637 |
execsql {
|
|
sl@0
|
638 |
INSERT INTO t5 VALUES(NULL);
|
|
sl@0
|
639 |
SELECT sum(x), total(x) FROM t5
|
|
sl@0
|
640 |
}
|
|
sl@0
|
641 |
} {{} 0.0}
|
|
sl@0
|
642 |
do_test func-18.6 {
|
|
sl@0
|
643 |
execsql {
|
|
sl@0
|
644 |
INSERT INTO t5 VALUES(123);
|
|
sl@0
|
645 |
SELECT sum(x), total(x) FROM t5
|
|
sl@0
|
646 |
}
|
|
sl@0
|
647 |
} {123 123.0}
|
|
sl@0
|
648 |
|
|
sl@0
|
649 |
# Ticket #1664, #1669, #1670, #1674: An integer overflow on SUM causes
|
|
sl@0
|
650 |
# an error. The non-standard TOTAL() function continues to give a helpful
|
|
sl@0
|
651 |
# result.
|
|
sl@0
|
652 |
#
|
|
sl@0
|
653 |
do_test func-18.10 {
|
|
sl@0
|
654 |
execsql {
|
|
sl@0
|
655 |
CREATE TABLE t6(x INTEGER);
|
|
sl@0
|
656 |
INSERT INTO t6 VALUES(1);
|
|
sl@0
|
657 |
INSERT INTO t6 VALUES(1<<62);
|
|
sl@0
|
658 |
SELECT sum(x) - ((1<<62)+1) from t6;
|
|
sl@0
|
659 |
}
|
|
sl@0
|
660 |
} 0
|
|
sl@0
|
661 |
do_test func-18.11 {
|
|
sl@0
|
662 |
execsql {
|
|
sl@0
|
663 |
SELECT typeof(sum(x)) FROM t6
|
|
sl@0
|
664 |
}
|
|
sl@0
|
665 |
} integer
|
|
sl@0
|
666 |
do_test func-18.12 {
|
|
sl@0
|
667 |
catchsql {
|
|
sl@0
|
668 |
INSERT INTO t6 VALUES(1<<62);
|
|
sl@0
|
669 |
SELECT sum(x) - ((1<<62)*2.0+1) from t6;
|
|
sl@0
|
670 |
}
|
|
sl@0
|
671 |
} {1 {integer overflow}}
|
|
sl@0
|
672 |
do_test func-18.13 {
|
|
sl@0
|
673 |
execsql {
|
|
sl@0
|
674 |
SELECT total(x) - ((1<<62)*2.0+1) FROM t6
|
|
sl@0
|
675 |
}
|
|
sl@0
|
676 |
} 0.0
|
|
sl@0
|
677 |
do_test func-18.14 {
|
|
sl@0
|
678 |
execsql {
|
|
sl@0
|
679 |
SELECT sum(-9223372036854775805);
|
|
sl@0
|
680 |
}
|
|
sl@0
|
681 |
} -9223372036854775805
|
|
sl@0
|
682 |
|
|
sl@0
|
683 |
ifcapable compound&&subquery {
|
|
sl@0
|
684 |
|
|
sl@0
|
685 |
do_test func-18.15 {
|
|
sl@0
|
686 |
catchsql {
|
|
sl@0
|
687 |
SELECT sum(x) FROM
|
|
sl@0
|
688 |
(SELECT 9223372036854775807 AS x UNION ALL
|
|
sl@0
|
689 |
SELECT 10 AS x);
|
|
sl@0
|
690 |
}
|
|
sl@0
|
691 |
} {1 {integer overflow}}
|
|
sl@0
|
692 |
do_test func-18.16 {
|
|
sl@0
|
693 |
catchsql {
|
|
sl@0
|
694 |
SELECT sum(x) FROM
|
|
sl@0
|
695 |
(SELECT 9223372036854775807 AS x UNION ALL
|
|
sl@0
|
696 |
SELECT -10 AS x);
|
|
sl@0
|
697 |
}
|
|
sl@0
|
698 |
} {0 9223372036854775797}
|
|
sl@0
|
699 |
do_test func-18.17 {
|
|
sl@0
|
700 |
catchsql {
|
|
sl@0
|
701 |
SELECT sum(x) FROM
|
|
sl@0
|
702 |
(SELECT -9223372036854775807 AS x UNION ALL
|
|
sl@0
|
703 |
SELECT 10 AS x);
|
|
sl@0
|
704 |
}
|
|
sl@0
|
705 |
} {0 -9223372036854775797}
|
|
sl@0
|
706 |
do_test func-18.18 {
|
|
sl@0
|
707 |
catchsql {
|
|
sl@0
|
708 |
SELECT sum(x) FROM
|
|
sl@0
|
709 |
(SELECT -9223372036854775807 AS x UNION ALL
|
|
sl@0
|
710 |
SELECT -10 AS x);
|
|
sl@0
|
711 |
}
|
|
sl@0
|
712 |
} {1 {integer overflow}}
|
|
sl@0
|
713 |
do_test func-18.19 {
|
|
sl@0
|
714 |
catchsql {
|
|
sl@0
|
715 |
SELECT sum(x) FROM (SELECT 9 AS x UNION ALL SELECT -10 AS x);
|
|
sl@0
|
716 |
}
|
|
sl@0
|
717 |
} {0 -1}
|
|
sl@0
|
718 |
do_test func-18.20 {
|
|
sl@0
|
719 |
catchsql {
|
|
sl@0
|
720 |
SELECT sum(x) FROM (SELECT -9 AS x UNION ALL SELECT 10 AS x);
|
|
sl@0
|
721 |
}
|
|
sl@0
|
722 |
} {0 1}
|
|
sl@0
|
723 |
do_test func-18.21 {
|
|
sl@0
|
724 |
catchsql {
|
|
sl@0
|
725 |
SELECT sum(x) FROM (SELECT -10 AS x UNION ALL SELECT 9 AS x);
|
|
sl@0
|
726 |
}
|
|
sl@0
|
727 |
} {0 -1}
|
|
sl@0
|
728 |
do_test func-18.22 {
|
|
sl@0
|
729 |
catchsql {
|
|
sl@0
|
730 |
SELECT sum(x) FROM (SELECT 10 AS x UNION ALL SELECT -9 AS x);
|
|
sl@0
|
731 |
}
|
|
sl@0
|
732 |
} {0 1}
|
|
sl@0
|
733 |
|
|
sl@0
|
734 |
} ;# ifcapable compound&&subquery
|
|
sl@0
|
735 |
|
|
sl@0
|
736 |
# Integer overflow on abs()
|
|
sl@0
|
737 |
#
|
|
sl@0
|
738 |
do_test func-18.31 {
|
|
sl@0
|
739 |
catchsql {
|
|
sl@0
|
740 |
SELECT abs(-9223372036854775807);
|
|
sl@0
|
741 |
}
|
|
sl@0
|
742 |
} {0 9223372036854775807}
|
|
sl@0
|
743 |
do_test func-18.32 {
|
|
sl@0
|
744 |
catchsql {
|
|
sl@0
|
745 |
SELECT abs(-9223372036854775807-1);
|
|
sl@0
|
746 |
}
|
|
sl@0
|
747 |
} {1 {integer overflow}}
|
|
sl@0
|
748 |
|
|
sl@0
|
749 |
# The MATCH function exists but is only a stub and always throws an error.
|
|
sl@0
|
750 |
#
|
|
sl@0
|
751 |
do_test func-19.1 {
|
|
sl@0
|
752 |
execsql {
|
|
sl@0
|
753 |
SELECT match(a,b) FROM t1 WHERE 0;
|
|
sl@0
|
754 |
}
|
|
sl@0
|
755 |
} {}
|
|
sl@0
|
756 |
do_test func-19.2 {
|
|
sl@0
|
757 |
catchsql {
|
|
sl@0
|
758 |
SELECT 'abc' MATCH 'xyz';
|
|
sl@0
|
759 |
}
|
|
sl@0
|
760 |
} {1 {unable to use function MATCH in the requested context}}
|
|
sl@0
|
761 |
do_test func-19.3 {
|
|
sl@0
|
762 |
catchsql {
|
|
sl@0
|
763 |
SELECT 'abc' NOT MATCH 'xyz';
|
|
sl@0
|
764 |
}
|
|
sl@0
|
765 |
} {1 {unable to use function MATCH in the requested context}}
|
|
sl@0
|
766 |
do_test func-19.4 {
|
|
sl@0
|
767 |
catchsql {
|
|
sl@0
|
768 |
SELECT match(1,2,3);
|
|
sl@0
|
769 |
}
|
|
sl@0
|
770 |
} {1 {wrong number of arguments to function match()}}
|
|
sl@0
|
771 |
|
|
sl@0
|
772 |
# Soundex tests.
|
|
sl@0
|
773 |
#
|
|
sl@0
|
774 |
if {![catch {db eval {SELECT soundex('hello')}}]} {
|
|
sl@0
|
775 |
set i 0
|
|
sl@0
|
776 |
foreach {name sdx} {
|
|
sl@0
|
777 |
euler E460
|
|
sl@0
|
778 |
EULER E460
|
|
sl@0
|
779 |
Euler E460
|
|
sl@0
|
780 |
ellery E460
|
|
sl@0
|
781 |
gauss G200
|
|
sl@0
|
782 |
ghosh G200
|
|
sl@0
|
783 |
hilbert H416
|
|
sl@0
|
784 |
Heilbronn H416
|
|
sl@0
|
785 |
knuth K530
|
|
sl@0
|
786 |
kant K530
|
|
sl@0
|
787 |
Lloyd L300
|
|
sl@0
|
788 |
LADD L300
|
|
sl@0
|
789 |
Lukasiewicz L222
|
|
sl@0
|
790 |
Lissajous L222
|
|
sl@0
|
791 |
A A000
|
|
sl@0
|
792 |
12345 ?000
|
|
sl@0
|
793 |
} {
|
|
sl@0
|
794 |
incr i
|
|
sl@0
|
795 |
do_test func-20.$i {
|
|
sl@0
|
796 |
execsql {SELECT soundex($name)}
|
|
sl@0
|
797 |
} $sdx
|
|
sl@0
|
798 |
}
|
|
sl@0
|
799 |
}
|
|
sl@0
|
800 |
|
|
sl@0
|
801 |
# Tests of the REPLACE function.
|
|
sl@0
|
802 |
#
|
|
sl@0
|
803 |
do_test func-21.1 {
|
|
sl@0
|
804 |
catchsql {
|
|
sl@0
|
805 |
SELECT replace(1,2);
|
|
sl@0
|
806 |
}
|
|
sl@0
|
807 |
} {1 {wrong number of arguments to function replace()}}
|
|
sl@0
|
808 |
do_test func-21.2 {
|
|
sl@0
|
809 |
catchsql {
|
|
sl@0
|
810 |
SELECT replace(1,2,3,4);
|
|
sl@0
|
811 |
}
|
|
sl@0
|
812 |
} {1 {wrong number of arguments to function replace()}}
|
|
sl@0
|
813 |
do_test func-21.3 {
|
|
sl@0
|
814 |
execsql {
|
|
sl@0
|
815 |
SELECT typeof(replace("This is the main test string", NULL, "ALT"));
|
|
sl@0
|
816 |
}
|
|
sl@0
|
817 |
} {null}
|
|
sl@0
|
818 |
do_test func-21.4 {
|
|
sl@0
|
819 |
execsql {
|
|
sl@0
|
820 |
SELECT typeof(replace(NULL, "main", "ALT"));
|
|
sl@0
|
821 |
}
|
|
sl@0
|
822 |
} {null}
|
|
sl@0
|
823 |
do_test func-21.5 {
|
|
sl@0
|
824 |
execsql {
|
|
sl@0
|
825 |
SELECT typeof(replace("This is the main test string", "main", NULL));
|
|
sl@0
|
826 |
}
|
|
sl@0
|
827 |
} {null}
|
|
sl@0
|
828 |
do_test func-21.6 {
|
|
sl@0
|
829 |
execsql {
|
|
sl@0
|
830 |
SELECT replace("This is the main test string", "main", "ALT");
|
|
sl@0
|
831 |
}
|
|
sl@0
|
832 |
} {{This is the ALT test string}}
|
|
sl@0
|
833 |
do_test func-21.7 {
|
|
sl@0
|
834 |
execsql {
|
|
sl@0
|
835 |
SELECT replace("This is the main test string", "main", "larger-main");
|
|
sl@0
|
836 |
}
|
|
sl@0
|
837 |
} {{This is the larger-main test string}}
|
|
sl@0
|
838 |
do_test func-21.8 {
|
|
sl@0
|
839 |
execsql {
|
|
sl@0
|
840 |
SELECT replace("aaaaaaa", "a", "0123456789");
|
|
sl@0
|
841 |
}
|
|
sl@0
|
842 |
} {0123456789012345678901234567890123456789012345678901234567890123456789}
|
|
sl@0
|
843 |
|
|
sl@0
|
844 |
ifcapable tclvar {
|
|
sl@0
|
845 |
do_test func-21.9 {
|
|
sl@0
|
846 |
# Attempt to exploit a buffer-overflow that at one time existed
|
|
sl@0
|
847 |
# in the REPLACE function.
|
|
sl@0
|
848 |
set ::str "[string repeat A 29998]CC[string repeat A 35537]"
|
|
sl@0
|
849 |
set ::rep [string repeat B 65536]
|
|
sl@0
|
850 |
execsql {
|
|
sl@0
|
851 |
SELECT LENGTH(REPLACE($::str, 'C', $::rep));
|
|
sl@0
|
852 |
}
|
|
sl@0
|
853 |
} [expr 29998 + 2*65536 + 35537]
|
|
sl@0
|
854 |
}
|
|
sl@0
|
855 |
|
|
sl@0
|
856 |
# Tests for the TRIM, LTRIM and RTRIM functions.
|
|
sl@0
|
857 |
#
|
|
sl@0
|
858 |
do_test func-22.1 {
|
|
sl@0
|
859 |
catchsql {SELECT trim(1,2,3)}
|
|
sl@0
|
860 |
} {1 {wrong number of arguments to function trim()}}
|
|
sl@0
|
861 |
do_test func-22.2 {
|
|
sl@0
|
862 |
catchsql {SELECT ltrim(1,2,3)}
|
|
sl@0
|
863 |
} {1 {wrong number of arguments to function ltrim()}}
|
|
sl@0
|
864 |
do_test func-22.3 {
|
|
sl@0
|
865 |
catchsql {SELECT rtrim(1,2,3)}
|
|
sl@0
|
866 |
} {1 {wrong number of arguments to function rtrim()}}
|
|
sl@0
|
867 |
do_test func-22.4 {
|
|
sl@0
|
868 |
execsql {SELECT trim(' hi ');}
|
|
sl@0
|
869 |
} {hi}
|
|
sl@0
|
870 |
do_test func-22.5 {
|
|
sl@0
|
871 |
execsql {SELECT ltrim(' hi ');}
|
|
sl@0
|
872 |
} {{hi }}
|
|
sl@0
|
873 |
do_test func-22.6 {
|
|
sl@0
|
874 |
execsql {SELECT rtrim(' hi ');}
|
|
sl@0
|
875 |
} {{ hi}}
|
|
sl@0
|
876 |
do_test func-22.7 {
|
|
sl@0
|
877 |
execsql {SELECT trim(' hi ','xyz');}
|
|
sl@0
|
878 |
} {{ hi }}
|
|
sl@0
|
879 |
do_test func-22.8 {
|
|
sl@0
|
880 |
execsql {SELECT ltrim(' hi ','xyz');}
|
|
sl@0
|
881 |
} {{ hi }}
|
|
sl@0
|
882 |
do_test func-22.9 {
|
|
sl@0
|
883 |
execsql {SELECT rtrim(' hi ','xyz');}
|
|
sl@0
|
884 |
} {{ hi }}
|
|
sl@0
|
885 |
do_test func-22.10 {
|
|
sl@0
|
886 |
execsql {SELECT trim('xyxzy hi zzzy','xyz');}
|
|
sl@0
|
887 |
} {{ hi }}
|
|
sl@0
|
888 |
do_test func-22.11 {
|
|
sl@0
|
889 |
execsql {SELECT ltrim('xyxzy hi zzzy','xyz');}
|
|
sl@0
|
890 |
} {{ hi zzzy}}
|
|
sl@0
|
891 |
do_test func-22.12 {
|
|
sl@0
|
892 |
execsql {SELECT rtrim('xyxzy hi zzzy','xyz');}
|
|
sl@0
|
893 |
} {{xyxzy hi }}
|
|
sl@0
|
894 |
do_test func-22.13 {
|
|
sl@0
|
895 |
execsql {SELECT trim(' hi ','');}
|
|
sl@0
|
896 |
} {{ hi }}
|
|
sl@0
|
897 |
if {[db one {PRAGMA encoding}]=="UTF-8"} {
|
|
sl@0
|
898 |
do_test func-22.14 {
|
|
sl@0
|
899 |
execsql {SELECT hex(trim(x'c280e1bfbff48fbfbf6869',x'6162e1bfbfc280'))}
|
|
sl@0
|
900 |
} {F48FBFBF6869}
|
|
sl@0
|
901 |
do_test func-22.15 {
|
|
sl@0
|
902 |
execsql {SELECT hex(trim(x'6869c280e1bfbff48fbfbf61',
|
|
sl@0
|
903 |
x'6162e1bfbfc280f48fbfbf'))}
|
|
sl@0
|
904 |
} {6869}
|
|
sl@0
|
905 |
do_test func-22.16 {
|
|
sl@0
|
906 |
execsql {SELECT hex(trim(x'ceb1ceb2ceb3',x'ceb1'));}
|
|
sl@0
|
907 |
} {CEB2CEB3}
|
|
sl@0
|
908 |
}
|
|
sl@0
|
909 |
do_test func-22.20 {
|
|
sl@0
|
910 |
execsql {SELECT typeof(trim(NULL));}
|
|
sl@0
|
911 |
} {null}
|
|
sl@0
|
912 |
do_test func-22.21 {
|
|
sl@0
|
913 |
execsql {SELECT typeof(trim(NULL,'xyz'));}
|
|
sl@0
|
914 |
} {null}
|
|
sl@0
|
915 |
do_test func-22.22 {
|
|
sl@0
|
916 |
execsql {SELECT typeof(trim('hello',NULL));}
|
|
sl@0
|
917 |
} {null}
|
|
sl@0
|
918 |
|
|
sl@0
|
919 |
# This is to test the deprecated sqlite3_aggregate_count() API.
|
|
sl@0
|
920 |
#
|
|
sl@0
|
921 |
do_test func-23.1 {
|
|
sl@0
|
922 |
sqlite3_create_aggregate db
|
|
sl@0
|
923 |
execsql {
|
|
sl@0
|
924 |
SELECT legacy_count() FROM t6;
|
|
sl@0
|
925 |
}
|
|
sl@0
|
926 |
} {3}
|
|
sl@0
|
927 |
|
|
sl@0
|
928 |
# The group_concat() function.
|
|
sl@0
|
929 |
#
|
|
sl@0
|
930 |
do_test func-24.1 {
|
|
sl@0
|
931 |
execsql {
|
|
sl@0
|
932 |
SELECT group_concat(t1) FROM tbl1
|
|
sl@0
|
933 |
}
|
|
sl@0
|
934 |
} {this,program,is,free,software}
|
|
sl@0
|
935 |
do_test func-24.2 {
|
|
sl@0
|
936 |
execsql {
|
|
sl@0
|
937 |
SELECT group_concat(t1,' ') FROM tbl1
|
|
sl@0
|
938 |
}
|
|
sl@0
|
939 |
} {{this program is free software}}
|
|
sl@0
|
940 |
do_test func-24.3 {
|
|
sl@0
|
941 |
execsql {
|
|
sl@0
|
942 |
SELECT group_concat(t1,' ' || rowid || ' ') FROM tbl1
|
|
sl@0
|
943 |
}
|
|
sl@0
|
944 |
} {{this 2 program 3 is 4 free 5 software}}
|
|
sl@0
|
945 |
do_test func-24.4 {
|
|
sl@0
|
946 |
execsql {
|
|
sl@0
|
947 |
SELECT group_concat(NULL,t1) FROM tbl1
|
|
sl@0
|
948 |
}
|
|
sl@0
|
949 |
} {{}}
|
|
sl@0
|
950 |
do_test func-24.5 {
|
|
sl@0
|
951 |
execsql {
|
|
sl@0
|
952 |
SELECT group_concat(t1,NULL) FROM tbl1
|
|
sl@0
|
953 |
}
|
|
sl@0
|
954 |
} {thisprogramisfreesoftware}
|
|
sl@0
|
955 |
do_test func-24.6 {
|
|
sl@0
|
956 |
execsql {
|
|
sl@0
|
957 |
SELECT 'BEGIN-'||group_concat(t1) FROM tbl1
|
|
sl@0
|
958 |
}
|
|
sl@0
|
959 |
} {BEGIN-this,program,is,free,software}
|
|
sl@0
|
960 |
unset -nocomplain midargs
|
|
sl@0
|
961 |
set midargs {}
|
|
sl@0
|
962 |
unset -nocomplain midres
|
|
sl@0
|
963 |
set midres {}
|
|
sl@0
|
964 |
unset -nocomplain result
|
|
sl@0
|
965 |
for {set i 1} {$i<[sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG -1]-1} {incr i} {
|
|
sl@0
|
966 |
append midargs ,'/$i'
|
|
sl@0
|
967 |
append midres /$i
|
|
sl@0
|
968 |
set result \
|
|
sl@0
|
969 |
"this$midres:program$midres:is$midres:free$midres:software$midres"
|
|
sl@0
|
970 |
set sql "SELECT group_concat(t1$midargs,':') FROM tbl1"
|
|
sl@0
|
971 |
do_test func-24.7.$i {
|
|
sl@0
|
972 |
db eval $::sql
|
|
sl@0
|
973 |
} $result
|
|
sl@0
|
974 |
}
|
|
sl@0
|
975 |
|
|
sl@0
|
976 |
# Use the test_isolation function to make sure that type conversions
|
|
sl@0
|
977 |
# on function arguments do not effect subsequent arguments.
|
|
sl@0
|
978 |
#
|
|
sl@0
|
979 |
do_test func-25.1 {
|
|
sl@0
|
980 |
execsql {SELECT test_isolation(t1,t1) FROM tbl1}
|
|
sl@0
|
981 |
} {this program is free software}
|
|
sl@0
|
982 |
|
|
sl@0
|
983 |
# Try to misuse the sqlite3_create_function() interface. Verify that
|
|
sl@0
|
984 |
# errors are returned.
|
|
sl@0
|
985 |
#
|
|
sl@0
|
986 |
do_test func-26.1 {
|
|
sl@0
|
987 |
abuse_create_function db
|
|
sl@0
|
988 |
} {}
|
|
sl@0
|
989 |
|
|
sl@0
|
990 |
# The previous test (func-26.1) registered a function with a very long
|
|
sl@0
|
991 |
# function name that takes many arguments and always returns NULL. Verify
|
|
sl@0
|
992 |
# that this function works correctly.
|
|
sl@0
|
993 |
#
|
|
sl@0
|
994 |
do_test func-26.2 {
|
|
sl@0
|
995 |
set a {}
|
|
sl@0
|
996 |
for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG} {incr i} {
|
|
sl@0
|
997 |
lappend a $i
|
|
sl@0
|
998 |
}
|
|
sl@0
|
999 |
db eval "
|
|
sl@0
|
1000 |
SELECT nullx_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789([join $a ,]);
|
|
sl@0
|
1001 |
"
|
|
sl@0
|
1002 |
} {{}}
|
|
sl@0
|
1003 |
do_test func-26.3 {
|
|
sl@0
|
1004 |
set a {}
|
|
sl@0
|
1005 |
for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG+1} {incr i} {
|
|
sl@0
|
1006 |
lappend a $i
|
|
sl@0
|
1007 |
}
|
|
sl@0
|
1008 |
catchsql "
|
|
sl@0
|
1009 |
SELECT nullx_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789([join $a ,]);
|
|
sl@0
|
1010 |
"
|
|
sl@0
|
1011 |
} {1 {too many arguments on function nullx_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789}}
|
|
sl@0
|
1012 |
do_test func-26.4 {
|
|
sl@0
|
1013 |
set a {}
|
|
sl@0
|
1014 |
for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG-1} {incr i} {
|
|
sl@0
|
1015 |
lappend a $i
|
|
sl@0
|
1016 |
}
|
|
sl@0
|
1017 |
catchsql "
|
|
sl@0
|
1018 |
SELECT nullx_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789([join $a ,]);
|
|
sl@0
|
1019 |
"
|
|
sl@0
|
1020 |
} {1 {wrong number of arguments to function nullx_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789()}}
|
|
sl@0
|
1021 |
do_test func-26.5 {
|
|
sl@0
|
1022 |
catchsql "
|
|
sl@0
|
1023 |
SELECT nullx_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_12345678a(0);
|
|
sl@0
|
1024 |
"
|
|
sl@0
|
1025 |
} {1 {no such function: nullx_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_12345678a}}
|
|
sl@0
|
1026 |
do_test func-26.6 {
|
|
sl@0
|
1027 |
catchsql "
|
|
sl@0
|
1028 |
SELECT nullx_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789a(0);
|
|
sl@0
|
1029 |
"
|
|
sl@0
|
1030 |
} {1 {no such function: nullx_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789a}}
|
|
sl@0
|
1031 |
|
|
sl@0
|
1032 |
finish_test
|