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.63 2007/05/02 15:36:02 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} 223do_test func-5.4 { 224 catchsql {SELECT upper(a,5) FROM t2} 225} {1 {wrong number of arguments to function upper()}} 226do_test func-5.5 { 227 catchsql {SELECT upper(*) FROM t2} 228} {1 {wrong number of arguments to function upper()}} 229 230# Test the coalesce() and nullif() functions 231# 232do_test func-6.1 { 233 execsql {SELECT coalesce(a,'xyz') FROM t2} 234} {1 xyz 345 xyz 67890} 235do_test func-6.2 { 236 execsql {SELECT coalesce(upper(a),'nil') FROM t2} 237} {1 nil 345 nil 67890} 238do_test func-6.3 { 239 execsql {SELECT coalesce(nullif(1,1),'nil')} 240} {nil} 241do_test func-6.4 { 242 execsql {SELECT coalesce(nullif(1,2),'nil')} 243} {1} 244do_test func-6.5 { 245 execsql {SELECT coalesce(nullif(1,NULL),'nil')} 246} {1} 247 248 249# Test the last_insert_rowid() function 250# 251do_test func-7.1 { 252 execsql {SELECT last_insert_rowid()} 253} [db last_insert_rowid] 254 255# Tests for aggregate functions and how they handle NULLs. 256# 257do_test func-8.1 { 258 ifcapable explain { 259 execsql {EXPLAIN SELECT sum(a) FROM t2;} 260 } 261 execsql { 262 SELECT sum(a), count(a), round(avg(a),2), min(a), max(a), count(*) FROM t2; 263 } 264} {68236 3 22745.33 1 67890 5} 265do_test func-8.2 { 266 execsql { 267 SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t2; 268 } 269} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP} 270 271ifcapable tempdb { 272 do_test func-8.3 { 273 execsql { 274 CREATE TEMP TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC; 275 SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3; 276 } 277 } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP} 278} else { 279 do_test func-8.3 { 280 execsql { 281 CREATE TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC; 282 SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3; 283 } 284 } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP} 285} 286do_test func-8.4 { 287 execsql { 288 SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3; 289 } 290} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP} 291 292# How do you test the random() function in a meaningful, deterministic way? 293# 294do_test func-9.1 { 295 execsql { 296 SELECT random() is not null; 297 } 298} {1} 299do_test func-9.2 { 300 execsql { 301 SELECT typeof(random()); 302 } 303} {integer} 304do_test func-9.3 { 305 execsql { 306 SELECT randomblob(32) is not null; 307 } 308} {1} 309do_test func-9.4 { 310 execsql { 311 SELECT typeof(randomblob(32)); 312 } 313} {blob} 314do_test func-9.5 { 315 execsql { 316 SELECT length(randomblob(32)), length(randomblob(-5)), 317 length(randomblob(2000)) 318 } 319} {32 1 2000} 320 321# The "hex()" function was added in order to be able to render blobs 322# generated by randomblob(). So this seems like a good place to test 323# hex(). 324# 325do_test func-9.10 { 326 execsql {SELECT hex(x'00112233445566778899aAbBcCdDeEfF')} 327} {00112233445566778899AABBCCDDEEFF} 328do_test func-9.11 { 329 execsql {SELECT hex(replace('abcdefg','ef','12'))} 330} {61626364313267} 331do_test func-9.12 { 332 execsql {SELECT hex(replace('abcdefg','','12'))} 333} {{}} 334do_test func-9.13 { 335 execsql {SELECT hex(replace('aabcdefg','a','aaa'))} 336} {616161616161626364656667} 337 338# Use the "sqlite_register_test_function" TCL command which is part of 339# the text fixture in order to verify correct operation of some of 340# the user-defined SQL function APIs that are not used by the built-in 341# functions. 342# 343set ::DB [sqlite3_connection_pointer db] 344sqlite_register_test_function $::DB testfunc 345do_test func-10.1 { 346 catchsql { 347 SELECT testfunc(NULL,NULL); 348 } 349} {1 {first argument should be one of: int int64 string double null value}} 350do_test func-10.2 { 351 execsql { 352 SELECT testfunc( 353 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 354 'int', 1234 355 ); 356 } 357} {1234} 358do_test func-10.3 { 359 execsql { 360 SELECT testfunc( 361 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 362 'string', NULL 363 ); 364 } 365} {{}} 366do_test func-10.4 { 367 execsql { 368 SELECT testfunc( 369 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 370 'double', 1.234 371 ); 372 } 373} {1.234} 374do_test func-10.5 { 375 execsql { 376 SELECT testfunc( 377 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 378 'int', 1234, 379 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 380 'string', NULL, 381 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 382 'double', 1.234, 383 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 384 'int', 1234, 385 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 386 'string', NULL, 387 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 388 'double', 1.234 389 ); 390 } 391} {1.234} 392 393# Test the built-in sqlite_version(*) SQL function. 394# 395do_test func-11.1 { 396 execsql { 397 SELECT sqlite_version(*); 398 } 399} [sqlite3 -version] 400 401# Test that destructors passed to sqlite3 by calls to sqlite3_result_text() 402# etc. are called. These tests use two special user-defined functions 403# (implemented in func.c) only available in test builds. 404# 405# Function test_destructor() takes one argument and returns a copy of the 406# text form of that argument. A destructor is associated with the return 407# value. Function test_destructor_count() returns the number of outstanding 408# destructor calls for values returned by test_destructor(). 409# 410do_test func-12.1 { 411 execsql { 412 SELECT test_destructor('hello world'), test_destructor_count(); 413 } 414} {{hello world} 1} 415do_test func-12.2 { 416 execsql { 417 SELECT test_destructor_count(); 418 } 419} {0} 420do_test func-12.3 { 421 execsql { 422 SELECT test_destructor('hello')||' world', test_destructor_count(); 423 } 424} {{hello world} 0} 425do_test func-12.4 { 426 execsql { 427 SELECT test_destructor_count(); 428 } 429} {0} 430do_test func-12.5 { 431 execsql { 432 CREATE TABLE t4(x); 433 INSERT INTO t4 VALUES(test_destructor('hello')); 434 INSERT INTO t4 VALUES(test_destructor('world')); 435 SELECT min(test_destructor(x)), max(test_destructor(x)) FROM t4; 436 } 437} {hello world} 438do_test func-12.6 { 439 execsql { 440 SELECT test_destructor_count(); 441 } 442} {0} 443do_test func-12.7 { 444 execsql { 445 DROP TABLE t4; 446 } 447} {} 448 449# Test that the auxdata API for scalar functions works. This test uses 450# a special user-defined function only available in test builds, 451# test_auxdata(). Function test_auxdata() takes any number of arguments. 452do_test func-13.1 { 453 execsql { 454 SELECT test_auxdata('hello world'); 455 } 456} {0} 457 458do_test func-13.2 { 459 execsql { 460 CREATE TABLE t4(a, b); 461 INSERT INTO t4 VALUES('abc', 'def'); 462 INSERT INTO t4 VALUES('ghi', 'jkl'); 463 } 464} {} 465do_test func-13.3 { 466 execsql { 467 SELECT test_auxdata('hello world') FROM t4; 468 } 469} {0 1} 470do_test func-13.4 { 471 execsql { 472 SELECT test_auxdata('hello world', 123) FROM t4; 473 } 474} {{0 0} {1 1}} 475do_test func-13.5 { 476 execsql { 477 SELECT test_auxdata('hello world', a) FROM t4; 478 } 479} {{0 0} {1 0}} 480do_test func-13.6 { 481 execsql { 482 SELECT test_auxdata('hello'||'world', a) FROM t4; 483 } 484} {{0 0} {1 0}} 485 486# Test that auxilary data is preserved between calls for SQL variables. 487do_test func-13.7 { 488 set DB [sqlite3_connection_pointer db] 489 set sql "SELECT test_auxdata( ? , a ) FROM t4;" 490 set STMT [sqlite3_prepare $DB $sql -1 TAIL] 491 sqlite3_bind_text $STMT 1 hello -1 492 set res [list] 493 while { "SQLITE_ROW"==[sqlite3_step $STMT] } { 494 lappend res [sqlite3_column_text $STMT 0] 495 } 496 lappend res [sqlite3_finalize $STMT] 497} {{0 0} {1 0} SQLITE_OK} 498 499# Make sure that a function with a very long name is rejected 500do_test func-14.1 { 501 catch { 502 db function [string repeat X 254] {return "hello"} 503 } 504} {0} 505do_test func-14.2 { 506 catch { 507 db function [string repeat X 256] {return "hello"} 508 } 509} {1} 510 511do_test func-15.1 { 512 catchsql { 513 select test_error(NULL); 514 } 515} {1 {}} 516 517# Test the quote function for BLOB and NULL values. 518do_test func-16.1 { 519 execsql { 520 CREATE TABLE tbl2(a, b); 521 } 522 set STMT [sqlite3_prepare $::DB "INSERT INTO tbl2 VALUES(?, ?)" -1 TAIL] 523 sqlite3_bind_blob $::STMT 1 abc 3 524 sqlite3_step $::STMT 525 sqlite3_finalize $::STMT 526 execsql { 527 SELECT quote(a), quote(b) FROM tbl2; 528 } 529} {X'616263' NULL} 530 531# Correctly handle function error messages that include %. Ticket #1354 532# 533do_test func-17.1 { 534 proc testfunc1 args {error "Error %d with %s percents %p"} 535 db function testfunc1 ::testfunc1 536 catchsql { 537 SELECT testfunc1(1,2,3); 538 } 539} {1 {Error %d with %s percents %p}} 540 541# The SUM function should return integer results when all inputs are integer. 542# 543do_test func-18.1 { 544 execsql { 545 CREATE TABLE t5(x); 546 INSERT INTO t5 VALUES(1); 547 INSERT INTO t5 VALUES(-99); 548 INSERT INTO t5 VALUES(10000); 549 SELECT sum(x) FROM t5; 550 } 551} {9902} 552do_test func-18.2 { 553 execsql { 554 INSERT INTO t5 VALUES(0.0); 555 SELECT sum(x) FROM t5; 556 } 557} {9902.0} 558 559# The sum of nothing is NULL. But the sum of all NULLs is NULL. 560# 561# The TOTAL of nothing is 0.0. 562# 563do_test func-18.3 { 564 execsql { 565 DELETE FROM t5; 566 SELECT sum(x), total(x) FROM t5; 567 } 568} {{} 0.0} 569do_test func-18.4 { 570 execsql { 571 INSERT INTO t5 VALUES(NULL); 572 SELECT sum(x), total(x) FROM t5 573 } 574} {{} 0.0} 575do_test func-18.5 { 576 execsql { 577 INSERT INTO t5 VALUES(NULL); 578 SELECT sum(x), total(x) FROM t5 579 } 580} {{} 0.0} 581do_test func-18.6 { 582 execsql { 583 INSERT INTO t5 VALUES(123); 584 SELECT sum(x), total(x) FROM t5 585 } 586} {123 123.0} 587 588# Ticket #1664, #1669, #1670, #1674: An integer overflow on SUM causes 589# an error. The non-standard TOTAL() function continues to give a helpful 590# result. 591# 592do_test func-18.10 { 593 execsql { 594 CREATE TABLE t6(x INTEGER); 595 INSERT INTO t6 VALUES(1); 596 INSERT INTO t6 VALUES(1<<62); 597 SELECT sum(x) - ((1<<62)+1) from t6; 598 } 599} 0 600do_test func-18.11 { 601 execsql { 602 SELECT typeof(sum(x)) FROM t6 603 } 604} integer 605do_test func-18.12 { 606 catchsql { 607 INSERT INTO t6 VALUES(1<<62); 608 SELECT sum(x) - ((1<<62)*2.0+1) from t6; 609 } 610} {1 {integer overflow}} 611do_test func-18.13 { 612 execsql { 613 SELECT total(x) - ((1<<62)*2.0+1) FROM t6 614 } 615} 0.0 616do_test func-18.14 { 617 execsql { 618 SELECT sum(-9223372036854775805); 619 } 620} -9223372036854775805 621 622ifcapable compound&&subquery { 623 624do_test func-18.15 { 625 catchsql { 626 SELECT sum(x) FROM 627 (SELECT 9223372036854775807 AS x UNION ALL 628 SELECT 10 AS x); 629 } 630} {1 {integer overflow}} 631do_test func-18.16 { 632 catchsql { 633 SELECT sum(x) FROM 634 (SELECT 9223372036854775807 AS x UNION ALL 635 SELECT -10 AS x); 636 } 637} {0 9223372036854775797} 638do_test func-18.17 { 639 catchsql { 640 SELECT sum(x) FROM 641 (SELECT -9223372036854775807 AS x UNION ALL 642 SELECT 10 AS x); 643 } 644} {0 -9223372036854775797} 645do_test func-18.18 { 646 catchsql { 647 SELECT sum(x) FROM 648 (SELECT -9223372036854775807 AS x UNION ALL 649 SELECT -10 AS x); 650 } 651} {1 {integer overflow}} 652do_test func-18.19 { 653 catchsql { 654 SELECT sum(x) FROM (SELECT 9 AS x UNION ALL SELECT -10 AS x); 655 } 656} {0 -1} 657do_test func-18.20 { 658 catchsql { 659 SELECT sum(x) FROM (SELECT -9 AS x UNION ALL SELECT 10 AS x); 660 } 661} {0 1} 662do_test func-18.21 { 663 catchsql { 664 SELECT sum(x) FROM (SELECT -10 AS x UNION ALL SELECT 9 AS x); 665 } 666} {0 -1} 667do_test func-18.22 { 668 catchsql { 669 SELECT sum(x) FROM (SELECT 10 AS x UNION ALL SELECT -9 AS x); 670 } 671} {0 1} 672 673} ;# ifcapable compound&&subquery 674 675# Integer overflow on abs() 676# 677do_test func-18.31 { 678 catchsql { 679 SELECT abs(-9223372036854775807); 680 } 681} {0 9223372036854775807} 682do_test func-18.32 { 683 catchsql { 684 SELECT abs(-9223372036854775807-1); 685 } 686} {1 {integer overflow}} 687 688# The MATCH function exists but is only a stub and always throws an error. 689# 690do_test func-19.1 { 691 execsql { 692 SELECT match(a,b) FROM t1 WHERE 0; 693 } 694} {} 695do_test func-19.2 { 696 catchsql { 697 SELECT 'abc' MATCH 'xyz'; 698 } 699} {1 {unable to use function MATCH in the requested context}} 700do_test func-19.3 { 701 catchsql { 702 SELECT 'abc' NOT MATCH 'xyz'; 703 } 704} {1 {unable to use function MATCH in the requested context}} 705do_test func-19.4 { 706 catchsql { 707 SELECT match(1,2,3); 708 } 709} {1 {wrong number of arguments to function match()}} 710 711# Soundex tests. 712# 713if {![catch {db eval {SELECT soundex('hello')}}]} { 714 set i 0 715 foreach {name sdx} { 716 euler E460 717 EULER E460 718 Euler E460 719 ellery E460 720 gauss G200 721 ghosh G200 722 hilbert H416 723 Heilbronn H416 724 knuth K530 725 kant K530 726 Lloyd L300 727 LADD L300 728 Lukasiewicz L222 729 Lissajous L222 730 A A000 731 12345 ?000 732 } { 733 incr i 734 do_test func-20.$i { 735 execsql {SELECT soundex($name)} 736 } $sdx 737 } 738} 739 740# Tests of the REPLACE function. 741# 742do_test func-21.1 { 743 catchsql { 744 SELECT replace(1,2); 745 } 746} {1 {wrong number of arguments to function replace()}} 747do_test func-21.2 { 748 catchsql { 749 SELECT replace(1,2,3,4); 750 } 751} {1 {wrong number of arguments to function replace()}} 752do_test func-21.3 { 753 execsql { 754 SELECT typeof(replace("This is the main test string", NULL, "ALT")); 755 } 756} {null} 757do_test func-21.4 { 758 execsql { 759 SELECT typeof(replace(NULL, "main", "ALT")); 760 } 761} {null} 762do_test func-21.5 { 763 execsql { 764 SELECT typeof(replace("This is the main test string", "main", NULL)); 765 } 766} {null} 767do_test func-21.6 { 768 execsql { 769 SELECT replace("This is the main test string", "main", "ALT"); 770 } 771} {{This is the ALT test string}} 772do_test func-21.7 { 773 execsql { 774 SELECT replace("This is the main test string", "main", "larger-main"); 775 } 776} {{This is the larger-main test string}} 777do_test func-21.8 { 778 execsql { 779 SELECT replace("aaaaaaa", "a", "0123456789"); 780 } 781} {0123456789012345678901234567890123456789012345678901234567890123456789} 782 783# Tests for the TRIM, LTRIM and RTRIM functions. 784# 785do_test func-22.1 { 786 catchsql {SELECT trim(1,2,3)} 787} {1 {wrong number of arguments to function trim()}} 788do_test func-22.2 { 789 catchsql {SELECT ltrim(1,2,3)} 790} {1 {wrong number of arguments to function ltrim()}} 791do_test func-22.3 { 792 catchsql {SELECT rtrim(1,2,3)} 793} {1 {wrong number of arguments to function rtrim()}} 794do_test func-22.4 { 795 execsql {SELECT trim(' hi ');} 796} {hi} 797do_test func-22.5 { 798 execsql {SELECT ltrim(' hi ');} 799} {{hi }} 800do_test func-22.6 { 801 execsql {SELECT rtrim(' hi ');} 802} {{ hi}} 803do_test func-22.7 { 804 execsql {SELECT trim(' hi ','xyz');} 805} {{ hi }} 806do_test func-22.8 { 807 execsql {SELECT ltrim(' hi ','xyz');} 808} {{ hi }} 809do_test func-22.9 { 810 execsql {SELECT rtrim(' hi ','xyz');} 811} {{ hi }} 812do_test func-22.10 { 813 execsql {SELECT trim('xyxzy hi zzzy','xyz');} 814} {{ hi }} 815do_test func-22.11 { 816 execsql {SELECT ltrim('xyxzy hi zzzy','xyz');} 817} {{ hi zzzy}} 818do_test func-22.12 { 819 execsql {SELECT rtrim('xyxzy hi zzzy','xyz');} 820} {{xyxzy hi }} 821do_test func-22.13 { 822 execsql {SELECT trim(' hi ','');} 823} {{ hi }} 824do_test func-22.14 { 825 execsql {SELECT hex(trim(x'c280e1bfbff48fbfbf6869',x'6162e1bfbfc280'))} 826} {F48FBFBF6869} 827do_test func-22.15 { 828 execsql {SELECT hex(trim(x'6869c280e1bfbff48fbfbf61', 829 x'6162e1bfbfc280f48fbfbf'))} 830} {6869} 831do_test func-22.16 { 832 execsql {SELECT hex(trim(x'ceb1ceb2ceb3',x'ceb1'));} 833} {CEB2CEB3} 834do_test func-22.20 { 835 execsql {SELECT typeof(trim(NULL));} 836} {null} 837do_test func-22.21 { 838 execsql {SELECT typeof(trim(NULL,'xyz'));} 839} {null} 840do_test func-22.22 { 841 execsql {SELECT typeof(trim('hello',NULL));} 842} {null} 843 844finish_test 845