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