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.16.2.2 2004/07/18 21:14:05 drh 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 and
98# the UTF-8 encoding is turned on in the SQLite library.
99#
100if {[sqlite -encoding]=="UTF-8" && "\u1234"!="u1234"} {
101
102# Put some UTF-8 characters in the database
103#
104do_test func-3.0 {
105  execsql {DELETE FROM tbl1}
106  foreach word "contains UTF-8 characters hi\u1234ho" {
107    execsql "INSERT INTO tbl1 VALUES('$word')"
108  }
109  execsql {SELECT t1 FROM tbl1 ORDER BY t1}
110} "UTF-8 characters contains hi\u1234ho"
111do_test func-3.1 {
112  execsql {SELECT length(t1) FROM tbl1 ORDER BY t1}
113} {5 10 8 5}
114do_test func-3.2 {
115  execsql {SELECT substr(t1,1,2) FROM tbl1 ORDER BY t1}
116} {UT ch co hi}
117do_test func-3.3 {
118  execsql {SELECT substr(t1,1,3) FROM tbl1 ORDER BY t1}
119} "UTF cha con hi\u1234"
120do_test func-3.4 {
121  execsql {SELECT substr(t1,2,2) FROM tbl1 ORDER BY t1}
122} "TF ha on i\u1234"
123do_test func-3.5 {
124  execsql {SELECT substr(t1,2,3) FROM tbl1 ORDER BY t1}
125} "TF- har ont i\u1234h"
126do_test func-3.6 {
127  execsql {SELECT substr(t1,3,2) FROM tbl1 ORDER BY t1}
128} "F- ar nt \u1234h"
129do_test func-3.7 {
130  execsql {SELECT substr(t1,4,2) FROM tbl1 ORDER BY t1}
131} "-8 ra ta ho"
132do_test func-3.8 {
133  execsql {SELECT substr(t1,-1,1) FROM tbl1 ORDER BY t1}
134} "8 s s o"
135do_test func-3.9 {
136  execsql {SELECT substr(t1,-3,2) FROM tbl1 ORDER BY t1}
137} "F- er in \u1234h"
138do_test func-3.10 {
139  execsql {SELECT substr(t1,-4,3) FROM tbl1 ORDER BY t1}
140} "TF- ter ain i\u1234h"
141do_test func-3.99 {
142  execsql {DELETE FROM tbl1}
143  foreach word {this program is free software} {
144    execsql "INSERT INTO tbl1 VALUES('$word')"
145  }
146  execsql {SELECT t1 FROM tbl1}
147} {this program is free software}
148
149} ;# End [sqlite -encoding]==UTF-8 and \u1234!=u1234
150
151# Test the abs() and round() functions.
152#
153do_test func-4.1 {
154  execsql {
155    CREATE TABLE t1(a,b,c);
156    INSERT INTO t1 VALUES(1,2,3);
157    INSERT INTO t1 VALUES(2,1.2345678901234,-12345.67890);
158    INSERT INTO t1 VALUES(3,-2,-5);
159  }
160  catchsql {SELECT abs(a,b) FROM t1}
161} {1 {wrong number of arguments to function abs()}}
162do_test func-4.2 {
163  catchsql {SELECT abs() FROM t1}
164} {1 {wrong number of arguments to function abs()}}
165do_test func-4.3 {
166  catchsql {SELECT abs(b) FROM t1 ORDER BY a}
167} {0 {2 1.2345678901234 2}}
168do_test func-4.4 {
169  catchsql {SELECT abs(c) FROM t1 ORDER BY a}
170} {0 {3 12345.67890 5}}
171do_test func-4.4.1 {
172  execsql {SELECT abs(a) FROM t2}
173} {1 {} 345 {} 67890}
174do_test func-4.4.2 {
175  execsql {SELECT abs(t1) FROM tbl1}
176} {this program is free software}
177
178do_test func-4.5 {
179  catchsql {SELECT round(a,b,c) FROM t1}
180} {1 {wrong number of arguments to function round()}}
181do_test func-4.6 {
182  catchsql {SELECT round(b,2) FROM t1 ORDER BY b}
183} {0 {-2.00 1.23 2.00}}
184do_test func-4.7 {
185  catchsql {SELECT round(b,0) FROM t1 ORDER BY a}
186} {0 {2 1 -2}}
187do_test func-4.8 {
188  catchsql {SELECT round(c) FROM t1 ORDER BY a}
189} {0 {3 -12346 -5}}
190do_test func-4.9 {
191  catchsql {SELECT round(c,a) FROM t1 ORDER BY a}
192} {0 {3.0 -12345.68 -5.000}}
193do_test func-4.10 {
194  catchsql {SELECT 'x' || round(c,a) || 'y' FROM t1 ORDER BY a}
195} {0 {x3.0y x-12345.68y x-5.000y}}
196do_test func-4.11 {
197  catchsql {SELECT round() FROM t1 ORDER BY a}
198} {1 {wrong number of arguments to function round()}}
199do_test func-4.12 {
200  execsql {SELECT coalesce(round(a,2),'nil') FROM t2}
201} {1.00 nil 345.00 nil 67890.00}
202do_test func-4.13 {
203  execsql {SELECT round(t1,2) FROM tbl1}
204} {0.00 0.00 0.00 0.00 0.00}
205
206# Test the upper() and lower() functions
207#
208do_test func-5.1 {
209  execsql {SELECT upper(t1) FROM tbl1}
210} {THIS PROGRAM IS FREE SOFTWARE}
211do_test func-5.2 {
212  execsql {SELECT lower(upper(t1)) FROM tbl1}
213} {this program is free software}
214do_test func-5.3 {
215  execsql {SELECT upper(a), lower(a) FROM t2}
216} {1 1 {} {} 345 345 {} {} 67890 67890}
217do_test func-5.4 {
218  catchsql {SELECT upper(a,5) FROM t2}
219} {1 {wrong number of arguments to function upper()}}
220do_test func-5.5 {
221  catchsql {SELECT upper(*) FROM t2}
222} {1 {wrong number of arguments to function upper()}}
223
224# Test the coalesce() and nullif() functions
225#
226do_test func-6.1 {
227  execsql {SELECT coalesce(a,'xyz') FROM t2}
228} {1 xyz 345 xyz 67890}
229do_test func-6.2 {
230  execsql {SELECT coalesce(upper(a),'nil') FROM t2}
231} {1 nil 345 nil 67890}
232do_test func-6.3 {
233  execsql {SELECT coalesce(nullif(1,1),'nil')}
234} {nil}
235do_test func-6.4 {
236  execsql {SELECT coalesce(nullif(1,2),'nil')}
237} {1}
238do_test func-6.5 {
239  execsql {SELECT coalesce(nullif(1,NULL),'nil')}
240} {1}
241
242
243# Test the last_insert_rowid() function
244#
245do_test func-7.1 {
246  execsql {SELECT last_insert_rowid()}
247} [db last_insert_rowid]
248
249# Tests for aggregate functions and how they handle NULLs.
250#
251do_test func-8.1 {
252  execsql {
253    SELECT sum(a), count(a), round(avg(a),2), min(a), max(a), count(*) FROM t2;
254  }
255} {68236 3 22745.33 1 67890 5}
256do_test func-8.2 {
257  execsql {
258    SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t2;
259  }
260} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
261do_test func-8.3 {
262  execsql {
263    CREATE TEMP TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;
264    SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
265  }
266} {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
267do_test func-8.4 {
268  execsql {
269    SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
270  }
271} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
272
273# How do you test the random() function in a meaningful, deterministic way?
274#
275do_test func-9.1 {
276  execsql {
277    SELECT random() is not null;
278  }
279} {1}
280
281# Use the "sqlite_register_test_function" TCL command which is part of
282# the text fixture in order to verify correct operation of some of
283# the user-defined SQL function APIs that are not used by the built-in
284# functions.
285#
286db close
287set ::DB [sqlite db test.db]
288sqlite_register_test_function $::DB testfunc
289do_test func-10.1 {
290  catchsql {
291    SELECT testfunc(NULL,NULL);
292  }
293} {1 {first argument to test function may not be NULL}}
294do_test func-10.2 {
295  execsql {
296    SELECT testfunc(
297     'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
298     'int', 1234
299    );
300  }
301} {1234}
302do_test func-10.3 {
303  execsql {
304    SELECT testfunc(
305     'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
306     'string', NULL
307    );
308  }
309} {{}}
310do_test func-10.4 {
311  execsql {
312    SELECT testfunc(
313     'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
314     'double', 1.234
315    );
316  }
317} {1.234}
318do_test func-10.5 {
319  execsql {
320    SELECT testfunc(
321     'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
322     'int', 1234,
323     'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
324     'string', NULL,
325     'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
326     'double', 1.234,
327     'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
328     'int', 1234,
329     'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
330     'string', NULL,
331     'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
332     'double', 1.234
333    );
334  }
335} {1.234}
336
337# Test the built-in sqlite_version(*) SQL function.
338#
339do_test func-11.1 {
340  execsql {
341    SELECT sqlite_version(*);
342  }
343} [sqlite -version]
344
345finish_test
346