1# 2001 September 15 2# 3# The author disclaims copyright to this source code. In place of 4# a legal notice, here is a blessing: 5# 6# May you do good and not evil. 7# May you find forgiveness for yourself and forgive others. 8# May you share freely, never taking more than you give. 9# 10#*********************************************************************** 11# This file implements regression tests for SQLite library. The 12# focus of this file is testing built-in functions. 13# 14# $Id: func.test,v 1.16.2.2 2004/07/18 21:14:05 drh Exp $ 15 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18 19# Create a table to work with. 20# 21do_test func-0.0 { 22 execsql {CREATE TABLE tbl1(t1 text)} 23 foreach word {this program is free software} { 24 execsql "INSERT INTO tbl1 VALUES('$word')" 25 } 26 execsql {SELECT t1 FROM tbl1 ORDER BY t1} 27} {free is program software this} 28do_test func-0.1 { 29 execsql { 30 CREATE TABLE t2(a); 31 INSERT INTO t2 VALUES(1); 32 INSERT INTO t2 VALUES(NULL); 33 INSERT INTO t2 VALUES(345); 34 INSERT INTO t2 VALUES(NULL); 35 INSERT INTO t2 VALUES(67890); 36 SELECT * FROM t2; 37 } 38} {1 {} 345 {} 67890} 39 40# Check out the length() function 41# 42do_test func-1.0 { 43 execsql {SELECT length(t1) FROM tbl1 ORDER BY t1} 44} {4 2 7 8 4} 45do_test func-1.1 { 46 set r [catch {execsql {SELECT length(*) FROM tbl1 ORDER BY t1}} msg] 47 lappend r $msg 48} {1 {wrong number of arguments to function length()}} 49do_test func-1.2 { 50 set r [catch {execsql {SELECT length(t1,5) FROM tbl1 ORDER BY t1}} msg] 51 lappend r $msg 52} {1 {wrong number of arguments to function length()}} 53do_test func-1.3 { 54 execsql {SELECT length(t1), count(*) FROM tbl1 GROUP BY length(t1) 55 ORDER BY length(t1)} 56} {2 1 4 2 7 1 8 1} 57do_test func-1.4 { 58 execsql {SELECT coalesce(length(a),-1) FROM t2} 59} {1 -1 3 -1 5} 60 61# Check out the substr() function 62# 63do_test func-2.0 { 64 execsql {SELECT substr(t1,1,2) FROM tbl1 ORDER BY t1} 65} {fr is pr so th} 66do_test func-2.1 { 67 execsql {SELECT substr(t1,2,1) FROM tbl1 ORDER BY t1} 68} {r s r o h} 69do_test func-2.2 { 70 execsql {SELECT substr(t1,3,3) FROM tbl1 ORDER BY t1} 71} {ee {} ogr ftw is} 72do_test func-2.3 { 73 execsql {SELECT substr(t1,-1,1) FROM tbl1 ORDER BY t1} 74} {e s m e s} 75do_test func-2.4 { 76 execsql {SELECT substr(t1,-1,2) FROM tbl1 ORDER BY t1} 77} {e s m e s} 78do_test func-2.5 { 79 execsql {SELECT substr(t1,-2,1) FROM tbl1 ORDER BY t1} 80} {e i a r i} 81do_test func-2.6 { 82 execsql {SELECT substr(t1,-2,2) FROM tbl1 ORDER BY t1} 83} {ee is am re is} 84do_test func-2.7 { 85 execsql {SELECT substr(t1,-4,2) FROM tbl1 ORDER BY t1} 86} {fr {} gr wa th} 87do_test func-2.8 { 88 execsql {SELECT t1 FROM tbl1 ORDER BY substr(t1,2,20)} 89} {this software free program is} 90do_test func-2.9 { 91 execsql {SELECT substr(a,1,1) FROM t2} 92} {1 {} 3 {} 6} 93do_test func-2.10 { 94 execsql {SELECT substr(a,2,2) FROM t2} 95} {{} {} 45 {} 78} 96 97# Only do the following tests if TCL has UTF-8 capabilities and 98# the UTF-8 encoding is turned on in the SQLite library. 99# 100if {[sqlite -encoding]=="UTF-8" && "\u1234"!="u1234"} { 101 102# Put some UTF-8 characters in the database 103# 104do_test func-3.0 { 105 execsql {DELETE FROM tbl1} 106 foreach word "contains UTF-8 characters hi\u1234ho" { 107 execsql "INSERT INTO tbl1 VALUES('$word')" 108 } 109 execsql {SELECT t1 FROM tbl1 ORDER BY t1} 110} "UTF-8 characters contains hi\u1234ho" 111do_test func-3.1 { 112 execsql {SELECT length(t1) FROM tbl1 ORDER BY t1} 113} {5 10 8 5} 114do_test func-3.2 { 115 execsql {SELECT substr(t1,1,2) FROM tbl1 ORDER BY t1} 116} {UT ch co hi} 117do_test func-3.3 { 118 execsql {SELECT substr(t1,1,3) FROM tbl1 ORDER BY t1} 119} "UTF cha con hi\u1234" 120do_test func-3.4 { 121 execsql {SELECT substr(t1,2,2) FROM tbl1 ORDER BY t1} 122} "TF ha on i\u1234" 123do_test func-3.5 { 124 execsql {SELECT substr(t1,2,3) FROM tbl1 ORDER BY t1} 125} "TF- har ont i\u1234h" 126do_test func-3.6 { 127 execsql {SELECT substr(t1,3,2) FROM tbl1 ORDER BY t1} 128} "F- ar nt \u1234h" 129do_test func-3.7 { 130 execsql {SELECT substr(t1,4,2) FROM tbl1 ORDER BY t1} 131} "-8 ra ta ho" 132do_test func-3.8 { 133 execsql {SELECT substr(t1,-1,1) FROM tbl1 ORDER BY t1} 134} "8 s s o" 135do_test func-3.9 { 136 execsql {SELECT substr(t1,-3,2) FROM tbl1 ORDER BY t1} 137} "F- er in \u1234h" 138do_test func-3.10 { 139 execsql {SELECT substr(t1,-4,3) FROM tbl1 ORDER BY t1} 140} "TF- ter ain i\u1234h" 141do_test func-3.99 { 142 execsql {DELETE FROM tbl1} 143 foreach word {this program is free software} { 144 execsql "INSERT INTO tbl1 VALUES('$word')" 145 } 146 execsql {SELECT t1 FROM tbl1} 147} {this program is free software} 148 149} ;# End [sqlite -encoding]==UTF-8 and \u1234!=u1234 150 151# Test the abs() and round() functions. 152# 153do_test func-4.1 { 154 execsql { 155 CREATE TABLE t1(a,b,c); 156 INSERT INTO t1 VALUES(1,2,3); 157 INSERT INTO t1 VALUES(2,1.2345678901234,-12345.67890); 158 INSERT INTO t1 VALUES(3,-2,-5); 159 } 160 catchsql {SELECT abs(a,b) FROM t1} 161} {1 {wrong number of arguments to function abs()}} 162do_test func-4.2 { 163 catchsql {SELECT abs() FROM t1} 164} {1 {wrong number of arguments to function abs()}} 165do_test func-4.3 { 166 catchsql {SELECT abs(b) FROM t1 ORDER BY a} 167} {0 {2 1.2345678901234 2}} 168do_test func-4.4 { 169 catchsql {SELECT abs(c) FROM t1 ORDER BY a} 170} {0 {3 12345.67890 5}} 171do_test func-4.4.1 { 172 execsql {SELECT abs(a) FROM t2} 173} {1 {} 345 {} 67890} 174do_test func-4.4.2 { 175 execsql {SELECT abs(t1) FROM tbl1} 176} {this program is free software} 177 178do_test func-4.5 { 179 catchsql {SELECT round(a,b,c) FROM t1} 180} {1 {wrong number of arguments to function round()}} 181do_test func-4.6 { 182 catchsql {SELECT round(b,2) FROM t1 ORDER BY b} 183} {0 {-2.00 1.23 2.00}} 184do_test func-4.7 { 185 catchsql {SELECT round(b,0) FROM t1 ORDER BY a} 186} {0 {2 1 -2}} 187do_test func-4.8 { 188 catchsql {SELECT round(c) FROM t1 ORDER BY a} 189} {0 {3 -12346 -5}} 190do_test func-4.9 { 191 catchsql {SELECT round(c,a) FROM t1 ORDER BY a} 192} {0 {3.0 -12345.68 -5.000}} 193do_test func-4.10 { 194 catchsql {SELECT 'x' || round(c,a) || 'y' FROM t1 ORDER BY a} 195} {0 {x3.0y x-12345.68y x-5.000y}} 196do_test func-4.11 { 197 catchsql {SELECT round() FROM t1 ORDER BY a} 198} {1 {wrong number of arguments to function round()}} 199do_test func-4.12 { 200 execsql {SELECT coalesce(round(a,2),'nil') FROM t2} 201} {1.00 nil 345.00 nil 67890.00} 202do_test func-4.13 { 203 execsql {SELECT round(t1,2) FROM tbl1} 204} {0.00 0.00 0.00 0.00 0.00} 205 206# Test the upper() and lower() functions 207# 208do_test func-5.1 { 209 execsql {SELECT upper(t1) FROM tbl1} 210} {THIS PROGRAM IS FREE SOFTWARE} 211do_test func-5.2 { 212 execsql {SELECT lower(upper(t1)) FROM tbl1} 213} {this program is free software} 214do_test func-5.3 { 215 execsql {SELECT upper(a), lower(a) FROM t2} 216} {1 1 {} {} 345 345 {} {} 67890 67890} 217do_test func-5.4 { 218 catchsql {SELECT upper(a,5) FROM t2} 219} {1 {wrong number of arguments to function upper()}} 220do_test func-5.5 { 221 catchsql {SELECT upper(*) FROM t2} 222} {1 {wrong number of arguments to function upper()}} 223 224# Test the coalesce() and nullif() functions 225# 226do_test func-6.1 { 227 execsql {SELECT coalesce(a,'xyz') FROM t2} 228} {1 xyz 345 xyz 67890} 229do_test func-6.2 { 230 execsql {SELECT coalesce(upper(a),'nil') FROM t2} 231} {1 nil 345 nil 67890} 232do_test func-6.3 { 233 execsql {SELECT coalesce(nullif(1,1),'nil')} 234} {nil} 235do_test func-6.4 { 236 execsql {SELECT coalesce(nullif(1,2),'nil')} 237} {1} 238do_test func-6.5 { 239 execsql {SELECT coalesce(nullif(1,NULL),'nil')} 240} {1} 241 242 243# Test the last_insert_rowid() function 244# 245do_test func-7.1 { 246 execsql {SELECT last_insert_rowid()} 247} [db last_insert_rowid] 248 249# Tests for aggregate functions and how they handle NULLs. 250# 251do_test func-8.1 { 252 execsql { 253 SELECT sum(a), count(a), round(avg(a),2), min(a), max(a), count(*) FROM t2; 254 } 255} {68236 3 22745.33 1 67890 5} 256do_test func-8.2 { 257 execsql { 258 SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t2; 259 } 260} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP} 261do_test func-8.3 { 262 execsql { 263 CREATE TEMP TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC; 264 SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3; 265 } 266} {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP} 267do_test func-8.4 { 268 execsql { 269 SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3; 270 } 271} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP} 272 273# How do you test the random() function in a meaningful, deterministic way? 274# 275do_test func-9.1 { 276 execsql { 277 SELECT random() is not null; 278 } 279} {1} 280 281# Use the "sqlite_register_test_function" TCL command which is part of 282# the text fixture in order to verify correct operation of some of 283# the user-defined SQL function APIs that are not used by the built-in 284# functions. 285# 286db close 287set ::DB [sqlite db test.db] 288sqlite_register_test_function $::DB testfunc 289do_test func-10.1 { 290 catchsql { 291 SELECT testfunc(NULL,NULL); 292 } 293} {1 {first argument to test function may not be NULL}} 294do_test func-10.2 { 295 execsql { 296 SELECT testfunc( 297 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 298 'int', 1234 299 ); 300 } 301} {1234} 302do_test func-10.3 { 303 execsql { 304 SELECT testfunc( 305 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 306 'string', NULL 307 ); 308 } 309} {{}} 310do_test func-10.4 { 311 execsql { 312 SELECT testfunc( 313 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 314 'double', 1.234 315 ); 316 } 317} {1.234} 318do_test func-10.5 { 319 execsql { 320 SELECT testfunc( 321 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 322 'int', 1234, 323 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 324 'string', NULL, 325 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 326 'double', 1.234, 327 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 328 'int', 1234, 329 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 330 'string', NULL, 331 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 332 'double', 1.234 333 ); 334 } 335} {1.234} 336 337# Test the built-in sqlite_version(*) SQL function. 338# 339do_test func-11.1 { 340 execsql { 341 SELECT sqlite_version(*); 342 } 343} [sqlite -version] 344 345finish_test 346