# 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.86 2008/08/04 03:51:24 danielk1977 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 coalesce(length(a),-1) FROM t2}

} {1 -1 3 -1 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

#

if {"\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}

} "UTF-8 characters contains hi\u1234ho"

do_test func-3.1 {

  execsql {SELECT length(t1) FROM tbl1 ORDER BY t1}

} {5 10 8 5}

do_test func-3.2 {

  execsql {SELECT substr(t1,1,2) FROM tbl1 ORDER BY t1}

} {UT ch co hi}

do_test func-3.3 {

  execsql {SELECT substr(t1,1,3) FROM tbl1 ORDER BY t1}

} "UTF cha con hi\u1234"

do_test func-3.4 {

  execsql {SELECT substr(t1,2,2) FROM tbl1 ORDER BY t1}

} "TF ha on i\u1234"

do_test func-3.5 {

  execsql {SELECT substr(t1,2,3) FROM tbl1 ORDER BY t1}

} "TF- har ont i\u1234h"

do_test func-3.6 {

  execsql {SELECT substr(t1,3,2) FROM tbl1 ORDER BY t1}

} "F- ar nt \u1234h"

do_test func-3.7 {

  execsql {SELECT substr(t1,4,2) FROM tbl1 ORDER BY t1}

} "-8 ra ta ho"

do_test func-3.8 {

  execsql {SELECT substr(t1,-1,1) FROM tbl1 ORDER BY t1}

} "8 s s o"

do_test func-3.9 {

  execsql {SELECT substr(t1,-3,2) FROM tbl1 ORDER BY t1}

} "F- er in \u1234h"

do_test func-3.10 {

  execsql {SELECT substr(t1,-4,3) FROM tbl1 ORDER BY t1}

} "TF- ter ain i\u1234h"

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 \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.6789 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}

} {0.0 0.0 0.0 0.0 0.0}

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.0 1.23 2.0}}

do_test func-4.7 {

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

} {0 {2.0 1.0 -2.0}}

do_test func-4.8 {

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

} {0 {3.0 -12346.0 -5.0}}

do_test func-4.9 {

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

} {0 {3.0 -12345.68 -5.0}}

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

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 coalesce(round(a,2),'nil') FROM t2}

} {1.0 nil 345.0 nil 67890.0}

do_test func-4.13 {

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

} {0.0 0.0 0.0 0.0 0.0}

do_test func-4.14 {

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

} {real}

do_test func-4.15 {

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

} {real}

# 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}

ifcapable !icu {

  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() and nullif() functions

#

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}

do_test func-6.3 {

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

} {nil}

do_test func-6.4 {

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

} {1}

do_test func-6.5 {

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

} {1}

# Test the last_insert_rowid() function

#

do_test func-7.1 {

  execsql {SELECT last_insert_rowid()}

} [db last_insert_rowid]

# Tests for aggregate functions and how they handle NULLs.

#

do_test func-8.1 {

  ifcapable explain {

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

  }

  execsql {

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

  }

} {68236 3 22745.33 1 67890 5}

do_test func-8.2 {

  execsql {

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

  }

} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}

ifcapable tempdb {

  do_test func-8.3 {

    execsql {

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

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

    }

  } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}

} else {

  do_test func-8.3 {

    execsql {

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

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

    }

  } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}

}

do_test func-8.4 {

  execsql {

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

  }

} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}

ifcapable compound {

  do_test func-8.5 {

    execsql {

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

                          UNION ALL SELECT -9223372036854775807)

    }

  } {0}

  do_test func-8.6 {

    execsql {

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

                          UNION ALL SELECT -9223372036854775807)

    }

  } {integer}

  do_test func-8.7 {

    execsql {

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

                          UNION ALL SELECT -9223372036854775807)

    }

  } {real}

  do_test func-8.8 {

    execsql {

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

                          UNION ALL SELECT -9223372036850000000)

    }

  } {1}

}

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

#

do_test func-9.1 {

  execsql {

    SELECT random() is not null;

  }

} {1}

do_test func-9.2 {

  execsql {

    SELECT typeof(random());

  }

} {integer}

do_test func-9.3 {

  execsql {

    SELECT randomblob(32) is not null;

  }

} {1}

do_test func-9.4 {

  execsql {

    SELECT typeof(randomblob(32));

  }

} {blob}

do_test func-9.5 {

  execsql {

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

           length(randomblob(2000))

  }

} {32 1 2000}

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

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

# hex().

#

ifcapable bloblit {

  do_test func-9.10 {

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

  } {00112233445566778899AABBCCDDEEFF}

}

set encoding [db one {PRAGMA encoding}]

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

  do_test func-9.11-utf16le {

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

  } {6100620063006400310032006700}

  do_test func-9.12-utf16le {

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

  } {{}}

  do_test func-9.13-utf16le {

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

  } {610061006100610061006100620063006400650066006700}

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

  do_test func-9.11-utf8 {

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

  } {61626364313267}

  do_test func-9.12-utf8 {

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

  } {{}}

  do_test func-9.13-utf8 {

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

  } {616161616161626364656667}

}

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

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

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

# functions.

#

set ::DB [sqlite3_connection_pointer db]

sqlite_register_test_function $::DB testfunc

do_test func-10.1 {

  catchsql {

    SELECT testfunc(NULL,NULL);

  }

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

do_test func-10.2 {

  execsql {

    SELECT testfunc(

     'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',

     'int', 1234

    );

  }

} {1234}

do_test func-10.3 {

  execsql {

    SELECT testfunc(

     'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',

     'string', NULL

    );

  }

} {{}}

do_test func-10.4 {

  execsql {

    SELECT testfunc(

     'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',

     'double', 1.234

    );

  }

} {1.234}

do_test func-10.5 {

  execsql {

    SELECT testfunc(

     'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',

     'int', 1234,

     'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',

     'string', NULL,

     'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',

     'double', 1.234,

     'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',

     'int', 1234,

     'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',

     'string', NULL,

     'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',

     'double', 1.234

    );

  }

} {1.234}

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

#

do_test func-11.1 {

  execsql {

    SELECT sqlite_version(*);

  }

} [sqlite3 -version]

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

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

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

#

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

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

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

# destructor calls for values returned by test_destructor().

#

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

  do_test func-12.1-utf8 {

    execsql {

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

    }

  } {{hello world} 1}

} else {

    ifcapable {utf16} {

      do_test func-12.1-utf16 {

        execsql {

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

        }

      } {{hello world} 1}

    }

}

do_test func-12.2 {

  execsql {

    SELECT test_destructor_count();

  }

} {0}

do_test func-12.3 {

  execsql {

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

  }

} {{hello world}}

do_test func-12.4 {

  execsql {

    SELECT test_destructor_count();

  }

} {0}

do_test func-12.5 {

  execsql {

    CREATE TABLE t4(x);

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

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

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

  }

} {hello world}

do_test func-12.6 {

  execsql {

    SELECT test_destructor_count();

  }

} {0}

do_test func-12.7 {

  execsql {

    DROP TABLE t4;

  }

} {}

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

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

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

do_test func-13.1 {

  execsql {

    SELECT test_auxdata('hello world');

  }

} {0}

do_test func-13.2 {

  execsql {

    CREATE TABLE t4(a, b);

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

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

  }

} {}

do_test func-13.3 {

  execsql {

    SELECT test_auxdata('hello world') FROM t4;

  }

} {0 1}

do_test func-13.4 {

  execsql {

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

  }

} {{0 0} {1 1}}