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