2N/A#pragma ident "%Z%%M% %I% %E% SMI"
2N/A# The author disclaims copyright to this source code. In place of
2N/A# a legal notice, here is a blessing:
2N/A# May you do good and not evil.
2N/A# May you find forgiveness for yourself and forgive others.
2N/A# May you share freely, never taking more than you give.
2N/A#***********************************************************************
2N/A# This file implements regression tests for SQLite library. The
2N/A# focus of this file is testing built-in functions.
2N/Aset testdir [file dirname $argv0]
2N/A# Create a table to work with.
2N/A execsql {CREATE TABLE tbl1(t1 text)}
2N/A foreach word {this program is free software} {
2N/A execsql "INSERT INTO tbl1 VALUES('$word')"
2N/A execsql {SELECT t1 FROM tbl1 ORDER BY t1}
2N/A} {free is program software this}
2N/A INSERT INTO t2 VALUES(1);
2N/A INSERT INTO t2 VALUES(NULL);
2N/A INSERT INTO t2 VALUES(345);
2N/A INSERT INTO t2 VALUES(NULL);
2N/A INSERT INTO t2 VALUES(67890);
2N/A} {1 {} 345 {} 67890}
2N/A# Check out the length() function
2N/A execsql {SELECT length(t1) FROM tbl1 ORDER BY t1}
2N/A set r [catch {execsql {SELECT length(*) FROM tbl1 ORDER BY t1}} msg]
2N/A} {1 {wrong number of arguments to function length()}}
2N/A set r [catch {execsql {SELECT length(t1,5) FROM tbl1 ORDER BY t1}} msg]
2N/A} {1 {wrong number of arguments to function length()}}
2N/A execsql {SELECT length(t1), count(*) FROM tbl1 GROUP BY length(t1)
2N/A ORDER BY length(t1)}
2N/A execsql {SELECT coalesce(length(a),-1) FROM t2}
2N/A# Check out the substr() function
2N/A execsql {SELECT substr(t1,1,2) FROM tbl1 ORDER BY t1}
2N/A execsql {SELECT substr(t1,2,1) FROM tbl1 ORDER BY t1}
2N/A execsql {SELECT substr(t1,3,3) FROM tbl1 ORDER BY t1}
2N/A execsql {SELECT substr(t1,-1,1) FROM tbl1 ORDER BY t1}
2N/A execsql {SELECT substr(t1,-1,2) FROM tbl1 ORDER BY t1}
2N/A execsql {SELECT substr(t1,-2,1) FROM tbl1 ORDER BY t1}
2N/A execsql {SELECT substr(t1,-2,2) FROM tbl1 ORDER BY t1}
2N/A execsql {SELECT substr(t1,-4,2) FROM tbl1 ORDER BY t1}
2N/A execsql {SELECT t1 FROM tbl1 ORDER BY substr(t1,2,20)}
2N/A} {this software free program is}
2N/A execsql {SELECT substr(a,1,1) FROM t2}
2N/A execsql {SELECT substr(a,2,2) FROM t2}
2N/A# Only do the following tests if TCL has UTF-8 capabilities and
2N/A# the UTF-8 encoding is turned on in the SQLite library.
2N/Aif {[sqlite -encoding]=="UTF-8" && "\u1234"!="u1234"} {
2N/A# Put some UTF-8 characters in the database
2N/A execsql {DELETE FROM tbl1}
2N/A foreach word "contains UTF-8 characters hi\u1234ho" {
2N/A execsql "INSERT INTO tbl1 VALUES('$word')"
2N/A execsql {SELECT t1 FROM tbl1 ORDER BY t1}
2N/A} "UTF-8 characters contains hi\u1234ho"
2N/A execsql {SELECT length(t1) FROM tbl1 ORDER BY t1}
2N/A execsql {SELECT substr(t1,1,2) FROM tbl1 ORDER BY t1}
2N/A execsql {SELECT substr(t1,1,3) FROM tbl1 ORDER BY t1}
2N/A} "UTF cha con hi\u1234"
2N/A execsql {SELECT substr(t1,2,2) FROM tbl1 ORDER BY t1}
2N/A execsql {SELECT substr(t1,2,3) FROM tbl1 ORDER BY t1}
2N/A} "TF- har ont i\u1234h"
2N/A execsql {SELECT substr(t1,3,2) FROM tbl1 ORDER BY t1}
2N/A execsql {SELECT substr(t1,4,2) FROM tbl1 ORDER BY t1}
2N/A execsql {SELECT substr(t1,-1,1) FROM tbl1 ORDER BY t1}
2N/A execsql {SELECT substr(t1,-3,2) FROM tbl1 ORDER BY t1}
2N/A execsql {SELECT substr(t1,-4,3) FROM tbl1 ORDER BY t1}
2N/A} "TF- ter ain i\u1234h"
2N/A execsql {DELETE FROM tbl1}
2N/A foreach word {this program is free software} {
2N/A execsql "INSERT INTO tbl1 VALUES('$word')"
2N/A execsql {SELECT t1 FROM tbl1}
2N/A} {this program is free software}
2N/A} ;# End [sqlite -encoding]==UTF-8 and \u1234!=u1234
2N/A# Test the abs() and round() functions.
2N/A CREATE TABLE t1(a,b,c);
2N/A INSERT INTO t1 VALUES(1,2,3);
2N/A INSERT INTO t1 VALUES(2,1.2345678901234,-12345.67890);
2N/A INSERT INTO t1 VALUES(3,-2,-5);
2N/A catchsql {SELECT abs(a,b) FROM t1}
2N/A} {1 {wrong number of arguments to function abs()}}
2N/A catchsql {SELECT abs() FROM t1}
2N/A} {1 {wrong number of arguments to function abs()}}
2N/A catchsql {SELECT abs(b) FROM t1 ORDER BY a}
2N/A} {0 {2 1.2345678901234 2}}
2N/A catchsql {SELECT abs(c) FROM t1 ORDER BY a}
2N/A} {0 {3 12345.67890 5}}
2N/A execsql {SELECT abs(a) FROM t2}
2N/A} {1 {} 345 {} 67890}
2N/A execsql {SELECT abs(t1) FROM tbl1}
2N/A} {this program is free software}
2N/A catchsql {SELECT round(a,b,c) FROM t1}
2N/A} {1 {wrong number of arguments to function round()}}
2N/A catchsql {SELECT round(b,2) FROM t1 ORDER BY b}
2N/A} {0 {-2.00 1.23 2.00}}
2N/A catchsql {SELECT round(b,0) FROM t1 ORDER BY a}
2N/A catchsql {SELECT round(c) FROM t1 ORDER BY a}
2N/A catchsql {SELECT round(c,a) FROM t1 ORDER BY a}
2N/A} {0 {3.0 -12345.68 -5.000}}
2N/A catchsql {SELECT 'x' || round(c,a) || 'y' FROM t1 ORDER BY a}
2N/A} {0 {x3.0y x-12345.68y x-5.000y}}
2N/A catchsql {SELECT round() FROM t1 ORDER BY a}
2N/A} {1 {wrong number of arguments to function round()}}
2N/A execsql {SELECT coalesce(round(a,2),'nil') FROM t2}
2N/A} {1.00 nil 345.00 nil 67890.00}
2N/A execsql {SELECT round(t1,2) FROM tbl1}
2N/A} {0.00 0.00 0.00 0.00 0.00}
2N/A# Test the upper() and lower() functions
2N/A execsql {SELECT upper(t1) FROM tbl1}
2N/A} {THIS PROGRAM IS FREE SOFTWARE}
2N/A execsql {SELECT lower(upper(t1)) FROM tbl1}
2N/A} {this program is free software}
2N/A execsql {SELECT upper(a), lower(a) FROM t2}
2N/A} {1 1 {} {} 345 345 {} {} 67890 67890}
2N/A catchsql {SELECT upper(a,5) FROM t2}
2N/A} {1 {wrong number of arguments to function upper()}}
2N/A catchsql {SELECT upper(*) FROM t2}
2N/A} {1 {wrong number of arguments to function upper()}}
2N/A# Test the coalesce() and nullif() functions
2N/A execsql {SELECT coalesce(a,'xyz') FROM t2}
2N/A} {1 xyz 345 xyz 67890}
2N/A execsql {SELECT coalesce(upper(a),'nil') FROM t2}
2N/A} {1 nil 345 nil 67890}
2N/A execsql {SELECT coalesce(nullif(1,1),'nil')}
2N/A execsql {SELECT coalesce(nullif(1,2),'nil')}
2N/A execsql {SELECT coalesce(nullif(1,NULL),'nil')}
2N/A# Test the last_insert_rowid() function
2N/A execsql {SELECT last_insert_rowid()}
2N/A} [db last_insert_rowid]
2N/A# Tests for aggregate functions and how they handle NULLs.
2N/A SELECT sum(a), count(a), round(avg(a),2), min(a), max(a), count(*) FROM t2;
2N/A} {68236 3 22745.33 1 67890 5}
2N/A SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t2;
2N/A} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
2N/A CREATE TEMP TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;
2N/A SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
2N/A} {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
2N/A SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
2N/A} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
2N/A# How do you test the random() function in a meaningful, deterministic way?
2N/A SELECT random() is not null;
2N/A# Use the "sqlite_register_test_function" TCL command which is part of
2N/A# the text fixture in order to verify correct operation of some of
2N/A# the user-defined SQL function APIs that are not used by the built-in
2N/Asqlite_register_test_function $::DB testfunc
2N/A SELECT testfunc(NULL,NULL);
2N/A} {1 {first argument to test function may not be NULL}}
2N/A 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
2N/A 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
2N/A 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
2N/A 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
2N/A 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
2N/A 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
2N/A 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
2N/A 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
2N/A 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
2N/A# Test the built-in sqlite_version(*) SQL function.
2N/A SELECT sqlite_version(*);