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