wasCSharpSQLite – Blame information for rev 1

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