summaryrefslogtreecommitdiff
path: root/lang/sql/sqlite/test/analyze3.test
diff options
context:
space:
mode:
Diffstat (limited to 'lang/sql/sqlite/test/analyze3.test')
-rw-r--r--lang/sql/sqlite/test/analyze3.test76
1 files changed, 54 insertions, 22 deletions
diff --git a/lang/sql/sqlite/test/analyze3.test b/lang/sql/sqlite/test/analyze3.test
index 2378ffaa..fb26303e 100644
--- a/lang/sql/sqlite/test/analyze3.test
+++ b/lang/sql/sqlite/test/analyze3.test
@@ -17,7 +17,7 @@
set testdir [file dirname $argv0]
source $testdir/tester.tcl
-ifcapable !stat2 {
+ifcapable !stat4&&!stat3 {
finish_test
return
}
@@ -43,6 +43,8 @@ ifcapable !stat2 {
# analyze3-5.*: Check that the query plans of applicable statements are
# invalidated if the values of SQL parameter are modified
# using the clear_bindings() or transfer_bindings() APIs.
+#
+# analyze3-6.*: Test that the problem fixed by commit [127a5b776d] is fixed.
#
proc getvar {varname} { uplevel #0 set $varname }
@@ -70,7 +72,7 @@ proc sf_execsql {sql {db db}} {
# Show that there are two possible plans for querying the table with
# a range constraint on the indexed column - "full table scan" or "use
# the index". When the range is specified using literal values, SQLite
-# is able to pick the best plan based on the samples in sqlite_stat2.
+# is able to pick the best plan based on the samples in sqlite_stat3.
#
# analyze3-1.1.4 - 3.1.9
# Show that using SQL variables produces the same results as using
@@ -93,14 +95,20 @@ do_test analyze3-1.1.1 {
COMMIT;
ANALYZE;
}
-} {}
+
+ ifcapable stat4 {
+ execsql { SELECT count(*)>0 FROM sqlite_stat4; }
+ } else {
+ execsql { SELECT count(*)>0 FROM sqlite_stat3; }
+ }
+} {1}
do_eqp_test analyze3-1.1.2 {
SELECT sum(y) FROM t1 WHERE x>200 AND x<300
-} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x<?) (~100 rows)}}
+} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x<?)}}
do_eqp_test analyze3-1.1.3 {
SELECT sum(y) FROM t1 WHERE x>0 AND x<1100
-} {0 0 0 {SCAN TABLE t1 (~111 rows)}}
+} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x<?)}}
do_test analyze3-1.1.4 {
sf_execsql { SELECT sum(y) FROM t1 WHERE x>200 AND x<300 }
@@ -117,17 +125,17 @@ do_test analyze3-1.1.6 {
} {199 0 14850}
do_test analyze3-1.1.7 {
sf_execsql { SELECT sum(y) FROM t1 WHERE x>0 AND x<1100 }
-} {999 999 499500}
+} {2000 0 499500}
do_test analyze3-1.1.8 {
set l [string range "0" 0 end]
set u [string range "1100" 0 end]
sf_execsql { SELECT sum(y) FROM t1 WHERE x>$l AND x<$u }
-} {999 999 499500}
+} {2000 0 499500}
do_test analyze3-1.1.9 {
set l [expr int(0)]
set u [expr int(1100)]
sf_execsql { SELECT sum(y) FROM t1 WHERE x>$l AND x<$u }
-} {999 999 499500}
+} {2000 0 499500}
# The following tests are similar to the block above. The difference is
@@ -146,10 +154,10 @@ do_test analyze3-1.2.1 {
} {}
do_eqp_test analyze3-1.2.2 {
SELECT sum(y) FROM t2 WHERE x>1 AND x<2
-} {0 0 0 {SEARCH TABLE t2 USING INDEX i2 (x>? AND x<?) (~200 rows)}}
+} {0 0 0 {SEARCH TABLE t2 USING INDEX i2 (x>? AND x<?)}}
do_eqp_test analyze3-1.2.3 {
SELECT sum(y) FROM t2 WHERE x>0 AND x<99
-} {0 0 0 {SCAN TABLE t2 (~111 rows)}}
+} {0 0 0 {SEARCH TABLE t2 USING INDEX i2 (x>? AND x<?)}}
do_test analyze3-1.2.4 {
sf_execsql { SELECT sum(y) FROM t2 WHERE x>12 AND x<20 }
} {161 0 4760}
@@ -165,17 +173,17 @@ do_test analyze3-1.2.6 {
} {161 0 integer integer 4760}
do_test analyze3-1.2.7 {
sf_execsql { SELECT sum(y) FROM t2 WHERE x>0 AND x<99 }
-} {999 999 490555}
+} {1981 0 490555}
do_test analyze3-1.2.8 {
set l [string range "0" 0 end]
set u [string range "99" 0 end]
sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u}
-} {999 999 text text 490555}
+} {1981 0 text text 490555}
do_test analyze3-1.2.9 {
set l [expr int(0)]
set u [expr int(99)]
sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u}
-} {999 999 integer integer 490555}
+} {1981 0 integer integer 490555}
# Same tests a third time. This time, column x has INTEGER affinity and
# is not the leftmost column of the table. This triggered a bug causing
@@ -193,10 +201,10 @@ do_test analyze3-1.3.1 {
} {}
do_eqp_test analyze3-1.3.2 {
SELECT sum(y) FROM t3 WHERE x>200 AND x<300
-} {0 0 0 {SEARCH TABLE t3 USING INDEX i3 (x>? AND x<?) (~100 rows)}}
+} {0 0 0 {SEARCH TABLE t3 USING INDEX i3 (x>? AND x<?)}}
do_eqp_test analyze3-1.3.3 {
SELECT sum(y) FROM t3 WHERE x>0 AND x<1100
-} {0 0 0 {SCAN TABLE t3 (~111 rows)}}
+} {0 0 0 {SEARCH TABLE t3 USING INDEX i3 (x>? AND x<?)}}
do_test analyze3-1.3.4 {
sf_execsql { SELECT sum(y) FROM t3 WHERE x>200 AND x<300 }
@@ -213,17 +221,17 @@ do_test analyze3-1.3.6 {
} {199 0 14850}
do_test analyze3-1.3.7 {
sf_execsql { SELECT sum(y) FROM t3 WHERE x>0 AND x<1100 }
-} {999 999 499500}
+} {2000 0 499500}
do_test analyze3-1.3.8 {
set l [string range "0" 0 end]
set u [string range "1100" 0 end]
sf_execsql { SELECT sum(y) FROM t3 WHERE x>$l AND x<$u }
-} {999 999 499500}
+} {2000 0 499500}
do_test analyze3-1.3.9 {
set l [expr int(0)]
set u [expr int(1100)]
sf_execsql { SELECT sum(y) FROM t3 WHERE x>$l AND x<$u }
-} {999 999 499500}
+} {2000 0 499500}
#-------------------------------------------------------------------------
# Test that the values of bound SQL variables may be used for the LIKE
@@ -248,10 +256,10 @@ do_test analyze3-2.1 {
} {}
do_eqp_test analyze3-2.2 {
SELECT count(a) FROM t1 WHERE b LIKE 'a%'
-} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (b>? AND b<?) (~30000 rows)}}
+} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (b>? AND b<?)}}
do_eqp_test analyze3-2.3 {
SELECT count(a) FROM t1 WHERE b LIKE '%a'
-} {0 0 0 {SCAN TABLE t1 (~500000 rows)}}
+} {0 0 0 {SCAN TABLE t1}}
do_test analyze3-2.4 {
sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE 'a%' }
@@ -310,7 +318,6 @@ do_test analyze3-3.1 {
execsql COMMIT
execsql ANALYZE
} {}
-
do_test analyze3-3.2.1 {
set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE b>?" -1 dummy]
sqlite3_expired $S
@@ -330,7 +337,7 @@ do_test analyze3-3.2.5 {
do_test analyze3-3.2.6 {
sqlite3_bind_text $S 1 "abc" 3
sqlite3_expired $S
-} {0}
+} {1}
do_test analyze3-3.2.7 {
sqlite3_finalize $S
} {SQLITE_OK}
@@ -612,4 +619,29 @@ do_test analyze3-5.1.3 {
sqlite3_finalize $S1
} {SQLITE_OK}
+#-------------------------------------------------------------------------
+
+do_test analyze3-6.1 {
+ execsql { DROP TABLE IF EXISTS t1 }
+ execsql BEGIN
+ execsql { CREATE TABLE t1(a, b, c) }
+ for {set i 0} {$i < 1000} {incr i} {
+ execsql "INSERT INTO t1 VALUES([expr $i/100], 'x', [expr $i/10])"
+ }
+ execsql {
+ CREATE INDEX i1 ON t1(a, b);
+ CREATE INDEX i2 ON t1(c);
+ }
+ execsql COMMIT
+ execsql ANALYZE
+} {}
+
+do_eqp_test analyze3-6-3 {
+ SELECT * FROM t1 WHERE a = 5 AND c = 13;
+} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (c=?)}}
+
+do_eqp_test analyze3-6-2 {
+ SELECT * FROM t1 WHERE a = 5 AND b > 'w' AND c = 13;
+} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (c=?)}}
+
finish_test
View Lorry Controller Status