AryaWu/sqlite
0
1# 2001 September 152#3# The author disclaims copyright to this source code. In place of4# 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. The12# focus of this file is testing built-in functions.13#14 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17set testprefix func18 19# Create a table to work with.20#21do_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}28do_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() function41#42do_test func-1.0 {43 execsql {SELECT length(t1) FROM tbl1 ORDER BY t1}44} {4 2 7 8 4}45set isutf16 [regexp 16 [db one {PRAGMA encoding}]]46do_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"}]49do_test func-1.1 {50 set r [catch {execsql {SELECT length(*) FROM tbl1 ORDER BY t1}} msg]51 lappend r $msg52} {1 {wrong number of arguments to function length()}}53do_test func-1.2 {54 set r [catch {execsql {SELECT length(t1,5) FROM tbl1 ORDER BY t1}} msg]55 lappend r $msg56} {1 {wrong number of arguments to function length()}}57do_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}61do_test func-1.4 {62 execsql {SELECT coalesce(length(a),-1) FROM t2}63} {1 -1 3 -1 5}64do_execsql_test func-1.5 {65 SELECT octet_length(12345);66} [expr {(1+($isutf16!=0))*5}]67db null NULL68do_execsql_test func-1.6 {69 SELECT octet_length(NULL);70} {NULL}71do_execsql_test func-1.7 {72 SELECT octet_length(7.5);73} [expr {(1+($isutf16!=0))*3}]74do_execsql_test func-1.8 {75 SELECT octet_length(x'30313233');76} {4}77do_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() function85#86db null {}87do_test func-2.0 {88 execsql {SELECT substr(t1,1,2) FROM tbl1 ORDER BY t1}89} {fr is pr so th}90do_test func-2.1 {91 execsql {SELECT substr(t1,2,1) FROM tbl1 ORDER BY t1}92} {r s r o h}93do_test func-2.2 {94 execsql {SELECT substr(t1,3,3) FROM tbl1 ORDER BY t1}95} {ee {} ogr ftw is}96do_test func-2.3 {97 execsql {SELECT substr(t1,-1,1) FROM tbl1 ORDER BY t1}98} {e s m e s}99do_test func-2.4 {100 execsql {SELECT substr(t1,-1,2) FROM tbl1 ORDER BY t1}101} {e s m e s}102do_test func-2.5 {103 execsql {SELECT substr(t1,-2,1) FROM tbl1 ORDER BY t1}104} {e i a r i}105do_test func-2.6 {106 execsql {SELECT substr(t1,-2,2) FROM tbl1 ORDER BY t1}107} {ee is am re is}108do_test func-2.7 {109 execsql {SELECT substr(t1,-4,2) FROM tbl1 ORDER BY t1}110} {fr {} gr wa th}111do_test func-2.8 {112 execsql {SELECT t1 FROM tbl1 ORDER BY substr(t1,2,20)}113} {this software free program is}114do_test func-2.9 {115 execsql {SELECT substr(a,1,1) FROM t2}116} {1 {} 3 {} 6}117do_test func-2.10 {118 execsql {SELECT substr(a,2,2) FROM t2}119} {{} {} 45 {} 78}120do_test func-2.11 {121 execsql {SELECT substr('abcdefg',0x100000001,2)}122} {{}}123do_test func-2.12 {124 execsql {SELECT substr('abcdefg',1,0x100000002)}125} {abcdefg}126do_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 capabilities131#132if {"\u1234"!="u1234"} {133 134# Put some UTF-8 characters in the database135#136do_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"143do_test func-3.1 {144 execsql {SELECT length(t1) FROM tbl1 ORDER BY t1}145} {5 10 8 5}146do_test func-3.2 {147 execsql {SELECT substr(t1,1,2) FROM tbl1 ORDER BY t1}148} {UT ch co hi}149do_test func-3.3 {150 execsql {SELECT substr(t1,1,3) FROM tbl1 ORDER BY t1}151} "UTF cha con hi\u1234"152do_test func-3.4 {153 execsql {SELECT substr(t1,2,2) FROM tbl1 ORDER BY t1}154} "TF ha on i\u1234"155do_test func-3.5 {156 execsql {SELECT substr(t1,2,3) FROM tbl1 ORDER BY t1}157} "TF- har ont i\u1234h"158do_test func-3.6 {159 execsql {SELECT substr(t1,3,2) FROM tbl1 ORDER BY t1}160} "F- ar nt \u1234h"161do_test func-3.7 {162 execsql {SELECT substr(t1,4,2) FROM tbl1 ORDER BY t1}163} "-8 ra ta ho"164do_test func-3.8 {165 execsql {SELECT substr(t1,-1,1) FROM tbl1 ORDER BY t1}166} "8 s s o"167do_test func-3.9 {168 execsql {SELECT substr(t1,-3,2) FROM tbl1 ORDER BY t1}169} "F- er in \u1234h"170do_test func-3.10 {171 execsql {SELECT substr(t1,-4,3) FROM tbl1 ORDER BY t1}172} "TF- ter ain i\u1234h"173do_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!=u1234182 183# Test the abs() and round() functions.184#185ifcapable !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}196ifcapable 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}207do_test func-4.2 {208 catchsql {SELECT abs() FROM t1}209} {1 {wrong number of arguments to function abs()}}210ifcapable 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}218ifcapable !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}228do_test func-4.4.1 {229 execsql {SELECT abs(a) FROM t2}230} {1 {} 345 {} 67890}231do_test func-4.4.2 {232 execsql {SELECT abs(t1) FROM tbl1}233} {0.0 0.0 0.0 0.0 0.0}234 235ifcapable 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 } $x2279 }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 } $x2286 }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() functions357#358do_test func-5.1 {359 execsql {SELECT upper(t1) FROM tbl1}360} {THIS PROGRAM IS FREE SOFTWARE}361do_test func-5.2 {362 execsql {SELECT lower(upper(t1)) FROM tbl1}363} {this program is free software}364do_test func-5.3 {365 execsql {SELECT upper(a), lower(a) FROM t2}366} {1 1 {} {} 345 345 {} {} 67890 67890}367ifcapable !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}372do_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() functions377#378do_test func-6.1 {379 execsql {SELECT coalesce(a,'xyz') FROM t2}380} {1 xyz 345 xyz 67890}381do_test func-6.2 {382 execsql {SELECT coalesce(upper(a),'nil') FROM t2}383} {1 nil 345 nil 67890}384do_test func-6.3 {385 execsql {SELECT coalesce(nullif(1,1),'nil')}386} {nil}387do_test func-6.4 {388 execsql {SELECT coalesce(nullif(1,2),'nil')}389} {1}390do_test func-6.5 {391 execsql {SELECT coalesce(nullif(1,NULL),'nil')}392} {1}393 394 395# Test the last_insert_rowid() function396#397do_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#403ifcapable 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}413ifcapable !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}423do_test func-8.2 {424 execsql {425 SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t2;426 }427} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}428 429ifcapable 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}444do_test func-8.4 {445 execsql {446 SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;447 }448} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}449ifcapable compound {450 do_test func-8.5 {451 execsql {452 SELECT sum(x) FROM (SELECT '9223372036' || '854775807' AS x453 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 x459 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 x465 UNION ALL SELECT -9223372036854775807)466 }467 } {real}468ifcapable floatingpoint {469 do_test func-8.8 {470 execsql {471 SELECT sum(x)>0.0 FROM (SELECT '9223372036' || '854775808' AS x472 UNION ALL SELECT -9223372036850000000)473 }474 } {1}475}476ifcapable !floatingpoint {477 do_test func-8.8 {478 execsql {479 SELECT sum(x)>0 FROM (SELECT '9223372036' || '854775808' AS x480 UNION ALL SELECT -9223372036850000000)481 }482 } {1}483}484}485 486# How do you test the random() function in a meaningful, deterministic way?487#488do_test func-9.1 {489 execsql {490 SELECT random() is not null;491 }492} {1}493do_test func-9.2 {494 execsql {495 SELECT typeof(random());496 }497} {integer}498do_test func-9.3 {499 execsql {500 SELECT randomblob(32) is not null;501 }502} {1}503do_test func-9.4 {504 execsql {505 SELECT typeof(randomblob(32));506 }507} {blob}508do_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 blobs516# generated by randomblob(). So this seems like a good place to test517# hex().518#519ifcapable bloblit {520 do_test func-9.10 {521 execsql {SELECT hex(x'00112233445566778899aAbBcCdDeEfF')}522 } {00112233445566778899AABBCCDDEEFF}523}524set encoding [db one {PRAGMA encoding}]525if {$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}546do_execsql_test func-9.14 {547 WITH RECURSIVE c(x) AS (548 VALUES(1)549 UNION ALL550 SELECT x+1 FROM c WHERE x<1040551 )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 of559# the text fixture in order to verify correct operation of some of560# the user-defined SQL function APIs that are not used by the built-in561# functions.562#563set ::DB [sqlite3_connection_pointer db]564sqlite_register_test_function $::DB testfunc565do_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}}570do_test func-10.2 {571 execsql {572 SELECT testfunc(573 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',574 'int', 1234575 );576 }577} {1234}578do_test func-10.3 {579 execsql {580 SELECT testfunc(581 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',582 'string', NULL583 );584 }585} {{}}586 587ifcapable floatingpoint {588 do_test func-10.4 {589 execsql {590 SELECT testfunc(591 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',592 'double', 1.234593 );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.234611 );612 }613 } {1.234}614}615 616# Test the built-in sqlite_version(*) SQL function.617#618do_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 functions626# (implemented in func.c) only available in test builds. 627#628# Function test_destructor() takes one argument and returns a copy of the629# text form of that argument. A destructor is associated with the return630# value. Function test_destructor_count() returns the number of outstanding631# destructor calls for values returned by test_destructor().632#633if {[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}648do_test func-12.2 {649 execsql {650 SELECT test_destructor_count();651 }652} {0}653do_test func-12.3 {654 execsql {655 SELECT test_destructor('hello')||' world'656 }657} {{hello world}}658do_test func-12.4 {659 execsql {660 SELECT test_destructor_count();661 }662} {0}663do_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}671do_test func-12.6 {672 execsql {673 SELECT test_destructor_count();674 }675} {0}676do_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 uses684# a special user-defined function only available in test builds,685# test_auxdata(). Function test_auxdata() takes any number of arguments.686do_test func-13.1 {687 execsql {688 SELECT test_auxdata('hello world');689 }690} {0}691 692do_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} {}699do_test func-13.3 {700 execsql {701 SELECT test_auxdata('hello world') FROM t4;702 }703} {0 1}704do_test func-13.4 {705 execsql {706 SELECT test_auxdata('hello world', 123) FROM t4;707 }708} {{0 0} {1 1}}709do_test func-13.5 {710 execsql {711 SELECT test_auxdata('hello world', a) FROM t4;712 }713} {{0 0} {1 0}}714do_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.721do_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 -1726 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.734do_execsql_test 13.8.1 {735 SELECT test_auxdata('constant') FROM t4;736} {0 1}737do_execsql_test 13.8.2 {738 SELECT test_auxdata('constant') FROM t4;739} {0 1}740db cache flush741do_execsql_test 13.8.3 {742 SELECT test_auxdata('constant') FROM t4;743} {0 1}744set V "one"745do_execsql_test 13.8.4 {746 SELECT test_auxdata($V), $V FROM t4;747} {0 one 1 one}748set V "two"749do_execsql_test 13.8.5 {750 SELECT test_auxdata($V), $V FROM t4;751} {0 two 1 two}752db cache flush753set V "three"754do_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 rejected760do_test func-14.1 {761 catch {762 db function [string repeat X 254] {return "hello"}763 } 764} {0}765do_test func-14.2 {766 catch {767 db function [string repeat X 256] {return "hello"}768 }769} {1}770 771do_test func-15.1 {772 catchsql {select test_error(NULL)}773} {1 {}}774do_test func-15.2 {775 catchsql {select test_error('this is the error message')}776} {1 {this is the error message}}777do_test func-15.3 {778 catchsql {select test_error('this is the error message',12)}779} {1 {this is the error message}}780do_test func-15.4 {781 db errorcode782} {12}783 784# Test the quote function for BLOB and NULL values.785do_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 3791 sqlite3_step $::STMT792 sqlite3_finalize $::STMT793 execsql {794 SELECT quote(a), quote(b) FROM tbl2;795 }796} {X'616263' NULL}797 798# Test the quote function for +Inf and -Inf799do_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 #1354804#805do_test func-17.1 {806 proc testfunc1 args {error "Error %d with %s percents %p"}807 db function testfunc1 ::testfunc1808 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#815do_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}824ifcapable 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#837do_test func-18.3 {838 execsql {839 DELETE FROM t5;840 SELECT sum(x), total(x) FROM t5;841 }842} {{} 0.0}843do_test func-18.4 {844 execsql {845 INSERT INTO t5 VALUES(NULL);846 SELECT sum(x), total(x) FROM t5847 }848} {{} 0.0}849do_test func-18.5 {850 execsql {851 INSERT INTO t5 VALUES(NULL);852 SELECT sum(x), total(x) FROM t5853 }854} {{} 0.0}855do_test func-18.6 {856 execsql {857 INSERT INTO t5 VALUES(123);858 SELECT sum(x), total(x) FROM t5859 }860} {123 123.0}861 862# Ticket #1664, #1669, #1670, #1674: An integer overflow on SUM causes863# an error. The non-standard TOTAL() function continues to give a helpful864# result.865#866do_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} 0874do_test func-18.11 {875 execsql {876 SELECT typeof(sum(x)) FROM t6877 }878} integer879ifcapable 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 t6886 } {0 0.0}887}888if {[working_64bit_int]} {889 do_test func-18.14 {890 execsql {891 SELECT sum(-9223372036854775805);892 }893 } -9223372036854775805894}895ifcapable compound&&subquery {896 897do_test func-18.15 {898 catchsql {899 SELECT sum(x) FROM 900 (SELECT 9223372036854775807 AS x UNION ALL901 SELECT 10 AS x);902 }903} {1 {integer overflow}}904if {[working_64bit_int]} {905 do_test func-18.16 {906 catchsql {907 SELECT sum(x) FROM 908 (SELECT 9223372036854775807 AS x UNION ALL909 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 ALL916 SELECT 10 AS x);917 }918 } {0 -9223372036854775797}919}920do_test func-18.18 {921 catchsql {922 SELECT sum(x) FROM 923 (SELECT -9223372036854775807 AS x UNION ALL924 SELECT -10 AS x);925 }926} {1 {integer overflow}}927do_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}932do_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}937do_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}942do_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&&subquery949 950# Integer overflow on abs()951#952if {[working_64bit_int]} {953 do_test func-18.31 {954 catchsql {955 SELECT abs(-9223372036854775807);956 }957 } {0 9223372036854775807}958}959do_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#967do_test func-19.1 {968 execsql {969 SELECT match(a,b) FROM t1 WHERE 0;970 }971} {}972do_test func-19.2 {973 catchsql {974 SELECT 'abc' MATCH 'xyz';975 }976} {1 {unable to use function MATCH in the requested context}}977do_test func-19.3 {978 catchsql {979 SELECT 'abc' NOT MATCH 'xyz';980 }981} {1 {unable to use function MATCH in the requested context}}982do_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#990if {![catch {db eval {SELECT soundex('hello')}}]} {991 set i 0992 foreach {name sdx} {993 euler E460994 EULER E460995 Euler E460996 ellery E460997 gauss G200998 ghosh G200999 hilbert H4161000 Heilbronn H4161001 knuth K5301002 kant K5301003 Lloyd L3001004 LADD L3001005 Lukasiewicz L2221006 Lissajous L2221007 A A0001008 12345 ?0001009 } {1010 incr i1011 do_test func-20.$i {1012 execsql {SELECT soundex($name)}1013 } $sdx1014 }1015}1016 1017# Tests of the REPLACE function.1018#1019do_test func-21.1 {1020 catchsql {1021 SELECT replace(1,2);1022 }1023} {1 {wrong number of arguments to function replace()}}1024do_test func-21.2 {1025 catchsql {1026 SELECT replace(1,2,3,4);1027 }1028} {1 {wrong number of arguments to function replace()}}1029do_test func-21.3 {1030 execsql {1031 SELECT typeof(replace('This is the main test string', NULL, 'ALT'));1032 }1033} {null}1034do_test func-21.4 {1035 execsql {1036 SELECT typeof(replace(NULL, 'main', 'ALT'));1037 }1038} {null}1039do_test func-21.5 {1040 execsql {1041 SELECT typeof(replace('This is the main test string', 'main', NULL));1042 }1043} {null}1044do_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}}1049do_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}}1054do_test func-21.8 {1055 execsql {1056 SELECT replace('aaaaaaa', 'a', '0123456789');1057 }1058} {0123456789012345678901234567890123456789012345678901234567890123456789}1059do_execsql_test func-21.9 {1060 SELECT typeof(replace(1,'',0));1061} {text}1062 1063ifcapable 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#1077do_test func-22.1 {1078 catchsql {SELECT trim(1,2,3)}1079} {1 {wrong number of arguments to function trim()}}1080do_test func-22.2 {1081 catchsql {SELECT ltrim(1,2,3)}1082} {1 {wrong number of arguments to function ltrim()}}1083do_test func-22.3 {1084 catchsql {SELECT rtrim(1,2,3)}1085} {1 {wrong number of arguments to function rtrim()}}1086do_test func-22.4 {1087 execsql {SELECT trim(' hi ');}1088} {hi}1089do_test func-22.5 {1090 execsql {SELECT ltrim(' hi ');}1091} {{hi }}1092do_test func-22.6 {1093 execsql {SELECT rtrim(' hi ');}1094} {{ hi}}1095do_test func-22.7 {1096 execsql {SELECT trim(' hi ','xyz');}1097} {{ hi }}1098do_test func-22.8 {1099 execsql {SELECT ltrim(' hi ','xyz');}1100} {{ hi }}1101do_test func-22.9 {1102 execsql {SELECT rtrim(' hi ','xyz');}1103} {{ hi }}1104do_test func-22.10 {1105 execsql {SELECT trim('xyxzy hi zzzy','xyz');}1106} {{ hi }}1107do_test func-22.11 {1108 execsql {SELECT ltrim('xyxzy hi zzzy','xyz');}1109} {{ hi zzzy}}1110do_test func-22.12 {1111 execsql {SELECT rtrim('xyxzy hi zzzy','xyz');}1112} {{xyxzy hi }}1113do_test func-22.13 {1114 execsql {SELECT trim(' hi ','');}1115} {{ hi }}1116if {[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}1128do_test func-22.20 {1129 execsql {SELECT typeof(trim(NULL));}1130} {null}1131do_test func-22.21 {1132 execsql {SELECT typeof(trim(NULL,'xyz'));}1133} {null}1134do_test func-22.22 {1135 execsql {SELECT typeof(trim('hello',NULL));}1136} {null}1137 1138# 2021-06-15 - infinite loop due to unsigned character counter1139# overflow, reported by Zimuzo Ezeozue1140#1141do_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#1147ifcapable deprecated {1148 do_test func-23.1 {1149 sqlite3_create_aggregate db1150 execsql {1151 SELECT legacy_count() FROM t6;1152 }1153 } {3}1154}1155 1156# The group_concat() and string_agg() functions.1157#1158do_test func-24.1 {1159 execsql {1160 SELECT group_concat(t1), string_agg(t1,',') FROM tbl11161 }1162} {this,program,is,free,software this,program,is,free,software}1163do_test func-24.2 {1164 execsql {1165 SELECT group_concat(t1,' '), string_agg(t1,' ') FROM tbl11166 }1167} {{this program is free software} {this program is free software}}1168do_test func-24.3 {1169 execsql {1170 SELECT group_concat(t1,' ' || rowid || ' ') FROM tbl11171 }1172} {{this 2 program 3 is 4 free 5 software}}1173do_test func-24.4 {1174 execsql {1175 SELECT group_concat(NULL,t1) FROM tbl11176 }1177} {{}}1178do_test func-24.5 {1179 execsql {1180 SELECT group_concat(t1,NULL), string_agg(t1,NULL) FROM tbl11181 }1182} {thisprogramisfreesoftware thisprogramisfreesoftware}1183do_test func-24.6 {1184 execsql {1185 SELECT 'BEGIN-'||group_concat(t1) FROM tbl11186 }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 the1191# test extensions.1192#1193unset -nocomplain midargs1194set midargs {}1195unset -nocomplain midres1196set midres {}1197unset -nocomplain result1198set limit [sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG -1]1199if {$limit>400} {set limit 400}1200for {set i 1} {$i<$limit} {incr i} {