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