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