1#
2# 2001 September 15
3#
4# The author disclaims copyright to this source code.  In place of
5# a legal notice, here is a blessing:
6#
7#    May you do good and not evil.
8#    May you find forgiveness for yourself and forgive others.
9#    May you share freely, never taking more than you give.
10#
11#***********************************************************************
12# This file implements regression tests for SQLite library.  The
13# focus of this file is testing built-in functions.
14#
15# $Id: func.test,v 1.16.2.2 2004/07/18 21:14:05 drh Exp $
16
17set testdir [file dirname $argv0]
18source $testdir/tester.tcl
19
20# Create a table to work with.
21#
22do_test func-0.0 {
23  execsql {CREATE TABLE tbl1(t1 text)}
24  foreach word {this program is free software} {
25    execsql "INSERT INTO tbl1 VALUES('$word')"
26  }
27  execsql {SELECT t1 FROM tbl1 ORDER BY t1}
28} {free is program software this}
29do_test func-0.1 {
30  execsql {
31     CREATE TABLE t2(a);
32     INSERT INTO t2 VALUES(1);
33     INSERT INTO t2 VALUES(NULL);
34     INSERT INTO t2 VALUES(345);
35     INSERT INTO t2 VALUES(NULL);
36     INSERT INTO t2 VALUES(67890);
37     SELECT * FROM t2;
38  }
39} {1 {} 345 {} 67890}
40
41# Check out the length() function
42#
43do_test func-1.0 {
44  execsql {SELECT length(t1) FROM tbl1 ORDER BY t1}
45} {4 2 7 8 4}
46do_test func-1.1 {
47  set r [catch {execsql {SELECT length(*) FROM tbl1 ORDER BY t1}} msg]
48  lappend r $msg
49} {1 {wrong number of arguments to function length()}}
50do_test func-1.2 {
51  set r [catch {execsql {SELECT length(t1,5) FROM tbl1 ORDER BY t1}} msg]
52  lappend r $msg
53} {1 {wrong number of arguments to function length()}}
54do_test func-1.3 {
55  execsql {SELECT length(t1), count(*) FROM tbl1 GROUP BY length(t1)
56           ORDER BY length(t1)}
57} {2 1 4 2 7 1 8 1}
58do_test func-1.4 {
59  execsql {SELECT coalesce(length(a),-1) FROM t2}
60} {1 -1 3 -1 5}
61
62# Check out the substr() function
63#
64do_test func-2.0 {
65  execsql {SELECT substr(t1,1,2) FROM tbl1 ORDER BY t1}
66} {fr is pr so th}
67do_test func-2.1 {
68  execsql {SELECT substr(t1,2,1) FROM tbl1 ORDER BY t1}
69} {r s r o h}
70do_test func-2.2 {
71  execsql {SELECT substr(t1,3,3) FROM tbl1 ORDER BY t1}
72} {ee {} ogr ftw is}
73do_test func-2.3 {
74  execsql {SELECT substr(t1,-1,1) FROM tbl1 ORDER BY t1}
75} {e s m e s}
76do_test func-2.4 {
77  execsql {SELECT substr(t1,-1,2) FROM tbl1 ORDER BY t1}
78} {e s m e s}
79do_test func-2.5 {
80  execsql {SELECT substr(t1,-2,1) FROM tbl1 ORDER BY t1}
81} {e i a r i}
82do_test func-2.6 {
83  execsql {SELECT substr(t1,-2,2) FROM tbl1 ORDER BY t1}
84} {ee is am re is}
85do_test func-2.7 {
86  execsql {SELECT substr(t1,-4,2) FROM tbl1 ORDER BY t1}
87} {fr {} gr wa th}
88do_test func-2.8 {
89  execsql {SELECT t1 FROM tbl1 ORDER BY substr(t1,2,20)}
90} {this software free program is}
91do_test func-2.9 {
92  execsql {SELECT substr(a,1,1) FROM t2}
93} {1 {} 3 {} 6}
94do_test func-2.10 {
95  execsql {SELECT substr(a,2,2) FROM t2}
96} {{} {} 45 {} 78}
97
98# Only do the following tests if TCL has UTF-8 capabilities and
99# the UTF-8 encoding is turned on in the SQLite library.
100#
101if {[sqlite -encoding]=="UTF-8" && "\u1234"!="u1234"} {
102
103# Put some UTF-8 characters in the database
104#
105do_test func-3.0 {
106  execsql {DELETE FROM tbl1}
107  foreach word "contains UTF-8 characters hi\u1234ho" {
108    execsql "INSERT INTO tbl1 VALUES('$word')"
109  }
110  execsql {SELECT t1 FROM tbl1 ORDER BY t1}
111} "UTF-8 characters contains hi\u1234ho"
112do_test func-3.1 {
113  execsql {SELECT length(t1) FROM tbl1 ORDER BY t1}
114} {5 10 8 5}
115do_test func-3.2 {
116  execsql {SELECT substr(t1,1,2) FROM tbl1 ORDER BY t1}
117} {UT ch co hi}
118do_test func-3.3 {
119  execsql {SELECT substr(t1,1,3) FROM tbl1 ORDER BY t1}
120} "UTF cha con hi\u1234"
121do_test func-3.4 {
122  execsql {SELECT substr(t1,2,2) FROM tbl1 ORDER BY t1}
123} "TF ha on i\u1234"
124do_test func-3.5 {
125  execsql {SELECT substr(t1,2,3) FROM tbl1 ORDER BY t1}
126} "TF- har ont i\u1234h"
127do_test func-3.6 {
128  execsql {SELECT substr(t1,3,2) FROM tbl1 ORDER BY t1}
129} "F- ar nt \u1234h"
130do_test func-3.7 {
131  execsql {SELECT substr(t1,4,2) FROM tbl1 ORDER BY t1}
132} "-8 ra ta ho"
133do_test func-3.8 {
134  execsql {SELECT substr(t1,-1,1) FROM tbl1 ORDER BY t1}
135} "8 s s o"
136do_test func-3.9 {
137  execsql {SELECT substr(t1,-3,2) FROM tbl1 ORDER BY t1}
138} "F- er in \u1234h"
139do_test func-3.10 {
140  execsql {SELECT substr(t1,-4,3) FROM tbl1 ORDER BY t1}
141} "TF- ter ain i\u1234h"
142do_test func-3.99 {
143  execsql {DELETE FROM tbl1}
144  foreach word {this program is free software} {
145    execsql "INSERT INTO tbl1 VALUES('$word')"
146  }
147  execsql {SELECT t1 FROM tbl1}
148} {this program is free software}
149
150} ;# End [sqlite -encoding]==UTF-8 and \u1234!=u1234
151
152# Test the abs() and round() functions.
153#
154do_test func-4.1 {
155  execsql {
156    CREATE TABLE t1(a,b,c);
157    INSERT INTO t1 VALUES(1,2,3);
158    INSERT INTO t1 VALUES(2,1.2345678901234,-12345.67890);
159    INSERT INTO t1 VALUES(3,-2,-5);
160  }
161  catchsql {SELECT abs(a,b) FROM t1}
162} {1 {wrong number of arguments to function abs()}}
163do_test func-4.2 {
164  catchsql {SELECT abs() FROM t1}
165} {1 {wrong number of arguments to function abs()}}
166do_test func-4.3 {
167  catchsql {SELECT abs(b) FROM t1 ORDER BY a}
168} {0 {2 1.2345678901234 2}}
169do_test func-4.4 {
170  catchsql {SELECT abs(c) FROM t1 ORDER BY a}
171} {0 {3 12345.67890 5}}
172do_test func-4.4.1 {
173  execsql {SELECT abs(a) FROM t2}
174} {1 {} 345 {} 67890}
175do_test func-4.4.2 {
176  execsql {SELECT abs(t1) FROM tbl1}
177} {this program is free software}
178
179do_test func-4.5 {
180  catchsql {SELECT round(a,b,c) FROM t1}
181} {1 {wrong number of arguments to function round()}}
182do_test func-4.6 {
183  catchsql {SELECT round(b,2) FROM t1 ORDER BY b}
184} {0 {-2.00 1.23 2.00}}
185do_test func-4.7 {
186  catchsql {SELECT round(b,0) FROM t1 ORDER BY a}
187} {0 {2 1 -2}}
188do_test func-4.8 {
189  catchsql {SELECT round(c) FROM t1 ORDER BY a}
190} {0 {3 -12346 -5}}
191do_test func-4.9 {
192  catchsql {SELECT round(c,a) FROM t1 ORDER BY a}
193} {0 {3.0 -12345.68 -5.000}}
194do_test func-4.10 {
195  catchsql {SELECT 'x' || round(c,a) || 'y' FROM t1 ORDER BY a}
196} {0 {x3.0y x-12345.68y x-5.000y}}
197do_test func-4.11 {
198  catchsql {SELECT round() FROM t1 ORDER BY a}
199} {1 {wrong number of arguments to function round()}}
200do_test func-4.12 {
201  execsql {SELECT coalesce(round(a,2),'nil') FROM t2}
202} {1.00 nil 345.00 nil 67890.00}
203do_test func-4.13 {
204  execsql {SELECT round(t1,2) FROM tbl1}
205} {0.00 0.00 0.00 0.00 0.00}
206
207# Test the upper() and lower() functions
208#
209do_test func-5.1 {
210  execsql {SELECT upper(t1) FROM tbl1}
211} {THIS PROGRAM IS FREE SOFTWARE}
212do_test func-5.2 {
213  execsql {SELECT lower(upper(t1)) FROM tbl1}
214} {this program is free software}
215do_test func-5.3 {
216  execsql {SELECT upper(a), lower(a) FROM t2}
217} {1 1 {} {} 345 345 {} {} 67890 67890}
218do_test func-5.4 {
219  catchsql {SELECT upper(a,5) FROM t2}
220} {1 {wrong number of arguments to function upper()}}
221do_test func-5.5 {
222  catchsql {SELECT upper(*) FROM t2}
223} {1 {wrong number of arguments to function upper()}}
224
225# Test the coalesce() and nullif() functions
226#
227do_test func-6.1 {
228  execsql {SELECT coalesce(a,'xyz') FROM t2}
229} {1 xyz 345 xyz 67890}
230do_test func-6.2 {
231  execsql {SELECT coalesce(upper(a),'nil') FROM t2}
232} {1 nil 345 nil 67890}
233do_test func-6.3 {
234  execsql {SELECT coalesce(nullif(1,1),'nil')}
235} {nil}
236do_test func-6.4 {
237  execsql {SELECT coalesce(nullif(1,2),'nil')}
238} {1}
239do_test func-6.5 {
240  execsql {SELECT coalesce(nullif(1,NULL),'nil')}
241} {1}
242
243
244# Test the last_insert_rowid() function
245#
246do_test func-7.1 {
247  execsql {SELECT last_insert_rowid()}
248} [db last_insert_rowid]
249
250# Tests for aggregate functions and how they handle NULLs.
251#
252do_test func-8.1 {
253  execsql {
254    SELECT sum(a), count(a), round(avg(a),2), min(a), max(a), count(*) FROM t2;
255  }
256} {68236 3 22745.33 1 67890 5}
257do_test func-8.2 {
258  execsql {
259    SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t2;
260  }
261} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
262do_test func-8.3 {
263  execsql {
264    CREATE TEMP TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;
265    SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
266  }
267} {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
268do_test func-8.4 {
269  execsql {
270    SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
271  }
272} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
273
274# How do you test the random() function in a meaningful, deterministic way?
275#
276do_test func-9.1 {
277  execsql {
278    SELECT random() is not null;
279  }
280} {1}
281
282# Use the "sqlite_register_test_function" TCL command which is part of
283# the text fixture in order to verify correct operation of some of
284# the user-defined SQL function APIs that are not used by the built-in
285# functions.
286#
287db close
288set ::DB [sqlite db test.db]
289sqlite_register_test_function $::DB testfunc
290do_test func-10.1 {
291  catchsql {
292    SELECT testfunc(NULL,NULL);
293  }
294} {1 {first argument to test function may not be NULL}}
295do_test func-10.2 {
296  execsql {
297    SELECT testfunc(
298     'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
299     'int', 1234
300    );
301  }
302} {1234}
303do_test func-10.3 {
304  execsql {
305    SELECT testfunc(
306     'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
307     'string', NULL
308    );
309  }
310} {{}}
311do_test func-10.4 {
312  execsql {
313    SELECT testfunc(
314     'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
315     'double', 1.234
316    );
317  }
318} {1.234}
319do_test func-10.5 {
320  execsql {
321    SELECT testfunc(
322     'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
323     'int', 1234,
324     'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
325     'string', NULL,
326     'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
327     'double', 1.234,
328     'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
329     'int', 1234,
330     'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
331     'string', NULL,
332     'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
333     'double', 1.234
334    );
335  }
336} {1.234}
337
338# Test the built-in sqlite_version(*) SQL function.
339#
340do_test func-11.1 {
341  execsql {
342    SELECT sqlite_version(*);
343  }
344} [sqlite -version]
345
346finish_test
347