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 }
000319
000320 # Test the upper() and lower() functions
000321 #
000322 do_test func-5.1 {
000323 execsql {SELECT upper(t1) FROM tbl1}
000324 } {THIS PROGRAM IS FREE SOFTWARE}
000325 do_test func-5.2 {
000326 execsql {SELECT lower(upper(t1)) FROM tbl1}
000327 } {this program is free software}
000328 do_test func-5.3 {
000329 execsql {SELECT upper(a), lower(a) FROM t2}
000330 } {1 1 {} {} 345 345 {} {} 67890 67890}
000331 ifcapable !icu {
000332 do_test func-5.4 {
000333 catchsql {SELECT upper(a,5) FROM t2}
000334 } {1 {wrong number of arguments to function upper()}}
000335 }
000336 do_test func-5.5 {
000337 catchsql {SELECT upper(*) FROM t2}
000338 } {1 {wrong number of arguments to function upper()}}
000339
000340 # Test the coalesce() and nullif() functions
000341 #
000342 do_test func-6.1 {
000343 execsql {SELECT coalesce(a,'xyz') FROM t2}
000344 } {1 xyz 345 xyz 67890}
000345 do_test func-6.2 {
000346 execsql {SELECT coalesce(upper(a),'nil') FROM t2}
000347 } {1 nil 345 nil 67890}
000348 do_test func-6.3 {
000349 execsql {SELECT coalesce(nullif(1,1),'nil')}
000350 } {nil}
000351 do_test func-6.4 {
000352 execsql {SELECT coalesce(nullif(1,2),'nil')}
000353 } {1}
000354 do_test func-6.5 {
000355 execsql {SELECT coalesce(nullif(1,NULL),'nil')}
000356 } {1}
000357
000358
000359 # Test the last_insert_rowid() function
000360 #
000361 do_test func-7.1 {
000362 execsql {SELECT last_insert_rowid()}
000363 } [db last_insert_rowid]
000364
000365 # Tests for aggregate functions and how they handle NULLs.
000366 #
000367 ifcapable floatingpoint {
000368 do_test func-8.1 {
000369 ifcapable explain {
000370 execsql {EXPLAIN SELECT sum(a) FROM t2;}
000371 }
000372 execsql {
000373 SELECT sum(a), count(a), round(avg(a),2), min(a), max(a), count(*) FROM t2;
000374 }
000375 } {68236 3 22745.33 1 67890 5}
000376 }
000377 ifcapable !floatingpoint {
000378 do_test func-8.1 {
000379 ifcapable explain {
000380 execsql {EXPLAIN SELECT sum(a) FROM t2;}
000381 }
000382 execsql {
000383 SELECT sum(a), count(a), avg(a), min(a), max(a), count(*) FROM t2;
000384 }
000385 } {68236 3 22745.0 1 67890 5}
000386 }
000387 do_test func-8.2 {
000388 execsql {
000389 SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t2;
000390 }
000391 } {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
000392
000393 ifcapable tempdb {
000394 do_test func-8.3 {
000395 execsql {
000396 CREATE TEMP TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;
000397 SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
000398 }
000399 } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
000400 } else {
000401 do_test func-8.3 {
000402 execsql {
000403 CREATE TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;
000404 SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
000405 }
000406 } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
000407 }
000408 do_test func-8.4 {
000409 execsql {
000410 SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
000411 }
000412 } {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
000413 ifcapable compound {
000414 do_test func-8.5 {
000415 execsql {
000416 SELECT sum(x) FROM (SELECT '9223372036' || '854775807' AS x
000417 UNION ALL SELECT -9223372036854775807)
000418 }
000419 } {0}
000420 do_test func-8.6 {
000421 execsql {
000422 SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775807' AS x
000423 UNION ALL SELECT -9223372036854775807)
000424 }
000425 } {integer}
000426 do_test func-8.7 {
000427 execsql {
000428 SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775808' AS x
000429 UNION ALL SELECT -9223372036854775807)
000430 }
000431 } {real}
000432 ifcapable floatingpoint {
000433 do_test func-8.8 {
000434 execsql {
000435 SELECT sum(x)>0.0 FROM (SELECT '9223372036' || '854775808' AS x
000436 UNION ALL SELECT -9223372036850000000)
000437 }
000438 } {1}
000439 }
000440 ifcapable !floatingpoint {
000441 do_test func-8.8 {
000442 execsql {
000443 SELECT sum(x)>0 FROM (SELECT '9223372036' || '854775808' AS x
000444 UNION ALL SELECT -9223372036850000000)
000445 }
000446 } {1}
000447 }
000448 }
000449
000450 # How do you test the random() function in a meaningful, deterministic way?
000451 #
000452 do_test func-9.1 {
000453 execsql {
000454 SELECT random() is not null;
000455 }
000456 } {1}
000457 do_test func-9.2 {
000458 execsql {
000459 SELECT typeof(random());
000460 }
000461 } {integer}
000462 do_test func-9.3 {
000463 execsql {
000464 SELECT randomblob(32) is not null;
000465 }
000466 } {1}
000467 do_test func-9.4 {
000468 execsql {
000469 SELECT typeof(randomblob(32));
000470 }
000471 } {blob}
000472 do_test func-9.5 {
000473 execsql {
000474 SELECT length(randomblob(32)), length(randomblob(-5)),
000475 length(randomblob(2000))
000476 }
000477 } {32 1 2000}
000478
000479 # The "hex()" function was added in order to be able to render blobs
000480 # generated by randomblob(). So this seems like a good place to test
000481 # hex().
000482 #
000483 ifcapable bloblit {
000484 do_test func-9.10 {
000485 execsql {SELECT hex(x'00112233445566778899aAbBcCdDeEfF')}
000486 } {00112233445566778899AABBCCDDEEFF}
000487 }
000488 set encoding [db one {PRAGMA encoding}]
000489 if {$encoding=="UTF-16le"} {
000490 do_test func-9.11-utf16le {
000491 execsql {SELECT hex(replace('abcdefg','ef','12'))}
000492 } {6100620063006400310032006700}
000493 do_test func-9.12-utf16le {
000494 execsql {SELECT hex(replace('abcdefg','','12'))}
000495 } {6100620063006400650066006700}
000496 do_test func-9.13-utf16le {
000497 execsql {SELECT hex(replace('aabcdefg','a','aaa'))}
000498 } {610061006100610061006100620063006400650066006700}
000499 } elseif {$encoding=="UTF-8"} {
000500 do_test func-9.11-utf8 {
000501 execsql {SELECT hex(replace('abcdefg','ef','12'))}
000502 } {61626364313267}
000503 do_test func-9.12-utf8 {
000504 execsql {SELECT hex(replace('abcdefg','','12'))}
000505 } {61626364656667}
000506 do_test func-9.13-utf8 {
000507 execsql {SELECT hex(replace('aabcdefg','a','aaa'))}
000508 } {616161616161626364656667}
000509 }
000510
000511 # Use the "sqlite_register_test_function" TCL command which is part of
000512 # the text fixture in order to verify correct operation of some of
000513 # the user-defined SQL function APIs that are not used by the built-in
000514 # functions.
000515 #
000516 set ::DB [sqlite3_connection_pointer db]
000517 sqlite_register_test_function $::DB testfunc
000518 do_test func-10.1 {
000519 catchsql {
000520 SELECT testfunc(NULL,NULL);
000521 }
000522 } {1 {first argument should be one of: int int64 string double null value}}
000523 do_test func-10.2 {
000524 execsql {
000525 SELECT testfunc(
000526 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
000527 'int', 1234
000528 );
000529 }
000530 } {1234}
000531 do_test func-10.3 {
000532 execsql {
000533 SELECT testfunc(
000534 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
000535 'string', NULL
000536 );
000537 }
000538 } {{}}
000539
000540 ifcapable floatingpoint {
000541 do_test func-10.4 {
000542 execsql {
000543 SELECT testfunc(
000544 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
000545 'double', 1.234
000546 );
000547 }
000548 } {1.234}
000549 do_test func-10.5 {
000550 execsql {
000551 SELECT testfunc(
000552 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
000553 'int', 1234,
000554 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
000555 'string', NULL,
000556 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
000557 'double', 1.234,
000558 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
000559 'int', 1234,
000560 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
000561 'string', NULL,
000562 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
000563 'double', 1.234
000564 );
000565 }
000566 } {1.234}
000567 }
000568
000569 # Test the built-in sqlite_version(*) SQL function.
000570 #
000571 do_test func-11.1 {
000572 execsql {
000573 SELECT sqlite_version(*);
000574 }
000575 } [sqlite3 -version]
000576
000577 # Test that destructors passed to sqlite3 by calls to sqlite3_result_text()
000578 # etc. are called. These tests use two special user-defined functions
000579 # (implemented in func.c) only available in test builds.
000580 #
000581 # Function test_destructor() takes one argument and returns a copy of the
000582 # text form of that argument. A destructor is associated with the return
000583 # value. Function test_destructor_count() returns the number of outstanding
000584 # destructor calls for values returned by test_destructor().
000585 #
000586 if {[db eval {PRAGMA encoding}]=="UTF-8"} {
000587 do_test func-12.1-utf8 {
000588 execsql {
000589 SELECT test_destructor('hello world'), test_destructor_count();
000590 }
000591 } {{hello world} 1}
000592 } else {
000593 ifcapable {utf16} {
000594 do_test func-12.1-utf16 {
000595 execsql {
000596 SELECT test_destructor16('hello world'), test_destructor_count();
000597 }
000598 } {{hello world} 1}
000599 }
000600 }
000601 do_test func-12.2 {
000602 execsql {
000603 SELECT test_destructor_count();
000604 }
000605 } {0}
000606 do_test func-12.3 {
000607 execsql {
000608 SELECT test_destructor('hello')||' world'
000609 }
000610 } {{hello world}}
000611 do_test func-12.4 {
000612 execsql {
000613 SELECT test_destructor_count();
000614 }
000615 } {0}
000616 do_test func-12.5 {
000617 execsql {
000618 CREATE TABLE t4(x);
000619 INSERT INTO t4 VALUES(test_destructor('hello'));
000620 INSERT INTO t4 VALUES(test_destructor('world'));
000621 SELECT min(test_destructor(x)), max(test_destructor(x)) FROM t4;
000622 }
000623 } {hello world}
000624 do_test func-12.6 {
000625 execsql {
000626 SELECT test_destructor_count();
000627 }
000628 } {0}
000629 do_test func-12.7 {
000630 execsql {
000631 DROP TABLE t4;
000632 }
000633 } {}
000634
000635
000636 # Test that the auxdata API for scalar functions works. This test uses
000637 # a special user-defined function only available in test builds,
000638 # test_auxdata(). Function test_auxdata() takes any number of arguments.
000639 do_test func-13.1 {
000640 execsql {
000641 SELECT test_auxdata('hello world');
000642 }
000643 } {0}
000644
000645 do_test func-13.2 {
000646 execsql {
000647 CREATE TABLE t4(a, b);
000648 INSERT INTO t4 VALUES('abc', 'def');
000649 INSERT INTO t4 VALUES('ghi', 'jkl');
000650 }
000651 } {}
000652 do_test func-13.3 {
000653 execsql {
000654 SELECT test_auxdata('hello world') FROM t4;
000655 }
000656 } {0 1}
000657 do_test func-13.4 {
000658 execsql {
000659 SELECT test_auxdata('hello world', 123) FROM t4;
000660 }
000661 } {{0 0} {1 1}}
000662 do_test func-13.5 {
000663 execsql {
000664 SELECT test_auxdata('hello world', a) FROM t4;
000665 }
000666 } {{0 0} {1 0}}
000667 do_test func-13.6 {
000668 execsql {
000669 SELECT test_auxdata('hello'||'world', a) FROM t4;
000670 }
000671 } {{0 0} {1 0}}
000672
000673 # Test that auxilary data is preserved between calls for SQL variables.
000674 do_test func-13.7 {
000675 set DB [sqlite3_connection_pointer db]
000676 set sql "SELECT test_auxdata( ? , a ) FROM t4;"
000677 set STMT [sqlite3_prepare $DB $sql -1 TAIL]
000678 sqlite3_bind_text $STMT 1 hello\000 -1
000679 set res [list]
000680 while { "SQLITE_ROW"==[sqlite3_step $STMT] } {
000681 lappend res [sqlite3_column_text $STMT 0]
000682 }
000683 lappend res [sqlite3_finalize $STMT]
000684 } {{0 0} {1 0} SQLITE_OK}
000685
000686 # Test that auxiliary data is discarded when a statement is reset.
000687 do_execsql_test 13.8.1 {
000688 SELECT test_auxdata('constant') FROM t4;
000689 } {0 1}
000690 do_execsql_test 13.8.2 {
000691 SELECT test_auxdata('constant') FROM t4;
000692 } {0 1}
000693 db cache flush
000694 do_execsql_test 13.8.3 {
000695 SELECT test_auxdata('constant') FROM t4;
000696 } {0 1}
000697 set V "one"
000698 do_execsql_test 13.8.4 {
000699 SELECT test_auxdata($V), $V FROM t4;
000700 } {0 one 1 one}
000701 set V "two"
000702 do_execsql_test 13.8.5 {
000703 SELECT test_auxdata($V), $V FROM t4;
000704 } {0 two 1 two}
000705 db cache flush
000706 set V "three"
000707 do_execsql_test 13.8.6 {
000708 SELECT test_auxdata($V), $V FROM t4;
000709 } {0 three 1 three}
000710
000711
000712 # Make sure that a function with a very long name is rejected
000713 do_test func-14.1 {
000714 catch {
000715 db function [string repeat X 254] {return "hello"}
000716 }
000717 } {0}
000718 do_test func-14.2 {
000719 catch {
000720 db function [string repeat X 256] {return "hello"}
000721 }
000722 } {1}
000723
000724 do_test func-15.1 {
000725 catchsql {select test_error(NULL)}
000726 } {1 {}}
000727 do_test func-15.2 {
000728 catchsql {select test_error('this is the error message')}
000729 } {1 {this is the error message}}
000730 do_test func-15.3 {
000731 catchsql {select test_error('this is the error message',12)}
000732 } {1 {this is the error message}}
000733 do_test func-15.4 {
000734 db errorcode
000735 } {12}
000736
000737 # Test the quote function for BLOB and NULL values.
000738 do_test func-16.1 {
000739 execsql {
000740 CREATE TABLE tbl2(a, b);
000741 }
000742 set STMT [sqlite3_prepare $::DB "INSERT INTO tbl2 VALUES(?, ?)" -1 TAIL]
000743 sqlite3_bind_blob $::STMT 1 abc 3
000744 sqlite3_step $::STMT
000745 sqlite3_finalize $::STMT
000746 execsql {
000747 SELECT quote(a), quote(b) FROM tbl2;
000748 }
000749 } {X'616263' NULL}
000750
000751 # Correctly handle function error messages that include %. Ticket #1354
000752 #
000753 do_test func-17.1 {
000754 proc testfunc1 args {error "Error %d with %s percents %p"}
000755 db function testfunc1 ::testfunc1
000756 catchsql {
000757 SELECT testfunc1(1,2,3);
000758 }
000759 } {1 {Error %d with %s percents %p}}
000760
000761 # The SUM function should return integer results when all inputs are integer.
000762 #
000763 do_test func-18.1 {
000764 execsql {
000765 CREATE TABLE t5(x);
000766 INSERT INTO t5 VALUES(1);
000767 INSERT INTO t5 VALUES(-99);
000768 INSERT INTO t5 VALUES(10000);
000769 SELECT sum(x) FROM t5;
000770 }
000771 } {9902}
000772 ifcapable floatingpoint {
000773 do_test func-18.2 {
000774 execsql {
000775 INSERT INTO t5 VALUES(0.0);
000776 SELECT sum(x) FROM t5;
000777 }
000778 } {9902.0}
000779 }
000780
000781 # The sum of nothing is NULL. But the sum of all NULLs is NULL.
000782 #
000783 # The TOTAL of nothing is 0.0.
000784 #
000785 do_test func-18.3 {
000786 execsql {
000787 DELETE FROM t5;
000788 SELECT sum(x), total(x) FROM t5;
000789 }
000790 } {{} 0.0}
000791 do_test func-18.4 {
000792 execsql {
000793 INSERT INTO t5 VALUES(NULL);
000794 SELECT sum(x), total(x) FROM t5
000795 }
000796 } {{} 0.0}
000797 do_test func-18.5 {
000798 execsql {
000799 INSERT INTO t5 VALUES(NULL);
000800 SELECT sum(x), total(x) FROM t5
000801 }
000802 } {{} 0.0}
000803 do_test func-18.6 {
000804 execsql {
000805 INSERT INTO t5 VALUES(123);
000806 SELECT sum(x), total(x) FROM t5
000807 }
000808 } {123 123.0}
000809
000810 # Ticket #1664, #1669, #1670, #1674: An integer overflow on SUM causes
000811 # an error. The non-standard TOTAL() function continues to give a helpful
000812 # result.
000813 #
000814 do_test func-18.10 {
000815 execsql {
000816 CREATE TABLE t6(x INTEGER);
000817 INSERT INTO t6 VALUES(1);
000818 INSERT INTO t6 VALUES(1<<62);
000819 SELECT sum(x) - ((1<<62)+1) from t6;
000820 }
000821 } 0
000822 do_test func-18.11 {
000823 execsql {
000824 SELECT typeof(sum(x)) FROM t6
000825 }
000826 } integer
000827 ifcapable floatingpoint {
000828 do_test func-18.12 {
000829 catchsql {
000830 INSERT INTO t6 VALUES(1<<62);
000831 SELECT sum(x) - ((1<<62)*2.0+1) from t6;
000832 }
000833 } {1 {integer overflow}}
000834 do_test func-18.13 {
000835 execsql {
000836 SELECT total(x) - ((1<<62)*2.0+1) FROM t6
000837 }
000838 } 0.0
000839 }
000840 ifcapable !floatingpoint {
000841 do_test func-18.12 {
000842 catchsql {
000843 INSERT INTO t6 VALUES(1<<62);
000844 SELECT sum(x) - ((1<<62)*2+1) from t6;
000845 }
000846 } {1 {integer overflow}}
000847 do_test func-18.13 {
000848 execsql {
000849 SELECT total(x) - ((1<<62)*2+1) FROM t6
000850 }
000851 } 0.0
000852 }
000853 if {[working_64bit_int]} {
000854 do_test func-18.14 {
000855 execsql {
000856 SELECT sum(-9223372036854775805);
000857 }
000858 } -9223372036854775805
000859 }
000860 ifcapable compound&&subquery {
000861
000862 do_test func-18.15 {
000863 catchsql {
000864 SELECT sum(x) FROM
000865 (SELECT 9223372036854775807 AS x UNION ALL
000866 SELECT 10 AS x);
000867 }
000868 } {1 {integer overflow}}
000869 if {[working_64bit_int]} {
000870 do_test func-18.16 {
000871 catchsql {
000872 SELECT sum(x) FROM
000873 (SELECT 9223372036854775807 AS x UNION ALL
000874 SELECT -10 AS x);
000875 }
000876 } {0 9223372036854775797}
000877 do_test func-18.17 {
000878 catchsql {
000879 SELECT sum(x) FROM
000880 (SELECT -9223372036854775807 AS x UNION ALL
000881 SELECT 10 AS x);
000882 }
000883 } {0 -9223372036854775797}
000884 }
000885 do_test func-18.18 {
000886 catchsql {
000887 SELECT sum(x) FROM
000888 (SELECT -9223372036854775807 AS x UNION ALL
000889 SELECT -10 AS x);
000890 }
000891 } {1 {integer overflow}}
000892 do_test func-18.19 {
000893 catchsql {
000894 SELECT sum(x) FROM (SELECT 9 AS x UNION ALL SELECT -10 AS x);
000895 }
000896 } {0 -1}
000897 do_test func-18.20 {
000898 catchsql {
000899 SELECT sum(x) FROM (SELECT -9 AS x UNION ALL SELECT 10 AS x);
000900 }
000901 } {0 1}
000902 do_test func-18.21 {
000903 catchsql {
000904 SELECT sum(x) FROM (SELECT -10 AS x UNION ALL SELECT 9 AS x);
000905 }
000906 } {0 -1}
000907 do_test func-18.22 {
000908 catchsql {
000909 SELECT sum(x) FROM (SELECT 10 AS x UNION ALL SELECT -9 AS x);
000910 }
000911 } {0 1}
000912
000913 } ;# ifcapable compound&&subquery
000914
000915 # Integer overflow on abs()
000916 #
000917 if {[working_64bit_int]} {
000918 do_test func-18.31 {
000919 catchsql {
000920 SELECT abs(-9223372036854775807);
000921 }
000922 } {0 9223372036854775807}
000923 }
000924 do_test func-18.32 {
000925 catchsql {
000926 SELECT abs(-9223372036854775807-1);
000927 }
000928 } {1 {integer overflow}}
000929
000930 # The MATCH function exists but is only a stub and always throws an error.
000931 #
000932 do_test func-19.1 {
000933 execsql {
000934 SELECT match(a,b) FROM t1 WHERE 0;
000935 }
000936 } {}
000937 do_test func-19.2 {
000938 catchsql {
000939 SELECT 'abc' MATCH 'xyz';
000940 }
000941 } {1 {unable to use function MATCH in the requested context}}
000942 do_test func-19.3 {
000943 catchsql {
000944 SELECT 'abc' NOT MATCH 'xyz';
000945 }
000946 } {1 {unable to use function MATCH in the requested context}}
000947 do_test func-19.4 {
000948 catchsql {
000949 SELECT match(1,2,3);
000950 }
000951 } {1 {wrong number of arguments to function match()}}
000952
000953 # Soundex tests.
000954 #
000955 if {![catch {db eval {SELECT soundex('hello')}}]} {
000956 set i 0
000957 foreach {name sdx} {
000958 euler E460
000959 EULER E460
000960 Euler E460
000961 ellery E460
000962 gauss G200
000963 ghosh G200
000964 hilbert H416
000965 Heilbronn H416
000966 knuth K530
000967 kant K530
000968 Lloyd L300
000969 LADD L300
000970 Lukasiewicz L222
000971 Lissajous L222
000972 A A000
000973 12345 ?000
000974 } {
000975 incr i
000976 do_test func-20.$i {
000977 execsql {SELECT soundex($name)}
000978 } $sdx
000979 }
000980 }
000981
000982 # Tests of the REPLACE function.
000983 #
000984 do_test func-21.1 {
000985 catchsql {
000986 SELECT replace(1,2);
000987 }
000988 } {1 {wrong number of arguments to function replace()}}
000989 do_test func-21.2 {
000990 catchsql {
000991 SELECT replace(1,2,3,4);
000992 }
000993 } {1 {wrong number of arguments to function replace()}}
000994 do_test func-21.3 {
000995 execsql {
000996 SELECT typeof(replace("This is the main test string", NULL, "ALT"));
000997 }
000998 } {null}
000999 do_test func-21.4 {
001000 execsql {
001001 SELECT typeof(replace(NULL, "main", "ALT"));
001002 }
001003 } {null}
001004 do_test func-21.5 {
001005 execsql {
001006 SELECT typeof(replace("This is the main test string", "main", NULL));
001007 }
001008 } {null}
001009 do_test func-21.6 {
001010 execsql {
001011 SELECT replace("This is the main test string", "main", "ALT");
001012 }
001013 } {{This is the ALT test string}}
001014 do_test func-21.7 {
001015 execsql {
001016 SELECT replace("This is the main test string", "main", "larger-main");
001017 }
001018 } {{This is the larger-main test string}}
001019 do_test func-21.8 {
001020 execsql {
001021 SELECT replace("aaaaaaa", "a", "0123456789");
001022 }
001023 } {0123456789012345678901234567890123456789012345678901234567890123456789}
001024
001025 ifcapable tclvar {
001026 do_test func-21.9 {
001027 # Attempt to exploit a buffer-overflow that at one time existed
001028 # in the REPLACE function.
001029 set ::str "[string repeat A 29998]CC[string repeat A 35537]"
001030 set ::rep [string repeat B 65536]
001031 execsql {
001032 SELECT LENGTH(REPLACE($::str, 'C', $::rep));
001033 }
001034 } [expr 29998 + 2*65536 + 35537]
001035 }
001036
001037 # Tests for the TRIM, LTRIM and RTRIM functions.
001038 #
001039 do_test func-22.1 {
001040 catchsql {SELECT trim(1,2,3)}
001041 } {1 {wrong number of arguments to function trim()}}
001042 do_test func-22.2 {
001043 catchsql {SELECT ltrim(1,2,3)}
001044 } {1 {wrong number of arguments to function ltrim()}}
001045 do_test func-22.3 {
001046 catchsql {SELECT rtrim(1,2,3)}
001047 } {1 {wrong number of arguments to function rtrim()}}
001048 do_test func-22.4 {
001049 execsql {SELECT trim(' hi ');}
001050 } {hi}
001051 do_test func-22.5 {
001052 execsql {SELECT ltrim(' hi ');}
001053 } {{hi }}
001054 do_test func-22.6 {
001055 execsql {SELECT rtrim(' hi ');}
001056 } {{ hi}}
001057 do_test func-22.7 {
001058 execsql {SELECT trim(' hi ','xyz');}
001059 } {{ hi }}
001060 do_test func-22.8 {
001061 execsql {SELECT ltrim(' hi ','xyz');}
001062 } {{ hi }}
001063 do_test func-22.9 {
001064 execsql {SELECT rtrim(' hi ','xyz');}
001065 } {{ hi }}
001066 do_test func-22.10 {
001067 execsql {SELECT trim('xyxzy hi zzzy','xyz');}
001068 } {{ hi }}
001069 do_test func-22.11 {
001070 execsql {SELECT ltrim('xyxzy hi zzzy','xyz');}
001071 } {{ hi zzzy}}
001072 do_test func-22.12 {
001073 execsql {SELECT rtrim('xyxzy hi zzzy','xyz');}
001074 } {{xyxzy hi }}
001075 do_test func-22.13 {
001076 execsql {SELECT trim(' hi ','');}
001077 } {{ hi }}
001078 if {[db one {PRAGMA encoding}]=="UTF-8"} {
001079 do_test func-22.14 {
001080 execsql {SELECT hex(trim(x'c280e1bfbff48fbfbf6869',x'6162e1bfbfc280'))}
001081 } {F48FBFBF6869}
001082 do_test func-22.15 {
001083 execsql {SELECT hex(trim(x'6869c280e1bfbff48fbfbf61',
001084 x'6162e1bfbfc280f48fbfbf'))}
001085 } {6869}
001086 do_test func-22.16 {
001087 execsql {SELECT hex(trim(x'ceb1ceb2ceb3',x'ceb1'));}
001088 } {CEB2CEB3}
001089 }
001090 do_test func-22.20 {
001091 execsql {SELECT typeof(trim(NULL));}
001092 } {null}
001093 do_test func-22.21 {
001094 execsql {SELECT typeof(trim(NULL,'xyz'));}
001095 } {null}
001096 do_test func-22.22 {
001097 execsql {SELECT typeof(trim('hello',NULL));}
001098 } {null}
001099
001100 # This is to test the deprecated sqlite3_aggregate_count() API.
001101 #
001102 ifcapable deprecated {
001103 do_test func-23.1 {
001104 sqlite3_create_aggregate db
001105 execsql {
001106 SELECT legacy_count() FROM t6;
001107 }
001108 } {3}
001109 }
001110
001111 # The group_concat() function.
001112 #
001113 do_test func-24.1 {
001114 execsql {
001115 SELECT group_concat(t1) FROM tbl1
001116 }
001117 } {this,program,is,free,software}
001118 do_test func-24.2 {
001119 execsql {
001120 SELECT group_concat(t1,' ') FROM tbl1
001121 }
001122 } {{this program is free software}}
001123 do_test func-24.3 {
001124 execsql {
001125 SELECT group_concat(t1,' ' || rowid || ' ') FROM tbl1
001126 }
001127 } {{this 2 program 3 is 4 free 5 software}}
001128 do_test func-24.4 {
001129 execsql {
001130 SELECT group_concat(NULL,t1) FROM tbl1
001131 }
001132 } {{}}
001133 do_test func-24.5 {
001134 execsql {
001135 SELECT group_concat(t1,NULL) FROM tbl1
001136 }
001137 } {thisprogramisfreesoftware}
001138 do_test func-24.6 {
001139 execsql {
001140 SELECT 'BEGIN-'||group_concat(t1) FROM tbl1
001141 }
001142 } {BEGIN-this,program,is,free,software}
001143
001144 # Ticket #3179: Make sure aggregate functions can take many arguments.
001145 # None of the built-in aggregates do this, so use the md5sum() from the
001146 # test extensions.
001147 #
001148 unset -nocomplain midargs
001149 set midargs {}
001150 unset -nocomplain midres
001151 set midres {}
001152 unset -nocomplain result
001153 for {set i 1} {$i<[sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG -1]} {incr i} {
001154 append midargs ,'/$i'
001155 append midres /$i
001156 set result [md5 \
001157 "this${midres}program${midres}is${midres}free${midres}software${midres}"]
001158 set sql "SELECT md5sum(t1$midargs) FROM tbl1"
001159 do_test func-24.7.$i {
001160 db eval $::sql
001161 } $result
001162 }
001163
001164 # Ticket #3806. If the initial string in a group_concat is an empty
001165 # string, the separator that follows should still be present.
001166 #
001167 do_test func-24.8 {
001168 execsql {
001169 SELECT group_concat(CASE t1 WHEN 'this' THEN '' ELSE t1 END) FROM tbl1
001170 }
001171 } {,program,is,free,software}
001172 do_test func-24.9 {
001173 execsql {
001174 SELECT group_concat(CASE WHEN t1!='software' THEN '' ELSE t1 END) FROM tbl1
001175 }
001176 } {,,,,software}
001177
001178 # Ticket #3923. Initial empty strings have a separator. But initial
001179 # NULLs do not.
001180 #
001181 do_test func-24.10 {
001182 execsql {
001183 SELECT group_concat(CASE t1 WHEN 'this' THEN null ELSE t1 END) FROM tbl1
001184 }
001185 } {program,is,free,software}
001186 do_test func-24.11 {
001187 execsql {
001188 SELECT group_concat(CASE WHEN t1!='software' THEN null ELSE t1 END) FROM tbl1
001189 }
001190 } {software}
001191 do_test func-24.12 {
001192 execsql {
001193 SELECT group_concat(CASE t1 WHEN 'this' THEN ''
001194 WHEN 'program' THEN null ELSE t1 END) FROM tbl1
001195 }
001196 } {,is,free,software}
001197 # Tests to verify ticket http://www.sqlite.org/src/tktview/55746f9e65f8587c0
001198 do_test func-24.13 {
001199 execsql {
001200 SELECT typeof(group_concat(x)) FROM (SELECT '' AS x);
001201 }
001202 } {text}
001203 do_test func-24.14 {
001204 execsql {
001205 SELECT typeof(group_concat(x,''))
001206 FROM (SELECT '' AS x UNION ALL SELECT '');
001207 }
001208 } {text}
001209
001210
001211 # Use the test_isolation function to make sure that type conversions
001212 # on function arguments do not effect subsequent arguments.
001213 #
001214 do_test func-25.1 {
001215 execsql {SELECT test_isolation(t1,t1) FROM tbl1}
001216 } {this program is free software}
001217
001218 # Try to misuse the sqlite3_create_function() interface. Verify that
001219 # errors are returned.
001220 #
001221 do_test func-26.1 {
001222 abuse_create_function db
001223 } {}
001224
001225 # The previous test (func-26.1) registered a function with a very long
001226 # function name that takes many arguments and always returns NULL. Verify
001227 # that this function works correctly.
001228 #
001229 do_test func-26.2 {
001230 set a {}
001231 for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG} {incr i} {
001232 lappend a $i
001233 }
001234 db eval "
001235 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 ,]);
001236 "
001237 } {{}}
001238 do_test func-26.3 {
001239 set a {}
001240 for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG+1} {incr i} {
001241 lappend a $i
001242 }
001243 catchsql "
001244 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 ,]);
001245 "
001246 } {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}}
001247 do_test func-26.4 {
001248 set a {}
001249 for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG-1} {incr i} {
001250 lappend a $i
001251 }
001252 catchsql "
001253 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 ,]);
001254 "
001255 } {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()}}
001256 do_test func-26.5 {
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_12345678a(0);
001259 "
001260 } {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}}
001261 do_test func-26.6 {
001262 catchsql "
001263 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);
001264 "
001265 } {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}}
001266
001267 do_test func-27.1 {
001268 catchsql {SELECT coalesce()}
001269 } {1 {wrong number of arguments to function coalesce()}}
001270 do_test func-27.2 {
001271 catchsql {SELECT coalesce(1)}
001272 } {1 {wrong number of arguments to function coalesce()}}
001273 do_test func-27.3 {
001274 catchsql {SELECT coalesce(1,2)}
001275 } {0 1}
001276
001277 # Ticket 2d401a94287b5
001278 # Unknown function in a DEFAULT expression causes a segfault.
001279 #
001280 do_test func-28.1 {
001281 db eval {
001282 CREATE TABLE t28(x, y DEFAULT(nosuchfunc(1)));
001283 }
001284 catchsql {
001285 INSERT INTO t28(x) VALUES(1);
001286 }
001287 } {1 {unknown function: nosuchfunc()}}
001288
001289 # Verify that the length() and typeof() functions do not actually load
001290 # the content of their argument.
001291 #
001292 do_test func-29.1 {
001293 db eval {
001294 CREATE TABLE t29(id INTEGER PRIMARY KEY, x, y);
001295 INSERT INTO t29 VALUES(1, 2, 3), (2, NULL, 4), (3, 4.5, 5);
001296 INSERT INTO t29 VALUES(4, randomblob(1000000), 6);
001297 INSERT INTO t29 VALUES(5, "hello", 7);
001298 }
001299 db close
001300 sqlite3 db test.db
001301 sqlite3_db_status db CACHE_MISS 1
001302 db eval {SELECT typeof(x), length(x), typeof(y) FROM t29 ORDER BY id}
001303 } {integer 1 integer null {} integer real 3 integer blob 1000000 integer text 5 integer}
001304 do_test func-29.2 {
001305 set x [lindex [sqlite3_db_status db CACHE_MISS 1] 1]
001306 if {$x<5} {set x 1}
001307 set x
001308 } {1}
001309 do_test func-29.3 {
001310 db close
001311 sqlite3 db test.db
001312 sqlite3_db_status db CACHE_MISS 1
001313 db eval {SELECT typeof(+x) FROM t29 ORDER BY id}
001314 } {integer null real blob text}
001315 if {[permutation] != "mmap"} {
001316 ifcapable !direct_read {
001317 do_test func-29.4 {
001318 set x [lindex [sqlite3_db_status db CACHE_MISS 1] 1]
001319 if {$x>100} {set x many}
001320 set x
001321 } {many}
001322 }
001323 }
001324 do_test func-29.5 {
001325 db close
001326 sqlite3 db test.db
001327 sqlite3_db_status db CACHE_MISS 1
001328 db eval {SELECT sum(length(x)) FROM t29}
001329 } {1000009}
001330 do_test func-29.6 {
001331 set x [lindex [sqlite3_db_status db CACHE_MISS 1] 1]
001332 if {$x<5} {set x 1}
001333 set x
001334 } {1}
001335
001336 # The OP_Column opcode has an optimization that avoids loading content
001337 # for fields with content-length=0 when the content offset is on an overflow
001338 # page. Make sure the optimization works.
001339 #
001340 do_execsql_test func-29.10 {
001341 CREATE TABLE t29b(a,b,c,d,e,f,g,h,i);
001342 INSERT INTO t29b
001343 VALUES(1, hex(randomblob(2000)), null, 0, 1, '', zeroblob(0),'x',x'01');
001344 SELECT typeof(c), typeof(d), typeof(e), typeof(f),
001345 typeof(g), typeof(h), typeof(i) FROM t29b;
001346 } {null integer integer text blob text blob}
001347 do_execsql_test func-29.11 {
001348 SELECT length(f), length(g), length(h), length(i) FROM t29b;
001349 } {0 0 1 1}
001350 do_execsql_test func-29.12 {
001351 SELECT quote(f), quote(g), quote(h), quote(i) FROM t29b;
001352 } {'' X'' 'x' X'01'}
001353
001354 # EVIDENCE-OF: R-29701-50711 The unicode(X) function returns the numeric
001355 # unicode code point corresponding to the first character of the string
001356 # X.
001357 #
001358 # EVIDENCE-OF: R-55469-62130 The char(X1,X2,...,XN) function returns a
001359 # string composed of characters having the unicode code point values of
001360 # integers X1 through XN, respectively.
001361 #
001362 do_execsql_test func-30.1 {SELECT unicode('$');} 36
001363 do_execsql_test func-30.2 [subst {SELECT unicode('\u00A2');}] 162
001364 do_execsql_test func-30.3 [subst {SELECT unicode('\u20AC');}] 8364
001365 do_execsql_test func-30.4 {SELECT char(36,162,8364);} [subst {$\u00A2\u20AC}]
001366
001367 for {set i 1} {$i<0xd800} {incr i 13} {
001368 do_execsql_test func-30.5.$i {SELECT unicode(char($i))} $i
001369 }
001370 for {set i 57344} {$i<=0xfffd} {incr i 17} {
001371 if {$i==0xfeff} continue
001372 do_execsql_test func-30.5.$i {SELECT unicode(char($i))} $i
001373 }
001374 for {set i 65536} {$i<=0x10ffff} {incr i 139} {
001375 do_execsql_test func-30.5.$i {SELECT unicode(char($i))} $i
001376 }
001377
001378 # Test char().
001379 #
001380 do_execsql_test func-31.1 {
001381 SELECT char(), length(char()), typeof(char())
001382 } {{} 0 text}
001383 finish_test