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} 239 240# Test the upper() and lower() functions 241# 242do_test func-5.1 { 243 execsql {SELECT upper(t1) FROM tbl1} 244} {THIS PROGRAM IS FREE SOFTWARE} 245do_test func-5.2 { 246 execsql {SELECT lower(upper(t1)) FROM tbl1} 247} {this program is free software} 248do_test func-5.3 { 249 execsql {SELECT upper(a), lower(a) FROM t2} 250} {1 1 {} {} 345 345 {} {} 67890 67890} 251ifcapable !icu { 252 do_test func-5.4 { 253 catchsql {SELECT upper(a,5) FROM t2} 254 } {1 {wrong number of arguments to function upper()}} 255} 256do_test func-5.5 { 257 catchsql {SELECT upper(*) FROM t2} 258} {1 {wrong number of arguments to function upper()}} 259 260# Test the coalesce() and nullif() functions 261# 262do_test func-6.1 { 263 execsql {SELECT coalesce(a,'xyz') FROM t2} 264} {1 xyz 345 xyz 67890} 265do_test func-6.2 { 266 execsql {SELECT coalesce(upper(a),'nil') FROM t2} 267} {1 nil 345 nil 67890} 268do_test func-6.3 { 269 execsql {SELECT coalesce(nullif(1,1),'nil')} 270} {nil} 271do_test func-6.4 { 272 execsql {SELECT coalesce(nullif(1,2),'nil')} 273} {1} 274do_test func-6.5 { 275 execsql {SELECT coalesce(nullif(1,NULL),'nil')} 276} {1} 277 278 279# Test the last_insert_rowid() function 280# 281do_test func-7.1 { 282 execsql {SELECT last_insert_rowid()} 283} [db last_insert_rowid] 284 285# Tests for aggregate functions and how they handle NULLs. 286# 287ifcapable floatingpoint { 288 do_test func-8.1 { 289 ifcapable explain { 290 execsql {EXPLAIN SELECT sum(a) FROM t2;} 291 } 292 execsql { 293 SELECT sum(a), count(a), round(avg(a),2), min(a), max(a), count(*) FROM t2; 294 } 295 } {68236 3 22745.33 1 67890 5} 296} 297ifcapable !floatingpoint { 298 do_test func-8.1 { 299 ifcapable explain { 300 execsql {EXPLAIN SELECT sum(a) FROM t2;} 301 } 302 execsql { 303 SELECT sum(a), count(a), avg(a), min(a), max(a), count(*) FROM t2; 304 } 305 } {68236 3 22745.0 1 67890 5} 306} 307do_test func-8.2 { 308 execsql { 309 SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t2; 310 } 311} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP} 312 313ifcapable tempdb { 314 do_test func-8.3 { 315 execsql { 316 CREATE TEMP TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC; 317 SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3; 318 } 319 } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP} 320} else { 321 do_test func-8.3 { 322 execsql { 323 CREATE TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC; 324 SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3; 325 } 326 } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP} 327} 328do_test func-8.4 { 329 execsql { 330 SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3; 331 } 332} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP} 333ifcapable compound { 334 do_test func-8.5 { 335 execsql { 336 SELECT sum(x) FROM (SELECT '9223372036' || '854775807' AS x 337 UNION ALL SELECT -9223372036854775807) 338 } 339 } {0} 340 do_test func-8.6 { 341 execsql { 342 SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775807' AS x 343 UNION ALL SELECT -9223372036854775807) 344 } 345 } {integer} 346 do_test func-8.7 { 347 execsql { 348 SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775808' AS x 349 UNION ALL SELECT -9223372036854775807) 350 } 351 } {real} 352ifcapable floatingpoint { 353 do_test func-8.8 { 354 execsql { 355 SELECT sum(x)>0.0 FROM (SELECT '9223372036' || '854775808' AS x 356 UNION ALL SELECT -9223372036850000000) 357 } 358 } {1} 359} 360ifcapable !floatingpoint { 361 do_test func-8.8 { 362 execsql { 363 SELECT sum(x)>0 FROM (SELECT '9223372036' || '854775808' AS x 364 UNION ALL SELECT -9223372036850000000) 365 } 366 } {1} 367} 368} 369 370# How do you test the random() function in a meaningful, deterministic way? 371# 372do_test func-9.1 { 373 execsql { 374 SELECT random() is not null; 375 } 376} {1} 377do_test func-9.2 { 378 execsql { 379 SELECT typeof(random()); 380 } 381} {integer} 382do_test func-9.3 { 383 execsql { 384 SELECT randomblob(32) is not null; 385 } 386} {1} 387do_test func-9.4 { 388 execsql { 389 SELECT typeof(randomblob(32)); 390 } 391} {blob} 392do_test func-9.5 { 393 execsql { 394 SELECT length(randomblob(32)), length(randomblob(-5)), 395 length(randomblob(2000)) 396 } 397} {32 1 2000} 398 399# The "hex()" function was added in order to be able to render blobs 400# generated by randomblob(). So this seems like a good place to test 401# hex(). 402# 403ifcapable bloblit { 404 do_test func-9.10 { 405 execsql {SELECT hex(x'00112233445566778899aAbBcCdDeEfF')} 406 } {00112233445566778899AABBCCDDEEFF} 407} 408set encoding [db one {PRAGMA encoding}] 409if {$encoding=="UTF-16le"} { 410 do_test func-9.11-utf16le { 411 execsql {SELECT hex(replace('abcdefg','ef','12'))} 412 } {6100620063006400310032006700} 413 do_test func-9.12-utf16le { 414 execsql {SELECT hex(replace('abcdefg','','12'))} 415 } {6100620063006400650066006700} 416 do_test func-9.13-utf16le { 417 execsql {SELECT hex(replace('aabcdefg','a','aaa'))} 418 } {610061006100610061006100620063006400650066006700} 419} elseif {$encoding=="UTF-8"} { 420 do_test func-9.11-utf8 { 421 execsql {SELECT hex(replace('abcdefg','ef','12'))} 422 } {61626364313267} 423 do_test func-9.12-utf8 { 424 execsql {SELECT hex(replace('abcdefg','','12'))} 425 } {61626364656667} 426 do_test func-9.13-utf8 { 427 execsql {SELECT hex(replace('aabcdefg','a','aaa'))} 428 } {616161616161626364656667} 429} 430 431# Use the "sqlite_register_test_function" TCL command which is part of 432# the text fixture in order to verify correct operation of some of 433# the user-defined SQL function APIs that are not used by the built-in 434# functions. 435# 436set ::DB [sqlite3_connection_pointer db] 437sqlite_register_test_function $::DB testfunc 438do_test func-10.1 { 439 catchsql { 440 SELECT testfunc(NULL,NULL); 441 } 442} {1 {first argument should be one of: int int64 string double null value}} 443do_test func-10.2 { 444 execsql { 445 SELECT testfunc( 446 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 447 'int', 1234 448 ); 449 } 450} {1234} 451do_test func-10.3 { 452 execsql { 453 SELECT testfunc( 454 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 455 'string', NULL 456 ); 457 } 458} {{}} 459 460ifcapable floatingpoint { 461 do_test func-10.4 { 462 execsql { 463 SELECT testfunc( 464 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 465 'double', 1.234 466 ); 467 } 468 } {1.234} 469 do_test func-10.5 { 470 execsql { 471 SELECT testfunc( 472 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 473 'int', 1234, 474 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 475 'string', NULL, 476 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 477 'double', 1.234, 478 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 479 'int', 1234, 480 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 481 'string', NULL, 482 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 483 'double', 1.234 484 ); 485 } 486 } {1.234} 487} 488 489# Test the built-in sqlite_version(*) SQL function. 490# 491do_test func-11.1 { 492 execsql { 493 SELECT sqlite_version(*); 494 } 495} [sqlite3 -version] 496 497# Test that destructors passed to sqlite3 by calls to sqlite3_result_text() 498# etc. are called. These tests use two special user-defined functions 499# (implemented in func.c) only available in test builds. 500# 501# Function test_destructor() takes one argument and returns a copy of the 502# text form of that argument. A destructor is associated with the return 503# value. Function test_destructor_count() returns the number of outstanding 504# destructor calls for values returned by test_destructor(). 505# 506if {[db eval {PRAGMA encoding}]=="UTF-8"} { 507 do_test func-12.1-utf8 { 508 execsql { 509 SELECT test_destructor('hello world'), test_destructor_count(); 510 } 511 } {{hello world} 1} 512} else { 513 ifcapable {utf16} { 514 do_test func-12.1-utf16 { 515 execsql { 516 SELECT test_destructor16('hello world'), test_destructor_count(); 517 } 518 } {{hello world} 1} 519 } 520} 521do_test func-12.2 { 522 execsql { 523 SELECT test_destructor_count(); 524 } 525} {0} 526do_test func-12.3 { 527 execsql { 528 SELECT test_destructor('hello')||' world' 529 } 530} {{hello world}} 531do_test func-12.4 { 532 execsql { 533 SELECT test_destructor_count(); 534 } 535} {0} 536do_test func-12.5 { 537 execsql { 538 CREATE TABLE t4(x); 539 INSERT INTO t4 VALUES(test_destructor('hello')); 540 INSERT INTO t4 VALUES(test_destructor('world')); 541 SELECT min(test_destructor(x)), max(test_destructor(x)) FROM t4; 542 } 543} {hello world} 544do_test func-12.6 { 545 execsql { 546 SELECT test_destructor_count(); 547 } 548} {0} 549do_test func-12.7 { 550 execsql { 551 DROP TABLE t4; 552 } 553} {} 554 555 556# Test that the auxdata API for scalar functions works. This test uses 557# a special user-defined function only available in test builds, 558# test_auxdata(). Function test_auxdata() takes any number of arguments. 559do_test func-13.1 { 560 execsql { 561 SELECT test_auxdata('hello world'); 562 } 563} {0} 564 565do_test func-13.2 { 566 execsql { 567 CREATE TABLE t4(a, b); 568 INSERT INTO t4 VALUES('abc', 'def'); 569 INSERT INTO t4 VALUES('ghi', 'jkl'); 570 } 571} {} 572do_test func-13.3 { 573 execsql { 574 SELECT test_auxdata('hello world') FROM t4; 575 } 576} {0 1} 577do_test func-13.4 { 578 execsql { 579 SELECT test_auxdata('hello world', 123) FROM t4; 580 } 581} {{0 0} {1 1}} 582do_test func-13.5 { 583 execsql { 584 SELECT test_auxdata('hello world', a) FROM t4; 585 } 586} {{0 0} {1 0}} 587do_test func-13.6 { 588 execsql { 589 SELECT test_auxdata('hello'||'world', a) FROM t4; 590 } 591} {{0 0} {1 0}} 592 593# Test that auxilary data is preserved between calls for SQL variables. 594do_test func-13.7 { 595 set DB [sqlite3_connection_pointer db] 596 set sql "SELECT test_auxdata( ? , a ) FROM t4;" 597 set STMT [sqlite3_prepare $DB $sql -1 TAIL] 598 sqlite3_bind_text $STMT 1 hello\000 -1 599 set res [list] 600 while { "SQLITE_ROW"==[sqlite3_step $STMT] } { 601 lappend res [sqlite3_column_text $STMT 0] 602 } 603 lappend res [sqlite3_finalize $STMT] 604} {{0 0} {1 0} SQLITE_OK} 605 606# Make sure that a function with a very long name is rejected 607do_test func-14.1 { 608 catch { 609 db function [string repeat X 254] {return "hello"} 610 } 611} {0} 612do_test func-14.2 { 613 catch { 614 db function [string repeat X 256] {return "hello"} 615 } 616} {1} 617 618do_test func-15.1 { 619 catchsql {select test_error(NULL)} 620} {1 {}} 621do_test func-15.2 { 622 catchsql {select test_error('this is the error message')} 623} {1 {this is the error message}} 624do_test func-15.3 { 625 catchsql {select test_error('this is the error message',12)} 626} {1 {this is the error message}} 627do_test func-15.4 { 628 db errorcode 629} {12} 630 631# Test the quote function for BLOB and NULL values. 632do_test func-16.1 { 633 execsql { 634 CREATE TABLE tbl2(a, b); 635 } 636 set STMT [sqlite3_prepare $::DB "INSERT INTO tbl2 VALUES(?, ?)" -1 TAIL] 637 sqlite3_bind_blob $::STMT 1 abc 3 638 sqlite3_step $::STMT 639 sqlite3_finalize $::STMT 640 execsql { 641 SELECT quote(a), quote(b) FROM tbl2; 642 } 643} {X'616263' NULL} 644 645# Correctly handle function error messages that include %. Ticket #1354 646# 647do_test func-17.1 { 648 proc testfunc1 args {error "Error %d with %s percents %p"} 649 db function testfunc1 ::testfunc1 650 catchsql { 651 SELECT testfunc1(1,2,3); 652 } 653} {1 {Error %d with %s percents %p}} 654 655# The SUM function should return integer results when all inputs are integer. 656# 657do_test func-18.1 { 658 execsql { 659 CREATE TABLE t5(x); 660 INSERT INTO t5 VALUES(1); 661 INSERT INTO t5 VALUES(-99); 662 INSERT INTO t5 VALUES(10000); 663 SELECT sum(x) FROM t5; 664 } 665} {9902} 666ifcapable floatingpoint { 667 do_test func-18.2 { 668 execsql { 669 INSERT INTO t5 VALUES(0.0); 670 SELECT sum(x) FROM t5; 671 } 672 } {9902.0} 673} 674 675# The sum of nothing is NULL. But the sum of all NULLs is NULL. 676# 677# The TOTAL of nothing is 0.0. 678# 679do_test func-18.3 { 680 execsql { 681 DELETE FROM t5; 682 SELECT sum(x), total(x) FROM t5; 683 } 684} {{} 0.0} 685do_test func-18.4 { 686 execsql { 687 INSERT INTO t5 VALUES(NULL); 688 SELECT sum(x), total(x) FROM t5 689 } 690} {{} 0.0} 691do_test func-18.5 { 692 execsql { 693 INSERT INTO t5 VALUES(NULL); 694 SELECT sum(x), total(x) FROM t5 695 } 696} {{} 0.0} 697do_test func-18.6 { 698 execsql { 699 INSERT INTO t5 VALUES(123); 700 SELECT sum(x), total(x) FROM t5 701 } 702} {123 123.0} 703 704# Ticket #1664, #1669, #1670, #1674: An integer overflow on SUM causes 705# an error. The non-standard TOTAL() function continues to give a helpful 706# result. 707# 708do_test func-18.10 { 709 execsql { 710 CREATE TABLE t6(x INTEGER); 711 INSERT INTO t6 VALUES(1); 712 INSERT INTO t6 VALUES(1<<62); 713 SELECT sum(x) - ((1<<62)+1) from t6; 714 } 715} 0 716do_test func-18.11 { 717 execsql { 718 SELECT typeof(sum(x)) FROM t6 719 } 720} integer 721ifcapable floatingpoint { 722 do_test func-18.12 { 723 catchsql { 724 INSERT INTO t6 VALUES(1<<62); 725 SELECT sum(x) - ((1<<62)*2.0+1) from t6; 726 } 727 } {1 {integer overflow}} 728 do_test func-18.13 { 729 execsql { 730 SELECT total(x) - ((1<<62)*2.0+1) FROM t6 731 } 732 } 0.0 733} 734ifcapable !floatingpoint { 735 do_test func-18.12 { 736 catchsql { 737 INSERT INTO t6 VALUES(1<<62); 738 SELECT sum(x) - ((1<<62)*2+1) from t6; 739 } 740 } {1 {integer overflow}} 741 do_test func-18.13 { 742 execsql { 743 SELECT total(x) - ((1<<62)*2+1) FROM t6 744 } 745 } 0.0 746} 747if {[working_64bit_int]} { 748 do_test func-18.14 { 749 execsql { 750 SELECT sum(-9223372036854775805); 751 } 752 } -9223372036854775805 753} 754ifcapable compound&&subquery { 755 756do_test func-18.15 { 757 catchsql { 758 SELECT sum(x) FROM 759 (SELECT 9223372036854775807 AS x UNION ALL 760 SELECT 10 AS x); 761 } 762} {1 {integer overflow}} 763if {[working_64bit_int]} { 764 do_test func-18.16 { 765 catchsql { 766 SELECT sum(x) FROM 767 (SELECT 9223372036854775807 AS x UNION ALL 768 SELECT -10 AS x); 769 } 770 } {0 9223372036854775797} 771 do_test func-18.17 { 772 catchsql { 773 SELECT sum(x) FROM 774 (SELECT -9223372036854775807 AS x UNION ALL 775 SELECT 10 AS x); 776 } 777 } {0 -9223372036854775797} 778} 779do_test func-18.18 { 780 catchsql { 781 SELECT sum(x) FROM 782 (SELECT -9223372036854775807 AS x UNION ALL 783 SELECT -10 AS x); 784 } 785} {1 {integer overflow}} 786do_test func-18.19 { 787 catchsql { 788 SELECT sum(x) FROM (SELECT 9 AS x UNION ALL SELECT -10 AS x); 789 } 790} {0 -1} 791do_test func-18.20 { 792 catchsql { 793 SELECT sum(x) FROM (SELECT -9 AS x UNION ALL SELECT 10 AS x); 794 } 795} {0 1} 796do_test func-18.21 { 797 catchsql { 798 SELECT sum(x) FROM (SELECT -10 AS x UNION ALL SELECT 9 AS x); 799 } 800} {0 -1} 801do_test func-18.22 { 802 catchsql { 803 SELECT sum(x) FROM (SELECT 10 AS x UNION ALL SELECT -9 AS x); 804 } 805} {0 1} 806 807} ;# ifcapable compound&&subquery 808 809# Integer overflow on abs() 810# 811if {[working_64bit_int]} { 812 do_test func-18.31 { 813 catchsql { 814 SELECT abs(-9223372036854775807); 815 } 816 } {0 9223372036854775807} 817} 818do_test func-18.32 { 819 catchsql { 820 SELECT abs(-9223372036854775807-1); 821 } 822} {1 {integer overflow}} 823 824# The MATCH function exists but is only a stub and always throws an error. 825# 826do_test func-19.1 { 827 execsql { 828 SELECT match(a,b) FROM t1 WHERE 0; 829 } 830} {} 831do_test func-19.2 { 832 catchsql { 833 SELECT 'abc' MATCH 'xyz'; 834 } 835} {1 {unable to use function MATCH in the requested context}} 836do_test func-19.3 { 837 catchsql { 838 SELECT 'abc' NOT MATCH 'xyz'; 839 } 840} {1 {unable to use function MATCH in the requested context}} 841do_test func-19.4 { 842 catchsql { 843 SELECT match(1,2,3); 844 } 845} {1 {wrong number of arguments to function match()}} 846 847# Soundex tests. 848# 849if {![catch {db eval {SELECT soundex('hello')}}]} { 850 set i 0 851 foreach {name sdx} { 852 euler E460 853 EULER E460 854 Euler E460 855 ellery E460 856 gauss G200 857 ghosh G200 858 hilbert H416 859 Heilbronn H416 860 knuth K530 861 kant K530 862 Lloyd L300 863 LADD L300 864 Lukasiewicz L222 865 Lissajous L222 866 A A000 867 12345 ?000 868 } { 869 incr i 870 do_test func-20.$i { 871 execsql {SELECT soundex($name)} 872 } $sdx 873 } 874} 875 876# Tests of the REPLACE function. 877# 878do_test func-21.1 { 879 catchsql { 880 SELECT replace(1,2); 881 } 882} {1 {wrong number of arguments to function replace()}} 883do_test func-21.2 { 884 catchsql { 885 SELECT replace(1,2,3,4); 886 } 887} {1 {wrong number of arguments to function replace()}} 888do_test func-21.3 { 889 execsql { 890 SELECT typeof(replace("This is the main test string", NULL, "ALT")); 891 } 892} {null} 893do_test func-21.4 { 894 execsql { 895 SELECT typeof(replace(NULL, "main", "ALT")); 896 } 897} {null} 898do_test func-21.5 { 899 execsql { 900 SELECT typeof(replace("This is the main test string", "main", NULL)); 901 } 902} {null} 903do_test func-21.6 { 904 execsql { 905 SELECT replace("This is the main test string", "main", "ALT"); 906 } 907} {{This is the ALT test string}} 908do_test func-21.7 { 909 execsql { 910 SELECT replace("This is the main test string", "main", "larger-main"); 911 } 912} {{This is the larger-main test string}} 913do_test func-21.8 { 914 execsql { 915 SELECT replace("aaaaaaa", "a", "0123456789"); 916 } 917} {0123456789012345678901234567890123456789012345678901234567890123456789} 918 919ifcapable tclvar { 920 do_test func-21.9 { 921 # Attempt to exploit a buffer-overflow that at one time existed 922 # in the REPLACE function. 923 set ::str "[string repeat A 29998]CC[string repeat A 35537]" 924 set ::rep [string repeat B 65536] 925 execsql { 926 SELECT LENGTH(REPLACE($::str, 'C', $::rep)); 927 } 928 } [expr 29998 + 2*65536 + 35537] 929} 930 931# Tests for the TRIM, LTRIM and RTRIM functions. 932# 933do_test func-22.1 { 934 catchsql {SELECT trim(1,2,3)} 935} {1 {wrong number of arguments to function trim()}} 936do_test func-22.2 { 937 catchsql {SELECT ltrim(1,2,3)} 938} {1 {wrong number of arguments to function ltrim()}} 939do_test func-22.3 { 940 catchsql {SELECT rtrim(1,2,3)} 941} {1 {wrong number of arguments to function rtrim()}} 942do_test func-22.4 { 943 execsql {SELECT trim(' hi ');} 944} {hi} 945do_test func-22.5 { 946 execsql {SELECT ltrim(' hi ');} 947} {{hi }} 948do_test func-22.6 { 949 execsql {SELECT rtrim(' hi ');} 950} {{ hi}} 951do_test func-22.7 { 952 execsql {SELECT trim(' hi ','xyz');} 953} {{ hi }} 954do_test func-22.8 { 955 execsql {SELECT ltrim(' hi ','xyz');} 956} {{ hi }} 957do_test func-22.9 { 958 execsql {SELECT rtrim(' hi ','xyz');} 959} {{ hi }} 960do_test func-22.10 { 961 execsql {SELECT trim('xyxzy hi zzzy','xyz');} 962} {{ hi }} 963do_test func-22.11 { 964 execsql {SELECT ltrim('xyxzy hi zzzy','xyz');} 965} {{ hi zzzy}} 966do_test func-22.12 { 967 execsql {SELECT rtrim('xyxzy hi zzzy','xyz');} 968} {{xyxzy hi }} 969do_test func-22.13 { 970 execsql {SELECT trim(' hi ','');} 971} {{ hi }} 972if {[db one {PRAGMA encoding}]=="UTF-8"} { 973 do_test func-22.14 { 974 execsql {SELECT hex(trim(x'c280e1bfbff48fbfbf6869',x'6162e1bfbfc280'))} 975 } {F48FBFBF6869} 976 do_test func-22.15 { 977 execsql {SELECT hex(trim(x'6869c280e1bfbff48fbfbf61', 978 x'6162e1bfbfc280f48fbfbf'))} 979 } {6869} 980 do_test func-22.16 { 981 execsql {SELECT hex(trim(x'ceb1ceb2ceb3',x'ceb1'));} 982 } {CEB2CEB3} 983} 984do_test func-22.20 { 985 execsql {SELECT typeof(trim(NULL));} 986} {null} 987do_test func-22.21 { 988 execsql {SELECT typeof(trim(NULL,'xyz'));} 989} {null} 990do_test func-22.22 { 991 execsql {SELECT typeof(trim('hello',NULL));} 992} {null} 993 994# This is to test the deprecated sqlite3_aggregate_count() API. 995# 996ifcapable deprecated { 997 do_test func-23.1 { 998 sqlite3_create_aggregate db 999 execsql { 1000 SELECT legacy_count() FROM t6; 1001 } 1002 } {3} 1003} 1004 1005# The group_concat() function. 1006# 1007do_test func-24.1 { 1008 execsql { 1009 SELECT group_concat(t1) FROM tbl1 1010 } 1011} {this,program,is,free,software} 1012do_test func-24.2 { 1013 execsql { 1014 SELECT group_concat(t1,' ') FROM tbl1 1015 } 1016} {{this program is free software}} 1017do_test func-24.3 { 1018 execsql { 1019 SELECT group_concat(t1,' ' || rowid || ' ') FROM tbl1 1020 } 1021} {{this 2 program 3 is 4 free 5 software}} 1022do_test func-24.4 { 1023 execsql { 1024 SELECT group_concat(NULL,t1) FROM tbl1 1025 } 1026} {{}} 1027do_test func-24.5 { 1028 execsql { 1029 SELECT group_concat(t1,NULL) FROM tbl1 1030 } 1031} {thisprogramisfreesoftware} 1032do_test func-24.6 { 1033 execsql { 1034 SELECT 'BEGIN-'||group_concat(t1) FROM tbl1 1035 } 1036} {BEGIN-this,program,is,free,software} 1037 1038# Ticket #3179: Make sure aggregate functions can take many arguments. 1039# None of the built-in aggregates do this, so use the md5sum() from the 1040# test extensions. 1041# 1042unset -nocomplain midargs 1043set midargs {} 1044unset -nocomplain midres 1045set midres {} 1046unset -nocomplain result 1047for {set i 1} {$i<[sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG -1]} {incr i} { 1048 append midargs ,'/$i' 1049 append midres /$i 1050 set result [md5 \ 1051 "this${midres}program${midres}is${midres}free${midres}software${midres}"] 1052 set sql "SELECT md5sum(t1$midargs) FROM tbl1" 1053 do_test func-24.7.$i { 1054 db eval $::sql 1055 } $result 1056} 1057 1058# Ticket #3806. If the initial string in a group_concat is an empty 1059# string, the separator that follows should still be present. 1060# 1061do_test func-24.8 { 1062 execsql { 1063 SELECT group_concat(CASE t1 WHEN 'this' THEN '' ELSE t1 END) FROM tbl1 1064 } 1065} {,program,is,free,software} 1066do_test func-24.9 { 1067 execsql { 1068 SELECT group_concat(CASE WHEN t1!='software' THEN '' ELSE t1 END) FROM tbl1 1069 } 1070} {,,,,software} 1071 1072# Ticket #3923. Initial empty strings have a separator. But initial 1073# NULLs do not. 1074# 1075do_test func-24.10 { 1076 execsql { 1077 SELECT group_concat(CASE t1 WHEN 'this' THEN null ELSE t1 END) FROM tbl1 1078 } 1079} {program,is,free,software} 1080do_test func-24.11 { 1081 execsql { 1082 SELECT group_concat(CASE WHEN t1!='software' THEN null ELSE t1 END) FROM tbl1 1083 } 1084} {software} 1085do_test func-24.12 { 1086 execsql { 1087 SELECT group_concat(CASE t1 WHEN 'this' THEN '' 1088 WHEN 'program' THEN null ELSE t1 END) FROM tbl1 1089 } 1090} {,is,free,software} 1091 1092 1093# Use the test_isolation function to make sure that type conversions 1094# on function arguments do not effect subsequent arguments. 1095# 1096do_test func-25.1 { 1097 execsql {SELECT test_isolation(t1,t1) FROM tbl1} 1098} {this program is free software} 1099 1100# Try to misuse the sqlite3_create_function() interface. Verify that 1101# errors are returned. 1102# 1103do_test func-26.1 { 1104 abuse_create_function db 1105} {} 1106 1107# The previous test (func-26.1) registered a function with a very long 1108# function name that takes many arguments and always returns NULL. Verify 1109# that this function works correctly. 1110# 1111do_test func-26.2 { 1112 set a {} 1113 for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG} {incr i} { 1114 lappend a $i 1115 } 1116 db eval " 1117 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 ,]); 1118 " 1119} {{}} 1120do_test func-26.3 { 1121 set a {} 1122 for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG+1} {incr i} { 1123 lappend a $i 1124 } 1125 catchsql " 1126 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 ,]); 1127 " 1128} {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}} 1129do_test func-26.4 { 1130 set a {} 1131 for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG-1} {incr i} { 1132 lappend a $i 1133 } 1134 catchsql " 1135 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 ,]); 1136 " 1137} {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()}} 1138do_test func-26.5 { 1139 catchsql " 1140 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); 1141 " 1142} {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}} 1143do_test func-26.6 { 1144 catchsql " 1145 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); 1146 " 1147} {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}} 1148 1149do_test func-27.1 { 1150 catchsql {SELECT coalesce()} 1151} {1 {wrong number of arguments to function coalesce()}} 1152do_test func-27.2 { 1153 catchsql {SELECT coalesce(1)} 1154} {1 {wrong number of arguments to function coalesce()}} 1155do_test func-27.3 { 1156 catchsql {SELECT coalesce(1,2)} 1157} {0 1} 1158 1159# Ticket 2d401a94287b5 1160# Unknown function in a DEFAULT expression causes a segfault. 1161# 1162do_test func-28.1 { 1163 db eval { 1164 CREATE TABLE t28(x, y DEFAULT(nosuchfunc(1))); 1165 } 1166 catchsql { 1167 INSERT INTO t28(x) VALUES(1); 1168 } 1169} {1 {unknown function: nosuchfunc()}} 1170 1171finish_test 1172