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