1 files changed, 348 insertions, 0 deletions
diff --git a/usr/src/lib/libsqlite/test/func.test b/usr/src/lib/libsqlite/test/func.test
new file mode 100644
index 0000000000..521e6af45f
--- /dev/null
+++ b/usr/src/lib/libsqlite/test/func.test
@@ -0,0 +1,348 @@
+
+#pragma ident	"%Z%%M%	%I%	%E% SMI"
+
+# 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.16.2.2 2004/07/18 21:14:05 drh 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 and
+# the UTF-8 encoding is turned on in the SQLite library.
+#
+if {[sqlite -encoding]=="UTF-8" && "\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 [sqlite -encoding]==UTF-8 and \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.67890 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}
+} {this program is free software}
+
+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.00 1.23 2.00}}
+do_test func-4.7 {
+  catchsql {SELECT round(b,0) FROM t1 ORDER BY a}
+} {0 {2 1 -2}}
+do_test func-4.8 {
+  catchsql {SELECT round(c) FROM t1 ORDER BY a}
+} {0 {3 -12346 -5}}
+do_test func-4.9 {
+  catchsql {SELECT round(c,a) FROM t1 ORDER BY a}
+} {0 {3.0 -12345.68 -5.000}}
+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.000y}}
+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.00 nil 345.00 nil 67890.00}
+do_test func-4.13 {
+  execsql {SELECT round(t1,2) FROM tbl1}
+} {0.00 0.00 0.00 0.00 0.00}
+
+# 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}
+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 {
+  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}
+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}
+do_test func-8.4 {
+  execsql {
+    SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
+  }
+} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
+
+# How do you test the random() function in a meaningful, deterministic way?
+#
+do_test func-9.1 {
+  execsql {
+    SELECT random() is not null;
+  }
+} {1}
+
+# 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.
+#
+db close
+set ::DB [sqlite db test.db]
+sqlite_register_test_function $::DB testfunc
+do_test func-10.1 {
+  catchsql {
+    SELECT testfunc(NULL,NULL);
+  }
+} {1 {first argument to test function may not be NULL}}
+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(*);
+  }
+} [sqlite -version]
+
+finish_test