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