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