000001 # 2001 September 15
000002 #
000003 # The author disclaims copyright to this source code. In place of
000004 # a legal notice, here is a blessing:
000005 #
000006 # May you do good and not evil.
000007 # May you find forgiveness for yourself and forgive others.
000008 # May you share freely, never taking more than you give.
000009 #
000010 #***********************************************************************
000011 # This file implements regression tests for SQLite library. The
000012 # focus of this file is testing built-in functions.
000013 #
000014
000015 set testdir [file dirname $argv0]
000016 source $testdir/tester.tcl
000017 set testprefix func
000018
000019 # Create a table to work with.
000020 #
000021 do_test func-0.0 {
000022 execsql {CREATE TABLE tbl1(t1 text)}
000023 foreach word {this program is free software} {
000024 execsql "INSERT INTO tbl1 VALUES('$word')"
000025 }
000026 execsql {SELECT t1 FROM tbl1 ORDER BY t1}
000027 } {free is program software this}
000028 do_test func-0.1 {
000029 execsql {
000030 CREATE TABLE t2(a);
000031 INSERT INTO t2 VALUES(1);
000032 INSERT INTO t2 VALUES(NULL);
000033 INSERT INTO t2 VALUES(345);
000034 INSERT INTO t2 VALUES(NULL);
000035 INSERT INTO t2 VALUES(67890);
000036 SELECT * FROM t2;
000037 }
000038 } {1 {} 345 {} 67890}
000039
000040 # Check out the length() function
000041 #
000042 do_test func-1.0 {
000043 execsql {SELECT length(t1) FROM tbl1 ORDER BY t1}
000044 } {4 2 7 8 4}
000045 do_test func-1.1 {
000046 set r [catch {execsql {SELECT length(*) FROM tbl1 ORDER BY t1}} msg]
000047 lappend r $msg
000048 } {1 {wrong number of arguments to function length()}}
000049 do_test func-1.2 {
000050 set r [catch {execsql {SELECT length(t1,5) FROM tbl1 ORDER BY t1}} msg]
000051 lappend r $msg
000052 } {1 {wrong number of arguments to function length()}}
000053 do_test func-1.3 {
000054 execsql {SELECT length(t1), count(*) FROM tbl1 GROUP BY length(t1)
000055 ORDER BY length(t1)}
000056 } {2 1 4 2 7 1 8 1}
000057 do_test func-1.4 {
000058 execsql {SELECT coalesce(length(a),-1) FROM t2}
000059 } {1 -1 3 -1 5}
000060
000061 # Check out the substr() function
000062 #
000063 do_test func-2.0 {
000064 execsql {SELECT substr(t1,1,2) FROM tbl1 ORDER BY t1}
000065 } {fr is pr so th}
000066 do_test func-2.1 {
000067 execsql {SELECT substr(t1,2,1) FROM tbl1 ORDER BY t1}
000068 } {r s r o h}
000069 do_test func-2.2 {
000070 execsql {SELECT substr(t1,3,3) FROM tbl1 ORDER BY t1}
000071 } {ee {} ogr ftw is}
000072 do_test func-2.3 {
000073 execsql {SELECT substr(t1,-1,1) FROM tbl1 ORDER BY t1}
000074 } {e s m e s}
000075 do_test func-2.4 {
000076 execsql {SELECT substr(t1,-1,2) FROM tbl1 ORDER BY t1}
000077 } {e s m e s}
000078 do_test func-2.5 {
000079 execsql {SELECT substr(t1,-2,1) FROM tbl1 ORDER BY t1}
000080 } {e i a r i}
000081 do_test func-2.6 {
000082 execsql {SELECT substr(t1,-2,2) FROM tbl1 ORDER BY t1}
000083 } {ee is am re is}
000084 do_test func-2.7 {
000085 execsql {SELECT substr(t1,-4,2) FROM tbl1 ORDER BY t1}
000086 } {fr {} gr wa th}
000087 do_test func-2.8 {
000088 execsql {SELECT t1 FROM tbl1 ORDER BY substr(t1,2,20)}
000089 } {this software free program is}
000090 do_test func-2.9 {
000091 execsql {SELECT substr(a,1,1) FROM t2}
000092 } {1 {} 3 {} 6}
000093 do_test func-2.10 {
000094 execsql {SELECT substr(a,2,2) FROM t2}
000095 } {{} {} 45 {} 78}
000096
000097 # Only do the following tests if TCL has UTF-8 capabilities
000098 #
000099 if {"\u1234"!="u1234"} {
000100
000101 # Put some UTF-8 characters in the database
000102 #
000103 do_test func-3.0 {
000104 execsql {DELETE FROM tbl1}
000105 foreach word "contains UTF-8 characters hi\u1234ho" {
000106 execsql "INSERT INTO tbl1 VALUES('$word')"
000107 }
000108 execsql {SELECT t1 FROM tbl1 ORDER BY t1}
000109 } "UTF-8 characters contains hi\u1234ho"
000110 do_test func-3.1 {
000111 execsql {SELECT length(t1) FROM tbl1 ORDER BY t1}
000112 } {5 10 8 5}
000113 do_test func-3.2 {
000114 execsql {SELECT substr(t1,1,2) FROM tbl1 ORDER BY t1}
000115 } {UT ch co hi}
000116 do_test func-3.3 {
000117 execsql {SELECT substr(t1,1,3) FROM tbl1 ORDER BY t1}
000118 } "UTF cha con hi\u1234"
000119 do_test func-3.4 {
000120 execsql {SELECT substr(t1,2,2) FROM tbl1 ORDER BY t1}
000121 } "TF ha on i\u1234"
000122 do_test func-3.5 {
000123 execsql {SELECT substr(t1,2,3) FROM tbl1 ORDER BY t1}
000124 } "TF- har ont i\u1234h"
000125 do_test func-3.6 {
000126 execsql {SELECT substr(t1,3,2) FROM tbl1 ORDER BY t1}
000127 } "F- ar nt \u1234h"
000128 do_test func-3.7 {
000129 execsql {SELECT substr(t1,4,2) FROM tbl1 ORDER BY t1}
000130 } "-8 ra ta ho"
000131 do_test func-3.8 {
000132 execsql {SELECT substr(t1,-1,1) FROM tbl1 ORDER BY t1}
000133 } "8 s s o"
000134 do_test func-3.9 {
000135 execsql {SELECT substr(t1,-3,2) FROM tbl1 ORDER BY t1}
000136 } "F- er in \u1234h"
000137 do_test func-3.10 {
000138 execsql {SELECT substr(t1,-4,3) FROM tbl1 ORDER BY t1}
000139 } "TF- ter ain i\u1234h"
000140 do_test func-3.99 {
000141 execsql {DELETE FROM tbl1}
000142 foreach word {this program is free software} {
000143 execsql "INSERT INTO tbl1 VALUES('$word')"
000144 }
000145 execsql {SELECT t1 FROM tbl1}
000146 } {this program is free software}
000147
000148 } ;# End \u1234!=u1234
000149
000150 # Test the abs() and round() functions.
000151 #
000152 ifcapable !floatingpoint {
000153 do_test func-4.1 {
000154 execsql {
000155 CREATE TABLE t1(a,b,c);
000156 INSERT INTO t1 VALUES(1,2,3);
000157 INSERT INTO t1 VALUES(2,12345678901234,-1234567890);
000158 INSERT INTO t1 VALUES(3,-2,-5);
000159 }
000160 catchsql {SELECT abs(a,b) FROM t1}
000161 } {1 {wrong number of arguments to function abs()}}
000162 }
000163 ifcapable floatingpoint {
000164 do_test func-4.1 {
000165 execsql {
000166 CREATE TABLE t1(a,b,c);
000167 INSERT INTO t1 VALUES(1,2,3);
000168 INSERT INTO t1 VALUES(2,1.2345678901234,-12345.67890);
000169 INSERT INTO t1 VALUES(3,-2,-5);
000170 }
000171 catchsql {SELECT abs(a,b) FROM t1}
000172 } {1 {wrong number of arguments to function abs()}}
000173 }
000174 do_test func-4.2 {
000175 catchsql {SELECT abs() FROM t1}
000176 } {1 {wrong number of arguments to function abs()}}
000177 ifcapable floatingpoint {
000178 do_test func-4.3 {
000179 catchsql {SELECT abs(b) FROM t1 ORDER BY a}
000180 } {0 {2 1.2345678901234 2}}
000181 do_test func-4.4 {
000182 catchsql {SELECT abs(c) FROM t1 ORDER BY a}
000183 } {0 {3 12345.6789 5}}
000184 }
000185 ifcapable !floatingpoint {
000186 if {[working_64bit_int]} {
000187 do_test func-4.3 {
000188 catchsql {SELECT abs(b) FROM t1 ORDER BY a}
000189 } {0 {2 12345678901234 2}}
000190 }
000191 do_test func-4.4 {
000192 catchsql {SELECT abs(c) FROM t1 ORDER BY a}
000193 } {0 {3 1234567890 5}}
000194 }
000195 do_test func-4.4.1 {
000196 execsql {SELECT abs(a) FROM t2}
000197 } {1 {} 345 {} 67890}
000198 do_test func-4.4.2 {
000199 execsql {SELECT abs(t1) FROM tbl1}
000200 } {0.0 0.0 0.0 0.0 0.0}
000201
000202 ifcapable floatingpoint {
000203 do_test func-4.5 {
000204 catchsql {SELECT round(a,b,c) FROM t1}
000205 } {1 {wrong number of arguments to function round()}}
000206 do_test func-4.6 {
000207 catchsql {SELECT round(b,2) FROM t1 ORDER BY b}
000208 } {0 {-2.0 1.23 2.0}}
000209 do_test func-4.7 {
000210 catchsql {SELECT round(b,0) FROM t1 ORDER BY a}
000211 } {0 {2.0 1.0 -2.0}}
000212 do_test func-4.8 {
000213 catchsql {SELECT round(c) FROM t1 ORDER BY a}
000214 } {0 {3.0 -12346.0 -5.0}}
000215 do_test func-4.9 {
000216 catchsql {SELECT round(c,a) FROM t1 ORDER BY a}
000217 } {0 {3.0 -12345.68 -5.0}}
000218 do_test func-4.10 {
000219 catchsql {SELECT 'x' || round(c,a) || 'y' FROM t1 ORDER BY a}
000220 } {0 {x3.0y x-12345.68y x-5.0y}}
000221 do_test func-4.11 {
000222 catchsql {SELECT round() FROM t1 ORDER BY a}
000223 } {1 {wrong number of arguments to function round()}}
000224 do_test func-4.12 {
000225 execsql {SELECT coalesce(round(a,2),'nil') FROM t2}
000226 } {1.0 nil 345.0 nil 67890.0}
000227 do_test func-4.13 {
000228 execsql {SELECT round(t1,2) FROM tbl1}
000229 } {0.0 0.0 0.0 0.0 0.0}
000230 do_test func-4.14 {
000231 execsql {SELECT typeof(round(5.1,1));}
000232 } {real}
000233 do_test func-4.15 {
000234 execsql {SELECT typeof(round(5.1));}
000235 } {real}
000236 do_test func-4.16 {
000237 catchsql {SELECT round(b,2.0) FROM t1 ORDER BY b}
000238 } {0 {-2.0 1.23 2.0}}
000239 # Verify some values reported on the mailing list.
000240 # Some of these fail on MSVC builds with 64-bit
000241 # long doubles, but not on GCC builds with 80-bit
000242 # long doubles.
000243 for {set i 1} {$i<999} {incr i} {
000244 set x1 [expr 40222.5 + $i]
000245 set x2 [expr 40223.0 + $i]
000246 do_test func-4.17.$i {
000247 execsql {SELECT round($x1);}
000248 } $x2
000249 }
000250 for {set i 1} {$i<999} {incr i} {
000251 set x1 [expr 40222.05 + $i]
000252 set x2 [expr 40222.10 + $i]
000253 do_test func-4.18.$i {
000254 execsql {SELECT round($x1,1);}
000255 } $x2
000256 }
000257 do_test func-4.20 {
000258 execsql {SELECT round(40223.4999999999);}
000259 } {40223.0}
000260 do_test func-4.21 {
000261 execsql {SELECT round(40224.4999999999);}
000262 } {40224.0}
000263 do_test func-4.22 {
000264 execsql {SELECT round(40225.4999999999);}
000265 } {40225.0}
000266 for {set i 1} {$i<10} {incr i} {
000267 do_test func-4.23.$i {
000268 execsql {SELECT round(40223.4999999999,$i);}
000269 } {40223.5}
000270 do_test func-4.24.$i {
000271 execsql {SELECT round(40224.4999999999,$i);}
000272 } {40224.5}
000273 do_test func-4.25.$i {
000274 execsql {SELECT round(40225.4999999999,$i);}
000275 } {40225.5}
000276 }
000277 for {set i 10} {$i<32} {incr i} {
000278 do_test func-4.26.$i {
000279 execsql {SELECT round(40223.4999999999,$i);}
000280 } {40223.4999999999}
000281 do_test func-4.27.$i {
000282 execsql {SELECT round(40224.4999999999,$i);}
000283 } {40224.4999999999}
000284 do_test func-4.28.$i {
000285 execsql {SELECT round(40225.4999999999,$i);}
000286 } {40225.4999999999}
000287 }
000288 do_test func-4.29 {
000289 execsql {SELECT round(1234567890.5);}
000290 } {1234567891.0}
000291 do_test func-4.30 {
000292 execsql {SELECT round(12345678901.5);}
000293 } {12345678902.0}
000294 do_test func-4.31 {
000295 execsql {SELECT round(123456789012.5);}
000296 } {123456789013.0}
000297 do_test func-4.32 {
000298 execsql {SELECT round(1234567890123.5);}
000299 } {1234567890124.0}
000300 do_test func-4.33 {
000301 execsql {SELECT round(12345678901234.5);}
000302 } {12345678901235.0}
000303 do_test func-4.34 {
000304 execsql {SELECT round(1234567890123.35,1);}
000305 } {1234567890123.4}
000306 do_test func-4.35 {
000307 execsql {SELECT round(1234567890123.445,2);}
000308 } {1234567890123.45}
000309 do_test func-4.36 {
000310 execsql {SELECT round(99999999999994.5);}
000311 } {99999999999995.0}
000312 do_test func-4.37 {
000313 execsql {SELECT round(9999999999999.55,1);}
000314 } {9999999999999.6}
000315 do_test func-4.38 {
000316 execsql {SELECT round(9999999999999.556,2);}
000317 } {9999999999999.56}
000318 do_test func-4.39 {
000319 string tolower [db eval {SELECT round(1e500), round(-1e500);}]
000320 } {inf -inf}
000321 }
000322
000323 # Test the upper() and lower() functions
000324 #
000325 do_test func-5.1 {
000326 execsql {SELECT upper(t1) FROM tbl1}
000327 } {THIS PROGRAM IS FREE SOFTWARE}
000328 do_test func-5.2 {
000329 execsql {SELECT lower(upper(t1)) FROM tbl1}
000330 } {this program is free software}
000331 do_test func-5.3 {
000332 execsql {SELECT upper(a), lower(a) FROM t2}
000333 } {1 1 {} {} 345 345 {} {} 67890 67890}
000334 ifcapable !icu {
000335 do_test func-5.4 {
000336 catchsql {SELECT upper(a,5) FROM t2}
000337 } {1 {wrong number of arguments to function upper()}}
000338 }
000339 do_test func-5.5 {
000340 catchsql {SELECT upper(*) FROM t2}
000341 } {1 {wrong number of arguments to function upper()}}
000342
000343 # Test the coalesce() and nullif() functions
000344 #
000345 do_test func-6.1 {
000346 execsql {SELECT coalesce(a,'xyz') FROM t2}
000347 } {1 xyz 345 xyz 67890}
000348 do_test func-6.2 {
000349 execsql {SELECT coalesce(upper(a),'nil') FROM t2}
000350 } {1 nil 345 nil 67890}
000351 do_test func-6.3 {
000352 execsql {SELECT coalesce(nullif(1,1),'nil')}
000353 } {nil}
000354 do_test func-6.4 {
000355 execsql {SELECT coalesce(nullif(1,2),'nil')}
000356 } {1}
000357 do_test func-6.5 {
000358 execsql {SELECT coalesce(nullif(1,NULL),'nil')}
000359 } {1}
000360
000361
000362 # Test the last_insert_rowid() function
000363 #
000364 do_test func-7.1 {
000365 execsql {SELECT last_insert_rowid()}
000366 } [db last_insert_rowid]
000367
000368 # Tests for aggregate functions and how they handle NULLs.
000369 #
000370 ifcapable floatingpoint {
000371 do_test func-8.1 {
000372 ifcapable explain {
000373 execsql {EXPLAIN SELECT sum(a) FROM t2;}
000374 }
000375 execsql {
000376 SELECT sum(a), count(a), round(avg(a),2), min(a), max(a), count(*) FROM t2;
000377 }
000378 } {68236 3 22745.33 1 67890 5}
000379 }
000380 ifcapable !floatingpoint {
000381 do_test func-8.1 {
000382 ifcapable explain {
000383 execsql {EXPLAIN SELECT sum(a) FROM t2;}
000384 }
000385 execsql {
000386 SELECT sum(a), count(a), avg(a), min(a), max(a), count(*) FROM t2;
000387 }
000388 } {68236 3 22745.0 1 67890 5}
000389 }
000390 do_test func-8.2 {
000391 execsql {
000392 SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t2;
000393 }
000394 } {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
000395
000396 ifcapable tempdb {
000397 do_test func-8.3 {
000398 execsql {
000399 CREATE TEMP TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;
000400 SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
000401 }
000402 } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
000403 } else {
000404 do_test func-8.3 {
000405 execsql {
000406 CREATE TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;
000407 SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
000408 }
000409 } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
000410 }
000411 do_test func-8.4 {
000412 execsql {
000413 SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
000414 }
000415 } {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
000416 ifcapable compound {
000417 do_test func-8.5 {
000418 execsql {
000419 SELECT sum(x) FROM (SELECT '9223372036' || '854775807' AS x
000420 UNION ALL SELECT -9223372036854775807)
000421 }
000422 } {0}
000423 do_test func-8.6 {
000424 execsql {
000425 SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775807' AS x
000426 UNION ALL SELECT -9223372036854775807)
000427 }
000428 } {integer}
000429 do_test func-8.7 {
000430 execsql {
000431 SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775808' AS x
000432 UNION ALL SELECT -9223372036854775807)
000433 }
000434 } {real}
000435 ifcapable floatingpoint {
000436 do_test func-8.8 {
000437 execsql {
000438 SELECT sum(x)>0.0 FROM (SELECT '9223372036' || '854775808' AS x
000439 UNION ALL SELECT -9223372036850000000)
000440 }
000441 } {1}
000442 }
000443 ifcapable !floatingpoint {
000444 do_test func-8.8 {
000445 execsql {
000446 SELECT sum(x)>0 FROM (SELECT '9223372036' || '854775808' AS x
000447 UNION ALL SELECT -9223372036850000000)
000448 }
000449 } {1}
000450 }
000451 }
000452
000453 # How do you test the random() function in a meaningful, deterministic way?
000454 #
000455 do_test func-9.1 {
000456 execsql {
000457 SELECT random() is not null;
000458 }
000459 } {1}
000460 do_test func-9.2 {
000461 execsql {
000462 SELECT typeof(random());
000463 }
000464 } {integer}
000465 do_test func-9.3 {
000466 execsql {
000467 SELECT randomblob(32) is not null;
000468 }
000469 } {1}
000470 do_test func-9.4 {
000471 execsql {
000472 SELECT typeof(randomblob(32));
000473 }
000474 } {blob}
000475 do_test func-9.5 {
000476 execsql {
000477 SELECT length(randomblob(32)), length(randomblob(-5)),
000478 length(randomblob(2000))
000479 }
000480 } {32 1 2000}
000481
000482 # The "hex()" function was added in order to be able to render blobs
000483 # generated by randomblob(). So this seems like a good place to test
000484 # hex().
000485 #
000486 ifcapable bloblit {
000487 do_test func-9.10 {
000488 execsql {SELECT hex(x'00112233445566778899aAbBcCdDeEfF')}
000489 } {00112233445566778899AABBCCDDEEFF}
000490 }
000491 set encoding [db one {PRAGMA encoding}]
000492 if {$encoding=="UTF-16le"} {
000493 do_test func-9.11-utf16le {
000494 execsql {SELECT hex(replace('abcdefg','ef','12'))}
000495 } {6100620063006400310032006700}
000496 do_test func-9.12-utf16le {
000497 execsql {SELECT hex(replace('abcdefg','','12'))}
000498 } {6100620063006400650066006700}
000499 do_test func-9.13-utf16le {
000500 execsql {SELECT hex(replace('aabcdefg','a','aaa'))}
000501 } {610061006100610061006100620063006400650066006700}
000502 } elseif {$encoding=="UTF-8"} {
000503 do_test func-9.11-utf8 {
000504 execsql {SELECT hex(replace('abcdefg','ef','12'))}
000505 } {61626364313267}
000506 do_test func-9.12-utf8 {
000507 execsql {SELECT hex(replace('abcdefg','','12'))}
000508 } {61626364656667}
000509 do_test func-9.13-utf8 {
000510 execsql {SELECT hex(replace('aabcdefg','a','aaa'))}
000511 } {616161616161626364656667}
000512 }
000513 do_execsql_test func-9.14 {
000514 WITH RECURSIVE c(x) AS (
000515 VALUES(1)
000516 UNION ALL
000517 SELECT x+1 FROM c WHERE x<1040
000518 )
000519 SELECT
000520 count(*),
000521 sum(length(replace(printf('abc%.*cxyz',x,'m'),'m','nnnn'))-(6+x*4))
000522 FROM c;
000523 } {1040 0}
000524
000525 # Use the "sqlite_register_test_function" TCL command which is part of
000526 # the text fixture in order to verify correct operation of some of
000527 # the user-defined SQL function APIs that are not used by the built-in
000528 # functions.
000529 #
000530 set ::DB [sqlite3_connection_pointer db]
000531 sqlite_register_test_function $::DB testfunc
000532 do_test func-10.1 {
000533 catchsql {
000534 SELECT testfunc(NULL,NULL);
000535 }
000536 } {1 {first argument should be one of: int int64 string double null value}}
000537 do_test func-10.2 {
000538 execsql {
000539 SELECT testfunc(
000540 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
000541 'int', 1234
000542 );
000543 }
000544 } {1234}
000545 do_test func-10.3 {
000546 execsql {
000547 SELECT testfunc(
000548 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
000549 'string', NULL
000550 );
000551 }
000552 } {{}}
000553
000554 ifcapable floatingpoint {
000555 do_test func-10.4 {
000556 execsql {
000557 SELECT testfunc(
000558 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
000559 'double', 1.234
000560 );
000561 }
000562 } {1.234}
000563 do_test func-10.5 {
000564 execsql {
000565 SELECT testfunc(
000566 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
000567 'int', 1234,
000568 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
000569 'string', NULL,
000570 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
000571 'double', 1.234,
000572 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
000573 'int', 1234,
000574 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
000575 'string', NULL,
000576 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
000577 'double', 1.234
000578 );
000579 }
000580 } {1.234}
000581 }
000582
000583 # Test the built-in sqlite_version(*) SQL function.
000584 #
000585 do_test func-11.1 {
000586 execsql {
000587 SELECT sqlite_version(*);
000588 }
000589 } [sqlite3 -version]
000590
000591 # Test that destructors passed to sqlite3 by calls to sqlite3_result_text()
000592 # etc. are called. These tests use two special user-defined functions
000593 # (implemented in func.c) only available in test builds.
000594 #
000595 # Function test_destructor() takes one argument and returns a copy of the
000596 # text form of that argument. A destructor is associated with the return
000597 # value. Function test_destructor_count() returns the number of outstanding
000598 # destructor calls for values returned by test_destructor().
000599 #
000600 if {[db eval {PRAGMA encoding}]=="UTF-8"} {
000601 do_test func-12.1-utf8 {
000602 execsql {
000603 SELECT test_destructor('hello world'), test_destructor_count();
000604 }
000605 } {{hello world} 1}
000606 } else {
000607 ifcapable {utf16} {
000608 do_test func-12.1-utf16 {
000609 execsql {
000610 SELECT test_destructor16('hello world'), test_destructor_count();
000611 }
000612 } {{hello world} 1}
000613 }
000614 }
000615 do_test func-12.2 {
000616 execsql {
000617 SELECT test_destructor_count();
000618 }
000619 } {0}
000620 do_test func-12.3 {
000621 execsql {
000622 SELECT test_destructor('hello')||' world'
000623 }
000624 } {{hello world}}
000625 do_test func-12.4 {
000626 execsql {
000627 SELECT test_destructor_count();
000628 }
000629 } {0}
000630 do_test func-12.5 {
000631 execsql {
000632 CREATE TABLE t4(x);
000633 INSERT INTO t4 VALUES(test_destructor('hello'));
000634 INSERT INTO t4 VALUES(test_destructor('world'));
000635 SELECT min(test_destructor(x)), max(test_destructor(x)) FROM t4;
000636 }
000637 } {hello world}
000638 do_test func-12.6 {
000639 execsql {
000640 SELECT test_destructor_count();
000641 }
000642 } {0}
000643 do_test func-12.7 {
000644 execsql {
000645 DROP TABLE t4;
000646 }
000647 } {}
000648
000649
000650 # Test that the auxdata API for scalar functions works. This test uses
000651 # a special user-defined function only available in test builds,
000652 # test_auxdata(). Function test_auxdata() takes any number of arguments.
000653 do_test func-13.1 {
000654 execsql {
000655 SELECT test_auxdata('hello world');
000656 }
000657 } {0}
000658
000659 do_test func-13.2 {
000660 execsql {
000661 CREATE TABLE t4(a, b);
000662 INSERT INTO t4 VALUES('abc', 'def');
000663 INSERT INTO t4 VALUES('ghi', 'jkl');
000664 }
000665 } {}
000666 do_test func-13.3 {
000667 execsql {
000668 SELECT test_auxdata('hello world') FROM t4;
000669 }
000670 } {0 1}
000671 do_test func-13.4 {
000672 execsql {
000673 SELECT test_auxdata('hello world', 123) FROM t4;
000674 }
000675 } {{0 0} {1 1}}
000676 do_test func-13.5 {
000677 execsql {
000678 SELECT test_auxdata('hello world', a) FROM t4;
000679 }
000680 } {{0 0} {1 0}}
000681 do_test func-13.6 {
000682 execsql {
000683 SELECT test_auxdata('hello'||'world', a) FROM t4;
000684 }
000685 } {{0 0} {1 0}}
000686
000687 # Test that auxilary data is preserved between calls for SQL variables.
000688 do_test func-13.7 {
000689 set DB [sqlite3_connection_pointer db]
000690 set sql "SELECT test_auxdata( ? , a ) FROM t4;"
000691 set STMT [sqlite3_prepare $DB $sql -1 TAIL]
000692 sqlite3_bind_text $STMT 1 hello\000 -1
000693 set res [list]
000694 while { "SQLITE_ROW"==[sqlite3_step $STMT] } {
000695 lappend res [sqlite3_column_text $STMT 0]
000696 }
000697 lappend res [sqlite3_finalize $STMT]
000698 } {{0 0} {1 0} SQLITE_OK}
000699
000700 # Test that auxiliary data is discarded when a statement is reset.
000701 do_execsql_test 13.8.1 {
000702 SELECT test_auxdata('constant') FROM t4;
000703 } {0 1}
000704 do_execsql_test 13.8.2 {
000705 SELECT test_auxdata('constant') FROM t4;
000706 } {0 1}
000707 db cache flush
000708 do_execsql_test 13.8.3 {
000709 SELECT test_auxdata('constant') FROM t4;
000710 } {0 1}
000711 set V "one"
000712 do_execsql_test 13.8.4 {
000713 SELECT test_auxdata($V), $V FROM t4;
000714 } {0 one 1 one}
000715 set V "two"
000716 do_execsql_test 13.8.5 {
000717 SELECT test_auxdata($V), $V FROM t4;
000718 } {0 two 1 two}
000719 db cache flush
000720 set V "three"
000721 do_execsql_test 13.8.6 {
000722 SELECT test_auxdata($V), $V FROM t4;
000723 } {0 three 1 three}
000724
000725
000726 # Make sure that a function with a very long name is rejected
000727 do_test func-14.1 {
000728 catch {
000729 db function [string repeat X 254] {return "hello"}
000730 }
000731 } {0}
000732 do_test func-14.2 {
000733 catch {
000734 db function [string repeat X 256] {return "hello"}
000735 }
000736 } {1}
000737
000738 do_test func-15.1 {
000739 catchsql {select test_error(NULL)}
000740 } {1 {}}
000741 do_test func-15.2 {
000742 catchsql {select test_error('this is the error message')}
000743 } {1 {this is the error message}}
000744 do_test func-15.3 {
000745 catchsql {select test_error('this is the error message',12)}
000746 } {1 {this is the error message}}
000747 do_test func-15.4 {
000748 db errorcode
000749 } {12}
000750
000751 # Test the quote function for BLOB and NULL values.
000752 do_test func-16.1 {
000753 execsql {
000754 CREATE TABLE tbl2(a, b);
000755 }
000756 set STMT [sqlite3_prepare $::DB "INSERT INTO tbl2 VALUES(?, ?)" -1 TAIL]
000757 sqlite3_bind_blob $::STMT 1 abc 3
000758 sqlite3_step $::STMT
000759 sqlite3_finalize $::STMT
000760 execsql {
000761 SELECT quote(a), quote(b) FROM tbl2;
000762 }
000763 } {X'616263' NULL}
000764
000765 # Correctly handle function error messages that include %. Ticket #1354
000766 #
000767 do_test func-17.1 {
000768 proc testfunc1 args {error "Error %d with %s percents %p"}
000769 db function testfunc1 ::testfunc1
000770 catchsql {
000771 SELECT testfunc1(1,2,3);
000772 }
000773 } {1 {Error %d with %s percents %p}}
000774
000775 # The SUM function should return integer results when all inputs are integer.
000776 #
000777 do_test func-18.1 {
000778 execsql {
000779 CREATE TABLE t5(x);
000780 INSERT INTO t5 VALUES(1);
000781 INSERT INTO t5 VALUES(-99);
000782 INSERT INTO t5 VALUES(10000);
000783 SELECT sum(x) FROM t5;
000784 }
000785 } {9902}
000786 ifcapable floatingpoint {
000787 do_test func-18.2 {
000788 execsql {
000789 INSERT INTO t5 VALUES(0.0);
000790 SELECT sum(x) FROM t5;
000791 }
000792 } {9902.0}
000793 }
000794
000795 # The sum of nothing is NULL. But the sum of all NULLs is NULL.
000796 #
000797 # The TOTAL of nothing is 0.0.
000798 #
000799 do_test func-18.3 {
000800 execsql {
000801 DELETE FROM t5;
000802 SELECT sum(x), total(x) FROM t5;
000803 }
000804 } {{} 0.0}
000805 do_test func-18.4 {
000806 execsql {
000807 INSERT INTO t5 VALUES(NULL);
000808 SELECT sum(x), total(x) FROM t5
000809 }
000810 } {{} 0.0}
000811 do_test func-18.5 {
000812 execsql {
000813 INSERT INTO t5 VALUES(NULL);
000814 SELECT sum(x), total(x) FROM t5
000815 }
000816 } {{} 0.0}
000817 do_test func-18.6 {
000818 execsql {
000819 INSERT INTO t5 VALUES(123);
000820 SELECT sum(x), total(x) FROM t5
000821 }
000822 } {123 123.0}
000823
000824 # Ticket #1664, #1669, #1670, #1674: An integer overflow on SUM causes
000825 # an error. The non-standard TOTAL() function continues to give a helpful
000826 # result.
000827 #
000828 do_test func-18.10 {
000829 execsql {
000830 CREATE TABLE t6(x INTEGER);
000831 INSERT INTO t6 VALUES(1);
000832 INSERT INTO t6 VALUES(1<<62);
000833 SELECT sum(x) - ((1<<62)+1) from t6;
000834 }
000835 } 0
000836 do_test func-18.11 {
000837 execsql {
000838 SELECT typeof(sum(x)) FROM t6
000839 }
000840 } integer
000841 ifcapable floatingpoint {
000842 do_test func-18.12 {
000843 catchsql {
000844 INSERT INTO t6 VALUES(1<<62);
000845 SELECT sum(x) - ((1<<62)*2.0+1) from t6;
000846 }
000847 } {1 {integer overflow}}
000848 do_test func-18.13 {
000849 execsql {
000850 SELECT total(x) - ((1<<62)*2.0+1) FROM t6
000851 }
000852 } 0.0
000853 }
000854 ifcapable !floatingpoint {
000855 do_test func-18.12 {
000856 catchsql {
000857 INSERT INTO t6 VALUES(1<<62);
000858 SELECT sum(x) - ((1<<62)*2+1) from t6;
000859 }
000860 } {1 {integer overflow}}
000861 do_test func-18.13 {
000862 execsql {
000863 SELECT total(x) - ((1<<62)*2+1) FROM t6
000864 }
000865 } 0.0
000866 }
000867 if {[working_64bit_int]} {
000868 do_test func-18.14 {
000869 execsql {
000870 SELECT sum(-9223372036854775805);
000871 }
000872 } -9223372036854775805
000873 }
000874 ifcapable compound&&subquery {
000875
000876 do_test func-18.15 {
000877 catchsql {
000878 SELECT sum(x) FROM
000879 (SELECT 9223372036854775807 AS x UNION ALL
000880 SELECT 10 AS x);
000881 }
000882 } {1 {integer overflow}}
000883 if {[working_64bit_int]} {
000884 do_test func-18.16 {
000885 catchsql {
000886 SELECT sum(x) FROM
000887 (SELECT 9223372036854775807 AS x UNION ALL
000888 SELECT -10 AS x);
000889 }
000890 } {0 9223372036854775797}
000891 do_test func-18.17 {
000892 catchsql {
000893 SELECT sum(x) FROM
000894 (SELECT -9223372036854775807 AS x UNION ALL
000895 SELECT 10 AS x);
000896 }
000897 } {0 -9223372036854775797}
000898 }
000899 do_test func-18.18 {
000900 catchsql {
000901 SELECT sum(x) FROM
000902 (SELECT -9223372036854775807 AS x UNION ALL
000903 SELECT -10 AS x);
000904 }
000905 } {1 {integer overflow}}
000906 do_test func-18.19 {
000907 catchsql {
000908 SELECT sum(x) FROM (SELECT 9 AS x UNION ALL SELECT -10 AS x);
000909 }
000910 } {0 -1}
000911 do_test func-18.20 {
000912 catchsql {
000913 SELECT sum(x) FROM (SELECT -9 AS x UNION ALL SELECT 10 AS x);
000914 }
000915 } {0 1}
000916 do_test func-18.21 {
000917 catchsql {
000918 SELECT sum(x) FROM (SELECT -10 AS x UNION ALL SELECT 9 AS x);
000919 }
000920 } {0 -1}
000921 do_test func-18.22 {
000922 catchsql {
000923 SELECT sum(x) FROM (SELECT 10 AS x UNION ALL SELECT -9 AS x);
000924 }
000925 } {0 1}
000926
000927 } ;# ifcapable compound&&subquery
000928
000929 # Integer overflow on abs()
000930 #
000931 if {[working_64bit_int]} {
000932 do_test func-18.31 {
000933 catchsql {
000934 SELECT abs(-9223372036854775807);
000935 }
000936 } {0 9223372036854775807}
000937 }
000938 do_test func-18.32 {
000939 catchsql {
000940 SELECT abs(-9223372036854775807-1);
000941 }
000942 } {1 {integer overflow}}
000943
000944 # The MATCH function exists but is only a stub and always throws an error.
000945 #
000946 do_test func-19.1 {
000947 execsql {
000948 SELECT match(a,b) FROM t1 WHERE 0;
000949 }
000950 } {}
000951 do_test func-19.2 {
000952 catchsql {
000953 SELECT 'abc' MATCH 'xyz';
000954 }
000955 } {1 {unable to use function MATCH in the requested context}}
000956 do_test func-19.3 {
000957 catchsql {
000958 SELECT 'abc' NOT MATCH 'xyz';
000959 }
000960 } {1 {unable to use function MATCH in the requested context}}
000961 do_test func-19.4 {
000962 catchsql {
000963 SELECT match(1,2,3);
000964 }
000965 } {1 {wrong number of arguments to function match()}}
000966
000967 # Soundex tests.
000968 #
000969 if {![catch {db eval {SELECT soundex('hello')}}]} {
000970 set i 0
000971 foreach {name sdx} {
000972 euler E460
000973 EULER E460
000974 Euler E460
000975 ellery E460
000976 gauss G200
000977 ghosh G200
000978 hilbert H416
000979 Heilbronn H416
000980 knuth K530
000981 kant K530
000982 Lloyd L300
000983 LADD L300
000984 Lukasiewicz L222
000985 Lissajous L222
000986 A A000
000987 12345 ?000
000988 } {
000989 incr i
000990 do_test func-20.$i {
000991 execsql {SELECT soundex($name)}
000992 } $sdx
000993 }
000994 }
000995
000996 # Tests of the REPLACE function.
000997 #
000998 do_test func-21.1 {
000999 catchsql {
001000 SELECT replace(1,2);
001001 }
001002 } {1 {wrong number of arguments to function replace()}}
001003 do_test func-21.2 {
001004 catchsql {
001005 SELECT replace(1,2,3,4);
001006 }
001007 } {1 {wrong number of arguments to function replace()}}
001008 do_test func-21.3 {
001009 execsql {
001010 SELECT typeof(replace("This is the main test string", NULL, "ALT"));
001011 }
001012 } {null}
001013 do_test func-21.4 {
001014 execsql {
001015 SELECT typeof(replace(NULL, "main", "ALT"));
001016 }
001017 } {null}
001018 do_test func-21.5 {
001019 execsql {
001020 SELECT typeof(replace("This is the main test string", "main", NULL));
001021 }
001022 } {null}
001023 do_test func-21.6 {
001024 execsql {
001025 SELECT replace("This is the main test string", "main", "ALT");
001026 }
001027 } {{This is the ALT test string}}
001028 do_test func-21.7 {
001029 execsql {
001030 SELECT replace("This is the main test string", "main", "larger-main");
001031 }
001032 } {{This is the larger-main test string}}
001033 do_test func-21.8 {
001034 execsql {
001035 SELECT replace("aaaaaaa", "a", "0123456789");
001036 }
001037 } {0123456789012345678901234567890123456789012345678901234567890123456789}
001038
001039 ifcapable tclvar {
001040 do_test func-21.9 {
001041 # Attempt to exploit a buffer-overflow that at one time existed
001042 # in the REPLACE function.
001043 set ::str "[string repeat A 29998]CC[string repeat A 35537]"
001044 set ::rep [string repeat B 65536]
001045 execsql {
001046 SELECT LENGTH(REPLACE($::str, 'C', $::rep));
001047 }
001048 } [expr 29998 + 2*65536 + 35537]
001049 }
001050
001051 # Tests for the TRIM, LTRIM and RTRIM functions.
001052 #
001053 do_test func-22.1 {
001054 catchsql {SELECT trim(1,2,3)}
001055 } {1 {wrong number of arguments to function trim()}}
001056 do_test func-22.2 {
001057 catchsql {SELECT ltrim(1,2,3)}
001058 } {1 {wrong number of arguments to function ltrim()}}
001059 do_test func-22.3 {
001060 catchsql {SELECT rtrim(1,2,3)}
001061 } {1 {wrong number of arguments to function rtrim()}}
001062 do_test func-22.4 {
001063 execsql {SELECT trim(' hi ');}
001064 } {hi}
001065 do_test func-22.5 {
001066 execsql {SELECT ltrim(' hi ');}
001067 } {{hi }}
001068 do_test func-22.6 {
001069 execsql {SELECT rtrim(' hi ');}
001070 } {{ hi}}
001071 do_test func-22.7 {
001072 execsql {SELECT trim(' hi ','xyz');}
001073 } {{ hi }}
001074 do_test func-22.8 {
001075 execsql {SELECT ltrim(' hi ','xyz');}
001076 } {{ hi }}
001077 do_test func-22.9 {
001078 execsql {SELECT rtrim(' hi ','xyz');}
001079 } {{ hi }}
001080 do_test func-22.10 {
001081 execsql {SELECT trim('xyxzy hi zzzy','xyz');}
001082 } {{ hi }}
001083 do_test func-22.11 {
001084 execsql {SELECT ltrim('xyxzy hi zzzy','xyz');}
001085 } {{ hi zzzy}}
001086 do_test func-22.12 {
001087 execsql {SELECT rtrim('xyxzy hi zzzy','xyz');}
001088 } {{xyxzy hi }}
001089 do_test func-22.13 {
001090 execsql {SELECT trim(' hi ','');}
001091 } {{ hi }}
001092 if {[db one {PRAGMA encoding}]=="UTF-8"} {
001093 do_test func-22.14 {
001094 execsql {SELECT hex(trim(x'c280e1bfbff48fbfbf6869',x'6162e1bfbfc280'))}
001095 } {F48FBFBF6869}
001096 do_test func-22.15 {
001097 execsql {SELECT hex(trim(x'6869c280e1bfbff48fbfbf61',
001098 x'6162e1bfbfc280f48fbfbf'))}
001099 } {6869}
001100 do_test func-22.16 {
001101 execsql {SELECT hex(trim(x'ceb1ceb2ceb3',x'ceb1'));}
001102 } {CEB2CEB3}
001103 }
001104 do_test func-22.20 {
001105 execsql {SELECT typeof(trim(NULL));}
001106 } {null}
001107 do_test func-22.21 {
001108 execsql {SELECT typeof(trim(NULL,'xyz'));}
001109 } {null}
001110 do_test func-22.22 {
001111 execsql {SELECT typeof(trim('hello',NULL));}
001112 } {null}
001113
001114 # This is to test the deprecated sqlite3_aggregate_count() API.
001115 #
001116 ifcapable deprecated {
001117 do_test func-23.1 {
001118 sqlite3_create_aggregate db
001119 execsql {
001120 SELECT legacy_count() FROM t6;
001121 }
001122 } {3}
001123 }
001124
001125 # The group_concat() function.
001126 #
001127 do_test func-24.1 {
001128 execsql {
001129 SELECT group_concat(t1) FROM tbl1
001130 }
001131 } {this,program,is,free,software}
001132 do_test func-24.2 {
001133 execsql {
001134 SELECT group_concat(t1,' ') FROM tbl1
001135 }
001136 } {{this program is free software}}
001137 do_test func-24.3 {
001138 execsql {
001139 SELECT group_concat(t1,' ' || rowid || ' ') FROM tbl1
001140 }
001141 } {{this 2 program 3 is 4 free 5 software}}
001142 do_test func-24.4 {
001143 execsql {
001144 SELECT group_concat(NULL,t1) FROM tbl1
001145 }
001146 } {{}}
001147 do_test func-24.5 {
001148 execsql {
001149 SELECT group_concat(t1,NULL) FROM tbl1
001150 }
001151 } {thisprogramisfreesoftware}
001152 do_test func-24.6 {
001153 execsql {
001154 SELECT 'BEGIN-'||group_concat(t1) FROM tbl1
001155 }
001156 } {BEGIN-this,program,is,free,software}
001157
001158 # Ticket #3179: Make sure aggregate functions can take many arguments.
001159 # None of the built-in aggregates do this, so use the md5sum() from the
001160 # test extensions.
001161 #
001162 unset -nocomplain midargs
001163 set midargs {}
001164 unset -nocomplain midres
001165 set midres {}
001166 unset -nocomplain result
001167 for {set i 1} {$i<[sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG -1]} {incr i} {
001168 append midargs ,'/$i'
001169 append midres /$i
001170 set result [md5 \
001171 "this${midres}program${midres}is${midres}free${midres}software${midres}"]
001172 set sql "SELECT md5sum(t1$midargs) FROM tbl1"
001173 do_test func-24.7.$i {
001174 db eval $::sql
001175 } $result
001176 }
001177
001178 # Ticket #3806. If the initial string in a group_concat is an empty
001179 # string, the separator that follows should still be present.
001180 #
001181 do_test func-24.8 {
001182 execsql {
001183 SELECT group_concat(CASE t1 WHEN 'this' THEN '' ELSE t1 END) FROM tbl1
001184 }
001185 } {,program,is,free,software}
001186 do_test func-24.9 {
001187 execsql {
001188 SELECT group_concat(CASE WHEN t1!='software' THEN '' ELSE t1 END) FROM tbl1
001189 }
001190 } {,,,,software}
001191
001192 # Ticket #3923. Initial empty strings have a separator. But initial
001193 # NULLs do not.
001194 #
001195 do_test func-24.10 {
001196 execsql {
001197 SELECT group_concat(CASE t1 WHEN 'this' THEN null ELSE t1 END) FROM tbl1
001198 }
001199 } {program,is,free,software}
001200 do_test func-24.11 {
001201 execsql {
001202 SELECT group_concat(CASE WHEN t1!='software' THEN null ELSE t1 END) FROM tbl1
001203 }
001204 } {software}
001205 do_test func-24.12 {
001206 execsql {
001207 SELECT group_concat(CASE t1 WHEN 'this' THEN ''
001208 WHEN 'program' THEN null ELSE t1 END) FROM tbl1
001209 }
001210 } {,is,free,software}
001211 # Tests to verify ticket http://www.sqlite.org/src/tktview/55746f9e65f8587c0
001212 do_test func-24.13 {
001213 execsql {
001214 SELECT typeof(group_concat(x)) FROM (SELECT '' AS x);
001215 }
001216 } {text}
001217 do_test func-24.14 {
001218 execsql {
001219 SELECT typeof(group_concat(x,''))
001220 FROM (SELECT '' AS x UNION ALL SELECT '');
001221 }
001222 } {text}
001223
001224
001225 # Use the test_isolation function to make sure that type conversions
001226 # on function arguments do not effect subsequent arguments.
001227 #
001228 do_test func-25.1 {
001229 execsql {SELECT test_isolation(t1,t1) FROM tbl1}
001230 } {this program is free software}
001231
001232 # Try to misuse the sqlite3_create_function() interface. Verify that
001233 # errors are returned.
001234 #
001235 do_test func-26.1 {
001236 abuse_create_function db
001237 } {}
001238
001239 # The previous test (func-26.1) registered a function with a very long
001240 # function name that takes many arguments and always returns NULL. Verify
001241 # that this function works correctly.
001242 #
001243 do_test func-26.2 {
001244 set a {}
001245 for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG} {incr i} {
001246 lappend a $i
001247 }
001248 db eval "
001249 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 ,]);
001250 "
001251 } {{}}
001252 do_test func-26.3 {
001253 set a {}
001254 for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG+1} {incr i} {
001255 lappend a $i
001256 }
001257 catchsql "
001258 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 ,]);
001259 "
001260 } {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}}
001261 do_test func-26.4 {
001262 set a {}
001263 for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG-1} {incr i} {
001264 lappend a $i
001265 }
001266 catchsql "
001267 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 ,]);
001268 "
001269 } {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()}}
001270 do_test func-26.5 {
001271 catchsql "
001272 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);
001273 "
001274 } {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}}
001275 do_test func-26.6 {
001276 catchsql "
001277 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);
001278 "
001279 } {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}}
001280
001281 do_test func-27.1 {
001282 catchsql {SELECT coalesce()}
001283 } {1 {wrong number of arguments to function coalesce()}}
001284 do_test func-27.2 {
001285 catchsql {SELECT coalesce(1)}
001286 } {1 {wrong number of arguments to function coalesce()}}
001287 do_test func-27.3 {
001288 catchsql {SELECT coalesce(1,2)}
001289 } {0 1}
001290
001291 # Ticket 2d401a94287b5
001292 # Unknown function in a DEFAULT expression causes a segfault.
001293 #
001294 do_test func-28.1 {
001295 db eval {
001296 CREATE TABLE t28(x, y DEFAULT(nosuchfunc(1)));
001297 }
001298 catchsql {
001299 INSERT INTO t28(x) VALUES(1);
001300 }
001301 } {1 {unknown function: nosuchfunc()}}
001302
001303 # Verify that the length() and typeof() functions do not actually load
001304 # the content of their argument.
001305 #
001306 do_test func-29.1 {
001307 db eval {
001308 CREATE TABLE t29(id INTEGER PRIMARY KEY, x, y);
001309 INSERT INTO t29 VALUES(1, 2, 3), (2, NULL, 4), (3, 4.5, 5);
001310 INSERT INTO t29 VALUES(4, randomblob(1000000), 6);
001311 INSERT INTO t29 VALUES(5, "hello", 7);
001312 }
001313 db close
001314 sqlite3 db test.db
001315 sqlite3_db_status db CACHE_MISS 1
001316 db eval {SELECT typeof(x), length(x), typeof(y) FROM t29 ORDER BY id}
001317 } {integer 1 integer null {} integer real 3 integer blob 1000000 integer text 5 integer}
001318 do_test func-29.2 {
001319 set x [lindex [sqlite3_db_status db CACHE_MISS 1] 1]
001320 if {$x<5} {set x 1}
001321 set x
001322 } {1}
001323 do_test func-29.3 {
001324 db close
001325 sqlite3 db test.db
001326 sqlite3_db_status db CACHE_MISS 1
001327 db eval {SELECT typeof(+x) FROM t29 ORDER BY id}
001328 } {integer null real blob text}
001329 if {[permutation] != "mmap"} {
001330 ifcapable !direct_read {
001331 do_test func-29.4 {
001332 set x [lindex [sqlite3_db_status db CACHE_MISS 1] 1]
001333 if {$x>100} {set x many}
001334 set x
001335 } {many}
001336 }
001337 }
001338 do_test func-29.5 {
001339 db close
001340 sqlite3 db test.db
001341 sqlite3_db_status db CACHE_MISS 1
001342 db eval {SELECT sum(length(x)) FROM t29}
001343 } {1000009}
001344 do_test func-29.6 {
001345 set x [lindex [sqlite3_db_status db CACHE_MISS 1] 1]
001346 if {$x<5} {set x 1}
001347 set x
001348 } {1}
001349
001350 # The OP_Column opcode has an optimization that avoids loading content
001351 # for fields with content-length=0 when the content offset is on an overflow
001352 # page. Make sure the optimization works.
001353 #
001354 do_execsql_test func-29.10 {
001355 CREATE TABLE t29b(a,b,c,d,e,f,g,h,i);
001356 INSERT INTO t29b
001357 VALUES(1, hex(randomblob(2000)), null, 0, 1, '', zeroblob(0),'x',x'01');
001358 SELECT typeof(c), typeof(d), typeof(e), typeof(f),
001359 typeof(g), typeof(h), typeof(i) FROM t29b;
001360 } {null integer integer text blob text blob}
001361 do_execsql_test func-29.11 {
001362 SELECT length(f), length(g), length(h), length(i) FROM t29b;
001363 } {0 0 1 1}
001364 do_execsql_test func-29.12 {
001365 SELECT quote(f), quote(g), quote(h), quote(i) FROM t29b;
001366 } {'' X'' 'x' X'01'}
001367
001368 # EVIDENCE-OF: R-29701-50711 The unicode(X) function returns the numeric
001369 # unicode code point corresponding to the first character of the string
001370 # X.
001371 #
001372 # EVIDENCE-OF: R-55469-62130 The char(X1,X2,...,XN) function returns a
001373 # string composed of characters having the unicode code point values of
001374 # integers X1 through XN, respectively.
001375 #
001376 do_execsql_test func-30.1 {SELECT unicode('$');} 36
001377 do_execsql_test func-30.2 [subst {SELECT unicode('\u00A2');}] 162
001378 do_execsql_test func-30.3 [subst {SELECT unicode('\u20AC');}] 8364
001379 do_execsql_test func-30.4 {SELECT char(36,162,8364);} [subst {$\u00A2\u20AC}]
001380
001381 for {set i 1} {$i<0xd800} {incr i 13} {
001382 do_execsql_test func-30.5.$i {SELECT unicode(char($i))} $i
001383 }
001384 for {set i 57344} {$i<=0xfffd} {incr i 17} {
001385 if {$i==0xfeff} continue
001386 do_execsql_test func-30.5.$i {SELECT unicode(char($i))} $i
001387 }
001388 for {set i 65536} {$i<=0x10ffff} {incr i 139} {
001389 do_execsql_test func-30.5.$i {SELECT unicode(char($i))} $i
001390 }
001391
001392 # Test char().
001393 #
001394 do_execsql_test func-31.1 {
001395 SELECT char(), length(char()), typeof(char())
001396 } {{} 0 text}
001397
001398 # sqlite3_value_frombind()
001399 #
001400 do_execsql_test func-32.100 {
001401 SELECT test_frombind(1,2,3,4);
001402 } {0}
001403 do_execsql_test func-32.110 {
001404 SELECT test_frombind(1,2,?,4);
001405 } {4}
001406 do_execsql_test func-32.120 {
001407 SELECT test_frombind(1,(?),4,?+7);
001408 } {2}
001409 do_execsql_test func-32.130 {
001410 DROP TABLE IF EXISTS t1;
001411 CREATE TABLE t1(a,b,c,e,f);
001412 INSERT INTO t1 VALUES(1,2.5,'xyz',x'e0c1b2a3',null);
001413 SELECT test_frombind(a,b,c,e,f,$xyz) FROM t1;
001414 } {32}
001415 do_execsql_test func-32.140 {
001416 SELECT test_frombind(a,b,c,e,f,$xyz+f) FROM t1;
001417 } {0}
001418 do_execsql_test func-32.150 {
001419 SELECT test_frombind(x.a,y.b,x.c,:123,y.e,x.f,$xyz+y.f) FROM t1 x, t1 y;
001420 } {8}
001421
001422 # 2019-08-15
001423 # Direct-only functions.
001424 #
001425 proc testdirectonly {x} {return [expr {$x*2}]}
001426 do_test func-33.1 {
001427 db func testdirectonly -directonly testdirectonly
001428 db eval {SELECT testdirectonly(15)}
001429 } {30}
001430 do_catchsql_test func-33.2 {
001431 CREATE VIEW v33(y) AS SELECT testdirectonly(15);
001432 SELECT * FROM v33;
001433 } {1 {testdirectonly() prohibited in triggers and views}}
001434 do_execsql_test func-33.3 {
001435 SELECT * FROM (SELECT testdirectonly(15)) AS v33;
001436 } {30}
001437 do_execsql_test func-33.4 {
001438 WITH c(x) AS (SELECT testdirectonly(15))
001439 SELECT * FROM c;
001440 } {30}
001441 do_catchsql_test func-33.5 {
001442 WITH c(x) AS (SELECT * FROM v33)
001443 SELECT * FROM c;
001444 } {1 {testdirectonly() prohibited in triggers and views}}
001445 do_execsql_test func-33.10 {
001446 CREATE TABLE t33a(a,b);
001447 CREATE TABLE t33b(x,y);
001448 CREATE TRIGGER r1 AFTER INSERT ON t33a BEGIN
001449 INSERT INTO t33b(x,y) VALUES(testdirectonly(new.a),new.b);
001450 END;
001451 } {}
001452 do_catchsql_test func-33.11 {
001453 INSERT INTO t33a VALUES(1,2);
001454 } {1 {testdirectonly() prohibited in triggers and views}}
001455 do_execsql_test func-33.20 {
001456 ALTER TABLE t33a RENAME COLUMN a TO aaa;
001457 SELECT sql FROM sqlite_master WHERE name='r1';
001458 } {{CREATE TRIGGER r1 AFTER INSERT ON t33a BEGIN
001459 INSERT INTO t33b(x,y) VALUES(testdirectonly(new.aaa),new.b);
001460 END}}
001461
001462
001463 finish_test