diff options
Diffstat (limited to 'test/analyze8.test')
| -rw-r--r-- | test/analyze8.test | 36 |
1 files changed, 24 insertions, 12 deletions
diff --git a/test/analyze8.test b/test/analyze8.test index f3e2710..4384c39 100644 --- a/test/analyze8.test +++ b/test/analyze8.test @@ -16,7 +16,7 @@ set testdir [file dirname $argv0] source $testdir/tester.tcl -ifcapable !stat3 { +ifcapable !stat4&&!stat3 { finish_test return } @@ -61,43 +61,55 @@ do_test 1.0 { # do_test 1.1 { eqp {SELECT * FROM t1 WHERE a=100 AND b=55} -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?) (~2 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}} do_test 1.2 { eqp {SELECT * FROM t1 WHERE a=99 AND b=55} -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}} do_test 1.3 { eqp {SELECT * FROM t1 WHERE a=101 AND b=55} -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}} do_test 1.4 { eqp {SELECT * FROM t1 WHERE a=100 AND b=56} -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?) (~2 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}} do_test 1.5 { eqp {SELECT * FROM t1 WHERE a=99 AND b=56} -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}} do_test 1.6 { eqp {SELECT * FROM t1 WHERE a=101 AND b=56} -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}} do_test 2.1 { eqp {SELECT * FROM t1 WHERE a=100 AND b BETWEEN 50 AND 54} -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?) (~2 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)}} # There are many more values of c between 0 and 100000 than there are # between 800000 and 900000. So t1c is more selective for the latter # range. +# +# Test 3.2 is a little unstable. It depends on the planner estimating +# that (b BETWEEN 50 AND 54) will match more rows than (c BETWEEN +# 800000 AND 900000). Which is a pretty close call (50 vs. 32), so +# the planner could get it wrong with an unlucky set of samples. This +# case happens to work, but others ("b BETWEEN 40 AND 44" for example) +# will fail. # +do_execsql_test 3.0 { + SELECT count(*) FROM t1 WHERE b BETWEEN 50 AND 54; + SELECT count(*) FROM t1 WHERE c BETWEEN 0 AND 100000; + SELECT count(*) FROM t1 WHERE c BETWEEN 800000 AND 900000; +} {50 376 32} do_test 3.1 { eqp {SELECT * FROM t1 WHERE b BETWEEN 50 AND 54 AND c BETWEEN 0 AND 100000} -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?) (~6 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)}} do_test 3.2 { eqp {SELECT * FROM t1 WHERE b BETWEEN 50 AND 54 AND c BETWEEN 800000 AND 900000} -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?) (~4 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?)}} do_test 3.3 { eqp {SELECT * FROM t1 WHERE a=100 AND c BETWEEN 0 AND 100000} -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~63 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}} do_test 3.4 { eqp {SELECT * FROM t1 WHERE a=100 AND c BETWEEN 800000 AND 900000} -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?) (~2 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?)}} finish_test |