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# This is to test the deprecated sqlite3_aggregate_count() API. 1115# 1116ifcapable deprecated { 1117 do_test func-23.1 { 1118 sqlite3_create_aggregate db 1119 execsql { 1120 SELECT legacy_count() FROM t6; 1121 } 1122 } {3} 1123} 1124 1125# The group_concat() function. 1126# 1127do_test func-24.1 { 1128 execsql { 1129 SELECT group_concat(t1) FROM tbl1 1130 } 1131} {this,program,is,free,software} 1132do_test func-24.2 { 1133 execsql { 1134 SELECT group_concat(t1,' ') FROM tbl1 1135 } 1136} {{this program is free software}} 1137do_test func-24.3 { 1138 execsql { 1139 SELECT group_concat(t1,' ' || rowid || ' ') FROM tbl1 1140 } 1141} {{this 2 program 3 is 4 free 5 software}} 1142do_test func-24.4 { 1143 execsql { 1144 SELECT group_concat(NULL,t1) FROM tbl1 1145 } 1146} {{}} 1147do_test func-24.5 { 1148 execsql { 1149 SELECT group_concat(t1,NULL) FROM tbl1 1150 } 1151} {thisprogramisfreesoftware} 1152do_test func-24.6 { 1153 execsql { 1154 SELECT 'BEGIN-'||group_concat(t1) FROM tbl1 1155 } 1156} {BEGIN-this,program,is,free,software} 1157 1158# Ticket #3179: Make sure aggregate functions can take many arguments. 1159# None of the built-in aggregates do this, so use the md5sum() from the 1160# test extensions. 1161# 1162unset -nocomplain midargs 1163set midargs {} 1164unset -nocomplain midres 1165set midres {} 1166unset -nocomplain result 1167for {set i 1} {$i<[sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG -1]} {incr i} { 1168 append midargs ,'/$i' 1169 append midres /$i 1170 set result [md5 \ 1171 "this${midres}program${midres}is${midres}free${midres}software${midres}"] 1172 set sql "SELECT md5sum(t1$midargs) FROM tbl1" 1173 do_test func-24.7.$i { 1174 db eval $::sql 1175 } $result 1176} 1177 1178# Ticket #3806. If the initial string in a group_concat is an empty 1179# string, the separator that follows should still be present. 1180# 1181do_test func-24.8 { 1182 execsql { 1183 SELECT group_concat(CASE t1 WHEN 'this' THEN '' ELSE t1 END) FROM tbl1 1184 } 1185} {,program,is,free,software} 1186do_test func-24.9 { 1187 execsql { 1188 SELECT group_concat(CASE WHEN t1!='software' THEN '' ELSE t1 END) FROM tbl1 1189 } 1190} {,,,,software} 1191 1192# Ticket #3923. Initial empty strings have a separator. But initial 1193# NULLs do not. 1194# 1195do_test func-24.10 { 1196 execsql { 1197 SELECT group_concat(CASE t1 WHEN 'this' THEN null ELSE t1 END) FROM tbl1 1198 } 1199} {program,is,free,software} 1200do_test func-24.11 { 1201 execsql { 1202 SELECT group_concat(CASE WHEN t1!='software' THEN null ELSE t1 END) FROM tbl1 1203 } 1204} {software} 1205do_test func-24.12 { 1206 execsql { 1207 SELECT group_concat(CASE t1 WHEN 'this' THEN '' 1208 WHEN 'program' THEN null ELSE t1 END) FROM tbl1 1209 } 1210} {,is,free,software} 1211# Tests to verify ticket http://www.sqlite.org/src/tktview/55746f9e65f8587c0 1212do_test func-24.13 { 1213 execsql { 1214 SELECT typeof(group_concat(x)) FROM (SELECT '' AS x); 1215 } 1216} {text} 1217do_test func-24.14 { 1218 execsql { 1219 SELECT typeof(group_concat(x,'')) 1220 FROM (SELECT '' AS x UNION ALL SELECT ''); 1221 } 1222} {text} 1223 1224 1225# Use the test_isolation function to make sure that type conversions 1226# on function arguments do not effect subsequent arguments. 1227# 1228do_test func-25.1 { 1229 execsql {SELECT test_isolation(t1,t1) FROM tbl1} 1230} {this program is free software} 1231 1232# Try to misuse the sqlite3_create_function() interface. Verify that 1233# errors are returned. 1234# 1235do_test func-26.1 { 1236 abuse_create_function db 1237} {} 1238 1239# The previous test (func-26.1) registered a function with a very long 1240# function name that takes many arguments and always returns NULL. Verify 1241# that this function works correctly. 1242# 1243do_test func-26.2 { 1244 set a {} 1245 for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG} {incr i} { 1246 lappend a $i 1247 } 1248 db eval " 1249 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 ,]); 1250 " 1251} {{}} 1252do_test func-26.3 { 1253 set a {} 1254 for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG+1} {incr i} { 1255 lappend a $i 1256 } 1257 catchsql " 1258 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 ,]); 1259 " 1260} {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}} 1261do_test func-26.4 { 1262 set a {} 1263 for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG-1} {incr i} { 1264 lappend a $i 1265 } 1266 catchsql " 1267 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 ,]); 1268 " 1269} {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()}} 1270do_test func-26.5 { 1271 catchsql " 1272 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); 1273 " 1274} {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}} 1275do_test func-26.6 { 1276 catchsql " 1277 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); 1278 " 1279} {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}} 1280 1281do_test func-27.1 { 1282 catchsql {SELECT coalesce()} 1283} {1 {wrong number of arguments to function coalesce()}} 1284do_test func-27.2 { 1285 catchsql {SELECT coalesce(1)} 1286} {1 {wrong number of arguments to function coalesce()}} 1287do_test func-27.3 { 1288 catchsql {SELECT coalesce(1,2)} 1289} {0 1} 1290 1291# Ticket 2d401a94287b5 1292# Unknown function in a DEFAULT expression causes a segfault. 1293# 1294do_test func-28.1 { 1295 db eval { 1296 CREATE TABLE t28(x, y DEFAULT(nosuchfunc(1))); 1297 } 1298 catchsql { 1299 INSERT INTO t28(x) VALUES(1); 1300 } 1301} {1 {unknown function: nosuchfunc()}} 1302 1303# Verify that the length() and typeof() functions do not actually load 1304# the content of their argument. 1305# 1306do_test func-29.1 { 1307 db eval { 1308 CREATE TABLE t29(id INTEGER PRIMARY KEY, x, y); 1309 INSERT INTO t29 VALUES(1, 2, 3), (2, NULL, 4), (3, 4.5, 5); 1310 INSERT INTO t29 VALUES(4, randomblob(1000000), 6); 1311 INSERT INTO t29 VALUES(5, "hello", 7); 1312 } 1313 db close 1314 sqlite3 db test.db 1315 sqlite3_db_status db CACHE_MISS 1 1316 db eval {SELECT typeof(x), length(x), typeof(y) FROM t29 ORDER BY id} 1317} {integer 1 integer null {} integer real 3 integer blob 1000000 integer text 5 integer} 1318do_test func-29.2 { 1319 set x [lindex [sqlite3_db_status db CACHE_MISS 1] 1] 1320 if {$x<5} {set x 1} 1321 set x 1322} {1} 1323do_test func-29.3 { 1324 db close 1325 sqlite3 db test.db 1326 sqlite3_db_status db CACHE_MISS 1 1327 db eval {SELECT typeof(+x) FROM t29 ORDER BY id} 1328} {integer null real blob text} 1329if {[permutation] != "mmap"} { 1330 ifcapable !direct_read { 1331 do_test func-29.4 { 1332 set x [lindex [sqlite3_db_status db CACHE_MISS 1] 1] 1333 if {$x>100} {set x many} 1334 set x 1335 } {many} 1336 } 1337} 1338do_test func-29.5 { 1339 db close 1340 sqlite3 db test.db 1341 sqlite3_db_status db CACHE_MISS 1 1342 db eval {SELECT sum(length(x)) FROM t29} 1343} {1000009} 1344do_test func-29.6 { 1345 set x [lindex [sqlite3_db_status db CACHE_MISS 1] 1] 1346 if {$x<5} {set x 1} 1347 set x 1348} {1} 1349 1350# The OP_Column opcode has an optimization that avoids loading content 1351# for fields with content-length=0 when the content offset is on an overflow 1352# page. Make sure the optimization works. 1353# 1354do_execsql_test func-29.10 { 1355 CREATE TABLE t29b(a,b,c,d,e,f,g,h,i); 1356 INSERT INTO t29b 1357 VALUES(1, hex(randomblob(2000)), null, 0, 1, '', zeroblob(0),'x',x'01'); 1358 SELECT typeof(c), typeof(d), typeof(e), typeof(f), 1359 typeof(g), typeof(h), typeof(i) FROM t29b; 1360} {null integer integer text blob text blob} 1361do_execsql_test func-29.11 { 1362 SELECT length(f), length(g), length(h), length(i) FROM t29b; 1363} {0 0 1 1} 1364do_execsql_test func-29.12 { 1365 SELECT quote(f), quote(g), quote(h), quote(i) FROM t29b; 1366} {'' X'' 'x' X'01'} 1367 1368# EVIDENCE-OF: R-29701-50711 The unicode(X) function returns the numeric 1369# unicode code point corresponding to the first character of the string 1370# X. 1371# 1372# EVIDENCE-OF: R-55469-62130 The char(X1,X2,...,XN) function returns a 1373# string composed of characters having the unicode code point values of 1374# integers X1 through XN, respectively. 1375# 1376do_execsql_test func-30.1 {SELECT unicode('$');} 36 1377do_execsql_test func-30.2 [subst {SELECT unicode('\u00A2');}] 162 1378do_execsql_test func-30.3 [subst {SELECT unicode('\u20AC');}] 8364 1379do_execsql_test func-30.4 {SELECT char(36,162,8364);} [subst {$\u00A2\u20AC}] 1380 1381for {set i 1} {$i<0xd800} {incr i 13} { 1382 do_execsql_test func-30.5.$i {SELECT unicode(char($i))} $i 1383} 1384for {set i 57344} {$i<=0xfffd} {incr i 17} { 1385 if {$i==0xfeff} continue 1386 do_execsql_test func-30.5.$i {SELECT unicode(char($i))} $i 1387} 1388for {set i 65536} {$i<=0x10ffff} {incr i 139} { 1389 do_execsql_test func-30.5.$i {SELECT unicode(char($i))} $i 1390} 1391 1392# Test char(). 1393# 1394do_execsql_test func-31.1 { 1395 SELECT char(), length(char()), typeof(char()) 1396} {{} 0 text} 1397 1398# sqlite3_value_frombind() 1399# 1400do_execsql_test func-32.100 { 1401 SELECT test_frombind(1,2,3,4); 1402} {0} 1403do_execsql_test func-32.110 { 1404 SELECT test_frombind(1,2,?,4); 1405} {4} 1406do_execsql_test func-32.120 { 1407 SELECT test_frombind(1,(?),4,?+7); 1408} {2} 1409do_execsql_test func-32.130 { 1410 DROP TABLE IF EXISTS t1; 1411 CREATE TABLE t1(a,b,c,e,f); 1412 INSERT INTO t1 VALUES(1,2.5,'xyz',x'e0c1b2a3',null); 1413 SELECT test_frombind(a,b,c,e,f,$xyz) FROM t1; 1414} {32} 1415do_execsql_test func-32.140 { 1416 SELECT test_frombind(a,b,c,e,f,$xyz+f) FROM t1; 1417} {0} 1418do_execsql_test func-32.150 { 1419 SELECT test_frombind(x.a,y.b,x.c,:123,y.e,x.f,$xyz+y.f) FROM t1 x, t1 y; 1420} {8} 1421 1422# 2019-08-15 1423# Direct-only functions. 1424# 1425proc testdirectonly {x} {return [expr {$x*2}]} 1426do_test func-33.1 { 1427 db func testdirectonly -directonly testdirectonly 1428 db eval {SELECT testdirectonly(15)} 1429} {30} 1430do_catchsql_test func-33.2 { 1431 CREATE VIEW v33(y) AS SELECT testdirectonly(15); 1432 SELECT * FROM v33; 1433} {1 {unsafe use of testdirectonly()}} 1434do_execsql_test func-33.3 { 1435 SELECT * FROM (SELECT testdirectonly(15)) AS v33; 1436} {30} 1437do_execsql_test func-33.4 { 1438 WITH c(x) AS (SELECT testdirectonly(15)) 1439 SELECT * FROM c; 1440} {30} 1441do_catchsql_test func-33.5 { 1442 WITH c(x) AS (SELECT * FROM v33) 1443 SELECT * FROM c; 1444} {1 {unsafe use of testdirectonly()}} 1445do_execsql_test func-33.10 { 1446 CREATE TABLE t33a(a,b); 1447 CREATE TABLE t33b(x,y); 1448 CREATE TRIGGER r1 AFTER INSERT ON t33a BEGIN 1449 INSERT INTO t33b(x,y) VALUES(testdirectonly(new.a),new.b); 1450 END; 1451} {} 1452do_catchsql_test func-33.11 { 1453 INSERT INTO t33a VALUES(1,2); 1454} {1 {unsafe use of testdirectonly()}} 1455do_execsql_test func-33.20 { 1456 ALTER TABLE t33a RENAME COLUMN a TO aaa; 1457 SELECT sql FROM sqlite_master WHERE name='r1'; 1458} {{CREATE TRIGGER r1 AFTER INSERT ON t33a BEGIN 1459 INSERT INTO t33b(x,y) VALUES(testdirectonly(new.aaa),new.b); 1460 END}} 1461 1462# 2020-01-09 Yongheng fuzzer find 1463# The bug is in the register-validity debug logic, not in the SQLite core 1464# and as such it only impacts debug builds. Release builds work fine. 1465# 1466reset_db 1467do_execsql_test func-34.10 { 1468 CREATE TABLE t1(a INT CHECK( 1469 datetime( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 1470 10,11,12,13,14,15,16,17,18,19, 1471 20,21,22,23,24,25,26,27,28,29, 1472 30,31,32,33,34,35,36,37,38,39, 1473 40,41,42,43,44,45,46,47,48,a) 1474 ) 1475 ); 1476 INSERT INTO t1(a) VALUES(1),(2); 1477 SELECT * FROM t1; 1478} {1 2} 1479 1480# 2020-03-11 COALESCE() should short-circuit 1481# See also ticket 3c9eadd2a6ba0aa5 1482# Both issues stem from the fact that functions that could 1483# throw exceptions were being factored out into initialization 1484# code. The fix was to put those function calls inside of 1485# OP_Once instead. 1486# 1487reset_db 1488do_execsql_test func-35.100 { 1489 CREATE TABLE t1(x); 1490 SELECT coalesce(x, abs(-9223372036854775808)) FROM t1; 1491} {} 1492do_execsql_test func-35.110 { 1493 SELECT coalesce(x, 'xyz' LIKE printf('%.1000000c','y')) FROM t1; 1494} {} 1495do_execsql_test func-35.200 { 1496 CREATE TABLE t0(c0 CHECK(ABS(-9223372036854775808))); 1497 PRAGMA integrity_check; 1498} {ok} 1499 1500finish_test 1501