# 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.9 2002/03/02 17:04:09 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 length(a) FROM t2} } {1 0 3 0 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} } "characters contains hi\u1234ho UTF-8" do_test func-3.1 { execsql {SELECT length(t1) FROM tbl1 ORDER BY t1} } {10 8 5 5} do_test func-3.2 { execsql {SELECT substr(t1,1,2) FROM tbl1 ORDER BY t1} } {ch co hi UT} do_test func-3.3 { execsql {SELECT substr(t1,1,3) FROM tbl1 ORDER BY t1} } "cha con hi\u1234 UTF" do_test func-3.4 { execsql {SELECT substr(t1,2,2) FROM tbl1 ORDER BY t1} } "ha on i\u1234 TF" do_test func-3.5 { execsql {SELECT substr(t1,2,3) FROM tbl1 ORDER BY t1} } "har ont i\u1234h TF-" do_test func-3.6 { execsql {SELECT substr(t1,3,2) FROM tbl1 ORDER BY t1} } "ar nt \u1234h F-" do_test func-3.7 { execsql {SELECT substr(t1,4,2) FROM tbl1 ORDER BY t1} } "ra ta ho -8" do_test func-3.8 { execsql {SELECT substr(t1,-1,1) FROM tbl1 ORDER BY t1} } "s s o 8" do_test func-3.9 { execsql {SELECT substr(t1,-3,2) FROM tbl1 ORDER BY t1} } "er in \u1234h F-" do_test func-3.10 { execsql {SELECT substr(t1,-4,3) FROM tbl1 ORDER BY t1} } "ter ain i\u1234h TF-" 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 round(a,2) FROM t2} } {1.00 0.00 345.00 0.00 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() function # 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} finish_test