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