Upgrade our sqlite to 3.6.1, with the local changes made by Gears. I'm

checking in the full sqlite tree to make upstream merges easier.  This means
we'll have generated sources split out from the originals.

One important change this makes is that "BEGIN" now defaults to "BEGIN
IMMEDIATE" rather than "BEGIN DEFERRED".  This doesn't affect us because we
don't use unqualified BEGIN statements.

The full CL is too big for Rietveld.  I'm splitting it into 2.  This one is
reviewable.  The other CL is just a fresh drop of:
//depot/googleclient/gears/opensource/third_party/sqlite_google
Review URL: http://codereview.chromium.org/15067

git-svn-id: svn://svn.chromium.org/chrome/trunk/src@7623 0039d316-1c4b-4281-b951-d872f2087c98

1 files changed, 1032 insertions, 0 deletions

diff --git a/third_party/sqlite/test/func.test b/third_party/sqlite/test/func.test
new file mode 100755
index 0000000..b61593f
--- /dev/null
+++ b/third_party/sqlite/test/func.test
@@ -0,0 +1,1032 @@
+# 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