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.40 2005/09/08 20:37:44 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.00 1.23 2.00}} 183do_test func-4.7 { 184 catchsql {SELECT round(b,0) FROM t1 ORDER BY a} 185} {0 {2 1 -2}} 186do_test func-4.8 { 187 catchsql {SELECT round(c) FROM t1 ORDER BY a} 188} {0 {3 -12346 -5}} 189do_test func-4.9 { 190 catchsql {SELECT round(c,a) FROM t1 ORDER BY a} 191} {0 {3.0 -12345.68 -5.000}} 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.000y}} 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.00 nil 345.00 nil 67890.00} 201do_test func-4.13 { 202 execsql {SELECT round(t1,2) FROM tbl1} 203} {0.00 0.00 0.00 0.00 0.00} 204 205# Test the upper() and lower() functions 206# 207do_test func-5.1 { 208 execsql {SELECT upper(t1) FROM tbl1} 209} {THIS PROGRAM IS FREE SOFTWARE} 210do_test func-5.2 { 211 execsql {SELECT lower(upper(t1)) FROM tbl1} 212} {this program is free software} 213do_test func-5.3 { 214 execsql {SELECT upper(a), lower(a) FROM t2} 215} {1 1 {} {} 345 345 {} {} 67890 67890} 216do_test func-5.4 { 217 catchsql {SELECT upper(a,5) FROM t2} 218} {1 {wrong number of arguments to function upper()}} 219do_test func-5.5 { 220 catchsql {SELECT upper(*) FROM t2} 221} {1 {wrong number of arguments to function upper()}} 222 223# Test the coalesce() and nullif() functions 224# 225do_test func-6.1 { 226 execsql {SELECT coalesce(a,'xyz') FROM t2} 227} {1 xyz 345 xyz 67890} 228do_test func-6.2 { 229 execsql {SELECT coalesce(upper(a),'nil') FROM t2} 230} {1 nil 345 nil 67890} 231do_test func-6.3 { 232 execsql {SELECT coalesce(nullif(1,1),'nil')} 233} {nil} 234do_test func-6.4 { 235 execsql {SELECT coalesce(nullif(1,2),'nil')} 236} {1} 237do_test func-6.5 { 238 execsql {SELECT coalesce(nullif(1,NULL),'nil')} 239} {1} 240 241 242# Test the last_insert_rowid() function 243# 244do_test func-7.1 { 245 execsql {SELECT last_insert_rowid()} 246} [db last_insert_rowid] 247 248# Tests for aggregate functions and how they handle NULLs. 249# 250do_test func-8.1 { 251 ifcapable explain { 252 execsql {EXPLAIN SELECT sum(a) FROM t2;} 253 } 254 execsql { 255 SELECT sum(a), count(a), round(avg(a),2), min(a), max(a), count(*) FROM t2; 256 } 257} {68236 3 22745.33 1 67890 5} 258do_test func-8.2 { 259 execsql { 260 SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t2; 261 } 262} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP} 263 264ifcapable tempdb { 265 do_test func-8.3 { 266 execsql { 267 CREATE TEMP TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC; 268 SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3; 269 } 270 } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP} 271} else { 272 do_test func-8.3 { 273 execsql { 274 CREATE TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC; 275 SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3; 276 } 277 } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP} 278} 279do_test func-8.4 { 280 execsql { 281 SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3; 282 } 283} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP} 284 285# How do you test the random() function in a meaningful, deterministic way? 286# 287do_test func-9.1 { 288 execsql { 289 SELECT random() is not null; 290 } 291} {1} 292 293# Use the "sqlite_register_test_function" TCL command which is part of 294# the text fixture in order to verify correct operation of some of 295# the user-defined SQL function APIs that are not used by the built-in 296# functions. 297# 298db close 299set ::DB [sqlite3 db test.db] 300sqlite_register_test_function $::DB testfunc 301do_test func-10.1 { 302 catchsql { 303 SELECT testfunc(NULL,NULL); 304 } 305} {1 {first argument should be one of: int int64 string double null value}} 306do_test func-10.2 { 307 execsql { 308 SELECT testfunc( 309 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 310 'int', 1234 311 ); 312 } 313} {1234} 314do_test func-10.3 { 315 execsql { 316 SELECT testfunc( 317 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 318 'string', NULL 319 ); 320 } 321} {{}} 322do_test func-10.4 { 323 execsql { 324 SELECT testfunc( 325 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 326 'double', 1.234 327 ); 328 } 329} {1.234} 330do_test func-10.5 { 331 execsql { 332 SELECT testfunc( 333 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 334 'int', 1234, 335 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 336 'string', NULL, 337 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 338 'double', 1.234, 339 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 340 'int', 1234, 341 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 342 'string', NULL, 343 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 344 'double', 1.234 345 ); 346 } 347} {1.234} 348 349# Test the built-in sqlite_version(*) SQL function. 350# 351do_test func-11.1 { 352 execsql { 353 SELECT sqlite_version(*); 354 } 355} [sqlite3 -version] 356 357# Test that destructors passed to sqlite3 by calls to sqlite3_result_text() 358# etc. are called. These tests use two special user-defined functions 359# (implemented in func.c) only available in test builds. 360# 361# Function test_destructor() takes one argument and returns a copy of the 362# text form of that argument. A destructor is associated with the return 363# value. Function test_destructor_count() returns the number of outstanding 364# destructor calls for values returned by test_destructor(). 365# 366do_test func-12.1 { 367 execsql { 368 SELECT test_destructor('hello world'), test_destructor_count(); 369 } 370} {{hello world} 1} 371do_test func-12.2 { 372 execsql { 373 SELECT test_destructor_count(); 374 } 375} {0} 376do_test func-12.3 { 377 execsql { 378 SELECT test_destructor('hello')||' world', test_destructor_count(); 379 } 380} {{hello world} 0} 381do_test func-12.4 { 382 execsql { 383 SELECT test_destructor_count(); 384 } 385} {0} 386do_test func-12.5 { 387 execsql { 388 CREATE TABLE t4(x); 389 INSERT INTO t4 VALUES(test_destructor('hello')); 390 INSERT INTO t4 VALUES(test_destructor('world')); 391 SELECT min(test_destructor(x)), max(test_destructor(x)) FROM t4; 392 } 393} {hello world} 394do_test func-12.6 { 395 execsql { 396 SELECT test_destructor_count(); 397 } 398} {0} 399do_test func-12.7 { 400 execsql { 401 DROP TABLE t4; 402 } 403} {} 404 405# Test that the auxdata API for scalar functions works. This test uses 406# a special user-defined function only available in test builds, 407# test_auxdata(). Function test_auxdata() takes any number of arguments. 408do_test func-13.1 { 409 execsql { 410 SELECT test_auxdata('hello world'); 411 } 412} {0} 413 414do_test func-13.2 { 415 execsql { 416 CREATE TABLE t4(a, b); 417 INSERT INTO t4 VALUES('abc', 'def'); 418 INSERT INTO t4 VALUES('ghi', 'jkl'); 419 } 420} {} 421do_test func-13.3 { 422 execsql { 423 SELECT test_auxdata('hello world') FROM t4; 424 } 425} {0 1} 426do_test func-13.4 { 427 execsql { 428 SELECT test_auxdata('hello world', 123) FROM t4; 429 } 430} {{0 0} {1 1}} 431do_test func-13.5 { 432 execsql { 433 SELECT test_auxdata('hello world', a) FROM t4; 434 } 435} {{0 0} {1 0}} 436do_test func-13.6 { 437 execsql { 438 SELECT test_auxdata('hello'||'world', a) FROM t4; 439 } 440} {{0 0} {1 0}} 441 442# Test that auxilary data is preserved between calls for SQL variables. 443do_test func-13.7 { 444 db close 445 set DB [sqlite3 db test.db] 446 set sql "SELECT test_auxdata( ? , a ) FROM t4;" 447 set STMT [sqlite3_prepare $DB $sql -1 TAIL] 448 sqlite3_bind_text $STMT 1 hello -1 449 set res [list] 450 while { "SQLITE_ROW"==[sqlite3_step $STMT] } { 451 lappend res [sqlite3_column_text $STMT 0] 452 } 453 lappend res [sqlite3_finalize $STMT] 454} {{0 0} {1 0} SQLITE_OK} 455 456# Make sure that a function with a very long name is rejected 457do_test func-14.1 { 458 catch { 459 db function [string repeat X 254] {return "hello"} 460 } 461} {0} 462do_test func-14.2 { 463 catch { 464 db function [string repeat X 256] {return "hello"} 465 } 466} {1} 467 468do_test func-15.1 { 469 catchsql { 470 select test_error(NULL); 471 } 472} {1 {user function error}} 473 474# Test the quote function for BLOB and NULL values. 475do_test func-16.1 { 476 execsql { 477 CREATE TABLE tbl2(a, b); 478 } 479 set STMT [sqlite3_prepare $::DB "INSERT INTO tbl2 VALUES(?, ?)" -1 TAIL] 480 sqlite3_bind_blob $::STMT 1 abc 3 481 sqlite3_step $::STMT 482 sqlite3_finalize $::STMT 483 execsql { 484 SELECT quote(a), quote(b) FROM tbl2; 485 } 486} {X'616263' NULL} 487 488# Correctly handle function error messages that include %. Ticket #1354 489# 490do_test func-17.1 { 491 proc testfunc1 args {error "Error %d with %s percents %p"} 492 db function testfunc1 ::testfunc1 493 catchsql { 494 SELECT testfunc1(1,2,3); 495 } 496} {1 {Error %d with %s percents %p}} 497 498# The SUM function should return integer results when all inputs are integer. 499# 500do_test func-18.1 { 501 execsql { 502 CREATE TABLE t5(x); 503 INSERT INTO t5 VALUES(1); 504 INSERT INTO t5 VALUES(-99); 505 INSERT INTO t5 VALUES(10000); 506 SELECT sum(x) FROM t5; 507 } 508} {9902} 509do_test func-18.2 { 510 execsql { 511 INSERT INTO t5 VALUES(0.0); 512 SELECT sum(x) FROM t5; 513 } 514} {9902.0} 515 516# The sum of nothing is NULL. But the sum of all NULLs is NULL. 517# 518do_test func-18.3 { 519 execsql { 520 DELETE FROM t5; 521 SELECT sum(x) FROM t5; 522 } 523} {{}} 524do_test func-18.4 { 525 execsql { 526 INSERT INTO t5 VALUES(NULL); 527 SELECT sum(x) FROM t5 528 } 529} {{}} 530do_test func-18.5 { 531 execsql { 532 INSERT INTO t5 VALUES(NULL); 533 SELECT sum(x) FROM t5 534 } 535} {{}} 536do_test func-18.6 { 537 execsql { 538 INSERT INTO t5 VALUES(123); 539 SELECT sum(x) FROM t5 540 } 541} {123} 542 543finish_test 544