| OLD | NEW |
|---|
| (Empty) |
| 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 | |
| 15 set testdir [file dirname $argv0] | |
| 16 source $testdir/tester.tcl | |
| 17 set testprefix func | |
| 18 | |
| 19 # Create a table to work with. | |
| 20 # | |
| 21 do_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} | |
| 28 do_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 # | |
| 42 do_test func-1.0 { | |
| 43 execsql {SELECT length(t1) FROM tbl1 ORDER BY t1} | |
| 44 } {4 2 7 8 4} | |
| 45 do_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()}} | |
| 49 do_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()}} | |
| 53 do_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} | |
| 57 do_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 # | |
| 63 do_test func-2.0 { | |
| 64 execsql {SELECT substr(t1,1,2) FROM tbl1 ORDER BY t1} | |
| 65 } {fr is pr so th} | |
| 66 do_test func-2.1 { | |
| 67 execsql {SELECT substr(t1,2,1) FROM tbl1 ORDER BY t1} | |
| 68 } {r s r o h} | |
| 69 do_test func-2.2 { | |
| 70 execsql {SELECT substr(t1,3,3) FROM tbl1 ORDER BY t1} | |
| 71 } {ee {} ogr ftw is} | |
| 72 do_test func-2.3 { | |
| 73 execsql {SELECT substr(t1,-1,1) FROM tbl1 ORDER BY t1} | |
| 74 } {e s m e s} | |
| 75 do_test func-2.4 { | |
| 76 execsql {SELECT substr(t1,-1,2) FROM tbl1 ORDER BY t1} | |
| 77 } {e s m e s} | |
| 78 do_test func-2.5 { | |
| 79 execsql {SELECT substr(t1,-2,1) FROM tbl1 ORDER BY t1} | |
| 80 } {e i a r i} | |
| 81 do_test func-2.6 { | |
| 82 execsql {SELECT substr(t1,-2,2) FROM tbl1 ORDER BY t1} | |
| 83 } {ee is am re is} | |
| 84 do_test func-2.7 { | |
| 85 execsql {SELECT substr(t1,-4,2) FROM tbl1 ORDER BY t1} | |
| 86 } {fr {} gr wa th} | |
| 87 do_test func-2.8 { | |
| 88 execsql {SELECT t1 FROM tbl1 ORDER BY substr(t1,2,20)} | |
| 89 } {this software free program is} | |
| 90 do_test func-2.9 { | |
| 91 execsql {SELECT substr(a,1,1) FROM t2} | |
| 92 } {1 {} 3 {} 6} | |
| 93 do_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 # | |
| 99 if {"\u1234"!="u1234"} { | |
| 100 | |
| 101 # Put some UTF-8 characters in the database | |
| 102 # | |
| 103 do_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" | |
| 110 do_test func-3.1 { | |
| 111 execsql {SELECT length(t1) FROM tbl1 ORDER BY t1} | |
| 112 } {5 10 8 5} | |
| 113 do_test func-3.2 { | |
| 114 execsql {SELECT substr(t1,1,2) FROM tbl1 ORDER BY t1} | |
| 115 } {UT ch co hi} | |
| 116 do_test func-3.3 { | |
| 117 execsql {SELECT substr(t1,1,3) FROM tbl1 ORDER BY t1} | |
| 118 } "UTF cha con hi\u1234" | |
| 119 do_test func-3.4 { | |
| 120 execsql {SELECT substr(t1,2,2) FROM tbl1 ORDER BY t1} | |
| 121 } "TF ha on i\u1234" | |
| 122 do_test func-3.5 { | |
| 123 execsql {SELECT substr(t1,2,3) FROM tbl1 ORDER BY t1} | |
| 124 } "TF- har ont i\u1234h" | |
| 125 do_test func-3.6 { | |
| 126 execsql {SELECT substr(t1,3,2) FROM tbl1 ORDER BY t1} | |
| 127 } "F- ar nt \u1234h" | |
| 128 do_test func-3.7 { | |
| 129 execsql {SELECT substr(t1,4,2) FROM tbl1 ORDER BY t1} | |
| 130 } "-8 ra ta ho" | |
| 131 do_test func-3.8 { | |
| 132 execsql {SELECT substr(t1,-1,1) FROM tbl1 ORDER BY t1} | |
| 133 } "8 s s o" | |
| 134 do_test func-3.9 { | |
| 135 execsql {SELECT substr(t1,-3,2) FROM tbl1 ORDER BY t1} | |
| 136 } "F- er in \u1234h" | |
| 137 do_test func-3.10 { | |
| 138 execsql {SELECT substr(t1,-4,3) FROM tbl1 ORDER BY t1} | |
| 139 } "TF- ter ain i\u1234h" | |
| 140 do_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 # | |
| 152 ifcapable !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 } | |
| 163 ifcapable 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 } | |
| 174 do_test func-4.2 { | |
| 175 catchsql {SELECT abs() FROM t1} | |
| 176 } {1 {wrong number of arguments to function abs()}} | |
| 177 ifcapable 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 } | |
| 185 ifcapable !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 } | |
| 195 do_test func-4.4.1 { | |
| 196 execsql {SELECT abs(a) FROM t2} | |
| 197 } {1 {} 345 {} 67890} | |
| 198 do_test func-4.4.2 { | |
| 199 execsql {SELECT abs(t1) FROM tbl1} | |
| 200 } {0.0 0.0 0.0 0.0 0.0} | |
| 201 | |
| 202 ifcapable 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 # | |
| 322 do_test func-5.1 { | |
| 323 execsql {SELECT upper(t1) FROM tbl1} | |
| 324 } {THIS PROGRAM IS FREE SOFTWARE} | |
| 325 do_test func-5.2 { | |
| 326 execsql {SELECT lower(upper(t1)) FROM tbl1} | |
| 327 } {this program is free software} | |
| 328 do_test func-5.3 { | |
| 329 execsql {SELECT upper(a), lower(a) FROM t2} | |
| 330 } {1 1 {} {} 345 345 {} {} 67890 67890} | |
| 331 ifcapable !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 } | |
| 336 do_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 # | |
| 342 do_test func-6.1 { | |
| 343 execsql {SELECT coalesce(a,'xyz') FROM t2} | |
| 344 } {1 xyz 345 xyz 67890} | |
| 345 do_test func-6.2 { | |
| 346 execsql {SELECT coalesce(upper(a),'nil') FROM t2} | |
| 347 } {1 nil 345 nil 67890} | |
| 348 do_test func-6.3 { | |
| 349 execsql {SELECT coalesce(nullif(1,1),'nil')} | |
| 350 } {nil} | |
| 351 do_test func-6.4 { | |
| 352 execsql {SELECT coalesce(nullif(1,2),'nil')} | |
| 353 } {1} | |
| 354 do_test func-6.5 { | |
| 355 execsql {SELECT coalesce(nullif(1,NULL),'nil')} | |
| 356 } {1} | |
| 357 | |
| 358 | |
| 359 # Test the last_insert_rowid() function | |
| 360 # | |
| 361 do_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 # | |
| 367 ifcapable 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 } | |
| 377 ifcapable !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 } | |
| 387 do_test func-8.2 { | |
| 388 execsql { | |
| 389 SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t2; | |
| 390 } | |
| 391 } {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP} | |
| 392 | |
| 393 ifcapable 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 } | |
| 408 do_test func-8.4 { | |
| 409 execsql { | |
| 410 SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3; | |
| 411 } | |
| 412 } {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP} | |
| 413 ifcapable 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} | |
| 432 ifcapable 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 } | |
| 440 ifcapable !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 # | |
| 452 do_test func-9.1 { | |
| 453 execsql { | |
| 454 SELECT random() is not null; | |
| 455 } | |
| 456 } {1} | |
| 457 do_test func-9.2 { | |
| 458 execsql { | |
| 459 SELECT typeof(random()); | |
| 460 } | |
| 461 } {integer} | |
| 462 do_test func-9.3 { | |
| 463 execsql { | |
| 464 SELECT randomblob(32) is not null; | |
| 465 } | |
| 466 } {1} | |
| 467 do_test func-9.4 { | |
| 468 execsql { | |
| 469 SELECT typeof(randomblob(32)); | |
| 470 } | |
| 471 } {blob} | |
| 472 do_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 # | |
| 483 ifcapable bloblit { | |
| 484 do_test func-9.10 { | |
| 485 execsql {SELECT hex(x'00112233445566778899aAbBcCdDeEfF')} | |
| 486 } {00112233445566778899AABBCCDDEEFF} | |
| 487 } | |
| 488 set encoding [db one {PRAGMA encoding}] | |
| 489 if {$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 # | |
| 516 set ::DB [sqlite3_connection_pointer db] | |
| 517 sqlite_register_test_function $::DB testfunc | |
| 518 do_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}} | |
| 523 do_test func-10.2 { | |
| 524 execsql { | |
| 525 SELECT testfunc( | |
| 526 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', | |
| 527 'int', 1234 | |
| 528 ); | |
| 529 } | |
| 530 } {1234} | |
| 531 do_test func-10.3 { | |
| 532 execsql { | |
| 533 SELECT testfunc( | |
| 534 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', | |
| 535 'string', NULL | |
| 536 ); | |
| 537 } | |
| 538 } {{}} | |
| 539 | |
| 540 ifcapable 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 # | |
| 571 do_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 # | |
| 586 if {[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 } | |
| 601 do_test func-12.2 { | |
| 602 execsql { | |
| 603 SELECT test_destructor_count(); | |
| 604 } | |
| 605 } {0} | |
| 606 do_test func-12.3 { | |
| 607 execsql { | |
| 608 SELECT test_destructor('hello')||' world' | |
| 609 } | |
| 610 } {{hello world}} | |
| 611 do_test func-12.4 { | |
| 612 execsql { | |
| 613 SELECT test_destructor_count(); | |
| 614 } | |
| 615 } {0} | |
| 616 do_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} | |
| 624 do_test func-12.6 { | |
| 625 execsql { | |
| 626 SELECT test_destructor_count(); | |
| 627 } | |
| 628 } {0} | |
| 629 do_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. | |
| 639 do_test func-13.1 { | |
| 640 execsql { | |
| 641 SELECT test_auxdata('hello world'); | |
| 642 } | |
| 643 } {0} | |
| 644 | |
| 645 do_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 } {} | |
| 652 do_test func-13.3 { | |
| 653 execsql { | |
| 654 SELECT test_auxdata('hello world') FROM t4; | |
| 655 } | |
| 656 } {0 1} | |
| 657 do_test func-13.4 { | |
| 658 execsql { | |
| 659 SELECT test_auxdata('hello world', 123) FROM t4; | |
| 660 } | |
| 661 } {{0 0} {1 1}} | |
| 662 do_test func-13.5 { | |
| 663 execsql { | |
| 664 SELECT test_auxdata('hello world', a) FROM t4; | |
| 665 } | |
| 666 } {{0 0} {1 0}} | |
| 667 do_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. | |
| 674 do_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. | |
| 687 do_execsql_test 13.8.1 { | |
| 688 SELECT test_auxdata('constant') FROM t4; | |
| 689 } {0 1} | |
| 690 do_execsql_test 13.8.2 { | |
| 691 SELECT test_auxdata('constant') FROM t4; | |
| 692 } {0 1} | |
| 693 db cache flush | |
| 694 do_execsql_test 13.8.3 { | |
| 695 SELECT test_auxdata('constant') FROM t4; | |
| 696 } {0 1} | |
| 697 set V "one" | |
| 698 do_execsql_test 13.8.4 { | |
| 699 SELECT test_auxdata($V), $V FROM t4; | |
| 700 } {0 one 1 one} | |
| 701 set V "two" | |
| 702 do_execsql_test 13.8.5 { | |
| 703 SELECT test_auxdata($V), $V FROM t4; | |
| 704 } {0 two 1 two} | |
| 705 db cache flush | |
| 706 set V "three" | |
| 707 do_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 | |
| 713 do_test func-14.1 { | |
| 714 catch { | |
| 715 db function [string repeat X 254] {return "hello"} | |
| 716 } | |
| 717 } {0} | |
| 718 do_test func-14.2 { | |
| 719 catch { | |
| 720 db function [string repeat X 256] {return "hello"} | |
| 721 } | |
| 722 } {1} | |
| 723 | |
| 724 do_test func-15.1 { | |
| 725 catchsql {select test_error(NULL)} | |
| 726 } {1 {}} | |
| 727 do_test func-15.2 { | |
| 728 catchsql {select test_error('this is the error message')} | |
| 729 } {1 {this is the error message}} | |
| 730 do_test func-15.3 { | |
| 731 catchsql {select test_error('this is the error message',12)} | |
| 732 } {1 {this is the error message}} | |
| 733 do_test func-15.4 { | |
| 734 db errorcode | |
| 735 } {12} | |
| 736 | |
| 737 # Test the quote function for BLOB and NULL values. | |
| 738 do_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 # | |
| 753 do_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 # | |
| 763 do_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} | |
| 772 ifcapable 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 # | |
| 785 do_test func-18.3 { | |
| 786 execsql { | |
| 787 DELETE FROM t5; | |
| 788 SELECT sum(x), total(x) FROM t5; | |
| 789 } | |
| 790 } {{} 0.0} | |
| 791 do_test func-18.4 { | |
| 792 execsql { | |
| 793 INSERT INTO t5 VALUES(NULL); | |
| 794 SELECT sum(x), total(x) FROM t5 | |
| 795 } | |
| 796 } {{} 0.0} | |
| 797 do_test func-18.5 { | |
| 798 execsql { | |
| 799 INSERT INTO t5 VALUES(NULL); | |
| 800 SELECT sum(x), total(x) FROM t5 | |
| 801 } | |
| 802 } {{} 0.0} | |
| 803 do_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 # | |
| 814 do_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 | |
| 822 do_test func-18.11 { | |
| 823 execsql { | |
| 824 SELECT typeof(sum(x)) FROM t6 | |
| 825 } | |
| 826 } integer | |
| 827 ifcapable 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 } | |
| 840 ifcapable !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 } | |
| 853 if {[working_64bit_int]} { | |
| 854 do_test func-18.14 { | |
| 855 execsql { | |
| 856 SELECT sum(-9223372036854775805); | |
| 857 } | |
| 858 } -9223372036854775805 | |
| 859 } | |
| 860 ifcapable compound&&subquery { | |
| 861 | |
| 862 do_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}} | |
| 869 if {[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 } | |
| 885 do_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}} | |
| 892 do_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} | |
| 897 do_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} | |
| 902 do_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} | |
| 907 do_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 # | |
| 917 if {[working_64bit_int]} { | |
| 918 do_test func-18.31 { | |
| 919 catchsql { | |
| 920 SELECT abs(-9223372036854775807); | |
| 921 } | |
| 922 } {0 9223372036854775807} | |
| 923 } | |
| 924 do_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 # | |
| 932 do_test func-19.1 { | |
| 933 execsql { | |
| 934 SELECT match(a,b) FROM t1 WHERE 0; | |
| 935 } | |
| 936 } {} | |
| 937 do_test func-19.2 { | |
| 938 catchsql { | |
| 939 SELECT 'abc' MATCH 'xyz'; | |
| 940 } | |
| 941 } {1 {unable to use function MATCH in the requested context}} | |
| 942 do_test func-19.3 { | |
| 943 catchsql { | |
| 944 SELECT 'abc' NOT MATCH 'xyz'; | |
| 945 } | |
| 946 } {1 {unable to use function MATCH in the requested context}} | |
| 947 do_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 # | |
| 955 if {![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 # | |
| 984 do_test func-21.1 { | |
| 985 catchsql { | |
| 986 SELECT replace(1,2); | |
| 987 } | |
| 988 } {1 {wrong number of arguments to function replace()}} | |
| 989 do_test func-21.2 { | |
| 990 catchsql { | |
| 991 SELECT replace(1,2,3,4); | |
| 992 } | |
| 993 } {1 {wrong number of arguments to function replace()}} | |
| 994 do_test func-21.3 { | |
| 995 execsql { | |
| 996 SELECT typeof(replace("This is the main test string", NULL, "ALT")); | |
| 997 } | |
| 998 } {null} | |
| 999 do_test func-21.4 { | |
| 1000 execsql { | |
| 1001 SELECT typeof(replace(NULL, "main", "ALT")); | |
| 1002 } | |
| 1003 } {null} | |
| 1004 do_test func-21.5 { | |
| 1005 execsql { | |
| 1006 SELECT typeof(replace("This is the main test string", "main", NULL)); | |
| 1007 } | |
| 1008 } {null} | |
| 1009 do_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}} | |
| 1014 do_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}} | |
| 1019 do_test func-21.8 { | |
| 1020 execsql { | |
| 1021 SELECT replace("aaaaaaa", "a", "0123456789"); | |
| 1022 } | |
| 1023 } {0123456789012345678901234567890123456789012345678901234567890123456789} | |
| 1024 | |
| 1025 ifcapable 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 # | |
| 1039 do_test func-22.1 { | |
| 1040 catchsql {SELECT trim(1,2,3)} | |
| 1041 } {1 {wrong number of arguments to function trim()}} | |
| 1042 do_test func-22.2 { | |
| 1043 catchsql {SELECT ltrim(1,2,3)} | |
| 1044 } {1 {wrong number of arguments to function ltrim()}} | |
| 1045 do_test func-22.3 { | |
| 1046 catchsql {SELECT rtrim(1,2,3)} | |
| 1047 } {1 {wrong number of arguments to function rtrim()}} | |
| 1048 do_test func-22.4 { | |
| 1049 execsql {SELECT trim(' hi ');} | |
| 1050 } {hi} | |
| 1051 do_test func-22.5 { | |
| 1052 execsql {SELECT ltrim(' hi ');} | |
| 1053 } {{hi }} | |
| 1054 do_test func-22.6 { | |
| 1055 execsql {SELECT rtrim(' hi ');} | |
| 1056 } {{ hi}} | |
| 1057 do_test func-22.7 { | |
| 1058 execsql {SELECT trim(' hi ','xyz');} | |
| 1059 } {{ hi }} | |
| 1060 do_test func-22.8 { | |
| 1061 execsql {SELECT ltrim(' hi ','xyz');} | |
| 1062 } {{ hi }} | |
| 1063 do_test func-22.9 { | |
| 1064 execsql {SELECT rtrim(' hi ','xyz');} | |
| 1065 } {{ hi }} | |
| 1066 do_test func-22.10 { | |
| 1067 execsql {SELECT trim('xyxzy hi zzzy','xyz');} | |
| 1068 } {{ hi }} | |
| 1069 do_test func-22.11 { | |
| 1070 execsql {SELECT ltrim('xyxzy hi zzzy','xyz');} | |
| 1071 } {{ hi zzzy}} | |
| 1072 do_test func-22.12 { | |
| 1073 execsql {SELECT rtrim('xyxzy hi zzzy','xyz');} | |
| 1074 } {{xyxzy hi }} | |
| 1075 do_test func-22.13 { | |
| 1076 execsql {SELECT trim(' hi ','');} | |
| 1077 } {{ hi }} | |
| 1078 if {[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 } | |
| 1090 do_test func-22.20 { | |
| 1091 execsql {SELECT typeof(trim(NULL));} | |
| 1092 } {null} | |
| 1093 do_test func-22.21 { | |
| 1094 execsql {SELECT typeof(trim(NULL,'xyz'));} | |
| 1095 } {null} | |
| 1096 do_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 # | |
| 1102 ifcapable 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 # | |
| 1113 do_test func-24.1 { | |
| 1114 execsql { | |
| 1115 SELECT group_concat(t1) FROM tbl1 | |
| 1116 } | |
| 1117 } {this,program,is,free,software} | |
| 1118 do_test func-24.2 { | |
| 1119 execsql { | |
| 1120 SELECT group_concat(t1,' ') FROM tbl1 | |
| 1121 } | |
| 1122 } {{this program is free software}} | |
| 1123 do_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}} | |
| 1128 do_test func-24.4 { | |
| 1129 execsql { | |
| 1130 SELECT group_concat(NULL,t1) FROM tbl1 | |
| 1131 } | |
| 1132 } {{}} | |
| 1133 do_test func-24.5 { | |
| 1134 execsql { | |
| 1135 SELECT group_concat(t1,NULL) FROM tbl1 | |
| 1136 } | |
| 1137 } {thisprogramisfreesoftware} | |
| 1138 do_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 # | |
| 1148 unset -nocomplain midargs | |
| 1149 set midargs {} | |
| 1150 unset -nocomplain midres | |
| 1151 set midres {} | |
| 1152 unset -nocomplain result | |
| 1153 for {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 # | |
| 1167 do_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} | |
| 1172 do_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 # | |
| 1181 do_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} | |
| 1186 do_test func-24.11 { | |
| 1187 execsql { | |
| 1188 SELECT group_concat(CASE WHEN t1!='software' THEN null ELSE t1 END) FROM tbl1 | |
| 1189 } | |
| 1190 } {software} | |
| 1191 do_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 | |
| 1198 do_test func-24.13 { | |
| 1199 execsql { | |
| 1200 SELECT typeof(group_concat(x)) FROM (SELECT '' AS x); | |
| 1201 } | |
| 1202 } {text} | |
| 1203 do_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 # | |
| 1214 do_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 # | |
| 1221 do_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 # | |
| 1229 do_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_12
3456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_12
3456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_12
3456789_123456789_123456789([join $a ,]); | |
| 1236 " | |
| 1237 } {{}} | |
| 1238 do_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_12
3456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_12
3456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_12
3456789_123456789_123456789([join $a ,]); | |
| 1245 " | |
| 1246 } {1 {too many arguments on function nullx_123456789_123456789_123456789_1234567
89_123456789_123456789_123456789_123456789_123456789_123456789_123456789_1234567
89_123456789_123456789_123456789_123456789_123456789_123456789_123456789_1234567
89_123456789_123456789_123456789_123456789_123456789}} | |
| 1247 do_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_12
3456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_12
3456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_12
3456789_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()}} | |
| 1256 do_test func-26.5 { | |
| 1257 catchsql " | |
| 1258 SELECT nullx_123456789_123456789_123456789_123456789_123456789_123456789_12
3456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_12
3456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_12
3456789_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}} | |
| 1261 do_test func-26.6 { | |
| 1262 catchsql " | |
| 1263 SELECT nullx_123456789_123456789_123456789_123456789_123456789_123456789_12
3456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_12
3456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_12
3456789_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 | |
| 1267 do_test func-27.1 { | |
| 1268 catchsql {SELECT coalesce()} | |
| 1269 } {1 {wrong number of arguments to function coalesce()}} | |
| 1270 do_test func-27.2 { | |
| 1271 catchsql {SELECT coalesce(1)} | |
| 1272 } {1 {wrong number of arguments to function coalesce()}} | |
| 1273 do_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 # | |
| 1280 do_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 # | |
| 1292 do_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} | |
| 1304 do_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} | |
| 1309 do_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} | |
| 1315 if {[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 } | |
| 1324 do_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} | |
| 1330 do_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 # | |
| 1340 do_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} | |
| 1347 do_execsql_test func-29.11 { | |
| 1348 SELECT length(f), length(g), length(h), length(i) FROM t29b; | |
| 1349 } {0 0 1 1} | |
| 1350 do_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 # | |
| 1362 do_execsql_test func-30.1 {SELECT unicode('$');} 36 | |
| 1363 do_execsql_test func-30.2 [subst {SELECT unicode('\u00A2');}] 162 | |
| 1364 do_execsql_test func-30.3 [subst {SELECT unicode('\u20AC');}] 8364 | |
| 1365 do_execsql_test func-30.4 {SELECT char(36,162,8364);} [subst {$\u00A2\u20AC}] | |
| 1366 | |
| 1367 for {set i 1} {$i<0xd800} {incr i 13} { | |
| 1368 do_execsql_test func-30.5.$i {SELECT unicode(char($i))} $i | |
| 1369 } | |
| 1370 for {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 } | |
| 1374 for {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 # | |
| 1380 do_execsql_test func-31.1 { | |
| 1381 SELECT char(), length(char()), typeof(char()) | |
| 1382 } {{} 0 text} | |
| 1383 finish_test | |
| OLD | NEW |
|---|
Chromium Code Reviews
Issue 2363173002:
[sqlite] Remove obsolete reference version 3.8.7.4. (Closed)
