Annotation of embedaddon/sqlite3/test/func.test, revision 1.1
1.1 ! misho 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:
! 15: set testdir [file dirname $argv0]
! 16: source $testdir/tester.tcl
! 17:
! 18: # Create a table to work with.
! 19: #
! 20: do_test func-0.0 {
! 21: execsql {CREATE TABLE tbl1(t1 text)}
! 22: foreach word {this program is free software} {
! 23: execsql "INSERT INTO tbl1 VALUES('$word')"
! 24: }
! 25: execsql {SELECT t1 FROM tbl1 ORDER BY t1}
! 26: } {free is program software this}
! 27: do_test func-0.1 {
! 28: execsql {
! 29: CREATE TABLE t2(a);
! 30: INSERT INTO t2 VALUES(1);
! 31: INSERT INTO t2 VALUES(NULL);
! 32: INSERT INTO t2 VALUES(345);
! 33: INSERT INTO t2 VALUES(NULL);
! 34: INSERT INTO t2 VALUES(67890);
! 35: SELECT * FROM t2;
! 36: }
! 37: } {1 {} 345 {} 67890}
! 38:
! 39: # Check out the length() function
! 40: #
! 41: do_test func-1.0 {
! 42: execsql {SELECT length(t1) FROM tbl1 ORDER BY t1}
! 43: } {4 2 7 8 4}
! 44: do_test func-1.1 {
! 45: set r [catch {execsql {SELECT length(*) FROM tbl1 ORDER BY t1}} msg]
! 46: lappend r $msg
! 47: } {1 {wrong number of arguments to function length()}}
! 48: do_test func-1.2 {
! 49: set r [catch {execsql {SELECT length(t1,5) FROM tbl1 ORDER BY t1}} msg]
! 50: lappend r $msg
! 51: } {1 {wrong number of arguments to function length()}}
! 52: do_test func-1.3 {
! 53: execsql {SELECT length(t1), count(*) FROM tbl1 GROUP BY length(t1)
! 54: ORDER BY length(t1)}
! 55: } {2 1 4 2 7 1 8 1}
! 56: do_test func-1.4 {
! 57: execsql {SELECT coalesce(length(a),-1) FROM t2}
! 58: } {1 -1 3 -1 5}
! 59:
! 60: # Check out the substr() function
! 61: #
! 62: do_test func-2.0 {
! 63: execsql {SELECT substr(t1,1,2) FROM tbl1 ORDER BY t1}
! 64: } {fr is pr so th}
! 65: do_test func-2.1 {
! 66: execsql {SELECT substr(t1,2,1) FROM tbl1 ORDER BY t1}
! 67: } {r s r o h}
! 68: do_test func-2.2 {
! 69: execsql {SELECT substr(t1,3,3) FROM tbl1 ORDER BY t1}
! 70: } {ee {} ogr ftw is}
! 71: do_test func-2.3 {
! 72: execsql {SELECT substr(t1,-1,1) FROM tbl1 ORDER BY t1}
! 73: } {e s m e s}
! 74: do_test func-2.4 {
! 75: execsql {SELECT substr(t1,-1,2) FROM tbl1 ORDER BY t1}
! 76: } {e s m e s}
! 77: do_test func-2.5 {
! 78: execsql {SELECT substr(t1,-2,1) FROM tbl1 ORDER BY t1}
! 79: } {e i a r i}
! 80: do_test func-2.6 {
! 81: execsql {SELECT substr(t1,-2,2) FROM tbl1 ORDER BY t1}
! 82: } {ee is am re is}
! 83: do_test func-2.7 {
! 84: execsql {SELECT substr(t1,-4,2) FROM tbl1 ORDER BY t1}
! 85: } {fr {} gr wa th}
! 86: do_test func-2.8 {
! 87: execsql {SELECT t1 FROM tbl1 ORDER BY substr(t1,2,20)}
! 88: } {this software free program is}
! 89: do_test func-2.9 {
! 90: execsql {SELECT substr(a,1,1) FROM t2}
! 91: } {1 {} 3 {} 6}
! 92: do_test func-2.10 {
! 93: execsql {SELECT substr(a,2,2) FROM t2}
! 94: } {{} {} 45 {} 78}
! 95:
! 96: # Only do the following tests if TCL has UTF-8 capabilities
! 97: #
! 98: if {"\u1234"!="u1234"} {
! 99:
! 100: # Put some UTF-8 characters in the database
! 101: #
! 102: do_test func-3.0 {
! 103: execsql {DELETE FROM tbl1}
! 104: foreach word "contains UTF-8 characters hi\u1234ho" {
! 105: execsql "INSERT INTO tbl1 VALUES('$word')"
! 106: }
! 107: execsql {SELECT t1 FROM tbl1 ORDER BY t1}
! 108: } "UTF-8 characters contains hi\u1234ho"
! 109: do_test func-3.1 {
! 110: execsql {SELECT length(t1) FROM tbl1 ORDER BY t1}
! 111: } {5 10 8 5}
! 112: do_test func-3.2 {
! 113: execsql {SELECT substr(t1,1,2) FROM tbl1 ORDER BY t1}
! 114: } {UT ch co hi}
! 115: do_test func-3.3 {
! 116: execsql {SELECT substr(t1,1,3) FROM tbl1 ORDER BY t1}
! 117: } "UTF cha con hi\u1234"
! 118: do_test func-3.4 {
! 119: execsql {SELECT substr(t1,2,2) FROM tbl1 ORDER BY t1}
! 120: } "TF ha on i\u1234"
! 121: do_test func-3.5 {
! 122: execsql {SELECT substr(t1,2,3) FROM tbl1 ORDER BY t1}
! 123: } "TF- har ont i\u1234h"
! 124: do_test func-3.6 {
! 125: execsql {SELECT substr(t1,3,2) FROM tbl1 ORDER BY t1}
! 126: } "F- ar nt \u1234h"
! 127: do_test func-3.7 {
! 128: execsql {SELECT substr(t1,4,2) FROM tbl1 ORDER BY t1}
! 129: } "-8 ra ta ho"
! 130: do_test func-3.8 {
! 131: execsql {SELECT substr(t1,-1,1) FROM tbl1 ORDER BY t1}
! 132: } "8 s s o"
! 133: do_test func-3.9 {
! 134: execsql {SELECT substr(t1,-3,2) FROM tbl1 ORDER BY t1}
! 135: } "F- er in \u1234h"
! 136: do_test func-3.10 {
! 137: execsql {SELECT substr(t1,-4,3) FROM tbl1 ORDER BY t1}
! 138: } "TF- ter ain i\u1234h"
! 139: do_test func-3.99 {
! 140: execsql {DELETE FROM tbl1}
! 141: foreach word {this program is free software} {
! 142: execsql "INSERT INTO tbl1 VALUES('$word')"
! 143: }
! 144: execsql {SELECT t1 FROM tbl1}
! 145: } {this program is free software}
! 146:
! 147: } ;# End \u1234!=u1234
! 148:
! 149: # Test the abs() and round() functions.
! 150: #
! 151: ifcapable !floatingpoint {
! 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,12345678901234,-1234567890);
! 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: }
! 162: ifcapable floatingpoint {
! 163: do_test func-4.1 {
! 164: execsql {
! 165: CREATE TABLE t1(a,b,c);
! 166: INSERT INTO t1 VALUES(1,2,3);
! 167: INSERT INTO t1 VALUES(2,1.2345678901234,-12345.67890);
! 168: INSERT INTO t1 VALUES(3,-2,-5);
! 169: }
! 170: catchsql {SELECT abs(a,b) FROM t1}
! 171: } {1 {wrong number of arguments to function abs()}}
! 172: }
! 173: do_test func-4.2 {
! 174: catchsql {SELECT abs() FROM t1}
! 175: } {1 {wrong number of arguments to function abs()}}
! 176: ifcapable floatingpoint {
! 177: do_test func-4.3 {
! 178: catchsql {SELECT abs(b) FROM t1 ORDER BY a}
! 179: } {0 {2 1.2345678901234 2}}
! 180: do_test func-4.4 {
! 181: catchsql {SELECT abs(c) FROM t1 ORDER BY a}
! 182: } {0 {3 12345.6789 5}}
! 183: }
! 184: ifcapable !floatingpoint {
! 185: if {[working_64bit_int]} {
! 186: do_test func-4.3 {
! 187: catchsql {SELECT abs(b) FROM t1 ORDER BY a}
! 188: } {0 {2 12345678901234 2}}
! 189: }
! 190: do_test func-4.4 {
! 191: catchsql {SELECT abs(c) FROM t1 ORDER BY a}
! 192: } {0 {3 1234567890 5}}
! 193: }
! 194: do_test func-4.4.1 {
! 195: execsql {SELECT abs(a) FROM t2}
! 196: } {1 {} 345 {} 67890}
! 197: do_test func-4.4.2 {
! 198: execsql {SELECT abs(t1) FROM tbl1}
! 199: } {0.0 0.0 0.0 0.0 0.0}
! 200:
! 201: ifcapable floatingpoint {
! 202: do_test func-4.5 {
! 203: catchsql {SELECT round(a,b,c) FROM t1}
! 204: } {1 {wrong number of arguments to function round()}}
! 205: do_test func-4.6 {
! 206: catchsql {SELECT round(b,2) FROM t1 ORDER BY b}
! 207: } {0 {-2.0 1.23 2.0}}
! 208: do_test func-4.7 {
! 209: catchsql {SELECT round(b,0) FROM t1 ORDER BY a}
! 210: } {0 {2.0 1.0 -2.0}}
! 211: do_test func-4.8 {
! 212: catchsql {SELECT round(c) FROM t1 ORDER BY a}
! 213: } {0 {3.0 -12346.0 -5.0}}
! 214: do_test func-4.9 {
! 215: catchsql {SELECT round(c,a) FROM t1 ORDER BY a}
! 216: } {0 {3.0 -12345.68 -5.0}}
! 217: do_test func-4.10 {
! 218: catchsql {SELECT 'x' || round(c,a) || 'y' FROM t1 ORDER BY a}
! 219: } {0 {x3.0y x-12345.68y x-5.0y}}
! 220: do_test func-4.11 {
! 221: catchsql {SELECT round() FROM t1 ORDER BY a}
! 222: } {1 {wrong number of arguments to function round()}}
! 223: do_test func-4.12 {
! 224: execsql {SELECT coalesce(round(a,2),'nil') FROM t2}
! 225: } {1.0 nil 345.0 nil 67890.0}
! 226: do_test func-4.13 {
! 227: execsql {SELECT round(t1,2) FROM tbl1}
! 228: } {0.0 0.0 0.0 0.0 0.0}
! 229: do_test func-4.14 {
! 230: execsql {SELECT typeof(round(5.1,1));}
! 231: } {real}
! 232: do_test func-4.15 {
! 233: execsql {SELECT typeof(round(5.1));}
! 234: } {real}
! 235: do_test func-4.16 {
! 236: catchsql {SELECT round(b,2.0) FROM t1 ORDER BY b}
! 237: } {0 {-2.0 1.23 2.0}}
! 238: # Verify some values reported on the mailing list.
! 239: # Some of these fail on MSVC builds with 64-bit
! 240: # long doubles, but not on GCC builds with 80-bit
! 241: # long doubles.
! 242: for {set i 1} {$i<999} {incr i} {
! 243: set x1 [expr 40222.5 + $i]
! 244: set x2 [expr 40223.0 + $i]
! 245: do_test func-4.17.$i {
! 246: execsql {SELECT round($x1);}
! 247: } $x2
! 248: }
! 249: for {set i 1} {$i<999} {incr i} {
! 250: set x1 [expr 40222.05 + $i]
! 251: set x2 [expr 40222.10 + $i]
! 252: do_test func-4.18.$i {
! 253: execsql {SELECT round($x1,1);}
! 254: } $x2
! 255: }
! 256: do_test func-4.20 {
! 257: execsql {SELECT round(40223.4999999999);}
! 258: } {40223.0}
! 259: do_test func-4.21 {
! 260: execsql {SELECT round(40224.4999999999);}
! 261: } {40224.0}
! 262: do_test func-4.22 {
! 263: execsql {SELECT round(40225.4999999999);}
! 264: } {40225.0}
! 265: for {set i 1} {$i<10} {incr i} {
! 266: do_test func-4.23.$i {
! 267: execsql {SELECT round(40223.4999999999,$i);}
! 268: } {40223.5}
! 269: do_test func-4.24.$i {
! 270: execsql {SELECT round(40224.4999999999,$i);}
! 271: } {40224.5}
! 272: do_test func-4.25.$i {
! 273: execsql {SELECT round(40225.4999999999,$i);}
! 274: } {40225.5}
! 275: }
! 276: for {set i 10} {$i<32} {incr i} {
! 277: do_test func-4.26.$i {
! 278: execsql {SELECT round(40223.4999999999,$i);}
! 279: } {40223.4999999999}
! 280: do_test func-4.27.$i {
! 281: execsql {SELECT round(40224.4999999999,$i);}
! 282: } {40224.4999999999}
! 283: do_test func-4.28.$i {
! 284: execsql {SELECT round(40225.4999999999,$i);}
! 285: } {40225.4999999999}
! 286: }
! 287: do_test func-4.29 {
! 288: execsql {SELECT round(1234567890.5);}
! 289: } {1234567891.0}
! 290: do_test func-4.30 {
! 291: execsql {SELECT round(12345678901.5);}
! 292: } {12345678902.0}
! 293: do_test func-4.31 {
! 294: execsql {SELECT round(123456789012.5);}
! 295: } {123456789013.0}
! 296: do_test func-4.32 {
! 297: execsql {SELECT round(1234567890123.5);}
! 298: } {1234567890124.0}
! 299: do_test func-4.33 {
! 300: execsql {SELECT round(12345678901234.5);}
! 301: } {12345678901235.0}
! 302: do_test func-4.34 {
! 303: execsql {SELECT round(1234567890123.35,1);}
! 304: } {1234567890123.4}
! 305: do_test func-4.35 {
! 306: execsql {SELECT round(1234567890123.445,2);}
! 307: } {1234567890123.45}
! 308: do_test func-4.36 {
! 309: execsql {SELECT round(99999999999994.5);}
! 310: } {99999999999995.0}
! 311: do_test func-4.37 {
! 312: execsql {SELECT round(9999999999999.55,1);}
! 313: } {9999999999999.6}
! 314: do_test func-4.38 {
! 315: execsql {SELECT round(9999999999999.555,2);}
! 316: } {9999999999999.56}
! 317: }
! 318:
! 319: # Test the upper() and lower() functions
! 320: #
! 321: do_test func-5.1 {
! 322: execsql {SELECT upper(t1) FROM tbl1}
! 323: } {THIS PROGRAM IS FREE SOFTWARE}
! 324: do_test func-5.2 {
! 325: execsql {SELECT lower(upper(t1)) FROM tbl1}
! 326: } {this program is free software}
! 327: do_test func-5.3 {
! 328: execsql {SELECT upper(a), lower(a) FROM t2}
! 329: } {1 1 {} {} 345 345 {} {} 67890 67890}
! 330: ifcapable !icu {
! 331: do_test func-5.4 {
! 332: catchsql {SELECT upper(a,5) FROM t2}
! 333: } {1 {wrong number of arguments to function upper()}}
! 334: }
! 335: do_test func-5.5 {
! 336: catchsql {SELECT upper(*) FROM t2}
! 337: } {1 {wrong number of arguments to function upper()}}
! 338:
! 339: # Test the coalesce() and nullif() functions
! 340: #
! 341: do_test func-6.1 {
! 342: execsql {SELECT coalesce(a,'xyz') FROM t2}
! 343: } {1 xyz 345 xyz 67890}
! 344: do_test func-6.2 {
! 345: execsql {SELECT coalesce(upper(a),'nil') FROM t2}
! 346: } {1 nil 345 nil 67890}
! 347: do_test func-6.3 {
! 348: execsql {SELECT coalesce(nullif(1,1),'nil')}
! 349: } {nil}
! 350: do_test func-6.4 {
! 351: execsql {SELECT coalesce(nullif(1,2),'nil')}
! 352: } {1}
! 353: do_test func-6.5 {
! 354: execsql {SELECT coalesce(nullif(1,NULL),'nil')}
! 355: } {1}
! 356:
! 357:
! 358: # Test the last_insert_rowid() function
! 359: #
! 360: do_test func-7.1 {
! 361: execsql {SELECT last_insert_rowid()}
! 362: } [db last_insert_rowid]
! 363:
! 364: # Tests for aggregate functions and how they handle NULLs.
! 365: #
! 366: ifcapable floatingpoint {
! 367: do_test func-8.1 {
! 368: ifcapable explain {
! 369: execsql {EXPLAIN SELECT sum(a) FROM t2;}
! 370: }
! 371: execsql {
! 372: SELECT sum(a), count(a), round(avg(a),2), min(a), max(a), count(*) FROM t2;
! 373: }
! 374: } {68236 3 22745.33 1 67890 5}
! 375: }
! 376: ifcapable !floatingpoint {
! 377: do_test func-8.1 {
! 378: ifcapable explain {
! 379: execsql {EXPLAIN SELECT sum(a) FROM t2;}
! 380: }
! 381: execsql {
! 382: SELECT sum(a), count(a), avg(a), min(a), max(a), count(*) FROM t2;
! 383: }
! 384: } {68236 3 22745.0 1 67890 5}
! 385: }
! 386: do_test func-8.2 {
! 387: execsql {
! 388: SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t2;
! 389: }
! 390: } {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
! 391:
! 392: ifcapable tempdb {
! 393: do_test func-8.3 {
! 394: execsql {
! 395: CREATE TEMP TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;
! 396: SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
! 397: }
! 398: } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
! 399: } else {
! 400: do_test func-8.3 {
! 401: execsql {
! 402: CREATE TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;
! 403: SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
! 404: }
! 405: } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
! 406: }
! 407: do_test func-8.4 {
! 408: execsql {
! 409: SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
! 410: }
! 411: } {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
! 412: ifcapable compound {
! 413: do_test func-8.5 {
! 414: execsql {
! 415: SELECT sum(x) FROM (SELECT '9223372036' || '854775807' AS x
! 416: UNION ALL SELECT -9223372036854775807)
! 417: }
! 418: } {0}
! 419: do_test func-8.6 {
! 420: execsql {
! 421: SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775807' AS x
! 422: UNION ALL SELECT -9223372036854775807)
! 423: }
! 424: } {integer}
! 425: do_test func-8.7 {
! 426: execsql {
! 427: SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775808' AS x
! 428: UNION ALL SELECT -9223372036854775807)
! 429: }
! 430: } {real}
! 431: ifcapable floatingpoint {
! 432: do_test func-8.8 {
! 433: execsql {
! 434: SELECT sum(x)>0.0 FROM (SELECT '9223372036' || '854775808' AS x
! 435: UNION ALL SELECT -9223372036850000000)
! 436: }
! 437: } {1}
! 438: }
! 439: ifcapable !floatingpoint {
! 440: do_test func-8.8 {
! 441: execsql {
! 442: SELECT sum(x)>0 FROM (SELECT '9223372036' || '854775808' AS x
! 443: UNION ALL SELECT -9223372036850000000)
! 444: }
! 445: } {1}
! 446: }
! 447: }
! 448:
! 449: # How do you test the random() function in a meaningful, deterministic way?
! 450: #
! 451: do_test func-9.1 {
! 452: execsql {
! 453: SELECT random() is not null;
! 454: }
! 455: } {1}
! 456: do_test func-9.2 {
! 457: execsql {
! 458: SELECT typeof(random());
! 459: }
! 460: } {integer}
! 461: do_test func-9.3 {
! 462: execsql {
! 463: SELECT randomblob(32) is not null;
! 464: }
! 465: } {1}
! 466: do_test func-9.4 {
! 467: execsql {
! 468: SELECT typeof(randomblob(32));
! 469: }
! 470: } {blob}
! 471: do_test func-9.5 {
! 472: execsql {
! 473: SELECT length(randomblob(32)), length(randomblob(-5)),
! 474: length(randomblob(2000))
! 475: }
! 476: } {32 1 2000}
! 477:
! 478: # The "hex()" function was added in order to be able to render blobs
! 479: # generated by randomblob(). So this seems like a good place to test
! 480: # hex().
! 481: #
! 482: ifcapable bloblit {
! 483: do_test func-9.10 {
! 484: execsql {SELECT hex(x'00112233445566778899aAbBcCdDeEfF')}
! 485: } {00112233445566778899AABBCCDDEEFF}
! 486: }
! 487: set encoding [db one {PRAGMA encoding}]
! 488: if {$encoding=="UTF-16le"} {
! 489: do_test func-9.11-utf16le {
! 490: execsql {SELECT hex(replace('abcdefg','ef','12'))}
! 491: } {6100620063006400310032006700}
! 492: do_test func-9.12-utf16le {
! 493: execsql {SELECT hex(replace('abcdefg','','12'))}
! 494: } {6100620063006400650066006700}
! 495: do_test func-9.13-utf16le {
! 496: execsql {SELECT hex(replace('aabcdefg','a','aaa'))}
! 497: } {610061006100610061006100620063006400650066006700}
! 498: } elseif {$encoding=="UTF-8"} {
! 499: do_test func-9.11-utf8 {
! 500: execsql {SELECT hex(replace('abcdefg','ef','12'))}
! 501: } {61626364313267}
! 502: do_test func-9.12-utf8 {
! 503: execsql {SELECT hex(replace('abcdefg','','12'))}
! 504: } {61626364656667}
! 505: do_test func-9.13-utf8 {
! 506: execsql {SELECT hex(replace('aabcdefg','a','aaa'))}
! 507: } {616161616161626364656667}
! 508: }
! 509:
! 510: # Use the "sqlite_register_test_function" TCL command which is part of
! 511: # the text fixture in order to verify correct operation of some of
! 512: # the user-defined SQL function APIs that are not used by the built-in
! 513: # functions.
! 514: #
! 515: set ::DB [sqlite3_connection_pointer db]
! 516: sqlite_register_test_function $::DB testfunc
! 517: do_test func-10.1 {
! 518: catchsql {
! 519: SELECT testfunc(NULL,NULL);
! 520: }
! 521: } {1 {first argument should be one of: int int64 string double null value}}
! 522: do_test func-10.2 {
! 523: execsql {
! 524: SELECT testfunc(
! 525: 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
! 526: 'int', 1234
! 527: );
! 528: }
! 529: } {1234}
! 530: do_test func-10.3 {
! 531: execsql {
! 532: SELECT testfunc(
! 533: 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
! 534: 'string', NULL
! 535: );
! 536: }
! 537: } {{}}
! 538:
! 539: ifcapable floatingpoint {
! 540: do_test func-10.4 {
! 541: execsql {
! 542: SELECT testfunc(
! 543: 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
! 544: 'double', 1.234
! 545: );
! 546: }
! 547: } {1.234}
! 548: do_test func-10.5 {
! 549: execsql {
! 550: SELECT testfunc(
! 551: 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
! 552: 'int', 1234,
! 553: 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
! 554: 'string', NULL,
! 555: 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
! 556: 'double', 1.234,
! 557: 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
! 558: 'int', 1234,
! 559: 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
! 560: 'string', NULL,
! 561: 'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
! 562: 'double', 1.234
! 563: );
! 564: }
! 565: } {1.234}
! 566: }
! 567:
! 568: # Test the built-in sqlite_version(*) SQL function.
! 569: #
! 570: do_test func-11.1 {
! 571: execsql {
! 572: SELECT sqlite_version(*);
! 573: }
! 574: } [sqlite3 -version]
! 575:
! 576: # Test that destructors passed to sqlite3 by calls to sqlite3_result_text()
! 577: # etc. are called. These tests use two special user-defined functions
! 578: # (implemented in func.c) only available in test builds.
! 579: #
! 580: # Function test_destructor() takes one argument and returns a copy of the
! 581: # text form of that argument. A destructor is associated with the return
! 582: # value. Function test_destructor_count() returns the number of outstanding
! 583: # destructor calls for values returned by test_destructor().
! 584: #
! 585: if {[db eval {PRAGMA encoding}]=="UTF-8"} {
! 586: do_test func-12.1-utf8 {
! 587: execsql {
! 588: SELECT test_destructor('hello world'), test_destructor_count();
! 589: }
! 590: } {{hello world} 1}
! 591: } else {
! 592: ifcapable {utf16} {
! 593: do_test func-12.1-utf16 {
! 594: execsql {
! 595: SELECT test_destructor16('hello world'), test_destructor_count();
! 596: }
! 597: } {{hello world} 1}
! 598: }
! 599: }
! 600: do_test func-12.2 {
! 601: execsql {
! 602: SELECT test_destructor_count();
! 603: }
! 604: } {0}
! 605: do_test func-12.3 {
! 606: execsql {
! 607: SELECT test_destructor('hello')||' world'
! 608: }
! 609: } {{hello world}}
! 610: do_test func-12.4 {
! 611: execsql {
! 612: SELECT test_destructor_count();
! 613: }
! 614: } {0}
! 615: do_test func-12.5 {
! 616: execsql {
! 617: CREATE TABLE t4(x);
! 618: INSERT INTO t4 VALUES(test_destructor('hello'));
! 619: INSERT INTO t4 VALUES(test_destructor('world'));
! 620: SELECT min(test_destructor(x)), max(test_destructor(x)) FROM t4;
! 621: }
! 622: } {hello world}
! 623: do_test func-12.6 {
! 624: execsql {
! 625: SELECT test_destructor_count();
! 626: }
! 627: } {0}
! 628: do_test func-12.7 {
! 629: execsql {
! 630: DROP TABLE t4;
! 631: }
! 632: } {}
! 633:
! 634:
! 635: # Test that the auxdata API for scalar functions works. This test uses
! 636: # a special user-defined function only available in test builds,
! 637: # test_auxdata(). Function test_auxdata() takes any number of arguments.
! 638: do_test func-13.1 {
! 639: execsql {
! 640: SELECT test_auxdata('hello world');
! 641: }
! 642: } {0}
! 643:
! 644: do_test func-13.2 {
! 645: execsql {
! 646: CREATE TABLE t4(a, b);
! 647: INSERT INTO t4 VALUES('abc', 'def');
! 648: INSERT INTO t4 VALUES('ghi', 'jkl');
! 649: }
! 650: } {}
! 651: do_test func-13.3 {
! 652: execsql {
! 653: SELECT test_auxdata('hello world') FROM t4;
! 654: }
! 655: } {0 1}
! 656: do_test func-13.4 {
! 657: execsql {
! 658: SELECT test_auxdata('hello world', 123) FROM t4;
! 659: }
! 660: } {{0 0} {1 1}}
! 661: do_test func-13.5 {
! 662: execsql {
! 663: SELECT test_auxdata('hello world', a) FROM t4;
! 664: }
! 665: } {{0 0} {1 0}}
! 666: do_test func-13.6 {
! 667: execsql {
! 668: SELECT test_auxdata('hello'||'world', a) FROM t4;
! 669: }
! 670: } {{0 0} {1 0}}
! 671:
! 672: # Test that auxilary data is preserved between calls for SQL variables.
! 673: do_test func-13.7 {
! 674: set DB [sqlite3_connection_pointer db]
! 675: set sql "SELECT test_auxdata( ? , a ) FROM t4;"
! 676: set STMT [sqlite3_prepare $DB $sql -1 TAIL]
! 677: sqlite3_bind_text $STMT 1 hello\000 -1
! 678: set res [list]
! 679: while { "SQLITE_ROW"==[sqlite3_step $STMT] } {
! 680: lappend res [sqlite3_column_text $STMT 0]
! 681: }
! 682: lappend res [sqlite3_finalize $STMT]
! 683: } {{0 0} {1 0} SQLITE_OK}
! 684:
! 685: # Make sure that a function with a very long name is rejected
! 686: do_test func-14.1 {
! 687: catch {
! 688: db function [string repeat X 254] {return "hello"}
! 689: }
! 690: } {0}
! 691: do_test func-14.2 {
! 692: catch {
! 693: db function [string repeat X 256] {return "hello"}
! 694: }
! 695: } {1}
! 696:
! 697: do_test func-15.1 {
! 698: catchsql {select test_error(NULL)}
! 699: } {1 {}}
! 700: do_test func-15.2 {
! 701: catchsql {select test_error('this is the error message')}
! 702: } {1 {this is the error message}}
! 703: do_test func-15.3 {
! 704: catchsql {select test_error('this is the error message',12)}
! 705: } {1 {this is the error message}}
! 706: do_test func-15.4 {
! 707: db errorcode
! 708: } {12}
! 709:
! 710: # Test the quote function for BLOB and NULL values.
! 711: do_test func-16.1 {
! 712: execsql {
! 713: CREATE TABLE tbl2(a, b);
! 714: }
! 715: set STMT [sqlite3_prepare $::DB "INSERT INTO tbl2 VALUES(?, ?)" -1 TAIL]
! 716: sqlite3_bind_blob $::STMT 1 abc 3
! 717: sqlite3_step $::STMT
! 718: sqlite3_finalize $::STMT
! 719: execsql {
! 720: SELECT quote(a), quote(b) FROM tbl2;
! 721: }
! 722: } {X'616263' NULL}
! 723:
! 724: # Correctly handle function error messages that include %. Ticket #1354
! 725: #
! 726: do_test func-17.1 {
! 727: proc testfunc1 args {error "Error %d with %s percents %p"}
! 728: db function testfunc1 ::testfunc1
! 729: catchsql {
! 730: SELECT testfunc1(1,2,3);
! 731: }
! 732: } {1 {Error %d with %s percents %p}}
! 733:
! 734: # The SUM function should return integer results when all inputs are integer.
! 735: #
! 736: do_test func-18.1 {
! 737: execsql {
! 738: CREATE TABLE t5(x);
! 739: INSERT INTO t5 VALUES(1);
! 740: INSERT INTO t5 VALUES(-99);
! 741: INSERT INTO t5 VALUES(10000);
! 742: SELECT sum(x) FROM t5;
! 743: }
! 744: } {9902}
! 745: ifcapable floatingpoint {
! 746: do_test func-18.2 {
! 747: execsql {
! 748: INSERT INTO t5 VALUES(0.0);
! 749: SELECT sum(x) FROM t5;
! 750: }
! 751: } {9902.0}
! 752: }
! 753:
! 754: # The sum of nothing is NULL. But the sum of all NULLs is NULL.
! 755: #
! 756: # The TOTAL of nothing is 0.0.
! 757: #
! 758: do_test func-18.3 {
! 759: execsql {
! 760: DELETE FROM t5;
! 761: SELECT sum(x), total(x) FROM t5;
! 762: }
! 763: } {{} 0.0}
! 764: do_test func-18.4 {
! 765: execsql {
! 766: INSERT INTO t5 VALUES(NULL);
! 767: SELECT sum(x), total(x) FROM t5
! 768: }
! 769: } {{} 0.0}
! 770: do_test func-18.5 {
! 771: execsql {
! 772: INSERT INTO t5 VALUES(NULL);
! 773: SELECT sum(x), total(x) FROM t5
! 774: }
! 775: } {{} 0.0}
! 776: do_test func-18.6 {
! 777: execsql {
! 778: INSERT INTO t5 VALUES(123);
! 779: SELECT sum(x), total(x) FROM t5
! 780: }
! 781: } {123 123.0}
! 782:
! 783: # Ticket #1664, #1669, #1670, #1674: An integer overflow on SUM causes
! 784: # an error. The non-standard TOTAL() function continues to give a helpful
! 785: # result.
! 786: #
! 787: do_test func-18.10 {
! 788: execsql {
! 789: CREATE TABLE t6(x INTEGER);
! 790: INSERT INTO t6 VALUES(1);
! 791: INSERT INTO t6 VALUES(1<<62);
! 792: SELECT sum(x) - ((1<<62)+1) from t6;
! 793: }
! 794: } 0
! 795: do_test func-18.11 {
! 796: execsql {
! 797: SELECT typeof(sum(x)) FROM t6
! 798: }
! 799: } integer
! 800: ifcapable floatingpoint {
! 801: do_test func-18.12 {
! 802: catchsql {
! 803: INSERT INTO t6 VALUES(1<<62);
! 804: SELECT sum(x) - ((1<<62)*2.0+1) from t6;
! 805: }
! 806: } {1 {integer overflow}}
! 807: do_test func-18.13 {
! 808: execsql {
! 809: SELECT total(x) - ((1<<62)*2.0+1) FROM t6
! 810: }
! 811: } 0.0
! 812: }
! 813: ifcapable !floatingpoint {
! 814: do_test func-18.12 {
! 815: catchsql {
! 816: INSERT INTO t6 VALUES(1<<62);
! 817: SELECT sum(x) - ((1<<62)*2+1) from t6;
! 818: }
! 819: } {1 {integer overflow}}
! 820: do_test func-18.13 {
! 821: execsql {
! 822: SELECT total(x) - ((1<<62)*2+1) FROM t6
! 823: }
! 824: } 0.0
! 825: }
! 826: if {[working_64bit_int]} {
! 827: do_test func-18.14 {
! 828: execsql {
! 829: SELECT sum(-9223372036854775805);
! 830: }
! 831: } -9223372036854775805
! 832: }
! 833: ifcapable compound&&subquery {
! 834:
! 835: do_test func-18.15 {
! 836: catchsql {
! 837: SELECT sum(x) FROM
! 838: (SELECT 9223372036854775807 AS x UNION ALL
! 839: SELECT 10 AS x);
! 840: }
! 841: } {1 {integer overflow}}
! 842: if {[working_64bit_int]} {
! 843: do_test func-18.16 {
! 844: catchsql {
! 845: SELECT sum(x) FROM
! 846: (SELECT 9223372036854775807 AS x UNION ALL
! 847: SELECT -10 AS x);
! 848: }
! 849: } {0 9223372036854775797}
! 850: do_test func-18.17 {
! 851: catchsql {
! 852: SELECT sum(x) FROM
! 853: (SELECT -9223372036854775807 AS x UNION ALL
! 854: SELECT 10 AS x);
! 855: }
! 856: } {0 -9223372036854775797}
! 857: }
! 858: do_test func-18.18 {
! 859: catchsql {
! 860: SELECT sum(x) FROM
! 861: (SELECT -9223372036854775807 AS x UNION ALL
! 862: SELECT -10 AS x);
! 863: }
! 864: } {1 {integer overflow}}
! 865: do_test func-18.19 {
! 866: catchsql {
! 867: SELECT sum(x) FROM (SELECT 9 AS x UNION ALL SELECT -10 AS x);
! 868: }
! 869: } {0 -1}
! 870: do_test func-18.20 {
! 871: catchsql {
! 872: SELECT sum(x) FROM (SELECT -9 AS x UNION ALL SELECT 10 AS x);
! 873: }
! 874: } {0 1}
! 875: do_test func-18.21 {
! 876: catchsql {
! 877: SELECT sum(x) FROM (SELECT -10 AS x UNION ALL SELECT 9 AS x);
! 878: }
! 879: } {0 -1}
! 880: do_test func-18.22 {
! 881: catchsql {
! 882: SELECT sum(x) FROM (SELECT 10 AS x UNION ALL SELECT -9 AS x);
! 883: }
! 884: } {0 1}
! 885:
! 886: } ;# ifcapable compound&&subquery
! 887:
! 888: # Integer overflow on abs()
! 889: #
! 890: if {[working_64bit_int]} {
! 891: do_test func-18.31 {
! 892: catchsql {
! 893: SELECT abs(-9223372036854775807);
! 894: }
! 895: } {0 9223372036854775807}
! 896: }
! 897: do_test func-18.32 {
! 898: catchsql {
! 899: SELECT abs(-9223372036854775807-1);
! 900: }
! 901: } {1 {integer overflow}}
! 902:
! 903: # The MATCH function exists but is only a stub and always throws an error.
! 904: #
! 905: do_test func-19.1 {
! 906: execsql {
! 907: SELECT match(a,b) FROM t1 WHERE 0;
! 908: }
! 909: } {}
! 910: do_test func-19.2 {
! 911: catchsql {
! 912: SELECT 'abc' MATCH 'xyz';
! 913: }
! 914: } {1 {unable to use function MATCH in the requested context}}
! 915: do_test func-19.3 {
! 916: catchsql {
! 917: SELECT 'abc' NOT MATCH 'xyz';
! 918: }
! 919: } {1 {unable to use function MATCH in the requested context}}
! 920: do_test func-19.4 {
! 921: catchsql {
! 922: SELECT match(1,2,3);
! 923: }
! 924: } {1 {wrong number of arguments to function match()}}
! 925:
! 926: # Soundex tests.
! 927: #
! 928: if {![catch {db eval {SELECT soundex('hello')}}]} {
! 929: set i 0
! 930: foreach {name sdx} {
! 931: euler E460
! 932: EULER E460
! 933: Euler E460
! 934: ellery E460
! 935: gauss G200
! 936: ghosh G200
! 937: hilbert H416
! 938: Heilbronn H416
! 939: knuth K530
! 940: kant K530
! 941: Lloyd L300
! 942: LADD L300
! 943: Lukasiewicz L222
! 944: Lissajous L222
! 945: A A000
! 946: 12345 ?000
! 947: } {
! 948: incr i
! 949: do_test func-20.$i {
! 950: execsql {SELECT soundex($name)}
! 951: } $sdx
! 952: }
! 953: }
! 954:
! 955: # Tests of the REPLACE function.
! 956: #
! 957: do_test func-21.1 {
! 958: catchsql {
! 959: SELECT replace(1,2);
! 960: }
! 961: } {1 {wrong number of arguments to function replace()}}
! 962: do_test func-21.2 {
! 963: catchsql {
! 964: SELECT replace(1,2,3,4);
! 965: }
! 966: } {1 {wrong number of arguments to function replace()}}
! 967: do_test func-21.3 {
! 968: execsql {
! 969: SELECT typeof(replace("This is the main test string", NULL, "ALT"));
! 970: }
! 971: } {null}
! 972: do_test func-21.4 {
! 973: execsql {
! 974: SELECT typeof(replace(NULL, "main", "ALT"));
! 975: }
! 976: } {null}
! 977: do_test func-21.5 {
! 978: execsql {
! 979: SELECT typeof(replace("This is the main test string", "main", NULL));
! 980: }
! 981: } {null}
! 982: do_test func-21.6 {
! 983: execsql {
! 984: SELECT replace("This is the main test string", "main", "ALT");
! 985: }
! 986: } {{This is the ALT test string}}
! 987: do_test func-21.7 {
! 988: execsql {
! 989: SELECT replace("This is the main test string", "main", "larger-main");
! 990: }
! 991: } {{This is the larger-main test string}}
! 992: do_test func-21.8 {
! 993: execsql {
! 994: SELECT replace("aaaaaaa", "a", "0123456789");
! 995: }
! 996: } {0123456789012345678901234567890123456789012345678901234567890123456789}
! 997:
! 998: ifcapable tclvar {
! 999: do_test func-21.9 {
! 1000: # Attempt to exploit a buffer-overflow that at one time existed
! 1001: # in the REPLACE function.
! 1002: set ::str "[string repeat A 29998]CC[string repeat A 35537]"
! 1003: set ::rep [string repeat B 65536]
! 1004: execsql {
! 1005: SELECT LENGTH(REPLACE($::str, 'C', $::rep));
! 1006: }
! 1007: } [expr 29998 + 2*65536 + 35537]
! 1008: }
! 1009:
! 1010: # Tests for the TRIM, LTRIM and RTRIM functions.
! 1011: #
! 1012: do_test func-22.1 {
! 1013: catchsql {SELECT trim(1,2,3)}
! 1014: } {1 {wrong number of arguments to function trim()}}
! 1015: do_test func-22.2 {
! 1016: catchsql {SELECT ltrim(1,2,3)}
! 1017: } {1 {wrong number of arguments to function ltrim()}}
! 1018: do_test func-22.3 {
! 1019: catchsql {SELECT rtrim(1,2,3)}
! 1020: } {1 {wrong number of arguments to function rtrim()}}
! 1021: do_test func-22.4 {
! 1022: execsql {SELECT trim(' hi ');}
! 1023: } {hi}
! 1024: do_test func-22.5 {
! 1025: execsql {SELECT ltrim(' hi ');}
! 1026: } {{hi }}
! 1027: do_test func-22.6 {
! 1028: execsql {SELECT rtrim(' hi ');}
! 1029: } {{ hi}}
! 1030: do_test func-22.7 {
! 1031: execsql {SELECT trim(' hi ','xyz');}
! 1032: } {{ hi }}
! 1033: do_test func-22.8 {
! 1034: execsql {SELECT ltrim(' hi ','xyz');}
! 1035: } {{ hi }}
! 1036: do_test func-22.9 {
! 1037: execsql {SELECT rtrim(' hi ','xyz');}
! 1038: } {{ hi }}
! 1039: do_test func-22.10 {
! 1040: execsql {SELECT trim('xyxzy hi zzzy','xyz');}
! 1041: } {{ hi }}
! 1042: do_test func-22.11 {
! 1043: execsql {SELECT ltrim('xyxzy hi zzzy','xyz');}
! 1044: } {{ hi zzzy}}
! 1045: do_test func-22.12 {
! 1046: execsql {SELECT rtrim('xyxzy hi zzzy','xyz');}
! 1047: } {{xyxzy hi }}
! 1048: do_test func-22.13 {
! 1049: execsql {SELECT trim(' hi ','');}
! 1050: } {{ hi }}
! 1051: if {[db one {PRAGMA encoding}]=="UTF-8"} {
! 1052: do_test func-22.14 {
! 1053: execsql {SELECT hex(trim(x'c280e1bfbff48fbfbf6869',x'6162e1bfbfc280'))}
! 1054: } {F48FBFBF6869}
! 1055: do_test func-22.15 {
! 1056: execsql {SELECT hex(trim(x'6869c280e1bfbff48fbfbf61',
! 1057: x'6162e1bfbfc280f48fbfbf'))}
! 1058: } {6869}
! 1059: do_test func-22.16 {
! 1060: execsql {SELECT hex(trim(x'ceb1ceb2ceb3',x'ceb1'));}
! 1061: } {CEB2CEB3}
! 1062: }
! 1063: do_test func-22.20 {
! 1064: execsql {SELECT typeof(trim(NULL));}
! 1065: } {null}
! 1066: do_test func-22.21 {
! 1067: execsql {SELECT typeof(trim(NULL,'xyz'));}
! 1068: } {null}
! 1069: do_test func-22.22 {
! 1070: execsql {SELECT typeof(trim('hello',NULL));}
! 1071: } {null}
! 1072:
! 1073: # This is to test the deprecated sqlite3_aggregate_count() API.
! 1074: #
! 1075: ifcapable deprecated {
! 1076: do_test func-23.1 {
! 1077: sqlite3_create_aggregate db
! 1078: execsql {
! 1079: SELECT legacy_count() FROM t6;
! 1080: }
! 1081: } {3}
! 1082: }
! 1083:
! 1084: # The group_concat() function.
! 1085: #
! 1086: do_test func-24.1 {
! 1087: execsql {
! 1088: SELECT group_concat(t1) FROM tbl1
! 1089: }
! 1090: } {this,program,is,free,software}
! 1091: do_test func-24.2 {
! 1092: execsql {
! 1093: SELECT group_concat(t1,' ') FROM tbl1
! 1094: }
! 1095: } {{this program is free software}}
! 1096: do_test func-24.3 {
! 1097: execsql {
! 1098: SELECT group_concat(t1,' ' || rowid || ' ') FROM tbl1
! 1099: }
! 1100: } {{this 2 program 3 is 4 free 5 software}}
! 1101: do_test func-24.4 {
! 1102: execsql {
! 1103: SELECT group_concat(NULL,t1) FROM tbl1
! 1104: }
! 1105: } {{}}
! 1106: do_test func-24.5 {
! 1107: execsql {
! 1108: SELECT group_concat(t1,NULL) FROM tbl1
! 1109: }
! 1110: } {thisprogramisfreesoftware}
! 1111: do_test func-24.6 {
! 1112: execsql {
! 1113: SELECT 'BEGIN-'||group_concat(t1) FROM tbl1
! 1114: }
! 1115: } {BEGIN-this,program,is,free,software}
! 1116:
! 1117: # Ticket #3179: Make sure aggregate functions can take many arguments.
! 1118: # None of the built-in aggregates do this, so use the md5sum() from the
! 1119: # test extensions.
! 1120: #
! 1121: unset -nocomplain midargs
! 1122: set midargs {}
! 1123: unset -nocomplain midres
! 1124: set midres {}
! 1125: unset -nocomplain result
! 1126: for {set i 1} {$i<[sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG -1]} {incr i} {
! 1127: append midargs ,'/$i'
! 1128: append midres /$i
! 1129: set result [md5 \
! 1130: "this${midres}program${midres}is${midres}free${midres}software${midres}"]
! 1131: set sql "SELECT md5sum(t1$midargs) FROM tbl1"
! 1132: do_test func-24.7.$i {
! 1133: db eval $::sql
! 1134: } $result
! 1135: }
! 1136:
! 1137: # Ticket #3806. If the initial string in a group_concat is an empty
! 1138: # string, the separator that follows should still be present.
! 1139: #
! 1140: do_test func-24.8 {
! 1141: execsql {
! 1142: SELECT group_concat(CASE t1 WHEN 'this' THEN '' ELSE t1 END) FROM tbl1
! 1143: }
! 1144: } {,program,is,free,software}
! 1145: do_test func-24.9 {
! 1146: execsql {
! 1147: SELECT group_concat(CASE WHEN t1!='software' THEN '' ELSE t1 END) FROM tbl1
! 1148: }
! 1149: } {,,,,software}
! 1150:
! 1151: # Ticket #3923. Initial empty strings have a separator. But initial
! 1152: # NULLs do not.
! 1153: #
! 1154: do_test func-24.10 {
! 1155: execsql {
! 1156: SELECT group_concat(CASE t1 WHEN 'this' THEN null ELSE t1 END) FROM tbl1
! 1157: }
! 1158: } {program,is,free,software}
! 1159: do_test func-24.11 {
! 1160: execsql {
! 1161: SELECT group_concat(CASE WHEN t1!='software' THEN null ELSE t1 END) FROM tbl1
! 1162: }
! 1163: } {software}
! 1164: do_test func-24.12 {
! 1165: execsql {
! 1166: SELECT group_concat(CASE t1 WHEN 'this' THEN ''
! 1167: WHEN 'program' THEN null ELSE t1 END) FROM tbl1
! 1168: }
! 1169: } {,is,free,software}
! 1170:
! 1171:
! 1172: # Use the test_isolation function to make sure that type conversions
! 1173: # on function arguments do not effect subsequent arguments.
! 1174: #
! 1175: do_test func-25.1 {
! 1176: execsql {SELECT test_isolation(t1,t1) FROM tbl1}
! 1177: } {this program is free software}
! 1178:
! 1179: # Try to misuse the sqlite3_create_function() interface. Verify that
! 1180: # errors are returned.
! 1181: #
! 1182: do_test func-26.1 {
! 1183: abuse_create_function db
! 1184: } {}
! 1185:
! 1186: # The previous test (func-26.1) registered a function with a very long
! 1187: # function name that takes many arguments and always returns NULL. Verify
! 1188: # that this function works correctly.
! 1189: #
! 1190: do_test func-26.2 {
! 1191: set a {}
! 1192: for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG} {incr i} {
! 1193: lappend a $i
! 1194: }
! 1195: db eval "
! 1196: 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 ,]);
! 1197: "
! 1198: } {{}}
! 1199: do_test func-26.3 {
! 1200: set a {}
! 1201: for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG+1} {incr i} {
! 1202: lappend a $i
! 1203: }
! 1204: catchsql "
! 1205: 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 ,]);
! 1206: "
! 1207: } {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}}
! 1208: do_test func-26.4 {
! 1209: set a {}
! 1210: for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG-1} {incr i} {
! 1211: lappend a $i
! 1212: }
! 1213: catchsql "
! 1214: 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 ,]);
! 1215: "
! 1216: } {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()}}
! 1217: do_test func-26.5 {
! 1218: catchsql "
! 1219: 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);
! 1220: "
! 1221: } {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}}
! 1222: do_test func-26.6 {
! 1223: catchsql "
! 1224: 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);
! 1225: "
! 1226: } {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}}
! 1227:
! 1228: do_test func-27.1 {
! 1229: catchsql {SELECT coalesce()}
! 1230: } {1 {wrong number of arguments to function coalesce()}}
! 1231: do_test func-27.2 {
! 1232: catchsql {SELECT coalesce(1)}
! 1233: } {1 {wrong number of arguments to function coalesce()}}
! 1234: do_test func-27.3 {
! 1235: catchsql {SELECT coalesce(1,2)}
! 1236: } {0 1}
! 1237:
! 1238: # Ticket 2d401a94287b5
! 1239: # Unknown function in a DEFAULT expression causes a segfault.
! 1240: #
! 1241: do_test func-28.1 {
! 1242: db eval {
! 1243: CREATE TABLE t28(x, y DEFAULT(nosuchfunc(1)));
! 1244: }
! 1245: catchsql {
! 1246: INSERT INTO t28(x) VALUES(1);
! 1247: }
! 1248: } {1 {unknown function: nosuchfunc()}}
! 1249:
! 1250: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to func.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test