aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/libraries/sqlite/unix/sqlite-3.5.1/test/func.test
diff options
context:
space:
mode:
Diffstat (limited to 'libraries/sqlite/unix/sqlite-3.5.1/test/func.test')
-rw-r--r--libraries/sqlite/unix/sqlite-3.5.1/test/func.test886
1 files changed, 886 insertions, 0 deletions
diff --git a/libraries/sqlite/unix/sqlite-3.5.1/test/func.test b/libraries/sqlite/unix/sqlite-3.5.1/test/func.test
new file mode 100644
index 0000000..3149c54
--- /dev/null
+++ b/libraries/sqlite/unix/sqlite-3.5.1/test/func.test
@@ -0,0 +1,886 @@
1# 2001 September 15
2#
3# The author disclaims copyright to this source code. In place of
4# a legal notice, here is a blessing:
5#
6# May you do good and not evil.
7# May you find forgiveness for yourself and forgive others.
8# May you share freely, never taking more than you give.
9#
10#***********************************************************************
11# This file implements regression tests for SQLite library. The
12# focus of this file is testing built-in functions.
13#
14# $Id: func.test,v 1.69 2007/09/12 17:01:45 danielk1977 Exp $
15
16set testdir [file dirname $argv0]
17source $testdir/tester.tcl
18
19# Create a table to work with.
20#
21do_test func-0.0 {
22 execsql {CREATE TABLE tbl1(t1 text)}
23 foreach word {this program is free software} {
24 execsql "INSERT INTO tbl1 VALUES('$word')"
25 }
26 execsql {SELECT t1 FROM tbl1 ORDER BY t1}
27} {free is program software this}
28do_test func-0.1 {
29 execsql {
30 CREATE TABLE t2(a);
31 INSERT INTO t2 VALUES(1);
32 INSERT INTO t2 VALUES(NULL);
33 INSERT INTO t2 VALUES(345);
34 INSERT INTO t2 VALUES(NULL);
35 INSERT INTO t2 VALUES(67890);
36 SELECT * FROM t2;
37 }
38} {1 {} 345 {} 67890}
39
40# Check out the length() function
41#
42do_test func-1.0 {
43 execsql {SELECT length(t1) FROM tbl1 ORDER BY t1}
44} {4 2 7 8 4}
45do_test func-1.1 {
46 set r [catch {execsql {SELECT length(*) FROM tbl1 ORDER BY t1}} msg]
47 lappend r $msg
48} {1 {wrong number of arguments to function length()}}
49do_test func-1.2 {
50 set r [catch {execsql {SELECT length(t1,5) FROM tbl1 ORDER BY t1}} msg]
51 lappend r $msg
52} {1 {wrong number of arguments to function length()}}
53do_test func-1.3 {
54 execsql {SELECT length(t1), count(*) FROM tbl1 GROUP BY length(t1)
55 ORDER BY length(t1)}
56} {2 1 4 2 7 1 8 1}
57do_test func-1.4 {
58 execsql {SELECT coalesce(length(a),-1) FROM t2}
59} {1 -1 3 -1 5}
60
61# Check out the substr() function
62#
63do_test func-2.0 {
64 execsql {SELECT substr(t1,1,2) FROM tbl1 ORDER BY t1}
65} {fr is pr so th}
66do_test func-2.1 {
67 execsql {SELECT substr(t1,2,1) FROM tbl1 ORDER BY t1}
68} {r s r o h}
69do_test func-2.2 {
70 execsql {SELECT substr(t1,3,3) FROM tbl1 ORDER BY t1}
71} {ee {} ogr ftw is}
72do_test func-2.3 {
73 execsql {SELECT substr(t1,-1,1) FROM tbl1 ORDER BY t1}
74} {e s m e s}
75do_test func-2.4 {
76 execsql {SELECT substr(t1,-1,2) FROM tbl1 ORDER BY t1}
77} {e s m e s}
78do_test func-2.5 {
79 execsql {SELECT substr(t1,-2,1) FROM tbl1 ORDER BY t1}
80} {e i a r i}
81do_test func-2.6 {
82 execsql {SELECT substr(t1,-2,2) FROM tbl1 ORDER BY t1}
83} {ee is am re is}
84do_test func-2.7 {
85 execsql {SELECT substr(t1,-4,2) FROM tbl1 ORDER BY t1}
86} {fr {} gr wa th}
87do_test func-2.8 {
88 execsql {SELECT t1 FROM tbl1 ORDER BY substr(t1,2,20)}
89} {this software free program is}
90do_test func-2.9 {
91 execsql {SELECT substr(a,1,1) FROM t2}
92} {1 {} 3 {} 6}
93do_test func-2.10 {
94 execsql {SELECT substr(a,2,2) FROM t2}
95} {{} {} 45 {} 78}
96
97# Only do the following tests if TCL has UTF-8 capabilities
98#
99if {"\u1234"!="u1234"} {
100
101# Put some UTF-8 characters in the database
102#
103do_test func-3.0 {
104 execsql {DELETE FROM tbl1}
105 foreach word "contains UTF-8 characters hi\u1234ho" {
106 execsql "INSERT INTO tbl1 VALUES('$word')"
107 }
108 execsql {SELECT t1 FROM tbl1 ORDER BY t1}
109} "UTF-8 characters contains hi\u1234ho"
110do_test func-3.1 {
111 execsql {SELECT length(t1) FROM tbl1 ORDER BY t1}
112} {5 10 8 5}
113do_test func-3.2 {
114 execsql {SELECT substr(t1,1,2) FROM tbl1 ORDER BY t1}
115} {UT ch co hi}
116do_test func-3.3 {
117 execsql {SELECT substr(t1,1,3) FROM tbl1 ORDER BY t1}
118} "UTF cha con hi\u1234"
119do_test func-3.4 {
120 execsql {SELECT substr(t1,2,2) FROM tbl1 ORDER BY t1}
121} "TF ha on i\u1234"
122do_test func-3.5 {
123 execsql {SELECT substr(t1,2,3) FROM tbl1 ORDER BY t1}
124} "TF- har ont i\u1234h"
125do_test func-3.6 {
126 execsql {SELECT substr(t1,3,2) FROM tbl1 ORDER BY t1}
127} "F- ar nt \u1234h"
128do_test func-3.7 {
129 execsql {SELECT substr(t1,4,2) FROM tbl1 ORDER BY t1}
130} "-8 ra ta ho"
131do_test func-3.8 {
132 execsql {SELECT substr(t1,-1,1) FROM tbl1 ORDER BY t1}
133} "8 s s o"
134do_test func-3.9 {
135 execsql {SELECT substr(t1,-3,2) FROM tbl1 ORDER BY t1}
136} "F- er in \u1234h"
137do_test func-3.10 {
138 execsql {SELECT substr(t1,-4,3) FROM tbl1 ORDER BY t1}
139} "TF- ter ain i\u1234h"
140do_test func-3.99 {
141 execsql {DELETE FROM tbl1}
142 foreach word {this program is free software} {
143 execsql "INSERT INTO tbl1 VALUES('$word')"
144 }
145 execsql {SELECT t1 FROM tbl1}
146} {this program is free software}
147
148} ;# End \u1234!=u1234
149
150# Test the abs() and round() functions.
151#
152do_test func-4.1 {
153 execsql {
154 CREATE TABLE t1(a,b,c);
155 INSERT INTO t1 VALUES(1,2,3);
156 INSERT INTO t1 VALUES(2,1.2345678901234,-12345.67890);
157 INSERT INTO t1 VALUES(3,-2,-5);
158 }
159 catchsql {SELECT abs(a,b) FROM t1}
160} {1 {wrong number of arguments to function abs()}}
161do_test func-4.2 {
162 catchsql {SELECT abs() FROM t1}
163} {1 {wrong number of arguments to function abs()}}
164do_test func-4.3 {
165 catchsql {SELECT abs(b) FROM t1 ORDER BY a}
166} {0 {2 1.2345678901234 2}}
167do_test func-4.4 {
168 catchsql {SELECT abs(c) FROM t1 ORDER BY a}
169} {0 {3 12345.6789 5}}
170do_test func-4.4.1 {
171 execsql {SELECT abs(a) FROM t2}
172} {1 {} 345 {} 67890}
173do_test func-4.4.2 {
174 execsql {SELECT abs(t1) FROM tbl1}
175} {0.0 0.0 0.0 0.0 0.0}
176
177do_test func-4.5 {
178 catchsql {SELECT round(a,b,c) FROM t1}
179} {1 {wrong number of arguments to function round()}}
180do_test func-4.6 {
181 catchsql {SELECT round(b,2) FROM t1 ORDER BY b}
182} {0 {-2.0 1.23 2.0}}
183do_test func-4.7 {
184 catchsql {SELECT round(b,0) FROM t1 ORDER BY a}
185} {0 {2.0 1.0 -2.0}}
186do_test func-4.8 {
187 catchsql {SELECT round(c) FROM t1 ORDER BY a}
188} {0 {3.0 -12346.0 -5.0}}
189do_test func-4.9 {
190 catchsql {SELECT round(c,a) FROM t1 ORDER BY a}
191} {0 {3.0 -12345.68 -5.0}}
192do_test func-4.10 {
193 catchsql {SELECT 'x' || round(c,a) || 'y' FROM t1 ORDER BY a}
194} {0 {x3.0y x-12345.68y x-5.0y}}
195do_test func-4.11 {
196 catchsql {SELECT round() FROM t1 ORDER BY a}
197} {1 {wrong number of arguments to function round()}}
198do_test func-4.12 {
199 execsql {SELECT coalesce(round(a,2),'nil') FROM t2}
200} {1.0 nil 345.0 nil 67890.0}
201do_test func-4.13 {
202 execsql {SELECT round(t1,2) FROM tbl1}
203} {0.0 0.0 0.0 0.0 0.0}
204do_test func-4.14 {
205 execsql {SELECT typeof(round(5.1,1));}
206} {real}
207do_test func-4.15 {
208 execsql {SELECT typeof(round(5.1));}
209} {real}
210
211
212# Test the upper() and lower() functions
213#
214do_test func-5.1 {
215 execsql {SELECT upper(t1) FROM tbl1}
216} {THIS PROGRAM IS FREE SOFTWARE}
217do_test func-5.2 {
218 execsql {SELECT lower(upper(t1)) FROM tbl1}
219} {this program is free software}
220do_test func-5.3 {
221 execsql {SELECT upper(a), lower(a) FROM t2}
222} {1 1 {} {} 345 345 {} {} 67890 67890}
223ifcapable !icu {
224 do_test func-5.4 {
225 catchsql {SELECT upper(a,5) FROM t2}
226 } {1 {wrong number of arguments to function upper()}}
227}
228do_test func-5.5 {
229 catchsql {SELECT upper(*) FROM t2}
230} {1 {wrong number of arguments to function upper()}}
231
232# Test the coalesce() and nullif() functions
233#
234do_test func-6.1 {
235 execsql {SELECT coalesce(a,'xyz') FROM t2}
236} {1 xyz 345 xyz 67890}
237do_test func-6.2 {
238 execsql {SELECT coalesce(upper(a),'nil') FROM t2}
239} {1 nil 345 nil 67890}
240do_test func-6.3 {
241 execsql {SELECT coalesce(nullif(1,1),'nil')}
242} {nil}
243do_test func-6.4 {
244 execsql {SELECT coalesce(nullif(1,2),'nil')}
245} {1}
246do_test func-6.5 {
247 execsql {SELECT coalesce(nullif(1,NULL),'nil')}
248} {1}
249
250
251# Test the last_insert_rowid() function
252#
253do_test func-7.1 {
254 execsql {SELECT last_insert_rowid()}
255} [db last_insert_rowid]
256
257# Tests for aggregate functions and how they handle NULLs.
258#
259do_test func-8.1 {
260 ifcapable explain {
261 execsql {EXPLAIN SELECT sum(a) FROM t2;}
262 }
263 execsql {
264 SELECT sum(a), count(a), round(avg(a),2), min(a), max(a), count(*) FROM t2;
265 }
266} {68236 3 22745.33 1 67890 5}
267do_test func-8.2 {
268 execsql {
269 SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t2;
270 }
271} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
272
273ifcapable tempdb {
274 do_test func-8.3 {
275 execsql {
276 CREATE TEMP TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;
277 SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
278 }
279 } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
280} else {
281 do_test func-8.3 {
282 execsql {
283 CREATE TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;
284 SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
285 }
286 } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
287}
288do_test func-8.4 {
289 execsql {
290 SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
291 }
292} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
293
294# How do you test the random() function in a meaningful, deterministic way?
295#
296do_test func-9.1 {
297 execsql {
298 SELECT random() is not null;
299 }
300} {1}
301do_test func-9.2 {
302 execsql {
303 SELECT typeof(random());
304 }
305} {integer}
306do_test func-9.3 {
307 execsql {
308 SELECT randomblob(32) is not null;
309 }
310} {1}
311do_test func-9.4 {
312 execsql {
313 SELECT typeof(randomblob(32));
314 }
315} {blob}
316do_test func-9.5 {
317 execsql {
318 SELECT length(randomblob(32)), length(randomblob(-5)),
319 length(randomblob(2000))
320 }
321} {32 1 2000}
322
323# The "hex()" function was added in order to be able to render blobs
324# generated by randomblob(). So this seems like a good place to test
325# hex().
326#
327ifcapable bloblit {
328 do_test func-9.10 {
329 execsql {SELECT hex(x'00112233445566778899aAbBcCdDeEfF')}
330 } {00112233445566778899AABBCCDDEEFF}
331}
332set encoding [db one {PRAGMA encoding}]
333if {$encoding=="UTF-16le"} {
334 do_test func-9.11-utf16le {
335 execsql {SELECT hex(replace('abcdefg','ef','12'))}
336 } {6100620063006400310032006700}
337 do_test func-9.12-utf16le {
338 execsql {SELECT hex(replace('abcdefg','','12'))}
339 } {{}}
340 breakpoint
341 do_test func-9.13-utf16le {
342 execsql {SELECT hex(replace('aabcdefg','a','aaa'))}
343 } {610061006100610061006100620063006400650066006700}
344} elseif {$encoding=="UTF-8"} {
345 do_test func-9.11-utf8 {
346 execsql {SELECT hex(replace('abcdefg','ef','12'))}
347 } {61626364313267}
348 do_test func-9.12-utf8 {
349 execsql {SELECT hex(replace('abcdefg','','12'))}
350 } {{}}
351 breakpoint
352 do_test func-9.13-utf8 {
353 execsql {SELECT hex(replace('aabcdefg','a','aaa'))}
354 } {616161616161626364656667}
355}
356
357# Use the "sqlite_register_test_function" TCL command which is part of
358# the text fixture in order to verify correct operation of some of
359# the user-defined SQL function APIs that are not used by the built-in
360# functions.
361#
362set ::DB [sqlite3_connection_pointer db]
363sqlite_register_test_function $::DB testfunc
364do_test func-10.1 {
365 catchsql {
366 SELECT testfunc(NULL,NULL);
367 }
368} {1 {first argument should be one of: int int64 string double null value}}
369do_test func-10.2 {
370 execsql {
371 SELECT testfunc(
372 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
373 'int', 1234
374 );
375 }
376} {1234}
377do_test func-10.3 {
378 execsql {
379 SELECT testfunc(
380 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
381 'string', NULL
382 );
383 }
384} {{}}
385do_test func-10.4 {
386 execsql {
387 SELECT testfunc(
388 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
389 'double', 1.234
390 );
391 }
392} {1.234}
393do_test func-10.5 {
394 execsql {
395 SELECT testfunc(
396 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
397 'int', 1234,
398 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
399 'string', NULL,
400 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
401 'double', 1.234,
402 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
403 'int', 1234,
404 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
405 'string', NULL,
406 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
407 'double', 1.234
408 );
409 }
410} {1.234}
411
412# Test the built-in sqlite_version(*) SQL function.
413#
414do_test func-11.1 {
415 execsql {
416 SELECT sqlite_version(*);
417 }
418} [sqlite3 -version]
419
420# Test that destructors passed to sqlite3 by calls to sqlite3_result_text()
421# etc. are called. These tests use two special user-defined functions
422# (implemented in func.c) only available in test builds.
423#
424# Function test_destructor() takes one argument and returns a copy of the
425# text form of that argument. A destructor is associated with the return
426# value. Function test_destructor_count() returns the number of outstanding
427# destructor calls for values returned by test_destructor().
428#
429do_test func-12.1 {
430 execsql {
431 SELECT test_destructor('hello world'), test_destructor_count();
432 }
433} {{hello world} 1}
434do_test func-12.2 {
435 execsql {
436 SELECT test_destructor_count();
437 }
438} {0}
439do_test func-12.3 {
440 execsql {
441 SELECT test_destructor('hello')||' world', test_destructor_count();
442 }
443} {{hello world} 0}
444do_test func-12.4 {
445 execsql {
446 SELECT test_destructor_count();
447 }
448} {0}
449do_test func-12.5 {
450 execsql {
451 CREATE TABLE t4(x);
452 INSERT INTO t4 VALUES(test_destructor('hello'));
453 INSERT INTO t4 VALUES(test_destructor('world'));
454 SELECT min(test_destructor(x)), max(test_destructor(x)) FROM t4;
455 }
456} {hello world}
457do_test func-12.6 {
458 execsql {
459 SELECT test_destructor_count();
460 }
461} {0}
462do_test func-12.7 {
463 execsql {
464 DROP TABLE t4;
465 }
466} {}
467
468# Test that the auxdata API for scalar functions works. This test uses
469# a special user-defined function only available in test builds,
470# test_auxdata(). Function test_auxdata() takes any number of arguments.
471do_test func-13.1 {
472 execsql {
473 SELECT test_auxdata('hello world');
474 }
475} {0}
476
477do_test func-13.2 {
478 execsql {
479 CREATE TABLE t4(a, b);
480 INSERT INTO t4 VALUES('abc', 'def');
481 INSERT INTO t4 VALUES('ghi', 'jkl');
482 }
483} {}
484do_test func-13.3 {
485 execsql {
486 SELECT test_auxdata('hello world') FROM t4;
487 }
488} {0 1}
489do_test func-13.4 {
490 execsql {
491 SELECT test_auxdata('hello world', 123) FROM t4;
492 }
493} {{0 0} {1 1}}
494do_test func-13.5 {
495 execsql {
496 SELECT test_auxdata('hello world', a) FROM t4;
497 }
498} {{0 0} {1 0}}
499do_test func-13.6 {
500 execsql {
501 SELECT test_auxdata('hello'||'world', a) FROM t4;
502 }
503} {{0 0} {1 0}}
504
505# Test that auxilary data is preserved between calls for SQL variables.
506do_test func-13.7 {
507 set DB [sqlite3_connection_pointer db]
508 set sql "SELECT test_auxdata( ? , a ) FROM t4;"
509 set STMT [sqlite3_prepare $DB $sql -1 TAIL]
510 sqlite3_bind_text $STMT 1 hello -1
511 set res [list]
512 while { "SQLITE_ROW"==[sqlite3_step $STMT] } {
513 lappend res [sqlite3_column_text $STMT 0]
514 }
515 lappend res [sqlite3_finalize $STMT]
516} {{0 0} {1 0} SQLITE_OK}
517
518# Make sure that a function with a very long name is rejected
519do_test func-14.1 {
520 catch {
521 db function [string repeat X 254] {return "hello"}
522 }
523} {0}
524do_test func-14.2 {
525 catch {
526 db function [string repeat X 256] {return "hello"}
527 }
528} {1}
529
530do_test func-15.1 {
531 catchsql {
532 select test_error(NULL);
533 }
534} {1 {}}
535
536# Test the quote function for BLOB and NULL values.
537do_test func-16.1 {
538 execsql {
539 CREATE TABLE tbl2(a, b);
540 }
541 set STMT [sqlite3_prepare $::DB "INSERT INTO tbl2 VALUES(?, ?)" -1 TAIL]
542 sqlite3_bind_blob $::STMT 1 abc 3
543 sqlite3_step $::STMT
544 sqlite3_finalize $::STMT
545 execsql {
546 SELECT quote(a), quote(b) FROM tbl2;
547 }
548} {X'616263' NULL}
549
550# Correctly handle function error messages that include %. Ticket #1354
551#
552do_test func-17.1 {
553 proc testfunc1 args {error "Error %d with %s percents %p"}
554 db function testfunc1 ::testfunc1
555 catchsql {
556 SELECT testfunc1(1,2,3);
557 }
558} {1 {Error %d with %s percents %p}}
559
560# The SUM function should return integer results when all inputs are integer.
561#
562do_test func-18.1 {
563 execsql {
564 CREATE TABLE t5(x);
565 INSERT INTO t5 VALUES(1);
566 INSERT INTO t5 VALUES(-99);
567 INSERT INTO t5 VALUES(10000);
568 SELECT sum(x) FROM t5;
569 }
570} {9902}
571do_test func-18.2 {
572 execsql {
573 INSERT INTO t5 VALUES(0.0);
574 SELECT sum(x) FROM t5;
575 }
576} {9902.0}
577
578# The sum of nothing is NULL. But the sum of all NULLs is NULL.
579#
580# The TOTAL of nothing is 0.0.
581#
582do_test func-18.3 {
583 execsql {
584 DELETE FROM t5;
585 SELECT sum(x), total(x) FROM t5;
586 }
587} {{} 0.0}
588do_test func-18.4 {
589 execsql {
590 INSERT INTO t5 VALUES(NULL);
591 SELECT sum(x), total(x) FROM t5
592 }
593} {{} 0.0}
594do_test func-18.5 {
595 execsql {
596 INSERT INTO t5 VALUES(NULL);
597 SELECT sum(x), total(x) FROM t5
598 }
599} {{} 0.0}
600do_test func-18.6 {
601 execsql {
602 INSERT INTO t5 VALUES(123);
603 SELECT sum(x), total(x) FROM t5
604 }
605} {123 123.0}
606
607# Ticket #1664, #1669, #1670, #1674: An integer overflow on SUM causes
608# an error. The non-standard TOTAL() function continues to give a helpful
609# result.
610#
611do_test func-18.10 {
612 execsql {
613 CREATE TABLE t6(x INTEGER);
614 INSERT INTO t6 VALUES(1);
615 INSERT INTO t6 VALUES(1<<62);
616 SELECT sum(x) - ((1<<62)+1) from t6;
617 }
618} 0
619do_test func-18.11 {
620 execsql {
621 SELECT typeof(sum(x)) FROM t6
622 }
623} integer
624do_test func-18.12 {
625 catchsql {
626 INSERT INTO t6 VALUES(1<<62);
627 SELECT sum(x) - ((1<<62)*2.0+1) from t6;
628 }
629} {1 {integer overflow}}
630do_test func-18.13 {
631 execsql {
632 SELECT total(x) - ((1<<62)*2.0+1) FROM t6
633 }
634} 0.0
635do_test func-18.14 {
636 execsql {
637 SELECT sum(-9223372036854775805);
638 }
639} -9223372036854775805
640
641ifcapable compound&&subquery {
642
643do_test func-18.15 {
644 catchsql {
645 SELECT sum(x) FROM
646 (SELECT 9223372036854775807 AS x UNION ALL
647 SELECT 10 AS x);
648 }
649} {1 {integer overflow}}
650do_test func-18.16 {
651 catchsql {
652 SELECT sum(x) FROM
653 (SELECT 9223372036854775807 AS x UNION ALL
654 SELECT -10 AS x);
655 }
656} {0 9223372036854775797}
657do_test func-18.17 {
658 catchsql {
659 SELECT sum(x) FROM
660 (SELECT -9223372036854775807 AS x UNION ALL
661 SELECT 10 AS x);
662 }
663} {0 -9223372036854775797}
664do_test func-18.18 {
665 catchsql {
666 SELECT sum(x) FROM
667 (SELECT -9223372036854775807 AS x UNION ALL
668 SELECT -10 AS x);
669 }
670} {1 {integer overflow}}
671do_test func-18.19 {
672 catchsql {
673 SELECT sum(x) FROM (SELECT 9 AS x UNION ALL SELECT -10 AS x);
674 }
675} {0 -1}
676do_test func-18.20 {
677 catchsql {
678 SELECT sum(x) FROM (SELECT -9 AS x UNION ALL SELECT 10 AS x);
679 }
680} {0 1}
681do_test func-18.21 {
682 catchsql {
683 SELECT sum(x) FROM (SELECT -10 AS x UNION ALL SELECT 9 AS x);
684 }
685} {0 -1}
686do_test func-18.22 {
687 catchsql {
688 SELECT sum(x) FROM (SELECT 10 AS x UNION ALL SELECT -9 AS x);
689 }
690} {0 1}
691
692} ;# ifcapable compound&&subquery
693
694# Integer overflow on abs()
695#
696do_test func-18.31 {
697 catchsql {
698 SELECT abs(-9223372036854775807);
699 }
700} {0 9223372036854775807}
701do_test func-18.32 {
702 catchsql {
703 SELECT abs(-9223372036854775807-1);
704 }
705} {1 {integer overflow}}
706
707# The MATCH function exists but is only a stub and always throws an error.
708#
709do_test func-19.1 {
710 execsql {
711 SELECT match(a,b) FROM t1 WHERE 0;
712 }
713} {}
714do_test func-19.2 {
715 catchsql {
716 SELECT 'abc' MATCH 'xyz';
717 }
718} {1 {unable to use function MATCH in the requested context}}
719do_test func-19.3 {
720 catchsql {
721 SELECT 'abc' NOT MATCH 'xyz';
722 }
723} {1 {unable to use function MATCH in the requested context}}
724do_test func-19.4 {
725 catchsql {
726 SELECT match(1,2,3);
727 }
728} {1 {wrong number of arguments to function match()}}
729
730# Soundex tests.
731#
732if {![catch {db eval {SELECT soundex('hello')}}]} {
733 set i 0
734 foreach {name sdx} {
735 euler E460
736 EULER E460
737 Euler E460
738 ellery E460
739 gauss G200
740 ghosh G200
741 hilbert H416
742 Heilbronn H416
743 knuth K530
744 kant K530
745 Lloyd L300
746 LADD L300
747 Lukasiewicz L222
748 Lissajous L222
749 A A000
750 12345 ?000
751 } {
752 incr i
753 do_test func-20.$i {
754 execsql {SELECT soundex($name)}
755 } $sdx
756 }
757}
758
759# Tests of the REPLACE function.
760#
761do_test func-21.1 {
762 catchsql {
763 SELECT replace(1,2);
764 }
765} {1 {wrong number of arguments to function replace()}}
766do_test func-21.2 {
767 catchsql {
768 SELECT replace(1,2,3,4);
769 }
770} {1 {wrong number of arguments to function replace()}}
771do_test func-21.3 {
772 execsql {
773 SELECT typeof(replace("This is the main test string", NULL, "ALT"));
774 }
775} {null}
776do_test func-21.4 {
777 execsql {
778 SELECT typeof(replace(NULL, "main", "ALT"));
779 }
780} {null}
781do_test func-21.5 {
782 execsql {
783 SELECT typeof(replace("This is the main test string", "main", NULL));
784 }
785} {null}
786do_test func-21.6 {
787 execsql {
788 SELECT replace("This is the main test string", "main", "ALT");
789 }
790} {{This is the ALT test string}}
791do_test func-21.7 {
792 execsql {
793 SELECT replace("This is the main test string", "main", "larger-main");
794 }
795} {{This is the larger-main test string}}
796do_test func-21.8 {
797 execsql {
798 SELECT replace("aaaaaaa", "a", "0123456789");
799 }
800} {0123456789012345678901234567890123456789012345678901234567890123456789}
801
802ifcapable tclvar {
803 do_test func-21.9 {
804 # Attempt to exploit a buffer-overflow that at one time existed
805 # in the REPLACE function.
806 set ::str "[string repeat A 29998]CC[string repeat A 35537]"
807 set ::rep [string repeat B 65536]
808 execsql {
809 SELECT LENGTH(REPLACE($::str, 'C', $::rep));
810 }
811 } [expr 29998 + 2*65536 + 35537]
812}
813
814# Tests for the TRIM, LTRIM and RTRIM functions.
815#
816do_test func-22.1 {
817 catchsql {SELECT trim(1,2,3)}
818} {1 {wrong number of arguments to function trim()}}
819do_test func-22.2 {
820 catchsql {SELECT ltrim(1,2,3)}
821} {1 {wrong number of arguments to function ltrim()}}
822do_test func-22.3 {
823 catchsql {SELECT rtrim(1,2,3)}
824} {1 {wrong number of arguments to function rtrim()}}
825do_test func-22.4 {
826 execsql {SELECT trim(' hi ');}
827} {hi}
828do_test func-22.5 {
829 execsql {SELECT ltrim(' hi ');}
830} {{hi }}
831do_test func-22.6 {
832 execsql {SELECT rtrim(' hi ');}
833} {{ hi}}
834do_test func-22.7 {
835 execsql {SELECT trim(' hi ','xyz');}
836} {{ hi }}
837do_test func-22.8 {
838 execsql {SELECT ltrim(' hi ','xyz');}
839} {{ hi }}
840do_test func-22.9 {
841 execsql {SELECT rtrim(' hi ','xyz');}
842} {{ hi }}
843do_test func-22.10 {
844 execsql {SELECT trim('xyxzy hi zzzy','xyz');}
845} {{ hi }}
846do_test func-22.11 {
847 execsql {SELECT ltrim('xyxzy hi zzzy','xyz');}
848} {{ hi zzzy}}
849do_test func-22.12 {
850 execsql {SELECT rtrim('xyxzy hi zzzy','xyz');}
851} {{xyxzy hi }}
852do_test func-22.13 {
853 execsql {SELECT trim(' hi ','');}
854} {{ hi }}
855if {[db one {PRAGMA encoding}]=="UTF-8"} {
856 do_test func-22.14 {
857 execsql {SELECT hex(trim(x'c280e1bfbff48fbfbf6869',x'6162e1bfbfc280'))}
858 } {F48FBFBF6869}
859 do_test func-22.15 {
860 execsql {SELECT hex(trim(x'6869c280e1bfbff48fbfbf61',
861 x'6162e1bfbfc280f48fbfbf'))}
862 } {6869}
863 do_test func-22.16 {
864 execsql {SELECT hex(trim(x'ceb1ceb2ceb3',x'ceb1'));}
865 } {CEB2CEB3}
866}
867do_test func-22.20 {
868 execsql {SELECT typeof(trim(NULL));}
869} {null}
870do_test func-22.21 {
871 execsql {SELECT typeof(trim(NULL,'xyz'));}
872} {null}
873do_test func-22.22 {
874 execsql {SELECT typeof(trim('hello',NULL));}
875} {null}
876
877# This is to test the deprecated sqlite3_aggregate_count() API.
878#
879do_test func-23.1 {
880 sqlite3_create_aggregate db
881 execsql {
882 SELECT legacy_count() FROM t6;
883 }
884} {3}
885
886finish_test
generated by cgit v1.1 at 2025-04-25 11:39:42 +0000