xref: /sqlite-3.40.0/test/func.test (revision 74e4352a) 
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.57 2007/01/29 17:58:28 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
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}
223do_test func-5.4 {
224  catchsql {SELECT upper(a,5) FROM t2}
225} {1 {wrong number of arguments to function upper()}}
226do_test func-5.5 {
227  catchsql {SELECT upper(*) FROM t2}
228} {1 {wrong number of arguments to function upper()}}
229
230# Test the coalesce() and nullif() functions
231#
232do_test func-6.1 {
233  execsql {SELECT coalesce(a,'xyz') FROM t2}
234} {1 xyz 345 xyz 67890}
235do_test func-6.2 {
236  execsql {SELECT coalesce(upper(a),'nil') FROM t2}
237} {1 nil 345 nil 67890}
238do_test func-6.3 {
239  execsql {SELECT coalesce(nullif(1,1),'nil')}
240} {nil}
241do_test func-6.4 {
242  execsql {SELECT coalesce(nullif(1,2),'nil')}
243} {1}
244do_test func-6.5 {
245  execsql {SELECT coalesce(nullif(1,NULL),'nil')}
246} {1}
247
248
249# Test the last_insert_rowid() function
250#
251do_test func-7.1 {
252  execsql {SELECT last_insert_rowid()}
253} [db last_insert_rowid]
254
255# Tests for aggregate functions and how they handle NULLs.
256#
257do_test func-8.1 {
258  ifcapable explain {
259    execsql {EXPLAIN SELECT sum(a) FROM t2;}
260  }
261  execsql {
262    SELECT sum(a), count(a), round(avg(a),2), min(a), max(a), count(*) FROM t2;
263  }
264} {68236 3 22745.33 1 67890 5}
265do_test func-8.2 {
266  execsql {
267    SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t2;
268  }
269} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
270
271ifcapable tempdb {
272  do_test func-8.3 {
273    execsql {
274      CREATE TEMP TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;
275      SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
276    }
277  } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
278} else {
279  do_test func-8.3 {
280    execsql {
281      CREATE TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;
282      SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
283    }
284  } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
285}
286do_test func-8.4 {
287  execsql {
288    SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
289  }
290} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
291
292# How do you test the random() function in a meaningful, deterministic way?
293#
294do_test func-9.1 {
295  execsql {
296    SELECT random() is not null;
297  }
298} {1}
299do_test func-9.2 {
300  execsql {
301    SELECT typeof(random());
302  }
303} {integer}
304do_test func-9.3 {
305  execsql {
306    SELECT randomblob(32) is not null;
307  }
308} {1}
309do_test func-9.4 {
310  execsql {
311    SELECT typeof(randomblob(32));
312  }
313} {blob}
314do_test func-9.5 {
315  execsql {
316    SELECT length(randomblob(32)), length(randomblob(-5)),
317           length(randomblob(2000))
318  }
319} {32 1 2000}
320
321# The "hex()" function was added in order to be able to render blobs
322# generated by randomblob().  So this seems like a good place to test
323# hex().
324#
325do_test func-9.10 {
326  execsql {SELECT hex(x'00112233445566778899aAbBcCdDeEfF')}
327} {00112233445566778899AABBCCDDEEFF}
328
329# Use the "sqlite_register_test_function" TCL command which is part of
330# the text fixture in order to verify correct operation of some of
331# the user-defined SQL function APIs that are not used by the built-in
332# functions.
333#
334set ::DB [sqlite3_connection_pointer db]
335sqlite_register_test_function $::DB testfunc
336do_test func-10.1 {
337  catchsql {
338    SELECT testfunc(NULL,NULL);
339  }
340} {1 {first argument should be one of: int int64 string double null value}}
341do_test func-10.2 {
342  execsql {
343    SELECT testfunc(
344     'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
345     'int', 1234
346    );
347  }
348} {1234}
349do_test func-10.3 {
350  execsql {
351    SELECT testfunc(
352     'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
353     'string', NULL
354    );
355  }
356} {{}}
357do_test func-10.4 {
358  execsql {
359    SELECT testfunc(
360     'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
361     'double', 1.234
362    );
363  }
364} {1.234}
365do_test func-10.5 {
366  execsql {
367    SELECT testfunc(
368     'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
369     'int', 1234,
370     'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
371     'string', NULL,
372     'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
373     'double', 1.234,
374     'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
375     'int', 1234,
376     'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
377     'string', NULL,
378     'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
379     'double', 1.234
380    );
381  }
382} {1.234}
383
384# Test the built-in sqlite_version(*) SQL function.
385#
386do_test func-11.1 {
387  execsql {
388    SELECT sqlite_version(*);
389  }
390} [sqlite3 -version]
391
392# Test that destructors passed to sqlite3 by calls to sqlite3_result_text()
393# etc. are called. These tests use two special user-defined functions
394# (implemented in func.c) only available in test builds.
395#
396# Function test_destructor() takes one argument and returns a copy of the
397# text form of that argument. A destructor is associated with the return
398# value. Function test_destructor_count() returns the number of outstanding
399# destructor calls for values returned by test_destructor().
400#
401do_test func-12.1 {
402  execsql {
403    SELECT test_destructor('hello world'), test_destructor_count();
404  }
405} {{hello world} 1}
406do_test func-12.2 {
407  execsql {
408    SELECT test_destructor_count();
409  }
410} {0}
411do_test func-12.3 {
412  execsql {
413    SELECT test_destructor('hello')||' world', test_destructor_count();
414  }
415} {{hello world} 0}
416do_test func-12.4 {
417  execsql {
418    SELECT test_destructor_count();
419  }
420} {0}
421do_test func-12.5 {
422  execsql {
423    CREATE TABLE t4(x);
424    INSERT INTO t4 VALUES(test_destructor('hello'));
425    INSERT INTO t4 VALUES(test_destructor('world'));
426    SELECT min(test_destructor(x)), max(test_destructor(x)) FROM t4;
427  }
428} {hello world}
429do_test func-12.6 {
430  execsql {
431    SELECT test_destructor_count();
432  }
433} {0}
434do_test func-12.7 {
435  execsql {
436    DROP TABLE t4;
437  }
438} {}
439
440# Test that the auxdata API for scalar functions works. This test uses
441# a special user-defined function only available in test builds,
442# test_auxdata(). Function test_auxdata() takes any number of arguments.
443btree_breakpoint
444do_test func-13.1 {
445  execsql {
446    SELECT test_auxdata('hello world');
447  }
448} {0}
449
450do_test func-13.2 {
451  execsql {
452    CREATE TABLE t4(a, b);
453    INSERT INTO t4 VALUES('abc', 'def');
454    INSERT INTO t4 VALUES('ghi', 'jkl');
455  }
456} {}
457do_test func-13.3 {
458  execsql {
459    SELECT test_auxdata('hello world') FROM t4;
460  }
461} {0 1}
462do_test func-13.4 {
463  execsql {
464    SELECT test_auxdata('hello world', 123) FROM t4;
465  }
466} {{0 0} {1 1}}
467do_test func-13.5 {
468  execsql {
469    SELECT test_auxdata('hello world', a) FROM t4;
470  }
471} {{0 0} {1 0}}
472do_test func-13.6 {
473  execsql {
474    SELECT test_auxdata('hello'||'world', a) FROM t4;
475  }
476} {{0 0} {1 0}}
477
478# Test that auxilary data is preserved between calls for SQL variables.
479do_test func-13.7 {
480  set DB [sqlite3_connection_pointer db]
481  set sql "SELECT test_auxdata( ? , a ) FROM t4;"
482  set STMT [sqlite3_prepare $DB $sql -1 TAIL]
483  sqlite3_bind_text $STMT 1 hello -1
484  set res [list]
485  while { "SQLITE_ROW"==[sqlite3_step $STMT] } {
486    lappend res [sqlite3_column_text $STMT 0]
487  }
488  lappend res [sqlite3_finalize $STMT]
489} {{0 0} {1 0} SQLITE_OK}
490
491# Make sure that a function with a very long name is rejected
492do_test func-14.1 {
493  catch {
494    db function [string repeat X 254] {return "hello"}
495  }
496} {0}
497do_test func-14.2 {
498  catch {
499    db function [string repeat X 256] {return "hello"}
500  }
501} {1}
502
503do_test func-15.1 {
504  catchsql {
505    select test_error(NULL);
506  }
507} {1 {}}
508
509# Test the quote function for BLOB and NULL values.
510do_test func-16.1 {
511  execsql {
512    CREATE TABLE tbl2(a, b);
513  }
514  set STMT [sqlite3_prepare $::DB "INSERT INTO tbl2 VALUES(?, ?)" -1 TAIL]
515  sqlite3_bind_blob $::STMT 1 abc 3
516  sqlite3_step $::STMT
517  sqlite3_finalize $::STMT
518  execsql {
519    SELECT quote(a), quote(b) FROM tbl2;
520  }
521} {X'616263' NULL}
522
523# Correctly handle function error messages that include %.  Ticket #1354
524#
525do_test func-17.1 {
526  proc testfunc1 args {error "Error %d with %s percents %p"}
527  db function testfunc1 ::testfunc1
528  catchsql {
529    SELECT testfunc1(1,2,3);
530  }
531} {1 {Error %d with %s percents %p}}
532
533# The SUM function should return integer results when all inputs are integer.
534#
535do_test func-18.1 {
536  execsql {
537    CREATE TABLE t5(x);
538    INSERT INTO t5 VALUES(1);
539    INSERT INTO t5 VALUES(-99);
540    INSERT INTO t5 VALUES(10000);
541    SELECT sum(x) FROM t5;
542  }
543} {9902}
544do_test func-18.2 {
545  execsql {
546    INSERT INTO t5 VALUES(0.0);
547    SELECT sum(x) FROM t5;
548  }
549} {9902.0}
550
551# The sum of nothing is NULL.  But the sum of all NULLs is NULL.
552#
553# The TOTAL of nothing is 0.0.
554#
555do_test func-18.3 {
556  execsql {
557    DELETE FROM t5;
558    SELECT sum(x), total(x) FROM t5;
559  }
560} {{} 0.0}
561do_test func-18.4 {
562  execsql {
563    INSERT INTO t5 VALUES(NULL);
564    SELECT sum(x), total(x) FROM t5
565  }
566} {{} 0.0}
567do_test func-18.5 {
568  execsql {
569    INSERT INTO t5 VALUES(NULL);
570    SELECT sum(x), total(x) FROM t5
571  }
572} {{} 0.0}
573do_test func-18.6 {
574  execsql {
575    INSERT INTO t5 VALUES(123);
576    SELECT sum(x), total(x) FROM t5
577  }
578} {123 123.0}
579
580# Ticket #1664, #1669, #1670, #1674: An integer overflow on SUM causes
581# an error. The non-standard TOTAL() function continues to give a helpful
582# result.
583#
584do_test func-18.10 {
585  execsql {
586    CREATE TABLE t6(x INTEGER);
587    INSERT INTO t6 VALUES(1);
588    INSERT INTO t6 VALUES(1<<62);
589    SELECT sum(x) - ((1<<62)+1) from t6;
590  }
591} 0
592do_test func-18.11 {
593  execsql {
594    SELECT typeof(sum(x)) FROM t6
595  }
596} integer
597do_test func-18.12 {
598  catchsql {
599    INSERT INTO t6 VALUES(1<<62);
600    SELECT sum(x) - ((1<<62)*2.0+1) from t6;
601  }
602} {1 {integer overflow}}
603do_test func-18.13 {
604  execsql {
605    SELECT total(x) - ((1<<62)*2.0+1) FROM t6
606  }
607} 0.0
608do_test func-18.14 {
609  execsql {
610    SELECT sum(-9223372036854775805);
611  }
612} -9223372036854775805
613
614ifcapable compound&&subquery {
615
616do_test func-18.15 {
617  catchsql {
618    SELECT sum(x) FROM
619       (SELECT 9223372036854775807 AS x UNION ALL
620        SELECT 10 AS x);
621  }
622} {1 {integer overflow}}
623do_test func-18.16 {
624  catchsql {
625    SELECT sum(x) FROM
626       (SELECT 9223372036854775807 AS x UNION ALL
627        SELECT -10 AS x);
628  }
629} {0 9223372036854775797}
630do_test func-18.17 {
631  catchsql {
632    SELECT sum(x) FROM
633       (SELECT -9223372036854775807 AS x UNION ALL
634        SELECT 10 AS x);
635  }
636} {0 -9223372036854775797}
637do_test func-18.18 {
638  catchsql {
639    SELECT sum(x) FROM
640       (SELECT -9223372036854775807 AS x UNION ALL
641        SELECT -10 AS x);
642  }
643} {1 {integer overflow}}
644do_test func-18.19 {
645  catchsql {
646    SELECT sum(x) FROM (SELECT 9 AS x UNION ALL SELECT -10 AS x);
647  }
648} {0 -1}
649do_test func-18.20 {
650  catchsql {
651    SELECT sum(x) FROM (SELECT -9 AS x UNION ALL SELECT 10 AS x);
652  }
653} {0 1}
654do_test func-18.21 {
655  catchsql {
656    SELECT sum(x) FROM (SELECT -10 AS x UNION ALL SELECT 9 AS x);
657  }
658} {0 -1}
659do_test func-18.22 {
660  catchsql {
661    SELECT sum(x) FROM (SELECT 10 AS x UNION ALL SELECT -9 AS x);
662  }
663} {0 1}
664
665} ;# ifcapable compound&&subquery
666
667# Integer overflow on abs()
668#
669do_test func-18.31 {
670  catchsql {
671    SELECT abs(-9223372036854775807);
672  }
673} {0 9223372036854775807}
674do_test func-18.32 {
675  catchsql {
676    SELECT abs(-9223372036854775807-1);
677  }
678} {1 {integer overflow}}
679
680# The MATCH function exists but is only a stub and always throws an error.
681#
682do_test func-19.1 {
683  execsql {
684    SELECT match(a,b) FROM t1 WHERE 0;
685  }
686} {}
687do_test func-19.2 {
688  catchsql {
689    SELECT 'abc' MATCH 'xyz';
690  }
691} {1 {unable to use function MATCH in the requested context}}
692do_test func-19.3 {
693  catchsql {
694    SELECT 'abc' NOT MATCH 'xyz';
695  }
696} {1 {unable to use function MATCH in the requested context}}
697do_test func-19.4 {
698  catchsql {
699    SELECT match(1,2,3);
700  }
701} {1 {wrong number of arguments to function match()}}
702
703# Soundex tests.
704#
705if {![catch {db eval {SELECT soundex('hello')}}]} {
706  set i 0
707  foreach {name sdx} {
708    euler        E460
709    EULER        E460
710    Euler        E460
711    ellery       E460
712    gauss        G200
713    ghosh        G200
714    hilbert      H416
715    Heilbronn    H416
716    knuth        K530
717    kant         K530
718    Lloyd        L300
719    LADD         L300
720    Lukasiewicz  L222
721    Lissajous    L222
722    A            A000
723    12345        ?000
724  } {
725    incr i
726    do_test func-20.$i {
727      execsql {SELECT soundex($name)}
728    } $sdx
729  }
730}
731
732finish_test
733