func.test

最新的sqlite3.6.2源代码

} {}
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\000 -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 {}}
do_test func-15.2 {
  catchsql {select test_error('this is the error message')}
} {1 {this is the error message}}
do_test func-15.3 {
  catchsql {select test_error('this is the error message',12)}
} {1 {this is the error message}}
do_test func-15.4 {
  db errorcode
} {12}

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

# Tests of the REPLACE function.
#
do_test func-21.1 {
  catchsql {
    SELECT replace(1,2);
  }
} {1 {wrong number of arguments to function replace()}}
do_test func-21.2 {
  catchsql {
    SELECT replace(1,2,3,4);
  }
} {1 {wrong number of arguments to function replace()}}
do_test func-21.3 {
  execsql {
    SELECT typeof(replace("This is the main test string", NULL, "ALT"));
  }
} {null}
do_test func-21.4 {
  execsql {
    SELECT typeof(replace(NULL, "main", "ALT"));
  }
} {null}
do_test func-21.5 {
  execsql {
    SELECT typeof(replace("This is the main test string", "main", NULL));
  }
} {null}
do_test func-21.6 {
  execsql {
    SELECT replace("This is the main test string", "main", "ALT");
  }
} {{This is the ALT test string}}
do_test func-21.7 {
  execsql {
    SELECT replace("This is the main test string", "main", "larger-main");
  }
} {{This is the larger-main test string}}
do_test func-21.8 {
  execsql {
    SELECT replace("aaaaaaa", "a", "0123456789");
  }
} {0123456789012345678901234567890123456789012345678901234567890123456789}

ifcapable tclvar {
  do_test func-21.9 {
    # Attempt to exploit a buffer-overflow that at one time existed 
    # in the REPLACE function. 
    set ::str "[string repeat A 29998]CC[string repeat A 35537]"
    set ::rep [string repeat B 65536]
    execsql {
      SELECT LENGTH(REPLACE($::str, 'C', $::rep));
    }
  } [expr 29998 + 2*65536 + 35537]
}

# Tests for the TRIM, LTRIM and RTRIM functions.
#
do_test func-22.1 {
  catchsql {SELECT trim(1,2,3)}
} {1 {wrong number of arguments to function trim()}}
do_test func-22.2 {
  catchsql {SELECT ltrim(1,2,3)}
} {1 {wrong number of arguments to function ltrim()}}
do_test func-22.3 {
  catchsql {SELECT rtrim(1,2,3)}
} {1 {wrong number of arguments to function rtrim()}}
do_test func-22.4 {
  execsql {SELECT trim('  hi  ');}
} {hi}
do_test func-22.5 {
  execsql {SELECT ltrim('  hi  ');}
} {{hi  }}
do_test func-22.6 {
  execsql {SELECT rtrim('  hi  ');}
} {{  hi}}
do_test func-22.7 {
  execsql {SELECT trim('  hi  ','xyz');}
} {{  hi  }}
do_test func-22.8 {
  execsql {SELECT ltrim('  hi  ','xyz');}
} {{  hi  }}
do_test func-22.9 {
  execsql {SELECT rtrim('  hi  ','xyz');}
} {{  hi  }}
do_test func-22.10 {
  execsql {SELECT trim('xyxzy  hi  zzzy','xyz');}
} {{  hi  }}
do_test func-22.11 {
  execsql {SELECT ltrim('xyxzy  hi  zzzy','xyz');}
} {{  hi  zzzy}}
do_test func-22.12 {
  execsql {SELECT rtrim('xyxzy  hi  zzzy','xyz');}
} {{xyxzy  hi  }}
do_test func-22.13 {
  execsql {SELECT trim('  hi  ','');}
} {{  hi  }}
if {[db one {PRAGMA encoding}]=="UTF-8"} {
  do_test func-22.14 {
    execsql {SELECT hex(trim(x'c280e1bfbff48fbfbf6869',x'6162e1bfbfc280'))}
  } {F48FBFBF6869}
  do_test func-22.15 {
    execsql {SELECT hex(trim(x'6869c280e1bfbff48fbfbf61',
                             x'6162e1bfbfc280f48fbfbf'))}
  } {6869}
  do_test func-22.16 {
    execsql {SELECT hex(trim(x'ceb1ceb2ceb3',x'ceb1'));}
  } {CEB2CEB3}
}
do_test func-22.20 {
  execsql {SELECT typeof(trim(NULL));}
} {null}
do_test func-22.21 {
  execsql {SELECT typeof(trim(NULL,'xyz'));}
} {null}
do_test func-22.22 {
  execsql {SELECT typeof(trim('hello',NULL));}
} {null}

# This is to test the deprecated sqlite3_aggregate_count() API.
#
do_test func-23.1 {
  sqlite3_create_aggregate db
  execsql {
    SELECT legacy_count() FROM t6;
  }
} {3}

# The group_concat() function.
#
do_test func-24.1 {
  execsql {
    SELECT group_concat(t1) FROM tbl1
  }
} {this,program,is,free,software}
do_test func-24.2 {
  execsql {
    SELECT group_concat(t1,' ') FROM tbl1
  }
} {{this program is free software}}
do_test func-24.3 {
  execsql {
    SELECT group_concat(t1,' ' || rowid || ' ') FROM tbl1
  }
} {{this 2 program 3 is 4 free 5 software}}
do_test func-24.4 {
  execsql {
    SELECT group_concat(NULL,t1) FROM tbl1
  }
} {{}}
do_test func-24.5 {
  execsql {
    SELECT group_concat(t1,NULL) FROM tbl1
  }
} {thisprogramisfreesoftware}
do_test func-24.6 {
  execsql {
    SELECT 'BEGIN-'||group_concat(t1) FROM tbl1
  }
} {BEGIN-this,program,is,free,software}
unset -nocomplain midargs
set midargs {}
unset -nocomplain midres
set midres {}
unset -nocomplain result
for {set i 1} {$i<[sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG -1]-1} {incr i} {
  append midargs ,'/$i'
  append midres /$i
  set result \
     "this$midres:program$midres:is$midres:free$midres:software$midres"
  set sql "SELECT group_concat(t1$midargs,':') FROM tbl1"
  do_test func-24.7.$i {
     db eval $::sql
  } $result
}

# Use the test_isolation function to make sure that type conversions
# on function arguments do not effect subsequent arguments.
#
do_test func-25.1 {
  execsql {SELECT test_isolation(t1,t1) FROM tbl1}
} {this program is free software}

# Try to misuse the sqlite3_create_function() interface.  Verify that
# errors are returned.
#
do_test func-26.1 {
  abuse_create_function db
} {}

# The previous test (func-26.1) registered a function with a very long
# function name that takes many arguments and always returns NULL.  Verify
# that this function works correctly.
#
do_test func-26.2 {
  set a {}
  for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG} {incr i} {
    lappend a $i
  }
  db eval "
     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 ,]);
  "
} {{}}
do_test func-26.3 {
  set a {}
  for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG+1} {incr i} {
    lappend a $i
  }
  catchsql "
     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 ,]);
  "
} {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}}
do_test func-26.4 {
  set a {}
  for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG-1} {incr i} {
    lappend a $i
  }
  catchsql "
     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 ,]);
  "
} {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()}}
do_test func-26.5 {
  catchsql "
     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);
  "
} {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}}
do_test func-26.6 {
  catchsql "
     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);
  "
} {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}}

finish_test