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 # Test the quote function for +Inf and -Inf
000790 do_execsql_test func-16.2 {
000791 SELECT quote(4.2e+859), quote(-7.8e+904);
000792 } {9.0e+999 -9.0e+999}
000793
000794 # Correctly handle function error messages that include %. Ticket #1354
000795 #
000796 do_test func-17.1 {
000797 proc testfunc1 args {error "Error %d with %s percents %p"}
000798 db function testfunc1 ::testfunc1
000799 catchsql {
000800 SELECT testfunc1(1,2,3);
000801 }
000802 } {1 {Error %d with %s percents %p}}
000803
000804 # The SUM function should return integer results when all inputs are integer.
000805 #
000806 do_test func-18.1 {
000807 execsql {
000808 CREATE TABLE t5(x);
000809 INSERT INTO t5 VALUES(1);
000810 INSERT INTO t5 VALUES(-99);
000811 INSERT INTO t5 VALUES(10000);
000812 SELECT sum(x) FROM t5;
000813 }
000814 } {9902}
000815 ifcapable floatingpoint {
000816 do_test func-18.2 {
000817 execsql {
000818 INSERT INTO t5 VALUES(0.0);
000819 SELECT sum(x) FROM t5;
000820 }
000821 } {9902.0}
000822 }
000823
000824 # The sum of nothing is NULL. But the sum of all NULLs is NULL.
000825 #
000826 # The TOTAL of nothing is 0.0.
000827 #
000828 do_test func-18.3 {
000829 execsql {
000830 DELETE FROM t5;
000831 SELECT sum(x), total(x) FROM t5;
000832 }
000833 } {{} 0.0}
000834 do_test func-18.4 {
000835 execsql {
000836 INSERT INTO t5 VALUES(NULL);
000837 SELECT sum(x), total(x) FROM t5
000838 }
000839 } {{} 0.0}
000840 do_test func-18.5 {
000841 execsql {
000842 INSERT INTO t5 VALUES(NULL);
000843 SELECT sum(x), total(x) FROM t5
000844 }
000845 } {{} 0.0}
000846 do_test func-18.6 {
000847 execsql {
000848 INSERT INTO t5 VALUES(123);
000849 SELECT sum(x), total(x) FROM t5
000850 }
000851 } {123 123.0}
000852
000853 # Ticket #1664, #1669, #1670, #1674: An integer overflow on SUM causes
000854 # an error. The non-standard TOTAL() function continues to give a helpful
000855 # result.
000856 #
000857 do_test func-18.10 {
000858 execsql {
000859 CREATE TABLE t6(x INTEGER);
000860 INSERT INTO t6 VALUES(1);
000861 INSERT INTO t6 VALUES(1<<62);
000862 SELECT sum(x) - ((1<<62)+1) from t6;
000863 }
000864 } 0
000865 do_test func-18.11 {
000866 execsql {
000867 SELECT typeof(sum(x)) FROM t6
000868 }
000869 } integer
000870 ifcapable floatingpoint {
000871 do_catchsql_test func-18.12 {
000872 INSERT INTO t6 VALUES(1<<62);
000873 SELECT sum(x) - ((1<<62)*2.0+1) from t6;
000874 } {1 {integer overflow}}
000875 do_catchsql_test func-18.13 {
000876 SELECT total(x) - ((1<<62)*2.0+1) FROM t6
000877 } {0 0.0}
000878 }
000879 if {[working_64bit_int]} {
000880 do_test func-18.14 {
000881 execsql {
000882 SELECT sum(-9223372036854775805);
000883 }
000884 } -9223372036854775805
000885 }
000886 ifcapable compound&&subquery {
000887
000888 do_test func-18.15 {
000889 catchsql {
000890 SELECT sum(x) FROM
000891 (SELECT 9223372036854775807 AS x UNION ALL
000892 SELECT 10 AS x);
000893 }
000894 } {1 {integer overflow}}
000895 if {[working_64bit_int]} {
000896 do_test func-18.16 {
000897 catchsql {
000898 SELECT sum(x) FROM
000899 (SELECT 9223372036854775807 AS x UNION ALL
000900 SELECT -10 AS x);
000901 }
000902 } {0 9223372036854775797}
000903 do_test func-18.17 {
000904 catchsql {
000905 SELECT sum(x) FROM
000906 (SELECT -9223372036854775807 AS x UNION ALL
000907 SELECT 10 AS x);
000908 }
000909 } {0 -9223372036854775797}
000910 }
000911 do_test func-18.18 {
000912 catchsql {
000913 SELECT sum(x) FROM
000914 (SELECT -9223372036854775807 AS x UNION ALL
000915 SELECT -10 AS x);
000916 }
000917 } {1 {integer overflow}}
000918 do_test func-18.19 {
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.20 {
000924 catchsql {
000925 SELECT sum(x) FROM (SELECT -9 AS x UNION ALL SELECT 10 AS x);
000926 }
000927 } {0 1}
000928 do_test func-18.21 {
000929 catchsql {
000930 SELECT sum(x) FROM (SELECT -10 AS x UNION ALL SELECT 9 AS x);
000931 }
000932 } {0 -1}
000933 do_test func-18.22 {
000934 catchsql {
000935 SELECT sum(x) FROM (SELECT 10 AS x UNION ALL SELECT -9 AS x);
000936 }
000937 } {0 1}
000938
000939 } ;# ifcapable compound&&subquery
000940
000941 # Integer overflow on abs()
000942 #
000943 if {[working_64bit_int]} {
000944 do_test func-18.31 {
000945 catchsql {
000946 SELECT abs(-9223372036854775807);
000947 }
000948 } {0 9223372036854775807}
000949 }
000950 do_test func-18.32 {
000951 catchsql {
000952 SELECT abs(-9223372036854775807-1);
000953 }
000954 } {1 {integer overflow}}
000955
000956 # The MATCH function exists but is only a stub and always throws an error.
000957 #
000958 do_test func-19.1 {
000959 execsql {
000960 SELECT match(a,b) FROM t1 WHERE 0;
000961 }
000962 } {}
000963 do_test func-19.2 {
000964 catchsql {
000965 SELECT 'abc' MATCH 'xyz';
000966 }
000967 } {1 {unable to use function MATCH in the requested context}}
000968 do_test func-19.3 {
000969 catchsql {
000970 SELECT 'abc' NOT MATCH 'xyz';
000971 }
000972 } {1 {unable to use function MATCH in the requested context}}
000973 do_test func-19.4 {
000974 catchsql {
000975 SELECT match(1,2,3);
000976 }
000977 } {1 {wrong number of arguments to function match()}}
000978
000979 # Soundex tests.
000980 #
000981 if {![catch {db eval {SELECT soundex('hello')}}]} {
000982 set i 0
000983 foreach {name sdx} {
000984 euler E460
000985 EULER E460
000986 Euler E460
000987 ellery E460
000988 gauss G200
000989 ghosh G200
000990 hilbert H416
000991 Heilbronn H416
000992 knuth K530
000993 kant K530
000994 Lloyd L300
000995 LADD L300
000996 Lukasiewicz L222
000997 Lissajous L222
000998 A A000
000999 12345 ?000
001000 } {
001001 incr i
001002 do_test func-20.$i {
001003 execsql {SELECT soundex($name)}
001004 } $sdx
001005 }
001006 }
001007
001008 # Tests of the REPLACE function.
001009 #
001010 do_test func-21.1 {
001011 catchsql {
001012 SELECT replace(1,2);
001013 }
001014 } {1 {wrong number of arguments to function replace()}}
001015 do_test func-21.2 {
001016 catchsql {
001017 SELECT replace(1,2,3,4);
001018 }
001019 } {1 {wrong number of arguments to function replace()}}
001020 do_test func-21.3 {
001021 execsql {
001022 SELECT typeof(replace('This is the main test string', NULL, 'ALT'));
001023 }
001024 } {null}
001025 do_test func-21.4 {
001026 execsql {
001027 SELECT typeof(replace(NULL, 'main', 'ALT'));
001028 }
001029 } {null}
001030 do_test func-21.5 {
001031 execsql {
001032 SELECT typeof(replace('This is the main test string', 'main', NULL));
001033 }
001034 } {null}
001035 do_test func-21.6 {
001036 execsql {
001037 SELECT replace('This is the main test string', 'main', 'ALT');
001038 }
001039 } {{This is the ALT test string}}
001040 do_test func-21.7 {
001041 execsql {
001042 SELECT replace('This is the main test string', 'main', 'larger-main');
001043 }
001044 } {{This is the larger-main test string}}
001045 do_test func-21.8 {
001046 execsql {
001047 SELECT replace('aaaaaaa', 'a', '0123456789');
001048 }
001049 } {0123456789012345678901234567890123456789012345678901234567890123456789}
001050 do_execsql_test func-21.9 {
001051 SELECT typeof(replace(1,'',0));
001052 } {text}
001053
001054 ifcapable tclvar {
001055 do_test func-21.9 {
001056 # Attempt to exploit a buffer-overflow that at one time existed
001057 # in the REPLACE function.
001058 set ::str "[string repeat A 29998]CC[string repeat A 35537]"
001059 set ::rep [string repeat B 65536]
001060 execsql {
001061 SELECT LENGTH(REPLACE($::str, 'C', $::rep));
001062 }
001063 } [expr 29998 + 2*65536 + 35537]
001064 }
001065
001066 # Tests for the TRIM, LTRIM and RTRIM functions.
001067 #
001068 do_test func-22.1 {
001069 catchsql {SELECT trim(1,2,3)}
001070 } {1 {wrong number of arguments to function trim()}}
001071 do_test func-22.2 {
001072 catchsql {SELECT ltrim(1,2,3)}
001073 } {1 {wrong number of arguments to function ltrim()}}
001074 do_test func-22.3 {
001075 catchsql {SELECT rtrim(1,2,3)}
001076 } {1 {wrong number of arguments to function rtrim()}}
001077 do_test func-22.4 {
001078 execsql {SELECT trim(' hi ');}
001079 } {hi}
001080 do_test func-22.5 {
001081 execsql {SELECT ltrim(' hi ');}
001082 } {{hi }}
001083 do_test func-22.6 {
001084 execsql {SELECT rtrim(' hi ');}
001085 } {{ hi}}
001086 do_test func-22.7 {
001087 execsql {SELECT trim(' hi ','xyz');}
001088 } {{ hi }}
001089 do_test func-22.8 {
001090 execsql {SELECT ltrim(' hi ','xyz');}
001091 } {{ hi }}
001092 do_test func-22.9 {
001093 execsql {SELECT rtrim(' hi ','xyz');}
001094 } {{ hi }}
001095 do_test func-22.10 {
001096 execsql {SELECT trim('xyxzy hi zzzy','xyz');}
001097 } {{ hi }}
001098 do_test func-22.11 {
001099 execsql {SELECT ltrim('xyxzy hi zzzy','xyz');}
001100 } {{ hi zzzy}}
001101 do_test func-22.12 {
001102 execsql {SELECT rtrim('xyxzy hi zzzy','xyz');}
001103 } {{xyxzy hi }}
001104 do_test func-22.13 {
001105 execsql {SELECT trim(' hi ','');}
001106 } {{ hi }}
001107 if {[db one {PRAGMA encoding}]=="UTF-8"} {
001108 do_test func-22.14 {
001109 execsql {SELECT hex(trim(x'c280e1bfbff48fbfbf6869',x'6162e1bfbfc280'))}
001110 } {F48FBFBF6869}
001111 do_test func-22.15 {
001112 execsql {SELECT hex(trim(x'6869c280e1bfbff48fbfbf61',
001113 x'6162e1bfbfc280f48fbfbf'))}
001114 } {6869}
001115 do_test func-22.16 {
001116 execsql {SELECT hex(trim(x'ceb1ceb2ceb3',x'ceb1'));}
001117 } {CEB2CEB3}
001118 }
001119 do_test func-22.20 {
001120 execsql {SELECT typeof(trim(NULL));}
001121 } {null}
001122 do_test func-22.21 {
001123 execsql {SELECT typeof(trim(NULL,'xyz'));}
001124 } {null}
001125 do_test func-22.22 {
001126 execsql {SELECT typeof(trim('hello',NULL));}
001127 } {null}
001128
001129 # 2021-06-15 - infinite loop due to unsigned character counter
001130 # overflow, reported by Zimuzo Ezeozue
001131 #
001132 do_execsql_test func-22.23 {
001133 SELECT trim('xyzzy',x'c0808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080');
001134 } {xyzzy}
001135
001136 # This is to test the deprecated sqlite3_aggregate_count() API.
001137 #
001138 ifcapable deprecated {
001139 do_test func-23.1 {
001140 sqlite3_create_aggregate db
001141 execsql {
001142 SELECT legacy_count() FROM t6;
001143 }
001144 } {3}
001145 }
001146
001147 # The group_concat() and string_agg() functions.
001148 #
001149 do_test func-24.1 {
001150 execsql {
001151 SELECT group_concat(t1), string_agg(t1,',') FROM tbl1
001152 }
001153 } {this,program,is,free,software this,program,is,free,software}
001154 do_test func-24.2 {
001155 execsql {
001156 SELECT group_concat(t1,' '), string_agg(t1,' ') FROM tbl1
001157 }
001158 } {{this program is free software} {this program is free software}}
001159 do_test func-24.3 {
001160 execsql {
001161 SELECT group_concat(t1,' ' || rowid || ' ') FROM tbl1
001162 }
001163 } {{this 2 program 3 is 4 free 5 software}}
001164 do_test func-24.4 {
001165 execsql {
001166 SELECT group_concat(NULL,t1) FROM tbl1
001167 }
001168 } {{}}
001169 do_test func-24.5 {
001170 execsql {
001171 SELECT group_concat(t1,NULL), string_agg(t1,NULL) FROM tbl1
001172 }
001173 } {thisprogramisfreesoftware thisprogramisfreesoftware}
001174 do_test func-24.6 {
001175 execsql {
001176 SELECT 'BEGIN-'||group_concat(t1) FROM tbl1
001177 }
001178 } {BEGIN-this,program,is,free,software}
001179
001180 # Ticket #3179: Make sure aggregate functions can take many arguments.
001181 # None of the built-in aggregates do this, so use the md5sum() from the
001182 # test extensions.
001183 #
001184 unset -nocomplain midargs
001185 set midargs {}
001186 unset -nocomplain midres
001187 set midres {}
001188 unset -nocomplain result
001189 for {set i 1} {$i<[sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG -1]} {incr i} {
001190 append midargs ,'/$i'
001191 append midres /$i
001192 set result [md5 \
001193 "this${midres}program${midres}is${midres}free${midres}software${midres}"]
001194 set sql "SELECT md5sum(t1$midargs) FROM tbl1"
001195 do_test func-24.7.$i {
001196 db eval $::sql
001197 } $result
001198 }
001199
001200 # Ticket #3806. If the initial string in a group_concat is an empty
001201 # string, the separator that follows should still be present.
001202 #
001203 do_test func-24.8 {
001204 execsql {
001205 SELECT group_concat(CASE t1 WHEN 'this' THEN '' ELSE t1 END) FROM tbl1
001206 }
001207 } {,program,is,free,software}
001208 do_test func-24.9 {
001209 execsql {
001210 SELECT group_concat(CASE WHEN t1!='software' THEN '' ELSE t1 END) FROM tbl1
001211 }
001212 } {,,,,software}
001213
001214 # Ticket #3923. Initial empty strings have a separator. But initial
001215 # NULLs do not.
001216 #
001217 do_test func-24.10 {
001218 execsql {
001219 SELECT group_concat(CASE t1 WHEN 'this' THEN null ELSE t1 END) FROM tbl1
001220 }
001221 } {program,is,free,software}
001222 do_test func-24.11 {
001223 execsql {
001224 SELECT group_concat(CASE WHEN t1!='software' THEN null ELSE t1 END) FROM tbl1
001225 }
001226 } {software}
001227 do_test func-24.12 {
001228 execsql {
001229 SELECT group_concat(CASE t1 WHEN 'this' THEN ''
001230 WHEN 'program' THEN null ELSE t1 END) FROM tbl1
001231 }
001232 } {,is,free,software}
001233 # Tests to verify ticket http://www.sqlite.org/src/tktview/55746f9e65f8587c0
001234 do_test func-24.13 {
001235 execsql {
001236 SELECT typeof(group_concat(x)) FROM (SELECT '' AS x);
001237 }
001238 } {text}
001239 do_test func-24.14 {
001240 execsql {
001241 SELECT typeof(group_concat(x,''))
001242 FROM (SELECT '' AS x UNION ALL SELECT '');
001243 }
001244 } {text}
001245
001246
001247 # Use the test_isolation function to make sure that type conversions
001248 # on function arguments do not effect subsequent arguments.
001249 #
001250 do_test func-25.1 {
001251 execsql {SELECT test_isolation(t1,t1) FROM tbl1}
001252 } {this program is free software}
001253
001254 # Try to misuse the sqlite3_create_function() interface. Verify that
001255 # errors are returned.
001256 #
001257 do_test func-26.1 {
001258 abuse_create_function db
001259 } {}
001260
001261 # The previous test (func-26.1) registered a function with a very long
001262 # function name that takes many arguments and always returns NULL. Verify
001263 # that this function works correctly.
001264 #
001265 do_test func-26.2 {
001266 set a {}
001267 for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG} {incr i} {
001268 lappend a $i
001269 }
001270 db eval "
001271 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 ,]);
001272 "
001273 } {{}}
001274 do_test func-26.3 {
001275 set a {}
001276 for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG+1} {incr i} {
001277 lappend a $i
001278 }
001279 catchsql "
001280 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 ,]);
001281 "
001282 } {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}}
001283 do_test func-26.4 {
001284 set a {}
001285 for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG-1} {incr i} {
001286 lappend a $i
001287 }
001288 catchsql "
001289 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 ,]);
001290 "
001291 } {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()}}
001292 do_test func-26.5 {
001293 catchsql "
001294 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);
001295 "
001296 } {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}}
001297 do_test func-26.6 {
001298 catchsql "
001299 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);
001300 "
001301 } {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}}
001302
001303 do_test func-27.1 {
001304 catchsql {SELECT coalesce()}
001305 } {1 {wrong number of arguments to function coalesce()}}
001306 do_test func-27.2 {
001307 catchsql {SELECT coalesce(1)}
001308 } {1 {wrong number of arguments to function coalesce()}}
001309 do_test func-27.3 {
001310 catchsql {SELECT coalesce(1,2)}
001311 } {0 1}
001312
001313 # Ticket 2d401a94287b5
001314 # Unknown function in a DEFAULT expression causes a segfault.
001315 #
001316 do_test func-28.1 {
001317 db eval {
001318 CREATE TABLE t28(x, y DEFAULT(nosuchfunc(1)));
001319 }
001320 catchsql {
001321 INSERT INTO t28(x) VALUES(1);
001322 }
001323 } {1 {unknown function: nosuchfunc()}}
001324
001325 # Verify that the length() and typeof() functions do not actually load
001326 # the content of their argument.
001327 #
001328 do_test func-29.1 {
001329 db eval {
001330 CREATE TABLE t29(id INTEGER PRIMARY KEY, x, y);
001331 INSERT INTO t29 VALUES(1, 2, 3), (2, NULL, 4), (3, 4.5, 5);
001332 INSERT INTO t29 VALUES(4, randomblob(1000000), 6);
001333 INSERT INTO t29 VALUES(5, 'hello', 7);
001334 }
001335 db close
001336 sqlite3 db test.db
001337 sqlite3_db_status db CACHE_MISS 1
001338 db eval {SELECT typeof(x), length(x), typeof(y) FROM t29 ORDER BY id}
001339 } {integer 1 integer null {} integer real 3 integer blob 1000000 integer text 5 integer}
001340 do_test func-29.2 {
001341 set x [lindex [sqlite3_db_status db CACHE_MISS 1] 1]
001342 if {$x<5} {set x 1}
001343 set x
001344 } {1}
001345 do_test func-29.3 {
001346 db close
001347 sqlite3 db test.db
001348 sqlite3_db_status db CACHE_MISS 1
001349 db eval {SELECT typeof(+x) FROM t29 ORDER BY id}
001350 } {integer null real blob text}
001351 if {[permutation] != "mmap"} {
001352 ifcapable !direct_read {
001353 do_test func-29.4 {
001354 set x [lindex [sqlite3_db_status db CACHE_MISS 1] 1]
001355 if {$x>100} {set x many}
001356 set x
001357 } {many}
001358 }
001359 }
001360 do_test func-29.5 {
001361 db close
001362 sqlite3 db test.db
001363 sqlite3_db_status db CACHE_MISS 1
001364 db eval {SELECT sum(length(x)) FROM t29}
001365 } {1000009}
001366 do_test func-29.6 {
001367 set x [lindex [sqlite3_db_status db CACHE_MISS 1] 1]
001368 if {$x<5} {set x 1}
001369 set x
001370 } {1}
001371
001372 # The OP_Column opcode has an optimization that avoids loading content
001373 # for fields with content-length=0 when the content offset is on an overflow
001374 # page. Make sure the optimization works.
001375 #
001376 do_execsql_test func-29.10 {
001377 CREATE TABLE t29b(a,b,c,d,e,f,g,h,i);
001378 INSERT INTO t29b
001379 VALUES(1, hex(randomblob(2000)), null, 0, 1, '', zeroblob(0),'x',x'01');
001380 SELECT typeof(c), typeof(d), typeof(e), typeof(f),
001381 typeof(g), typeof(h), typeof(i) FROM t29b;
001382 } {null integer integer text blob text blob}
001383 do_execsql_test func-29.11 {
001384 SELECT length(f), length(g), length(h), length(i) FROM t29b;
001385 } {0 0 1 1}
001386 do_execsql_test func-29.12 {
001387 SELECT quote(f), quote(g), quote(h), quote(i) FROM t29b;
001388 } {'' X'' 'x' X'01'}
001389
001390 # EVIDENCE-OF: R-29701-50711 The unicode(X) function returns the numeric
001391 # unicode code point corresponding to the first character of the string
001392 # X.
001393 #
001394 # EVIDENCE-OF: R-55469-62130 The char(X1,X2,...,XN) function returns a
001395 # string composed of characters having the unicode code point values of
001396 # integers X1 through XN, respectively.
001397 #
001398 do_execsql_test func-30.1 {SELECT unicode('$');} 36
001399 do_execsql_test func-30.2 [subst {SELECT unicode('\u00A2');}] 162
001400 do_execsql_test func-30.3 [subst {SELECT unicode('\u20AC');}] 8364
001401 do_execsql_test func-30.4 {SELECT char(36,162,8364);} [subst {$\u00A2\u20AC}]
001402
001403 for {set i 1} {$i<0xd800} {incr i 13} {
001404 do_execsql_test func-30.5.$i {SELECT unicode(char($i))} $i
001405 }
001406 for {set i 57344} {$i<=0xfffd} {incr i 17} {
001407 if {$i==0xfeff} continue
001408 do_execsql_test func-30.5.$i {SELECT unicode(char($i))} $i
001409 }
001410 for {set i 65536} {$i<=0x10ffff} {incr i 139} {
001411 do_execsql_test func-30.5.$i {SELECT unicode(char($i))} $i
001412 }
001413
001414 # Test char().
001415 #
001416 do_execsql_test func-31.1 {
001417 SELECT char(), length(char()), typeof(char())
001418 } {{} 0 text}
001419
001420 # sqlite3_value_frombind()
001421 #
001422 do_execsql_test func-32.100 {
001423 SELECT test_frombind(1,2,3,4);
001424 } {0}
001425 do_execsql_test func-32.110 {
001426 SELECT test_frombind(1,2,?,4);
001427 } {4}
001428 do_execsql_test func-32.120 {
001429 SELECT test_frombind(1,(?),4,?+7);
001430 } {2}
001431 do_execsql_test func-32.130 {
001432 DROP TABLE IF EXISTS t1;
001433 CREATE TABLE t1(a,b,c,e,f);
001434 INSERT INTO t1 VALUES(1,2.5,'xyz',x'e0c1b2a3',null);
001435 SELECT test_frombind(a,b,c,e,f,$xyz) FROM t1;
001436 } {32}
001437 do_execsql_test func-32.140 {
001438 SELECT test_frombind(a,b,c,e,f,$xyz+f) FROM t1;
001439 } {0}
001440 do_execsql_test func-32.150 {
001441 SELECT test_frombind(x.a,y.b,x.c,:123,y.e,x.f,$xyz+y.f) FROM t1 x, t1 y;
001442 } {8}
001443
001444 # 2019-08-15
001445 # Direct-only functions.
001446 #
001447 proc testdirectonly {x} {return [expr {$x*2}]}
001448 do_test func-33.1 {
001449 db func testdirectonly -directonly testdirectonly
001450 db eval {SELECT testdirectonly(15)}
001451 } {30}
001452 do_catchsql_test func-33.2 {
001453 CREATE VIEW v33(y) AS SELECT testdirectonly(15);
001454 SELECT * FROM v33;
001455 } {1 {unsafe use of testdirectonly()}}
001456 do_execsql_test func-33.3 {
001457 SELECT * FROM (SELECT testdirectonly(15)) AS v33;
001458 } {30}
001459 do_execsql_test func-33.4 {
001460 WITH c(x) AS (SELECT testdirectonly(15))
001461 SELECT * FROM c;
001462 } {30}
001463 do_catchsql_test func-33.5 {
001464 WITH c(x) AS (SELECT * FROM v33)
001465 SELECT * FROM c;
001466 } {1 {unsafe use of testdirectonly()}}
001467 do_execsql_test func-33.10 {
001468 CREATE TABLE t33a(a,b);
001469 CREATE TABLE t33b(x,y);
001470 CREATE TRIGGER r1 AFTER INSERT ON t33a BEGIN
001471 INSERT INTO t33b(x,y) VALUES(testdirectonly(new.a),new.b);
001472 END;
001473 } {}
001474 do_catchsql_test func-33.11 {
001475 INSERT INTO t33a VALUES(1,2);
001476 } {1 {unsafe use of testdirectonly()}}
001477
001478 ifcapable altertable {
001479 do_execsql_test func-33.20 {
001480 ALTER TABLE t33a RENAME COLUMN a TO aaa;
001481 SELECT sql FROM sqlite_master WHERE name='r1';
001482 } {{CREATE TRIGGER r1 AFTER INSERT ON t33a BEGIN
001483 INSERT INTO t33b(x,y) VALUES(testdirectonly(new.aaa),new.b);
001484 END}}
001485 }
001486
001487 # 2020-01-09 Yongheng fuzzer find
001488 # The bug is in the register-validity debug logic, not in the SQLite core
001489 # and as such it only impacts debug builds. Release builds work fine.
001490 #
001491 reset_db
001492 do_execsql_test func-34.10 {
001493 CREATE TABLE t1(a INT CHECK(
001494 datetime( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9,
001495 10,11,12,13,14,15,16,17,18,19,
001496 20,21,22,23,24,25,26,27,28,29,
001497 30,31,32,33,34,35,36,37,38,39,
001498 40,41,42,43,44,45,46,47,48,a)
001499 )
001500 );
001501 INSERT INTO t1(a) VALUES(1),(2);
001502 SELECT * FROM t1;
001503 } {1 2}
001504
001505 # 2020-03-11 COALESCE() should short-circuit
001506 # See also ticket 3c9eadd2a6ba0aa5
001507 # Both issues stem from the fact that functions that could
001508 # throw exceptions were being factored out into initialization
001509 # code. The fix was to put those function calls inside of
001510 # OP_Once instead.
001511 #
001512 reset_db
001513 do_execsql_test func-35.100 {
001514 CREATE TABLE t1(x);
001515 SELECT coalesce(x, abs(-9223372036854775808)) FROM t1;
001516 } {}
001517 do_execsql_test func-35.110 {
001518 SELECT coalesce(x, 'xyz' LIKE printf('%.1000000c','y')) FROM t1;
001519 } {}
001520 do_execsql_test func-35.200 {
001521 CREATE TABLE t0(c0 CHECK(ABS(-9223372036854775808)));
001522 PRAGMA integrity_check;
001523 } {ok}
001524
001525 # 2021-01-07: The -> and ->> operators.
001526 #
001527 proc ptr1 {a b} { return "$a->$b" }
001528 db func -> ptr1
001529 proc ptr2 {a b} { return "$a->>$b" }
001530 db func ->> ptr2
001531 do_execsql_test func-36.100 {
001532 SELECT 123 -> 456
001533 } {123->456}
001534 do_execsql_test func-36.110 {
001535 SELECT 123 ->> 456
001536 } {123->>456}
001537
001538 # 2023-06-26
001539 # Enhanced precision of SUM().
001540 #
001541 reset_db
001542 do_catchsql_test func-37.100 {
001543 WITH c(x) AS (VALUES(9223372036854775807),(9223372036854775807),
001544 (123),(-9223372036854775807),(-9223372036854775807))
001545 SELECT sum(x) FROM c;
001546 } {1 {integer overflow}}
001547 do_catchsql_test func-37.110 {
001548 WITH c(x) AS (VALUES(9223372036854775807),(1))
001549 SELECT sum(x) FROM c;
001550 } {1 {integer overflow}}
001551 do_catchsql_test func-37.120 {
001552 WITH c(x) AS (VALUES(9223372036854775807),(10000),(-10010))
001553 SELECT sum(x) FROM c;
001554 } {1 {integer overflow}}
001555
001556 # 2023-08-28 forum post https://sqlite.org/forum/forumpost/1c06ddcacc86032a
001557 # Incorrect handling of infinity by SUM().
001558 #
001559 do_execsql_test func-38.100 {
001560 WITH t1(x) AS (VALUES(9e+999)) SELECT sum(x), avg(x), total(x) FROM t1;
001561 WITH t1(x) AS (VALUES(-9e+999)) SELECT sum(x), avg(x), total(x) FROM t1;
001562 } {Inf Inf Inf -Inf -Inf -Inf}
001563
001564 # 2024-03-21 https://sqlite.org/forum/forumpost/23b8688ef4
001565 # Another problem with Kahan-Babushka-Neumaier summation and
001566 # infinities.
001567 #
001568 do_execsql_test func-39.101 {
001569 WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<1)
001570 SELECT sum(1.7976931348623157e308),
001571 avg(1.7976931348623157e308),
001572 total(1.7976931348623157e308)
001573 FROM c;
001574 } {1.79769313486232e+308 1.79769313486232e+308 1.79769313486232e+308}
001575 for {set i 2} {$i<10} {incr i} {
001576 do_execsql_test func-39.[expr {10*$i+100}] {
001577 WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<$i)
001578 SELECT sum(1.7976931348623157e308),
001579 avg(1.7976931348623157e308),
001580 total(1.7976931348623157e308)
001581 FROM c;
001582 } {Inf Inf Inf}
001583 }
001584
001585 finish_test