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 {}}
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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to func.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