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