File:  [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test / analyze3.test
Revision 1.1.1.1 (vendor branch): download - view: text, annotated - select for diffs - revision graph
Tue Feb 21 17:04:16 2012 UTC (12 years, 10 months ago) by misho
Branches: sqlite3, MAIN
CVS tags: v3_7_10, HEAD
sqlite3

    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>