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