summaryrefslogtreecommitdiff
path: root/test/analyze3.test
diff options
context:
space:
mode:
Diffstat (limited to 'test/analyze3.test')
-rw-r--r--test/analyze3.test92
1 files changed, 71 insertions, 21 deletions
diff --git a/test/analyze3.test b/test/analyze3.test
index f705bc6..e7416d5 100644
--- a/test/analyze3.test
+++ b/test/analyze3.test
@@ -17,7 +17,7 @@
set testdir [file dirname $argv0]
source $testdir/tester.tcl
-ifcapable !stat3 {
+ifcapable !stat4&&!stat3 {
finish_test
return
}
@@ -43,6 +43,8 @@ ifcapable !stat3 {
# 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 }
@@ -93,14 +95,29 @@ 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_execsql_test analyze3-1.1.x {
+ SELECT count(*) FROM t1 WHERE x>200 AND x<300;
+ SELECT count(*) FROM t1 WHERE x>0 AND x<1100;
+} {99 1000}
+
+# The first of the following two SELECT statements visits 99 rows. So
+# it is better to use the index. But the second visits every row in
+# the table (1000 in total) so it is better to do a full-table scan.
+#
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<?) (~179 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 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x<?) (~959 rows)}}
+} {0 0 0 {SCAN TABLE t1}}
do_test analyze3-1.1.4 {
sf_execsql { SELECT sum(y) FROM t1 WHERE x>200 AND x<300 }
@@ -117,17 +134,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 }
-} {2000 0 499500}
+} {999 999 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 }
-} {2000 0 499500}
+} {999 999 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 }
-} {2000 0 499500}
+} {999 999 499500}
# The following tests are similar to the block above. The difference is
@@ -144,12 +161,17 @@ do_test analyze3-1.2.1 {
ANALYZE;
}
} {}
+do_execsql_test analyze3-2.1.x {
+ SELECT count(*) FROM t2 WHERE x>1 AND x<2;
+ SELECT count(*) FROM t2 WHERE x>0 AND x<99;
+} {200 990}
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<?) (~196 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 {SEARCH TABLE t2 USING INDEX i2 (x>? AND x<?) (~968 rows)}}
+} {0 0 0 {SCAN TABLE t2}}
+
do_test analyze3-1.2.4 {
sf_execsql { SELECT sum(y) FROM t2 WHERE x>12 AND x<20 }
} {161 0 4760}
@@ -165,17 +187,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 }
-} {1981 0 490555}
+} {999 999 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}
-} {1981 0 text text 490555}
+} {999 999 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}
-} {1981 0 integer integer 490555}
+} {999 999 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
@@ -191,12 +213,16 @@ do_test analyze3-1.3.1 {
ANALYZE;
}
} {}
+do_execsql_test analyze3-1.3.x {
+ SELECT count(*) FROM t3 WHERE x>200 AND x<300;
+ SELECT count(*) FROM t3 WHERE x>0 AND x<1100
+} {99 1000}
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<?) (~156 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 {SEARCH TABLE t3 USING INDEX i3 (x>? AND x<?) (~989 rows)}}
+} {0 0 0 {SCAN TABLE t3}}
do_test analyze3-1.3.4 {
sf_execsql { SELECT sum(y) FROM t3 WHERE x>200 AND x<300 }
@@ -213,17 +239,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 }
-} {2000 0 499500}
+} {999 999 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 }
-} {2000 0 499500}
+} {999 999 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 }
-} {2000 0 499500}
+} {999 999 499500}
#-------------------------------------------------------------------------
# Test that the values of bound SQL variables may be used for the LIKE
@@ -248,10 +274,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<?) (~31250 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 +336,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 +355,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 +637,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
generated by cgit v1.2.3 (git 2.25.1) at 2024-05-09 03:48:12 +0000