3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
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.
10 #***********************************************************************
11 # This file implements regression tests for SQLite library. The
12 # focus of this file is testing built-in functions.
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
19 # Create a table to work with.
22 execsql {CREATE TABLE tbl1(t1 text)}
23 foreach word {this program is free software} {
24 execsql "INSERT INTO tbl1 VALUES('$word')"
26 execsql {SELECT t1 FROM tbl1 ORDER BY t1}
27 } {free is program software this}
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);
40 # Check out the length() function
43 execsql {SELECT length(t1) FROM tbl1 ORDER BY t1}
46 set r [catch {execsql {SELECT length(*) FROM tbl1 ORDER BY t1}} msg]
48 } {1 {wrong number of arguments to function length()}}
50 set r [catch {execsql {SELECT length(t1,5) FROM tbl1 ORDER BY t1}} msg]
52 } {1 {wrong number of arguments to function length()}}
54 execsql {SELECT length(t1), count(*) FROM tbl1 GROUP BY length(t1)
58 execsql {SELECT coalesce(length(a),-1) FROM t2}
61 # Check out the substr() function
64 execsql {SELECT substr(t1,1,2) FROM tbl1 ORDER BY t1}
67 execsql {SELECT substr(t1,2,1) FROM tbl1 ORDER BY t1}
70 execsql {SELECT substr(t1,3,3) FROM tbl1 ORDER BY t1}
73 execsql {SELECT substr(t1,-1,1) FROM tbl1 ORDER BY t1}
76 execsql {SELECT substr(t1,-1,2) FROM tbl1 ORDER BY t1}
79 execsql {SELECT substr(t1,-2,1) FROM tbl1 ORDER BY t1}
82 execsql {SELECT substr(t1,-2,2) FROM tbl1 ORDER BY t1}
85 execsql {SELECT substr(t1,-4,2) FROM tbl1 ORDER BY t1}
88 execsql {SELECT t1 FROM tbl1 ORDER BY substr(t1,2,20)}
89 } {this software free program is}
91 execsql {SELECT substr(a,1,1) FROM t2}
94 execsql {SELECT substr(a,2,2) FROM t2}
97 # Only do the following tests if TCL has UTF-8 capabilities
99 if {"\u1234"!="u1234"} {
101 # Put some UTF-8 characters in the database
104 execsql {DELETE FROM tbl1}
105 foreach word "contains UTF-8 characters hi\u1234ho" {
106 execsql "INSERT INTO tbl1 VALUES('$word')"
108 execsql {SELECT t1 FROM tbl1 ORDER BY t1}
109 } "UTF-8 characters contains hi\u1234ho"
111 execsql {SELECT length(t1) FROM tbl1 ORDER BY t1}
114 execsql {SELECT substr(t1,1,2) FROM tbl1 ORDER BY t1}
117 execsql {SELECT substr(t1,1,3) FROM tbl1 ORDER BY t1}
118 } "UTF cha con hi\u1234"
120 execsql {SELECT substr(t1,2,2) FROM tbl1 ORDER BY t1}
123 execsql {SELECT substr(t1,2,3) FROM tbl1 ORDER BY t1}
124 } "TF- har ont i\u1234h"
126 execsql {SELECT substr(t1,3,2) FROM tbl1 ORDER BY t1}
129 execsql {SELECT substr(t1,4,2) FROM tbl1 ORDER BY t1}
132 execsql {SELECT substr(t1,-1,1) FROM tbl1 ORDER BY t1}
135 execsql {SELECT substr(t1,-3,2) FROM tbl1 ORDER BY t1}
138 execsql {SELECT substr(t1,-4,3) FROM tbl1 ORDER BY t1}
139 } "TF- ter ain i\u1234h"
141 execsql {DELETE FROM tbl1}
142 foreach word {this program is free software} {
143 execsql "INSERT INTO tbl1 VALUES('$word')"
145 execsql {SELECT t1 FROM tbl1}
146 } {this program is free software}
148 } ;# End \u1234!=u1234
150 # Test the abs() and round() functions.
152 ifcapable !floatingpoint {
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);
160 catchsql {SELECT abs(a,b) FROM t1}
161 } {1 {wrong number of arguments to function abs()}}
163 ifcapable floatingpoint {
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);
171 catchsql {SELECT abs(a,b) FROM t1}
172 } {1 {wrong number of arguments to function abs()}}
175 catchsql {SELECT abs() FROM t1}
176 } {1 {wrong number of arguments to function abs()}}
177 ifcapable floatingpoint {
179 catchsql {SELECT abs(b) FROM t1 ORDER BY a}
180 } {0 {2 1.2345678901234 2}}
182 catchsql {SELECT abs(c) FROM t1 ORDER BY a}
183 } {0 {3 12345.6789 5}}
185 ifcapable !floatingpoint {
186 if {[working_64bit_int]} {
188 catchsql {SELECT abs(b) FROM t1 ORDER BY a}
189 } {0 {2 12345678901234 2}}
192 catchsql {SELECT abs(c) FROM t1 ORDER BY a}
193 } {0 {3 1234567890 5}}
196 execsql {SELECT abs(a) FROM t2}
197 } {1 {} 345 {} 67890}
199 execsql {SELECT abs(t1) FROM tbl1}
200 } {0.0 0.0 0.0 0.0 0.0}
202 ifcapable floatingpoint {
204 catchsql {SELECT round(a,b,c) FROM t1}
205 } {1 {wrong number of arguments to function round()}}
207 catchsql {SELECT round(b,2) FROM t1 ORDER BY b}
208 } {0 {-2.0 1.23 2.0}}
210 catchsql {SELECT round(b,0) FROM t1 ORDER BY a}
213 catchsql {SELECT round(c) FROM t1 ORDER BY a}
214 } {0 {3.0 -12346.0 -5.0}}
216 catchsql {SELECT round(c,a) FROM t1 ORDER BY a}
217 } {0 {3.0 -12345.68 -5.0}}
219 catchsql {SELECT 'x' || round(c,a) || 'y' FROM t1 ORDER BY a}
220 } {0 {x3.0y x-12345.68y x-5.0y}}
222 catchsql {SELECT round() FROM t1 ORDER BY a}
223 } {1 {wrong number of arguments to function round()}}
225 execsql {SELECT coalesce(round(a,2),'nil') FROM t2}
226 } {1.0 nil 345.0 nil 67890.0}
228 execsql {SELECT round(t1,2) FROM tbl1}
229 } {0.0 0.0 0.0 0.0 0.0}
231 execsql {SELECT typeof(round(5.1,1));}
234 execsql {SELECT typeof(round(5.1));}
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
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);}
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);}
258 execsql {SELECT round(40223.4999999999);}
261 execsql {SELECT round(40224.4999999999);}
264 execsql {SELECT round(40225.4999999999);}
266 for {set i 1} {$i<10} {incr i} {
267 do_test func-4.23.$i {
268 execsql {SELECT round(40223.4999999999,$i);}
270 do_test func-4.24.$i {
271 execsql {SELECT round(40224.4999999999,$i);}
273 do_test func-4.25.$i {
274 execsql {SELECT round(40225.4999999999,$i);}
277 for {set i 10} {$i<32} {incr i} {
278 do_test func-4.26.$i {
279 execsql {SELECT round(40223.4999999999,$i);}
281 do_test func-4.27.$i {
282 execsql {SELECT round(40224.4999999999,$i);}
284 do_test func-4.28.$i {
285 execsql {SELECT round(40225.4999999999,$i);}
289 execsql {SELECT round(1234567890.5);}
292 execsql {SELECT round(12345678901.5);}
295 execsql {SELECT round(123456789012.5);}
298 execsql {SELECT round(1234567890123.5);}
301 execsql {SELECT round(12345678901234.5);}
304 execsql {SELECT round(1234567890123.35,1);}
307 execsql {SELECT round(1234567890123.445,2);}
310 execsql {SELECT round(99999999999994.5);}
313 execsql {SELECT round(9999999999999.55,1);}
316 execsql {SELECT round(9999999999999.556,2);}
319 string tolower [db eval {SELECT round(1e500), round(-1e500);}]
323 # Test the upper() and lower() functions
326 execsql {SELECT upper(t1) FROM tbl1}
327 } {THIS PROGRAM IS FREE SOFTWARE}
329 execsql {SELECT lower(upper(t1)) FROM tbl1}
330 } {this program is free software}
332 execsql {SELECT upper(a), lower(a) FROM t2}
333 } {1 1 {} {} 345 345 {} {} 67890 67890}
336 catchsql {SELECT upper(a,5) FROM t2}
337 } {1 {wrong number of arguments to function upper()}}
340 catchsql {SELECT upper(*) FROM t2}
341 } {1 {wrong number of arguments to function upper()}}
343 # Test the coalesce() and nullif() functions
346 execsql {SELECT coalesce(a,'xyz') FROM t2}
347 } {1 xyz 345 xyz 67890}
349 execsql {SELECT coalesce(upper(a),'nil') FROM t2}
350 } {1 nil 345 nil 67890}
352 execsql {SELECT coalesce(nullif(1,1),'nil')}
355 execsql {SELECT coalesce(nullif(1,2),'nil')}
358 execsql {SELECT coalesce(nullif(1,NULL),'nil')}
362 # Test the last_insert_rowid() function
365 execsql {SELECT last_insert_rowid()}
366 } [db last_insert_rowid]
368 # Tests for aggregate functions and how they handle NULLs.
370 ifcapable floatingpoint {
373 execsql {EXPLAIN SELECT sum(a) FROM t2;}
376 SELECT sum(a), count(a), round(avg(a),2), min(a), max(a), count(*) FROM t2;
378 } {68236 3 22745.33 1 67890 5}
380 ifcapable !floatingpoint {
383 execsql {EXPLAIN SELECT sum(a) FROM t2;}
386 SELECT sum(a), count(a), avg(a), min(a), max(a), count(*) FROM t2;
388 } {68236 3 22745.0 1 67890 5}
392 SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t2;
394 } {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
399 CREATE TEMP TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;
400 SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
402 } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
406 CREATE TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;
407 SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
409 } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
413 SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
415 } {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
419 SELECT sum(x) FROM (SELECT '9223372036' || '854775807' AS x
420 UNION ALL SELECT -9223372036854775807)
425 SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775807' AS x
426 UNION ALL SELECT -9223372036854775807)
431 SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775808' AS x
432 UNION ALL SELECT -9223372036854775807)
435 ifcapable floatingpoint {
438 SELECT sum(x)>0.0 FROM (SELECT '9223372036' || '854775808' AS x
439 UNION ALL SELECT -9223372036850000000)
443 ifcapable !floatingpoint {
446 SELECT sum(x)>0 FROM (SELECT '9223372036' || '854775808' AS x
447 UNION ALL SELECT -9223372036850000000)
453 # How do you test the random() function in a meaningful, deterministic way?
457 SELECT random() is not null;
462 SELECT typeof(random());
467 SELECT randomblob(32) is not null;
472 SELECT typeof(randomblob(32));
477 SELECT length(randomblob(32)), length(randomblob(-5)),
478 length(randomblob(2000))
482 # The "hex()" function was added in order to be able to render blobs
483 # generated by randomblob(). So this seems like a good place to test
488 execsql {SELECT hex(x'00112233445566778899aAbBcCdDeEfF')}
489 } {00112233445566778899AABBCCDDEEFF}
491 set encoding [db one {PRAGMA encoding}]
492 if {$encoding=="UTF-16le"} {
493 do_test func-9.11-utf16le {
494 execsql {SELECT hex(replace('abcdefg','ef','12'))}
495 } {6100620063006400310032006700}
496 do_test func-9.12-utf16le {
497 execsql {SELECT hex(replace('abcdefg','','12'))}
498 } {6100620063006400650066006700}
499 do_test func-9.13-utf16le {
500 execsql {SELECT hex(replace('aabcdefg','a','aaa'))}
501 } {610061006100610061006100620063006400650066006700}
502 } elseif {$encoding=="UTF-8"} {
503 do_test func-9.11-utf8 {
504 execsql {SELECT hex(replace('abcdefg','ef','12'))}
506 do_test func-9.12-utf8 {
507 execsql {SELECT hex(replace('abcdefg','','12'))}
509 do_test func-9.13-utf8 {
510 execsql {SELECT hex(replace('aabcdefg','a','aaa'))}
511 } {616161616161626364656667}
513 do_execsql_test func-9.14 {
514 WITH RECURSIVE c(x) AS (
517 SELECT x+1 FROM c WHERE x<1040
521 sum(length(replace(printf('abc%.*cxyz',x,'m'),'m','nnnn'))-(6+x*4))
525 # Use the "sqlite_register_test_function" TCL command which is part of
526 # the text fixture in order to verify correct operation of some of
527 # the user-defined SQL function APIs that are not used by the built-in
530 set ::DB [sqlite3_connection_pointer db]
531 sqlite_register_test_function $::DB testfunc
534 SELECT testfunc(NULL,NULL);
536 } {1 {first argument should be one of: int int64 string double null value}}
540 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
548 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
554 ifcapable floatingpoint {
558 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
566 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
568 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
570 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
572 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
574 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
576 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
583 # Test the built-in sqlite_version(*) SQL function.
587 SELECT sqlite_version(*);
591 # Test that destructors passed to sqlite3 by calls to sqlite3_result_text()
592 # etc. are called. These tests use two special user-defined functions
593 # (implemented in func.c) only available in test builds.
595 # Function test_destructor() takes one argument and returns a copy of the
596 # text form of that argument. A destructor is associated with the return
597 # value. Function test_destructor_count() returns the number of outstanding
598 # destructor calls for values returned by test_destructor().
600 if {[db eval {PRAGMA encoding}]=="UTF-8"} {
601 do_test func-12.1-utf8 {
603 SELECT test_destructor('hello world'), test_destructor_count();
608 do_test func-12.1-utf16 {
610 SELECT test_destructor16('hello world'), test_destructor_count();
617 SELECT test_destructor_count();
622 SELECT test_destructor('hello')||' world'
627 SELECT test_destructor_count();
633 INSERT INTO t4 VALUES(test_destructor('hello'));
634 INSERT INTO t4 VALUES(test_destructor('world'));
635 SELECT min(test_destructor(x)), max(test_destructor(x)) FROM t4;
640 SELECT test_destructor_count();
650 # Test that the auxdata API for scalar functions works. This test uses
651 # a special user-defined function only available in test builds,
652 # test_auxdata(). Function test_auxdata() takes any number of arguments.
655 SELECT test_auxdata('hello world');
661 CREATE TABLE t4(a, b);
662 INSERT INTO t4 VALUES('abc', 'def');
663 INSERT INTO t4 VALUES('ghi', 'jkl');
668 SELECT test_auxdata('hello world') FROM t4;
673 SELECT test_auxdata('hello world', 123) FROM t4;
678 SELECT test_auxdata('hello world', a) FROM t4;
683 SELECT test_auxdata('hello'||'world', a) FROM t4;
687 # Test that auxilary data is preserved between calls for SQL variables.
689 set DB [sqlite3_connection_pointer db]
690 set sql "SELECT test_auxdata( ? , a ) FROM t4;"
691 set STMT [sqlite3_prepare $DB $sql -1 TAIL]
692 sqlite3_bind_text $STMT 1 hello\000 -1
694 while { "SQLITE_ROW"==[sqlite3_step $STMT] } {
695 lappend res [sqlite3_column_text $STMT 0]
697 lappend res [sqlite3_finalize $STMT]
698 } {{0 0} {1 0} SQLITE_OK}
700 # Test that auxiliary data is discarded when a statement is reset.
701 do_execsql_test 13.8.1 {
702 SELECT test_auxdata('constant') FROM t4;
704 do_execsql_test 13.8.2 {
705 SELECT test_auxdata('constant') FROM t4;
708 do_execsql_test 13.8.3 {
709 SELECT test_auxdata('constant') FROM t4;
712 do_execsql_test 13.8.4 {
713 SELECT test_auxdata($V), $V FROM t4;
716 do_execsql_test 13.8.5 {
717 SELECT test_auxdata($V), $V FROM t4;
721 do_execsql_test 13.8.6 {
722 SELECT test_auxdata($V), $V FROM t4;
726 # Make sure that a function with a very long name is rejected
729 db function [string repeat X 254] {return "hello"}
734 db function [string repeat X 256] {return "hello"}
739 catchsql {select test_error(NULL)}
742 catchsql {select test_error('this is the error message')}
743 } {1 {this is the error message}}
745 catchsql {select test_error('this is the error message',12)}
746 } {1 {this is the error message}}
751 # Test the quote function for BLOB and NULL values.
754 CREATE TABLE tbl2(a, b);
756 set STMT [sqlite3_prepare $::DB "INSERT INTO tbl2 VALUES(?, ?)" -1 TAIL]
757 sqlite3_bind_blob $::STMT 1 abc 3
759 sqlite3_finalize $::STMT
761 SELECT quote(a), quote(b) FROM tbl2;
765 # Correctly handle function error messages that include %. Ticket #1354
768 proc testfunc1 args {error "Error %d with %s percents %p"}
769 db function testfunc1 ::testfunc1
771 SELECT testfunc1(1,2,3);
773 } {1 {Error %d with %s percents %p}}
775 # The SUM function should return integer results when all inputs are integer.
780 INSERT INTO t5 VALUES(1);
781 INSERT INTO t5 VALUES(-99);
782 INSERT INTO t5 VALUES(10000);
783 SELECT sum(x) FROM t5;
786 ifcapable floatingpoint {
789 INSERT INTO t5 VALUES(0.0);
790 SELECT sum(x) FROM t5;
795 # The sum of nothing is NULL. But the sum of all NULLs is NULL.
797 # The TOTAL of nothing is 0.0.
802 SELECT sum(x), total(x) FROM t5;
807 INSERT INTO t5 VALUES(NULL);
808 SELECT sum(x), total(x) FROM t5
813 INSERT INTO t5 VALUES(NULL);
814 SELECT sum(x), total(x) FROM t5
819 INSERT INTO t5 VALUES(123);
820 SELECT sum(x), total(x) FROM t5
824 # Ticket #1664, #1669, #1670, #1674: An integer overflow on SUM causes
825 # an error. The non-standard TOTAL() function continues to give a helpful
830 CREATE TABLE t6(x INTEGER);
831 INSERT INTO t6 VALUES(1);
832 INSERT INTO t6 VALUES(1<<62);
833 SELECT sum(x) - ((1<<62)+1) from t6;
838 SELECT typeof(sum(x)) FROM t6
841 ifcapable floatingpoint {
844 INSERT INTO t6 VALUES(1<<62);
845 SELECT sum(x) - ((1<<62)*2.0+1) from t6;
847 } {1 {integer overflow}}
850 SELECT total(x) - ((1<<62)*2.0+1) FROM t6
854 ifcapable !floatingpoint {
857 INSERT INTO t6 VALUES(1<<62);
858 SELECT sum(x) - ((1<<62)*2+1) from t6;
860 } {1 {integer overflow}}
863 SELECT total(x) - ((1<<62)*2+1) FROM t6
867 if {[working_64bit_int]} {
870 SELECT sum(-9223372036854775805);
872 } -9223372036854775805
874 ifcapable compound&&subquery {
879 (SELECT 9223372036854775807 AS x UNION ALL
882 } {1 {integer overflow}}
883 if {[working_64bit_int]} {
887 (SELECT 9223372036854775807 AS x UNION ALL
890 } {0 9223372036854775797}
894 (SELECT -9223372036854775807 AS x UNION ALL
897 } {0 -9223372036854775797}
902 (SELECT -9223372036854775807 AS x UNION ALL
905 } {1 {integer overflow}}
908 SELECT sum(x) FROM (SELECT 9 AS x UNION ALL SELECT -10 AS x);
913 SELECT sum(x) FROM (SELECT -9 AS x UNION ALL SELECT 10 AS x);
918 SELECT sum(x) FROM (SELECT -10 AS x UNION ALL SELECT 9 AS x);
923 SELECT sum(x) FROM (SELECT 10 AS x UNION ALL SELECT -9 AS x);
927 } ;# ifcapable compound&&subquery
929 # Integer overflow on abs()
931 if {[working_64bit_int]} {
934 SELECT abs(-9223372036854775807);
936 } {0 9223372036854775807}
940 SELECT abs(-9223372036854775807-1);
942 } {1 {integer overflow}}
944 # The MATCH function exists but is only a stub and always throws an error.
948 SELECT match(a,b) FROM t1 WHERE 0;
953 SELECT 'abc' MATCH 'xyz';
955 } {1 {unable to use function MATCH in the requested context}}
958 SELECT 'abc' NOT MATCH 'xyz';
960 } {1 {unable to use function MATCH in the requested context}}
965 } {1 {wrong number of arguments to function match()}}
969 if {![catch {db eval {SELECT soundex('hello')}}]} {
991 execsql {SELECT soundex($name)}
996 # Tests of the REPLACE function.
1000 SELECT replace(1,2);
1002 } {1 {wrong number of arguments to function replace()}}
1005 SELECT replace(1,2,3,4);
1007 } {1 {wrong number of arguments to function replace()}}
1010 SELECT typeof(replace("This is the main test string", NULL, "ALT"));
1015 SELECT typeof(replace(NULL, "main", "ALT"));
1020 SELECT typeof(replace("This is the main test string", "main", NULL));
1025 SELECT replace("This is the main test string", "main", "ALT");
1027 } {{This is the ALT test string}}
1030 SELECT replace("This is the main test string", "main", "larger-main");
1032 } {{This is the larger-main test string}}
1035 SELECT replace("aaaaaaa", "a", "0123456789");
1037 } {0123456789012345678901234567890123456789012345678901234567890123456789}
1041 # Attempt to exploit a buffer-overflow that at one time existed
1042 # in the REPLACE function.
1043 set ::str "[string repeat A 29998]CC[string repeat A 35537]"
1044 set ::rep [string repeat B 65536]
1046 SELECT LENGTH(REPLACE($::str, 'C', $::rep));
1048 } [expr 29998 + 2*65536 + 35537]
1051 # Tests for the TRIM, LTRIM and RTRIM functions.
1054 catchsql {SELECT trim(1,2,3)}
1055 } {1 {wrong number of arguments to function trim()}}
1057 catchsql {SELECT ltrim(1,2,3)}
1058 } {1 {wrong number of arguments to function ltrim()}}
1060 catchsql {SELECT rtrim(1,2,3)}
1061 } {1 {wrong number of arguments to function rtrim()}}
1063 execsql {SELECT trim(' hi ');}
1066 execsql {SELECT ltrim(' hi ');}
1069 execsql {SELECT rtrim(' hi ');}
1072 execsql {SELECT trim(' hi ','xyz');}
1075 execsql {SELECT ltrim(' hi ','xyz');}
1078 execsql {SELECT rtrim(' hi ','xyz');}
1080 do_test func-22.10 {
1081 execsql {SELECT trim('xyxzy hi zzzy','xyz');}
1083 do_test func-22.11 {
1084 execsql {SELECT ltrim('xyxzy hi zzzy','xyz');}
1086 do_test func-22.12 {
1087 execsql {SELECT rtrim('xyxzy hi zzzy','xyz');}
1089 do_test func-22.13 {
1090 execsql {SELECT trim(' hi ','');}
1092 if {[db one {PRAGMA encoding}]=="UTF-8"} {
1093 do_test func-22.14 {
1094 execsql {SELECT hex(trim(x'c280e1bfbff48fbfbf6869',x'6162e1bfbfc280'))}
1096 do_test func-22.15 {
1097 execsql {SELECT hex(trim(x'6869c280e1bfbff48fbfbf61',
1098 x'6162e1bfbfc280f48fbfbf'))}
1100 do_test func-22.16 {
1101 execsql {SELECT hex(trim(x'ceb1ceb2ceb3',x'ceb1'));}
1104 do_test func-22.20 {
1105 execsql {SELECT typeof(trim(NULL));}
1107 do_test func-22.21 {
1108 execsql {SELECT typeof(trim(NULL,'xyz'));}
1110 do_test func-22.22 {
1111 execsql {SELECT typeof(trim('hello',NULL));}
1114 # 2021-06-15 - infinite loop due to unsigned character counter
1115 # overflow, reported by Zimuzo Ezeozue
1117 do_execsql_test func-22.23 {
1118 SELECT trim('xyzzy',x'c0808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080');
1121 # This is to test the deprecated sqlite3_aggregate_count() API.
1123 ifcapable deprecated {
1125 sqlite3_create_aggregate db
1127 SELECT legacy_count() FROM t6;
1132 # The group_concat() function.
1136 SELECT group_concat(t1) FROM tbl1
1138 } {this,program,is,free,software}
1141 SELECT group_concat(t1,' ') FROM tbl1
1143 } {{this program is free software}}
1146 SELECT group_concat(t1,' ' || rowid || ' ') FROM tbl1
1148 } {{this 2 program 3 is 4 free 5 software}}
1151 SELECT group_concat(NULL,t1) FROM tbl1
1156 SELECT group_concat(t1,NULL) FROM tbl1
1158 } {thisprogramisfreesoftware}
1161 SELECT 'BEGIN-'||group_concat(t1) FROM tbl1
1163 } {BEGIN-this,program,is,free,software}
1165 # Ticket #3179: Make sure aggregate functions can take many arguments.
1166 # None of the built-in aggregates do this, so use the md5sum() from the
1169 unset -nocomplain midargs
1171 unset -nocomplain midres
1173 unset -nocomplain result
1174 for {set i 1} {$i<[sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG -1]} {incr i} {
1175 append midargs ,'/$i'
1178 "this${midres}program${midres}is${midres}free${midres}software${midres}"]
1179 set sql "SELECT md5sum(t1$midargs) FROM tbl1"
1180 do_test func-24.7.$i {
1185 # Ticket #3806. If the initial string in a group_concat is an empty
1186 # string, the separator that follows should still be present.
1190 SELECT group_concat(CASE t1 WHEN 'this' THEN '' ELSE t1 END) FROM tbl1
1192 } {,program,is,free,software}
1195 SELECT group_concat(CASE WHEN t1!='software' THEN '' ELSE t1 END) FROM tbl1
1199 # Ticket #3923. Initial empty strings have a separator. But initial
1202 do_test func-24.10 {
1204 SELECT group_concat(CASE t1 WHEN 'this' THEN null ELSE t1 END) FROM tbl1
1206 } {program,is,free,software}
1207 do_test func-24.11 {
1209 SELECT group_concat(CASE WHEN t1!='software' THEN null ELSE t1 END) FROM tbl1
1212 do_test func-24.12 {
1214 SELECT group_concat(CASE t1 WHEN 'this' THEN ''
1215 WHEN 'program' THEN null ELSE t1 END) FROM tbl1
1217 } {,is,free,software}
1218 # Tests to verify ticket http://www.sqlite.org/src/tktview/55746f9e65f8587c0
1219 do_test func-24.13 {
1221 SELECT typeof(group_concat(x)) FROM (SELECT '' AS x);
1224 do_test func-24.14 {
1226 SELECT typeof(group_concat(x,''))
1227 FROM (SELECT '' AS x UNION ALL SELECT '');
1232 # Use the test_isolation function to make sure that type conversions
1233 # on function arguments do not effect subsequent arguments.
1236 execsql {SELECT test_isolation(t1,t1) FROM tbl1}
1237 } {this program is free software}
1239 # Try to misuse the sqlite3_create_function() interface. Verify that
1240 # errors are returned.
1243 abuse_create_function db
1246 # The previous test (func-26.1) registered a function with a very long
1247 # function name that takes many arguments and always returns NULL. Verify
1248 # that this function works correctly.
1252 for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG} {incr i} {
1256 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 ,]);
1261 for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG+1} {incr i} {
1265 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 ,]);
1267 } {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}}
1270 for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG-1} {incr i} {
1274 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 ,]);
1276 } {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()}}
1279 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);
1281 } {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}}
1284 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);
1286 } {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}}
1289 catchsql {SELECT coalesce()}
1290 } {1 {wrong number of arguments to function coalesce()}}
1292 catchsql {SELECT coalesce(1)}
1293 } {1 {wrong number of arguments to function coalesce()}}
1295 catchsql {SELECT coalesce(1,2)}
1298 # Ticket 2d401a94287b5
1299 # Unknown function in a DEFAULT expression causes a segfault.
1303 CREATE TABLE t28(x, y DEFAULT(nosuchfunc(1)));
1306 INSERT INTO t28(x) VALUES(1);
1308 } {1 {unknown function: nosuchfunc()}}
1310 # Verify that the length() and typeof() functions do not actually load
1311 # the content of their argument.
1315 CREATE TABLE t29(id INTEGER PRIMARY KEY, x, y);
1316 INSERT INTO t29 VALUES(1, 2, 3), (2, NULL, 4), (3, 4.5, 5);
1317 INSERT INTO t29 VALUES(4, randomblob(1000000), 6);
1318 INSERT INTO t29 VALUES(5, "hello", 7);
1322 sqlite3_db_status db CACHE_MISS 1
1323 db eval {SELECT typeof(x), length(x), typeof(y) FROM t29 ORDER BY id}
1324 } {integer 1 integer null {} integer real 3 integer blob 1000000 integer text 5 integer}
1326 set x [lindex [sqlite3_db_status db CACHE_MISS 1] 1]
1333 sqlite3_db_status db CACHE_MISS 1
1334 db eval {SELECT typeof(+x) FROM t29 ORDER BY id}
1335 } {integer null real blob text}
1336 if {[permutation] != "mmap"} {
1337 ifcapable !direct_read {
1339 set x [lindex [sqlite3_db_status db CACHE_MISS 1] 1]
1340 if {$x>100} {set x many}
1348 sqlite3_db_status db CACHE_MISS 1
1349 db eval {SELECT sum(length(x)) FROM t29}
1352 set x [lindex [sqlite3_db_status db CACHE_MISS 1] 1]
1357 # The OP_Column opcode has an optimization that avoids loading content
1358 # for fields with content-length=0 when the content offset is on an overflow
1359 # page. Make sure the optimization works.
1361 do_execsql_test func-29.10 {
1362 CREATE TABLE t29b(a,b,c,d,e,f,g,h,i);
1364 VALUES(1, hex(randomblob(2000)), null, 0, 1, '', zeroblob(0),'x',x'01');
1365 SELECT typeof(c), typeof(d), typeof(e), typeof(f),
1366 typeof(g), typeof(h), typeof(i) FROM t29b;
1367 } {null integer integer text blob text blob}
1368 do_execsql_test func-29.11 {
1369 SELECT length(f), length(g), length(h), length(i) FROM t29b;
1371 do_execsql_test func-29.12 {
1372 SELECT quote(f), quote(g), quote(h), quote(i) FROM t29b;
1373 } {'' X'' 'x' X'01'}
1375 # EVIDENCE-OF: R-29701-50711 The unicode(X) function returns the numeric
1376 # unicode code point corresponding to the first character of the string
1379 # EVIDENCE-OF: R-55469-62130 The char(X1,X2,...,XN) function returns a
1380 # string composed of characters having the unicode code point values of
1381 # integers X1 through XN, respectively.
1383 do_execsql_test func-30.1 {SELECT unicode('$');} 36
1384 do_execsql_test func-30.2 [subst {SELECT unicode('\u00A2');}] 162
1385 do_execsql_test func-30.3 [subst {SELECT unicode('\u20AC');}] 8364
1386 do_execsql_test func-30.4 {SELECT char(36,162,8364);} [subst {$\u00A2\u20AC}]
1388 for {set i 1} {$i<0xd800} {incr i 13} {
1389 do_execsql_test func-30.5.$i {SELECT unicode(char($i))} $i
1391 for {set i 57344} {$i<=0xfffd} {incr i 17} {
1392 if {$i==0xfeff} continue
1393 do_execsql_test func-30.5.$i {SELECT unicode(char($i))} $i
1395 for {set i 65536} {$i<=0x10ffff} {incr i 139} {
1396 do_execsql_test func-30.5.$i {SELECT unicode(char($i))} $i
1401 do_execsql_test func-31.1 {
1402 SELECT char(), length(char()), typeof(char())
1405 # sqlite3_value_frombind()
1407 do_execsql_test func-32.100 {
1408 SELECT test_frombind(1,2,3,4);
1410 do_execsql_test func-32.110 {
1411 SELECT test_frombind(1,2,?,4);
1413 do_execsql_test func-32.120 {
1414 SELECT test_frombind(1,(?),4,?+7);
1416 do_execsql_test func-32.130 {
1417 DROP TABLE IF EXISTS t1;
1418 CREATE TABLE t1(a,b,c,e,f);
1419 INSERT INTO t1 VALUES(1,2.5,'xyz',x'e0c1b2a3',null);
1420 SELECT test_frombind(a,b,c,e,f,$xyz) FROM t1;
1422 do_execsql_test func-32.140 {
1423 SELECT test_frombind(a,b,c,e,f,$xyz+f) FROM t1;
1425 do_execsql_test func-32.150 {
1426 SELECT test_frombind(x.a,y.b,x.c,:123,y.e,x.f,$xyz+y.f) FROM t1 x, t1 y;
1430 # Direct-only functions.
1432 proc testdirectonly {x} {return [expr {$x*2}]}
1434 db func testdirectonly -directonly testdirectonly
1435 db eval {SELECT testdirectonly(15)}
1437 do_catchsql_test func-33.2 {
1438 CREATE VIEW v33(y) AS SELECT testdirectonly(15);
1440 } {1 {unsafe use of testdirectonly()}}
1441 do_execsql_test func-33.3 {
1442 SELECT * FROM (SELECT testdirectonly(15)) AS v33;
1444 do_execsql_test func-33.4 {
1445 WITH c(x) AS (SELECT testdirectonly(15))
1448 do_catchsql_test func-33.5 {
1449 WITH c(x) AS (SELECT * FROM v33)
1451 } {1 {unsafe use of testdirectonly()}}
1452 do_execsql_test func-33.10 {
1453 CREATE TABLE t33a(a,b);
1454 CREATE TABLE t33b(x,y);
1455 CREATE TRIGGER r1 AFTER INSERT ON t33a BEGIN
1456 INSERT INTO t33b(x,y) VALUES(testdirectonly(new.a),new.b);
1459 do_catchsql_test func-33.11 {
1460 INSERT INTO t33a VALUES(1,2);
1461 } {1 {unsafe use of testdirectonly()}}
1463 ifcapable altertable {
1464 do_execsql_test func-33.20 {
1465 ALTER TABLE t33a RENAME COLUMN a TO aaa;
1466 SELECT sql FROM sqlite_master WHERE name='r1';
1467 } {{CREATE TRIGGER r1 AFTER INSERT ON t33a BEGIN
1468 INSERT INTO t33b(x,y) VALUES(testdirectonly(new.aaa),new.b);
1472 # 2020-01-09 Yongheng fuzzer find
1473 # The bug is in the register-validity debug logic, not in the SQLite core
1474 # and as such it only impacts debug builds. Release builds work fine.
1477 do_execsql_test func-34.10 {
1478 CREATE TABLE t1(a INT CHECK(
1479 datetime( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9,
1480 10,11,12,13,14,15,16,17,18,19,
1481 20,21,22,23,24,25,26,27,28,29,
1482 30,31,32,33,34,35,36,37,38,39,
1483 40,41,42,43,44,45,46,47,48,a)
1486 INSERT INTO t1(a) VALUES(1),(2);
1490 # 2020-03-11 COALESCE() should short-circuit
1491 # See also ticket 3c9eadd2a6ba0aa5
1492 # Both issues stem from the fact that functions that could
1493 # throw exceptions were being factored out into initialization
1494 # code. The fix was to put those function calls inside of
1498 do_execsql_test func-35.100 {
1500 SELECT coalesce(x, abs(-9223372036854775808)) FROM t1;
1502 do_execsql_test func-35.110 {
1503 SELECT coalesce(x, 'xyz' LIKE printf('%.1000000c','y')) FROM t1;
1505 do_execsql_test func-35.200 {
1506 CREATE TABLE t0(c0 CHECK(ABS(-9223372036854775808)));
1507 PRAGMA integrity_check;