From 569c6676a6ddb0ff73821d7693b5e18ddef809b9 Mon Sep 17 00:00:00 2001
From: Hans-Christoph Steiner <hans@eds.org>
Date: Thu, 16 Oct 2014 22:51:35 -0400
Subject: Imported Upstream version 3.2.0

---
 test/analyze8.test | 36 ++++++++++++++++++++++++------------
 1 file changed, 24 insertions(+), 12 deletions(-)

(limited to 'test/analyze8.test')

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
-- 
cgit v1.2.3