sqlite3/test/func.test

   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.34 2005/03/29 03:11:00 danielk1977 Exp $
  15
  16 set testdir [file dirname $argv0]
  17 source $testdir/tester.tcl
  18
  19 # Create a table to work with.
  20 #
  21 do_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}
  28 do_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 #
  42 do_test func-1.0 {
  43   execsql {SELECT length(t1) FROM tbl1 ORDER BY t1}
  44 } {4 2 7 8 4}
  45 do_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()}}
  49 do_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()}}
  53 do_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}
  57 do_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 #
  63 do_test func-2.0 {
  64   execsql {SELECT substr(t1,1,2) FROM tbl1 ORDER BY t1}
  65 } {fr is pr so th}
  66 do_test func-2.1 {
  67   execsql {SELECT substr(t1,2,1) FROM tbl1 ORDER BY t1}
  68 } {r s r o h}
  69 do_test func-2.2 {
  70   execsql {SELECT substr(t1,3,3) FROM tbl1 ORDER BY t1}
  71 } {ee {} ogr ftw is}
  72 do_test func-2.3 {
  73   execsql {SELECT substr(t1,-1,1) FROM tbl1 ORDER BY t1}
  74 } {e s m e s}
  75 do_test func-2.4 {
  76   execsql {SELECT substr(t1,-1,2) FROM tbl1 ORDER BY t1}
  77 } {e s m e s}
  78 do_test func-2.5 {
  79   execsql {SELECT substr(t1,-2,1) FROM tbl1 ORDER BY t1}
  80 } {e i a r i}
  81 do_test func-2.6 {
  82   execsql {SELECT substr(t1,-2,2) FROM tbl1 ORDER BY t1}
  83 } {ee is am re is}
  84 do_test func-2.7 {
  85   execsql {SELECT substr(t1,-4,2) FROM tbl1 ORDER BY t1}
  86 } {fr {} gr wa th}
  87 do_test func-2.8 {
  88   execsql {SELECT t1 FROM tbl1 ORDER BY substr(t1,2,20)}
  89 } {this software free program is}
  90 do_test func-2.9 {
  91   execsql {SELECT substr(a,1,1) FROM t2}
  92 } {1 {} 3 {} 6}
  93 do_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
  98 #
  99 if {"\u1234"!="u1234"} {
 100
 101 # Put some UTF-8 characters in the database
 102 #
 103 do_test func-3.0 {
 104   execsql {DELETE FROM tbl1}
 105   foreach word "contains UTF-8 characters hi\u1234ho" {
 106     execsql "INSERT INTO tbl1 VALUES('$word')"
 107   }
 108   execsql {SELECT t1 FROM tbl1 ORDER BY t1}
 109 } "UTF-8 characters contains hi\u1234ho"
 110 do_test func-3.1 {
 111   execsql {SELECT length(t1) FROM tbl1 ORDER BY t1}
 112 } {5 10 8 5}
 113 do_test func-3.2 {
 114   execsql {SELECT substr(t1,1,2) FROM tbl1 ORDER BY t1}
 115 } {UT ch co hi}
 116 do_test func-3.3 {
 117   execsql {SELECT substr(t1,1,3) FROM tbl1 ORDER BY t1}
 118 } "UTF cha con hi\u1234"
 119 do_test func-3.4 {
 120   execsql {SELECT substr(t1,2,2) FROM tbl1 ORDER BY t1}
 121 } "TF ha on i\u1234"
 122 do_test func-3.5 {
 123   execsql {SELECT substr(t1,2,3) FROM tbl1 ORDER BY t1}
 124 } "TF- har ont i\u1234h"
 125 do_test func-3.6 {
 126   execsql {SELECT substr(t1,3,2) FROM tbl1 ORDER BY t1}
 127 } "F- ar nt \u1234h"
 128 do_test func-3.7 {
 129   execsql {SELECT substr(t1,4,2) FROM tbl1 ORDER BY t1}
 130 } "-8 ra ta ho"
 131 do_test func-3.8 {
 132   execsql {SELECT substr(t1,-1,1) FROM tbl1 ORDER BY t1}
 133 } "8 s s o"
 134 do_test func-3.9 {
 135   execsql {SELECT substr(t1,-3,2) FROM tbl1 ORDER BY t1}
 136 } "F- er in \u1234h"
 137 do_test func-3.10 {
 138   execsql {SELECT substr(t1,-4,3) FROM tbl1 ORDER BY t1}
 139 } "TF- ter ain i\u1234h"
 140 do_test func-3.99 {
 141   execsql {DELETE FROM tbl1}
 142   foreach word {this program is free software} {
 143     execsql "INSERT INTO tbl1 VALUES('$word')"
 144   }
 145   execsql {SELECT t1 FROM tbl1}
 146 } {this program is free software}
 147
 148 } ;# End \u1234!=u1234
 149
 150 # Test the abs() and round() functions.
 151 #
 152 do_test func-4.1 {
 153   execsql {
 154     CREATE TABLE t1(a,b,c);
 155     INSERT INTO t1 VALUES(1,2,3);
 156     INSERT INTO t1 VALUES(2,1.2345678901234,-12345.67890);
 157     INSERT INTO t1 VALUES(3,-2,-5);
 158   }
 159   catchsql {SELECT abs(a,b) FROM t1}
 160 } {1 {wrong number of arguments to function abs()}}
 161 do_test func-4.2 {
 162   catchsql {SELECT abs() FROM t1}
 163 } {1 {wrong number of arguments to function abs()}}
 164 do_test func-4.3 {
 165   catchsql {SELECT abs(b) FROM t1 ORDER BY a}
 166 } {0 {2 1.2345678901234 2}}
 167 do_test func-4.4 {
 168   catchsql {SELECT abs(c) FROM t1 ORDER BY a}
 169 } {0 {3 12345.6789 5}}
 170 do_test func-4.4.1 {
 171   execsql {SELECT abs(a) FROM t2}
 172 } {1 {} 345 {} 67890}
 173 do_test func-4.4.2 {
 174   execsql {SELECT abs(t1) FROM tbl1}
 175 } {0.0 0.0 0.0 0.0 0.0}
 176
 177 do_test func-4.5 {
 178   catchsql {SELECT round(a,b,c) FROM t1}
 179 } {1 {wrong number of arguments to function round()}}
 180 do_test func-4.6 {
 181   catchsql {SELECT round(b,2) FROM t1 ORDER BY b}
 182 } {0 {-2.00 1.23 2.00}}
 183 do_test func-4.7 {
 184   catchsql {SELECT round(b,0) FROM t1 ORDER BY a}
 185 } {0 {2 1 -2}}
 186 do_test func-4.8 {
 187   catchsql {SELECT round(c) FROM t1 ORDER BY a}
 188 } {0 {3 -12346 -5}}
 189 do_test func-4.9 {
 190   catchsql {SELECT round(c,a) FROM t1 ORDER BY a}
 191 } {0 {3.0 -12345.68 -5.000}}
 192 do_test func-4.10 {
 193   catchsql {SELECT 'x' || round(c,a) || 'y' FROM t1 ORDER BY a}
 194 } {0 {x3.0y x-12345.68y x-5.000y}}
 195 do_test func-4.11 {
 196   catchsql {SELECT round() FROM t1 ORDER BY a}
 197 } {1 {wrong number of arguments to function round()}}
 198 do_test func-4.12 {
 199   execsql {SELECT coalesce(round(a,2),'nil') FROM t2}
 200 } {1.00 nil 345.00 nil 67890.00}
 201 do_test func-4.13 {
 202   execsql {SELECT round(t1,2) FROM tbl1}
 203 } {0.00 0.00 0.00 0.00 0.00}
 204
 205 # Test the upper() and lower() functions
 206 #
 207 do_test func-5.1 {
 208   execsql {SELECT upper(t1) FROM tbl1}
 209 } {THIS PROGRAM IS FREE SOFTWARE}
 210 do_test func-5.2 {
 211   execsql {SELECT lower(upper(t1)) FROM tbl1}
 212 } {this program is free software}
 213 do_test func-5.3 {
 214   execsql {SELECT upper(a), lower(a) FROM t2}
 215 } {1 1 {} {} 345 345 {} {} 67890 67890}
 216 do_test func-5.4 {
 217   catchsql {SELECT upper(a,5) FROM t2}
 218 } {1 {wrong number of arguments to function upper()}}
 219 do_test func-5.5 {
 220   catchsql {SELECT upper(*) FROM t2}
 221 } {1 {wrong number of arguments to function upper()}}
 222
 223 # Test the coalesce() and nullif() functions
 224 #
 225 do_test func-6.1 {
 226   execsql {SELECT coalesce(a,'xyz') FROM t2}
 227 } {1 xyz 345 xyz 67890}
 228 do_test func-6.2 {
 229   execsql {SELECT coalesce(upper(a),'nil') FROM t2}
 230 } {1 nil 345 nil 67890}
 231 do_test func-6.3 {
 232   execsql {SELECT coalesce(nullif(1,1),'nil')}
 233 } {nil}
 234 do_test func-6.4 {
 235   execsql {SELECT coalesce(nullif(1,2),'nil')}
 236 } {1}
 237 do_test func-6.5 {
 238   execsql {SELECT coalesce(nullif(1,NULL),'nil')}
 239 } {1}
 240
 241
 242 # Test the last_insert_rowid() function
 243 #
 244 do_test func-7.1 {
 245   execsql {SELECT last_insert_rowid()}
 246 } [db last_insert_rowid]
 247
 248 # Tests for aggregate functions and how they handle NULLs.
 249 #
 250 do_test func-8.1 {
 251   ifcapable explain {
 252     execsql {EXPLAIN SELECT sum(a) FROM t2;}
 253   }
 254   execsql {
 255     SELECT sum(a), count(a), round(avg(a),2), min(a), max(a), count(*) FROM t2;
 256   }
 257 } {68236.0 3 22745.33 1 67890 5}
 258 do_test func-8.2 {
 259   execsql {
 260     SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t2;
 261   }
 262 } {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
 263
 264 ifcapable tempdb {
 265   do_test func-8.3 {
 266     execsql {
 267       CREATE TEMP TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;
 268       SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
 269     }
 270   } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
 271 } else {
 272   do_test func-8.3 {
 273     execsql {
 274       CREATE TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;
 275       SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
 276     }
 277   } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
 278 }
 279 do_test func-8.4 {
 280   execsql {
 281     SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
 282   }
 283 } {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
 284
 285 # How do you test the random() function in a meaningful, deterministic way?
 286 #
 287 do_test func-9.1 {
 288   execsql {
 289     SELECT random() is not null;
 290   }
 291 } {1}
 292
 293 # Use the "sqlite_register_test_function" TCL command which is part of
 294 # the text fixture in order to verify correct operation of some of
 295 # the user-defined SQL function APIs that are not used by the built-in
 296 # functions.
 297 #
 298 db close
 299 set ::DB [sqlite3 db test.db]
 300 sqlite_register_test_function $::DB testfunc
 301 do_test func-10.1 {
 302   catchsql {
 303     SELECT testfunc(NULL,NULL);
 304   }
 305 } {1 {first argument should be one of: int int64 string double null value}}
 306 do_test func-10.2 {
 307   execsql {
 308     SELECT testfunc(
 309      'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
 310      'int', 1234
 311     );
 312   }
 313 } {1234}
 314 do_test func-10.3 {
 315   execsql {
 316     SELECT testfunc(
 317      'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
 318      'string', NULL
 319     );
 320   }
 321 } {{}}
 322 do_test func-10.4 {
 323   execsql {
 324     SELECT testfunc(
 325      'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
 326      'double', 1.234
 327     );
 328   }
 329 } {1.234}
 330 do_test func-10.5 {
 331   execsql {
 332     SELECT testfunc(
 333      'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
 334      'int', 1234,
 335      'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
 336      'string', NULL,
 337      'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
 338      'double', 1.234,
 339      'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
 340      'int', 1234,
 341      'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
 342      'string', NULL,
 343      'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
 344      'double', 1.234
 345     );
 346   }
 347 } {1.234}
 348
 349 # Test the built-in sqlite_version(*) SQL function.
 350 #
 351 do_test func-11.1 {
 352   execsql {
 353     SELECT sqlite_version(*);
 354   }
 355 } [sqlite3 -version]
 356
 357 # Test that destructors passed to sqlite3 by calls to sqlite3_result_text()
 358 # etc. are called. These tests use two special user-defined functions
 359 # (implemented in func.c) only available in test builds.
 360 #
 361 # Function test_destructor() takes one argument and returns a copy of the
 362 # text form of that argument. A destructor is associated with the return
 363 # value. Function test_destructor_count() returns the number of outstanding
 364 # destructor calls for values returned by test_destructor().
 365 #
 366 do_test func-12.1 {
 367   execsql {
 368     SELECT test_destructor('hello world'), test_destructor_count();
 369   }
 370 } {{hello world} 1}
 371 do_test func-12.2 {
 372   execsql {
 373     SELECT test_destructor_count();
 374   }
 375 } {0}
 376 do_test func-12.3 {
 377   execsql {
 378     SELECT test_destructor('hello')||' world', test_destructor_count();
 379   }
 380 } {{hello world} 0}
 381 do_test func-12.4 {
 382   execsql {
 383     SELECT test_destructor_count();
 384   }
 385 } {0}
 386 do_test func-12.5 {
 387   execsql {
 388     CREATE TABLE t4(x);
 389     INSERT INTO t4 VALUES(test_destructor('hello'));
 390     INSERT INTO t4 VALUES(test_destructor('world'));
 391     SELECT min(test_destructor(x)), max(test_destructor(x)) FROM t4;
 392   }
 393 } {hello world}
 394 do_test func-12.6 {
 395   execsql {
 396     SELECT test_destructor_count();
 397   }
 398 } {0}
 399 do_test func-12.7 {
 400   execsql {
 401     DROP TABLE t4;
 402   }
 403 } {}
 404
 405 # Test that the auxdata API for scalar functions works. This test uses
 406 # a special user-defined function only available in test builds,
 407 # test_auxdata(). Function test_auxdata() takes any number of arguments.
 408 do_test func-13.1 {
 409   execsql {
 410     SELECT test_auxdata('hello world');
 411   }
 412 } {0}
 413
 414 do_test func-13.2 {
 415   execsql {
 416     CREATE TABLE t4(a, b);
 417     INSERT INTO t4 VALUES('abc', 'def');
 418     INSERT INTO t4 VALUES('ghi', 'jkl');
 419   }
 420 } {}
 421 do_test func-13.3 {
 422   execsql {
 423     SELECT test_auxdata('hello world') FROM t4;
 424   }
 425 } {0 1}
 426 do_test func-13.4 {
 427   execsql {
 428     SELECT test_auxdata('hello world', 123) FROM t4;
 429   }
 430 } {{0 0} {1 1}}
 431 do_test func-13.5 {
 432   execsql {
 433     SELECT test_auxdata('hello world', a) FROM t4;
 434   }
 435 } {{0 0} {1 0}}
 436 do_test func-13.6 {
 437   execsql {
 438     SELECT test_auxdata('hello'||'world', a) FROM t4;
 439   }
 440 } {{0 0} {1 0}}
 441
 442 # Test that auxilary data is preserved between calls for SQL variables.
 443 do_test func-13.7 {
 444   db close
 445   set DB [sqlite3 db test.db]
 446   set sql "SELECT test_auxdata( ? , a ) FROM t4;"
 447   set STMT [sqlite3_prepare $DB $sql -1 TAIL]
 448   sqlite3_bind_text $STMT 1 hello -1
 449   set res [list]
 450   while { "SQLITE_ROW"==[sqlite3_step $STMT] } {
 451     lappend res [sqlite3_column_text $STMT 0]
 452   }
 453   lappend res [sqlite3_finalize $STMT]
 454 } {{0 0} {1 0} SQLITE_OK}
 455
 456 # Make sure that a function with a very long name is rejected
 457 do_test func-14.1 {
 458   catch {
 459     db function [string repeat X 254] {return "hello"}
 460   }
 461 } {0}
 462 do_test func-14.2 {
 463   catch {
 464     db function [string repeat X 256] {return "hello"}
 465   }
 466 } {1}
 467
 468 do_test func-15.1 {
 469   catchsql {
 470     select test_error(NULL);
 471   }
 472 } {1 {user function error}}
 473
 474 # Test the quote function for BLOB and NULL values.
 475 do_test func-16.1 {
 476   execsql {
 477     CREATE TABLE tbl2(a, b);
 478   }
 479   set STMT [sqlite3_prepare $::DB "INSERT INTO tbl2 VALUES(?, ?)" -1 TAIL]
 480   sqlite3_bind_blob $::STMT 1 abc 3
 481   sqlite3_step $::STMT
 482   sqlite3_finalize $::STMT
 483   execsql {
 484     SELECT quote(a), quote(b) FROM tbl2;
 485   }
 486 } {X'616263' NULL}
 487
 488 finish_test
 489