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 15set testdir [file dirname $argv0] 16source $testdir/tester.tcl 17set testprefix func 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 # Verify some values reported on the mailing list. 240 # Some of these fail on MSVC builds with 64-bit 241 # long doubles, but not on GCC builds with 80-bit 242 # long doubles. 243 for {set i 1} {$i<999} {incr i} { 244 set x1 [expr 40222.5 + $i] 245 set x2 [expr 40223.0 + $i] 246 do_test func-4.17.$i { 247 execsql {SELECT round($x1);} 248 } $x2 249 } 250 for {set i 1} {$i<999} {incr i} { 251 set x1 [expr 40222.05 + $i] 252 set x2 [expr 40222.10 + $i] 253 do_test func-4.18.$i { 254 execsql {SELECT round($x1,1);} 255 } $x2 256 } 257 do_test func-4.20 { 258 execsql {SELECT round(40223.4999999999);} 259 } {40223.0} 260 do_test func-4.21 { 261 execsql {SELECT round(40224.4999999999);} 262 } {40224.0} 263 do_test func-4.22 { 264 execsql {SELECT round(40225.4999999999);} 265 } {40225.0} 266 for {set i 1} {$i<10} {incr i} { 267 do_test func-4.23.$i { 268 execsql {SELECT round(40223.4999999999,$i);} 269 } {40223.5} 270 do_test func-4.24.$i { 271 execsql {SELECT round(40224.4999999999,$i);} 272 } {40224.5} 273 do_test func-4.25.$i { 274 execsql {SELECT round(40225.4999999999,$i);} 275 } {40225.5} 276 } 277 for {set i 10} {$i<32} {incr i} { 278 do_test func-4.26.$i { 279 execsql {SELECT round(40223.4999999999,$i);} 280 } {40223.4999999999} 281 do_test func-4.27.$i { 282 execsql {SELECT round(40224.4999999999,$i);} 283 } {40224.4999999999} 284 do_test func-4.28.$i { 285 execsql {SELECT round(40225.4999999999,$i);} 286 } {40225.4999999999} 287 } 288 do_test func-4.29 { 289 execsql {SELECT round(1234567890.5);} 290 } {1234567891.0} 291 do_test func-4.30 { 292 execsql {SELECT round(12345678901.5);} 293 } {12345678902.0} 294 do_test func-4.31 { 295 execsql {SELECT round(123456789012.5);} 296 } {123456789013.0} 297 do_test func-4.32 { 298 execsql {SELECT round(1234567890123.5);} 299 } {1234567890124.0} 300 do_test func-4.33 { 301 execsql {SELECT round(12345678901234.5);} 302 } {12345678901235.0} 303 do_test func-4.34 { 304 execsql {SELECT round(1234567890123.35,1);} 305 } {1234567890123.4} 306 do_test func-4.35 { 307 execsql {SELECT round(1234567890123.445,2);} 308 } {1234567890123.45} 309 do_test func-4.36 { 310 execsql {SELECT round(99999999999994.5);} 311 } {99999999999995.0} 312 do_test func-4.37 { 313 execsql {SELECT round(9999999999999.55,1);} 314 } {9999999999999.6} 315 do_test func-4.38 { 316 execsql {SELECT round(9999999999999.556,2);} 317 } {9999999999999.56} 318 do_test func-4.39 { 319 string tolower [db eval {SELECT round(1e500), round(-1e500);}] 320 } {inf -inf} 321} 322 323# Test the upper() and lower() functions 324# 325do_test func-5.1 { 326 execsql {SELECT upper(t1) FROM tbl1} 327} {THIS PROGRAM IS FREE SOFTWARE} 328do_test func-5.2 { 329 execsql {SELECT lower(upper(t1)) FROM tbl1} 330} {this program is free software} 331do_test func-5.3 { 332 execsql {SELECT upper(a), lower(a) FROM t2} 333} {1 1 {} {} 345 345 {} {} 67890 67890} 334ifcapable !icu { 335 do_test func-5.4 { 336 catchsql {SELECT upper(a,5) FROM t2} 337 } {1 {wrong number of arguments to function upper()}} 338} 339do_test func-5.5 { 340 catchsql {SELECT upper(*) FROM t2} 341} {1 {wrong number of arguments to function upper()}} 342 343# Test the coalesce() and nullif() functions 344# 345do_test func-6.1 { 346 execsql {SELECT coalesce(a,'xyz') FROM t2} 347} {1 xyz 345 xyz 67890} 348do_test func-6.2 { 349 execsql {SELECT coalesce(upper(a),'nil') FROM t2} 350} {1 nil 345 nil 67890} 351do_test func-6.3 { 352 execsql {SELECT coalesce(nullif(1,1),'nil')} 353} {nil} 354do_test func-6.4 { 355 execsql {SELECT coalesce(nullif(1,2),'nil')} 356} {1} 357do_test func-6.5 { 358 execsql {SELECT coalesce(nullif(1,NULL),'nil')} 359} {1} 360 361 362# Test the last_insert_rowid() function 363# 364do_test func-7.1 { 365 execsql {SELECT last_insert_rowid()} 366} [db last_insert_rowid] 367 368# Tests for aggregate functions and how they handle NULLs. 369# 370ifcapable floatingpoint { 371 do_test func-8.1 { 372 ifcapable explain { 373 execsql {EXPLAIN SELECT sum(a) FROM t2;} 374 } 375 execsql { 376 SELECT sum(a), count(a), round(avg(a),2), min(a), max(a), count(*) FROM t2; 377 } 378 } {68236 3 22745.33 1 67890 5} 379} 380ifcapable !floatingpoint { 381 do_test func-8.1 { 382 ifcapable explain { 383 execsql {EXPLAIN SELECT sum(a) FROM t2;} 384 } 385 execsql { 386 SELECT sum(a), count(a), avg(a), min(a), max(a), count(*) FROM t2; 387 } 388 } {68236 3 22745.0 1 67890 5} 389} 390do_test func-8.2 { 391 execsql { 392 SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t2; 393 } 394} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP} 395 396ifcapable tempdb { 397 do_test func-8.3 { 398 execsql { 399 CREATE TEMP TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC; 400 SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3; 401 } 402 } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP} 403} else { 404 do_test func-8.3 { 405 execsql { 406 CREATE TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC; 407 SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3; 408 } 409 } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP} 410} 411do_test func-8.4 { 412 execsql { 413 SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3; 414 } 415} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP} 416ifcapable compound { 417 do_test func-8.5 { 418 execsql { 419 SELECT sum(x) FROM (SELECT '9223372036' || '854775807' AS x 420 UNION ALL SELECT -9223372036854775807) 421 } 422 } {0} 423 do_test func-8.6 { 424 execsql { 425 SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775807' AS x 426 UNION ALL SELECT -9223372036854775807) 427 } 428 } {integer} 429 do_test func-8.7 { 430 execsql { 431 SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775808' AS x 432 UNION ALL SELECT -9223372036854775807) 433 } 434 } {real} 435ifcapable floatingpoint { 436 do_test func-8.8 { 437 execsql { 438 SELECT sum(x)>0.0 FROM (SELECT '9223372036' || '854775808' AS x 439 UNION ALL SELECT -9223372036850000000) 440 } 441 } {1} 442} 443ifcapable !floatingpoint { 444 do_test func-8.8 { 445 execsql { 446 SELECT sum(x)>0 FROM (SELECT '9223372036' || '854775808' AS x 447 UNION ALL SELECT -9223372036850000000) 448 } 449 } {1} 450} 451} 452 453# How do you test the random() function in a meaningful, deterministic way? 454# 455do_test func-9.1 { 456 execsql { 457 SELECT random() is not null; 458 } 459} {1} 460do_test func-9.2 { 461 execsql { 462 SELECT typeof(random()); 463 } 464} {integer} 465do_test func-9.3 { 466 execsql { 467 SELECT randomblob(32) is not null; 468 } 469} {1} 470do_test func-9.4 { 471 execsql { 472 SELECT typeof(randomblob(32)); 473 } 474} {blob} 475do_test func-9.5 { 476 execsql { 477 SELECT length(randomblob(32)), length(randomblob(-5)), 478 length(randomblob(2000)) 479 } 480} {32 1 2000} 481 482# The "hex()" function was added in order to be able to render blobs 483# generated by randomblob(). So this seems like a good place to test 484# hex(). 485# 486ifcapable bloblit { 487 do_test func-9.10 { 488 execsql {SELECT hex(x'00112233445566778899aAbBcCdDeEfF')} 489 } {00112233445566778899AABBCCDDEEFF} 490} 491set encoding [db one {PRAGMA encoding}] 492if {$encoding=="UTF-16le"} { 493 do_test func-9.11-utf16le { 494 execsql {SELECT hex(replace('abcdefg','ef','12'))} 495 } {6100620063006400310032006700} 496 do_test func-9.12-utf16le { 497 execsql {SELECT hex(replace('abcdefg','','12'))} 498 } {6100620063006400650066006700} 499 do_test func-9.13-utf16le { 500 execsql {SELECT hex(replace('aabcdefg','a','aaa'))} 501 } {610061006100610061006100620063006400650066006700} 502} elseif {$encoding=="UTF-8"} { 503 do_test func-9.11-utf8 { 504 execsql {SELECT hex(replace('abcdefg','ef','12'))} 505 } {61626364313267} 506 do_test func-9.12-utf8 { 507 execsql {SELECT hex(replace('abcdefg','','12'))} 508 } {61626364656667} 509 do_test func-9.13-utf8 { 510 execsql {SELECT hex(replace('aabcdefg','a','aaa'))} 511 } {616161616161626364656667} 512} 513do_execsql_test func-9.14 { 514 WITH RECURSIVE c(x) AS ( 515 VALUES(1) 516 UNION ALL 517 SELECT x+1 FROM c WHERE x<1040 518 ) 519 SELECT 520 count(*), 521 sum(length(replace(printf('abc%.*cxyz',x,'m'),'m','nnnn'))-(6+x*4)) 522 FROM c; 523} {1040 0} 524 525# Use the "sqlite_register_test_function" TCL command which is part of 526# the text fixture in order to verify correct operation of some of 527# the user-defined SQL function APIs that are not used by the built-in 528# functions. 529# 530set ::DB [sqlite3_connection_pointer db] 531sqlite_register_test_function $::DB testfunc 532do_test func-10.1 { 533 catchsql { 534 SELECT testfunc(NULL,NULL); 535 } 536} {1 {first argument should be one of: int int64 string double null value}} 537do_test func-10.2 { 538 execsql { 539 SELECT testfunc( 540 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 541 'int', 1234 542 ); 543 } 544} {1234} 545do_test func-10.3 { 546 execsql { 547 SELECT testfunc( 548 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 549 'string', NULL 550 ); 551 } 552} {{}} 553 554ifcapable floatingpoint { 555 do_test func-10.4 { 556 execsql { 557 SELECT testfunc( 558 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 559 'double', 1.234 560 ); 561 } 562 } {1.234} 563 do_test func-10.5 { 564 execsql { 565 SELECT testfunc( 566 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 567 'int', 1234, 568 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 569 'string', NULL, 570 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 571 'double', 1.234, 572 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 573 'int', 1234, 574 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 575 'string', NULL, 576 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 577 'double', 1.234 578 ); 579 } 580 } {1.234} 581} 582 583# Test the built-in sqlite_version(*) SQL function. 584# 585do_test func-11.1 { 586 execsql { 587 SELECT sqlite_version(*); 588 } 589} [sqlite3 -version] 590 591# Test that destructors passed to sqlite3 by calls to sqlite3_result_text() 592# etc. are called. These tests use two special user-defined functions 593# (implemented in func.c) only available in test builds. 594# 595# Function test_destructor() takes one argument and returns a copy of the 596# text form of that argument. A destructor is associated with the return 597# value. Function test_destructor_count() returns the number of outstanding 598# destructor calls for values returned by test_destructor(). 599# 600if {[db eval {PRAGMA encoding}]=="UTF-8"} { 601 do_test func-12.1-utf8 { 602 execsql { 603 SELECT test_destructor('hello world'), test_destructor_count(); 604 } 605 } {{hello world} 1} 606} else { 607 ifcapable {utf16} { 608 do_test func-12.1-utf16 { 609 execsql { 610 SELECT test_destructor16('hello world'), test_destructor_count(); 611 } 612 } {{hello world} 1} 613 } 614} 615do_test func-12.2 { 616 execsql { 617 SELECT test_destructor_count(); 618 } 619} {0} 620do_test func-12.3 { 621 execsql { 622 SELECT test_destructor('hello')||' world' 623 } 624} {{hello world}} 625do_test func-12.4 { 626 execsql { 627 SELECT test_destructor_count(); 628 } 629} {0} 630do_test func-12.5 { 631 execsql { 632 CREATE TABLE t4(x); 633 INSERT INTO t4 VALUES(test_destructor('hello')); 634 INSERT INTO t4 VALUES(test_destructor('world')); 635 SELECT min(test_destructor(x)), max(test_destructor(x)) FROM t4; 636 } 637} {hello world} 638do_test func-12.6 { 639 execsql { 640 SELECT test_destructor_count(); 641 } 642} {0} 643do_test func-12.7 { 644 execsql { 645 DROP TABLE t4; 646 } 647} {} 648 649 650# Test that the auxdata API for scalar functions works. This test uses 651# a special user-defined function only available in test builds, 652# test_auxdata(). Function test_auxdata() takes any number of arguments. 653do_test func-13.1 { 654 execsql { 655 SELECT test_auxdata('hello world'); 656 } 657} {0} 658 659do_test func-13.2 { 660 execsql { 661 CREATE TABLE t4(a, b); 662 INSERT INTO t4 VALUES('abc', 'def'); 663 INSERT INTO t4 VALUES('ghi', 'jkl'); 664 } 665} {} 666do_test func-13.3 { 667 execsql { 668 SELECT test_auxdata('hello world') FROM t4; 669 } 670} {0 1} 671do_test func-13.4 { 672 execsql { 673 SELECT test_auxdata('hello world', 123) FROM t4; 674 } 675} {{0 0} {1 1}} 676do_test func-13.5 { 677 execsql { 678 SELECT test_auxdata('hello world', a) FROM t4; 679 } 680} {{0 0} {1 0}} 681do_test func-13.6 { 682 execsql { 683 SELECT test_auxdata('hello'||'world', a) FROM t4; 684 } 685} {{0 0} {1 0}} 686 687# Test that auxilary data is preserved between calls for SQL variables. 688do_test func-13.7 { 689 set DB [sqlite3_connection_pointer db] 690 set sql "SELECT test_auxdata( ? , a ) FROM t4;" 691 set STMT [sqlite3_prepare $DB $sql -1 TAIL] 692 sqlite3_bind_text $STMT 1 hello\000 -1 693 set res [list] 694 while { "SQLITE_ROW"==[sqlite3_step $STMT] } { 695 lappend res [sqlite3_column_text $STMT 0] 696 } 697 lappend res [sqlite3_finalize $STMT] 698} {{0 0} {1 0} SQLITE_OK} 699 700# Test that auxiliary data is discarded when a statement is reset. 701do_execsql_test 13.8.1 { 702 SELECT test_auxdata('constant') FROM t4; 703} {0 1} 704do_execsql_test 13.8.2 { 705 SELECT test_auxdata('constant') FROM t4; 706} {0 1} 707db cache flush 708do_execsql_test 13.8.3 { 709 SELECT test_auxdata('constant') FROM t4; 710} {0 1} 711set V "one" 712do_execsql_test 13.8.4 { 713 SELECT test_auxdata($V), $V FROM t4; 714} {0 one 1 one} 715set V "two" 716do_execsql_test 13.8.5 { 717 SELECT test_auxdata($V), $V FROM t4; 718} {0 two 1 two} 719db cache flush 720set V "three" 721do_execsql_test 13.8.6 { 722 SELECT test_auxdata($V), $V FROM t4; 723} {0 three 1 three} 724 725 726# Make sure that a function with a very long name is rejected 727do_test func-14.1 { 728 catch { 729 db function [string repeat X 254] {return "hello"} 730 } 731} {0} 732do_test func-14.2 { 733 catch { 734 db function [string repeat X 256] {return "hello"} 735 } 736} {1} 737 738do_test func-15.1 { 739 catchsql {select test_error(NULL)} 740} {1 {}} 741do_test func-15.2 { 742 catchsql {select test_error('this is the error message')} 743} {1 {this is the error message}} 744do_test func-15.3 { 745 catchsql {select test_error('this is the error message',12)} 746} {1 {this is the error message}} 747do_test func-15.4 { 748 db errorcode 749} {12} 750 751# Test the quote function for BLOB and NULL values. 752do_test func-16.1 { 753 execsql { 754 CREATE TABLE tbl2(a, b); 755 } 756 set STMT [sqlite3_prepare $::DB "INSERT INTO tbl2 VALUES(?, ?)" -1 TAIL] 757 sqlite3_bind_blob $::STMT 1 abc 3 758 sqlite3_step $::STMT 759 sqlite3_finalize $::STMT 760 execsql { 761 SELECT quote(a), quote(b) FROM tbl2; 762 } 763} {X'616263' NULL} 764 765# Correctly handle function error messages that include %. Ticket #1354 766# 767do_test func-17.1 { 768 proc testfunc1 args {error "Error %d with %s percents %p"} 769 db function testfunc1 ::testfunc1 770 catchsql { 771 SELECT testfunc1(1,2,3); 772 } 773} {1 {Error %d with %s percents %p}} 774 775# The SUM function should return integer results when all inputs are integer. 776# 777do_test func-18.1 { 778 execsql { 779 CREATE TABLE t5(x); 780 INSERT INTO t5 VALUES(1); 781 INSERT INTO t5 VALUES(-99); 782 INSERT INTO t5 VALUES(10000); 783 SELECT sum(x) FROM t5; 784 } 785} {9902} 786ifcapable floatingpoint { 787 do_test func-18.2 { 788 execsql { 789 INSERT INTO t5 VALUES(0.0); 790 SELECT sum(x) FROM t5; 791 } 792 } {9902.0} 793} 794 795# The sum of nothing is NULL. But the sum of all NULLs is NULL. 796# 797# The TOTAL of nothing is 0.0. 798# 799do_test func-18.3 { 800 execsql { 801 DELETE FROM t5; 802 SELECT sum(x), total(x) FROM t5; 803 } 804} {{} 0.0} 805do_test func-18.4 { 806 execsql { 807 INSERT INTO t5 VALUES(NULL); 808 SELECT sum(x), total(x) FROM t5 809 } 810} {{} 0.0} 811do_test func-18.5 { 812 execsql { 813 INSERT INTO t5 VALUES(NULL); 814 SELECT sum(x), total(x) FROM t5 815 } 816} {{} 0.0} 817do_test func-18.6 { 818 execsql { 819 INSERT INTO t5 VALUES(123); 820 SELECT sum(x), total(x) FROM t5 821 } 822} {123 123.0} 823 824# Ticket #1664, #1669, #1670, #1674: An integer overflow on SUM causes 825# an error. The non-standard TOTAL() function continues to give a helpful 826# result. 827# 828do_test func-18.10 { 829 execsql { 830 CREATE TABLE t6(x INTEGER); 831 INSERT INTO t6 VALUES(1); 832 INSERT INTO t6 VALUES(1<<62); 833 SELECT sum(x) - ((1<<62)+1) from t6; 834 } 835} 0 836do_test func-18.11 { 837 execsql { 838 SELECT typeof(sum(x)) FROM t6 839 } 840} integer 841ifcapable floatingpoint { 842 do_test func-18.12 { 843 catchsql { 844 INSERT INTO t6 VALUES(1<<62); 845 SELECT sum(x) - ((1<<62)*2.0+1) from t6; 846 } 847 } {1 {integer overflow}} 848 do_test func-18.13 { 849 execsql { 850 SELECT total(x) - ((1<<62)*2.0+1) FROM t6 851 } 852 } 0.0 853} 854ifcapable !floatingpoint { 855 do_test func-18.12 { 856 catchsql { 857 INSERT INTO t6 VALUES(1<<62); 858 SELECT sum(x) - ((1<<62)*2+1) from t6; 859 } 860 } {1 {integer overflow}} 861 do_test func-18.13 { 862 execsql { 863 SELECT total(x) - ((1<<62)*2+1) FROM t6 864 } 865 } 0.0 866} 867if {[working_64bit_int]} { 868 do_test func-18.14 { 869 execsql { 870 SELECT sum(-9223372036854775805); 871 } 872 } -9223372036854775805 873} 874ifcapable compound&&subquery { 875 876do_test func-18.15 { 877 catchsql { 878 SELECT sum(x) FROM 879 (SELECT 9223372036854775807 AS x UNION ALL 880 SELECT 10 AS x); 881 } 882} {1 {integer overflow}} 883if {[working_64bit_int]} { 884 do_test func-18.16 { 885 catchsql { 886 SELECT sum(x) FROM 887 (SELECT 9223372036854775807 AS x UNION ALL 888 SELECT -10 AS x); 889 } 890 } {0 9223372036854775797} 891 do_test func-18.17 { 892 catchsql { 893 SELECT sum(x) FROM 894 (SELECT -9223372036854775807 AS x UNION ALL 895 SELECT 10 AS x); 896 } 897 } {0 -9223372036854775797} 898} 899do_test func-18.18 { 900 catchsql { 901 SELECT sum(x) FROM 902 (SELECT -9223372036854775807 AS x UNION ALL 903 SELECT -10 AS x); 904 } 905} {1 {integer overflow}} 906do_test func-18.19 { 907 catchsql { 908 SELECT sum(x) FROM (SELECT 9 AS x UNION ALL SELECT -10 AS x); 909 } 910} {0 -1} 911do_test func-18.20 { 912 catchsql { 913 SELECT sum(x) FROM (SELECT -9 AS x UNION ALL SELECT 10 AS x); 914 } 915} {0 1} 916do_test func-18.21 { 917 catchsql { 918 SELECT sum(x) FROM (SELECT -10 AS x UNION ALL SELECT 9 AS x); 919 } 920} {0 -1} 921do_test func-18.22 { 922 catchsql { 923 SELECT sum(x) FROM (SELECT 10 AS x UNION ALL SELECT -9 AS x); 924 } 925} {0 1} 926 927} ;# ifcapable compound&&subquery 928 929# Integer overflow on abs() 930# 931if {[working_64bit_int]} { 932 do_test func-18.31 { 933 catchsql { 934 SELECT abs(-9223372036854775807); 935 } 936 } {0 9223372036854775807} 937} 938do_test func-18.32 { 939 catchsql { 940 SELECT abs(-9223372036854775807-1); 941 } 942} {1 {integer overflow}} 943 944# The MATCH function exists but is only a stub and always throws an error. 945# 946do_test func-19.1 { 947 execsql { 948 SELECT match(a,b) FROM t1 WHERE 0; 949 } 950} {} 951do_test func-19.2 { 952 catchsql { 953 SELECT 'abc' MATCH 'xyz'; 954 } 955} {1 {unable to use function MATCH in the requested context}} 956do_test func-19.3 { 957 catchsql { 958 SELECT 'abc' NOT MATCH 'xyz'; 959 } 960} {1 {unable to use function MATCH in the requested context}} 961do_test func-19.4 { 962 catchsql { 963 SELECT match(1,2,3); 964 } 965} {1 {wrong number of arguments to function match()}} 966 967# Soundex tests. 968# 969if {![catch {db eval {SELECT soundex('hello')}}]} { 970 set i 0 971 foreach {name sdx} { 972 euler E460 973 EULER E460 974 Euler E460 975 ellery E460 976 gauss G200 977 ghosh G200 978 hilbert H416 979 Heilbronn H416 980 knuth K530 981 kant K530 982 Lloyd L300 983 LADD L300 984 Lukasiewicz L222 985 Lissajous L222 986 A A000 987 12345 ?000 988 } { 989 incr i 990 do_test func-20.$i { 991 execsql {SELECT soundex($name)} 992 } $sdx 993 } 994} 995 996# Tests of the REPLACE function. 997# 998do_test func-21.1 { 999 catchsql { 1000 SELECT replace(1,2); 1001 } 1002} {1 {wrong number of arguments to function replace()}} 1003do_test func-21.2 { 1004 catchsql { 1005 SELECT replace(1,2,3,4); 1006 } 1007} {1 {wrong number of arguments to function replace()}} 1008do_test func-21.3 { 1009 execsql { 1010 SELECT typeof(replace('This is the main test string', NULL, 'ALT')); 1011 } 1012} {null} 1013do_test func-21.4 { 1014 execsql { 1015 SELECT typeof(replace(NULL, 'main', 'ALT')); 1016 } 1017} {null} 1018do_test func-21.5 { 1019 execsql { 1020 SELECT typeof(replace('This is the main test string', 'main', NULL)); 1021 } 1022} {null} 1023do_test func-21.6 { 1024 execsql { 1025 SELECT replace('This is the main test string', 'main', 'ALT'); 1026 } 1027} {{This is the ALT test string}} 1028do_test func-21.7 { 1029 execsql { 1030 SELECT replace('This is the main test string', 'main', 'larger-main'); 1031 } 1032} {{This is the larger-main test string}} 1033do_test func-21.8 { 1034 execsql { 1035 SELECT replace('aaaaaaa', 'a', '0123456789'); 1036 } 1037} {0123456789012345678901234567890123456789012345678901234567890123456789} 1038 1039ifcapable tclvar { 1040 do_test func-21.9 { 1041 # Attempt to exploit a buffer-overflow that at one time existed 1042 # in the REPLACE function. 1043 set ::str "[string repeat A 29998]CC[string repeat A 35537]" 1044 set ::rep [string repeat B 65536] 1045 execsql { 1046 SELECT LENGTH(REPLACE($::str, 'C', $::rep)); 1047 } 1048 } [expr 29998 + 2*65536 + 35537] 1049} 1050 1051# Tests for the TRIM, LTRIM and RTRIM functions. 1052# 1053do_test func-22.1 { 1054 catchsql {SELECT trim(1,2,3)} 1055} {1 {wrong number of arguments to function trim()}} 1056do_test func-22.2 { 1057 catchsql {SELECT ltrim(1,2,3)} 1058} {1 {wrong number of arguments to function ltrim()}} 1059do_test func-22.3 { 1060 catchsql {SELECT rtrim(1,2,3)} 1061} {1 {wrong number of arguments to function rtrim()}} 1062do_test func-22.4 { 1063 execsql {SELECT trim(' hi ');} 1064} {hi} 1065do_test func-22.5 { 1066 execsql {SELECT ltrim(' hi ');} 1067} {{hi }} 1068do_test func-22.6 { 1069 execsql {SELECT rtrim(' hi ');} 1070} {{ hi}} 1071do_test func-22.7 { 1072 execsql {SELECT trim(' hi ','xyz');} 1073} {{ hi }} 1074do_test func-22.8 { 1075 execsql {SELECT ltrim(' hi ','xyz');} 1076} {{ hi }} 1077do_test func-22.9 { 1078 execsql {SELECT rtrim(' hi ','xyz');} 1079} {{ hi }} 1080do_test func-22.10 { 1081 execsql {SELECT trim('xyxzy hi zzzy','xyz');} 1082} {{ hi }} 1083do_test func-22.11 { 1084 execsql {SELECT ltrim('xyxzy hi zzzy','xyz');} 1085} {{ hi zzzy}} 1086do_test func-22.12 { 1087 execsql {SELECT rtrim('xyxzy hi zzzy','xyz');} 1088} {{xyxzy hi }} 1089do_test func-22.13 { 1090 execsql {SELECT trim(' hi ','');} 1091} {{ hi }} 1092if {[db one {PRAGMA encoding}]=="UTF-8"} { 1093 do_test func-22.14 { 1094 execsql {SELECT hex(trim(x'c280e1bfbff48fbfbf6869',x'6162e1bfbfc280'))} 1095 } {F48FBFBF6869} 1096 do_test func-22.15 { 1097 execsql {SELECT hex(trim(x'6869c280e1bfbff48fbfbf61', 1098 x'6162e1bfbfc280f48fbfbf'))} 1099 } {6869} 1100 do_test func-22.16 { 1101 execsql {SELECT hex(trim(x'ceb1ceb2ceb3',x'ceb1'));} 1102 } {CEB2CEB3} 1103} 1104do_test func-22.20 { 1105 execsql {SELECT typeof(trim(NULL));} 1106} {null} 1107do_test func-22.21 { 1108 execsql {SELECT typeof(trim(NULL,'xyz'));} 1109} {null} 1110do_test func-22.22 { 1111 execsql {SELECT typeof(trim('hello',NULL));} 1112} {null} 1113 1114# 2021-06-15 - infinite loop due to unsigned character counter 1115# overflow, reported by Zimuzo Ezeozue 1116# 1117do_execsql_test func-22.23 { 1118 SELECT trim('xyzzy',x'c0808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080'); 1119} {xyzzy} 1120 1121# This is to test the deprecated sqlite3_aggregate_count() API. 1122# 1123ifcapable deprecated { 1124 do_test func-23.1 { 1125 sqlite3_create_aggregate db 1126 execsql { 1127 SELECT legacy_count() FROM t6; 1128 } 1129 } {3} 1130} 1131 1132# The group_concat() function. 1133# 1134do_test func-24.1 { 1135 execsql { 1136 SELECT group_concat(t1) FROM tbl1 1137 } 1138} {this,program,is,free,software} 1139do_test func-24.2 { 1140 execsql { 1141 SELECT group_concat(t1,' ') FROM tbl1 1142 } 1143} {{this program is free software}} 1144do_test func-24.3 { 1145 execsql { 1146 SELECT group_concat(t1,' ' || rowid || ' ') FROM tbl1 1147 } 1148} {{this 2 program 3 is 4 free 5 software}} 1149do_test func-24.4 { 1150 execsql { 1151 SELECT group_concat(NULL,t1) FROM tbl1 1152 } 1153} {{}} 1154do_test func-24.5 { 1155 execsql { 1156 SELECT group_concat(t1,NULL) FROM tbl1 1157 } 1158} {thisprogramisfreesoftware} 1159do_test func-24.6 { 1160 execsql { 1161 SELECT 'BEGIN-'||group_concat(t1) FROM tbl1 1162 } 1163} {BEGIN-this,program,is,free,software} 1164 1165# Ticket #3179: Make sure aggregate functions can take many arguments. 1166# None of the built-in aggregates do this, so use the md5sum() from the 1167# test extensions. 1168# 1169unset -nocomplain midargs 1170set midargs {} 1171unset -nocomplain midres 1172set midres {} 1173unset -nocomplain result 1174for {set i 1} {$i<[sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG -1]} {incr i} { 1175 append midargs ,'/$i' 1176 append midres /$i 1177 set result [md5 \ 1178 "this${midres}program${midres}is${midres}free${midres}software${midres}"] 1179 set sql "SELECT md5sum(t1$midargs) FROM tbl1" 1180 do_test func-24.7.$i { 1181 db eval $::sql 1182 } $result 1183} 1184 1185# Ticket #3806. If the initial string in a group_concat is an empty 1186# string, the separator that follows should still be present. 1187# 1188do_test func-24.8 { 1189 execsql { 1190 SELECT group_concat(CASE t1 WHEN 'this' THEN '' ELSE t1 END) FROM tbl1 1191 } 1192} {,program,is,free,software} 1193do_test func-24.9 { 1194 execsql { 1195 SELECT group_concat(CASE WHEN t1!='software' THEN '' ELSE t1 END) FROM tbl1 1196 } 1197} {,,,,software} 1198 1199# Ticket #3923. Initial empty strings have a separator. But initial 1200# NULLs do not. 1201# 1202do_test func-24.10 { 1203 execsql { 1204 SELECT group_concat(CASE t1 WHEN 'this' THEN null ELSE t1 END) FROM tbl1 1205 } 1206} {program,is,free,software} 1207do_test func-24.11 { 1208 execsql { 1209 SELECT group_concat(CASE WHEN t1!='software' THEN null ELSE t1 END) FROM tbl1 1210 } 1211} {software} 1212do_test func-24.12 { 1213 execsql { 1214 SELECT group_concat(CASE t1 WHEN 'this' THEN '' 1215 WHEN 'program' THEN null ELSE t1 END) FROM tbl1 1216 } 1217} {,is,free,software} 1218# Tests to verify ticket http://www.sqlite.org/src/tktview/55746f9e65f8587c0 1219do_test func-24.13 { 1220 execsql { 1221 SELECT typeof(group_concat(x)) FROM (SELECT '' AS x); 1222 } 1223} {text} 1224do_test func-24.14 { 1225 execsql { 1226 SELECT typeof(group_concat(x,'')) 1227 FROM (SELECT '' AS x UNION ALL SELECT ''); 1228 } 1229} {text} 1230 1231 1232# Use the test_isolation function to make sure that type conversions 1233# on function arguments do not effect subsequent arguments. 1234# 1235do_test func-25.1 { 1236 execsql {SELECT test_isolation(t1,t1) FROM tbl1} 1237} {this program is free software} 1238 1239# Try to misuse the sqlite3_create_function() interface. Verify that 1240# errors are returned. 1241# 1242do_test func-26.1 { 1243 abuse_create_function db 1244} {} 1245 1246# The previous test (func-26.1) registered a function with a very long 1247# function name that takes many arguments and always returns NULL. Verify 1248# that this function works correctly. 1249# 1250do_test func-26.2 { 1251 set a {} 1252 for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG} {incr i} { 1253 lappend a $i 1254 } 1255 db eval " 1256 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 ,]); 1257 " 1258} {{}} 1259do_test func-26.3 { 1260 set a {} 1261 for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG+1} {incr i} { 1262 lappend a $i 1263 } 1264 catchsql " 1265 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 ,]); 1266 " 1267} {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}} 1268do_test func-26.4 { 1269 set a {} 1270 for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG-1} {incr i} { 1271 lappend a $i 1272 } 1273 catchsql " 1274 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 ,]); 1275 " 1276} {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()}} 1277do_test func-26.5 { 1278 catchsql " 1279 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); 1280 " 1281} {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}} 1282do_test func-26.6 { 1283 catchsql " 1284 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); 1285 " 1286} {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}} 1287 1288do_test func-27.1 { 1289 catchsql {SELECT coalesce()} 1290} {1 {wrong number of arguments to function coalesce()}} 1291do_test func-27.2 { 1292 catchsql {SELECT coalesce(1)} 1293} {1 {wrong number of arguments to function coalesce()}} 1294do_test func-27.3 { 1295 catchsql {SELECT coalesce(1,2)} 1296} {0 1} 1297 1298# Ticket 2d401a94287b5 1299# Unknown function in a DEFAULT expression causes a segfault. 1300# 1301do_test func-28.1 { 1302 db eval { 1303 CREATE TABLE t28(x, y DEFAULT(nosuchfunc(1))); 1304 } 1305 catchsql { 1306 INSERT INTO t28(x) VALUES(1); 1307 } 1308} {1 {unknown function: nosuchfunc()}} 1309 1310# Verify that the length() and typeof() functions do not actually load 1311# the content of their argument. 1312# 1313do_test func-29.1 { 1314 db eval { 1315 CREATE TABLE t29(id INTEGER PRIMARY KEY, x, y); 1316 INSERT INTO t29 VALUES(1, 2, 3), (2, NULL, 4), (3, 4.5, 5); 1317 INSERT INTO t29 VALUES(4, randomblob(1000000), 6); 1318 INSERT INTO t29 VALUES(5, 'hello', 7); 1319 } 1320 db close 1321 sqlite3 db test.db 1322 sqlite3_db_status db CACHE_MISS 1 1323 db eval {SELECT typeof(x), length(x), typeof(y) FROM t29 ORDER BY id} 1324} {integer 1 integer null {} integer real 3 integer blob 1000000 integer text 5 integer} 1325do_test func-29.2 { 1326 set x [lindex [sqlite3_db_status db CACHE_MISS 1] 1] 1327 if {$x<5} {set x 1} 1328 set x 1329} {1} 1330do_test func-29.3 { 1331 db close 1332 sqlite3 db test.db 1333 sqlite3_db_status db CACHE_MISS 1 1334 db eval {SELECT typeof(+x) FROM t29 ORDER BY id} 1335} {integer null real blob text} 1336if {[permutation] != "mmap"} { 1337 ifcapable !direct_read { 1338 do_test func-29.4 { 1339 set x [lindex [sqlite3_db_status db CACHE_MISS 1] 1] 1340 if {$x>100} {set x many} 1341 set x 1342 } {many} 1343 } 1344} 1345do_test func-29.5 { 1346 db close 1347 sqlite3 db test.db 1348 sqlite3_db_status db CACHE_MISS 1 1349 db eval {SELECT sum(length(x)) FROM t29} 1350} {1000009} 1351do_test func-29.6 { 1352 set x [lindex [sqlite3_db_status db CACHE_MISS 1] 1] 1353 if {$x<5} {set x 1} 1354 set x 1355} {1} 1356 1357# The OP_Column opcode has an optimization that avoids loading content 1358# for fields with content-length=0 when the content offset is on an overflow 1359# page. Make sure the optimization works. 1360# 1361do_execsql_test func-29.10 { 1362 CREATE TABLE t29b(a,b,c,d,e,f,g,h,i); 1363 INSERT INTO t29b 1364 VALUES(1, hex(randomblob(2000)), null, 0, 1, '', zeroblob(0),'x',x'01'); 1365 SELECT typeof(c), typeof(d), typeof(e), typeof(f), 1366 typeof(g), typeof(h), typeof(i) FROM t29b; 1367} {null integer integer text blob text blob} 1368do_execsql_test func-29.11 { 1369 SELECT length(f), length(g), length(h), length(i) FROM t29b; 1370} {0 0 1 1} 1371do_execsql_test func-29.12 { 1372 SELECT quote(f), quote(g), quote(h), quote(i) FROM t29b; 1373} {'' X'' 'x' X'01'} 1374 1375# EVIDENCE-OF: R-29701-50711 The unicode(X) function returns the numeric 1376# unicode code point corresponding to the first character of the string 1377# X. 1378# 1379# EVIDENCE-OF: R-55469-62130 The char(X1,X2,...,XN) function returns a 1380# string composed of characters having the unicode code point values of 1381# integers X1 through XN, respectively. 1382# 1383do_execsql_test func-30.1 {SELECT unicode('$');} 36 1384do_execsql_test func-30.2 [subst {SELECT unicode('\u00A2');}] 162 1385do_execsql_test func-30.3 [subst {SELECT unicode('\u20AC');}] 8364 1386do_execsql_test func-30.4 {SELECT char(36,162,8364);} [subst {$\u00A2\u20AC}] 1387 1388for {set i 1} {$i<0xd800} {incr i 13} { 1389 do_execsql_test func-30.5.$i {SELECT unicode(char($i))} $i 1390} 1391for {set i 57344} {$i<=0xfffd} {incr i 17} { 1392 if {$i==0xfeff} continue 1393 do_execsql_test func-30.5.$i {SELECT unicode(char($i))} $i 1394} 1395for {set i 65536} {$i<=0x10ffff} {incr i 139} { 1396 do_execsql_test func-30.5.$i {SELECT unicode(char($i))} $i 1397} 1398 1399# Test char(). 1400# 1401do_execsql_test func-31.1 { 1402 SELECT char(), length(char()), typeof(char()) 1403} {{} 0 text} 1404 1405# sqlite3_value_frombind() 1406# 1407do_execsql_test func-32.100 { 1408 SELECT test_frombind(1,2,3,4); 1409} {0} 1410do_execsql_test func-32.110 { 1411 SELECT test_frombind(1,2,?,4); 1412} {4} 1413do_execsql_test func-32.120 { 1414 SELECT test_frombind(1,(?),4,?+7); 1415} {2} 1416do_execsql_test func-32.130 { 1417 DROP TABLE IF EXISTS t1; 1418 CREATE TABLE t1(a,b,c,e,f); 1419 INSERT INTO t1 VALUES(1,2.5,'xyz',x'e0c1b2a3',null); 1420 SELECT test_frombind(a,b,c,e,f,$xyz) FROM t1; 1421} {32} 1422do_execsql_test func-32.140 { 1423 SELECT test_frombind(a,b,c,e,f,$xyz+f) FROM t1; 1424} {0} 1425do_execsql_test func-32.150 { 1426 SELECT test_frombind(x.a,y.b,x.c,:123,y.e,x.f,$xyz+y.f) FROM t1 x, t1 y; 1427} {8} 1428 1429# 2019-08-15 1430# Direct-only functions. 1431# 1432proc testdirectonly {x} {return [expr {$x*2}]} 1433do_test func-33.1 { 1434 db func testdirectonly -directonly testdirectonly 1435 db eval {SELECT testdirectonly(15)} 1436} {30} 1437do_catchsql_test func-33.2 { 1438 CREATE VIEW v33(y) AS SELECT testdirectonly(15); 1439 SELECT * FROM v33; 1440} {1 {unsafe use of testdirectonly()}} 1441do_execsql_test func-33.3 { 1442 SELECT * FROM (SELECT testdirectonly(15)) AS v33; 1443} {30} 1444do_execsql_test func-33.4 { 1445 WITH c(x) AS (SELECT testdirectonly(15)) 1446 SELECT * FROM c; 1447} {30} 1448do_catchsql_test func-33.5 { 1449 WITH c(x) AS (SELECT * FROM v33) 1450 SELECT * FROM c; 1451} {1 {unsafe use of testdirectonly()}} 1452do_execsql_test func-33.10 { 1453 CREATE TABLE t33a(a,b); 1454 CREATE TABLE t33b(x,y); 1455 CREATE TRIGGER r1 AFTER INSERT ON t33a BEGIN 1456 INSERT INTO t33b(x,y) VALUES(testdirectonly(new.a),new.b); 1457 END; 1458} {} 1459do_catchsql_test func-33.11 { 1460 INSERT INTO t33a VALUES(1,2); 1461} {1 {unsafe use of testdirectonly()}} 1462 1463ifcapable altertable { 1464do_execsql_test func-33.20 { 1465 ALTER TABLE t33a RENAME COLUMN a TO aaa; 1466 SELECT sql FROM sqlite_master WHERE name='r1'; 1467} {{CREATE TRIGGER r1 AFTER INSERT ON t33a BEGIN 1468 INSERT INTO t33b(x,y) VALUES(testdirectonly(new.aaa),new.b); 1469 END}} 1470} 1471 1472# 2020-01-09 Yongheng fuzzer find 1473# The bug is in the register-validity debug logic, not in the SQLite core 1474# and as such it only impacts debug builds. Release builds work fine. 1475# 1476reset_db 1477do_execsql_test func-34.10 { 1478 CREATE TABLE t1(a INT CHECK( 1479 datetime( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 1480 10,11,12,13,14,15,16,17,18,19, 1481 20,21,22,23,24,25,26,27,28,29, 1482 30,31,32,33,34,35,36,37,38,39, 1483 40,41,42,43,44,45,46,47,48,a) 1484 ) 1485 ); 1486 INSERT INTO t1(a) VALUES(1),(2); 1487 SELECT * FROM t1; 1488} {1 2} 1489 1490# 2020-03-11 COALESCE() should short-circuit 1491# See also ticket 3c9eadd2a6ba0aa5 1492# Both issues stem from the fact that functions that could 1493# throw exceptions were being factored out into initialization 1494# code. The fix was to put those function calls inside of 1495# OP_Once instead. 1496# 1497reset_db 1498do_execsql_test func-35.100 { 1499 CREATE TABLE t1(x); 1500 SELECT coalesce(x, abs(-9223372036854775808)) FROM t1; 1501} {} 1502do_execsql_test func-35.110 { 1503 SELECT coalesce(x, 'xyz' LIKE printf('%.1000000c','y')) FROM t1; 1504} {} 1505do_execsql_test func-35.200 { 1506 CREATE TABLE t0(c0 CHECK(ABS(-9223372036854775808))); 1507 PRAGMA integrity_check; 1508} {ok} 1509 1510# 2021-01-07: The -> and ->> operators. 1511# 1512proc ptr1 {a b} { return "$a->$b" } 1513db func -> ptr1 1514proc ptr2 {a b} { return "$a->>$b" } 1515db func ->> ptr2 1516do_execsql_test func-36.100 { 1517 SELECT 123 -> 456 1518} {123->456} 1519do_execsql_test func-36.110 { 1520 SELECT 123 ->> 456 1521} {123->>456} 1522 1523 1524 1525finish_test 1526