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.86 2008/08/04 03:51:24 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.0 1.23 2.0}}

   183 do_test func-4.7 {

   184   catchsql {SELECT round(b,0) FROM t1 ORDER BY a}

   185 } {0 {2.0 1.0 -2.0}}

   186 do_test func-4.8 {

   187   catchsql {SELECT round(c) FROM t1 ORDER BY a}

   188 } {0 {3.0 -12346.0 -5.0}}

   189 do_test func-4.9 {

   190   catchsql {SELECT round(c,a) FROM t1 ORDER BY a}

   191 } {0 {3.0 -12345.68 -5.0}}

   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.0y}}

   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.0 nil 345.0 nil 67890.0}

   201 do_test func-4.13 {

   202   execsql {SELECT round(t1,2) FROM tbl1}

   203 } {0.0 0.0 0.0 0.0 0.0}

   204 do_test func-4.14 {

   205   execsql {SELECT typeof(round(5.1,1));}

   206 } {real}

   207 do_test func-4.15 {

   208   execsql {SELECT typeof(round(5.1));}

   209 } {real}

   210 

   211 

   212 # Test the upper() and lower() functions

   213 #

   214 do_test func-5.1 {

   215   execsql {SELECT upper(t1) FROM tbl1}

   216 } {THIS PROGRAM IS FREE SOFTWARE}

   217 do_test func-5.2 {

   218   execsql {SELECT lower(upper(t1)) FROM tbl1}

   219 } {this program is free software}

   220 do_test func-5.3 {

   221   execsql {SELECT upper(a), lower(a) FROM t2}

   222 } {1 1 {} {} 345 345 {} {} 67890 67890}

   223 ifcapable !icu {

   224   do_test func-5.4 {

   225     catchsql {SELECT upper(a,5) FROM t2}

   226   } {1 {wrong number of arguments to function upper()}}

   227 }

   228 do_test func-5.5 {

   229   catchsql {SELECT upper(*) FROM t2}

   230 } {1 {wrong number of arguments to function upper()}}

   231 

   232 # Test the coalesce() and nullif() functions

   233 #

   234 do_test func-6.1 {

   235   execsql {SELECT coalesce(a,'xyz') FROM t2}

   236 } {1 xyz 345 xyz 67890}

   237 do_test func-6.2 {

   238   execsql {SELECT coalesce(upper(a),'nil') FROM t2}

   239 } {1 nil 345 nil 67890}

   240 do_test func-6.3 {

   241   execsql {SELECT coalesce(nullif(1,1),'nil')}

   242 } {nil}

   243 do_test func-6.4 {

   244   execsql {SELECT coalesce(nullif(1,2),'nil')}

   245 } {1}

   246 do_test func-6.5 {

   247   execsql {SELECT coalesce(nullif(1,NULL),'nil')}

   248 } {1}

   249 

   250 

   251 # Test the last_insert_rowid() function

   252 #

   253 do_test func-7.1 {

   254   execsql {SELECT last_insert_rowid()}

   255 } [db last_insert_rowid]

   256 

   257 # Tests for aggregate functions and how they handle NULLs.

   258 #

   259 do_test func-8.1 {

   260   ifcapable explain {

   261     execsql {EXPLAIN SELECT sum(a) FROM t2;}

   262   }

   263   execsql {

   264     SELECT sum(a), count(a), round(avg(a),2), min(a), max(a), count(*) FROM t2;

   265   }

   266 } {68236 3 22745.33 1 67890 5}

   267 do_test func-8.2 {

   268   execsql {

   269     SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t2;

   270   }

   271 } {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}

   272 

   273 ifcapable tempdb {

   274   do_test func-8.3 {

   275     execsql {

   276       CREATE TEMP TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;

   277       SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;

   278     }

   279   } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}

   280 } else {

   281   do_test func-8.3 {

   282     execsql {

   283       CREATE TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;

   284       SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;

   285     }

   286   } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}

   287 }

   288 do_test func-8.4 {

   289   execsql {

   290     SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;

   291   }

   292 } {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}

   293 ifcapable compound {

   294   do_test func-8.5 {

   295     execsql {

   296       SELECT sum(x) FROM (SELECT '9223372036' || '854775807' AS x

   297                           UNION ALL SELECT -9223372036854775807)

   298     }

   299   } {0}

   300   do_test func-8.6 {

   301     execsql {

   302       SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775807' AS x

   303                           UNION ALL SELECT -9223372036854775807)

   304     }

   305   } {integer}

   306   do_test func-8.7 {

   307     execsql {

   308       SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775808' AS x

   309                           UNION ALL SELECT -9223372036854775807)

   310     }

   311   } {real}

   312   do_test func-8.8 {

   313     execsql {

   314       SELECT sum(x)>0.0 FROM (SELECT '9223372036' || '854775808' AS x

   315                           UNION ALL SELECT -9223372036850000000)

   316     }

   317   } {1}

   318 }

   319 

   320 # How do you test the random() function in a meaningful, deterministic way?

   321 #

   322 do_test func-9.1 {

   323   execsql {

   324     SELECT random() is not null;

   325   }

   326 } {1}

   327 do_test func-9.2 {

   328   execsql {

   329     SELECT typeof(random());

   330   }

   331 } {integer}

   332 do_test func-9.3 {

   333   execsql {

   334     SELECT randomblob(32) is not null;

   335   }

   336 } {1}

   337 do_test func-9.4 {

   338   execsql {

   339     SELECT typeof(randomblob(32));

   340   }

   341 } {blob}

   342 do_test func-9.5 {

   343   execsql {

   344     SELECT length(randomblob(32)), length(randomblob(-5)),

   345            length(randomblob(2000))

   346   }

   347 } {32 1 2000}

   348 

   349 # The "hex()" function was added in order to be able to render blobs

   350 # generated by randomblob().  So this seems like a good place to test

   351 # hex().

   352 #

   353 ifcapable bloblit {

   354   do_test func-9.10 {

   355     execsql {SELECT hex(x'00112233445566778899aAbBcCdDeEfF')}

   356   } {00112233445566778899AABBCCDDEEFF}

   357 }

   358 set encoding [db one {PRAGMA encoding}]

   359 if {$encoding=="UTF-16le"} {

   360   do_test func-9.11-utf16le {

   361     execsql {SELECT hex(replace('abcdefg','ef','12'))}

   362   } {6100620063006400310032006700}

   363   do_test func-9.12-utf16le {

   364     execsql {SELECT hex(replace('abcdefg','','12'))}

   365   } {{}}

   366   do_test func-9.13-utf16le {

   367     execsql {SELECT hex(replace('aabcdefg','a','aaa'))}

   368   } {610061006100610061006100620063006400650066006700}

   369 } elseif {$encoding=="UTF-8"} {

   370   do_test func-9.11-utf8 {

   371     execsql {SELECT hex(replace('abcdefg','ef','12'))}

   372   } {61626364313267}

   373   do_test func-9.12-utf8 {

   374     execsql {SELECT hex(replace('abcdefg','','12'))}

   375   } {{}}

   376   do_test func-9.13-utf8 {

   377     execsql {SELECT hex(replace('aabcdefg','a','aaa'))}

   378   } {616161616161626364656667}

   379 }

   380   

   381 # Use the "sqlite_register_test_function" TCL command which is part of

   382 # the text fixture in order to verify correct operation of some of

   383 # the user-defined SQL function APIs that are not used by the built-in

   384 # functions.

   385 #

   386 set ::DB [sqlite3_connection_pointer db]

   387 sqlite_register_test_function $::DB testfunc

   388 do_test func-10.1 {

   389   catchsql {

   390     SELECT testfunc(NULL,NULL);

   391   }

   392 } {1 {first argument should be one of: int int64 string double null value}}

   393 do_test func-10.2 {

   394   execsql {

   395     SELECT testfunc(

   396      'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',

   397      'int', 1234

   398     );

   399   }

   400 } {1234}

   401 do_test func-10.3 {

   402   execsql {

   403     SELECT testfunc(

   404      'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',

   405      'string', NULL

   406     );

   407   }

   408 } {{}}

   409 do_test func-10.4 {

   410   execsql {

   411     SELECT testfunc(

   412      'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',

   413      'double', 1.234

   414     );

   415   }

   416 } {1.234}

   417 do_test func-10.5 {

   418   execsql {

   419     SELECT testfunc(

   420      'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',

   421      'int', 1234,

   422      'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',

   423      'string', NULL,

   424      'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',

   425      'double', 1.234,

   426      'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',

   427      'int', 1234,

   428      'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',

   429      'string', NULL,

   430      'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',

   431      'double', 1.234

   432     );

   433   }

   434 } {1.234}

   435 

   436 # Test the built-in sqlite_version(*) SQL function.

   437 #

   438 do_test func-11.1 {

   439   execsql {

   440     SELECT sqlite_version(*);

   441   }

   442 } [sqlite3 -version]

   443 

   444 # Test that destructors passed to sqlite3 by calls to sqlite3_result_text()

   445 # etc. are called. These tests use two special user-defined functions

   446 # (implemented in func.c) only available in test builds. 

   447 #

   448 # Function test_destructor() takes one argument and returns a copy of the

   449 # text form of that argument. A destructor is associated with the return

   450 # value. Function test_destructor_count() returns the number of outstanding

   451 # destructor calls for values returned by test_destructor().

   452 #

   453 if {[db eval {PRAGMA encoding}]=="UTF-8"} {

   454   do_test func-12.1-utf8 {

   455     execsql {

   456       SELECT test_destructor('hello world'), test_destructor_count();

   457     }

   458   } {{hello world} 1}

   459 } else {

   460     ifcapable {utf16} {

   461       do_test func-12.1-utf16 {

   462         execsql {

   463           SELECT test_destructor16('hello world'), test_destructor_count();

   464         }

   465       } {{hello world} 1}

   466     }

   467 }

   468 do_test func-12.2 {

   469   execsql {

   470     SELECT test_destructor_count();

   471   }

   472 } {0}

   473 do_test func-12.3 {

   474   execsql {

   475     SELECT test_destructor('hello')||' world'

   476   }

   477 } {{hello world}}

   478 do_test func-12.4 {

   479   execsql {

   480     SELECT test_destructor_count();

   481   }

   482 } {0}

   483 do_test func-12.5 {

   484   execsql {

   485     CREATE TABLE t4(x);

   486     INSERT INTO t4 VALUES(test_destructor('hello'));

   487     INSERT INTO t4 VALUES(test_destructor('world'));

   488     SELECT min(test_destructor(x)), max(test_destructor(x)) FROM t4;

   489   }

   490 } {hello world}

   491 do_test func-12.6 {

   492   execsql {

   493     SELECT test_destructor_count();

   494   }

   495 } {0}

   496 do_test func-12.7 {

   497   execsql {

   498     DROP TABLE t4;

   499   }

   500 } {}

   501 

   502 

   503 # Test that the auxdata API for scalar functions works. This test uses

   504 # a special user-defined function only available in test builds,

   505 # test_auxdata(). Function test_auxdata() takes any number of arguments.

   506 do_test func-13.1 {

   507   execsql {

   508     SELECT test_auxdata('hello world');

   509   }

   510 } {0}

   511 

   512 do_test func-13.2 {

   513   execsql {

   514     CREATE TABLE t4(a, b);

   515     INSERT INTO t4 VALUES('abc', 'def');

   516     INSERT INTO t4 VALUES('ghi', 'jkl');

   517   }

   518 } {}

   519 do_test func-13.3 {

   520   execsql {

   521     SELECT test_auxdata('hello world') FROM t4;

   522   }

   523 } {0 1}

   524 do_test func-13.4 {

   525   execsql {

   526     SELECT test_auxdata('hello world', 123) FROM t4;

   527   }

   528 } {{0 0} {1 1}}

   529 do_test func-13.5 {

   530   execsql {

   531     SELECT test_auxdata('hello world', a) FROM t4;

   532   }

   533 } {{0 0} {1 0}}

   534 do_test func-13.6 {

   535   execsql {

   536     SELECT test_auxdata('hello'||'world', a) FROM t4;

   537   }

   538 } {{0 0} {1 0}}

   539 

   540 # Test that auxilary data is preserved between calls for SQL variables.

   541 do_test func-13.7 {

   542   set DB [sqlite3_connection_pointer db]

   543   set sql "SELECT test_auxdata( ? , a ) FROM t4;"

   544   set STMT [sqlite3_prepare $DB $sql -1 TAIL]

   545   sqlite3_bind_text $STMT 1 hello\000 -1

   546   set res [list]

   547   while { "SQLITE_ROW"==[sqlite3_step $STMT] } {

   548     lappend res [sqlite3_column_text $STMT 0]

   549   }

   550   lappend res [sqlite3_finalize $STMT]

   551 } {{0 0} {1 0} SQLITE_OK}

   552 

   553 # Make sure that a function with a very long name is rejected

   554 do_test func-14.1 {

   555   catch {

   556     db function [string repeat X 254] {return "hello"}

   557   } 

   558 } {0}

   559 do_test func-14.2 {

   560   catch {

   561     db function [string repeat X 256] {return "hello"}

   562   }

   563 } {1}

   564 

   565 do_test func-15.1 {

   566   catchsql {select test_error(NULL)}

   567 } {1 {}}

   568 do_test func-15.2 {

   569   catchsql {select test_error('this is the error message')}

   570 } {1 {this is the error message}}

   571 do_test func-15.3 {

   572   catchsql {select test_error('this is the error message',12)}

   573 } {1 {this is the error message}}

   574 do_test func-15.4 {

   575   db errorcode

   576 } {12}

   577 

   578 # Test the quote function for BLOB and NULL values.

   579 do_test func-16.1 {

   580   execsql {

   581     CREATE TABLE tbl2(a, b);

   582   }

   583   set STMT [sqlite3_prepare $::DB "INSERT INTO tbl2 VALUES(?, ?)" -1 TAIL]

   584   sqlite3_bind_blob $::STMT 1 abc 3

   585   sqlite3_step $::STMT

   586   sqlite3_finalize $::STMT

   587   execsql {

   588     SELECT quote(a), quote(b) FROM tbl2;

   589   }

   590 } {X'616263' NULL}

   591 

   592 # Correctly handle function error messages that include %.  Ticket #1354

   593 #

   594 do_test func-17.1 {

   595   proc testfunc1 args {error "Error %d with %s percents %p"}

   596   db function testfunc1 ::testfunc1

   597   catchsql {

   598     SELECT testfunc1(1,2,3);

   599   }

   600 } {1 {Error %d with %s percents %p}}

   601 

   602 # The SUM function should return integer results when all inputs are integer.

   603 #

   604 do_test func-18.1 {

   605   execsql {

   606     CREATE TABLE t5(x);

   607     INSERT INTO t5 VALUES(1);

   608     INSERT INTO t5 VALUES(-99);

   609     INSERT INTO t5 VALUES(10000);

   610     SELECT sum(x) FROM t5;

   611   }

   612 } {9902}

   613 do_test func-18.2 {

   614   execsql {

   615     INSERT INTO t5 VALUES(0.0);

   616     SELECT sum(x) FROM t5;

   617   }

   618 } {9902.0}

   619 

   620 # The sum of nothing is NULL.  But the sum of all NULLs is NULL.

   621 #

   622 # The TOTAL of nothing is 0.0.

   623 #

   624 do_test func-18.3 {

   625   execsql {

   626     DELETE FROM t5;

   627     SELECT sum(x), total(x) FROM t5;

   628   }

   629 } {{} 0.0}

   630 do_test func-18.4 {

   631   execsql {

   632     INSERT INTO t5 VALUES(NULL);

   633     SELECT sum(x), total(x) FROM t5

   634   }

   635 } {{} 0.0}

   636 do_test func-18.5 {

   637   execsql {

   638     INSERT INTO t5 VALUES(NULL);

   639     SELECT sum(x), total(x) FROM t5

   640   }

   641 } {{} 0.0}

   642 do_test func-18.6 {

   643   execsql {

   644     INSERT INTO t5 VALUES(123);

   645     SELECT sum(x), total(x) FROM t5

   646   }

   647 } {123 123.0}

   648 

   649 # Ticket #1664, #1669, #1670, #1674: An integer overflow on SUM causes

   650 # an error. The non-standard TOTAL() function continues to give a helpful

   651 # result.

   652 #

   653 do_test func-18.10 {

   654   execsql {

   655     CREATE TABLE t6(x INTEGER);

   656     INSERT INTO t6 VALUES(1);

   657     INSERT INTO t6 VALUES(1<<62);

   658     SELECT sum(x) - ((1<<62)+1) from t6;

   659   }

   660 } 0

   661 do_test func-18.11 {

   662   execsql {

   663     SELECT typeof(sum(x)) FROM t6

   664   }

   665 } integer

   666 do_test func-18.12 {

   667   catchsql {

   668     INSERT INTO t6 VALUES(1<<62);

   669     SELECT sum(x) - ((1<<62)*2.0+1) from t6;

   670   }

   671 } {1 {integer overflow}}

   672 do_test func-18.13 {

   673   execsql {

   674     SELECT total(x) - ((1<<62)*2.0+1) FROM t6

   675   }

   676 } 0.0

   677 do_test func-18.14 {

   678   execsql {

   679     SELECT sum(-9223372036854775805);

   680   }

   681 } -9223372036854775805

   682 

   683 ifcapable compound&&subquery {

   684 

   685 do_test func-18.15 {

   686   catchsql {

   687     SELECT sum(x) FROM 

   688        (SELECT 9223372036854775807 AS x UNION ALL

   689         SELECT 10 AS x);

   690   }

   691 } {1 {integer overflow}}

   692 do_test func-18.16 {

   693   catchsql {

   694     SELECT sum(x) FROM 

   695        (SELECT 9223372036854775807 AS x UNION ALL

   696         SELECT -10 AS x);

   697   }

   698 } {0 9223372036854775797}

   699 do_test func-18.17 {

   700   catchsql {

   701     SELECT sum(x) FROM 

   702        (SELECT -9223372036854775807 AS x UNION ALL

   703         SELECT 10 AS x);

   704   }

   705 } {0 -9223372036854775797}

   706 do_test func-18.18 {

   707   catchsql {

   708     SELECT sum(x) FROM 

   709        (SELECT -9223372036854775807 AS x UNION ALL

   710         SELECT -10 AS x);

   711   }

   712 } {1 {integer overflow}}

   713 do_test func-18.19 {

   714   catchsql {

   715     SELECT sum(x) FROM (SELECT 9 AS x UNION ALL SELECT -10 AS x);

   716   }

   717 } {0 -1}

   718 do_test func-18.20 {

   719   catchsql {

   720     SELECT sum(x) FROM (SELECT -9 AS x UNION ALL SELECT 10 AS x);

   721   }

   722 } {0 1}

   723 do_test func-18.21 {

   724   catchsql {

   725     SELECT sum(x) FROM (SELECT -10 AS x UNION ALL SELECT 9 AS x);

   726   }

   727 } {0 -1}

   728 do_test func-18.22 {

   729   catchsql {

   730     SELECT sum(x) FROM (SELECT 10 AS x UNION ALL SELECT -9 AS x);

   731   }

   732 } {0 1}

   733 

   734 } ;# ifcapable compound&&subquery

   735 

   736 # Integer overflow on abs()

   737 #

   738 do_test func-18.31 {

   739   catchsql {

   740     SELECT abs(-9223372036854775807);

   741   }

   742 } {0 9223372036854775807}

   743 do_test func-18.32 {

   744   catchsql {

   745     SELECT abs(-9223372036854775807-1);

   746   }

   747 } {1 {integer overflow}}

   748 

   749 # The MATCH function exists but is only a stub and always throws an error.

   750 #

   751 do_test func-19.1 {

   752   execsql {

   753     SELECT match(a,b) FROM t1 WHERE 0;

   754   }

   755 } {}

   756 do_test func-19.2 {

   757   catchsql {

   758     SELECT 'abc' MATCH 'xyz';

   759   }

   760 } {1 {unable to use function MATCH in the requested context}}

   761 do_test func-19.3 {

   762   catchsql {

   763     SELECT 'abc' NOT MATCH 'xyz';

   764   }

   765 } {1 {unable to use function MATCH in the requested context}}

   766 do_test func-19.4 {

   767   catchsql {

   768     SELECT match(1,2,3);

   769   }

   770 } {1 {wrong number of arguments to function match()}}

   771 

   772 # Soundex tests.

   773 #

   774 if {![catch {db eval {SELECT soundex('hello')}}]} {

   775   set i 0

   776   foreach {name sdx} {

   777     euler        E460

   778     EULER        E460

   779     Euler        E460

   780     ellery       E460

   781     gauss        G200

   782     ghosh        G200

   783     hilbert      H416

   784     Heilbronn    H416

   785     knuth        K530

   786     kant         K530

   787     Lloyd        L300

   788     LADD         L300

   789     Lukasiewicz  L222

   790     Lissajous    L222

   791     A            A000

   792     12345        ?000

   793   } {

   794     incr i

   795     do_test func-20.$i {

   796       execsql {SELECT soundex($name)}

   797     } $sdx

   798   }

   799 }

   800 

   801 # Tests of the REPLACE function.

   802 #

   803 do_test func-21.1 {

   804   catchsql {

   805     SELECT replace(1,2);

   806   }

   807 } {1 {wrong number of arguments to function replace()}}

   808 do_test func-21.2 {

   809   catchsql {

   810     SELECT replace(1,2,3,4);

   811   }

   812 } {1 {wrong number of arguments to function replace()}}

   813 do_test func-21.3 {

   814   execsql {

   815     SELECT typeof(replace("This is the main test string", NULL, "ALT"));

   816   }

   817 } {null}

   818 do_test func-21.4 {

   819   execsql {

   820     SELECT typeof(replace(NULL, "main", "ALT"));

   821   }

   822 } {null}

   823 do_test func-21.5 {

   824   execsql {

   825     SELECT typeof(replace("This is the main test string", "main", NULL));

   826   }

   827 } {null}

   828 do_test func-21.6 {

   829   execsql {

   830     SELECT replace("This is the main test string", "main", "ALT");

   831   }

   832 } {{This is the ALT test string}}

   833 do_test func-21.7 {

   834   execsql {

   835     SELECT replace("This is the main test string", "main", "larger-main");

   836   }

   837 } {{This is the larger-main test string}}

   838 do_test func-21.8 {

   839   execsql {

   840     SELECT replace("aaaaaaa", "a", "0123456789");

   841   }

   842 } {0123456789012345678901234567890123456789012345678901234567890123456789}

   843 

   844 ifcapable tclvar {

   845   do_test func-21.9 {

   846     # Attempt to exploit a buffer-overflow that at one time existed 

   847     # in the REPLACE function. 

   848     set ::str "[string repeat A 29998]CC[string repeat A 35537]"

   849     set ::rep [string repeat B 65536]

   850     execsql {

   851       SELECT LENGTH(REPLACE($::str, 'C', $::rep));

   852     }

   853   } [expr 29998 + 2*65536 + 35537]

   854 }

   855 

   856 # Tests for the TRIM, LTRIM and RTRIM functions.

   857 #

   858 do_test func-22.1 {

   859   catchsql {SELECT trim(1,2,3)}

   860 } {1 {wrong number of arguments to function trim()}}

   861 do_test func-22.2 {

   862   catchsql {SELECT ltrim(1,2,3)}

   863 } {1 {wrong number of arguments to function ltrim()}}

   864 do_test func-22.3 {

   865   catchsql {SELECT rtrim(1,2,3)}

   866 } {1 {wrong number of arguments to function rtrim()}}

   867 do_test func-22.4 {

   868   execsql {SELECT trim('  hi  ');}

   869 } {hi}

   870 do_test func-22.5 {

   871   execsql {SELECT ltrim('  hi  ');}

   872 } {{hi  }}

   873 do_test func-22.6 {

   874   execsql {SELECT rtrim('  hi  ');}

   875 } {{  hi}}

   876 do_test func-22.7 {

   877   execsql {SELECT trim('  hi  ','xyz');}

   878 } {{  hi  }}

   879 do_test func-22.8 {

   880   execsql {SELECT ltrim('  hi  ','xyz');}

   881 } {{  hi  }}

   882 do_test func-22.9 {

   883   execsql {SELECT rtrim('  hi  ','xyz');}

   884 } {{  hi  }}

   885 do_test func-22.10 {

   886   execsql {SELECT trim('xyxzy  hi  zzzy','xyz');}

   887 } {{  hi  }}

   888 do_test func-22.11 {

   889   execsql {SELECT ltrim('xyxzy  hi  zzzy','xyz');}

   890 } {{  hi  zzzy}}

   891 do_test func-22.12 {

   892   execsql {SELECT rtrim('xyxzy  hi  zzzy','xyz');}

   893 } {{xyxzy  hi  }}

   894 do_test func-22.13 {

   895   execsql {SELECT trim('  hi  ','');}

   896 } {{  hi  }}

   897 if {[db one {PRAGMA encoding}]=="UTF-8"} {

   898   do_test func-22.14 {

   899     execsql {SELECT hex(trim(x'c280e1bfbff48fbfbf6869',x'6162e1bfbfc280'))}

   900   } {F48FBFBF6869}

   901   do_test func-22.15 {

   902     execsql {SELECT hex(trim(x'6869c280e1bfbff48fbfbf61',

   903                              x'6162e1bfbfc280f48fbfbf'))}

   904   } {6869}

   905   do_test func-22.16 {

   906     execsql {SELECT hex(trim(x'ceb1ceb2ceb3',x'ceb1'));}

   907   } {CEB2CEB3}

   908 }

   909 do_test func-22.20 {

   910   execsql {SELECT typeof(trim(NULL));}

   911 } {null}

   912 do_test func-22.21 {

   913   execsql {SELECT typeof(trim(NULL,'xyz'));}

   914 } {null}

   915 do_test func-22.22 {

   916   execsql {SELECT typeof(trim('hello',NULL));}

   917 } {null}

   918 

   919 # This is to test the deprecated sqlite3_aggregate_count() API.

   920 #

   921 do_test func-23.1 {

   922   sqlite3_create_aggregate db

   923   execsql {

   924     SELECT legacy_count() FROM t6;

   925   }

   926 } {3}

   927 

   928 # The group_concat() function.

   929 #

   930 do_test func-24.1 {

   931   execsql {

   932     SELECT group_concat(t1) FROM tbl1

   933   }

   934 } {this,program,is,free,software}

   935 do_test func-24.2 {

   936   execsql {

   937     SELECT group_concat(t1,' ') FROM tbl1

   938   }

   939 } {{this program is free software}}

   940 do_test func-24.3 {

   941   execsql {

   942     SELECT group_concat(t1,' ' || rowid || ' ') FROM tbl1

   943   }

   944 } {{this 2 program 3 is 4 free 5 software}}

   945 do_test func-24.4 {

   946   execsql {

   947     SELECT group_concat(NULL,t1) FROM tbl1

   948   }

   949 } {{}}

   950 do_test func-24.5 {

   951   execsql {

   952     SELECT group_concat(t1,NULL) FROM tbl1

   953   }

   954 } {thisprogramisfreesoftware}

   955 do_test func-24.6 {

   956   execsql {

   957     SELECT 'BEGIN-'||group_concat(t1) FROM tbl1

   958   }

   959 } {BEGIN-this,program,is,free,software}

   960 unset -nocomplain midargs

   961 set midargs {}

   962 unset -nocomplain midres

   963 set midres {}

   964 unset -nocomplain result

   965 for {set i 1} {$i<[sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG -1]-1} {incr i} {

   966   append midargs ,'/$i'

   967   append midres /$i

   968   set result \

   969      "this$midres:program$midres:is$midres:free$midres:software$midres"

   970   set sql "SELECT group_concat(t1$midargs,':') FROM tbl1"

   971   do_test func-24.7.$i {

   972      db eval $::sql

   973   } $result

   974 }

   975 

   976 # Use the test_isolation function to make sure that type conversions

   977 # on function arguments do not effect subsequent arguments.

   978 #

   979 do_test func-25.1 {

   980   execsql {SELECT test_isolation(t1,t1) FROM tbl1}

   981 } {this program is free software}

   982 

   983 # Try to misuse the sqlite3_create_function() interface.  Verify that

   984 # errors are returned.

   985 #

   986 do_test func-26.1 {

   987   abuse_create_function db

   988 } {}

   989 

   990 # The previous test (func-26.1) registered a function with a very long

   991 # function name that takes many arguments and always returns NULL.  Verify

   992 # that this function works correctly.

   993 #

   994 do_test func-26.2 {

   995   set a {}

   996   for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG} {incr i} {

   997     lappend a $i

   998   }

   999   db eval "

  1000      SELECT nullx_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789([join $a ,]);

  1001   "

  1002 } {{}}

  1003 do_test func-26.3 {

  1004   set a {}

  1005   for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG+1} {incr i} {

  1006     lappend a $i

  1007   }

  1008   catchsql "

  1009      SELECT nullx_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789([join $a ,]);

  1010   "

  1011 } {1 {too many arguments on function nullx_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789}}

  1012 do_test func-26.4 {

  1013   set a {}

  1014   for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG-1} {incr i} {

  1015     lappend a $i

  1016   }

  1017   catchsql "

  1018      SELECT nullx_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789([join $a ,]);

  1019   "

  1020 } {1 {wrong number of arguments to function nullx_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789()}}

  1021 do_test func-26.5 {

  1022   catchsql "

  1023      SELECT nullx_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_12345678a(0);

  1024   "

  1025 } {1 {no such function: nullx_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_12345678a}}

  1026 do_test func-26.6 {

  1027   catchsql "

  1028      SELECT nullx_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789a(0);

  1029   "

  1030 } {1 {no such function: nullx_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789a}}

  1031 

  1032 finish_test