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