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 17 18# Create a table to work with. 19# 20do_test func-0.0 { 21 execsql {CREATE TABLE tbl1(t1 text)} 22 foreach word {this program is free software} { 23 execsql "INSERT INTO tbl1 VALUES('$word')" 24 } 25 execsql {SELECT t1 FROM tbl1 ORDER BY t1} 26} {free is program software this} 27do_test func-0.1 { 28 execsql { 29 CREATE TABLE t2(a); 30 INSERT INTO t2 VALUES(1); 31 INSERT INTO t2 VALUES(NULL); 32 INSERT INTO t2 VALUES(345); 33 INSERT INTO t2 VALUES(NULL); 34 INSERT INTO t2 VALUES(67890); 35 SELECT * FROM t2; 36 } 37} {1 {} 345 {} 67890} 38 39# Check out the length() function 40# 41do_test func-1.0 { 42 execsql {SELECT length(t1) FROM tbl1 ORDER BY t1} 43} {4 2 7 8 4} 44do_test func-1.1 { 45 set r [catch {execsql {SELECT length(*) FROM tbl1 ORDER BY t1}} msg] 46 lappend r $msg 47} {1 {wrong number of arguments to function length()}} 48do_test func-1.2 { 49 set r [catch {execsql {SELECT length(t1,5) FROM tbl1 ORDER BY t1}} msg] 50 lappend r $msg 51} {1 {wrong number of arguments to function length()}} 52do_test func-1.3 { 53 execsql {SELECT length(t1), count(*) FROM tbl1 GROUP BY length(t1) 54 ORDER BY length(t1)} 55} {2 1 4 2 7 1 8 1} 56do_test func-1.4 { 57 execsql {SELECT coalesce(length(a),-1) FROM t2} 58} {1 -1 3 -1 5} 59 60# Check out the substr() function 61# 62do_test func-2.0 { 63 execsql {SELECT substr(t1,1,2) FROM tbl1 ORDER BY t1} 64} {fr is pr so th} 65do_test func-2.1 { 66 execsql {SELECT substr(t1,2,1) FROM tbl1 ORDER BY t1} 67} {r s r o h} 68do_test func-2.2 { 69 execsql {SELECT substr(t1,3,3) FROM tbl1 ORDER BY t1} 70} {ee {} ogr ftw is} 71do_test func-2.3 { 72 execsql {SELECT substr(t1,-1,1) FROM tbl1 ORDER BY t1} 73} {e s m e s} 74do_test func-2.4 { 75 execsql {SELECT substr(t1,-1,2) FROM tbl1 ORDER BY t1} 76} {e s m e s} 77do_test func-2.5 { 78 execsql {SELECT substr(t1,-2,1) FROM tbl1 ORDER BY t1} 79} {e i a r i} 80do_test func-2.6 { 81 execsql {SELECT substr(t1,-2,2) FROM tbl1 ORDER BY t1} 82} {ee is am re is} 83do_test func-2.7 { 84 execsql {SELECT substr(t1,-4,2) FROM tbl1 ORDER BY t1} 85} {fr {} gr wa th} 86do_test func-2.8 { 87 execsql {SELECT t1 FROM tbl1 ORDER BY substr(t1,2,20)} 88} {this software free program is} 89do_test func-2.9 { 90 execsql {SELECT substr(a,1,1) FROM t2} 91} {1 {} 3 {} 6} 92do_test func-2.10 { 93 execsql {SELECT substr(a,2,2) FROM t2} 94} {{} {} 45 {} 78} 95 96# Only do the following tests if TCL has UTF-8 capabilities 97# 98if {"\u1234"!="u1234"} { 99 100# Put some UTF-8 characters in the database 101# 102do_test func-3.0 { 103 execsql {DELETE FROM tbl1} 104 foreach word "contains UTF-8 characters hi\u1234ho" { 105 execsql "INSERT INTO tbl1 VALUES('$word')" 106 } 107 execsql {SELECT t1 FROM tbl1 ORDER BY t1} 108} "UTF-8 characters contains hi\u1234ho" 109do_test func-3.1 { 110 execsql {SELECT length(t1) FROM tbl1 ORDER BY t1} 111} {5 10 8 5} 112do_test func-3.2 { 113 execsql {SELECT substr(t1,1,2) FROM tbl1 ORDER BY t1} 114} {UT ch co hi} 115do_test func-3.3 { 116 execsql {SELECT substr(t1,1,3) FROM tbl1 ORDER BY t1} 117} "UTF cha con hi\u1234" 118do_test func-3.4 { 119 execsql {SELECT substr(t1,2,2) FROM tbl1 ORDER BY t1} 120} "TF ha on i\u1234" 121do_test func-3.5 { 122 execsql {SELECT substr(t1,2,3) FROM tbl1 ORDER BY t1} 123} "TF- har ont i\u1234h" 124do_test func-3.6 { 125 execsql {SELECT substr(t1,3,2) FROM tbl1 ORDER BY t1} 126} "F- ar nt \u1234h" 127do_test func-3.7 { 128 execsql {SELECT substr(t1,4,2) FROM tbl1 ORDER BY t1} 129} "-8 ra ta ho" 130do_test func-3.8 { 131 execsql {SELECT substr(t1,-1,1) FROM tbl1 ORDER BY t1} 132} "8 s s o" 133do_test func-3.9 { 134 execsql {SELECT substr(t1,-3,2) FROM tbl1 ORDER BY t1} 135} "F- er in \u1234h" 136do_test func-3.10 { 137 execsql {SELECT substr(t1,-4,3) FROM tbl1 ORDER BY t1} 138} "TF- ter ain i\u1234h" 139do_test func-3.99 { 140 execsql {DELETE FROM tbl1} 141 foreach word {this program is free software} { 142 execsql "INSERT INTO tbl1 VALUES('$word')" 143 } 144 execsql {SELECT t1 FROM tbl1} 145} {this program is free software} 146 147} ;# End \u1234!=u1234 148 149# Test the abs() and round() functions. 150# 151ifcapable !floatingpoint { 152 do_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,12345678901234,-1234567890); 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()}} 161} 162ifcapable floatingpoint { 163 do_test func-4.1 { 164 execsql { 165 CREATE TABLE t1(a,b,c); 166 INSERT INTO t1 VALUES(1,2,3); 167 INSERT INTO t1 VALUES(2,1.2345678901234,-12345.67890); 168 INSERT INTO t1 VALUES(3,-2,-5); 169 } 170 catchsql {SELECT abs(a,b) FROM t1} 171 } {1 {wrong number of arguments to function abs()}} 172} 173do_test func-4.2 { 174 catchsql {SELECT abs() FROM t1} 175} {1 {wrong number of arguments to function abs()}} 176ifcapable floatingpoint { 177 do_test func-4.3 { 178 catchsql {SELECT abs(b) FROM t1 ORDER BY a} 179 } {0 {2 1.2345678901234 2}} 180 do_test func-4.4 { 181 catchsql {SELECT abs(c) FROM t1 ORDER BY a} 182 } {0 {3 12345.6789 5}} 183} 184ifcapable !floatingpoint { 185 if {[working_64bit_int]} { 186 do_test func-4.3 { 187 catchsql {SELECT abs(b) FROM t1 ORDER BY a} 188 } {0 {2 12345678901234 2}} 189 } 190 do_test func-4.4 { 191 catchsql {SELECT abs(c) FROM t1 ORDER BY a} 192 } {0 {3 1234567890 5}} 193} 194do_test func-4.4.1 { 195 execsql {SELECT abs(a) FROM t2} 196} {1 {} 345 {} 67890} 197do_test func-4.4.2 { 198 execsql {SELECT abs(t1) FROM tbl1} 199} {0.0 0.0 0.0 0.0 0.0} 200 201ifcapable floatingpoint { 202 do_test func-4.5 { 203 catchsql {SELECT round(a,b,c) FROM t1} 204 } {1 {wrong number of arguments to function round()}} 205 do_test func-4.6 { 206 catchsql {SELECT round(b,2) FROM t1 ORDER BY b} 207 } {0 {-2.0 1.23 2.0}} 208 do_test func-4.7 { 209 catchsql {SELECT round(b,0) FROM t1 ORDER BY a} 210 } {0 {2.0 1.0 -2.0}} 211 do_test func-4.8 { 212 catchsql {SELECT round(c) FROM t1 ORDER BY a} 213 } {0 {3.0 -12346.0 -5.0}} 214 do_test func-4.9 { 215 catchsql {SELECT round(c,a) FROM t1 ORDER BY a} 216 } {0 {3.0 -12345.68 -5.0}} 217 do_test func-4.10 { 218 catchsql {SELECT 'x' || round(c,a) || 'y' FROM t1 ORDER BY a} 219 } {0 {x3.0y x-12345.68y x-5.0y}} 220 do_test func-4.11 { 221 catchsql {SELECT round() FROM t1 ORDER BY a} 222 } {1 {wrong number of arguments to function round()}} 223 do_test func-4.12 { 224 execsql {SELECT coalesce(round(a,2),'nil') FROM t2} 225 } {1.0 nil 345.0 nil 67890.0} 226 do_test func-4.13 { 227 execsql {SELECT round(t1,2) FROM tbl1} 228 } {0.0 0.0 0.0 0.0 0.0} 229 do_test func-4.14 { 230 execsql {SELECT typeof(round(5.1,1));} 231 } {real} 232 do_test func-4.15 { 233 execsql {SELECT typeof(round(5.1));} 234 } {real} 235 do_test func-4.16 { 236 catchsql {SELECT round(b,2.0) FROM t1 ORDER BY b} 237 } {0 {-2.0 1.23 2.0}} 238 # Verify some values reported on the mailing list. 239 # Some of these fail on MSVC builds with 64-bit 240 # long doubles, but not on GCC builds with 80-bit 241 # long doubles. 242 for {set i 1} {$i<999} {incr i} { 243 set x1 [expr 40222.5 + $i] 244 set x2 [expr 40223.0 + $i] 245 do_test func-4.17.$i { 246 execsql {SELECT round($x1);} 247 } $x2 248 } 249 for {set i 1} {$i<999} {incr i} { 250 set x1 [expr 40222.05 + $i] 251 set x2 [expr 40222.10 + $i] 252 do_test func-4.18.$i { 253 execsql {SELECT round($x1,1);} 254 } $x2 255 } 256 do_test func-4.20 { 257 execsql {SELECT round(40223.4999999999);} 258 } {40223.0} 259 do_test func-4.21 { 260 execsql {SELECT round(40224.4999999999);} 261 } {40224.0} 262 do_test func-4.22 { 263 execsql {SELECT round(40225.4999999999);} 264 } {40225.0} 265 for {set i 1} {$i<10} {incr i} { 266 do_test func-4.23.$i { 267 execsql {SELECT round(40223.4999999999,$i);} 268 } {40223.5} 269 do_test func-4.24.$i { 270 execsql {SELECT round(40224.4999999999,$i);} 271 } {40224.5} 272 do_test func-4.25.$i { 273 execsql {SELECT round(40225.4999999999,$i);} 274 } {40225.5} 275 } 276 for {set i 10} {$i<32} {incr i} { 277 do_test func-4.26.$i { 278 execsql {SELECT round(40223.4999999999,$i);} 279 } {40223.4999999999} 280 do_test func-4.27.$i { 281 execsql {SELECT round(40224.4999999999,$i);} 282 } {40224.4999999999} 283 do_test func-4.28.$i { 284 execsql {SELECT round(40225.4999999999,$i);} 285 } {40225.4999999999} 286 } 287 do_test func-4.29 { 288 execsql {SELECT round(1234567890.5);} 289 } {1234567891.0} 290 do_test func-4.30 { 291 execsql {SELECT round(12345678901.5);} 292 } {12345678902.0} 293 do_test func-4.31 { 294 execsql {SELECT round(123456789012.5);} 295 } {123456789013.0} 296 do_test func-4.32 { 297 execsql {SELECT round(1234567890123.5);} 298 } {1234567890124.0} 299 do_test func-4.33 { 300 execsql {SELECT round(12345678901234.5);} 301 } {12345678901235.0} 302 do_test func-4.34 { 303 execsql {SELECT round(1234567890123.35,1);} 304 } {1234567890123.4} 305 do_test func-4.35 { 306 execsql {SELECT round(1234567890123.445,2);} 307 } {1234567890123.45} 308 do_test func-4.36 { 309 execsql {SELECT round(99999999999994.5);} 310 } {99999999999995.0} 311 do_test func-4.37 { 312 execsql {SELECT round(9999999999999.55,1);} 313 } {9999999999999.6} 314 do_test func-4.38 { 315 execsql {SELECT round(9999999999999.556,2);} 316 } {9999999999999.56} 317} 318 319# Test the upper() and lower() functions 320# 321do_test func-5.1 { 322 execsql {SELECT upper(t1) FROM tbl1} 323} {THIS PROGRAM IS FREE SOFTWARE} 324do_test func-5.2 { 325 execsql {SELECT lower(upper(t1)) FROM tbl1} 326} {this program is free software} 327do_test func-5.3 { 328 execsql {SELECT upper(a), lower(a) FROM t2} 329} {1 1 {} {} 345 345 {} {} 67890 67890} 330ifcapable !icu { 331 do_test func-5.4 { 332 catchsql {SELECT upper(a,5) FROM t2} 333 } {1 {wrong number of arguments to function upper()}} 334} 335do_test func-5.5 { 336 catchsql {SELECT upper(*) FROM t2} 337} {1 {wrong number of arguments to function upper()}} 338 339# Test the coalesce() and nullif() functions 340# 341do_test func-6.1 { 342 execsql {SELECT coalesce(a,'xyz') FROM t2} 343} {1 xyz 345 xyz 67890} 344do_test func-6.2 { 345 execsql {SELECT coalesce(upper(a),'nil') FROM t2} 346} {1 nil 345 nil 67890} 347do_test func-6.3 { 348 execsql {SELECT coalesce(nullif(1,1),'nil')} 349} {nil} 350do_test func-6.4 { 351 execsql {SELECT coalesce(nullif(1,2),'nil')} 352} {1} 353do_test func-6.5 { 354 execsql {SELECT coalesce(nullif(1,NULL),'nil')} 355} {1} 356 357 358# Test the last_insert_rowid() function 359# 360do_test func-7.1 { 361 execsql {SELECT last_insert_rowid()} 362} [db last_insert_rowid] 363 364# Tests for aggregate functions and how they handle NULLs. 365# 366ifcapable floatingpoint { 367 do_test func-8.1 { 368 ifcapable explain { 369 execsql {EXPLAIN SELECT sum(a) FROM t2;} 370 } 371 execsql { 372 SELECT sum(a), count(a), round(avg(a),2), min(a), max(a), count(*) FROM t2; 373 } 374 } {68236 3 22745.33 1 67890 5} 375} 376ifcapable !floatingpoint { 377 do_test func-8.1 { 378 ifcapable explain { 379 execsql {EXPLAIN SELECT sum(a) FROM t2;} 380 } 381 execsql { 382 SELECT sum(a), count(a), avg(a), min(a), max(a), count(*) FROM t2; 383 } 384 } {68236 3 22745.0 1 67890 5} 385} 386do_test func-8.2 { 387 execsql { 388 SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t2; 389 } 390} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP} 391 392ifcapable tempdb { 393 do_test func-8.3 { 394 execsql { 395 CREATE TEMP TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC; 396 SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3; 397 } 398 } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP} 399} else { 400 do_test func-8.3 { 401 execsql { 402 CREATE TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC; 403 SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3; 404 } 405 } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP} 406} 407do_test func-8.4 { 408 execsql { 409 SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3; 410 } 411} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP} 412ifcapable compound { 413 do_test func-8.5 { 414 execsql { 415 SELECT sum(x) FROM (SELECT '9223372036' || '854775807' AS x 416 UNION ALL SELECT -9223372036854775807) 417 } 418 } {0} 419 do_test func-8.6 { 420 execsql { 421 SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775807' AS x 422 UNION ALL SELECT -9223372036854775807) 423 } 424 } {integer} 425 do_test func-8.7 { 426 execsql { 427 SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775808' AS x 428 UNION ALL SELECT -9223372036854775807) 429 } 430 } {real} 431ifcapable floatingpoint { 432 do_test func-8.8 { 433 execsql { 434 SELECT sum(x)>0.0 FROM (SELECT '9223372036' || '854775808' AS x 435 UNION ALL SELECT -9223372036850000000) 436 } 437 } {1} 438} 439ifcapable !floatingpoint { 440 do_test func-8.8 { 441 execsql { 442 SELECT sum(x)>0 FROM (SELECT '9223372036' || '854775808' AS x 443 UNION ALL SELECT -9223372036850000000) 444 } 445 } {1} 446} 447} 448 449# How do you test the random() function in a meaningful, deterministic way? 450# 451do_test func-9.1 { 452 execsql { 453 SELECT random() is not null; 454 } 455} {1} 456do_test func-9.2 { 457 execsql { 458 SELECT typeof(random()); 459 } 460} {integer} 461do_test func-9.3 { 462 execsql { 463 SELECT randomblob(32) is not null; 464 } 465} {1} 466do_test func-9.4 { 467 execsql { 468 SELECT typeof(randomblob(32)); 469 } 470} {blob} 471do_test func-9.5 { 472 execsql { 473 SELECT length(randomblob(32)), length(randomblob(-5)), 474 length(randomblob(2000)) 475 } 476} {32 1 2000} 477 478# The "hex()" function was added in order to be able to render blobs 479# generated by randomblob(). So this seems like a good place to test 480# hex(). 481# 482ifcapable bloblit { 483 do_test func-9.10 { 484 execsql {SELECT hex(x'00112233445566778899aAbBcCdDeEfF')} 485 } {00112233445566778899AABBCCDDEEFF} 486} 487set encoding [db one {PRAGMA encoding}] 488if {$encoding=="UTF-16le"} { 489 do_test func-9.11-utf16le { 490 execsql {SELECT hex(replace('abcdefg','ef','12'))} 491 } {6100620063006400310032006700} 492 do_test func-9.12-utf16le { 493 execsql {SELECT hex(replace('abcdefg','','12'))} 494 } {6100620063006400650066006700} 495 do_test func-9.13-utf16le { 496 execsql {SELECT hex(replace('aabcdefg','a','aaa'))} 497 } {610061006100610061006100620063006400650066006700} 498} elseif {$encoding=="UTF-8"} { 499 do_test func-9.11-utf8 { 500 execsql {SELECT hex(replace('abcdefg','ef','12'))} 501 } {61626364313267} 502 do_test func-9.12-utf8 { 503 execsql {SELECT hex(replace('abcdefg','','12'))} 504 } {61626364656667} 505 do_test func-9.13-utf8 { 506 execsql {SELECT hex(replace('aabcdefg','a','aaa'))} 507 } {616161616161626364656667} 508} 509 510# Use the "sqlite_register_test_function" TCL command which is part of 511# the text fixture in order to verify correct operation of some of 512# the user-defined SQL function APIs that are not used by the built-in 513# functions. 514# 515set ::DB [sqlite3_connection_pointer db] 516sqlite_register_test_function $::DB testfunc 517do_test func-10.1 { 518 catchsql { 519 SELECT testfunc(NULL,NULL); 520 } 521} {1 {first argument should be one of: int int64 string double null value}} 522do_test func-10.2 { 523 execsql { 524 SELECT testfunc( 525 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 526 'int', 1234 527 ); 528 } 529} {1234} 530do_test func-10.3 { 531 execsql { 532 SELECT testfunc( 533 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 534 'string', NULL 535 ); 536 } 537} {{}} 538 539ifcapable floatingpoint { 540 do_test func-10.4 { 541 execsql { 542 SELECT testfunc( 543 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 544 'double', 1.234 545 ); 546 } 547 } {1.234} 548 do_test func-10.5 { 549 execsql { 550 SELECT testfunc( 551 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 552 'int', 1234, 553 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 554 'string', NULL, 555 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 556 'double', 1.234, 557 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 558 'int', 1234, 559 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 560 'string', NULL, 561 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 562 'double', 1.234 563 ); 564 } 565 } {1.234} 566} 567 568# Test the built-in sqlite_version(*) SQL function. 569# 570do_test func-11.1 { 571 execsql { 572 SELECT sqlite_version(*); 573 } 574} [sqlite3 -version] 575 576# Test that destructors passed to sqlite3 by calls to sqlite3_result_text() 577# etc. are called. These tests use two special user-defined functions 578# (implemented in func.c) only available in test builds. 579# 580# Function test_destructor() takes one argument and returns a copy of the 581# text form of that argument. A destructor is associated with the return 582# value. Function test_destructor_count() returns the number of outstanding 583# destructor calls for values returned by test_destructor(). 584# 585if {[db eval {PRAGMA encoding}]=="UTF-8"} { 586 do_test func-12.1-utf8 { 587 execsql { 588 SELECT test_destructor('hello world'), test_destructor_count(); 589 } 590 } {{hello world} 1} 591} else { 592 ifcapable {utf16} { 593 do_test func-12.1-utf16 { 594 execsql { 595 SELECT test_destructor16('hello world'), test_destructor_count(); 596 } 597 } {{hello world} 1} 598 } 599} 600do_test func-12.2 { 601 execsql { 602 SELECT test_destructor_count(); 603 } 604} {0} 605do_test func-12.3 { 606 execsql { 607 SELECT test_destructor('hello')||' world' 608 } 609} {{hello world}} 610do_test func-12.4 { 611 execsql { 612 SELECT test_destructor_count(); 613 } 614} {0} 615do_test func-12.5 { 616 execsql { 617 CREATE TABLE t4(x); 618 INSERT INTO t4 VALUES(test_destructor('hello')); 619 INSERT INTO t4 VALUES(test_destructor('world')); 620 SELECT min(test_destructor(x)), max(test_destructor(x)) FROM t4; 621 } 622} {hello world} 623do_test func-12.6 { 624 execsql { 625 SELECT test_destructor_count(); 626 } 627} {0} 628do_test func-12.7 { 629 execsql { 630 DROP TABLE t4; 631 } 632} {} 633 634 635# Test that the auxdata API for scalar functions works. This test uses 636# a special user-defined function only available in test builds, 637# test_auxdata(). Function test_auxdata() takes any number of arguments. 638do_test func-13.1 { 639 execsql { 640 SELECT test_auxdata('hello world'); 641 } 642} {0} 643 644do_test func-13.2 { 645 execsql { 646 CREATE TABLE t4(a, b); 647 INSERT INTO t4 VALUES('abc', 'def'); 648 INSERT INTO t4 VALUES('ghi', 'jkl'); 649 } 650} {} 651do_test func-13.3 { 652 execsql { 653 SELECT test_auxdata('hello world') FROM t4; 654 } 655} {0 1} 656do_test func-13.4 { 657 execsql { 658 SELECT test_auxdata('hello world', 123) FROM t4; 659 } 660} {{0 0} {1 1}} 661do_test func-13.5 { 662 execsql { 663 SELECT test_auxdata('hello world', a) FROM t4; 664 } 665} {{0 0} {1 0}} 666do_test func-13.6 { 667 execsql { 668 SELECT test_auxdata('hello'||'world', a) FROM t4; 669 } 670} {{0 0} {1 0}} 671 672# Test that auxilary data is preserved between calls for SQL variables. 673do_test func-13.7 { 674 set DB [sqlite3_connection_pointer db] 675 set sql "SELECT test_auxdata( ? , a ) FROM t4;" 676 set STMT [sqlite3_prepare $DB $sql -1 TAIL] 677 sqlite3_bind_text $STMT 1 hello\000 -1 678 set res [list] 679 while { "SQLITE_ROW"==[sqlite3_step $STMT] } { 680 lappend res [sqlite3_column_text $STMT 0] 681 } 682 lappend res [sqlite3_finalize $STMT] 683} {{0 0} {1 0} SQLITE_OK} 684 685# Test that auxiliary data is discarded when a statement is reset. 686do_execsql_test 13.8.1 { 687 SELECT test_auxdata('constant') FROM t4; 688} {0 1} 689do_execsql_test 13.8.2 { 690 SELECT test_auxdata('constant') FROM t4; 691} {0 1} 692db cache flush 693do_execsql_test 13.8.3 { 694 SELECT test_auxdata('constant') FROM t4; 695} {0 1} 696set V "one" 697do_execsql_test 13.8.4 { 698 SELECT test_auxdata($V), $V FROM t4; 699} {0 one 1 one} 700set V "two" 701do_execsql_test 13.8.5 { 702 SELECT test_auxdata($V), $V FROM t4; 703} {0 two 1 two} 704db cache flush 705set V "three" 706do_execsql_test 2.3 { 707 SELECT test_auxdata($V), $V FROM t4; 708} {0 three 1 three} 709 710 711# Make sure that a function with a very long name is rejected 712do_test func-14.1 { 713 catch { 714 db function [string repeat X 254] {return "hello"} 715 } 716} {0} 717do_test func-14.2 { 718 catch { 719 db function [string repeat X 256] {return "hello"} 720 } 721} {1} 722 723do_test func-15.1 { 724 catchsql {select test_error(NULL)} 725} {1 {}} 726do_test func-15.2 { 727 catchsql {select test_error('this is the error message')} 728} {1 {this is the error message}} 729do_test func-15.3 { 730 catchsql {select test_error('this is the error message',12)} 731} {1 {this is the error message}} 732do_test func-15.4 { 733 db errorcode 734} {12} 735 736# Test the quote function for BLOB and NULL values. 737do_test func-16.1 { 738 execsql { 739 CREATE TABLE tbl2(a, b); 740 } 741 set STMT [sqlite3_prepare $::DB "INSERT INTO tbl2 VALUES(?, ?)" -1 TAIL] 742 sqlite3_bind_blob $::STMT 1 abc 3 743 sqlite3_step $::STMT 744 sqlite3_finalize $::STMT 745 execsql { 746 SELECT quote(a), quote(b) FROM tbl2; 747 } 748} {X'616263' NULL} 749 750# Correctly handle function error messages that include %. Ticket #1354 751# 752do_test func-17.1 { 753 proc testfunc1 args {error "Error %d with %s percents %p"} 754 db function testfunc1 ::testfunc1 755 catchsql { 756 SELECT testfunc1(1,2,3); 757 } 758} {1 {Error %d with %s percents %p}} 759 760# The SUM function should return integer results when all inputs are integer. 761# 762do_test func-18.1 { 763 execsql { 764 CREATE TABLE t5(x); 765 INSERT INTO t5 VALUES(1); 766 INSERT INTO t5 VALUES(-99); 767 INSERT INTO t5 VALUES(10000); 768 SELECT sum(x) FROM t5; 769 } 770} {9902} 771ifcapable floatingpoint { 772 do_test func-18.2 { 773 execsql { 774 INSERT INTO t5 VALUES(0.0); 775 SELECT sum(x) FROM t5; 776 } 777 } {9902.0} 778} 779 780# The sum of nothing is NULL. But the sum of all NULLs is NULL. 781# 782# The TOTAL of nothing is 0.0. 783# 784do_test func-18.3 { 785 execsql { 786 DELETE FROM t5; 787 SELECT sum(x), total(x) FROM t5; 788 } 789} {{} 0.0} 790do_test func-18.4 { 791 execsql { 792 INSERT INTO t5 VALUES(NULL); 793 SELECT sum(x), total(x) FROM t5 794 } 795} {{} 0.0} 796do_test func-18.5 { 797 execsql { 798 INSERT INTO t5 VALUES(NULL); 799 SELECT sum(x), total(x) FROM t5 800 } 801} {{} 0.0} 802do_test func-18.6 { 803 execsql { 804 INSERT INTO t5 VALUES(123); 805 SELECT sum(x), total(x) FROM t5 806 } 807} {123 123.0} 808 809# Ticket #1664, #1669, #1670, #1674: An integer overflow on SUM causes 810# an error. The non-standard TOTAL() function continues to give a helpful 811# result. 812# 813do_test func-18.10 { 814 execsql { 815 CREATE TABLE t6(x INTEGER); 816 INSERT INTO t6 VALUES(1); 817 INSERT INTO t6 VALUES(1<<62); 818 SELECT sum(x) - ((1<<62)+1) from t6; 819 } 820} 0 821do_test func-18.11 { 822 execsql { 823 SELECT typeof(sum(x)) FROM t6 824 } 825} integer 826ifcapable floatingpoint { 827 do_test func-18.12 { 828 catchsql { 829 INSERT INTO t6 VALUES(1<<62); 830 SELECT sum(x) - ((1<<62)*2.0+1) from t6; 831 } 832 } {1 {integer overflow}} 833 do_test func-18.13 { 834 execsql { 835 SELECT total(x) - ((1<<62)*2.0+1) FROM t6 836 } 837 } 0.0 838} 839ifcapable !floatingpoint { 840 do_test func-18.12 { 841 catchsql { 842 INSERT INTO t6 VALUES(1<<62); 843 SELECT sum(x) - ((1<<62)*2+1) from t6; 844 } 845 } {1 {integer overflow}} 846 do_test func-18.13 { 847 execsql { 848 SELECT total(x) - ((1<<62)*2+1) FROM t6 849 } 850 } 0.0 851} 852if {[working_64bit_int]} { 853 do_test func-18.14 { 854 execsql { 855 SELECT sum(-9223372036854775805); 856 } 857 } -9223372036854775805 858} 859ifcapable compound&&subquery { 860 861do_test func-18.15 { 862 catchsql { 863 SELECT sum(x) FROM 864 (SELECT 9223372036854775807 AS x UNION ALL 865 SELECT 10 AS x); 866 } 867} {1 {integer overflow}} 868if {[working_64bit_int]} { 869 do_test func-18.16 { 870 catchsql { 871 SELECT sum(x) FROM 872 (SELECT 9223372036854775807 AS x UNION ALL 873 SELECT -10 AS x); 874 } 875 } {0 9223372036854775797} 876 do_test func-18.17 { 877 catchsql { 878 SELECT sum(x) FROM 879 (SELECT -9223372036854775807 AS x UNION ALL 880 SELECT 10 AS x); 881 } 882 } {0 -9223372036854775797} 883} 884do_test func-18.18 { 885 catchsql { 886 SELECT sum(x) FROM 887 (SELECT -9223372036854775807 AS x UNION ALL 888 SELECT -10 AS x); 889 } 890} {1 {integer overflow}} 891do_test func-18.19 { 892 catchsql { 893 SELECT sum(x) FROM (SELECT 9 AS x UNION ALL SELECT -10 AS x); 894 } 895} {0 -1} 896do_test func-18.20 { 897 catchsql { 898 SELECT sum(x) FROM (SELECT -9 AS x UNION ALL SELECT 10 AS x); 899 } 900} {0 1} 901do_test func-18.21 { 902 catchsql { 903 SELECT sum(x) FROM (SELECT -10 AS x UNION ALL SELECT 9 AS x); 904 } 905} {0 -1} 906do_test func-18.22 { 907 catchsql { 908 SELECT sum(x) FROM (SELECT 10 AS x UNION ALL SELECT -9 AS x); 909 } 910} {0 1} 911 912} ;# ifcapable compound&&subquery 913 914# Integer overflow on abs() 915# 916if {[working_64bit_int]} { 917 do_test func-18.31 { 918 catchsql { 919 SELECT abs(-9223372036854775807); 920 } 921 } {0 9223372036854775807} 922} 923do_test func-18.32 { 924 catchsql { 925 SELECT abs(-9223372036854775807-1); 926 } 927} {1 {integer overflow}} 928 929# The MATCH function exists but is only a stub and always throws an error. 930# 931do_test func-19.1 { 932 execsql { 933 SELECT match(a,b) FROM t1 WHERE 0; 934 } 935} {} 936do_test func-19.2 { 937 catchsql { 938 SELECT 'abc' MATCH 'xyz'; 939 } 940} {1 {unable to use function MATCH in the requested context}} 941do_test func-19.3 { 942 catchsql { 943 SELECT 'abc' NOT MATCH 'xyz'; 944 } 945} {1 {unable to use function MATCH in the requested context}} 946do_test func-19.4 { 947 catchsql { 948 SELECT match(1,2,3); 949 } 950} {1 {wrong number of arguments to function match()}} 951 952# Soundex tests. 953# 954if {![catch {db eval {SELECT soundex('hello')}}]} { 955 set i 0 956 foreach {name sdx} { 957 euler E460 958 EULER E460 959 Euler E460 960 ellery E460 961 gauss G200 962 ghosh G200 963 hilbert H416 964 Heilbronn H416 965 knuth K530 966 kant K530 967 Lloyd L300 968 LADD L300 969 Lukasiewicz L222 970 Lissajous L222 971 A A000 972 12345 ?000 973 } { 974 incr i 975 do_test func-20.$i { 976 execsql {SELECT soundex($name)} 977 } $sdx 978 } 979} 980 981# Tests of the REPLACE function. 982# 983do_test func-21.1 { 984 catchsql { 985 SELECT replace(1,2); 986 } 987} {1 {wrong number of arguments to function replace()}} 988do_test func-21.2 { 989 catchsql { 990 SELECT replace(1,2,3,4); 991 } 992} {1 {wrong number of arguments to function replace()}} 993do_test func-21.3 { 994 execsql { 995 SELECT typeof(replace("This is the main test string", NULL, "ALT")); 996 } 997} {null} 998do_test func-21.4 { 999 execsql { 1000 SELECT typeof(replace(NULL, "main", "ALT")); 1001 } 1002} {null} 1003do_test func-21.5 { 1004 execsql { 1005 SELECT typeof(replace("This is the main test string", "main", NULL)); 1006 } 1007} {null} 1008do_test func-21.6 { 1009 execsql { 1010 SELECT replace("This is the main test string", "main", "ALT"); 1011 } 1012} {{This is the ALT test string}} 1013do_test func-21.7 { 1014 execsql { 1015 SELECT replace("This is the main test string", "main", "larger-main"); 1016 } 1017} {{This is the larger-main test string}} 1018do_test func-21.8 { 1019 execsql { 1020 SELECT replace("aaaaaaa", "a", "0123456789"); 1021 } 1022} {0123456789012345678901234567890123456789012345678901234567890123456789} 1023 1024ifcapable tclvar { 1025 do_test func-21.9 { 1026 # Attempt to exploit a buffer-overflow that at one time existed 1027 # in the REPLACE function. 1028 set ::str "[string repeat A 29998]CC[string repeat A 35537]" 1029 set ::rep [string repeat B 65536] 1030 execsql { 1031 SELECT LENGTH(REPLACE($::str, 'C', $::rep)); 1032 } 1033 } [expr 29998 + 2*65536 + 35537] 1034} 1035 1036# Tests for the TRIM, LTRIM and RTRIM functions. 1037# 1038do_test func-22.1 { 1039 catchsql {SELECT trim(1,2,3)} 1040} {1 {wrong number of arguments to function trim()}} 1041do_test func-22.2 { 1042 catchsql {SELECT ltrim(1,2,3)} 1043} {1 {wrong number of arguments to function ltrim()}} 1044do_test func-22.3 { 1045 catchsql {SELECT rtrim(1,2,3)} 1046} {1 {wrong number of arguments to function rtrim()}} 1047do_test func-22.4 { 1048 execsql {SELECT trim(' hi ');} 1049} {hi} 1050do_test func-22.5 { 1051 execsql {SELECT ltrim(' hi ');} 1052} {{hi }} 1053do_test func-22.6 { 1054 execsql {SELECT rtrim(' hi ');} 1055} {{ hi}} 1056do_test func-22.7 { 1057 execsql {SELECT trim(' hi ','xyz');} 1058} {{ hi }} 1059do_test func-22.8 { 1060 execsql {SELECT ltrim(' hi ','xyz');} 1061} {{ hi }} 1062do_test func-22.9 { 1063 execsql {SELECT rtrim(' hi ','xyz');} 1064} {{ hi }} 1065do_test func-22.10 { 1066 execsql {SELECT trim('xyxzy hi zzzy','xyz');} 1067} {{ hi }} 1068do_test func-22.11 { 1069 execsql {SELECT ltrim('xyxzy hi zzzy','xyz');} 1070} {{ hi zzzy}} 1071do_test func-22.12 { 1072 execsql {SELECT rtrim('xyxzy hi zzzy','xyz');} 1073} {{xyxzy hi }} 1074do_test func-22.13 { 1075 execsql {SELECT trim(' hi ','');} 1076} {{ hi }} 1077if {[db one {PRAGMA encoding}]=="UTF-8"} { 1078 do_test func-22.14 { 1079 execsql {SELECT hex(trim(x'c280e1bfbff48fbfbf6869',x'6162e1bfbfc280'))} 1080 } {F48FBFBF6869} 1081 do_test func-22.15 { 1082 execsql {SELECT hex(trim(x'6869c280e1bfbff48fbfbf61', 1083 x'6162e1bfbfc280f48fbfbf'))} 1084 } {6869} 1085 do_test func-22.16 { 1086 execsql {SELECT hex(trim(x'ceb1ceb2ceb3',x'ceb1'));} 1087 } {CEB2CEB3} 1088} 1089do_test func-22.20 { 1090 execsql {SELECT typeof(trim(NULL));} 1091} {null} 1092do_test func-22.21 { 1093 execsql {SELECT typeof(trim(NULL,'xyz'));} 1094} {null} 1095do_test func-22.22 { 1096 execsql {SELECT typeof(trim('hello',NULL));} 1097} {null} 1098 1099# This is to test the deprecated sqlite3_aggregate_count() API. 1100# 1101ifcapable deprecated { 1102 do_test func-23.1 { 1103 sqlite3_create_aggregate db 1104 execsql { 1105 SELECT legacy_count() FROM t6; 1106 } 1107 } {3} 1108} 1109 1110# The group_concat() function. 1111# 1112do_test func-24.1 { 1113 execsql { 1114 SELECT group_concat(t1) FROM tbl1 1115 } 1116} {this,program,is,free,software} 1117do_test func-24.2 { 1118 execsql { 1119 SELECT group_concat(t1,' ') FROM tbl1 1120 } 1121} {{this program is free software}} 1122do_test func-24.3 { 1123 execsql { 1124 SELECT group_concat(t1,' ' || rowid || ' ') FROM tbl1 1125 } 1126} {{this 2 program 3 is 4 free 5 software}} 1127do_test func-24.4 { 1128 execsql { 1129 SELECT group_concat(NULL,t1) FROM tbl1 1130 } 1131} {{}} 1132do_test func-24.5 { 1133 execsql { 1134 SELECT group_concat(t1,NULL) FROM tbl1 1135 } 1136} {thisprogramisfreesoftware} 1137do_test func-24.6 { 1138 execsql { 1139 SELECT 'BEGIN-'||group_concat(t1) FROM tbl1 1140 } 1141} {BEGIN-this,program,is,free,software} 1142 1143# Ticket #3179: Make sure aggregate functions can take many arguments. 1144# None of the built-in aggregates do this, so use the md5sum() from the 1145# test extensions. 1146# 1147unset -nocomplain midargs 1148set midargs {} 1149unset -nocomplain midres 1150set midres {} 1151unset -nocomplain result 1152for {set i 1} {$i<[sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG -1]} {incr i} { 1153 append midargs ,'/$i' 1154 append midres /$i 1155 set result [md5 \ 1156 "this${midres}program${midres}is${midres}free${midres}software${midres}"] 1157 set sql "SELECT md5sum(t1$midargs) FROM tbl1" 1158 do_test func-24.7.$i { 1159 db eval $::sql 1160 } $result 1161} 1162 1163# Ticket #3806. If the initial string in a group_concat is an empty 1164# string, the separator that follows should still be present. 1165# 1166do_test func-24.8 { 1167 execsql { 1168 SELECT group_concat(CASE t1 WHEN 'this' THEN '' ELSE t1 END) FROM tbl1 1169 } 1170} {,program,is,free,software} 1171do_test func-24.9 { 1172 execsql { 1173 SELECT group_concat(CASE WHEN t1!='software' THEN '' ELSE t1 END) FROM tbl1 1174 } 1175} {,,,,software} 1176 1177# Ticket #3923. Initial empty strings have a separator. But initial 1178# NULLs do not. 1179# 1180do_test func-24.10 { 1181 execsql { 1182 SELECT group_concat(CASE t1 WHEN 'this' THEN null ELSE t1 END) FROM tbl1 1183 } 1184} {program,is,free,software} 1185do_test func-24.11 { 1186 execsql { 1187 SELECT group_concat(CASE WHEN t1!='software' THEN null ELSE t1 END) FROM tbl1 1188 } 1189} {software} 1190do_test func-24.12 { 1191 execsql { 1192 SELECT group_concat(CASE t1 WHEN 'this' THEN '' 1193 WHEN 'program' THEN null ELSE t1 END) FROM tbl1 1194 } 1195} {,is,free,software} 1196 1197 1198# Use the test_isolation function to make sure that type conversions 1199# on function arguments do not effect subsequent arguments. 1200# 1201do_test func-25.1 { 1202 execsql {SELECT test_isolation(t1,t1) FROM tbl1} 1203} {this program is free software} 1204 1205# Try to misuse the sqlite3_create_function() interface. Verify that 1206# errors are returned. 1207# 1208do_test func-26.1 { 1209 abuse_create_function db 1210} {} 1211 1212# The previous test (func-26.1) registered a function with a very long 1213# function name that takes many arguments and always returns NULL. Verify 1214# that this function works correctly. 1215# 1216do_test func-26.2 { 1217 set a {} 1218 for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG} {incr i} { 1219 lappend a $i 1220 } 1221 db eval " 1222 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 ,]); 1223 " 1224} {{}} 1225do_test func-26.3 { 1226 set a {} 1227 for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG+1} {incr i} { 1228 lappend a $i 1229 } 1230 catchsql " 1231 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 ,]); 1232 " 1233} {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}} 1234do_test func-26.4 { 1235 set a {} 1236 for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG-1} {incr i} { 1237 lappend a $i 1238 } 1239 catchsql " 1240 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 ,]); 1241 " 1242} {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()}} 1243do_test func-26.5 { 1244 catchsql " 1245 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); 1246 " 1247} {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}} 1248do_test func-26.6 { 1249 catchsql " 1250 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); 1251 " 1252} {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}} 1253 1254do_test func-27.1 { 1255 catchsql {SELECT coalesce()} 1256} {1 {wrong number of arguments to function coalesce()}} 1257do_test func-27.2 { 1258 catchsql {SELECT coalesce(1)} 1259} {1 {wrong number of arguments to function coalesce()}} 1260do_test func-27.3 { 1261 catchsql {SELECT coalesce(1,2)} 1262} {0 1} 1263 1264# Ticket 2d401a94287b5 1265# Unknown function in a DEFAULT expression causes a segfault. 1266# 1267do_test func-28.1 { 1268 db eval { 1269 CREATE TABLE t28(x, y DEFAULT(nosuchfunc(1))); 1270 } 1271 catchsql { 1272 INSERT INTO t28(x) VALUES(1); 1273 } 1274} {1 {unknown function: nosuchfunc()}} 1275 1276# Verify that the length() and typeof() functions do not actually load 1277# the content of their argument. 1278# 1279do_test func-29.1 { 1280 db eval { 1281 CREATE TABLE t29(id INTEGER PRIMARY KEY, x, y); 1282 INSERT INTO t29 VALUES(1, 2, 3), (2, NULL, 4), (3, 4.5, 5); 1283 INSERT INTO t29 VALUES(4, randomblob(1000000), 6); 1284 INSERT INTO t29 VALUES(5, "hello", 7); 1285 } 1286 db close 1287 sqlite3 db test.db 1288 sqlite3_db_status db CACHE_MISS 1 1289 db eval {SELECT typeof(x), length(x), typeof(y) FROM t29 ORDER BY id} 1290} {integer 1 integer null {} integer real 3 integer blob 1000000 integer text 5 integer} 1291do_test func-29.2 { 1292 set x [lindex [sqlite3_db_status db CACHE_MISS 1] 1] 1293 if {$x<5} {set x 1} 1294 set x 1295} {1} 1296do_test func-29.3 { 1297 db close 1298 sqlite3 db test.db 1299 sqlite3_db_status db CACHE_MISS 1 1300 db eval {SELECT typeof(+x) FROM t29 ORDER BY id} 1301} {integer null real blob text} 1302if {[permutation] != "mmap"} { 1303 do_test func-29.4 { 1304 set x [lindex [sqlite3_db_status db CACHE_MISS 1] 1] 1305 if {$x>100} {set x many} 1306 set x 1307 } {many} 1308} 1309do_test func-29.5 { 1310 db close 1311 sqlite3 db test.db 1312 sqlite3_db_status db CACHE_MISS 1 1313 db eval {SELECT sum(length(x)) FROM t29} 1314} {1000009} 1315do_test func-29.6 { 1316 set x [lindex [sqlite3_db_status db CACHE_MISS 1] 1] 1317 if {$x<5} {set x 1} 1318 set x 1319} {1} 1320 1321do_execsql_test func-30.1 {SELECT unicode('$');} 36 1322do_execsql_test func-30.2 [subst {SELECT unicode('\u00A2');}] 162 1323do_execsql_test func-30.3 [subst {SELECT unicode('\u20AC');}] 8364 1324do_execsql_test func-30.4 {SELECT char(36,162,8364);} [subst {$\u00A2\u20AC}] 1325 1326for {set i 1} {$i<0xd800} {incr i 13} { 1327 do_execsql_test func-30.5.$i {SELECT unicode(char($i))} $i 1328} 1329for {set i 57344} {$i<=0xfffd} {incr i 17} { 1330 if {$i==0xfeff} continue 1331 do_execsql_test func-30.5.$i {SELECT unicode(char($i))} $i 1332} 1333for {set i 65536} {$i<=0x10ffff} {incr i 139} { 1334 do_execsql_test func-30.5.$i {SELECT unicode(char($i))} $i 1335} 1336 1337finish_test 1338