Annotation of embedaddon/sqlite3/test/analyze3.test, revision 1.1
1.1 ! misho 1: # 2009 August 06
! 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: #
! 12: # This file implements regression tests for SQLite library. This file
! 13: # implements tests for range and LIKE constraints that use bound variables
! 14: # instead of literal constant arguments.
! 15: #
! 16:
! 17: set testdir [file dirname $argv0]
! 18: source $testdir/tester.tcl
! 19:
! 20: ifcapable !stat3 {
! 21: finish_test
! 22: return
! 23: }
! 24:
! 25: #----------------------------------------------------------------------
! 26: # Test Organization:
! 27: #
! 28: # analyze3-1.*: Test that the values of bound parameters are considered
! 29: # in the same way as constants when planning queries that
! 30: # use range constraints.
! 31: #
! 32: # analyze3-2.*: Test that the values of bound parameters are considered
! 33: # in the same way as constants when planning queries that
! 34: # use LIKE expressions in the WHERE clause.
! 35: #
! 36: # analyze3-3.*: Test that binding to a variable does not invalidate the
! 37: # query plan when there is no way in which replanning the
! 38: # query may produce a superior outcome.
! 39: #
! 40: # analyze3-4.*: Test that SQL or authorization callback errors occuring
! 41: # within sqlite3Reprepare() are handled correctly.
! 42: #
! 43: # analyze3-5.*: Check that the query plans of applicable statements are
! 44: # invalidated if the values of SQL parameter are modified
! 45: # using the clear_bindings() or transfer_bindings() APIs.
! 46: #
! 47:
! 48: proc getvar {varname} { uplevel #0 set $varname }
! 49: db function var getvar
! 50:
! 51: proc eqp {sql {db db}} {
! 52: uplevel execsql [list "EXPLAIN QUERY PLAN $sql"] $db
! 53: }
! 54:
! 55: proc sf_execsql {sql {db db}} {
! 56: set ::sqlite_search_count 0
! 57: set r [uplevel [list execsql $sql $db]]
! 58:
! 59: concat $::sqlite_search_count [$db status step] $r
! 60: }
! 61:
! 62: #-------------------------------------------------------------------------
! 63: #
! 64: # analyze3-1.1.1:
! 65: # Create a table with two columns. Populate the first column (affinity
! 66: # INTEGER) with integer values from 100 to 1100. Create an index on this
! 67: # column. ANALYZE the table.
! 68: #
! 69: # analyze3-1.1.2 - 3.1.3
! 70: # Show that there are two possible plans for querying the table with
! 71: # a range constraint on the indexed column - "full table scan" or "use
! 72: # the index". When the range is specified using literal values, SQLite
! 73: # is able to pick the best plan based on the samples in sqlite_stat3.
! 74: #
! 75: # analyze3-1.1.4 - 3.1.9
! 76: # Show that using SQL variables produces the same results as using
! 77: # literal values to constrain the range scan.
! 78: #
! 79: # These tests also check that the compiler code considers column
! 80: # affinities when estimating the number of rows scanned by the "use
! 81: # index strategy".
! 82: #
! 83: do_test analyze3-1.1.1 {
! 84: execsql {
! 85: BEGIN;
! 86: CREATE TABLE t1(x INTEGER, y);
! 87: CREATE INDEX i1 ON t1(x);
! 88: }
! 89: for {set i 0} {$i < 1000} {incr i} {
! 90: execsql { INSERT INTO t1 VALUES($i+100, $i) }
! 91: }
! 92: execsql {
! 93: COMMIT;
! 94: ANALYZE;
! 95: }
! 96: } {}
! 97:
! 98: do_eqp_test analyze3-1.1.2 {
! 99: SELECT sum(y) FROM t1 WHERE x>200 AND x<300
! 100: } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x<?) (~179 rows)}}
! 101: do_eqp_test analyze3-1.1.3 {
! 102: SELECT sum(y) FROM t1 WHERE x>0 AND x<1100
! 103: } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x<?) (~959 rows)}}
! 104:
! 105: do_test analyze3-1.1.4 {
! 106: sf_execsql { SELECT sum(y) FROM t1 WHERE x>200 AND x<300 }
! 107: } {199 0 14850}
! 108: do_test analyze3-1.1.5 {
! 109: set l [string range "200" 0 end]
! 110: set u [string range "300" 0 end]
! 111: sf_execsql { SELECT sum(y) FROM t1 WHERE x>$l AND x<$u }
! 112: } {199 0 14850}
! 113: do_test analyze3-1.1.6 {
! 114: set l [expr int(200)]
! 115: set u [expr int(300)]
! 116: sf_execsql { SELECT sum(y) FROM t1 WHERE x>$l AND x<$u }
! 117: } {199 0 14850}
! 118: do_test analyze3-1.1.7 {
! 119: sf_execsql { SELECT sum(y) FROM t1 WHERE x>0 AND x<1100 }
! 120: } {2000 0 499500}
! 121: do_test analyze3-1.1.8 {
! 122: set l [string range "0" 0 end]
! 123: set u [string range "1100" 0 end]
! 124: sf_execsql { SELECT sum(y) FROM t1 WHERE x>$l AND x<$u }
! 125: } {2000 0 499500}
! 126: do_test analyze3-1.1.9 {
! 127: set l [expr int(0)]
! 128: set u [expr int(1100)]
! 129: sf_execsql { SELECT sum(y) FROM t1 WHERE x>$l AND x<$u }
! 130: } {2000 0 499500}
! 131:
! 132:
! 133: # The following tests are similar to the block above. The difference is
! 134: # that the indexed column has TEXT affinity in this case. In the tests
! 135: # above the affinity is INTEGER.
! 136: #
! 137: do_test analyze3-1.2.1 {
! 138: execsql {
! 139: BEGIN;
! 140: CREATE TABLE t2(x TEXT, y);
! 141: INSERT INTO t2 SELECT * FROM t1;
! 142: CREATE INDEX i2 ON t2(x);
! 143: COMMIT;
! 144: ANALYZE;
! 145: }
! 146: } {}
! 147: do_eqp_test analyze3-1.2.2 {
! 148: SELECT sum(y) FROM t2 WHERE x>1 AND x<2
! 149: } {0 0 0 {SEARCH TABLE t2 USING INDEX i2 (x>? AND x<?) (~196 rows)}}
! 150: do_eqp_test analyze3-1.2.3 {
! 151: SELECT sum(y) FROM t2 WHERE x>0 AND x<99
! 152: } {0 0 0 {SEARCH TABLE t2 USING INDEX i2 (x>? AND x<?) (~968 rows)}}
! 153: do_test analyze3-1.2.4 {
! 154: sf_execsql { SELECT sum(y) FROM t2 WHERE x>12 AND x<20 }
! 155: } {161 0 4760}
! 156: do_test analyze3-1.2.5 {
! 157: set l [string range "12" 0 end]
! 158: set u [string range "20" 0 end]
! 159: sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u}
! 160: } {161 0 text text 4760}
! 161: do_test analyze3-1.2.6 {
! 162: set l [expr int(12)]
! 163: set u [expr int(20)]
! 164: sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u}
! 165: } {161 0 integer integer 4760}
! 166: do_test analyze3-1.2.7 {
! 167: sf_execsql { SELECT sum(y) FROM t2 WHERE x>0 AND x<99 }
! 168: } {1981 0 490555}
! 169: do_test analyze3-1.2.8 {
! 170: set l [string range "0" 0 end]
! 171: set u [string range "99" 0 end]
! 172: sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u}
! 173: } {1981 0 text text 490555}
! 174: do_test analyze3-1.2.9 {
! 175: set l [expr int(0)]
! 176: set u [expr int(99)]
! 177: sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u}
! 178: } {1981 0 integer integer 490555}
! 179:
! 180: # Same tests a third time. This time, column x has INTEGER affinity and
! 181: # is not the leftmost column of the table. This triggered a bug causing
! 182: # SQLite to use sub-optimal query plans in 3.6.18 and earlier.
! 183: #
! 184: do_test analyze3-1.3.1 {
! 185: execsql {
! 186: BEGIN;
! 187: CREATE TABLE t3(y TEXT, x INTEGER);
! 188: INSERT INTO t3 SELECT y, x FROM t1;
! 189: CREATE INDEX i3 ON t3(x);
! 190: COMMIT;
! 191: ANALYZE;
! 192: }
! 193: } {}
! 194: do_eqp_test analyze3-1.3.2 {
! 195: SELECT sum(y) FROM t3 WHERE x>200 AND x<300
! 196: } {0 0 0 {SEARCH TABLE t3 USING INDEX i3 (x>? AND x<?) (~156 rows)}}
! 197: do_eqp_test analyze3-1.3.3 {
! 198: SELECT sum(y) FROM t3 WHERE x>0 AND x<1100
! 199: } {0 0 0 {SEARCH TABLE t3 USING INDEX i3 (x>? AND x<?) (~989 rows)}}
! 200:
! 201: do_test analyze3-1.3.4 {
! 202: sf_execsql { SELECT sum(y) FROM t3 WHERE x>200 AND x<300 }
! 203: } {199 0 14850}
! 204: do_test analyze3-1.3.5 {
! 205: set l [string range "200" 0 end]
! 206: set u [string range "300" 0 end]
! 207: sf_execsql { SELECT sum(y) FROM t3 WHERE x>$l AND x<$u }
! 208: } {199 0 14850}
! 209: do_test analyze3-1.3.6 {
! 210: set l [expr int(200)]
! 211: set u [expr int(300)]
! 212: sf_execsql { SELECT sum(y) FROM t3 WHERE x>$l AND x<$u }
! 213: } {199 0 14850}
! 214: do_test analyze3-1.3.7 {
! 215: sf_execsql { SELECT sum(y) FROM t3 WHERE x>0 AND x<1100 }
! 216: } {2000 0 499500}
! 217: do_test analyze3-1.3.8 {
! 218: set l [string range "0" 0 end]
! 219: set u [string range "1100" 0 end]
! 220: sf_execsql { SELECT sum(y) FROM t3 WHERE x>$l AND x<$u }
! 221: } {2000 0 499500}
! 222: do_test analyze3-1.3.9 {
! 223: set l [expr int(0)]
! 224: set u [expr int(1100)]
! 225: sf_execsql { SELECT sum(y) FROM t3 WHERE x>$l AND x<$u }
! 226: } {2000 0 499500}
! 227:
! 228: #-------------------------------------------------------------------------
! 229: # Test that the values of bound SQL variables may be used for the LIKE
! 230: # optimization.
! 231: #
! 232: drop_all_tables
! 233: do_test analyze3-2.1 {
! 234: execsql {
! 235: PRAGMA case_sensitive_like=off;
! 236: BEGIN;
! 237: CREATE TABLE t1(a, b TEXT COLLATE nocase);
! 238: CREATE INDEX i1 ON t1(b);
! 239: }
! 240: for {set i 0} {$i < 1000} {incr i} {
! 241: set t ""
! 242: append t [lindex {a b c d e f g h i j} [expr $i/100]]
! 243: append t [lindex {a b c d e f g h i j} [expr ($i/10)%10]]
! 244: append t [lindex {a b c d e f g h i j} [expr ($i%10)]]
! 245: execsql { INSERT INTO t1 VALUES($i, $t) }
! 246: }
! 247: execsql COMMIT
! 248: } {}
! 249: do_eqp_test analyze3-2.2 {
! 250: SELECT count(a) FROM t1 WHERE b LIKE 'a%'
! 251: } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (b>? AND b<?) (~31250 rows)}}
! 252: do_eqp_test analyze3-2.3 {
! 253: SELECT count(a) FROM t1 WHERE b LIKE '%a'
! 254: } {0 0 0 {SCAN TABLE t1 (~500000 rows)}}
! 255:
! 256: do_test analyze3-2.4 {
! 257: sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE 'a%' }
! 258: } {101 0 100}
! 259: do_test analyze3-2.5 {
! 260: sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE '%a' }
! 261: } {999 999 100}
! 262:
! 263: do_test analyze3-2.4 {
! 264: set like "a%"
! 265: sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
! 266: } {101 0 100}
! 267: do_test analyze3-2.5 {
! 268: set like "%a"
! 269: sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
! 270: } {999 999 100}
! 271: do_test analyze3-2.6 {
! 272: set like "a"
! 273: sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
! 274: } {101 0 0}
! 275: do_test analyze3-2.7 {
! 276: set like "ab"
! 277: sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
! 278: } {11 0 0}
! 279: do_test analyze3-2.8 {
! 280: set like "abc"
! 281: sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
! 282: } {2 0 1}
! 283: do_test analyze3-2.9 {
! 284: set like "a_c"
! 285: sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
! 286: } {101 0 10}
! 287:
! 288:
! 289: #-------------------------------------------------------------------------
! 290: # This block of tests checks that statements are correctly marked as
! 291: # expired when the values bound to any parameters that may affect the
! 292: # query plan are modified.
! 293: #
! 294: drop_all_tables
! 295: db auth auth
! 296: proc auth {args} {
! 297: set ::auth 1
! 298: return SQLITE_OK
! 299: }
! 300:
! 301: do_test analyze3-3.1 {
! 302: execsql {
! 303: BEGIN;
! 304: CREATE TABLE t1(a, b, c);
! 305: CREATE INDEX i1 ON t1(b);
! 306: }
! 307: for {set i 0} {$i < 100} {incr i} {
! 308: execsql { INSERT INTO t1 VALUES($i, $i, $i) }
! 309: }
! 310: execsql COMMIT
! 311: execsql ANALYZE
! 312: } {}
! 313:
! 314: do_test analyze3-3.2.1 {
! 315: set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE b>?" -1 dummy]
! 316: sqlite3_expired $S
! 317: } {0}
! 318: do_test analyze3-3.2.2 {
! 319: sqlite3_bind_text $S 1 "abc" 3
! 320: sqlite3_expired $S
! 321: } {1}
! 322: do_test analyze3-3.2.4 {
! 323: sqlite3_finalize $S
! 324: } {SQLITE_OK}
! 325:
! 326: do_test analyze3-3.2.5 {
! 327: set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE b=?" -1 dummy]
! 328: sqlite3_expired $S
! 329: } {0}
! 330: do_test analyze3-3.2.6 {
! 331: sqlite3_bind_text $S 1 "abc" 3
! 332: sqlite3_expired $S
! 333: } {0}
! 334: do_test analyze3-3.2.7 {
! 335: sqlite3_finalize $S
! 336: } {SQLITE_OK}
! 337:
! 338: do_test analyze3-3.4.1 {
! 339: set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE a=? AND b>?" -1 dummy]
! 340: sqlite3_expired $S
! 341: } {0}
! 342: do_test analyze3-3.4.2 {
! 343: sqlite3_bind_text $S 1 "abc" 3
! 344: sqlite3_expired $S
! 345: } {0}
! 346: do_test analyze3-3.4.3 {
! 347: sqlite3_bind_text $S 2 "def" 3
! 348: sqlite3_expired $S
! 349: } {1}
! 350: do_test analyze3-3.4.4 {
! 351: sqlite3_bind_text $S 2 "ghi" 3
! 352: sqlite3_expired $S
! 353: } {1}
! 354: do_test analyze3-3.4.5 {
! 355: sqlite3_expired $S
! 356: } {1}
! 357: do_test analyze3-3.4.6 {
! 358: sqlite3_finalize $S
! 359: } {SQLITE_OK}
! 360:
! 361: do_test analyze3-3.5.1 {
! 362: set S [sqlite3_prepare_v2 db {
! 363: SELECT * FROM t1 WHERE a IN (
! 364: ?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10,
! 365: ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18, ?19, ?20,
! 366: ?21, ?22, ?23, ?24, ?25, ?26, ?27, ?28, ?29, ?30, ?31
! 367: ) AND b>?32;
! 368: } -1 dummy]
! 369: sqlite3_expired $S
! 370: } {0}
! 371: do_test analyze3-3.5.2 {
! 372: sqlite3_bind_text $S 31 "abc" 3
! 373: sqlite3_expired $S
! 374: } {0}
! 375: do_test analyze3-3.5.3 {
! 376: sqlite3_bind_text $S 32 "def" 3
! 377: sqlite3_expired $S
! 378: } {1}
! 379: do_test analyze3-3.5.5 {
! 380: sqlite3_finalize $S
! 381: } {SQLITE_OK}
! 382:
! 383: do_test analyze3-3.6.1 {
! 384: set S [sqlite3_prepare_v2 db {
! 385: SELECT * FROM t1 WHERE a IN (
! 386: ?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10,
! 387: ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18, ?19, ?20,
! 388: ?21, ?22, ?23, ?24, ?25, ?26, ?27, ?28, ?29, ?30, ?31, ?32
! 389: ) AND b>?33;
! 390: } -1 dummy]
! 391: sqlite3_expired $S
! 392: } {0}
! 393: do_test analyze3-3.6.2 {
! 394: sqlite3_bind_text $S 32 "abc" 3
! 395: sqlite3_expired $S
! 396: } {1}
! 397: do_test analyze3-3.6.3 {
! 398: sqlite3_bind_text $S 33 "def" 3
! 399: sqlite3_expired $S
! 400: } {1}
! 401: do_test analyze3-3.6.5 {
! 402: sqlite3_finalize $S
! 403: } {SQLITE_OK}
! 404:
! 405: do_test analyze3-3.7.1 {
! 406: set S [sqlite3_prepare_v2 db {
! 407: SELECT * FROM t1 WHERE a IN (
! 408: ?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?33,
! 409: ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18, ?19, ?20,
! 410: ?21, ?22, ?23, ?24, ?25, ?26, ?27, ?28, ?29, ?30, ?31, ?32
! 411: ) AND b>?10;
! 412: } -1 dummy]
! 413: sqlite3_expired $S
! 414: } {0}
! 415: do_test analyze3-3.7.2 {
! 416: sqlite3_bind_text $S 32 "abc" 3
! 417: sqlite3_expired $S
! 418: } {0}
! 419: do_test analyze3-3.7.3 {
! 420: sqlite3_bind_text $S 33 "def" 3
! 421: sqlite3_expired $S
! 422: } {0}
! 423: do_test analyze3-3.7.4 {
! 424: sqlite3_bind_text $S 10 "def" 3
! 425: sqlite3_expired $S
! 426: } {1}
! 427: do_test analyze3-3.7.6 {
! 428: sqlite3_finalize $S
! 429: } {SQLITE_OK}
! 430:
! 431: do_test analyze3-3.8.1 {
! 432: execsql {
! 433: CREATE TABLE t4(x, y TEXT COLLATE NOCASE);
! 434: CREATE INDEX i4 ON t4(y);
! 435: }
! 436: } {}
! 437: do_test analyze3-3.8.2 {
! 438: set S [sqlite3_prepare_v2 db {
! 439: SELECT * FROM t4 WHERE x != ? AND y LIKE ?
! 440: } -1 dummy]
! 441: sqlite3_expired $S
! 442: } {0}
! 443: do_test analyze3-3.8.3 {
! 444: sqlite3_bind_text $S 1 "abc" 3
! 445: sqlite3_expired $S
! 446: } {0}
! 447: do_test analyze3-3.8.4 {
! 448: sqlite3_bind_text $S 2 "def" 3
! 449: sqlite3_expired $S
! 450: } {1}
! 451: do_test analyze3-3.8.7 {
! 452: sqlite3_bind_text $S 2 "ghi%" 4
! 453: sqlite3_expired $S
! 454: } {1}
! 455: do_test analyze3-3.8.8 {
! 456: sqlite3_expired $S
! 457: } {1}
! 458: do_test analyze3-3.8.9 {
! 459: sqlite3_bind_text $S 2 "ghi%def" 7
! 460: sqlite3_expired $S
! 461: } {1}
! 462: do_test analyze3-3.8.10 {
! 463: sqlite3_expired $S
! 464: } {1}
! 465: do_test analyze3-3.8.11 {
! 466: sqlite3_bind_text $S 2 "%ab" 3
! 467: sqlite3_expired $S
! 468: } {1}
! 469: do_test analyze3-3.8.12 {
! 470: sqlite3_expired $S
! 471: } {1}
! 472: do_test analyze3-3.8.12 {
! 473: sqlite3_bind_text $S 2 "%de" 3
! 474: sqlite3_expired $S
! 475: } {1}
! 476: do_test analyze3-3.8.13 {
! 477: sqlite3_expired $S
! 478: } {1}
! 479: do_test analyze3-3.8.14 {
! 480: sqlite3_finalize $S
! 481: } {SQLITE_OK}
! 482:
! 483: #-------------------------------------------------------------------------
! 484: # These tests check that errors encountered while repreparing an SQL
! 485: # statement within sqlite3Reprepare() are handled correctly.
! 486: #
! 487:
! 488: # Check a schema error.
! 489: #
! 490: do_test analyze3-4.1.1 {
! 491: set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE a=? AND b>?" -1 dummy]
! 492: sqlite3_step $S
! 493: } {SQLITE_DONE}
! 494: do_test analyze3-4.1.2 {
! 495: sqlite3_reset $S
! 496: sqlite3_bind_text $S 2 "abc" 3
! 497: execsql { DROP TABLE t1 }
! 498: sqlite3_step $S
! 499: } {SQLITE_ERROR}
! 500: do_test analyze3-4.1.3 {
! 501: sqlite3_finalize $S
! 502: } {SQLITE_ERROR}
! 503:
! 504: # Check an authorization error.
! 505: #
! 506: do_test analyze3-4.2.1 {
! 507: execsql {
! 508: BEGIN;
! 509: CREATE TABLE t1(a, b, c);
! 510: CREATE INDEX i1 ON t1(b);
! 511: }
! 512: for {set i 0} {$i < 100} {incr i} {
! 513: execsql { INSERT INTO t1 VALUES($i, $i, $i) }
! 514: }
! 515: execsql COMMIT
! 516: execsql ANALYZE
! 517: set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE a=? AND b>?" -1 dummy]
! 518: sqlite3_step $S
! 519: } {SQLITE_DONE}
! 520: db auth auth
! 521: proc auth {args} {
! 522: if {[lindex $args 0] == "SQLITE_READ"} {return SQLITE_DENY}
! 523: return SQLITE_OK
! 524: }
! 525: do_test analyze3-4.2.2 {
! 526: sqlite3_reset $S
! 527: sqlite3_bind_text $S 2 "abc" 3
! 528: sqlite3_step $S
! 529: } {SQLITE_AUTH}
! 530: do_test analyze3-4.2.4 {
! 531: sqlite3_finalize $S
! 532: } {SQLITE_AUTH}
! 533:
! 534: # Check the effect of an authorization error that occurs in a re-prepare
! 535: # performed by sqlite3_step() is the same as one that occurs within
! 536: # sqlite3Reprepare().
! 537: #
! 538: do_test analyze3-4.3.1 {
! 539: db auth {}
! 540: set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE a=? AND b>?" -1 dummy]
! 541: execsql { CREATE TABLE t2(d, e, f) }
! 542: db auth auth
! 543: sqlite3_step $S
! 544: } {SQLITE_AUTH}
! 545: do_test analyze3-4.3.2 {
! 546: sqlite3_finalize $S
! 547: } {SQLITE_AUTH}
! 548: db auth {}
! 549:
! 550: #-------------------------------------------------------------------------
! 551: # Test that modifying bound variables using the clear_bindings() or
! 552: # transfer_bindings() APIs works.
! 553: #
! 554: # analyze3-5.1.*: sqlite3_clear_bindings()
! 555: # analyze3-5.2.*: sqlite3_transfer_bindings()
! 556: #
! 557: do_test analyze3-5.1.1 {
! 558: drop_all_tables
! 559: execsql {
! 560: CREATE TABLE t1(x TEXT COLLATE NOCASE);
! 561: CREATE INDEX i1 ON t1(x);
! 562: INSERT INTO t1 VALUES('aaa');
! 563: INSERT INTO t1 VALUES('abb');
! 564: INSERT INTO t1 VALUES('acc');
! 565: INSERT INTO t1 VALUES('baa');
! 566: INSERT INTO t1 VALUES('bbb');
! 567: INSERT INTO t1 VALUES('bcc');
! 568: }
! 569:
! 570: set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE x LIKE ?" -1 dummy]
! 571: sqlite3_bind_text $S 1 "a%" 2
! 572: set R [list]
! 573: while { "SQLITE_ROW" == [sqlite3_step $S] } {
! 574: lappend R [sqlite3_column_text $S 0]
! 575: }
! 576: concat [sqlite3_reset $S] $R
! 577: } {SQLITE_OK aaa abb acc}
! 578: do_test analyze3-5.1.2 {
! 579: sqlite3_clear_bindings $S
! 580: set R [list]
! 581: while { "SQLITE_ROW" == [sqlite3_step $S] } {
! 582: lappend R [sqlite3_column_text $S 0]
! 583: }
! 584: concat [sqlite3_reset $S] $R
! 585: } {SQLITE_OK}
! 586: do_test analyze3-5.1.3 {
! 587: sqlite3_finalize $S
! 588: } {SQLITE_OK}
! 589:
! 590: do_test analyze3-5.1.1 {
! 591: set S1 [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE x LIKE ?" -1 dummy]
! 592: sqlite3_bind_text $S1 1 "b%" 2
! 593: set R [list]
! 594: while { "SQLITE_ROW" == [sqlite3_step $S1] } {
! 595: lappend R [sqlite3_column_text $S1 0]
! 596: }
! 597: concat [sqlite3_reset $S1] $R
! 598: } {SQLITE_OK baa bbb bcc}
! 599:
! 600: do_test analyze3-5.1.2 {
! 601: set S2 [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE x = ?" -1 dummy]
! 602: sqlite3_bind_text $S2 1 "a%" 2
! 603: sqlite3_transfer_bindings $S2 $S1
! 604: set R [list]
! 605: while { "SQLITE_ROW" == [sqlite3_step $S1] } {
! 606: lappend R [sqlite3_column_text $S1 0]
! 607: }
! 608: concat [sqlite3_reset $S1] $R
! 609: } {SQLITE_OK aaa abb acc}
! 610: do_test analyze3-5.1.3 {
! 611: sqlite3_finalize $S2
! 612: sqlite3_finalize $S1
! 613: } {SQLITE_OK}
! 614:
! 615: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to analyze3.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