1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/func.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,1032 @@
1.4 +# 2001 September 15
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +# This file implements regression tests for SQLite library. The
1.15 +# focus of this file is testing built-in functions.
1.16 +#
1.17 +# $Id: func.test,v 1.86 2008/08/04 03:51:24 danielk1977 Exp $
1.18 +
1.19 +set testdir [file dirname $argv0]
1.20 +source $testdir/tester.tcl
1.21 +
1.22 +# Create a table to work with.
1.23 +#
1.24 +do_test func-0.0 {
1.25 + execsql {CREATE TABLE tbl1(t1 text)}
1.26 + foreach word {this program is free software} {
1.27 + execsql "INSERT INTO tbl1 VALUES('$word')"
1.28 + }
1.29 + execsql {SELECT t1 FROM tbl1 ORDER BY t1}
1.30 +} {free is program software this}
1.31 +do_test func-0.1 {
1.32 + execsql {
1.33 + CREATE TABLE t2(a);
1.34 + INSERT INTO t2 VALUES(1);
1.35 + INSERT INTO t2 VALUES(NULL);
1.36 + INSERT INTO t2 VALUES(345);
1.37 + INSERT INTO t2 VALUES(NULL);
1.38 + INSERT INTO t2 VALUES(67890);
1.39 + SELECT * FROM t2;
1.40 + }
1.41 +} {1 {} 345 {} 67890}
1.42 +
1.43 +# Check out the length() function
1.44 +#
1.45 +do_test func-1.0 {
1.46 + execsql {SELECT length(t1) FROM tbl1 ORDER BY t1}
1.47 +} {4 2 7 8 4}
1.48 +do_test func-1.1 {
1.49 + set r [catch {execsql {SELECT length(*) FROM tbl1 ORDER BY t1}} msg]
1.50 + lappend r $msg
1.51 +} {1 {wrong number of arguments to function length()}}
1.52 +do_test func-1.2 {
1.53 + set r [catch {execsql {SELECT length(t1,5) FROM tbl1 ORDER BY t1}} msg]
1.54 + lappend r $msg
1.55 +} {1 {wrong number of arguments to function length()}}
1.56 +do_test func-1.3 {
1.57 + execsql {SELECT length(t1), count(*) FROM tbl1 GROUP BY length(t1)
1.58 + ORDER BY length(t1)}
1.59 +} {2 1 4 2 7 1 8 1}
1.60 +do_test func-1.4 {
1.61 + execsql {SELECT coalesce(length(a),-1) FROM t2}
1.62 +} {1 -1 3 -1 5}
1.63 +
1.64 +# Check out the substr() function
1.65 +#
1.66 +do_test func-2.0 {
1.67 + execsql {SELECT substr(t1,1,2) FROM tbl1 ORDER BY t1}
1.68 +} {fr is pr so th}
1.69 +do_test func-2.1 {
1.70 + execsql {SELECT substr(t1,2,1) FROM tbl1 ORDER BY t1}
1.71 +} {r s r o h}
1.72 +do_test func-2.2 {
1.73 + execsql {SELECT substr(t1,3,3) FROM tbl1 ORDER BY t1}
1.74 +} {ee {} ogr ftw is}
1.75 +do_test func-2.3 {
1.76 + execsql {SELECT substr(t1,-1,1) FROM tbl1 ORDER BY t1}
1.77 +} {e s m e s}
1.78 +do_test func-2.4 {
1.79 + execsql {SELECT substr(t1,-1,2) FROM tbl1 ORDER BY t1}
1.80 +} {e s m e s}
1.81 +do_test func-2.5 {
1.82 + execsql {SELECT substr(t1,-2,1) FROM tbl1 ORDER BY t1}
1.83 +} {e i a r i}
1.84 +do_test func-2.6 {
1.85 + execsql {SELECT substr(t1,-2,2) FROM tbl1 ORDER BY t1}
1.86 +} {ee is am re is}
1.87 +do_test func-2.7 {
1.88 + execsql {SELECT substr(t1,-4,2) FROM tbl1 ORDER BY t1}
1.89 +} {fr {} gr wa th}
1.90 +do_test func-2.8 {
1.91 + execsql {SELECT t1 FROM tbl1 ORDER BY substr(t1,2,20)}
1.92 +} {this software free program is}
1.93 +do_test func-2.9 {
1.94 + execsql {SELECT substr(a,1,1) FROM t2}
1.95 +} {1 {} 3 {} 6}
1.96 +do_test func-2.10 {
1.97 + execsql {SELECT substr(a,2,2) FROM t2}
1.98 +} {{} {} 45 {} 78}
1.99 +
1.100 +# Only do the following tests if TCL has UTF-8 capabilities
1.101 +#
1.102 +if {"\u1234"!="u1234"} {
1.103 +
1.104 +# Put some UTF-8 characters in the database
1.105 +#
1.106 +do_test func-3.0 {
1.107 + execsql {DELETE FROM tbl1}
1.108 + foreach word "contains UTF-8 characters hi\u1234ho" {
1.109 + execsql "INSERT INTO tbl1 VALUES('$word')"
1.110 + }
1.111 + execsql {SELECT t1 FROM tbl1 ORDER BY t1}
1.112 +} "UTF-8 characters contains hi\u1234ho"
1.113 +do_test func-3.1 {
1.114 + execsql {SELECT length(t1) FROM tbl1 ORDER BY t1}
1.115 +} {5 10 8 5}
1.116 +do_test func-3.2 {
1.117 + execsql {SELECT substr(t1,1,2) FROM tbl1 ORDER BY t1}
1.118 +} {UT ch co hi}
1.119 +do_test func-3.3 {
1.120 + execsql {SELECT substr(t1,1,3) FROM tbl1 ORDER BY t1}
1.121 +} "UTF cha con hi\u1234"
1.122 +do_test func-3.4 {
1.123 + execsql {SELECT substr(t1,2,2) FROM tbl1 ORDER BY t1}
1.124 +} "TF ha on i\u1234"
1.125 +do_test func-3.5 {
1.126 + execsql {SELECT substr(t1,2,3) FROM tbl1 ORDER BY t1}
1.127 +} "TF- har ont i\u1234h"
1.128 +do_test func-3.6 {
1.129 + execsql {SELECT substr(t1,3,2) FROM tbl1 ORDER BY t1}
1.130 +} "F- ar nt \u1234h"
1.131 +do_test func-3.7 {
1.132 + execsql {SELECT substr(t1,4,2) FROM tbl1 ORDER BY t1}
1.133 +} "-8 ra ta ho"
1.134 +do_test func-3.8 {
1.135 + execsql {SELECT substr(t1,-1,1) FROM tbl1 ORDER BY t1}
1.136 +} "8 s s o"
1.137 +do_test func-3.9 {
1.138 + execsql {SELECT substr(t1,-3,2) FROM tbl1 ORDER BY t1}
1.139 +} "F- er in \u1234h"
1.140 +do_test func-3.10 {
1.141 + execsql {SELECT substr(t1,-4,3) FROM tbl1 ORDER BY t1}
1.142 +} "TF- ter ain i\u1234h"
1.143 +do_test func-3.99 {
1.144 + execsql {DELETE FROM tbl1}
1.145 + foreach word {this program is free software} {
1.146 + execsql "INSERT INTO tbl1 VALUES('$word')"
1.147 + }
1.148 + execsql {SELECT t1 FROM tbl1}
1.149 +} {this program is free software}
1.150 +
1.151 +} ;# End \u1234!=u1234
1.152 +
1.153 +# Test the abs() and round() functions.
1.154 +#
1.155 +do_test func-4.1 {
1.156 + execsql {
1.157 + CREATE TABLE t1(a,b,c);
1.158 + INSERT INTO t1 VALUES(1,2,3);
1.159 + INSERT INTO t1 VALUES(2,1.2345678901234,-12345.67890);
1.160 + INSERT INTO t1 VALUES(3,-2,-5);
1.161 + }
1.162 + catchsql {SELECT abs(a,b) FROM t1}
1.163 +} {1 {wrong number of arguments to function abs()}}
1.164 +do_test func-4.2 {
1.165 + catchsql {SELECT abs() FROM t1}
1.166 +} {1 {wrong number of arguments to function abs()}}
1.167 +do_test func-4.3 {
1.168 + catchsql {SELECT abs(b) FROM t1 ORDER BY a}
1.169 +} {0 {2 1.2345678901234 2}}
1.170 +do_test func-4.4 {
1.171 + catchsql {SELECT abs(c) FROM t1 ORDER BY a}
1.172 +} {0 {3 12345.6789 5}}
1.173 +do_test func-4.4.1 {
1.174 + execsql {SELECT abs(a) FROM t2}
1.175 +} {1 {} 345 {} 67890}
1.176 +do_test func-4.4.2 {
1.177 + execsql {SELECT abs(t1) FROM tbl1}
1.178 +} {0.0 0.0 0.0 0.0 0.0}
1.179 +
1.180 +do_test func-4.5 {
1.181 + catchsql {SELECT round(a,b,c) FROM t1}
1.182 +} {1 {wrong number of arguments to function round()}}
1.183 +do_test func-4.6 {
1.184 + catchsql {SELECT round(b,2) FROM t1 ORDER BY b}
1.185 +} {0 {-2.0 1.23 2.0}}
1.186 +do_test func-4.7 {
1.187 + catchsql {SELECT round(b,0) FROM t1 ORDER BY a}
1.188 +} {0 {2.0 1.0 -2.0}}
1.189 +do_test func-4.8 {
1.190 + catchsql {SELECT round(c) FROM t1 ORDER BY a}
1.191 +} {0 {3.0 -12346.0 -5.0}}
1.192 +do_test func-4.9 {
1.193 + catchsql {SELECT round(c,a) FROM t1 ORDER BY a}
1.194 +} {0 {3.0 -12345.68 -5.0}}
1.195 +do_test func-4.10 {
1.196 + catchsql {SELECT 'x' || round(c,a) || 'y' FROM t1 ORDER BY a}
1.197 +} {0 {x3.0y x-12345.68y x-5.0y}}
1.198 +do_test func-4.11 {
1.199 + catchsql {SELECT round() FROM t1 ORDER BY a}
1.200 +} {1 {wrong number of arguments to function round()}}
1.201 +do_test func-4.12 {
1.202 + execsql {SELECT coalesce(round(a,2),'nil') FROM t2}
1.203 +} {1.0 nil 345.0 nil 67890.0}
1.204 +do_test func-4.13 {
1.205 + execsql {SELECT round(t1,2) FROM tbl1}
1.206 +} {0.0 0.0 0.0 0.0 0.0}
1.207 +do_test func-4.14 {
1.208 + execsql {SELECT typeof(round(5.1,1));}
1.209 +} {real}
1.210 +do_test func-4.15 {
1.211 + execsql {SELECT typeof(round(5.1));}
1.212 +} {real}
1.213 +
1.214 +
1.215 +# Test the upper() and lower() functions
1.216 +#
1.217 +do_test func-5.1 {
1.218 + execsql {SELECT upper(t1) FROM tbl1}
1.219 +} {THIS PROGRAM IS FREE SOFTWARE}
1.220 +do_test func-5.2 {
1.221 + execsql {SELECT lower(upper(t1)) FROM tbl1}
1.222 +} {this program is free software}
1.223 +do_test func-5.3 {
1.224 + execsql {SELECT upper(a), lower(a) FROM t2}
1.225 +} {1 1 {} {} 345 345 {} {} 67890 67890}
1.226 +ifcapable !icu {
1.227 + do_test func-5.4 {
1.228 + catchsql {SELECT upper(a,5) FROM t2}
1.229 + } {1 {wrong number of arguments to function upper()}}
1.230 +}
1.231 +do_test func-5.5 {
1.232 + catchsql {SELECT upper(*) FROM t2}
1.233 +} {1 {wrong number of arguments to function upper()}}
1.234 +
1.235 +# Test the coalesce() and nullif() functions
1.236 +#
1.237 +do_test func-6.1 {
1.238 + execsql {SELECT coalesce(a,'xyz') FROM t2}
1.239 +} {1 xyz 345 xyz 67890}
1.240 +do_test func-6.2 {
1.241 + execsql {SELECT coalesce(upper(a),'nil') FROM t2}
1.242 +} {1 nil 345 nil 67890}
1.243 +do_test func-6.3 {
1.244 + execsql {SELECT coalesce(nullif(1,1),'nil')}
1.245 +} {nil}
1.246 +do_test func-6.4 {
1.247 + execsql {SELECT coalesce(nullif(1,2),'nil')}
1.248 +} {1}
1.249 +do_test func-6.5 {
1.250 + execsql {SELECT coalesce(nullif(1,NULL),'nil')}
1.251 +} {1}
1.252 +
1.253 +
1.254 +# Test the last_insert_rowid() function
1.255 +#
1.256 +do_test func-7.1 {
1.257 + execsql {SELECT last_insert_rowid()}
1.258 +} [db last_insert_rowid]
1.259 +
1.260 +# Tests for aggregate functions and how they handle NULLs.
1.261 +#
1.262 +do_test func-8.1 {
1.263 + ifcapable explain {
1.264 + execsql {EXPLAIN SELECT sum(a) FROM t2;}
1.265 + }
1.266 + execsql {
1.267 + SELECT sum(a), count(a), round(avg(a),2), min(a), max(a), count(*) FROM t2;
1.268 + }
1.269 +} {68236 3 22745.33 1 67890 5}
1.270 +do_test func-8.2 {
1.271 + execsql {
1.272 + SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t2;
1.273 + }
1.274 +} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
1.275 +
1.276 +ifcapable tempdb {
1.277 + do_test func-8.3 {
1.278 + execsql {
1.279 + CREATE TEMP TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;
1.280 + SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
1.281 + }
1.282 + } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
1.283 +} else {
1.284 + do_test func-8.3 {
1.285 + execsql {
1.286 + CREATE TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;
1.287 + SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
1.288 + }
1.289 + } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
1.290 +}
1.291 +do_test func-8.4 {
1.292 + execsql {
1.293 + SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
1.294 + }
1.295 +} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
1.296 +ifcapable compound {
1.297 + do_test func-8.5 {
1.298 + execsql {
1.299 + SELECT sum(x) FROM (SELECT '9223372036' || '854775807' AS x
1.300 + UNION ALL SELECT -9223372036854775807)
1.301 + }
1.302 + } {0}
1.303 + do_test func-8.6 {
1.304 + execsql {
1.305 + SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775807' AS x
1.306 + UNION ALL SELECT -9223372036854775807)
1.307 + }
1.308 + } {integer}
1.309 + do_test func-8.7 {
1.310 + execsql {
1.311 + SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775808' AS x
1.312 + UNION ALL SELECT -9223372036854775807)
1.313 + }
1.314 + } {real}
1.315 + do_test func-8.8 {
1.316 + execsql {
1.317 + SELECT sum(x)>0.0 FROM (SELECT '9223372036' || '854775808' AS x
1.318 + UNION ALL SELECT -9223372036850000000)
1.319 + }
1.320 + } {1}
1.321 +}
1.322 +
1.323 +# How do you test the random() function in a meaningful, deterministic way?
1.324 +#
1.325 +do_test func-9.1 {
1.326 + execsql {
1.327 + SELECT random() is not null;
1.328 + }
1.329 +} {1}
1.330 +do_test func-9.2 {
1.331 + execsql {
1.332 + SELECT typeof(random());
1.333 + }
1.334 +} {integer}
1.335 +do_test func-9.3 {
1.336 + execsql {
1.337 + SELECT randomblob(32) is not null;
1.338 + }
1.339 +} {1}
1.340 +do_test func-9.4 {
1.341 + execsql {
1.342 + SELECT typeof(randomblob(32));
1.343 + }
1.344 +} {blob}
1.345 +do_test func-9.5 {
1.346 + execsql {
1.347 + SELECT length(randomblob(32)), length(randomblob(-5)),
1.348 + length(randomblob(2000))
1.349 + }
1.350 +} {32 1 2000}
1.351 +
1.352 +# The "hex()" function was added in order to be able to render blobs
1.353 +# generated by randomblob(). So this seems like a good place to test
1.354 +# hex().
1.355 +#
1.356 +ifcapable bloblit {
1.357 + do_test func-9.10 {
1.358 + execsql {SELECT hex(x'00112233445566778899aAbBcCdDeEfF')}
1.359 + } {00112233445566778899AABBCCDDEEFF}
1.360 +}
1.361 +set encoding [db one {PRAGMA encoding}]
1.362 +if {$encoding=="UTF-16le"} {
1.363 + do_test func-9.11-utf16le {
1.364 + execsql {SELECT hex(replace('abcdefg','ef','12'))}
1.365 + } {6100620063006400310032006700}
1.366 + do_test func-9.12-utf16le {
1.367 + execsql {SELECT hex(replace('abcdefg','','12'))}
1.368 + } {{}}
1.369 + do_test func-9.13-utf16le {
1.370 + execsql {SELECT hex(replace('aabcdefg','a','aaa'))}
1.371 + } {610061006100610061006100620063006400650066006700}
1.372 +} elseif {$encoding=="UTF-8"} {
1.373 + do_test func-9.11-utf8 {
1.374 + execsql {SELECT hex(replace('abcdefg','ef','12'))}
1.375 + } {61626364313267}
1.376 + do_test func-9.12-utf8 {
1.377 + execsql {SELECT hex(replace('abcdefg','','12'))}
1.378 + } {{}}
1.379 + do_test func-9.13-utf8 {
1.380 + execsql {SELECT hex(replace('aabcdefg','a','aaa'))}
1.381 + } {616161616161626364656667}
1.382 +}
1.383 +
1.384 +# Use the "sqlite_register_test_function" TCL command which is part of
1.385 +# the text fixture in order to verify correct operation of some of
1.386 +# the user-defined SQL function APIs that are not used by the built-in
1.387 +# functions.
1.388 +#
1.389 +set ::DB [sqlite3_connection_pointer db]
1.390 +sqlite_register_test_function $::DB testfunc
1.391 +do_test func-10.1 {
1.392 + catchsql {
1.393 + SELECT testfunc(NULL,NULL);
1.394 + }
1.395 +} {1 {first argument should be one of: int int64 string double null value}}
1.396 +do_test func-10.2 {
1.397 + execsql {
1.398 + SELECT testfunc(
1.399 + 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
1.400 + 'int', 1234
1.401 + );
1.402 + }
1.403 +} {1234}
1.404 +do_test func-10.3 {
1.405 + execsql {
1.406 + SELECT testfunc(
1.407 + 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
1.408 + 'string', NULL
1.409 + );
1.410 + }
1.411 +} {{}}
1.412 +do_test func-10.4 {
1.413 + execsql {
1.414 + SELECT testfunc(
1.415 + 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
1.416 + 'double', 1.234
1.417 + );
1.418 + }
1.419 +} {1.234}
1.420 +do_test func-10.5 {
1.421 + execsql {
1.422 + SELECT testfunc(
1.423 + 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
1.424 + 'int', 1234,
1.425 + 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
1.426 + 'string', NULL,
1.427 + 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
1.428 + 'double', 1.234,
1.429 + 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
1.430 + 'int', 1234,
1.431 + 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
1.432 + 'string', NULL,
1.433 + 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
1.434 + 'double', 1.234
1.435 + );
1.436 + }
1.437 +} {1.234}
1.438 +
1.439 +# Test the built-in sqlite_version(*) SQL function.
1.440 +#
1.441 +do_test func-11.1 {
1.442 + execsql {
1.443 + SELECT sqlite_version(*);
1.444 + }
1.445 +} [sqlite3 -version]
1.446 +
1.447 +# Test that destructors passed to sqlite3 by calls to sqlite3_result_text()
1.448 +# etc. are called. These tests use two special user-defined functions
1.449 +# (implemented in func.c) only available in test builds.
1.450 +#
1.451 +# Function test_destructor() takes one argument and returns a copy of the
1.452 +# text form of that argument. A destructor is associated with the return
1.453 +# value. Function test_destructor_count() returns the number of outstanding
1.454 +# destructor calls for values returned by test_destructor().
1.455 +#
1.456 +if {[db eval {PRAGMA encoding}]=="UTF-8"} {
1.457 + do_test func-12.1-utf8 {
1.458 + execsql {
1.459 + SELECT test_destructor('hello world'), test_destructor_count();
1.460 + }
1.461 + } {{hello world} 1}
1.462 +} else {
1.463 + ifcapable {utf16} {
1.464 + do_test func-12.1-utf16 {
1.465 + execsql {
1.466 + SELECT test_destructor16('hello world'), test_destructor_count();
1.467 + }
1.468 + } {{hello world} 1}
1.469 + }
1.470 +}
1.471 +do_test func-12.2 {
1.472 + execsql {
1.473 + SELECT test_destructor_count();
1.474 + }
1.475 +} {0}
1.476 +do_test func-12.3 {
1.477 + execsql {
1.478 + SELECT test_destructor('hello')||' world'
1.479 + }
1.480 +} {{hello world}}
1.481 +do_test func-12.4 {
1.482 + execsql {
1.483 + SELECT test_destructor_count();
1.484 + }
1.485 +} {0}
1.486 +do_test func-12.5 {
1.487 + execsql {
1.488 + CREATE TABLE t4(x);
1.489 + INSERT INTO t4 VALUES(test_destructor('hello'));
1.490 + INSERT INTO t4 VALUES(test_destructor('world'));
1.491 + SELECT min(test_destructor(x)), max(test_destructor(x)) FROM t4;
1.492 + }
1.493 +} {hello world}
1.494 +do_test func-12.6 {
1.495 + execsql {
1.496 + SELECT test_destructor_count();
1.497 + }
1.498 +} {0}
1.499 +do_test func-12.7 {
1.500 + execsql {
1.501 + DROP TABLE t4;
1.502 + }
1.503 +} {}
1.504 +
1.505 +
1.506 +# Test that the auxdata API for scalar functions works. This test uses
1.507 +# a special user-defined function only available in test builds,
1.508 +# test_auxdata(). Function test_auxdata() takes any number of arguments.
1.509 +do_test func-13.1 {
1.510 + execsql {
1.511 + SELECT test_auxdata('hello world');
1.512 + }
1.513 +} {0}
1.514 +
1.515 +do_test func-13.2 {
1.516 + execsql {
1.517 + CREATE TABLE t4(a, b);
1.518 + INSERT INTO t4 VALUES('abc', 'def');
1.519 + INSERT INTO t4 VALUES('ghi', 'jkl');
1.520 + }
1.521 +} {}
1.522 +do_test func-13.3 {
1.523 + execsql {
1.524 + SELECT test_auxdata('hello world') FROM t4;
1.525 + }
1.526 +} {0 1}
1.527 +do_test func-13.4 {
1.528 + execsql {
1.529 + SELECT test_auxdata('hello world', 123) FROM t4;
1.530 + }
1.531 +} {{0 0} {1 1}}
1.532 +do_test func-13.5 {
1.533 + execsql {
1.534 + SELECT test_auxdata('hello world', a) FROM t4;
1.535 + }
1.536 +} {{0 0} {1 0}}
1.537 +do_test func-13.6 {
1.538 + execsql {
1.539 + SELECT test_auxdata('hello'||'world', a) FROM t4;
1.540 + }
1.541 +} {{0 0} {1 0}}
1.542 +
1.543 +# Test that auxilary data is preserved between calls for SQL variables.
1.544 +do_test func-13.7 {
1.545 + set DB [sqlite3_connection_pointer db]
1.546 + set sql "SELECT test_auxdata( ? , a ) FROM t4;"
1.547 + set STMT [sqlite3_prepare $DB $sql -1 TAIL]
1.548 + sqlite3_bind_text $STMT 1 hello\000 -1
1.549 + set res [list]
1.550 + while { "SQLITE_ROW"==[sqlite3_step $STMT] } {
1.551 + lappend res [sqlite3_column_text $STMT 0]
1.552 + }
1.553 + lappend res [sqlite3_finalize $STMT]
1.554 +} {{0 0} {1 0} SQLITE_OK}
1.555 +
1.556 +# Make sure that a function with a very long name is rejected
1.557 +do_test func-14.1 {
1.558 + catch {
1.559 + db function [string repeat X 254] {return "hello"}
1.560 + }
1.561 +} {0}
1.562 +do_test func-14.2 {
1.563 + catch {
1.564 + db function [string repeat X 256] {return "hello"}
1.565 + }
1.566 +} {1}
1.567 +
1.568 +do_test func-15.1 {
1.569 + catchsql {select test_error(NULL)}
1.570 +} {1 {}}
1.571 +do_test func-15.2 {
1.572 + catchsql {select test_error('this is the error message')}
1.573 +} {1 {this is the error message}}
1.574 +do_test func-15.3 {
1.575 + catchsql {select test_error('this is the error message',12)}
1.576 +} {1 {this is the error message}}
1.577 +do_test func-15.4 {
1.578 + db errorcode
1.579 +} {12}
1.580 +
1.581 +# Test the quote function for BLOB and NULL values.
1.582 +do_test func-16.1 {
1.583 + execsql {
1.584 + CREATE TABLE tbl2(a, b);
1.585 + }
1.586 + set STMT [sqlite3_prepare $::DB "INSERT INTO tbl2 VALUES(?, ?)" -1 TAIL]
1.587 + sqlite3_bind_blob $::STMT 1 abc 3
1.588 + sqlite3_step $::STMT
1.589 + sqlite3_finalize $::STMT
1.590 + execsql {
1.591 + SELECT quote(a), quote(b) FROM tbl2;
1.592 + }
1.593 +} {X'616263' NULL}
1.594 +
1.595 +# Correctly handle function error messages that include %. Ticket #1354
1.596 +#
1.597 +do_test func-17.1 {
1.598 + proc testfunc1 args {error "Error %d with %s percents %p"}
1.599 + db function testfunc1 ::testfunc1
1.600 + catchsql {
1.601 + SELECT testfunc1(1,2,3);
1.602 + }
1.603 +} {1 {Error %d with %s percents %p}}
1.604 +
1.605 +# The SUM function should return integer results when all inputs are integer.
1.606 +#
1.607 +do_test func-18.1 {
1.608 + execsql {
1.609 + CREATE TABLE t5(x);
1.610 + INSERT INTO t5 VALUES(1);
1.611 + INSERT INTO t5 VALUES(-99);
1.612 + INSERT INTO t5 VALUES(10000);
1.613 + SELECT sum(x) FROM t5;
1.614 + }
1.615 +} {9902}
1.616 +do_test func-18.2 {
1.617 + execsql {
1.618 + INSERT INTO t5 VALUES(0.0);
1.619 + SELECT sum(x) FROM t5;
1.620 + }
1.621 +} {9902.0}
1.622 +
1.623 +# The sum of nothing is NULL. But the sum of all NULLs is NULL.
1.624 +#
1.625 +# The TOTAL of nothing is 0.0.
1.626 +#
1.627 +do_test func-18.3 {
1.628 + execsql {
1.629 + DELETE FROM t5;
1.630 + SELECT sum(x), total(x) FROM t5;
1.631 + }
1.632 +} {{} 0.0}
1.633 +do_test func-18.4 {
1.634 + execsql {
1.635 + INSERT INTO t5 VALUES(NULL);
1.636 + SELECT sum(x), total(x) FROM t5
1.637 + }
1.638 +} {{} 0.0}
1.639 +do_test func-18.5 {
1.640 + execsql {
1.641 + INSERT INTO t5 VALUES(NULL);
1.642 + SELECT sum(x), total(x) FROM t5
1.643 + }
1.644 +} {{} 0.0}
1.645 +do_test func-18.6 {
1.646 + execsql {
1.647 + INSERT INTO t5 VALUES(123);
1.648 + SELECT sum(x), total(x) FROM t5
1.649 + }
1.650 +} {123 123.0}
1.651 +
1.652 +# Ticket #1664, #1669, #1670, #1674: An integer overflow on SUM causes
1.653 +# an error. The non-standard TOTAL() function continues to give a helpful
1.654 +# result.
1.655 +#
1.656 +do_test func-18.10 {
1.657 + execsql {
1.658 + CREATE TABLE t6(x INTEGER);
1.659 + INSERT INTO t6 VALUES(1);
1.660 + INSERT INTO t6 VALUES(1<<62);
1.661 + SELECT sum(x) - ((1<<62)+1) from t6;
1.662 + }
1.663 +} 0
1.664 +do_test func-18.11 {
1.665 + execsql {
1.666 + SELECT typeof(sum(x)) FROM t6
1.667 + }
1.668 +} integer
1.669 +do_test func-18.12 {
1.670 + catchsql {
1.671 + INSERT INTO t6 VALUES(1<<62);
1.672 + SELECT sum(x) - ((1<<62)*2.0+1) from t6;
1.673 + }
1.674 +} {1 {integer overflow}}
1.675 +do_test func-18.13 {
1.676 + execsql {
1.677 + SELECT total(x) - ((1<<62)*2.0+1) FROM t6
1.678 + }
1.679 +} 0.0
1.680 +do_test func-18.14 {
1.681 + execsql {
1.682 + SELECT sum(-9223372036854775805);
1.683 + }
1.684 +} -9223372036854775805
1.685 +
1.686 +ifcapable compound&&subquery {
1.687 +
1.688 +do_test func-18.15 {
1.689 + catchsql {
1.690 + SELECT sum(x) FROM
1.691 + (SELECT 9223372036854775807 AS x UNION ALL
1.692 + SELECT 10 AS x);
1.693 + }
1.694 +} {1 {integer overflow}}
1.695 +do_test func-18.16 {
1.696 + catchsql {
1.697 + SELECT sum(x) FROM
1.698 + (SELECT 9223372036854775807 AS x UNION ALL
1.699 + SELECT -10 AS x);
1.700 + }
1.701 +} {0 9223372036854775797}
1.702 +do_test func-18.17 {
1.703 + catchsql {
1.704 + SELECT sum(x) FROM
1.705 + (SELECT -9223372036854775807 AS x UNION ALL
1.706 + SELECT 10 AS x);
1.707 + }
1.708 +} {0 -9223372036854775797}
1.709 +do_test func-18.18 {
1.710 + catchsql {
1.711 + SELECT sum(x) FROM
1.712 + (SELECT -9223372036854775807 AS x UNION ALL
1.713 + SELECT -10 AS x);
1.714 + }
1.715 +} {1 {integer overflow}}
1.716 +do_test func-18.19 {
1.717 + catchsql {
1.718 + SELECT sum(x) FROM (SELECT 9 AS x UNION ALL SELECT -10 AS x);
1.719 + }
1.720 +} {0 -1}
1.721 +do_test func-18.20 {
1.722 + catchsql {
1.723 + SELECT sum(x) FROM (SELECT -9 AS x UNION ALL SELECT 10 AS x);
1.724 + }
1.725 +} {0 1}
1.726 +do_test func-18.21 {
1.727 + catchsql {
1.728 + SELECT sum(x) FROM (SELECT -10 AS x UNION ALL SELECT 9 AS x);
1.729 + }
1.730 +} {0 -1}
1.731 +do_test func-18.22 {
1.732 + catchsql {
1.733 + SELECT sum(x) FROM (SELECT 10 AS x UNION ALL SELECT -9 AS x);
1.734 + }
1.735 +} {0 1}
1.736 +
1.737 +} ;# ifcapable compound&&subquery
1.738 +
1.739 +# Integer overflow on abs()
1.740 +#
1.741 +do_test func-18.31 {
1.742 + catchsql {
1.743 + SELECT abs(-9223372036854775807);
1.744 + }
1.745 +} {0 9223372036854775807}
1.746 +do_test func-18.32 {
1.747 + catchsql {
1.748 + SELECT abs(-9223372036854775807-1);
1.749 + }
1.750 +} {1 {integer overflow}}
1.751 +
1.752 +# The MATCH function exists but is only a stub and always throws an error.
1.753 +#
1.754 +do_test func-19.1 {
1.755 + execsql {
1.756 + SELECT match(a,b) FROM t1 WHERE 0;
1.757 + }
1.758 +} {}
1.759 +do_test func-19.2 {
1.760 + catchsql {
1.761 + SELECT 'abc' MATCH 'xyz';
1.762 + }
1.763 +} {1 {unable to use function MATCH in the requested context}}
1.764 +do_test func-19.3 {
1.765 + catchsql {
1.766 + SELECT 'abc' NOT MATCH 'xyz';
1.767 + }
1.768 +} {1 {unable to use function MATCH in the requested context}}
1.769 +do_test func-19.4 {
1.770 + catchsql {
1.771 + SELECT match(1,2,3);
1.772 + }
1.773 +} {1 {wrong number of arguments to function match()}}
1.774 +
1.775 +# Soundex tests.
1.776 +#
1.777 +if {![catch {db eval {SELECT soundex('hello')}}]} {
1.778 + set i 0
1.779 + foreach {name sdx} {
1.780 + euler E460
1.781 + EULER E460
1.782 + Euler E460
1.783 + ellery E460
1.784 + gauss G200
1.785 + ghosh G200
1.786 + hilbert H416
1.787 + Heilbronn H416
1.788 + knuth K530
1.789 + kant K530
1.790 + Lloyd L300
1.791 + LADD L300
1.792 + Lukasiewicz L222
1.793 + Lissajous L222
1.794 + A A000
1.795 + 12345 ?000
1.796 + } {
1.797 + incr i
1.798 + do_test func-20.$i {
1.799 + execsql {SELECT soundex($name)}
1.800 + } $sdx
1.801 + }
1.802 +}
1.803 +
1.804 +# Tests of the REPLACE function.
1.805 +#
1.806 +do_test func-21.1 {
1.807 + catchsql {
1.808 + SELECT replace(1,2);
1.809 + }
1.810 +} {1 {wrong number of arguments to function replace()}}
1.811 +do_test func-21.2 {
1.812 + catchsql {
1.813 + SELECT replace(1,2,3,4);
1.814 + }
1.815 +} {1 {wrong number of arguments to function replace()}}
1.816 +do_test func-21.3 {
1.817 + execsql {
1.818 + SELECT typeof(replace("This is the main test string", NULL, "ALT"));
1.819 + }
1.820 +} {null}
1.821 +do_test func-21.4 {
1.822 + execsql {
1.823 + SELECT typeof(replace(NULL, "main", "ALT"));
1.824 + }
1.825 +} {null}
1.826 +do_test func-21.5 {
1.827 + execsql {
1.828 + SELECT typeof(replace("This is the main test string", "main", NULL));
1.829 + }
1.830 +} {null}
1.831 +do_test func-21.6 {
1.832 + execsql {
1.833 + SELECT replace("This is the main test string", "main", "ALT");
1.834 + }
1.835 +} {{This is the ALT test string}}
1.836 +do_test func-21.7 {
1.837 + execsql {
1.838 + SELECT replace("This is the main test string", "main", "larger-main");
1.839 + }
1.840 +} {{This is the larger-main test string}}
1.841 +do_test func-21.8 {
1.842 + execsql {
1.843 + SELECT replace("aaaaaaa", "a", "0123456789");
1.844 + }
1.845 +} {0123456789012345678901234567890123456789012345678901234567890123456789}
1.846 +
1.847 +ifcapable tclvar {
1.848 + do_test func-21.9 {
1.849 + # Attempt to exploit a buffer-overflow that at one time existed
1.850 + # in the REPLACE function.
1.851 + set ::str "[string repeat A 29998]CC[string repeat A 35537]"
1.852 + set ::rep [string repeat B 65536]
1.853 + execsql {
1.854 + SELECT LENGTH(REPLACE($::str, 'C', $::rep));
1.855 + }
1.856 + } [expr 29998 + 2*65536 + 35537]
1.857 +}
1.858 +
1.859 +# Tests for the TRIM, LTRIM and RTRIM functions.
1.860 +#
1.861 +do_test func-22.1 {
1.862 + catchsql {SELECT trim(1,2,3)}
1.863 +} {1 {wrong number of arguments to function trim()}}
1.864 +do_test func-22.2 {
1.865 + catchsql {SELECT ltrim(1,2,3)}
1.866 +} {1 {wrong number of arguments to function ltrim()}}
1.867 +do_test func-22.3 {
1.868 + catchsql {SELECT rtrim(1,2,3)}
1.869 +} {1 {wrong number of arguments to function rtrim()}}
1.870 +do_test func-22.4 {
1.871 + execsql {SELECT trim(' hi ');}
1.872 +} {hi}
1.873 +do_test func-22.5 {
1.874 + execsql {SELECT ltrim(' hi ');}
1.875 +} {{hi }}
1.876 +do_test func-22.6 {
1.877 + execsql {SELECT rtrim(' hi ');}
1.878 +} {{ hi}}
1.879 +do_test func-22.7 {
1.880 + execsql {SELECT trim(' hi ','xyz');}
1.881 +} {{ hi }}
1.882 +do_test func-22.8 {
1.883 + execsql {SELECT ltrim(' hi ','xyz');}
1.884 +} {{ hi }}
1.885 +do_test func-22.9 {
1.886 + execsql {SELECT rtrim(' hi ','xyz');}
1.887 +} {{ hi }}
1.888 +do_test func-22.10 {
1.889 + execsql {SELECT trim('xyxzy hi zzzy','xyz');}
1.890 +} {{ hi }}
1.891 +do_test func-22.11 {
1.892 + execsql {SELECT ltrim('xyxzy hi zzzy','xyz');}
1.893 +} {{ hi zzzy}}
1.894 +do_test func-22.12 {
1.895 + execsql {SELECT rtrim('xyxzy hi zzzy','xyz');}
1.896 +} {{xyxzy hi }}
1.897 +do_test func-22.13 {
1.898 + execsql {SELECT trim(' hi ','');}
1.899 +} {{ hi }}
1.900 +if {[db one {PRAGMA encoding}]=="UTF-8"} {
1.901 + do_test func-22.14 {
1.902 + execsql {SELECT hex(trim(x'c280e1bfbff48fbfbf6869',x'6162e1bfbfc280'))}
1.903 + } {F48FBFBF6869}
1.904 + do_test func-22.15 {
1.905 + execsql {SELECT hex(trim(x'6869c280e1bfbff48fbfbf61',
1.906 + x'6162e1bfbfc280f48fbfbf'))}
1.907 + } {6869}
1.908 + do_test func-22.16 {
1.909 + execsql {SELECT hex(trim(x'ceb1ceb2ceb3',x'ceb1'));}
1.910 + } {CEB2CEB3}
1.911 +}
1.912 +do_test func-22.20 {
1.913 + execsql {SELECT typeof(trim(NULL));}
1.914 +} {null}
1.915 +do_test func-22.21 {
1.916 + execsql {SELECT typeof(trim(NULL,'xyz'));}
1.917 +} {null}
1.918 +do_test func-22.22 {
1.919 + execsql {SELECT typeof(trim('hello',NULL));}
1.920 +} {null}
1.921 +
1.922 +# This is to test the deprecated sqlite3_aggregate_count() API.
1.923 +#
1.924 +do_test func-23.1 {
1.925 + sqlite3_create_aggregate db
1.926 + execsql {
1.927 + SELECT legacy_count() FROM t6;
1.928 + }
1.929 +} {3}
1.930 +
1.931 +# The group_concat() function.
1.932 +#
1.933 +do_test func-24.1 {
1.934 + execsql {
1.935 + SELECT group_concat(t1) FROM tbl1
1.936 + }
1.937 +} {this,program,is,free,software}
1.938 +do_test func-24.2 {
1.939 + execsql {
1.940 + SELECT group_concat(t1,' ') FROM tbl1
1.941 + }
1.942 +} {{this program is free software}}
1.943 +do_test func-24.3 {
1.944 + execsql {
1.945 + SELECT group_concat(t1,' ' || rowid || ' ') FROM tbl1
1.946 + }
1.947 +} {{this 2 program 3 is 4 free 5 software}}
1.948 +do_test func-24.4 {
1.949 + execsql {
1.950 + SELECT group_concat(NULL,t1) FROM tbl1
1.951 + }
1.952 +} {{}}
1.953 +do_test func-24.5 {
1.954 + execsql {
1.955 + SELECT group_concat(t1,NULL) FROM tbl1
1.956 + }
1.957 +} {thisprogramisfreesoftware}
1.958 +do_test func-24.6 {
1.959 + execsql {
1.960 + SELECT 'BEGIN-'||group_concat(t1) FROM tbl1
1.961 + }
1.962 +} {BEGIN-this,program,is,free,software}
1.963 +unset -nocomplain midargs
1.964 +set midargs {}
1.965 +unset -nocomplain midres
1.966 +set midres {}
1.967 +unset -nocomplain result
1.968 +for {set i 1} {$i<[sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG -1]-1} {incr i} {
1.969 + append midargs ,'/$i'
1.970 + append midres /$i
1.971 + set result \
1.972 + "this$midres:program$midres:is$midres:free$midres:software$midres"
1.973 + set sql "SELECT group_concat(t1$midargs,':') FROM tbl1"
1.974 + do_test func-24.7.$i {
1.975 + db eval $::sql
1.976 + } $result
1.977 +}
1.978 +
1.979 +# Use the test_isolation function to make sure that type conversions
1.980 +# on function arguments do not effect subsequent arguments.
1.981 +#
1.982 +do_test func-25.1 {
1.983 + execsql {SELECT test_isolation(t1,t1) FROM tbl1}
1.984 +} {this program is free software}
1.985 +
1.986 +# Try to misuse the sqlite3_create_function() interface. Verify that
1.987 +# errors are returned.
1.988 +#
1.989 +do_test func-26.1 {
1.990 + abuse_create_function db
1.991 +} {}
1.992 +
1.993 +# The previous test (func-26.1) registered a function with a very long
1.994 +# function name that takes many arguments and always returns NULL. Verify
1.995 +# that this function works correctly.
1.996 +#
1.997 +do_test func-26.2 {
1.998 + set a {}
1.999 + for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG} {incr i} {
1.1000 + lappend a $i
1.1001 + }
1.1002 + db eval "
1.1003 + 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 ,]);
1.1004 + "
1.1005 +} {{}}
1.1006 +do_test func-26.3 {
1.1007 + set a {}
1.1008 + for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG+1} {incr i} {
1.1009 + lappend a $i
1.1010 + }
1.1011 + catchsql "
1.1012 + 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 ,]);
1.1013 + "
1.1014 +} {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}}
1.1015 +do_test func-26.4 {
1.1016 + set a {}
1.1017 + for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG-1} {incr i} {
1.1018 + lappend a $i
1.1019 + }
1.1020 + catchsql "
1.1021 + 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 ,]);
1.1022 + "
1.1023 +} {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()}}
1.1024 +do_test func-26.5 {
1.1025 + catchsql "
1.1026 + 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);
1.1027 + "
1.1028 +} {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}}
1.1029 +do_test func-26.6 {
1.1030 + catchsql "
1.1031 + 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);
1.1032 + "
1.1033 +} {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}}
1.1034 +
1.1035 +finish_test