# 2001 September 15

 #

 # The author disclaims copyright to this source code.  In place of

 # a legal notice, here is a blessing:

 #

 #    May you do good and not evil.

 #    May you find forgiveness for yourself and forgive others.

 #    May you share freely, never taking more than you give.

 #

 #***********************************************************************

 # This file implements regression tests for SQLite library.  The

 # focus of this file is testing built-in functions.

 #

 # $Id: func.test,v 1.86 2008/08/04 03:51:24 danielk1977 Exp $

 set testdir [file dirname $argv0]

 source $testdir/tester.tcl

 # Create a table to work with.

 #

 do_test func-0.0 {

   execsql {CREATE TABLE tbl1(t1 text)}

   foreach word {this program is free software} {

     execsql "INSERT INTO tbl1 VALUES('$word')"

   }

   execsql {SELECT t1 FROM tbl1 ORDER BY t1}

 } {free is program software this}

 do_test func-0.1 {

   execsql {

      CREATE TABLE t2(a);

      INSERT INTO t2 VALUES(1);

      INSERT INTO t2 VALUES(NULL);

      INSERT INTO t2 VALUES(345);

      INSERT INTO t2 VALUES(NULL);

      INSERT INTO t2 VALUES(67890);

      SELECT * FROM t2;

   }

 } {1 {} 345 {} 67890}

 # Check out the length() function

 #

 do_test func-1.0 {

   execsql {SELECT length(t1) FROM tbl1 ORDER BY t1}

 } {4 2 7 8 4}

 do_test func-1.1 {

   set r [catch {execsql {SELECT length(*) FROM tbl1 ORDER BY t1}} msg]

   lappend r $msg

 } {1 {wrong number of arguments to function length()}}

 do_test func-1.2 {

   set r [catch {execsql {SELECT length(t1,5) FROM tbl1 ORDER BY t1}} msg]

   lappend r $msg

 } {1 {wrong number of arguments to function length()}}

 do_test func-1.3 {

   execsql {SELECT length(t1), count(*) FROM tbl1 GROUP BY length(t1)

            ORDER BY length(t1)}

 } {2 1 4 2 7 1 8 1}

 do_test func-1.4 {

   execsql {SELECT coalesce(length(a),-1) FROM t2}

 } {1 -1 3 -1 5}

 # Check out the substr() function

 #

 do_test func-2.0 {

   execsql {SELECT substr(t1,1,2) FROM tbl1 ORDER BY t1}

 } {fr is pr so th}

 do_test func-2.1 {

   execsql {SELECT substr(t1,2,1) FROM tbl1 ORDER BY t1}

 } {r s r o h}

 do_test func-2.2 {

   execsql {SELECT substr(t1,3,3) FROM tbl1 ORDER BY t1}

 } {ee {} ogr ftw is}

 do_test func-2.3 {

   execsql {SELECT substr(t1,-1,1) FROM tbl1 ORDER BY t1}

 } {e s m e s}

 do_test func-2.4 {

   execsql {SELECT substr(t1,-1,2) FROM tbl1 ORDER BY t1}

 } {e s m e s}

 do_test func-2.5 {

   execsql {SELECT substr(t1,-2,1) FROM tbl1 ORDER BY t1}

 } {e i a r i}

 do_test func-2.6 {

   execsql {SELECT substr(t1,-2,2) FROM tbl1 ORDER BY t1}

 } {ee is am re is}

 do_test func-2.7 {

   execsql {SELECT substr(t1,-4,2) FROM tbl1 ORDER BY t1}

 } {fr {} gr wa th}

 do_test func-2.8 {

   execsql {SELECT t1 FROM tbl1 ORDER BY substr(t1,2,20)}

 } {this software free program is}

 do_test func-2.9 {

   execsql {SELECT substr(a,1,1) FROM t2}

 } {1 {} 3 {} 6}

 do_test func-2.10 {

   execsql {SELECT substr(a,2,2) FROM t2}

 } {{} {} 45 {} 78}

 # Only do the following tests if TCL has UTF-8 capabilities

 #

 if {"\u1234"!="u1234"} {

 # Put some UTF-8 characters in the database

 #

 do_test func-3.0 {

   execsql {DELETE FROM tbl1}

   foreach word "contains UTF-8 characters hi\u1234ho" {

     execsql "INSERT INTO tbl1 VALUES('$word')"

   }

   execsql {SELECT t1 FROM tbl1 ORDER BY t1}

 } "UTF-8 characters contains hi\u1234ho"

 do_test func-3.1 {

   execsql {SELECT length(t1) FROM tbl1 ORDER BY t1}

 } {5 10 8 5}

 do_test func-3.2 {

   execsql {SELECT substr(t1,1,2) FROM tbl1 ORDER BY t1}

 } {UT ch co hi}

 do_test func-3.3 {

   execsql {SELECT substr(t1,1,3) FROM tbl1 ORDER BY t1}

 } "UTF cha con hi\u1234"

 do_test func-3.4 {

   execsql {SELECT substr(t1,2,2) FROM tbl1 ORDER BY t1}

 } "TF ha on i\u1234"

 do_test func-3.5 {

   execsql {SELECT substr(t1,2,3) FROM tbl1 ORDER BY t1}

 } "TF- har ont i\u1234h"

 do_test func-3.6 {

   execsql {SELECT substr(t1,3,2) FROM tbl1 ORDER BY t1}

 } "F- ar nt \u1234h"

 do_test func-3.7 {

   execsql {SELECT substr(t1,4,2) FROM tbl1 ORDER BY t1}

 } "-8 ra ta ho"

 do_test func-3.8 {

   execsql {SELECT substr(t1,-1,1) FROM tbl1 ORDER BY t1}

 } "8 s s o"

 do_test func-3.9 {

   execsql {SELECT substr(t1,-3,2) FROM tbl1 ORDER BY t1}

 } "F- er in \u1234h"

 do_test func-3.10 {

   execsql {SELECT substr(t1,-4,3) FROM tbl1 ORDER BY t1}

 } "TF- ter ain i\u1234h"

 do_test func-3.99 {

   execsql {DELETE FROM tbl1}

   foreach word {this program is free software} {

     execsql "INSERT INTO tbl1 VALUES('$word')"

   }

   execsql {SELECT t1 FROM tbl1}

 } {this program is free software}

 } ;# End \u1234!=u1234

 # Test the abs() and round() functions.

 #

 do_test func-4.1 {

   execsql {

     CREATE TABLE t1(a,b,c);

     INSERT INTO t1 VALUES(1,2,3);

     INSERT INTO t1 VALUES(2,1.2345678901234,-12345.67890);

     INSERT INTO t1 VALUES(3,-2,-5);

   }

   catchsql {SELECT abs(a,b) FROM t1}

 } {1 {wrong number of arguments to function abs()}}

 do_test func-4.2 {

   catchsql {SELECT abs() FROM t1}

 } {1 {wrong number of arguments to function abs()}}

 do_test func-4.3 {

   catchsql {SELECT abs(b) FROM t1 ORDER BY a}

 } {0 {2 1.2345678901234 2}}

 do_test func-4.4 {

   catchsql {SELECT abs(c) FROM t1 ORDER BY a}

 } {0 {3 12345.6789 5}}

 do_test func-4.4.1 {

   execsql {SELECT abs(a) FROM t2}

 } {1 {} 345 {} 67890}

 do_test func-4.4.2 {

   execsql {SELECT abs(t1) FROM tbl1}

 } {0.0 0.0 0.0 0.0 0.0}

 do_test func-4.5 {

   catchsql {SELECT round(a,b,c) FROM t1}

 } {1 {wrong number of arguments to function round()}}

 do_test func-4.6 {

   catchsql {SELECT round(b,2) FROM t1 ORDER BY b}

 } {0 {-2.0 1.23 2.0}}

 do_test func-4.7 {

   catchsql {SELECT round(b,0) FROM t1 ORDER BY a}

 } {0 {2.0 1.0 -2.0}}

 do_test func-4.8 {

   catchsql {SELECT round(c) FROM t1 ORDER BY a}

 } {0 {3.0 -12346.0 -5.0}}

 do_test func-4.9 {

   catchsql {SELECT round(c,a) FROM t1 ORDER BY a}

 } {0 {3.0 -12345.68 -5.0}}

 do_test func-4.10 {

   catchsql {SELECT 'x' || round(c,a) || 'y' FROM t1 ORDER BY a}

 } {0 {x3.0y x-12345.68y x-5.0y}}

 do_test func-4.11 {

   catchsql {SELECT round() FROM t1 ORDER BY a}

 } {1 {wrong number of arguments to function round()}}

 do_test func-4.12 {

   execsql {SELECT coalesce(round(a,2),'nil') FROM t2}

 } {1.0 nil 345.0 nil 67890.0}

 do_test func-4.13 {

   execsql {SELECT round(t1,2) FROM tbl1}

 } {0.0 0.0 0.0 0.0 0.0}

 do_test func-4.14 {

   execsql {SELECT typeof(round(5.1,1));}

 } {real}

 do_test func-4.15 {

   execsql {SELECT typeof(round(5.1));}

 } {real}

 # Test the upper() and lower() functions

 #

 do_test func-5.1 {

   execsql {SELECT upper(t1) FROM tbl1}

 } {THIS PROGRAM IS FREE SOFTWARE}

 do_test func-5.2 {

   execsql {SELECT lower(upper(t1)) FROM tbl1}

 } {this program is free software}

 do_test func-5.3 {

   execsql {SELECT upper(a), lower(a) FROM t2}

 } {1 1 {} {} 345 345 {} {} 67890 67890}

 ifcapable !icu {

   do_test func-5.4 {

     catchsql {SELECT upper(a,5) FROM t2}

   } {1 {wrong number of arguments to function upper()}}

 }

 do_test func-5.5 {

   catchsql {SELECT upper(*) FROM t2}

 } {1 {wrong number of arguments to function upper()}}

 # Test the coalesce() and nullif() functions

 #

 do_test func-6.1 {

   execsql {SELECT coalesce(a,'xyz') FROM t2}

 } {1 xyz 345 xyz 67890}

 do_test func-6.2 {

   execsql {SELECT coalesce(upper(a),'nil') FROM t2}

 } {1 nil 345 nil 67890}

 do_test func-6.3 {

   execsql {SELECT coalesce(nullif(1,1),'nil')}

 } {nil}

 do_test func-6.4 {

   execsql {SELECT coalesce(nullif(1,2),'nil')}

 } {1}

 do_test func-6.5 {

   execsql {SELECT coalesce(nullif(1,NULL),'nil')}

 } {1}

 # Test the last_insert_rowid() function

 #

 do_test func-7.1 {

   execsql {SELECT last_insert_rowid()}

 } [db last_insert_rowid]

 # Tests for aggregate functions and how they handle NULLs.

 #

 do_test func-8.1 {

   ifcapable explain {

     execsql {EXPLAIN SELECT sum(a) FROM t2;}

   }

   execsql {

     SELECT sum(a), count(a), round(avg(a),2), min(a), max(a), count(*) FROM t2;

   }

 } {68236 3 22745.33 1 67890 5}

 do_test func-8.2 {

   execsql {

     SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t2;

   }

 } {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}

 ifcapable tempdb {

   do_test func-8.3 {

     execsql {

       CREATE TEMP TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;

       SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;

     }

   } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}

 } else {

   do_test func-8.3 {

     execsql {

       CREATE TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;

       SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;

     }

   } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}

 }

 do_test func-8.4 {

   execsql {

     SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;

   }

 } {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}

 ifcapable compound {

   do_test func-8.5 {

     execsql {

       SELECT sum(x) FROM (SELECT '9223372036' || '854775807' AS x

                           UNION ALL SELECT -9223372036854775807)

     }

   } {0}

   do_test func-8.6 {

     execsql {

       SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775807' AS x

                           UNION ALL SELECT -9223372036854775807)

     }

   } {integer}

   do_test func-8.7 {

     execsql {

       SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775808' AS x

                           UNION ALL SELECT -9223372036854775807)

     }

   } {real}

   do_test func-8.8 {

     execsql {

       SELECT sum(x)>0.0 FROM (SELECT '9223372036' || '854775808' AS x

                           UNION ALL SELECT -9223372036850000000)

     }

   } {1}

 }

 # How do you test the random() function in a meaningful, deterministic way?

 #

 do_test func-9.1 {

   execsql {

     SELECT random() is not null;

   }

 } {1}

 do_test func-9.2 {

   execsql {

     SELECT typeof(random());

   }

 } {integer}

 do_test func-9.3 {

   execsql {

     SELECT randomblob(32) is not null;

   }

 } {1}

 do_test func-9.4 {

   execsql {

     SELECT typeof(randomblob(32));

   }

 } {blob}

 do_test func-9.5 {

   execsql {

     SELECT length(randomblob(32)), length(randomblob(-5)),

            length(randomblob(2000))

   }

 } {32 1 2000}

 # The "hex()" function was added in order to be able to render blobs

 # generated by randomblob().  So this seems like a good place to test

 # hex().

 #

 ifcapable bloblit {

   do_test func-9.10 {

     execsql {SELECT hex(x'00112233445566778899aAbBcCdDeEfF')}

   } {00112233445566778899AABBCCDDEEFF}

 }

 set encoding [db one {PRAGMA encoding}]

 if {$encoding=="UTF-16le"} {

   do_test func-9.11-utf16le {

     execsql {SELECT hex(replace('abcdefg','ef','12'))}

   } {6100620063006400310032006700}

   do_test func-9.12-utf16le {

     execsql {SELECT hex(replace('abcdefg','','12'))}

   } {{}}

   do_test func-9.13-utf16le {

     execsql {SELECT hex(replace('aabcdefg','a','aaa'))}

   } {610061006100610061006100620063006400650066006700}

 } elseif {$encoding=="UTF-8"} {

   do_test func-9.11-utf8 {

     execsql {SELECT hex(replace('abcdefg','ef','12'))}

   } {61626364313267}

   do_test func-9.12-utf8 {

     execsql {SELECT hex(replace('abcdefg','','12'))}

   } {{}}

   do_test func-9.13-utf8 {

     execsql {SELECT hex(replace('aabcdefg','a','aaa'))}

   } {616161616161626364656667}

 }

 # Use the "sqlite_register_test_function" TCL command which is part of

 # the text fixture in order to verify correct operation of some of

 # the user-defined SQL function APIs that are not used by the built-in

 # functions.

 #

 set ::DB [sqlite3_connection_pointer db]

 sqlite_register_test_function $::DB testfunc

 do_test func-10.1 {

   catchsql {

     SELECT testfunc(NULL,NULL);

   }

 } {1 {first argument should be one of: int int64 string double null value}}

 do_test func-10.2 {

   execsql {

     SELECT testfunc(

      'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',

      'int', 1234

     );

   }

 } {1234}

 do_test func-10.3 {

   execsql {

     SELECT testfunc(

      'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',

      'string', NULL

     );

   }

 } {{}}

 do_test func-10.4 {

   execsql {

     SELECT testfunc(

      'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',

      'double', 1.234

     );

   }

 } {1.234}

 do_test func-10.5 {

   execsql {

     SELECT testfunc(

      'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',

      'int', 1234,

      'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',

      'string', NULL,

      'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',

      'double', 1.234,

      'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',

      'int', 1234,

      'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',

      'string', NULL,

      'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',

      'double', 1.234

     );

   }

 } {1.234}

 # Test the built-in sqlite_version(*) SQL function.

 #

 do_test func-11.1 {

   execsql {

     SELECT sqlite_version(*);

   }

 } [sqlite3 -version]

 # Test that destructors passed to sqlite3 by calls to sqlite3_result_text()

 # etc. are called. These tests use two special user-defined functions

 # (implemented in func.c) only available in test builds. 

 #

 # Function test_destructor() takes one argument and returns a copy of the

 # text form of that argument. A destructor is associated with the return

 # value. Function test_destructor_count() returns the number of outstanding

 # destructor calls for values returned by test_destructor().

 #

 if {[db eval {PRAGMA encoding}]=="UTF-8"} {

   do_test func-12.1-utf8 {

     execsql {

       SELECT test_destructor('hello world'), test_destructor_count();

     }

   } {{hello world} 1}

 } else {

     ifcapable {utf16} {

       do_test func-12.1-utf16 {

         execsql {

           SELECT test_destructor16('hello world'), test_destructor_count();

         }

       } {{hello world} 1}

     }

 }

 do_test func-12.2 {

   execsql {

     SELECT test_destructor_count();

   }

 } {0}

 do_test func-12.3 {

   execsql {

     SELECT test_destructor('hello')||' world'

   }

 } {{hello world}}

 do_test func-12.4 {

   execsql {

     SELECT test_destructor_count();

   }

 } {0}

 do_test func-12.5 {

   execsql {

     CREATE TABLE t4(x);

     INSERT INTO t4 VALUES(test_destructor('hello'));

     INSERT INTO t4 VALUES(test_destructor('world'));

     SELECT min(test_destructor(x)), max(test_destructor(x)) FROM t4;

   }

 } {hello world}

 do_test func-12.6 {

   execsql {

     SELECT test_destructor_count();

   }

 } {0}

 do_test func-12.7 {

   execsql {

     DROP TABLE t4;

   }

 } {}

 # Test that the auxdata API for scalar functions works. This test uses

 # a special user-defined function only available in test builds,

 # test_auxdata(). Function test_auxdata() takes any number of arguments.

 do_test func-13.1 {

   execsql {

     SELECT test_auxdata('hello world');

   }

 } {0}

 do_test func-13.2 {

   execsql {

     CREATE TABLE t4(a, b);

     INSERT INTO t4 VALUES('abc', 'def');

     INSERT INTO t4 VALUES('ghi', 'jkl');

   }

 } {}

 do_test func-13.3 {

   execsql {

     SELECT test_auxdata('hello world') FROM t4;

   }

 } {0 1}

 do_test func-13.4 {

   execsql {

     SELECT test_auxdata('hello world', 123) FROM t4;

   }

 } {{0 0} {1 1}}

 do_test func-13.5 {

   execsql {

     SELECT test_auxdata('hello world', a) FROM t4;

   }

 } {{0 0} {1 0}}

 do_test func-13.6 {

   execsql {

     SELECT test_auxdata('hello'||'world', a) FROM t4;

   }

 } {{0 0} {1 0}}

 # Test that auxilary data is preserved between calls for SQL variables.

 do_test func-13.7 {

   set DB [sqlite3_connection_pointer db]

   set sql "SELECT test_auxdata( ? , a ) FROM t4;"

   set STMT [sqlite3_prepare $DB $sql -1 TAIL]

   sqlite3_bind_text $STMT 1 hello\000 -1

   set res [list]

   while { "SQLITE_ROW"==[sqlite3_step $STMT] } {

     lappend res [sqlite3_column_text $STMT 0]

   }

   lappend res [sqlite3_finalize $STMT]

 } {{0 0} {1 0} SQLITE_OK}

 # Make sure that a function with a very long name is rejected

 do_test func-14.1 {

   catch {

     db function [string repeat X 254] {return "hello"}

   } 

 } {0}

 do_test func-14.2 {

   catch {

     db function [string repeat X 256] {return "hello"}

   }

 } {1}

 do_test func-15.1 {

   catchsql {select test_error(NULL)}

 } {1 {}}

 do_test func-15.2 {

   catchsql {select test_error('this is the error message')}

 } {1 {this is the error message}}

 do_test func-15.3 {

   catchsql {select test_error('this is the error message',12)}

 } {1 {this is the error message}}

 do_test func-15.4 {

   db errorcode

 } {12}

 # Test the quote function for BLOB and NULL values.

 do_test func-16.1 {

   execsql {

     CREATE TABLE tbl2(a, b);

   }

   set STMT [sqlite3_prepare $::DB "INSERT INTO tbl2 VALUES(?, ?)" -1 TAIL]

   sqlite3_bind_blob $::STMT 1 abc 3

   sqlite3_step $::STMT

   sqlite3_finalize $::STMT

   execsql {

     SELECT quote(a), quote(b) FROM tbl2;

   }

 } {X'616263' NULL}

 # Correctly handle function error messages that include %.  Ticket #1354

 #

 do_test func-17.1 {

   proc testfunc1 args {error "Error %d with %s percents %p"}

   db function testfunc1 ::testfunc1

   catchsql {

     SELECT testfunc1(1,2,3);

   }

 } {1 {Error %d with %s percents %p}}

 # The SUM function should return integer results when all inputs are integer.

 #

 do_test func-18.1 {

   execsql {

     CREATE TABLE t5(x);

     INSERT INTO t5 VALUES(1);

     INSERT INTO t5 VALUES(-99);

     INSERT INTO t5 VALUES(10000);

     SELECT sum(x) FROM t5;

   }

 } {9902}

 do_test func-18.2 {

   execsql {

     INSERT INTO t5 VALUES(0.0);

     SELECT sum(x) FROM t5;

   }

 } {9902.0}

 # The sum of nothing is NULL.  But the sum of all NULLs is NULL.

 #

 # The TOTAL of nothing is 0.0.

 #

 do_test func-18.3 {

   execsql {

     DELETE FROM t5;

     SELECT sum(x), total(x) FROM t5;

   }

 } {{} 0.0}

 do_test func-18.4 {

   execsql {

     INSERT INTO t5 VALUES(NULL);

     SELECT sum(x), total(x) FROM t5

   }

 } {{} 0.0}

 do_test func-18.5 {

   execsql {

     INSERT INTO t5 VALUES(NULL);

     SELECT sum(x), total(x) FROM t5

   }

 } {{} 0.0}

 do_test func-18.6 {

   execsql {

     INSERT INTO t5 VALUES(123);

     SELECT sum(x), total(x) FROM t5

   }

 } {123 123.0}

 # Ticket #1664, #1669, #1670, #1674: An integer overflow on SUM causes

 # an error. The non-standard TOTAL() function continues to give a helpful

 # result.

 #

 do_test func-18.10 {

   execsql {

     CREATE TABLE t6(x INTEGER);

     INSERT INTO t6 VALUES(1);

     INSERT INTO t6 VALUES(1<<62);

     SELECT sum(x) - ((1<<62)+1) from t6;

   }

 } 0

 do_test func-18.11 {

   execsql {

     SELECT typeof(sum(x)) FROM t6

   }

 } integer

 do_test func-18.12 {

   catchsql {

     INSERT INTO t6 VALUES(1<<62);

     SELECT sum(x) - ((1<<62)*2.0+1) from t6;

   }

 } {1 {integer overflow}}

 do_test func-18.13 {

   execsql {

     SELECT total(x) - ((1<<62)*2.0+1) FROM t6

   }

 } 0.0

 do_test func-18.14 {

   execsql {

     SELECT sum(-9223372036854775805);

   }

 } -9223372036854775805

 ifcapable compound&&subquery {

 do_test func-18.15 {

   catchsql {

     SELECT sum(x) FROM 

        (SELECT 9223372036854775807 AS x UNION ALL

         SELECT 10 AS x);

   }

 } {1 {integer overflow}}

 do_test func-18.16 {

   catchsql {

     SELECT sum(x) FROM 

        (SELECT 9223372036854775807 AS x UNION ALL

         SELECT -10 AS x);

   }

 } {0 9223372036854775797}

 do_test func-18.17 {

   catchsql {

     SELECT sum(x) FROM 

        (SELECT -9223372036854775807 AS x UNION ALL

         SELECT 10 AS x);

   }

 } {0 -9223372036854775797}

 do_test func-18.18 {

   catchsql {

     SELECT sum(x) FROM 

        (SELECT -9223372036854775807 AS x UNION ALL

         SELECT -10 AS x);

   }

 } {1 {integer overflow}}

 do_test func-18.19 {

   catchsql {

     SELECT sum(x) FROM (SELECT 9 AS x UNION ALL SELECT -10 AS x);

   }

 } {0 -1}

 do_test func-18.20 {

   catchsql {

     SELECT sum(x) FROM (SELECT -9 AS x UNION ALL SELECT 10 AS x);

   }

 } {0 1}

 do_test func-18.21 {

   catchsql {

     SELECT sum(x) FROM (SELECT -10 AS x UNION ALL SELECT 9 AS x);

   }

 } {0 -1}

 do_test func-18.22 {

   catchsql {

     SELECT sum(x) FROM (SELECT 10 AS x UNION ALL SELECT -9 AS x);

   }

 } {0 1}

 } ;# ifcapable compound&&subquery

 # Integer overflow on abs()

 #

 do_test func-18.31 {

   catchsql {

     SELECT abs(-9223372036854775807);

   }

 } {0 9223372036854775807}

 do_test func-18.32 {

   catchsql {

     SELECT abs(-9223372036854775807-1);

   }

 } {1 {integer overflow}}

 # The MATCH function exists but is only a stub and always throws an error.

 #

 do_test func-19.1 {

   execsql {

     SELECT match(a,b) FROM t1 WHERE 0;

   }

 } {}

 do_test func-19.2 {

   catchsql {

     SELECT 'abc' MATCH 'xyz';

   }

 } {1 {unable to use function MATCH in the requested context}}

 do_test func-19.3 {

   catchsql {

     SELECT 'abc' NOT MATCH 'xyz';

   }

 } {1 {unable to use function MATCH in the requested context}}

 do_test func-19.4 {

   catchsql {

     SELECT match(1,2,3);

   }

 } {1 {wrong number of arguments to function match()}}

 # Soundex tests.

 #

 if {![catch {db eval {SELECT soundex('hello')}}]} {

   set i 0

   foreach {name sdx} {

     euler        E460

     EULER        E460

     Euler        E460

     ellery       E460

     gauss        G200

     ghosh        G200

     hilbert      H416

     Heilbronn    H416

     knuth        K530

     kant         K530

     Lloyd        L300

     LADD         L300

     Lukasiewicz  L222

     Lissajous    L222

     A            A000

     12345        ?000

   } {

     incr i

     do_test func-20.$i {

       execsql {SELECT soundex($name)}

     } $sdx

   }

 }

 # Tests of the REPLACE function.

 #

 do_test func-21.1 {

   catchsql {

     SELECT replace(1,2);

   }

 } {1 {wrong number of arguments to function replace()}}

 do_test func-21.2 {

   catchsql {

     SELECT replace(1,2,3,4);

   }

 } {1 {wrong number of arguments to function replace()}}

 do_test func-21.3 {

   execsql {

     SELECT typeof(replace("This is the main test string", NULL, "ALT"));

   }

 } {null}

 do_test func-21.4 {

   execsql {

     SELECT typeof(replace(NULL, "main", "ALT"));

   }

 } {null}

 do_test func-21.5 {

   execsql {

     SELECT typeof(replace("This is the main test string", "main", NULL));

   }

 } {null}

 do_test func-21.6 {

   execsql {

     SELECT replace("This is the main test string", "main", "ALT");

   }

 } {{This is the ALT test string}}

 do_test func-21.7 {

   execsql {

     SELECT replace("This is the main test string", "main", "larger-main");

   }

 } {{This is the larger-main test string}}

 do_test func-21.8 {

   execsql {

     SELECT replace("aaaaaaa", "a", "0123456789");

   }

 } {0123456789012345678901234567890123456789012345678901234567890123456789}

 ifcapable tclvar {

   do_test func-21.9 {

     # Attempt to exploit a buffer-overflow that at one time existed 

     # in the REPLACE function. 

     set ::str "[string repeat A 29998]CC[string repeat A 35537]"

     set ::rep [string repeat B 65536]

     execsql {

       SELECT LENGTH(REPLACE($::str, 'C', $::rep));

     }

   } [expr 29998 + 2*65536 + 35537]

 }

 # Tests for the TRIM, LTRIM and RTRIM functions.

 #

 do_test func-22.1 {

   catchsql {SELECT trim(1,2,3)}

 } {1 {wrong number of arguments to function trim()}}

 do_test func-22.2 {

   catchsql {SELECT ltrim(1,2,3)}

 } {1 {wrong number of arguments to function ltrim()}}

 do_test func-22.3 {

   catchsql {SELECT rtrim(1,2,3)}

 } {1 {wrong number of arguments to function rtrim()}}

 do_test func-22.4 {

   execsql {SELECT trim('  hi  ');}

 } {hi}

 do_test func-22.5 {

   execsql {SELECT ltrim('  hi  ');}

 } {{hi  }}

 do_test func-22.6 {

   execsql {SELECT rtrim('  hi  ');}

 } {{  hi}}

 do_test func-22.7 {

   execsql {SELECT trim('  hi  ','xyz');}

 } {{  hi  }}

 do_test func-22.8 {

   execsql {SELECT ltrim('  hi  ','xyz');}

 } {{  hi  }}

 do_test func-22.9 {

   execsql {SELECT rtrim('  hi  ','xyz');}

 } {{  hi  }}

 do_test func-22.10 {

   execsql {SELECT trim('xyxzy  hi  zzzy','xyz');}

 } {{  hi  }}

 do_test func-22.11 {

   execsql {SELECT ltrim('xyxzy  hi  zzzy','xyz');}

 } {{  hi  zzzy}}

 do_test func-22.12 {

   execsql {SELECT rtrim('xyxzy  hi  zzzy','xyz');}

 } {{xyxzy  hi  }}

 do_test func-22.13 {

   execsql {SELECT trim('  hi  ','');}

 } {{  hi  }}

 if {[db one {PRAGMA encoding}]=="UTF-8"} {

   do_test func-22.14 {

     execsql {SELECT hex(trim(x'c280e1bfbff48fbfbf6869',x'6162e1bfbfc280'))}

   } {F48FBFBF6869}

   do_test func-22.15 {

     execsql {SELECT hex(trim(x'6869c280e1bfbff48fbfbf61',

                              x'6162e1bfbfc280f48fbfbf'))}

   } {6869}

   do_test func-22.16 {

     execsql {SELECT hex(trim(x'ceb1ceb2ceb3',x'ceb1'));}

   } {CEB2CEB3}

 }

 do_test func-22.20 {

   execsql {SELECT typeof(trim(NULL));}

 } {null}

 do_test func-22.21 {

   execsql {SELECT typeof(trim(NULL,'xyz'));}

 } {null}

 do_test func-22.22 {

   execsql {SELECT typeof(trim('hello',NULL));}

 } {null}

 # This is to test the deprecated sqlite3_aggregate_count() API.

 #

 do_test func-23.1 {

   sqlite3_create_aggregate db

   execsql {

     SELECT legacy_count() FROM t6;

   }

 } {3}

 # The group_concat() function.

 #

 do_test func-24.1 {

   execsql {

     SELECT group_concat(t1) FROM tbl1

   }

 } {this,program,is,free,software}

 do_test func-24.2 {

   execsql {

     SELECT group_concat(t1,' ') FROM tbl1

   }

 } {{this program is free software}}

 do_test func-24.3 {

   execsql {

     SELECT group_concat(t1,' ' || rowid || ' ') FROM tbl1

   }

 } {{this 2 program 3 is 4 free 5 software}}

 do_test func-24.4 {

   execsql {

     SELECT group_concat(NULL,t1) FROM tbl1

   }

 } {{}}

 do_test func-24.5 {

   execsql {

     SELECT group_concat(t1,NULL) FROM tbl1

   }

 } {thisprogramisfreesoftware}

 do_test func-24.6 {

   execsql {

     SELECT 'BEGIN-'||group_concat(t1) FROM tbl1

   }

 } {BEGIN-this,program,is,free,software}

 unset -nocomplain midargs

 set midargs {}

 unset -nocomplain midres

 set midres {}

 unset -nocomplain result

 for {set i 1} {$i<[sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG -1]-1} {incr i} {

   append midargs ,'/$i'

   append midres /$i

   set result \

      "this$midres:program$midres:is$midres:free$midres:software$midres"

   set sql "SELECT group_concat(t1$midargs,':') FROM tbl1"

   do_test func-24.7.$i {

      db eval $::sql

   } $result

 }

 # Use the test_isolation function to make sure that type conversions

 # on function arguments do not effect subsequent arguments.

 #

 do_test func-25.1 {

   execsql {SELECT test_isolation(t1,t1) FROM tbl1}

 } {this program is free software}

 # Try to misuse the sqlite3_create_function() interface.  Verify that

 # errors are returned.

 #

 do_test func-26.1 {

   abuse_create_function db

 } {}

 # The previous test (func-26.1) registered a function with a very long

 # function name that takes many arguments and always returns NULL.  Verify

 # that this function works correctly.

 #

 do_test func-26.2 {

   set a {}

   for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG} {incr i} {

     lappend a $i

   }

   db eval "

      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 ,]);

   "

 } {{}}

 do_test func-26.3 {

   set a {}

   for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG+1} {incr i} {

     lappend a $i

   }

   catchsql "

      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 {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}}

 do_test func-26.4 {

   set a {}

   for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG-1} {incr i} {

     lappend a $i

   }

   catchsql "

      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 {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()}}

 do_test func-26.5 {

   catchsql "

      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 {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}}

 do_test func-26.6 {

   catchsql "

      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 {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}}

 finish_test