Debian Codegraph @

test/func.test

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