000001 # 2001 September 15 000002 # 000003 # The author disclaims copyright to this source code. In place of 000004 # a legal notice, here is a blessing: 000005 # 000006 # May you do good and not evil. 000007 # May you find forgiveness for yourself and forgive others. 000008 # May you share freely, never taking more than you give. 000009 # 000010 #*********************************************************************** 000011 # This file implements regression tests for SQLite library. The 000012 # focus of this file is testing built-in functions. 000013 # 000014 000015 set testdir [file dirname $argv0] 000016 source $testdir/tester.tcl 000017 set testprefix func 000018 000019 # Create a table to work with. 000020 # 000021 do_test func-0.0 { 000022 execsql {CREATE TABLE tbl1(t1 text)} 000023 foreach word {this program is free software} { 000024 execsql "INSERT INTO tbl1 VALUES('$word')" 000025 } 000026 execsql {SELECT t1 FROM tbl1 ORDER BY t1} 000027 } {free is program software this} 000028 do_test func-0.1 { 000029 execsql { 000030 CREATE TABLE t2(a); 000031 INSERT INTO t2 VALUES(1); 000032 INSERT INTO t2 VALUES(NULL); 000033 INSERT INTO t2 VALUES(345); 000034 INSERT INTO t2 VALUES(NULL); 000035 INSERT INTO t2 VALUES(67890); 000036 SELECT * FROM t2; 000037 } 000038 } {1 {} 345 {} 67890} 000039 000040 # Check out the length() function 000041 # 000042 do_test func-1.0 { 000043 execsql {SELECT length(t1) FROM tbl1 ORDER BY t1} 000044 } {4 2 7 8 4} 000045 do_test func-1.1 { 000046 set r [catch {execsql {SELECT length(*) FROM tbl1 ORDER BY t1}} msg] 000047 lappend r $msg 000048 } {1 {wrong number of arguments to function length()}} 000049 do_test func-1.2 { 000050 set r [catch {execsql {SELECT length(t1,5) FROM tbl1 ORDER BY t1}} msg] 000051 lappend r $msg 000052 } {1 {wrong number of arguments to function length()}} 000053 do_test func-1.3 { 000054 execsql {SELECT length(t1), count(*) FROM tbl1 GROUP BY length(t1) 000055 ORDER BY length(t1)} 000056 } {2 1 4 2 7 1 8 1} 000057 do_test func-1.4 { 000058 execsql {SELECT coalesce(length(a),-1) FROM t2} 000059 } {1 -1 3 -1 5} 000060 000061 # Check out the substr() function 000062 # 000063 do_test func-2.0 { 000064 execsql {SELECT substr(t1,1,2) FROM tbl1 ORDER BY t1} 000065 } {fr is pr so th} 000066 do_test func-2.1 { 000067 execsql {SELECT substr(t1,2,1) FROM tbl1 ORDER BY t1} 000068 } {r s r o h} 000069 do_test func-2.2 { 000070 execsql {SELECT substr(t1,3,3) FROM tbl1 ORDER BY t1} 000071 } {ee {} ogr ftw is} 000072 do_test func-2.3 { 000073 execsql {SELECT substr(t1,-1,1) FROM tbl1 ORDER BY t1} 000074 } {e s m e s} 000075 do_test func-2.4 { 000076 execsql {SELECT substr(t1,-1,2) FROM tbl1 ORDER BY t1} 000077 } {e s m e s} 000078 do_test func-2.5 { 000079 execsql {SELECT substr(t1,-2,1) FROM tbl1 ORDER BY t1} 000080 } {e i a r i} 000081 do_test func-2.6 { 000082 execsql {SELECT substr(t1,-2,2) FROM tbl1 ORDER BY t1} 000083 } {ee is am re is} 000084 do_test func-2.7 { 000085 execsql {SELECT substr(t1,-4,2) FROM tbl1 ORDER BY t1} 000086 } {fr {} gr wa th} 000087 do_test func-2.8 { 000088 execsql {SELECT t1 FROM tbl1 ORDER BY substr(t1,2,20)} 000089 } {this software free program is} 000090 do_test func-2.9 { 000091 execsql {SELECT substr(a,1,1) FROM t2} 000092 } {1 {} 3 {} 6} 000093 do_test func-2.10 { 000094 execsql {SELECT substr(a,2,2) FROM t2} 000095 } {{} {} 45 {} 78} 000096 000097 # Only do the following tests if TCL has UTF-8 capabilities 000098 # 000099 if {"\u1234"!="u1234"} { 000100 000101 # Put some UTF-8 characters in the database 000102 # 000103 do_test func-3.0 { 000104 execsql {DELETE FROM tbl1} 000105 foreach word "contains UTF-8 characters hi\u1234ho" { 000106 execsql "INSERT INTO tbl1 VALUES('$word')" 000107 } 000108 execsql {SELECT t1 FROM tbl1 ORDER BY t1} 000109 } "UTF-8 characters contains hi\u1234ho" 000110 do_test func-3.1 { 000111 execsql {SELECT length(t1) FROM tbl1 ORDER BY t1} 000112 } {5 10 8 5} 000113 do_test func-3.2 { 000114 execsql {SELECT substr(t1,1,2) FROM tbl1 ORDER BY t1} 000115 } {UT ch co hi} 000116 do_test func-3.3 { 000117 execsql {SELECT substr(t1,1,3) FROM tbl1 ORDER BY t1} 000118 } "UTF cha con hi\u1234" 000119 do_test func-3.4 { 000120 execsql {SELECT substr(t1,2,2) FROM tbl1 ORDER BY t1} 000121 } "TF ha on i\u1234" 000122 do_test func-3.5 { 000123 execsql {SELECT substr(t1,2,3) FROM tbl1 ORDER BY t1} 000124 } "TF- har ont i\u1234h" 000125 do_test func-3.6 { 000126 execsql {SELECT substr(t1,3,2) FROM tbl1 ORDER BY t1} 000127 } "F- ar nt \u1234h" 000128 do_test func-3.7 { 000129 execsql {SELECT substr(t1,4,2) FROM tbl1 ORDER BY t1} 000130 } "-8 ra ta ho" 000131 do_test func-3.8 { 000132 execsql {SELECT substr(t1,-1,1) FROM tbl1 ORDER BY t1} 000133 } "8 s s o" 000134 do_test func-3.9 { 000135 execsql {SELECT substr(t1,-3,2) FROM tbl1 ORDER BY t1} 000136 } "F- er in \u1234h" 000137 do_test func-3.10 { 000138 execsql {SELECT substr(t1,-4,3) FROM tbl1 ORDER BY t1} 000139 } "TF- ter ain i\u1234h" 000140 do_test func-3.99 { 000141 execsql {DELETE FROM tbl1} 000142 foreach word {this program is free software} { 000143 execsql "INSERT INTO tbl1 VALUES('$word')" 000144 } 000145 execsql {SELECT t1 FROM tbl1} 000146 } {this program is free software} 000147 000148 } ;# End \u1234!=u1234 000149 000150 # Test the abs() and round() functions. 000151 # 000152 ifcapable !floatingpoint { 000153 do_test func-4.1 { 000154 execsql { 000155 CREATE TABLE t1(a,b,c); 000156 INSERT INTO t1 VALUES(1,2,3); 000157 INSERT INTO t1 VALUES(2,12345678901234,-1234567890); 000158 INSERT INTO t1 VALUES(3,-2,-5); 000159 } 000160 catchsql {SELECT abs(a,b) FROM t1} 000161 } {1 {wrong number of arguments to function abs()}} 000162 } 000163 ifcapable floatingpoint { 000164 do_test func-4.1 { 000165 execsql { 000166 CREATE TABLE t1(a,b,c); 000167 INSERT INTO t1 VALUES(1,2,3); 000168 INSERT INTO t1 VALUES(2,1.2345678901234,-12345.67890); 000169 INSERT INTO t1 VALUES(3,-2,-5); 000170 } 000171 catchsql {SELECT abs(a,b) FROM t1} 000172 } {1 {wrong number of arguments to function abs()}} 000173 } 000174 do_test func-4.2 { 000175 catchsql {SELECT abs() FROM t1} 000176 } {1 {wrong number of arguments to function abs()}} 000177 ifcapable floatingpoint { 000178 do_test func-4.3 { 000179 catchsql {SELECT abs(b) FROM t1 ORDER BY a} 000180 } {0 {2 1.2345678901234 2}} 000181 do_test func-4.4 { 000182 catchsql {SELECT abs(c) FROM t1 ORDER BY a} 000183 } {0 {3 12345.6789 5}} 000184 } 000185 ifcapable !floatingpoint { 000186 if {[working_64bit_int]} { 000187 do_test func-4.3 { 000188 catchsql {SELECT abs(b) FROM t1 ORDER BY a} 000189 } {0 {2 12345678901234 2}} 000190 } 000191 do_test func-4.4 { 000192 catchsql {SELECT abs(c) FROM t1 ORDER BY a} 000193 } {0 {3 1234567890 5}} 000194 } 000195 do_test func-4.4.1 { 000196 execsql {SELECT abs(a) FROM t2} 000197 } {1 {} 345 {} 67890} 000198 do_test func-4.4.2 { 000199 execsql {SELECT abs(t1) FROM tbl1} 000200 } {0.0 0.0 0.0 0.0 0.0} 000201 000202 ifcapable floatingpoint { 000203 do_test func-4.5 { 000204 catchsql {SELECT round(a,b,c) FROM t1} 000205 } {1 {wrong number of arguments to function round()}} 000206 do_test func-4.6 { 000207 catchsql {SELECT round(b,2) FROM t1 ORDER BY b} 000208 } {0 {-2.0 1.23 2.0}} 000209 do_test func-4.7 { 000210 catchsql {SELECT round(b,0) FROM t1 ORDER BY a} 000211 } {0 {2.0 1.0 -2.0}} 000212 do_test func-4.8 { 000213 catchsql {SELECT round(c) FROM t1 ORDER BY a} 000214 } {0 {3.0 -12346.0 -5.0}} 000215 do_test func-4.9 { 000216 catchsql {SELECT round(c,a) FROM t1 ORDER BY a} 000217 } {0 {3.0 -12345.68 -5.0}} 000218 do_test func-4.10 { 000219 catchsql {SELECT 'x' || round(c,a) || 'y' FROM t1 ORDER BY a} 000220 } {0 {x3.0y x-12345.68y x-5.0y}} 000221 do_test func-4.11 { 000222 catchsql {SELECT round() FROM t1 ORDER BY a} 000223 } {1 {wrong number of arguments to function round()}} 000224 do_test func-4.12 { 000225 execsql {SELECT coalesce(round(a,2),'nil') FROM t2} 000226 } {1.0 nil 345.0 nil 67890.0} 000227 do_test func-4.13 { 000228 execsql {SELECT round(t1,2) FROM tbl1} 000229 } {0.0 0.0 0.0 0.0 0.0} 000230 do_test func-4.14 { 000231 execsql {SELECT typeof(round(5.1,1));} 000232 } {real} 000233 do_test func-4.15 { 000234 execsql {SELECT typeof(round(5.1));} 000235 } {real} 000236 do_test func-4.16 { 000237 catchsql {SELECT round(b,2.0) FROM t1 ORDER BY b} 000238 } {0 {-2.0 1.23 2.0}} 000239 # Verify some values reported on the mailing list. 000240 # Some of these fail on MSVC builds with 64-bit 000241 # long doubles, but not on GCC builds with 80-bit 000242 # long doubles. 000243 for {set i 1} {$i<999} {incr i} { 000244 set x1 [expr 40222.5 + $i] 000245 set x2 [expr 40223.0 + $i] 000246 do_test func-4.17.$i { 000247 execsql {SELECT round($x1);} 000248 } $x2 000249 } 000250 for {set i 1} {$i<999} {incr i} { 000251 set x1 [expr 40222.05 + $i] 000252 set x2 [expr 40222.10 + $i] 000253 do_test func-4.18.$i { 000254 execsql {SELECT round($x1,1);} 000255 } $x2 000256 } 000257 do_test func-4.20 { 000258 execsql {SELECT round(40223.4999999999);} 000259 } {40223.0} 000260 do_test func-4.21 { 000261 execsql {SELECT round(40224.4999999999);} 000262 } {40224.0} 000263 do_test func-4.22 { 000264 execsql {SELECT round(40225.4999999999);} 000265 } {40225.0} 000266 for {set i 1} {$i<10} {incr i} { 000267 do_test func-4.23.$i { 000268 execsql {SELECT round(40223.4999999999,$i);} 000269 } {40223.5} 000270 do_test func-4.24.$i { 000271 execsql {SELECT round(40224.4999999999,$i);} 000272 } {40224.5} 000273 do_test func-4.25.$i { 000274 execsql {SELECT round(40225.4999999999,$i);} 000275 } {40225.5} 000276 } 000277 for {set i 10} {$i<32} {incr i} { 000278 do_test func-4.26.$i { 000279 execsql {SELECT round(40223.4999999999,$i);} 000280 } {40223.4999999999} 000281 do_test func-4.27.$i { 000282 execsql {SELECT round(40224.4999999999,$i);} 000283 } {40224.4999999999} 000284 do_test func-4.28.$i { 000285 execsql {SELECT round(40225.4999999999,$i);} 000286 } {40225.4999999999} 000287 } 000288 do_test func-4.29 { 000289 execsql {SELECT round(1234567890.5);} 000290 } {1234567891.0} 000291 do_test func-4.30 { 000292 execsql {SELECT round(12345678901.5);} 000293 } {12345678902.0} 000294 do_test func-4.31 { 000295 execsql {SELECT round(123456789012.5);} 000296 } {123456789013.0} 000297 do_test func-4.32 { 000298 execsql {SELECT round(1234567890123.5);} 000299 } {1234567890124.0} 000300 do_test func-4.33 { 000301 execsql {SELECT round(12345678901234.5);} 000302 } {12345678901235.0} 000303 do_test func-4.34 { 000304 execsql {SELECT round(1234567890123.35,1);} 000305 } {1234567890123.4} 000306 do_test func-4.35 { 000307 execsql {SELECT round(1234567890123.445,2);} 000308 } {1234567890123.45} 000309 do_test func-4.36 { 000310 execsql {SELECT round(99999999999994.5);} 000311 } {99999999999995.0} 000312 do_test func-4.37 { 000313 execsql {SELECT round(9999999999999.55,1);} 000314 } {9999999999999.6} 000315 do_test func-4.38 { 000316 execsql {SELECT round(9999999999999.556,2);} 000317 } {9999999999999.56} 000318 do_test func-4.39 { 000319 string tolower [db eval {SELECT round(1e500), round(-1e500);}] 000320 } {inf -inf} 000321 } 000322 000323 # Test the upper() and lower() functions 000324 # 000325 do_test func-5.1 { 000326 execsql {SELECT upper(t1) FROM tbl1} 000327 } {THIS PROGRAM IS FREE SOFTWARE} 000328 do_test func-5.2 { 000329 execsql {SELECT lower(upper(t1)) FROM tbl1} 000330 } {this program is free software} 000331 do_test func-5.3 { 000332 execsql {SELECT upper(a), lower(a) FROM t2} 000333 } {1 1 {} {} 345 345 {} {} 67890 67890} 000334 ifcapable !icu { 000335 do_test func-5.4 { 000336 catchsql {SELECT upper(a,5) FROM t2} 000337 } {1 {wrong number of arguments to function upper()}} 000338 } 000339 do_test func-5.5 { 000340 catchsql {SELECT upper(*) FROM t2} 000341 } {1 {wrong number of arguments to function upper()}} 000342 000343 # Test the coalesce() and nullif() functions 000344 # 000345 do_test func-6.1 { 000346 execsql {SELECT coalesce(a,'xyz') FROM t2} 000347 } {1 xyz 345 xyz 67890} 000348 do_test func-6.2 { 000349 execsql {SELECT coalesce(upper(a),'nil') FROM t2} 000350 } {1 nil 345 nil 67890} 000351 do_test func-6.3 { 000352 execsql {SELECT coalesce(nullif(1,1),'nil')} 000353 } {nil} 000354 do_test func-6.4 { 000355 execsql {SELECT coalesce(nullif(1,2),'nil')} 000356 } {1} 000357 do_test func-6.5 { 000358 execsql {SELECT coalesce(nullif(1,NULL),'nil')} 000359 } {1} 000360 000361 000362 # Test the last_insert_rowid() function 000363 # 000364 do_test func-7.1 { 000365 execsql {SELECT last_insert_rowid()} 000366 } [db last_insert_rowid] 000367 000368 # Tests for aggregate functions and how they handle NULLs. 000369 # 000370 ifcapable floatingpoint { 000371 do_test func-8.1 { 000372 ifcapable explain { 000373 execsql {EXPLAIN SELECT sum(a) FROM t2;} 000374 } 000375 execsql { 000376 SELECT sum(a), count(a), round(avg(a),2), min(a), max(a), count(*) FROM t2; 000377 } 000378 } {68236 3 22745.33 1 67890 5} 000379 } 000380 ifcapable !floatingpoint { 000381 do_test func-8.1 { 000382 ifcapable explain { 000383 execsql {EXPLAIN SELECT sum(a) FROM t2;} 000384 } 000385 execsql { 000386 SELECT sum(a), count(a), avg(a), min(a), max(a), count(*) FROM t2; 000387 } 000388 } {68236 3 22745.0 1 67890 5} 000389 } 000390 do_test func-8.2 { 000391 execsql { 000392 SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t2; 000393 } 000394 } {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP} 000395 000396 ifcapable tempdb { 000397 do_test func-8.3 { 000398 execsql { 000399 CREATE TEMP TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC; 000400 SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3; 000401 } 000402 } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP} 000403 } else { 000404 do_test func-8.3 { 000405 execsql { 000406 CREATE TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC; 000407 SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3; 000408 } 000409 } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP} 000410 } 000411 do_test func-8.4 { 000412 execsql { 000413 SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3; 000414 } 000415 } {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP} 000416 ifcapable compound { 000417 do_test func-8.5 { 000418 execsql { 000419 SELECT sum(x) FROM (SELECT '9223372036' || '854775807' AS x 000420 UNION ALL SELECT -9223372036854775807) 000421 } 000422 } {0} 000423 do_test func-8.6 { 000424 execsql { 000425 SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775807' AS x 000426 UNION ALL SELECT -9223372036854775807) 000427 } 000428 } {integer} 000429 do_test func-8.7 { 000430 execsql { 000431 SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775808' AS x 000432 UNION ALL SELECT -9223372036854775807) 000433 } 000434 } {real} 000435 ifcapable floatingpoint { 000436 do_test func-8.8 { 000437 execsql { 000438 SELECT sum(x)>0.0 FROM (SELECT '9223372036' || '854775808' AS x 000439 UNION ALL SELECT -9223372036850000000) 000440 } 000441 } {1} 000442 } 000443 ifcapable !floatingpoint { 000444 do_test func-8.8 { 000445 execsql { 000446 SELECT sum(x)>0 FROM (SELECT '9223372036' || '854775808' AS x 000447 UNION ALL SELECT -9223372036850000000) 000448 } 000449 } {1} 000450 } 000451 } 000452 000453 # How do you test the random() function in a meaningful, deterministic way? 000454 # 000455 do_test func-9.1 { 000456 execsql { 000457 SELECT random() is not null; 000458 } 000459 } {1} 000460 do_test func-9.2 { 000461 execsql { 000462 SELECT typeof(random()); 000463 } 000464 } {integer} 000465 do_test func-9.3 { 000466 execsql { 000467 SELECT randomblob(32) is not null; 000468 } 000469 } {1} 000470 do_test func-9.4 { 000471 execsql { 000472 SELECT typeof(randomblob(32)); 000473 } 000474 } {blob} 000475 do_test func-9.5 { 000476 execsql { 000477 SELECT length(randomblob(32)), length(randomblob(-5)), 000478 length(randomblob(2000)) 000479 } 000480 } {32 1 2000} 000481 000482 # The "hex()" function was added in order to be able to render blobs 000483 # generated by randomblob(). So this seems like a good place to test 000484 # hex(). 000485 # 000486 ifcapable bloblit { 000487 do_test func-9.10 { 000488 execsql {SELECT hex(x'00112233445566778899aAbBcCdDeEfF')} 000489 } {00112233445566778899AABBCCDDEEFF} 000490 } 000491 set encoding [db one {PRAGMA encoding}] 000492 if {$encoding=="UTF-16le"} { 000493 do_test func-9.11-utf16le { 000494 execsql {SELECT hex(replace('abcdefg','ef','12'))} 000495 } {6100620063006400310032006700} 000496 do_test func-9.12-utf16le { 000497 execsql {SELECT hex(replace('abcdefg','','12'))} 000498 } {6100620063006400650066006700} 000499 do_test func-9.13-utf16le { 000500 execsql {SELECT hex(replace('aabcdefg','a','aaa'))} 000501 } {610061006100610061006100620063006400650066006700} 000502 } elseif {$encoding=="UTF-8"} { 000503 do_test func-9.11-utf8 { 000504 execsql {SELECT hex(replace('abcdefg','ef','12'))} 000505 } {61626364313267} 000506 do_test func-9.12-utf8 { 000507 execsql {SELECT hex(replace('abcdefg','','12'))} 000508 } {61626364656667} 000509 do_test func-9.13-utf8 { 000510 execsql {SELECT hex(replace('aabcdefg','a','aaa'))} 000511 } {616161616161626364656667} 000512 } 000513 do_execsql_test func-9.14 { 000514 WITH RECURSIVE c(x) AS ( 000515 VALUES(1) 000516 UNION ALL 000517 SELECT x+1 FROM c WHERE x<1040 000518 ) 000519 SELECT 000520 count(*), 000521 sum(length(replace(printf('abc%.*cxyz',x,'m'),'m','nnnn'))-(6+x*4)) 000522 FROM c; 000523 } {1040 0} 000524 000525 # Use the "sqlite_register_test_function" TCL command which is part of 000526 # the text fixture in order to verify correct operation of some of 000527 # the user-defined SQL function APIs that are not used by the built-in 000528 # functions. 000529 # 000530 set ::DB [sqlite3_connection_pointer db] 000531 sqlite_register_test_function $::DB testfunc 000532 do_test func-10.1 { 000533 catchsql { 000534 SELECT testfunc(NULL,NULL); 000535 } 000536 } {1 {first argument should be one of: int int64 string double null value}} 000537 do_test func-10.2 { 000538 execsql { 000539 SELECT testfunc( 000540 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 000541 'int', 1234 000542 ); 000543 } 000544 } {1234} 000545 do_test func-10.3 { 000546 execsql { 000547 SELECT testfunc( 000548 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 000549 'string', NULL 000550 ); 000551 } 000552 } {{}} 000553 000554 ifcapable floatingpoint { 000555 do_test func-10.4 { 000556 execsql { 000557 SELECT testfunc( 000558 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 000559 'double', 1.234 000560 ); 000561 } 000562 } {1.234} 000563 do_test func-10.5 { 000564 execsql { 000565 SELECT testfunc( 000566 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 000567 'int', 1234, 000568 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 000569 'string', NULL, 000570 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 000571 'double', 1.234, 000572 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 000573 'int', 1234, 000574 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 000575 'string', NULL, 000576 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 000577 'double', 1.234 000578 ); 000579 } 000580 } {1.234} 000581 } 000582 000583 # Test the built-in sqlite_version(*) SQL function. 000584 # 000585 do_test func-11.1 { 000586 execsql { 000587 SELECT sqlite_version(*); 000588 } 000589 } [sqlite3 -version] 000590 000591 # Test that destructors passed to sqlite3 by calls to sqlite3_result_text() 000592 # etc. are called. These tests use two special user-defined functions 000593 # (implemented in func.c) only available in test builds. 000594 # 000595 # Function test_destructor() takes one argument and returns a copy of the 000596 # text form of that argument. A destructor is associated with the return 000597 # value. Function test_destructor_count() returns the number of outstanding 000598 # destructor calls for values returned by test_destructor(). 000599 # 000600 if {[db eval {PRAGMA encoding}]=="UTF-8"} { 000601 do_test func-12.1-utf8 { 000602 execsql { 000603 SELECT test_destructor('hello world'), test_destructor_count(); 000604 } 000605 } {{hello world} 1} 000606 } else { 000607 ifcapable {utf16} { 000608 do_test func-12.1-utf16 { 000609 execsql { 000610 SELECT test_destructor16('hello world'), test_destructor_count(); 000611 } 000612 } {{hello world} 1} 000613 } 000614 } 000615 do_test func-12.2 { 000616 execsql { 000617 SELECT test_destructor_count(); 000618 } 000619 } {0} 000620 do_test func-12.3 { 000621 execsql { 000622 SELECT test_destructor('hello')||' world' 000623 } 000624 } {{hello world}} 000625 do_test func-12.4 { 000626 execsql { 000627 SELECT test_destructor_count(); 000628 } 000629 } {0} 000630 do_test func-12.5 { 000631 execsql { 000632 CREATE TABLE t4(x); 000633 INSERT INTO t4 VALUES(test_destructor('hello')); 000634 INSERT INTO t4 VALUES(test_destructor('world')); 000635 SELECT min(test_destructor(x)), max(test_destructor(x)) FROM t4; 000636 } 000637 } {hello world} 000638 do_test func-12.6 { 000639 execsql { 000640 SELECT test_destructor_count(); 000641 } 000642 } {0} 000643 do_test func-12.7 { 000644 execsql { 000645 DROP TABLE t4; 000646 } 000647 } {} 000648 000649 000650 # Test that the auxdata API for scalar functions works. This test uses 000651 # a special user-defined function only available in test builds, 000652 # test_auxdata(). Function test_auxdata() takes any number of arguments. 000653 do_test func-13.1 { 000654 execsql { 000655 SELECT test_auxdata('hello world'); 000656 } 000657 } {0} 000658 000659 do_test func-13.2 { 000660 execsql { 000661 CREATE TABLE t4(a, b); 000662 INSERT INTO t4 VALUES('abc', 'def'); 000663 INSERT INTO t4 VALUES('ghi', 'jkl'); 000664 } 000665 } {} 000666 do_test func-13.3 { 000667 execsql { 000668 SELECT test_auxdata('hello world') FROM t4; 000669 } 000670 } {0 1} 000671 do_test func-13.4 { 000672 execsql { 000673 SELECT test_auxdata('hello world', 123) FROM t4; 000674 } 000675 } {{0 0} {1 1}} 000676 do_test func-13.5 { 000677 execsql { 000678 SELECT test_auxdata('hello world', a) FROM t4; 000679 } 000680 } {{0 0} {1 0}} 000681 do_test func-13.6 { 000682 execsql { 000683 SELECT test_auxdata('hello'||'world', a) FROM t4; 000684 } 000685 } {{0 0} {1 0}} 000686 000687 # Test that auxilary data is preserved between calls for SQL variables. 000688 do_test func-13.7 { 000689 set DB [sqlite3_connection_pointer db] 000690 set sql "SELECT test_auxdata( ? , a ) FROM t4;" 000691 set STMT [sqlite3_prepare $DB $sql -1 TAIL] 000692 sqlite3_bind_text $STMT 1 hello\000 -1 000693 set res [list] 000694 while { "SQLITE_ROW"==[sqlite3_step $STMT] } { 000695 lappend res [sqlite3_column_text $STMT 0] 000696 } 000697 lappend res [sqlite3_finalize $STMT] 000698 } {{0 0} {1 0} SQLITE_OK} 000699 000700 # Test that auxiliary data is discarded when a statement is reset. 000701 do_execsql_test 13.8.1 { 000702 SELECT test_auxdata('constant') FROM t4; 000703 } {0 1} 000704 do_execsql_test 13.8.2 { 000705 SELECT test_auxdata('constant') FROM t4; 000706 } {0 1} 000707 db cache flush 000708 do_execsql_test 13.8.3 { 000709 SELECT test_auxdata('constant') FROM t4; 000710 } {0 1} 000711 set V "one" 000712 do_execsql_test 13.8.4 { 000713 SELECT test_auxdata($V), $V FROM t4; 000714 } {0 one 1 one} 000715 set V "two" 000716 do_execsql_test 13.8.5 { 000717 SELECT test_auxdata($V), $V FROM t4; 000718 } {0 two 1 two} 000719 db cache flush 000720 set V "three" 000721 do_execsql_test 13.8.6 { 000722 SELECT test_auxdata($V), $V FROM t4; 000723 } {0 three 1 three} 000724 000725 000726 # Make sure that a function with a very long name is rejected 000727 do_test func-14.1 { 000728 catch { 000729 db function [string repeat X 254] {return "hello"} 000730 } 000731 } {0} 000732 do_test func-14.2 { 000733 catch { 000734 db function [string repeat X 256] {return "hello"} 000735 } 000736 } {1} 000737 000738 do_test func-15.1 { 000739 catchsql {select test_error(NULL)} 000740 } {1 {}} 000741 do_test func-15.2 { 000742 catchsql {select test_error('this is the error message')} 000743 } {1 {this is the error message}} 000744 do_test func-15.3 { 000745 catchsql {select test_error('this is the error message',12)} 000746 } {1 {this is the error message}} 000747 do_test func-15.4 { 000748 db errorcode 000749 } {12} 000750 000751 # Test the quote function for BLOB and NULL values. 000752 do_test func-16.1 { 000753 execsql { 000754 CREATE TABLE tbl2(a, b); 000755 } 000756 set STMT [sqlite3_prepare $::DB "INSERT INTO tbl2 VALUES(?, ?)" -1 TAIL] 000757 sqlite3_bind_blob $::STMT 1 abc 3 000758 sqlite3_step $::STMT 000759 sqlite3_finalize $::STMT 000760 execsql { 000761 SELECT quote(a), quote(b) FROM tbl2; 000762 } 000763 } {X'616263' NULL} 000764 000765 # Correctly handle function error messages that include %. Ticket #1354 000766 # 000767 do_test func-17.1 { 000768 proc testfunc1 args {error "Error %d with %s percents %p"} 000769 db function testfunc1 ::testfunc1 000770 catchsql { 000771 SELECT testfunc1(1,2,3); 000772 } 000773 } {1 {Error %d with %s percents %p}} 000774 000775 # The SUM function should return integer results when all inputs are integer. 000776 # 000777 do_test func-18.1 { 000778 execsql { 000779 CREATE TABLE t5(x); 000780 INSERT INTO t5 VALUES(1); 000781 INSERT INTO t5 VALUES(-99); 000782 INSERT INTO t5 VALUES(10000); 000783 SELECT sum(x) FROM t5; 000784 } 000785 } {9902} 000786 ifcapable floatingpoint { 000787 do_test func-18.2 { 000788 execsql { 000789 INSERT INTO t5 VALUES(0.0); 000790 SELECT sum(x) FROM t5; 000791 } 000792 } {9902.0} 000793 } 000794 000795 # The sum of nothing is NULL. But the sum of all NULLs is NULL. 000796 # 000797 # The TOTAL of nothing is 0.0. 000798 # 000799 do_test func-18.3 { 000800 execsql { 000801 DELETE FROM t5; 000802 SELECT sum(x), total(x) FROM t5; 000803 } 000804 } {{} 0.0} 000805 do_test func-18.4 { 000806 execsql { 000807 INSERT INTO t5 VALUES(NULL); 000808 SELECT sum(x), total(x) FROM t5 000809 } 000810 } {{} 0.0} 000811 do_test func-18.5 { 000812 execsql { 000813 INSERT INTO t5 VALUES(NULL); 000814 SELECT sum(x), total(x) FROM t5 000815 } 000816 } {{} 0.0} 000817 do_test func-18.6 { 000818 execsql { 000819 INSERT INTO t5 VALUES(123); 000820 SELECT sum(x), total(x) FROM t5 000821 } 000822 } {123 123.0} 000823 000824 # Ticket #1664, #1669, #1670, #1674: An integer overflow on SUM causes 000825 # an error. The non-standard TOTAL() function continues to give a helpful 000826 # result. 000827 # 000828 do_test func-18.10 { 000829 execsql { 000830 CREATE TABLE t6(x INTEGER); 000831 INSERT INTO t6 VALUES(1); 000832 INSERT INTO t6 VALUES(1<<62); 000833 SELECT sum(x) - ((1<<62)+1) from t6; 000834 } 000835 } 0 000836 do_test func-18.11 { 000837 execsql { 000838 SELECT typeof(sum(x)) FROM t6 000839 } 000840 } integer 000841 ifcapable floatingpoint { 000842 do_test func-18.12 { 000843 catchsql { 000844 INSERT INTO t6 VALUES(1<<62); 000845 SELECT sum(x) - ((1<<62)*2.0+1) from t6; 000846 } 000847 } {1 {integer overflow}} 000848 do_test func-18.13 { 000849 execsql { 000850 SELECT total(x) - ((1<<62)*2.0+1) FROM t6 000851 } 000852 } 0.0 000853 } 000854 ifcapable !floatingpoint { 000855 do_test func-18.12 { 000856 catchsql { 000857 INSERT INTO t6 VALUES(1<<62); 000858 SELECT sum(x) - ((1<<62)*2+1) from t6; 000859 } 000860 } {1 {integer overflow}} 000861 do_test func-18.13 { 000862 execsql { 000863 SELECT total(x) - ((1<<62)*2+1) FROM t6 000864 } 000865 } 0.0 000866 } 000867 if {[working_64bit_int]} { 000868 do_test func-18.14 { 000869 execsql { 000870 SELECT sum(-9223372036854775805); 000871 } 000872 } -9223372036854775805 000873 } 000874 ifcapable compound&&subquery { 000875 000876 do_test func-18.15 { 000877 catchsql { 000878 SELECT sum(x) FROM 000879 (SELECT 9223372036854775807 AS x UNION ALL 000880 SELECT 10 AS x); 000881 } 000882 } {1 {integer overflow}} 000883 if {[working_64bit_int]} { 000884 do_test func-18.16 { 000885 catchsql { 000886 SELECT sum(x) FROM 000887 (SELECT 9223372036854775807 AS x UNION ALL 000888 SELECT -10 AS x); 000889 } 000890 } {0 9223372036854775797} 000891 do_test func-18.17 { 000892 catchsql { 000893 SELECT sum(x) FROM 000894 (SELECT -9223372036854775807 AS x UNION ALL 000895 SELECT 10 AS x); 000896 } 000897 } {0 -9223372036854775797} 000898 } 000899 do_test func-18.18 { 000900 catchsql { 000901 SELECT sum(x) FROM 000902 (SELECT -9223372036854775807 AS x UNION ALL 000903 SELECT -10 AS x); 000904 } 000905 } {1 {integer overflow}} 000906 do_test func-18.19 { 000907 catchsql { 000908 SELECT sum(x) FROM (SELECT 9 AS x UNION ALL SELECT -10 AS x); 000909 } 000910 } {0 -1} 000911 do_test func-18.20 { 000912 catchsql { 000913 SELECT sum(x) FROM (SELECT -9 AS x UNION ALL SELECT 10 AS x); 000914 } 000915 } {0 1} 000916 do_test func-18.21 { 000917 catchsql { 000918 SELECT sum(x) FROM (SELECT -10 AS x UNION ALL SELECT 9 AS x); 000919 } 000920 } {0 -1} 000921 do_test func-18.22 { 000922 catchsql { 000923 SELECT sum(x) FROM (SELECT 10 AS x UNION ALL SELECT -9 AS x); 000924 } 000925 } {0 1} 000926 000927 } ;# ifcapable compound&&subquery 000928 000929 # Integer overflow on abs() 000930 # 000931 if {[working_64bit_int]} { 000932 do_test func-18.31 { 000933 catchsql { 000934 SELECT abs(-9223372036854775807); 000935 } 000936 } {0 9223372036854775807} 000937 } 000938 do_test func-18.32 { 000939 catchsql { 000940 SELECT abs(-9223372036854775807-1); 000941 } 000942 } {1 {integer overflow}} 000943 000944 # The MATCH function exists but is only a stub and always throws an error. 000945 # 000946 do_test func-19.1 { 000947 execsql { 000948 SELECT match(a,b) FROM t1 WHERE 0; 000949 } 000950 } {} 000951 do_test func-19.2 { 000952 catchsql { 000953 SELECT 'abc' MATCH 'xyz'; 000954 } 000955 } {1 {unable to use function MATCH in the requested context}} 000956 do_test func-19.3 { 000957 catchsql { 000958 SELECT 'abc' NOT MATCH 'xyz'; 000959 } 000960 } {1 {unable to use function MATCH in the requested context}} 000961 do_test func-19.4 { 000962 catchsql { 000963 SELECT match(1,2,3); 000964 } 000965 } {1 {wrong number of arguments to function match()}} 000966 000967 # Soundex tests. 000968 # 000969 if {![catch {db eval {SELECT soundex('hello')}}]} { 000970 set i 0 000971 foreach {name sdx} { 000972 euler E460 000973 EULER E460 000974 Euler E460 000975 ellery E460 000976 gauss G200 000977 ghosh G200 000978 hilbert H416 000979 Heilbronn H416 000980 knuth K530 000981 kant K530 000982 Lloyd L300 000983 LADD L300 000984 Lukasiewicz L222 000985 Lissajous L222 000986 A A000 000987 12345 ?000 000988 } { 000989 incr i 000990 do_test func-20.$i { 000991 execsql {SELECT soundex($name)} 000992 } $sdx 000993 } 000994 } 000995 000996 # Tests of the REPLACE function. 000997 # 000998 do_test func-21.1 { 000999 catchsql { 001000 SELECT replace(1,2); 001001 } 001002 } {1 {wrong number of arguments to function replace()}} 001003 do_test func-21.2 { 001004 catchsql { 001005 SELECT replace(1,2,3,4); 001006 } 001007 } {1 {wrong number of arguments to function replace()}} 001008 do_test func-21.3 { 001009 execsql { 001010 SELECT typeof(replace("This is the main test string", NULL, "ALT")); 001011 } 001012 } {null} 001013 do_test func-21.4 { 001014 execsql { 001015 SELECT typeof(replace(NULL, "main", "ALT")); 001016 } 001017 } {null} 001018 do_test func-21.5 { 001019 execsql { 001020 SELECT typeof(replace("This is the main test string", "main", NULL)); 001021 } 001022 } {null} 001023 do_test func-21.6 { 001024 execsql { 001025 SELECT replace("This is the main test string", "main", "ALT"); 001026 } 001027 } {{This is the ALT test string}} 001028 do_test func-21.7 { 001029 execsql { 001030 SELECT replace("This is the main test string", "main", "larger-main"); 001031 } 001032 } {{This is the larger-main test string}} 001033 do_test func-21.8 { 001034 execsql { 001035 SELECT replace("aaaaaaa", "a", "0123456789"); 001036 } 001037 } {0123456789012345678901234567890123456789012345678901234567890123456789} 001038 001039 ifcapable tclvar { 001040 do_test func-21.9 { 001041 # Attempt to exploit a buffer-overflow that at one time existed 001042 # in the REPLACE function. 001043 set ::str "[string repeat A 29998]CC[string repeat A 35537]" 001044 set ::rep [string repeat B 65536] 001045 execsql { 001046 SELECT LENGTH(REPLACE($::str, 'C', $::rep)); 001047 } 001048 } [expr 29998 + 2*65536 + 35537] 001049 } 001050 001051 # Tests for the TRIM, LTRIM and RTRIM functions. 001052 # 001053 do_test func-22.1 { 001054 catchsql {SELECT trim(1,2,3)} 001055 } {1 {wrong number of arguments to function trim()}} 001056 do_test func-22.2 { 001057 catchsql {SELECT ltrim(1,2,3)} 001058 } {1 {wrong number of arguments to function ltrim()}} 001059 do_test func-22.3 { 001060 catchsql {SELECT rtrim(1,2,3)} 001061 } {1 {wrong number of arguments to function rtrim()}} 001062 do_test func-22.4 { 001063 execsql {SELECT trim(' hi ');} 001064 } {hi} 001065 do_test func-22.5 { 001066 execsql {SELECT ltrim(' hi ');} 001067 } {{hi }} 001068 do_test func-22.6 { 001069 execsql {SELECT rtrim(' hi ');} 001070 } {{ hi}} 001071 do_test func-22.7 { 001072 execsql {SELECT trim(' hi ','xyz');} 001073 } {{ hi }} 001074 do_test func-22.8 { 001075 execsql {SELECT ltrim(' hi ','xyz');} 001076 } {{ hi }} 001077 do_test func-22.9 { 001078 execsql {SELECT rtrim(' hi ','xyz');} 001079 } {{ hi }} 001080 do_test func-22.10 { 001081 execsql {SELECT trim('xyxzy hi zzzy','xyz');} 001082 } {{ hi }} 001083 do_test func-22.11 { 001084 execsql {SELECT ltrim('xyxzy hi zzzy','xyz');} 001085 } {{ hi zzzy}} 001086 do_test func-22.12 { 001087 execsql {SELECT rtrim('xyxzy hi zzzy','xyz');} 001088 } {{xyxzy hi }} 001089 do_test func-22.13 { 001090 execsql {SELECT trim(' hi ','');} 001091 } {{ hi }} 001092 if {[db one {PRAGMA encoding}]=="UTF-8"} { 001093 do_test func-22.14 { 001094 execsql {SELECT hex(trim(x'c280e1bfbff48fbfbf6869',x'6162e1bfbfc280'))} 001095 } {F48FBFBF6869} 001096 do_test func-22.15 { 001097 execsql {SELECT hex(trim(x'6869c280e1bfbff48fbfbf61', 001098 x'6162e1bfbfc280f48fbfbf'))} 001099 } {6869} 001100 do_test func-22.16 { 001101 execsql {SELECT hex(trim(x'ceb1ceb2ceb3',x'ceb1'));} 001102 } {CEB2CEB3} 001103 } 001104 do_test func-22.20 { 001105 execsql {SELECT typeof(trim(NULL));} 001106 } {null} 001107 do_test func-22.21 { 001108 execsql {SELECT typeof(trim(NULL,'xyz'));} 001109 } {null} 001110 do_test func-22.22 { 001111 execsql {SELECT typeof(trim('hello',NULL));} 001112 } {null} 001113 001114 # This is to test the deprecated sqlite3_aggregate_count() API. 001115 # 001116 ifcapable deprecated { 001117 do_test func-23.1 { 001118 sqlite3_create_aggregate db 001119 execsql { 001120 SELECT legacy_count() FROM t6; 001121 } 001122 } {3} 001123 } 001124 001125 # The group_concat() function. 001126 # 001127 do_test func-24.1 { 001128 execsql { 001129 SELECT group_concat(t1) FROM tbl1 001130 } 001131 } {this,program,is,free,software} 001132 do_test func-24.2 { 001133 execsql { 001134 SELECT group_concat(t1,' ') FROM tbl1 001135 } 001136 } {{this program is free software}} 001137 do_test func-24.3 { 001138 execsql { 001139 SELECT group_concat(t1,' ' || rowid || ' ') FROM tbl1 001140 } 001141 } {{this 2 program 3 is 4 free 5 software}} 001142 do_test func-24.4 { 001143 execsql { 001144 SELECT group_concat(NULL,t1) FROM tbl1 001145 } 001146 } {{}} 001147 do_test func-24.5 { 001148 execsql { 001149 SELECT group_concat(t1,NULL) FROM tbl1 001150 } 001151 } {thisprogramisfreesoftware} 001152 do_test func-24.6 { 001153 execsql { 001154 SELECT 'BEGIN-'||group_concat(t1) FROM tbl1 001155 } 001156 } {BEGIN-this,program,is,free,software} 001157 001158 # Ticket #3179: Make sure aggregate functions can take many arguments. 001159 # None of the built-in aggregates do this, so use the md5sum() from the 001160 # test extensions. 001161 # 001162 unset -nocomplain midargs 001163 set midargs {} 001164 unset -nocomplain midres 001165 set midres {} 001166 unset -nocomplain result 001167 for {set i 1} {$i<[sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG -1]} {incr i} { 001168 append midargs ,'/$i' 001169 append midres /$i 001170 set result [md5 \ 001171 "this${midres}program${midres}is${midres}free${midres}software${midres}"] 001172 set sql "SELECT md5sum(t1$midargs) FROM tbl1" 001173 do_test func-24.7.$i { 001174 db eval $::sql 001175 } $result 001176 } 001177 001178 # Ticket #3806. If the initial string in a group_concat is an empty 001179 # string, the separator that follows should still be present. 001180 # 001181 do_test func-24.8 { 001182 execsql { 001183 SELECT group_concat(CASE t1 WHEN 'this' THEN '' ELSE t1 END) FROM tbl1 001184 } 001185 } {,program,is,free,software} 001186 do_test func-24.9 { 001187 execsql { 001188 SELECT group_concat(CASE WHEN t1!='software' THEN '' ELSE t1 END) FROM tbl1 001189 } 001190 } {,,,,software} 001191 001192 # Ticket #3923. Initial empty strings have a separator. But initial 001193 # NULLs do not. 001194 # 001195 do_test func-24.10 { 001196 execsql { 001197 SELECT group_concat(CASE t1 WHEN 'this' THEN null ELSE t1 END) FROM tbl1 001198 } 001199 } {program,is,free,software} 001200 do_test func-24.11 { 001201 execsql { 001202 SELECT group_concat(CASE WHEN t1!='software' THEN null ELSE t1 END) FROM tbl1 001203 } 001204 } {software} 001205 do_test func-24.12 { 001206 execsql { 001207 SELECT group_concat(CASE t1 WHEN 'this' THEN '' 001208 WHEN 'program' THEN null ELSE t1 END) FROM tbl1 001209 } 001210 } {,is,free,software} 001211 # Tests to verify ticket http://www.sqlite.org/src/tktview/55746f9e65f8587c0 001212 do_test func-24.13 { 001213 execsql { 001214 SELECT typeof(group_concat(x)) FROM (SELECT '' AS x); 001215 } 001216 } {text} 001217 do_test func-24.14 { 001218 execsql { 001219 SELECT typeof(group_concat(x,'')) 001220 FROM (SELECT '' AS x UNION ALL SELECT ''); 001221 } 001222 } {text} 001223 001224 001225 # Use the test_isolation function to make sure that type conversions 001226 # on function arguments do not effect subsequent arguments. 001227 # 001228 do_test func-25.1 { 001229 execsql {SELECT test_isolation(t1,t1) FROM tbl1} 001230 } {this program is free software} 001231 001232 # Try to misuse the sqlite3_create_function() interface. Verify that 001233 # errors are returned. 001234 # 001235 do_test func-26.1 { 001236 abuse_create_function db 001237 } {} 001238 001239 # The previous test (func-26.1) registered a function with a very long 001240 # function name that takes many arguments and always returns NULL. Verify 001241 # that this function works correctly. 001242 # 001243 do_test func-26.2 { 001244 set a {} 001245 for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG} {incr i} { 001246 lappend a $i 001247 } 001248 db eval " 001249 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 ,]); 001250 " 001251 } {{}} 001252 do_test func-26.3 { 001253 set a {} 001254 for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG+1} {incr i} { 001255 lappend a $i 001256 } 001257 catchsql " 001258 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 ,]); 001259 " 001260 } {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}} 001261 do_test func-26.4 { 001262 set a {} 001263 for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG-1} {incr i} { 001264 lappend a $i 001265 } 001266 catchsql " 001267 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 ,]); 001268 " 001269 } {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()}} 001270 do_test func-26.5 { 001271 catchsql " 001272 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); 001273 " 001274 } {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}} 001275 do_test func-26.6 { 001276 catchsql " 001277 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); 001278 " 001279 } {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}} 001280 001281 do_test func-27.1 { 001282 catchsql {SELECT coalesce()} 001283 } {1 {wrong number of arguments to function coalesce()}} 001284 do_test func-27.2 { 001285 catchsql {SELECT coalesce(1)} 001286 } {1 {wrong number of arguments to function coalesce()}} 001287 do_test func-27.3 { 001288 catchsql {SELECT coalesce(1,2)} 001289 } {0 1} 001290 001291 # Ticket 2d401a94287b5 001292 # Unknown function in a DEFAULT expression causes a segfault. 001293 # 001294 do_test func-28.1 { 001295 db eval { 001296 CREATE TABLE t28(x, y DEFAULT(nosuchfunc(1))); 001297 } 001298 catchsql { 001299 INSERT INTO t28(x) VALUES(1); 001300 } 001301 } {1 {unknown function: nosuchfunc()}} 001302 001303 # Verify that the length() and typeof() functions do not actually load 001304 # the content of their argument. 001305 # 001306 do_test func-29.1 { 001307 db eval { 001308 CREATE TABLE t29(id INTEGER PRIMARY KEY, x, y); 001309 INSERT INTO t29 VALUES(1, 2, 3), (2, NULL, 4), (3, 4.5, 5); 001310 INSERT INTO t29 VALUES(4, randomblob(1000000), 6); 001311 INSERT INTO t29 VALUES(5, "hello", 7); 001312 } 001313 db close 001314 sqlite3 db test.db 001315 sqlite3_db_status db CACHE_MISS 1 001316 db eval {SELECT typeof(x), length(x), typeof(y) FROM t29 ORDER BY id} 001317 } {integer 1 integer null {} integer real 3 integer blob 1000000 integer text 5 integer} 001318 do_test func-29.2 { 001319 set x [lindex [sqlite3_db_status db CACHE_MISS 1] 1] 001320 if {$x<5} {set x 1} 001321 set x 001322 } {1} 001323 do_test func-29.3 { 001324 db close 001325 sqlite3 db test.db 001326 sqlite3_db_status db CACHE_MISS 1 001327 db eval {SELECT typeof(+x) FROM t29 ORDER BY id} 001328 } {integer null real blob text} 001329 if {[permutation] != "mmap"} { 001330 ifcapable !direct_read { 001331 do_test func-29.4 { 001332 set x [lindex [sqlite3_db_status db CACHE_MISS 1] 1] 001333 if {$x>100} {set x many} 001334 set x 001335 } {many} 001336 } 001337 } 001338 do_test func-29.5 { 001339 db close 001340 sqlite3 db test.db 001341 sqlite3_db_status db CACHE_MISS 1 001342 db eval {SELECT sum(length(x)) FROM t29} 001343 } {1000009} 001344 do_test func-29.6 { 001345 set x [lindex [sqlite3_db_status db CACHE_MISS 1] 1] 001346 if {$x<5} {set x 1} 001347 set x 001348 } {1} 001349 001350 # The OP_Column opcode has an optimization that avoids loading content 001351 # for fields with content-length=0 when the content offset is on an overflow 001352 # page. Make sure the optimization works. 001353 # 001354 do_execsql_test func-29.10 { 001355 CREATE TABLE t29b(a,b,c,d,e,f,g,h,i); 001356 INSERT INTO t29b 001357 VALUES(1, hex(randomblob(2000)), null, 0, 1, '', zeroblob(0),'x',x'01'); 001358 SELECT typeof(c), typeof(d), typeof(e), typeof(f), 001359 typeof(g), typeof(h), typeof(i) FROM t29b; 001360 } {null integer integer text blob text blob} 001361 do_execsql_test func-29.11 { 001362 SELECT length(f), length(g), length(h), length(i) FROM t29b; 001363 } {0 0 1 1} 001364 do_execsql_test func-29.12 { 001365 SELECT quote(f), quote(g), quote(h), quote(i) FROM t29b; 001366 } {'' X'' 'x' X'01'} 001367 001368 # EVIDENCE-OF: R-29701-50711 The unicode(X) function returns the numeric 001369 # unicode code point corresponding to the first character of the string 001370 # X. 001371 # 001372 # EVIDENCE-OF: R-55469-62130 The char(X1,X2,...,XN) function returns a 001373 # string composed of characters having the unicode code point values of 001374 # integers X1 through XN, respectively. 001375 # 001376 do_execsql_test func-30.1 {SELECT unicode('$');} 36 001377 do_execsql_test func-30.2 [subst {SELECT unicode('\u00A2');}] 162 001378 do_execsql_test func-30.3 [subst {SELECT unicode('\u20AC');}] 8364 001379 do_execsql_test func-30.4 {SELECT char(36,162,8364);} [subst {$\u00A2\u20AC}] 001380 001381 for {set i 1} {$i<0xd800} {incr i 13} { 001382 do_execsql_test func-30.5.$i {SELECT unicode(char($i))} $i 001383 } 001384 for {set i 57344} {$i<=0xfffd} {incr i 17} { 001385 if {$i==0xfeff} continue 001386 do_execsql_test func-30.5.$i {SELECT unicode(char($i))} $i 001387 } 001388 for {set i 65536} {$i<=0x10ffff} {incr i 139} { 001389 do_execsql_test func-30.5.$i {SELECT unicode(char($i))} $i 001390 } 001391 001392 # Test char(). 001393 # 001394 do_execsql_test func-31.1 { 001395 SELECT char(), length(char()), typeof(char()) 001396 } {{} 0 text} 001397 001398 # sqlite3_value_frombind() 001399 # 001400 do_execsql_test func-32.100 { 001401 SELECT test_frombind(1,2,3,4); 001402 } {0} 001403 do_execsql_test func-32.110 { 001404 SELECT test_frombind(1,2,?,4); 001405 } {4} 001406 do_execsql_test func-32.120 { 001407 SELECT test_frombind(1,(?),4,?+7); 001408 } {2} 001409 do_execsql_test func-32.130 { 001410 DROP TABLE IF EXISTS t1; 001411 CREATE TABLE t1(a,b,c,e,f); 001412 INSERT INTO t1 VALUES(1,2.5,'xyz',x'e0c1b2a3',null); 001413 SELECT test_frombind(a,b,c,e,f,$xyz) FROM t1; 001414 } {32} 001415 do_execsql_test func-32.140 { 001416 SELECT test_frombind(a,b,c,e,f,$xyz+f) FROM t1; 001417 } {0} 001418 do_execsql_test func-32.150 { 001419 SELECT test_frombind(x.a,y.b,x.c,:123,y.e,x.f,$xyz+y.f) FROM t1 x, t1 y; 001420 } {8} 001421 001422 # 2019-08-15 001423 # Direct-only functions. 001424 # 001425 proc testdirectonly {x} {return [expr {$x*2}]} 001426 do_test func-33.1 { 001427 db func testdirectonly -directonly testdirectonly 001428 db eval {SELECT testdirectonly(15)} 001429 } {30} 001430 do_catchsql_test func-33.2 { 001431 CREATE VIEW v33(y) AS SELECT testdirectonly(15); 001432 SELECT * FROM v33; 001433 } {1 {testdirectonly() prohibited in triggers and views}} 001434 do_execsql_test func-33.3 { 001435 SELECT * FROM (SELECT testdirectonly(15)) AS v33; 001436 } {30} 001437 do_execsql_test func-33.4 { 001438 WITH c(x) AS (SELECT testdirectonly(15)) 001439 SELECT * FROM c; 001440 } {30} 001441 do_catchsql_test func-33.5 { 001442 WITH c(x) AS (SELECT * FROM v33) 001443 SELECT * FROM c; 001444 } {1 {testdirectonly() prohibited in triggers and views}} 001445 do_execsql_test func-33.10 { 001446 CREATE TABLE t33a(a,b); 001447 CREATE TABLE t33b(x,y); 001448 CREATE TRIGGER r1 AFTER INSERT ON t33a BEGIN 001449 INSERT INTO t33b(x,y) VALUES(testdirectonly(new.a),new.b); 001450 END; 001451 } {} 001452 do_catchsql_test func-33.11 { 001453 INSERT INTO t33a VALUES(1,2); 001454 } {1 {testdirectonly() prohibited in triggers and views}} 001455 do_execsql_test func-33.20 { 001456 ALTER TABLE t33a RENAME COLUMN a TO aaa; 001457 SELECT sql FROM sqlite_master WHERE name='r1'; 001458 } {{CREATE TRIGGER r1 AFTER INSERT ON t33a BEGIN 001459 INSERT INTO t33b(x,y) VALUES(testdirectonly(new.aaa),new.b); 001460 END}} 001461 001462 001463 finish_test