test/func.test

# 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.56 2007/01/29 15:50:06 drh 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}
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}

# 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 randomhex(32) is not null;
  }
} {1}
do_test func-9.4 {
  execsql {
    SELECT typeof(randomhex(32));
  }
} {text}
do_test func-9.5 {
  execsql {
    SELECT length(randomhex(32)), length(randomhex(-5)),
           length(randomhex(2000)), length(randomhex(31));
  }
} {32 2 1000 32}


# 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().
#
do_test func-12.1 {
  execsql {
    SELECT test_destructor('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', test_destructor_count();
  }
} {{hello world} 0}
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.
btree_breakpoint
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}}
do_test func-13.5 {
  execsql {
    SELECT test_auxdata('hello world', a) FROM t4;
  }
} {{0 0} {1 0}}
do_test func-13.6 {
  execsql {
    SELECT test_auxdata('hello'||'world', a) FROM t4;
  }
} {{0 0} {1 0}}

# Test that auxilary data is preserved between calls for SQL variables.
do_test func-13.7 {
  set DB [sqlite3_connection_pointer db]
  set sql "SELECT test_auxdata( ? , a ) FROM t4;"
  set STMT [sqlite3_prepare $DB $sql -1 TAIL]
  sqlite3_bind_text $STMT 1 hello -1
  set res [list]
  while { "SQLITE_ROW"==[sqlite3_step $STMT] } {
    lappend res [sqlite3_column_text $STMT 0]
  }
  lappend res [sqlite3_finalize $STMT]
} {{0 0} {1 0} SQLITE_OK}

# Make sure that a function with a very long name is rejected
do_test func-14.1 {
  catch {
    db function [string repeat X 254] {return "hello"}
  } 
} {0}
do_test func-14.2 {
  catch {
    db function [string repeat X 256] {return "hello"}
  }
} {1}

do_test func-15.1 {
  catchsql {
    select test_error(NULL);
  }
} {1 {}}

# Test the quote function for BLOB and NULL values.
do_test func-16.1 {
  execsql {
    CREATE TABLE tbl2(a, b);
  }
  set STMT [sqlite3_prepare $::DB "INSERT INTO tbl2 VALUES(?, ?)" -1 TAIL]
  sqlite3_bind_blob $::STMT 1 abc 3
  sqlite3_step $::STMT
  sqlite3_finalize $::STMT
  execsql {
    SELECT quote(a), quote(b) FROM tbl2;
  }
} {X'616263' NULL}

# Correctly handle function error messages that include %.  Ticket #1354
#
do_test func-17.1 {
  proc testfunc1 args {error "Error %d with %s percents %p"}
  db function testfunc1 ::testfunc1
  catchsql {
    SELECT testfunc1(1,2,3);
  }
} {1 {Error %d with %s percents %p}}

# The SUM function should return integer results when all inputs are integer.
#
do_test func-18.1 {
  execsql {
    CREATE TABLE t5(x);
    INSERT INTO t5 VALUES(1);
    INSERT INTO t5 VALUES(-99);
    INSERT INTO t5 VALUES(10000);
    SELECT sum(x) FROM t5;
  }
} {9902}
do_test func-18.2 {
  execsql {
    INSERT INTO t5 VALUES(0.0);
    SELECT sum(x) FROM t5;
  }
} {9902.0}

# The sum of nothing is NULL.  But the sum of all NULLs is NULL.
#
# The TOTAL of nothing is 0.0.
#
do_test func-18.3 {
  execsql {
    DELETE FROM t5;
    SELECT sum(x), total(x) FROM t5;
  }
} {{} 0.0}
do_test func-18.4 {
  execsql {
    INSERT INTO t5 VALUES(NULL);
    SELECT sum(x), total(x) FROM t5
  }
} {{} 0.0}
do_test func-18.5 {
  execsql {
    INSERT INTO t5 VALUES(NULL);
    SELECT sum(x), total(x) FROM t5
  }
} {{} 0.0}
do_test func-18.6 {
  execsql {
    INSERT INTO t5 VALUES(123);
    SELECT sum(x), total(x) FROM t5
  }
} {123 123.0}

# Ticket #1664, #1669, #1670, #1674: An integer overflow on SUM causes
# an error. The non-standard TOTAL() function continues to give a helpful
# result.
#
do_test func-18.10 {
  execsql {
    CREATE TABLE t6(x INTEGER);
    INSERT INTO t6 VALUES(1);
    INSERT INTO t6 VALUES(1<<62);
    SELECT sum(x) - ((1<<62)+1) from t6;
  }
} 0
do_test func-18.11 {
  execsql {
    SELECT typeof(sum(x)) FROM t6
  }
} integer
do_test func-18.12 {
  catchsql {
    INSERT INTO t6 VALUES(1<<62);
    SELECT sum(x) - ((1<<62)*2.0+1) from t6;
  }
} {1 {integer overflow}}
do_test func-18.13 {
  execsql {
    SELECT total(x) - ((1<<62)*2.0+1) FROM t6
  }
} 0.0
do_test func-18.14 {
  execsql {
    SELECT sum(-9223372036854775805);
  }
} -9223372036854775805

ifcapable compound&&subquery {

do_test func-18.15 {
  catchsql {
    SELECT sum(x) FROM 
       (SELECT 9223372036854775807 AS x UNION ALL
        SELECT 10 AS x);
  }
} {1 {integer overflow}}
do_test func-18.16 {
  catchsql {
    SELECT sum(x) FROM 
       (SELECT 9223372036854775807 AS x UNION ALL
        SELECT -10 AS x);
  }
} {0 9223372036854775797}
do_test func-18.17 {
  catchsql {
    SELECT sum(x) FROM 
       (SELECT -9223372036854775807 AS x UNION ALL
        SELECT 10 AS x);
  }
} {0 -9223372036854775797}
do_test func-18.18 {
  catchsql {
    SELECT sum(x) FROM 
       (SELECT -9223372036854775807 AS x UNION ALL
        SELECT -10 AS x);
  }
} {1 {integer overflow}}
do_test func-18.19 {
  catchsql {
    SELECT sum(x) FROM (SELECT 9 AS x UNION ALL SELECT -10 AS x);
  }
} {0 -1}
do_test func-18.20 {
  catchsql {
    SELECT sum(x) FROM (SELECT -9 AS x UNION ALL SELECT 10 AS x);
  }
} {0 1}
do_test func-18.21 {
  catchsql {
    SELECT sum(x) FROM (SELECT -10 AS x UNION ALL SELECT 9 AS x);
  }
} {0 -1}
do_test func-18.22 {
  catchsql {
    SELECT sum(x) FROM (SELECT 10 AS x UNION ALL SELECT -9 AS x);
  }
} {0 1}

} ;# ifcapable compound&&subquery

# Integer overflow on abs()
#
do_test func-18.31 {
  catchsql {
    SELECT abs(-9223372036854775807);
  }
} {0 9223372036854775807}
do_test func-18.32 {
  catchsql {
    SELECT abs(-9223372036854775807-1);
  }
} {1 {integer overflow}}

# The MATCH function exists but is only a stub and always throws an error.
#
do_test func-19.1 {
  execsql {
    SELECT match(a,b) FROM t1 WHERE 0;
  }
} {}
do_test func-19.2 {
  catchsql {
    SELECT 'abc' MATCH 'xyz';
  }
} {1 {unable to use function MATCH in the requested context}}
do_test func-19.3 {
  catchsql {
    SELECT 'abc' NOT MATCH 'xyz';
  }
} {1 {unable to use function MATCH in the requested context}}
do_test func-19.4 {
  catchsql {
    SELECT match(1,2,3);
  }
} {1 {wrong number of arguments to function match()}}

# Soundex tests.
#
if {![catch {db eval {SELECT soundex('hello')}}]} {
  set i 0
  foreach {name sdx} {
    euler        E460
    EULER        E460
    Euler        E460
    ellery       E460
    gauss        G200
    ghosh        G200
    hilbert      H416
    Heilbronn    H416
    knuth        K530
    kant         K530
    Lloyd        L300
    LADD         L300
    Lukasiewicz  L222
    Lissajous    L222
    A            A000
    12345        ?000
  } {
    incr i
    do_test func-20.$i {
      execsql {SELECT soundex($name)}
    } $sdx
  }
}

finish_test