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.93 2009/06/19 16:44:41 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# 152ifcapable !floatingpoint { 153 do_test func-4.1 { 154 execsql { 155 CREATE TABLE t1(a,b,c); 156 INSERT INTO t1 VALUES(1,2,3); 157 INSERT INTO t1 VALUES(2,12345678901234,-1234567890); 158 INSERT INTO t1 VALUES(3,-2,-5); 159 } 160 catchsql {SELECT abs(a,b) FROM t1} 161 } {1 {wrong number of arguments to function abs()}} 162} 163ifcapable floatingpoint { 164 do_test func-4.1 { 165 execsql { 166 CREATE TABLE t1(a,b,c); 167 INSERT INTO t1 VALUES(1,2,3); 168 INSERT INTO t1 VALUES(2,1.2345678901234,-12345.67890); 169 INSERT INTO t1 VALUES(3,-2,-5); 170 } 171 catchsql {SELECT abs(a,b) FROM t1} 172 } {1 {wrong number of arguments to function abs()}} 173} 174do_test func-4.2 { 175 catchsql {SELECT abs() FROM t1} 176} {1 {wrong number of arguments to function abs()}} 177ifcapable floatingpoint { 178 do_test func-4.3 { 179 catchsql {SELECT abs(b) FROM t1 ORDER BY a} 180 } {0 {2 1.2345678901234 2}} 181 do_test func-4.4 { 182 catchsql {SELECT abs(c) FROM t1 ORDER BY a} 183 } {0 {3 12345.6789 5}} 184} 185ifcapable !floatingpoint { 186 if {[working_64bit_int]} { 187 do_test func-4.3 { 188 catchsql {SELECT abs(b) FROM t1 ORDER BY a} 189 } {0 {2 12345678901234 2}} 190 } 191 do_test func-4.4 { 192 catchsql {SELECT abs(c) FROM t1 ORDER BY a} 193 } {0 {3 1234567890 5}} 194} 195do_test func-4.4.1 { 196 execsql {SELECT abs(a) FROM t2} 197} {1 {} 345 {} 67890} 198do_test func-4.4.2 { 199 execsql {SELECT abs(t1) FROM tbl1} 200} {0.0 0.0 0.0 0.0 0.0} 201 202ifcapable floatingpoint { 203 do_test func-4.5 { 204 catchsql {SELECT round(a,b,c) FROM t1} 205 } {1 {wrong number of arguments to function round()}} 206 do_test func-4.6 { 207 catchsql {SELECT round(b,2) FROM t1 ORDER BY b} 208 } {0 {-2.0 1.23 2.0}} 209 do_test func-4.7 { 210 catchsql {SELECT round(b,0) FROM t1 ORDER BY a} 211 } {0 {2.0 1.0 -2.0}} 212 do_test func-4.8 { 213 catchsql {SELECT round(c) FROM t1 ORDER BY a} 214 } {0 {3.0 -12346.0 -5.0}} 215 do_test func-4.9 { 216 catchsql {SELECT round(c,a) FROM t1 ORDER BY a} 217 } {0 {3.0 -12345.68 -5.0}} 218 do_test func-4.10 { 219 catchsql {SELECT 'x' || round(c,a) || 'y' FROM t1 ORDER BY a} 220 } {0 {x3.0y x-12345.68y x-5.0y}} 221 do_test func-4.11 { 222 catchsql {SELECT round() FROM t1 ORDER BY a} 223 } {1 {wrong number of arguments to function round()}} 224 do_test func-4.12 { 225 execsql {SELECT coalesce(round(a,2),'nil') FROM t2} 226 } {1.0 nil 345.0 nil 67890.0} 227 do_test func-4.13 { 228 execsql {SELECT round(t1,2) FROM tbl1} 229 } {0.0 0.0 0.0 0.0 0.0} 230 do_test func-4.14 { 231 execsql {SELECT typeof(round(5.1,1));} 232 } {real} 233 do_test func-4.15 { 234 execsql {SELECT typeof(round(5.1));} 235 } {real} 236 do_test func-4.16 { 237 catchsql {SELECT round(b,2.0) FROM t1 ORDER BY b} 238 } {0 {-2.0 1.23 2.0}} 239} 240 241# Test the upper() and lower() functions 242# 243do_test func-5.1 { 244 execsql {SELECT upper(t1) FROM tbl1} 245} {THIS PROGRAM IS FREE SOFTWARE} 246do_test func-5.2 { 247 execsql {SELECT lower(upper(t1)) FROM tbl1} 248} {this program is free software} 249do_test func-5.3 { 250 execsql {SELECT upper(a), lower(a) FROM t2} 251} {1 1 {} {} 345 345 {} {} 67890 67890} 252ifcapable !icu { 253 do_test func-5.4 { 254 catchsql {SELECT upper(a,5) FROM t2} 255 } {1 {wrong number of arguments to function upper()}} 256} 257do_test func-5.5 { 258 catchsql {SELECT upper(*) FROM t2} 259} {1 {wrong number of arguments to function upper()}} 260 261# Test the coalesce() and nullif() functions 262# 263do_test func-6.1 { 264 execsql {SELECT coalesce(a,'xyz') FROM t2} 265} {1 xyz 345 xyz 67890} 266do_test func-6.2 { 267 execsql {SELECT coalesce(upper(a),'nil') FROM t2} 268} {1 nil 345 nil 67890} 269do_test func-6.3 { 270 execsql {SELECT coalesce(nullif(1,1),'nil')} 271} {nil} 272do_test func-6.4 { 273 execsql {SELECT coalesce(nullif(1,2),'nil')} 274} {1} 275do_test func-6.5 { 276 execsql {SELECT coalesce(nullif(1,NULL),'nil')} 277} {1} 278 279 280# Test the last_insert_rowid() function 281# 282do_test func-7.1 { 283 execsql {SELECT last_insert_rowid()} 284} [db last_insert_rowid] 285 286# Tests for aggregate functions and how they handle NULLs. 287# 288ifcapable floatingpoint { 289 do_test func-8.1 { 290 ifcapable explain { 291 execsql {EXPLAIN SELECT sum(a) FROM t2;} 292 } 293 execsql { 294 SELECT sum(a), count(a), round(avg(a),2), min(a), max(a), count(*) FROM t2; 295 } 296 } {68236 3 22745.33 1 67890 5} 297} 298ifcapable !floatingpoint { 299 do_test func-8.1 { 300 ifcapable explain { 301 execsql {EXPLAIN SELECT sum(a) FROM t2;} 302 } 303 execsql { 304 SELECT sum(a), count(a), avg(a), min(a), max(a), count(*) FROM t2; 305 } 306 } {68236 3 22745.0 1 67890 5} 307} 308do_test func-8.2 { 309 execsql { 310 SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t2; 311 } 312} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP} 313 314ifcapable tempdb { 315 do_test func-8.3 { 316 execsql { 317 CREATE TEMP TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC; 318 SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3; 319 } 320 } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP} 321} else { 322 do_test func-8.3 { 323 execsql { 324 CREATE TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC; 325 SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3; 326 } 327 } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP} 328} 329do_test func-8.4 { 330 execsql { 331 SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3; 332 } 333} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP} 334ifcapable compound { 335 do_test func-8.5 { 336 execsql { 337 SELECT sum(x) FROM (SELECT '9223372036' || '854775807' AS x 338 UNION ALL SELECT -9223372036854775807) 339 } 340 } {0} 341 do_test func-8.6 { 342 execsql { 343 SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775807' AS x 344 UNION ALL SELECT -9223372036854775807) 345 } 346 } {integer} 347 do_test func-8.7 { 348 execsql { 349 SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775808' AS x 350 UNION ALL SELECT -9223372036854775807) 351 } 352 } {real} 353ifcapable floatingpoint { 354 do_test func-8.8 { 355 execsql { 356 SELECT sum(x)>0.0 FROM (SELECT '9223372036' || '854775808' AS x 357 UNION ALL SELECT -9223372036850000000) 358 } 359 } {1} 360} 361ifcapable !floatingpoint { 362 do_test func-8.8 { 363 execsql { 364 SELECT sum(x)>0 FROM (SELECT '9223372036' || '854775808' AS x 365 UNION ALL SELECT -9223372036850000000) 366 } 367 } {1} 368} 369} 370 371# How do you test the random() function in a meaningful, deterministic way? 372# 373do_test func-9.1 { 374 execsql { 375 SELECT random() is not null; 376 } 377} {1} 378do_test func-9.2 { 379 execsql { 380 SELECT typeof(random()); 381 } 382} {integer} 383do_test func-9.3 { 384 execsql { 385 SELECT randomblob(32) is not null; 386 } 387} {1} 388do_test func-9.4 { 389 execsql { 390 SELECT typeof(randomblob(32)); 391 } 392} {blob} 393do_test func-9.5 { 394 execsql { 395 SELECT length(randomblob(32)), length(randomblob(-5)), 396 length(randomblob(2000)) 397 } 398} {32 1 2000} 399 400# The "hex()" function was added in order to be able to render blobs 401# generated by randomblob(). So this seems like a good place to test 402# hex(). 403# 404ifcapable bloblit { 405 do_test func-9.10 { 406 execsql {SELECT hex(x'00112233445566778899aAbBcCdDeEfF')} 407 } {00112233445566778899AABBCCDDEEFF} 408} 409set encoding [db one {PRAGMA encoding}] 410if {$encoding=="UTF-16le"} { 411 do_test func-9.11-utf16le { 412 execsql {SELECT hex(replace('abcdefg','ef','12'))} 413 } {6100620063006400310032006700} 414 do_test func-9.12-utf16le { 415 execsql {SELECT hex(replace('abcdefg','','12'))} 416 } {6100620063006400650066006700} 417 do_test func-9.13-utf16le { 418 execsql {SELECT hex(replace('aabcdefg','a','aaa'))} 419 } {610061006100610061006100620063006400650066006700} 420} elseif {$encoding=="UTF-8"} { 421 do_test func-9.11-utf8 { 422 execsql {SELECT hex(replace('abcdefg','ef','12'))} 423 } {61626364313267} 424 do_test func-9.12-utf8 { 425 execsql {SELECT hex(replace('abcdefg','','12'))} 426 } {61626364656667} 427 do_test func-9.13-utf8 { 428 execsql {SELECT hex(replace('aabcdefg','a','aaa'))} 429 } {616161616161626364656667} 430} 431 432# Use the "sqlite_register_test_function" TCL command which is part of 433# the text fixture in order to verify correct operation of some of 434# the user-defined SQL function APIs that are not used by the built-in 435# functions. 436# 437set ::DB [sqlite3_connection_pointer db] 438sqlite_register_test_function $::DB testfunc 439do_test func-10.1 { 440 catchsql { 441 SELECT testfunc(NULL,NULL); 442 } 443} {1 {first argument should be one of: int int64 string double null value}} 444do_test func-10.2 { 445 execsql { 446 SELECT testfunc( 447 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 448 'int', 1234 449 ); 450 } 451} {1234} 452do_test func-10.3 { 453 execsql { 454 SELECT testfunc( 455 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 456 'string', NULL 457 ); 458 } 459} {{}} 460 461ifcapable floatingpoint { 462 do_test func-10.4 { 463 execsql { 464 SELECT testfunc( 465 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 466 'double', 1.234 467 ); 468 } 469 } {1.234} 470 do_test func-10.5 { 471 execsql { 472 SELECT testfunc( 473 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 474 'int', 1234, 475 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 476 'string', NULL, 477 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 478 'double', 1.234, 479 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 480 'int', 1234, 481 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 482 'string', NULL, 483 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 484 'double', 1.234 485 ); 486 } 487 } {1.234} 488} 489 490# Test the built-in sqlite_version(*) SQL function. 491# 492do_test func-11.1 { 493 execsql { 494 SELECT sqlite_version(*); 495 } 496} [sqlite3 -version] 497 498# Test that destructors passed to sqlite3 by calls to sqlite3_result_text() 499# etc. are called. These tests use two special user-defined functions 500# (implemented in func.c) only available in test builds. 501# 502# Function test_destructor() takes one argument and returns a copy of the 503# text form of that argument. A destructor is associated with the return 504# value. Function test_destructor_count() returns the number of outstanding 505# destructor calls for values returned by test_destructor(). 506# 507if {[db eval {PRAGMA encoding}]=="UTF-8"} { 508 do_test func-12.1-utf8 { 509 execsql { 510 SELECT test_destructor('hello world'), test_destructor_count(); 511 } 512 } {{hello world} 1} 513} else { 514 ifcapable {utf16} { 515 do_test func-12.1-utf16 { 516 execsql { 517 SELECT test_destructor16('hello world'), test_destructor_count(); 518 } 519 } {{hello world} 1} 520 } 521} 522do_test func-12.2 { 523 execsql { 524 SELECT test_destructor_count(); 525 } 526} {0} 527do_test func-12.3 { 528 execsql { 529 SELECT test_destructor('hello')||' world' 530 } 531} {{hello world}} 532do_test func-12.4 { 533 execsql { 534 SELECT test_destructor_count(); 535 } 536} {0} 537do_test func-12.5 { 538 execsql { 539 CREATE TABLE t4(x); 540 INSERT INTO t4 VALUES(test_destructor('hello')); 541 INSERT INTO t4 VALUES(test_destructor('world')); 542 SELECT min(test_destructor(x)), max(test_destructor(x)) FROM t4; 543 } 544} {hello world} 545do_test func-12.6 { 546 execsql { 547 SELECT test_destructor_count(); 548 } 549} {0} 550do_test func-12.7 { 551 execsql { 552 DROP TABLE t4; 553 } 554} {} 555 556 557# Test that the auxdata API for scalar functions works. This test uses 558# a special user-defined function only available in test builds, 559# test_auxdata(). Function test_auxdata() takes any number of arguments. 560do_test func-13.1 { 561 execsql { 562 SELECT test_auxdata('hello world'); 563 } 564} {0} 565 566do_test func-13.2 { 567 execsql { 568 CREATE TABLE t4(a, b); 569 INSERT INTO t4 VALUES('abc', 'def'); 570 INSERT INTO t4 VALUES('ghi', 'jkl'); 571 } 572} {} 573do_test func-13.3 { 574 execsql { 575 SELECT test_auxdata('hello world') FROM t4; 576 } 577} {0 1} 578do_test func-13.4 { 579 execsql { 580 SELECT test_auxdata('hello world', 123) FROM t4; 581 } 582} {{0 0} {1 1}} 583do_test func-13.5 { 584 execsql { 585 SELECT test_auxdata('hello world', a) FROM t4; 586 } 587} {{0 0} {1 0}} 588do_test func-13.6 { 589 execsql { 590 SELECT test_auxdata('hello'||'world', a) FROM t4; 591 } 592} {{0 0} {1 0}} 593 594# Test that auxilary data is preserved between calls for SQL variables. 595do_test func-13.7 { 596 set DB [sqlite3_connection_pointer db] 597 set sql "SELECT test_auxdata( ? , a ) FROM t4;" 598 set STMT [sqlite3_prepare $DB $sql -1 TAIL] 599 sqlite3_bind_text $STMT 1 hello\000 -1 600 set res [list] 601 while { "SQLITE_ROW"==[sqlite3_step $STMT] } { 602 lappend res [sqlite3_column_text $STMT 0] 603 } 604 lappend res [sqlite3_finalize $STMT] 605} {{0 0} {1 0} SQLITE_OK} 606 607# Make sure that a function with a very long name is rejected 608do_test func-14.1 { 609 catch { 610 db function [string repeat X 254] {return "hello"} 611 } 612} {0} 613do_test func-14.2 { 614 catch { 615 db function [string repeat X 256] {return "hello"} 616 } 617} {1} 618 619do_test func-15.1 { 620 catchsql {select test_error(NULL)} 621} {1 {}} 622do_test func-15.2 { 623 catchsql {select test_error('this is the error message')} 624} {1 {this is the error message}} 625do_test func-15.3 { 626 catchsql {select test_error('this is the error message',12)} 627} {1 {this is the error message}} 628do_test func-15.4 { 629 db errorcode 630} {12} 631 632# Test the quote function for BLOB and NULL values. 633do_test func-16.1 { 634 execsql { 635 CREATE TABLE tbl2(a, b); 636 } 637 set STMT [sqlite3_prepare $::DB "INSERT INTO tbl2 VALUES(?, ?)" -1 TAIL] 638 sqlite3_bind_blob $::STMT 1 abc 3 639 sqlite3_step $::STMT 640 sqlite3_finalize $::STMT 641 execsql { 642 SELECT quote(a), quote(b) FROM tbl2; 643 } 644} {X'616263' NULL} 645 646# Correctly handle function error messages that include %. Ticket #1354 647# 648do_test func-17.1 { 649 proc testfunc1 args {error "Error %d with %s percents %p"} 650 db function testfunc1 ::testfunc1 651 catchsql { 652 SELECT testfunc1(1,2,3); 653 } 654} {1 {Error %d with %s percents %p}} 655 656# The SUM function should return integer results when all inputs are integer. 657# 658do_test func-18.1 { 659 execsql { 660 CREATE TABLE t5(x); 661 INSERT INTO t5 VALUES(1); 662 INSERT INTO t5 VALUES(-99); 663 INSERT INTO t5 VALUES(10000); 664 SELECT sum(x) FROM t5; 665 } 666} {9902} 667ifcapable floatingpoint { 668 do_test func-18.2 { 669 execsql { 670 INSERT INTO t5 VALUES(0.0); 671 SELECT sum(x) FROM t5; 672 } 673 } {9902.0} 674} 675 676# The sum of nothing is NULL. But the sum of all NULLs is NULL. 677# 678# The TOTAL of nothing is 0.0. 679# 680do_test func-18.3 { 681 execsql { 682 DELETE FROM t5; 683 SELECT sum(x), total(x) FROM t5; 684 } 685} {{} 0.0} 686do_test func-18.4 { 687 execsql { 688 INSERT INTO t5 VALUES(NULL); 689 SELECT sum(x), total(x) FROM t5 690 } 691} {{} 0.0} 692do_test func-18.5 { 693 execsql { 694 INSERT INTO t5 VALUES(NULL); 695 SELECT sum(x), total(x) FROM t5 696 } 697} {{} 0.0} 698do_test func-18.6 { 699 execsql { 700 INSERT INTO t5 VALUES(123); 701 SELECT sum(x), total(x) FROM t5 702 } 703} {123 123.0} 704 705# Ticket #1664, #1669, #1670, #1674: An integer overflow on SUM causes 706# an error. The non-standard TOTAL() function continues to give a helpful 707# result. 708# 709do_test func-18.10 { 710 execsql { 711 CREATE TABLE t6(x INTEGER); 712 INSERT INTO t6 VALUES(1); 713 INSERT INTO t6 VALUES(1<<62); 714 SELECT sum(x) - ((1<<62)+1) from t6; 715 } 716} 0 717do_test func-18.11 { 718 execsql { 719 SELECT typeof(sum(x)) FROM t6 720 } 721} integer 722ifcapable floatingpoint { 723 do_test func-18.12 { 724 catchsql { 725 INSERT INTO t6 VALUES(1<<62); 726 SELECT sum(x) - ((1<<62)*2.0+1) from t6; 727 } 728 } {1 {integer overflow}} 729 do_test func-18.13 { 730 execsql { 731 SELECT total(x) - ((1<<62)*2.0+1) FROM t6 732 } 733 } 0.0 734} 735ifcapable !floatingpoint { 736 do_test func-18.12 { 737 catchsql { 738 INSERT INTO t6 VALUES(1<<62); 739 SELECT sum(x) - ((1<<62)*2+1) from t6; 740 } 741 } {1 {integer overflow}} 742 do_test func-18.13 { 743 execsql { 744 SELECT total(x) - ((1<<62)*2+1) FROM t6 745 } 746 } 0.0 747} 748if {[working_64bit_int]} { 749 do_test func-18.14 { 750 execsql { 751 SELECT sum(-9223372036854775805); 752 } 753 } -9223372036854775805 754} 755ifcapable compound&&subquery { 756 757do_test func-18.15 { 758 catchsql { 759 SELECT sum(x) FROM 760 (SELECT 9223372036854775807 AS x UNION ALL 761 SELECT 10 AS x); 762 } 763} {1 {integer overflow}} 764if {[working_64bit_int]} { 765 do_test func-18.16 { 766 catchsql { 767 SELECT sum(x) FROM 768 (SELECT 9223372036854775807 AS x UNION ALL 769 SELECT -10 AS x); 770 } 771 } {0 9223372036854775797} 772 do_test func-18.17 { 773 catchsql { 774 SELECT sum(x) FROM 775 (SELECT -9223372036854775807 AS x UNION ALL 776 SELECT 10 AS x); 777 } 778 } {0 -9223372036854775797} 779} 780do_test func-18.18 { 781 catchsql { 782 SELECT sum(x) FROM 783 (SELECT -9223372036854775807 AS x UNION ALL 784 SELECT -10 AS x); 785 } 786} {1 {integer overflow}} 787do_test func-18.19 { 788 catchsql { 789 SELECT sum(x) FROM (SELECT 9 AS x UNION ALL SELECT -10 AS x); 790 } 791} {0 -1} 792do_test func-18.20 { 793 catchsql { 794 SELECT sum(x) FROM (SELECT -9 AS x UNION ALL SELECT 10 AS x); 795 } 796} {0 1} 797do_test func-18.21 { 798 catchsql { 799 SELECT sum(x) FROM (SELECT -10 AS x UNION ALL SELECT 9 AS x); 800 } 801} {0 -1} 802do_test func-18.22 { 803 catchsql { 804 SELECT sum(x) FROM (SELECT 10 AS x UNION ALL SELECT -9 AS x); 805 } 806} {0 1} 807 808} ;# ifcapable compound&&subquery 809 810# Integer overflow on abs() 811# 812if {[working_64bit_int]} { 813 do_test func-18.31 { 814 catchsql { 815 SELECT abs(-9223372036854775807); 816 } 817 } {0 9223372036854775807} 818} 819do_test func-18.32 { 820 catchsql { 821 SELECT abs(-9223372036854775807-1); 822 } 823} {1 {integer overflow}} 824 825# The MATCH function exists but is only a stub and always throws an error. 826# 827do_test func-19.1 { 828 execsql { 829 SELECT match(a,b) FROM t1 WHERE 0; 830 } 831} {} 832do_test func-19.2 { 833 catchsql { 834 SELECT 'abc' MATCH 'xyz'; 835 } 836} {1 {unable to use function MATCH in the requested context}} 837do_test func-19.3 { 838 catchsql { 839 SELECT 'abc' NOT MATCH 'xyz'; 840 } 841} {1 {unable to use function MATCH in the requested context}} 842do_test func-19.4 { 843 catchsql { 844 SELECT match(1,2,3); 845 } 846} {1 {wrong number of arguments to function match()}} 847 848# Soundex tests. 849# 850if {![catch {db eval {SELECT soundex('hello')}}]} { 851 set i 0 852 foreach {name sdx} { 853 euler E460 854 EULER E460 855 Euler E460 856 ellery E460 857 gauss G200 858 ghosh G200 859 hilbert H416 860 Heilbronn H416 861 knuth K530 862 kant K530 863 Lloyd L300 864 LADD L300 865 Lukasiewicz L222 866 Lissajous L222 867 A A000 868 12345 ?000 869 } { 870 incr i 871 do_test func-20.$i { 872 execsql {SELECT soundex($name)} 873 } $sdx 874 } 875} 876 877# Tests of the REPLACE function. 878# 879do_test func-21.1 { 880 catchsql { 881 SELECT replace(1,2); 882 } 883} {1 {wrong number of arguments to function replace()}} 884do_test func-21.2 { 885 catchsql { 886 SELECT replace(1,2,3,4); 887 } 888} {1 {wrong number of arguments to function replace()}} 889do_test func-21.3 { 890 execsql { 891 SELECT typeof(replace("This is the main test string", NULL, "ALT")); 892 } 893} {null} 894do_test func-21.4 { 895 execsql { 896 SELECT typeof(replace(NULL, "main", "ALT")); 897 } 898} {null} 899do_test func-21.5 { 900 execsql { 901 SELECT typeof(replace("This is the main test string", "main", NULL)); 902 } 903} {null} 904do_test func-21.6 { 905 execsql { 906 SELECT replace("This is the main test string", "main", "ALT"); 907 } 908} {{This is the ALT test string}} 909do_test func-21.7 { 910 execsql { 911 SELECT replace("This is the main test string", "main", "larger-main"); 912 } 913} {{This is the larger-main test string}} 914do_test func-21.8 { 915 execsql { 916 SELECT replace("aaaaaaa", "a", "0123456789"); 917 } 918} {0123456789012345678901234567890123456789012345678901234567890123456789} 919 920ifcapable tclvar { 921 do_test func-21.9 { 922 # Attempt to exploit a buffer-overflow that at one time existed 923 # in the REPLACE function. 924 set ::str "[string repeat A 29998]CC[string repeat A 35537]" 925 set ::rep [string repeat B 65536] 926 execsql { 927 SELECT LENGTH(REPLACE($::str, 'C', $::rep)); 928 } 929 } [expr 29998 + 2*65536 + 35537] 930} 931 932# Tests for the TRIM, LTRIM and RTRIM functions. 933# 934do_test func-22.1 { 935 catchsql {SELECT trim(1,2,3)} 936} {1 {wrong number of arguments to function trim()}} 937do_test func-22.2 { 938 catchsql {SELECT ltrim(1,2,3)} 939} {1 {wrong number of arguments to function ltrim()}} 940do_test func-22.3 { 941 catchsql {SELECT rtrim(1,2,3)} 942} {1 {wrong number of arguments to function rtrim()}} 943do_test func-22.4 { 944 execsql {SELECT trim(' hi ');} 945} {hi} 946do_test func-22.5 { 947 execsql {SELECT ltrim(' hi ');} 948} {{hi }} 949do_test func-22.6 { 950 execsql {SELECT rtrim(' hi ');} 951} {{ hi}} 952do_test func-22.7 { 953 execsql {SELECT trim(' hi ','xyz');} 954} {{ hi }} 955do_test func-22.8 { 956 execsql {SELECT ltrim(' hi ','xyz');} 957} {{ hi }} 958do_test func-22.9 { 959 execsql {SELECT rtrim(' hi ','xyz');} 960} {{ hi }} 961do_test func-22.10 { 962 execsql {SELECT trim('xyxzy hi zzzy','xyz');} 963} {{ hi }} 964do_test func-22.11 { 965 execsql {SELECT ltrim('xyxzy hi zzzy','xyz');} 966} {{ hi zzzy}} 967do_test func-22.12 { 968 execsql {SELECT rtrim('xyxzy hi zzzy','xyz');} 969} {{xyxzy hi }} 970do_test func-22.13 { 971 execsql {SELECT trim(' hi ','');} 972} {{ hi }} 973if {[db one {PRAGMA encoding}]=="UTF-8"} { 974 do_test func-22.14 { 975 execsql {SELECT hex(trim(x'c280e1bfbff48fbfbf6869',x'6162e1bfbfc280'))} 976 } {F48FBFBF6869} 977 do_test func-22.15 { 978 execsql {SELECT hex(trim(x'6869c280e1bfbff48fbfbf61', 979 x'6162e1bfbfc280f48fbfbf'))} 980 } {6869} 981 do_test func-22.16 { 982 execsql {SELECT hex(trim(x'ceb1ceb2ceb3',x'ceb1'));} 983 } {CEB2CEB3} 984} 985do_test func-22.20 { 986 execsql {SELECT typeof(trim(NULL));} 987} {null} 988do_test func-22.21 { 989 execsql {SELECT typeof(trim(NULL,'xyz'));} 990} {null} 991do_test func-22.22 { 992 execsql {SELECT typeof(trim('hello',NULL));} 993} {null} 994 995# This is to test the deprecated sqlite3_aggregate_count() API. 996# 997ifcapable deprecated { 998 do_test func-23.1 { 999 sqlite3_create_aggregate db 1000 execsql { 1001 SELECT legacy_count() FROM t6; 1002 } 1003 } {3} 1004} 1005 1006# The group_concat() function. 1007# 1008do_test func-24.1 { 1009 execsql { 1010 SELECT group_concat(t1) FROM tbl1 1011 } 1012} {this,program,is,free,software} 1013do_test func-24.2 { 1014 execsql { 1015 SELECT group_concat(t1,' ') FROM tbl1 1016 } 1017} {{this program is free software}} 1018do_test func-24.3 { 1019 execsql { 1020 SELECT group_concat(t1,' ' || rowid || ' ') FROM tbl1 1021 } 1022} {{this 2 program 3 is 4 free 5 software}} 1023do_test func-24.4 { 1024 execsql { 1025 SELECT group_concat(NULL,t1) FROM tbl1 1026 } 1027} {{}} 1028do_test func-24.5 { 1029 execsql { 1030 SELECT group_concat(t1,NULL) FROM tbl1 1031 } 1032} {thisprogramisfreesoftware} 1033do_test func-24.6 { 1034 execsql { 1035 SELECT 'BEGIN-'||group_concat(t1) FROM tbl1 1036 } 1037} {BEGIN-this,program,is,free,software} 1038 1039# Ticket #3179: Make sure aggregate functions can take many arguments. 1040# None of the built-in aggregates do this, so use the md5sum() from the 1041# test extensions. 1042# 1043unset -nocomplain midargs 1044set midargs {} 1045unset -nocomplain midres 1046set midres {} 1047unset -nocomplain result 1048for {set i 1} {$i<[sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG -1]} {incr i} { 1049 append midargs ,'/$i' 1050 append midres /$i 1051 set result [md5 \ 1052 "this${midres}program${midres}is${midres}free${midres}software${midres}"] 1053 set sql "SELECT md5sum(t1$midargs) FROM tbl1" 1054 do_test func-24.7.$i { 1055 db eval $::sql 1056 } $result 1057} 1058 1059# Ticket #3806. If the initial string in a group_concat is an empty 1060# string, the separator that follows should still be present. 1061# 1062do_test func-24.8 { 1063 execsql { 1064 SELECT group_concat(CASE t1 WHEN 'this' THEN '' ELSE t1 END) FROM tbl1 1065 } 1066} {,program,is,free,software} 1067do_test func-24.9 { 1068 execsql { 1069 SELECT group_concat(CASE WHEN t1!='software' THEN '' ELSE t1 END) FROM tbl1 1070 } 1071} {,,,,software} 1072 1073# Ticket #3923. Initial empty strings have a separator. But initial 1074# NULLs do not. 1075# 1076do_test func-24.10 { 1077 execsql { 1078 SELECT group_concat(CASE t1 WHEN 'this' THEN null ELSE t1 END) FROM tbl1 1079 } 1080} {program,is,free,software} 1081do_test func-24.11 { 1082 execsql { 1083 SELECT group_concat(CASE WHEN t1!='software' THEN null ELSE t1 END) FROM tbl1 1084 } 1085} {software} 1086do_test func-24.12 { 1087 execsql { 1088 SELECT group_concat(CASE t1 WHEN 'this' THEN '' 1089 WHEN 'program' THEN null ELSE t1 END) FROM tbl1 1090 } 1091} {,is,free,software} 1092 1093 1094# Use the test_isolation function to make sure that type conversions 1095# on function arguments do not effect subsequent arguments. 1096# 1097do_test func-25.1 { 1098 execsql {SELECT test_isolation(t1,t1) FROM tbl1} 1099} {this program is free software} 1100 1101# Try to misuse the sqlite3_create_function() interface. Verify that 1102# errors are returned. 1103# 1104do_test func-26.1 { 1105 abuse_create_function db 1106} {} 1107 1108# The previous test (func-26.1) registered a function with a very long 1109# function name that takes many arguments and always returns NULL. Verify 1110# that this function works correctly. 1111# 1112do_test func-26.2 { 1113 set a {} 1114 for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG} {incr i} { 1115 lappend a $i 1116 } 1117 db eval " 1118 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 ,]); 1119 " 1120} {{}} 1121do_test func-26.3 { 1122 set a {} 1123 for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG+1} {incr i} { 1124 lappend a $i 1125 } 1126 catchsql " 1127 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 ,]); 1128 " 1129} {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}} 1130do_test func-26.4 { 1131 set a {} 1132 for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG-1} {incr i} { 1133 lappend a $i 1134 } 1135 catchsql " 1136 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 ,]); 1137 " 1138} {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()}} 1139do_test func-26.5 { 1140 catchsql " 1141 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); 1142 " 1143} {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}} 1144do_test func-26.6 { 1145 catchsql " 1146 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); 1147 " 1148} {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}} 1149 1150do_test func-27.1 { 1151 catchsql {SELECT coalesce()} 1152} {1 {wrong number of arguments to function coalesce()}} 1153do_test func-27.2 { 1154 catchsql {SELECT coalesce(1)} 1155} {1 {wrong number of arguments to function coalesce()}} 1156do_test func-27.3 { 1157 catchsql {SELECT coalesce(1,2)} 1158} {0 1} 1159 1160finish_test 1161