AryaWu/sqlite
0
1# 2009 August 062#3# The author disclaims copyright to this source code. In place of4# 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 variables14# instead of literal constant arguments.15#16 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19set testprefix analyze320 21ifcapable !stat4 {22 finish_test23 return24}25 26# This test cannot be run with the sqlite3_prepare() permutation, as it27# tests that stat4 data can be used to influence the plans of queries28# based on bound variable values. And this is not possible when using29# sqlite3_prepare() - as queries cannot be internally re-prepared after30# binding values are available.31if {[permutation]=="prepare"} {32 finish_test33 return34}35 36#----------------------------------------------------------------------37# Test Organization:38#39# analyze3-1.*: Test that the values of bound parameters are considered 40# in the same way as constants when planning queries that41# use range constraints.42#43# analyze3-2.*: Test that the values of bound parameters are considered 44# in the same way as constants when planning queries that45# use LIKE expressions in the WHERE clause.46#47# analyze3-3.*: Test that binding to a variable does not invalidate the 48# query plan when there is no way in which replanning the49# query may produce a superior outcome.50#51# analyze3-4.*: Test that SQL or authorization callback errors occurring52# within sqlite3Reprepare() are handled correctly.53#54# analyze3-5.*: Check that the query plans of applicable statements are55# invalidated if the values of SQL parameter are modified56# using the clear_bindings() or transfer_bindings() APIs.57# 58# analyze3-6.*: Test that the problem fixed by commit [127a5b776d] is fixed.59#60# analyze3-7.*: Test that some memory leaks discovered by fuzz testing 61# have been fixed.62#63 64proc getvar {varname} { uplevel #0 set $varname }65db function var getvar66 67proc eqp {sql {db db}} {68 uplevel execsql [list "EXPLAIN QUERY PLAN $sql"] $db69}70 71proc sf_execsql {sql {db db}} {72 set ::sqlite_search_count 073 set r [uplevel [list execsql $sql $db]]74 75 concat $::sqlite_search_count [$db status step] $r76}77 78#-------------------------------------------------------------------------79#80# analyze3-1.1.1: 81# Create a table with two columns. Populate the first column (affinity 82# INTEGER) with integer values from 100 to 1100. Create an index on this 83# column. ANALYZE the table.84#85# analyze3-1.1.2 - 3.1.386# Show that there are two possible plans for querying the table with87# a range constraint on the indexed column - "full table scan" or "use 88# the index". When the range is specified using literal values, SQLite89# is able to pick the best plan based on the samples in sqlite_stat3.90#91# analyze3-1.1.4 - 3.1.992# Show that using SQL variables produces the same results as using93# literal values to constrain the range scan.94#95# These tests also check that the compiler code considers column 96# affinities when estimating the number of rows scanned by the "use 97# index strategy".98#99do_test analyze3-1.1.1 {100 execsql {101 BEGIN;102 CREATE TABLE t1(x INTEGER, y);103 CREATE INDEX i1 ON t1(x);104 }105 for {set i 0} {$i < 1000} {incr i} {106 execsql { INSERT INTO t1 VALUES($i+100, $i) }107 }108 execsql {109 COMMIT;110 ANALYZE;111 }112 113 execsql { SELECT count(*)>0 FROM sqlite_stat4; }114} {1}115 116do_execsql_test analyze3-1.1.x {117 SELECT count(*) FROM t1 WHERE x>200 AND x<300;118 SELECT count(*) FROM t1 WHERE x>0 AND x<1100;119} {99 1000}120 121# The first of the following two SELECT statements visits 99 rows. So122# it is better to use the index. But the second visits every row in 123# the table (1000 in total) so it is better to do a full-table scan.124#125do_eqp_test analyze3-1.1.2 {126 SELECT sum(y) FROM t1 WHERE x>200 AND x<300127} {SEARCH t1 USING INDEX i1 (x>? AND x<?)}128do_eqp_test analyze3-1.1.3 {129 SELECT sum(y) FROM t1 WHERE x>0 AND x<1100 130} {SCAN t1}131 132# 2017-06-26: Verify that the SQLITE_DBCONFIG_ENABLE_QPSG setting disables133# the use of bound parameters by STAT4134#135db cache flush136unset -nocomplain l137unset -nocomplain u138do_eqp_test analyze3-1.1.3.100 {139 SELECT sum(y) FROM t1 WHERE x>$l AND x<$u140} {SEARCH t1 USING INDEX i1 (x>? AND x<?)}141set l 200142set u 300143do_eqp_test analyze3-1.1.3.101 {144 SELECT sum(y) FROM t1 WHERE x>$l AND x<$u145} {SEARCH t1 USING INDEX i1 (x>? AND x<?)}146set l 0147set u 1100148do_eqp_test analyze3-1.1.3.102 {149 SELECT sum(y) FROM t1 WHERE x>$l AND x<$u150} {SCAN t1}151db cache flush152sqlite3_db_config db ENABLE_QPSG 1153do_eqp_test analyze3-1.1.3.103 {154 SELECT sum(y) FROM t1 WHERE x>$l AND x<$u155} {SEARCH t1 USING INDEX i1 (x>? AND x<?)}156db cache flush157sqlite3_db_config db ENABLE_QPSG 0158do_eqp_test analyze3-1.1.3.104 {159 SELECT sum(y) FROM t1 WHERE x>$l AND x<$u160} {SCAN t1}161 162do_test analyze3-1.1.4 {163 sf_execsql { SELECT sum(y) FROM t1 WHERE x>200 AND x<300 }164} {199 0 14850}165do_test analyze3-1.1.5 {166 set l [string range "200" 0 end]167 set u [string range "300" 0 end]168 sf_execsql { SELECT sum(y) FROM t1 WHERE x>$l AND x<$u }169} {199 0 14850}170do_test analyze3-1.1.6 {171 set l [expr int(200)]172 set u [expr int(300)]173 sf_execsql { SELECT sum(y) FROM t1 WHERE x>$l AND x<$u }174} {199 0 14850}175do_test analyze3-1.1.7 {176 sf_execsql { SELECT sum(y) FROM t1 WHERE x>0 AND x<1100 }177} {999 999 499500}178do_test analyze3-1.1.8 {179 set l [string range "0" 0 end]180 set u [string range "1100" 0 end]181 sf_execsql { SELECT sum(y) FROM t1 WHERE x>$l AND x<$u }182} {999 999 499500}183do_test analyze3-1.1.9 {184 set l [expr int(0)]185 set u [expr int(1100)]186 sf_execsql { SELECT sum(y) FROM t1 WHERE x>$l AND x<$u }187} {999 999 499500}188 189 190# The following tests are similar to the block above. The difference is191# that the indexed column has TEXT affinity in this case. In the tests192# above the affinity is INTEGER.193#194do_test analyze3-1.2.1 {195 execsql {196 BEGIN;197 CREATE TABLE t2(x TEXT, y);198 INSERT INTO t2 SELECT * FROM t1;199 CREATE INDEX i2 ON t2(x);200 COMMIT;201 ANALYZE;202 }203} {}204do_execsql_test analyze3-2.1.x {205 SELECT count(*) FROM t2 WHERE x>1 AND x<2;206 SELECT count(*) FROM t2 WHERE x>0 AND x<99;207} {200 990}208do_eqp_test analyze3-1.2.2 {209 SELECT sum(y) FROM t2 WHERE x>1 AND x<2210} {SEARCH t2 USING INDEX i2 (x>? AND x<?)}211do_eqp_test analyze3-1.2.3 {212 SELECT sum(y) FROM t2 WHERE x>0 AND x<99213} {SCAN t2}214 215do_test analyze3-1.2.4 {216 sf_execsql { SELECT sum(y) FROM t2 WHERE x>12 AND x<20 }217} {161 0 4760}218do_test analyze3-1.2.5 {219 set l [string range "12" 0 end]220 set u [string range "20" 0 end]221 sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u}222} {161 0 text text 4760}223do_test analyze3-1.2.6 {224 set l [expr int(12)]225 set u [expr int(20)]226 sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u}227} {161 0 integer integer 4760}228do_test analyze3-1.2.7 {229 sf_execsql { SELECT sum(y) FROM t2 WHERE x>0 AND x<99 }230} {999 999 490555}231do_test analyze3-1.2.8 {232 set l [string range "0" 0 end]233 set u [string range "99" 0 end]234 sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u}235} {999 999 text text 490555}236do_test analyze3-1.2.9 {237 set l [expr int(0)]238 set u [expr int(99)]239 sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u}240} {999 999 integer integer 490555}241 242# Same tests a third time. This time, column x has INTEGER affinity and243# is not the leftmost column of the table. This triggered a bug causing244# SQLite to use sub-optimal query plans in 3.6.18 and earlier.245#246do_test analyze3-1.3.1 {247 execsql {248 BEGIN;249 CREATE TABLE t3(y TEXT, x INTEGER);250 INSERT INTO t3 SELECT y, x FROM t1;251 CREATE INDEX i3 ON t3(x);252 COMMIT;253 ANALYZE;254 }255} {}256do_execsql_test analyze3-1.3.x {257 SELECT count(*) FROM t3 WHERE x>200 AND x<300;258 SELECT count(*) FROM t3 WHERE x>0 AND x<1100259} {99 1000}260do_eqp_test analyze3-1.3.2 {261 SELECT sum(y) FROM t3 WHERE x>200 AND x<300262} {SEARCH t3 USING INDEX i3 (x>? AND x<?)}263do_eqp_test analyze3-1.3.3 {264 SELECT sum(y) FROM t3 WHERE x>0 AND x<1100265} {SCAN t3}266 267do_test analyze3-1.3.4 {268 sf_execsql { SELECT sum(y) FROM t3 WHERE x>200 AND x<300 }269} {199 0 14850}270do_test analyze3-1.3.5 {271 set l [string range "200" 0 end]272 set u [string range "300" 0 end]273 sf_execsql { SELECT sum(y) FROM t3 WHERE x>$l AND x<$u }274} {199 0 14850}275do_test analyze3-1.3.6 {276 set l [expr int(200)]277 set u [expr int(300)]278 sf_execsql { SELECT sum(y) FROM t3 WHERE x>$l AND x<$u }279} {199 0 14850}280do_test analyze3-1.3.7 {281 sf_execsql { SELECT sum(y) FROM t3 WHERE x>0 AND x<1100 }282} {999 999 499500}283do_test analyze3-1.3.8 {284 set l [string range "0" 0 end]285 set u [string range "1100" 0 end]286 sf_execsql { SELECT sum(y) FROM t3 WHERE x>$l AND x<$u }287} {999 999 499500}288do_test analyze3-1.3.9 {289 set l [expr int(0)]290 set u [expr int(1100)]291 sf_execsql { SELECT sum(y) FROM t3 WHERE x>$l AND x<$u }292} {999 999 499500}293 294#-------------------------------------------------------------------------295# Test that the values of bound SQL variables may be used for the LIKE296# optimization.297#298drop_all_tables299do_test analyze3-2.1 {300 execsql {301 PRAGMA case_sensitive_like=off;302 BEGIN;303 CREATE TABLE t1(a, b TEXT COLLATE nocase);304 CREATE INDEX i1 ON t1(b);305 }306 for {set i 0} {$i < 1000} {incr i} {307 set t ""308 append t [lindex {a b c d e f g h i j} [expr $i/100]]309 append t [lindex {a b c d e f g h i j} [expr ($i/10)%10]]310 append t [lindex {a b c d e f g h i j} [expr ($i%10)]]311 execsql { INSERT INTO t1 VALUES($i, $t) }312 }313 execsql COMMIT314} {}315do_eqp_test analyze3-2.2 {316 SELECT count(a) FROM t1 WHERE b LIKE 'a%'317} {SEARCH t1 USING INDEX i1 (b>? AND b<?)}318do_eqp_test analyze3-2.3 {319 SELECT count(a) FROM t1 WHERE b LIKE '%a'320} {SCAN t1}321 322# Return the first argument if like_match_blobs is true (the default)323# or the second argument if not324#325proc ilmb {a b} {326 ifcapable like_match_blobs {return $a}327 return $b328}329 330do_test analyze3-2.4 {331 sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE 'a%' }332} [list [ilmb 102 101] 0 100]333do_test analyze3-2.5 {334 sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE '%a' }335} {999 999 100}336 337do_test analyze3-2.6 {338 set like "a%"339 sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }340} [list [ilmb 102 101] 0 100]341do_test analyze3-2.7 {342 set like "%a"343 sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }344} {999 999 100}345do_test analyze3-2.8 {346 set like "a"347 sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }348} [list [ilmb 102 101] 0 0]349do_test analyze3-2.9 {350 set like "ab"351 sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }352} [list [ilmb 12 11] 0 0]353do_test analyze3-2.10 {354 set like "abc"355 sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }356} [list [ilmb 3 2] 0 1]357do_test analyze3-2.11 {358 set like "a_c"359 sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }360} [list [ilmb 102 101] 0 10]361 362 363#-------------------------------------------------------------------------364# This block of tests checks that statements are correctly marked as365# expired when the values bound to any parameters that may affect the 366# query plan are modified.367#368drop_all_tables369db auth auth370proc auth {args} {371 set ::auth 1372 return SQLITE_OK373}374 375do_test analyze3-3.1 {376 execsql {377 BEGIN;378 CREATE TABLE t1(a, b, c);379 CREATE INDEX i1 ON t1(b);380 }381 for {set i 0} {$i < 100} {incr i} {382 execsql { INSERT INTO t1 VALUES($i, $i, $i) }383 }384 execsql COMMIT385 execsql ANALYZE386} {}387do_test analyze3-3.2.1 {388 set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE b>?" -1 dummy]389 sqlite3_expired $S390} {0}391do_test analyze3-3.2.2 {392 sqlite3_bind_text $S 1 "abc" 3393 sqlite3_expired $S394} {1}395do_test analyze3-3.2.4 {396 sqlite3_finalize $S397} {SQLITE_OK}398 399do_test analyze3-3.2.5 {400 set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE b=?" -1 dummy]401 sqlite3_expired $S402} {0}403do_test analyze3-3.2.6 {404 sqlite3_bind_text $S 1 "abc" 3405 sqlite3_expired $S406} {1}407do_test analyze3-3.2.7 {408 sqlite3_finalize $S409} {SQLITE_OK}410 411do_test analyze3-3.4.1 {412 set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE a=? AND b>?" -1 dummy]413 sqlite3_expired $S414} {0}415do_test analyze3-3.4.2 {416 sqlite3_bind_text $S 1 "abc" 3417 sqlite3_expired $S418} {0}419do_test analyze3-3.4.3 {420 sqlite3_bind_text $S 2 "def" 3421 sqlite3_expired $S422} {1}423do_test analyze3-3.4.4 {424 sqlite3_bind_text $S 2 "ghi" 3425 sqlite3_expired $S426} {1}427do_test analyze3-3.4.5 {428 sqlite3_expired $S429} {1}430do_test analyze3-3.4.6 {431 sqlite3_finalize $S432} {SQLITE_OK}433 434do_test analyze3-3.5.1 {435 set S [sqlite3_prepare_v2 db {436 SELECT * FROM t1 WHERE a IN (437 ?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10,438 ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18, ?19, ?20,439 ?21, ?22, ?23, ?24, ?25, ?26, ?27, ?28, ?29, ?30, ?31440 ) AND b>?32;441 } -1 dummy]442 sqlite3_expired $S443} {0}444do_test analyze3-3.5.2 {445 sqlite3_bind_text $S 31 "abc" 3446 sqlite3_expired $S447} {0}448do_test analyze3-3.5.3 {449 sqlite3_bind_text $S 32 "def" 3450 sqlite3_expired $S451} {1}452do_test analyze3-3.5.5 {453 sqlite3_finalize $S454} {SQLITE_OK}455 456do_test analyze3-3.6.1 {457 set S [sqlite3_prepare_v2 db {458 SELECT * FROM t1 WHERE a IN (459 ?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10,460 ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18, ?19, ?20,461 ?21, ?22, ?23, ?24, ?25, ?26, ?27, ?28, ?29, ?30, ?31, ?32462 ) AND b>?33;463 } -1 dummy]464 sqlite3_expired $S465} {0}466do_test analyze3-3.6.2 {467 sqlite3_bind_text $S 32 "abc" 3468 sqlite3_expired $S469} {1}470do_test analyze3-3.6.3 {471 sqlite3_bind_text $S 33 "def" 3472 sqlite3_expired $S473} {1}474do_test analyze3-3.6.5 {475 sqlite3_finalize $S476} {SQLITE_OK}477 478do_test analyze3-3.7.1 {479 set S [sqlite3_prepare_v2 db {480 SELECT * FROM t1 WHERE a IN (481 ?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?33,482 ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18, ?19, ?20,483 ?21, ?22, ?23, ?24, ?25, ?26, ?27, ?28, ?29, ?30, ?31, ?32484 ) AND b>?10;485 } -1 dummy]486 sqlite3_expired $S487} {0}488do_test analyze3-3.7.2 {489 sqlite3_bind_text $S 32 "abc" 3490 sqlite3_expired $S491} {0}492do_test analyze3-3.7.3 {493 sqlite3_bind_text $S 33 "def" 3494 sqlite3_expired $S495} {0}496do_test analyze3-3.7.4 {497 sqlite3_bind_text $S 10 "def" 3498 sqlite3_expired $S499} {1}500do_test analyze3-3.7.6 {501 sqlite3_finalize $S502} {SQLITE_OK}503 504do_test analyze3-3.8.1 {505 execsql {506 CREATE TABLE t4(x, y TEXT COLLATE NOCASE);507 CREATE INDEX i4 ON t4(y);508 }509} {}510do_test analyze3-3.8.2 {511 set S [sqlite3_prepare_v2 db {512 SELECT * FROM t4 WHERE x != ? AND y LIKE ?513 } -1 dummy]514 sqlite3_expired $S515} {0}516do_test analyze3-3.8.3 {517 sqlite3_bind_text $S 1 "abc" 3518 sqlite3_expired $S519} {0}520do_test analyze3-3.8.4 {521 sqlite3_bind_text $S 2 "def" 3522 sqlite3_expired $S523} {1}524do_test analyze3-3.8.7 {525 sqlite3_bind_text $S 2 "ghi%" 4526 sqlite3_expired $S527} {1}528do_test analyze3-3.8.8 {529 sqlite3_expired $S530} {1}531do_test analyze3-3.8.9 {532 sqlite3_bind_text $S 2 "ghi%def" 7533 sqlite3_expired $S534} {1}535do_test analyze3-3.8.10 {536 sqlite3_expired $S537} {1}538do_test analyze3-3.8.11 {539 sqlite3_bind_text $S 2 "%ab" 3540 sqlite3_expired $S541} {1}542do_test analyze3-3.8.12 {543 sqlite3_expired $S544} {1}545do_test analyze3-3.8.12 {546 sqlite3_bind_text $S 2 "%de" 3547 sqlite3_expired $S548} {1}549do_test analyze3-3.8.13 {550 sqlite3_expired $S551} {1}552do_test analyze3-3.8.14 {553 sqlite3_finalize $S554} {SQLITE_OK}555 556#-------------------------------------------------------------------------557# These tests check that errors encountered while repreparing an SQL558# statement within sqlite3Reprepare() are handled correctly.559#560 561# Check a schema error.562#563do_test analyze3-4.1.1 {564 set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE a=? AND b>?" -1 dummy]565 sqlite3_step $S566} {SQLITE_DONE}567do_test analyze3-4.1.2 {568 sqlite3_reset $S569 sqlite3_bind_text $S 2 "abc" 3570 execsql { DROP TABLE t1 }571 sqlite3_step $S572} {SQLITE_ERROR}573do_test analyze3-4.1.3 {574 sqlite3_finalize $S575} {SQLITE_ERROR}576 577# Check an authorization error.578#579do_test analyze3-4.2.1 {580 execsql {581 BEGIN;582 CREATE TABLE t1(a, b, c);583 CREATE INDEX i1 ON t1(b);584 }585 for {set i 0} {$i < 100} {incr i} {586 execsql { INSERT INTO t1 VALUES($i, $i, $i) }587 }588 execsql COMMIT589 execsql ANALYZE590 set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE a=? AND b>?" -1 dummy]591 sqlite3_step $S592} {SQLITE_DONE}593db auth auth594proc auth {args} {595 if {[lindex $args 0] == "SQLITE_READ"} {return SQLITE_DENY}596 return SQLITE_OK597}598do_test analyze3-4.2.2 {599 sqlite3_reset $S600 sqlite3_bind_text $S 2 "abc" 3601 sqlite3_step $S602} {SQLITE_AUTH}603do_test analyze3-4.2.4 {604 sqlite3_finalize $S605} {SQLITE_AUTH}606 607# Check the effect of an authorization error that occurs in a re-prepare608# performed by sqlite3_step() is the same as one that occurs within609# sqlite3Reprepare().610#611do_test analyze3-4.3.1 {612 db auth {}613 set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE a=? AND b>?" -1 dummy]614 execsql { CREATE TABLE t2(d, e, f) }615 db auth auth616 sqlite3_step $S617} {SQLITE_AUTH}618do_test analyze3-4.3.2 {619 sqlite3_finalize $S620} {SQLITE_AUTH}621db auth {}622 623#-------------------------------------------------------------------------624# Test that modifying bound variables using the clear_bindings() or625# transfer_bindings() APIs works.626#627# analyze3-5.1.*: sqlite3_clear_bindings()628# analyze3-5.2.*: sqlite3_transfer_bindings()629#630do_test analyze3-5.1.1 {631 drop_all_tables632 execsql {633 CREATE TABLE t1(x TEXT COLLATE NOCASE);634 CREATE INDEX i1 ON t1(x);635 INSERT INTO t1 VALUES('aaa');636 INSERT INTO t1 VALUES('abb');637 INSERT INTO t1 VALUES('acc');638 INSERT INTO t1 VALUES('baa');639 INSERT INTO t1 VALUES('bbb');640 INSERT INTO t1 VALUES('bcc');641 }642 643 set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE x LIKE ?" -1 dummy]644 sqlite3_bind_text $S 1 "a%" 2645 set R [list]646 while { "SQLITE_ROW" == [sqlite3_step $S] } {647 lappend R [sqlite3_column_text $S 0]648 }649 concat [sqlite3_reset $S] $R650} {SQLITE_OK aaa abb acc}651do_test analyze3-5.1.2 {652 sqlite3_clear_bindings $S653 set R [list]654 while { "SQLITE_ROW" == [sqlite3_step $S] } {655 lappend R [sqlite3_column_text $S 0]656 }657 concat [sqlite3_reset $S] $R658} {SQLITE_OK}659do_test analyze3-5.1.3 {660 sqlite3_finalize $S661} {SQLITE_OK}662 663do_test analyze3-5.1.1 {664 set S1 [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE x LIKE ?" -1 dummy]665 sqlite3_bind_text $S1 1 "b%" 2666 set R [list]667 while { "SQLITE_ROW" == [sqlite3_step $S1] } {668 lappend R [sqlite3_column_text $S1 0]669 }670 concat [sqlite3_reset $S1] $R671} {SQLITE_OK baa bbb bcc}672 673do_test analyze3-5.1.2 {674 set S2 [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE x = ?" -1 dummy]675 sqlite3_bind_text $S2 1 "a%" 2676 sqlite3_transfer_bindings $S2 $S1677 set R [list]678 while { "SQLITE_ROW" == [sqlite3_step $S1] } {679 lappend R [sqlite3_column_text $S1 0]680 }681 concat [sqlite3_reset $S1] $R682} {SQLITE_OK aaa abb acc}683do_test analyze3-5.1.3 {684 sqlite3_finalize $S2685 sqlite3_finalize $S1686} {SQLITE_OK}687 688#-------------------------------------------------------------------------689 690do_test analyze3-6.1 {691 execsql { DROP TABLE IF EXISTS t1 }692 execsql BEGIN693 execsql { CREATE TABLE t1(a, b, c) }694 for {set i 0} {$i < 1000} {incr i} {695 execsql "INSERT INTO t1 VALUES([expr $i/100], 'x', [expr $i/10])"696 }697 execsql {698 CREATE INDEX i1 ON t1(a, b);699 CREATE INDEX i2 ON t1(c);700 }701 execsql COMMIT702 execsql ANALYZE703} {}704 705do_eqp_test analyze3-6-3 {706 SELECT * FROM t1 WHERE a = 5 AND c = 13;707} {SEARCH t1 USING INDEX i2 (c=?)}708 709do_eqp_test analyze3-6-2 {710 SELECT * FROM t1 WHERE a = 5 AND b > 'w' AND c = 13;711} {SEARCH t1 USING INDEX i2 (c=?)}712 713#-----------------------------------------------------------------------------714# 2015-04-20.715# Memory leak in sqlite3Stat4ProbeFree(). (Discovered while fuzzing.)716#717do_execsql_test analyze-7.1 {718 DROP TABLE IF EXISTS t1;719 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);720 INSERT INTO t1 VALUES(1,1,'0000');721 CREATE INDEX t0b ON t1(b);722 ANALYZE;723 SELECT c FROM t1 WHERE b=3 AND a BETWEEN 30 AND hex(1);724} {}725 726# At one point duplicate stat1 entries were causing a memory leak.727#728reset_db729do_execsql_test 7.2 {730 CREATE TABLE t1(a,b,c);731 CREATE INDEX t1a ON t1(a);732 ANALYZE;733 SELECT * FROM sqlite_stat1;734 INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t1','t1a','12000');735 INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t1','t1a','12000');736 ANALYZE sqlite_master;737}738 739# 2023-04-22 https://sqlite.org/forum/info/6c118daad0f1f5ef740# Case differences in the sqlite_stat4.idx field should not matter.741#742reset_db743do_execsql_test 8.0 {744 CREATE TABLE t1(a PRIMARY KEY, v) WITHOUT ROWID;745 ANALYZE sqlite_schema;746 INSERT INTO sqlite_stat1 VALUES('t1','t1','1 1');747 INSERT INTO sqlite_stat4 VALUES('t1','t1','1','0','0',X'021b76657273696f6e');748 INSERT INTO sqlite_stat4 VALUES('T1','T1','1','0','0',X'021b76657273696f6e');749 ANALYZE sqlite_schema;750} {}751 752# 2023-05-03 https://sqlite.org/forum/forumpost/537d8ab118753# Same index appears by two different names in the sqlite_stat4 table.754#755reset_db756do_execsql_test 8.1 {757 CREATE TABLE t1(a INT PRIMARY KEY, b INT) WITHOUT ROWID;758 ANALYZE sqlite_schema;759 INSERT INTO sqlite_stat4 VALUES760 ('t1','t1','1','2','2',X'03000103'),761 ('t1','sqlite_autoindex_t1_1','1','2','2',X'03000103');762 ANALYZE sqlite_schema;763 PRAGMA integrity_check;764} {ok}765 766finish_test767