Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Update some test cases to work with sqlite_stat3 instead of sqlite_stat2. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | stat3-enhancement |
| Files: | files | file ages | folders |
| SHA1: |
2504bcfb0cf14b5ce51db0af1269ac28 |
| User & Date: | drh 2011-08-15 12:58:23 |
Context
|
2011-08-16
| ||
| 01:15 | Add the analyze8.test test module for sqlite_stat3. (check-in: 2c83ac89 user: drh tags: stat3-enhancement) | |
|
2011-08-15
| ||
| 12:58 | Update some test cases to work with sqlite_stat3 instead of sqlite_stat2. (check-in: 2504bcfb user: drh tags: stat3-enhancement) | |
| 12:02 | Fix a couple of typos in comments in analyze.c. (check-in: ae31dc67 user: dan tags: stat3-enhancement) | |
Changes
Changes to test/alter.test.
843 843 #------------------------------------------------------------------------- 844 844 # Test that it is not possible to use ALTER TABLE on any system table. 845 845 # 846 846 set system_table_list {1 sqlite_master} 847 847 catchsql ANALYZE 848 848 ifcapable analyze { lappend system_table_list 2 sqlite_stat1 } 849 849 ifcapable stat2 { lappend system_table_list 3 sqlite_stat2 } 850 +ifcapable stat3 { lappend system_table_list 4 sqlite_stat3 } 850 851 851 852 foreach {tn tbl} $system_table_list { 852 853 do_test alter-15.$tn.1 { 853 854 catchsql "ALTER TABLE $tbl RENAME TO xyz" 854 855 } [list 1 "table $tbl may not be altered"] 855 856 856 857 do_test alter-15.$tn.2 { 857 858 catchsql "ALTER TABLE $tbl ADD COLUMN xyz" 858 859 } [list 1 "table $tbl may not be altered"] 859 860 } 860 861 861 862 862 863 finish_test
Changes to test/analyze3.test.
13 13 # implements tests for range and LIKE constraints that use bound variables 14 14 # instead of literal constant arguments. 15 15 # 16 16 17 17 set testdir [file dirname $argv0] 18 18 source $testdir/tester.tcl 19 19 20 -ifcapable !stat2 { 20 +ifcapable !stat3 { 21 21 finish_test 22 22 return 23 23 } 24 24 25 25 #---------------------------------------------------------------------- 26 26 # Test Organization: 27 27 # ................................................................................ 96 96 } {} 97 97 98 98 do_eqp_test analyze3-1.1.2 { 99 99 SELECT sum(y) FROM t1 WHERE x>200 AND x<300 100 100 } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x<?) (~179 rows)}} 101 101 do_eqp_test analyze3-1.1.3 { 102 102 SELECT sum(y) FROM t1 WHERE x>0 AND x<1100 103 -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? and x<?) {~959 rows)}} 103 +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x<?) (~959 rows)}} 104 104 105 105 do_test analyze3-1.1.4 { 106 106 sf_execsql { SELECT sum(y) FROM t1 WHERE x>200 AND x<300 } 107 107 } {199 0 14850} 108 108 do_test analyze3-1.1.5 { 109 109 set l [string range "200" 0 end] 110 110 set u [string range "300" 0 end] ................................................................................ 113 113 do_test analyze3-1.1.6 { 114 114 set l [expr int(200)] 115 115 set u [expr int(300)] 116 116 sf_execsql { SELECT sum(y) FROM t1 WHERE x>$l AND x<$u } 117 117 } {199 0 14850} 118 118 do_test analyze3-1.1.7 { 119 119 sf_execsql { SELECT sum(y) FROM t1 WHERE x>0 AND x<1100 } 120 -} {999 999 499500} 120 +} {2000 0 499500} 121 121 do_test analyze3-1.1.8 { 122 122 set l [string range "0" 0 end] 123 123 set u [string range "1100" 0 end] 124 124 sf_execsql { SELECT sum(y) FROM t1 WHERE x>$l AND x<$u } 125 -} {999 999 499500} 125 +} {2000 0 499500} 126 126 do_test analyze3-1.1.9 { 127 127 set l [expr int(0)] 128 128 set u [expr int(1100)] 129 129 sf_execsql { SELECT sum(y) FROM t1 WHERE x>$l AND x<$u } 130 -} {999 999 499500} 130 +} {2000 0 499500} 131 131 132 132 133 133 # The following tests are similar to the block above. The difference is 134 134 # that the indexed column has TEXT affinity in this case. In the tests 135 135 # above the affinity is INTEGER. 136 136 # 137 137 do_test analyze3-1.2.1 { ................................................................................ 142 142 CREATE INDEX i2 ON t2(x); 143 143 COMMIT; 144 144 ANALYZE; 145 145 } 146 146 } {} 147 147 do_eqp_test analyze3-1.2.2 { 148 148 SELECT sum(y) FROM t2 WHERE x>1 AND x<2 149 -} {0 0 0 {SEARCH TABLE t2 USING INDEX i2 (x>? AND x<?) (~200 rows)}} 149 +} {0 0 0 {SEARCH TABLE t2 USING INDEX i2 (x>? AND x<?) (~196 rows)}} 150 150 do_eqp_test analyze3-1.2.3 { 151 151 SELECT sum(y) FROM t2 WHERE x>0 AND x<99 152 -} {0 0 0 {SCAN TABLE t2 (~111 rows)}} 152 +} {0 0 0 {SEARCH TABLE t2 USING INDEX i2 (x>? AND x<?) (~982 rows)}} 153 153 do_test analyze3-1.2.4 { 154 154 sf_execsql { SELECT sum(y) FROM t2 WHERE x>12 AND x<20 } 155 155 } {161 0 4760} 156 156 do_test analyze3-1.2.5 { 157 157 set l [string range "12" 0 end] 158 158 set u [string range "20" 0 end] 159 159 sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u} ................................................................................ 161 161 do_test analyze3-1.2.6 { 162 162 set l [expr int(12)] 163 163 set u [expr int(20)] 164 164 sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u} 165 165 } {161 0 integer integer 4760} 166 166 do_test analyze3-1.2.7 { 167 167 sf_execsql { SELECT sum(y) FROM t2 WHERE x>0 AND x<99 } 168 -} {999 999 490555} 168 +} {1981 0 490555} 169 169 do_test analyze3-1.2.8 { 170 170 set l [string range "0" 0 end] 171 171 set u [string range "99" 0 end] 172 172 sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u} 173 -} {999 999 text text 490555} 173 +} {1981 0 text text 490555} 174 174 do_test analyze3-1.2.9 { 175 175 set l [expr int(0)] 176 176 set u [expr int(99)] 177 177 sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u} 178 -} {999 999 integer integer 490555} 178 +} {1981 0 integer integer 490555} 179 179 180 180 # Same tests a third time. This time, column x has INTEGER affinity and 181 181 # is not the leftmost column of the table. This triggered a bug causing 182 182 # SQLite to use sub-optimal query plans in 3.6.18 and earlier. 183 183 # 184 184 do_test analyze3-1.3.1 { 185 185 execsql { ................................................................................ 189 189 CREATE INDEX i3 ON t3(x); 190 190 COMMIT; 191 191 ANALYZE; 192 192 } 193 193 } {} 194 194 do_eqp_test analyze3-1.3.2 { 195 195 SELECT sum(y) FROM t3 WHERE x>200 AND x<300 196 -} {0 0 0 {SEARCH TABLE t3 USING INDEX i3 (x>? AND x<?) (~166 rows)}} 196 +} {0 0 0 {SEARCH TABLE t3 USING INDEX i3 (x>? AND x<?) (~156 rows)}} 197 197 do_eqp_test analyze3-1.3.3 { 198 198 SELECT sum(y) FROM t3 WHERE x>0 AND x<1100 199 -} {0 0 0 {SCAN TABLE t3 (~111 rows)}} 199 +} {0 0 0 {SEARCH TABLE t3 USING INDEX i3 (x>? AND x<?) (~989 rows)}} 200 200 201 201 do_test analyze3-1.3.4 { 202 202 sf_execsql { SELECT sum(y) FROM t3 WHERE x>200 AND x<300 } 203 203 } {199 0 14850} 204 204 do_test analyze3-1.3.5 { 205 205 set l [string range "200" 0 end] 206 206 set u [string range "300" 0 end] ................................................................................ 209 209 do_test analyze3-1.3.6 { 210 210 set l [expr int(200)] 211 211 set u [expr int(300)] 212 212 sf_execsql { SELECT sum(y) FROM t3 WHERE x>$l AND x<$u } 213 213 } {199 0 14850} 214 214 do_test analyze3-1.3.7 { 215 215 sf_execsql { SELECT sum(y) FROM t3 WHERE x>0 AND x<1100 } 216 -} {999 999 499500} 216 +} {2000 0 499500} 217 217 do_test analyze3-1.3.8 { 218 218 set l [string range "0" 0 end] 219 219 set u [string range "1100" 0 end] 220 220 sf_execsql { SELECT sum(y) FROM t3 WHERE x>$l AND x<$u } 221 -} {999 999 499500} 221 +} {2000 0 499500} 222 222 do_test analyze3-1.3.9 { 223 223 set l [expr int(0)] 224 224 set u [expr int(1100)] 225 225 sf_execsql { SELECT sum(y) FROM t3 WHERE x>$l AND x<$u } 226 -} {999 999 499500} 226 +} {2000 0 499500} 227 227 228 228 #------------------------------------------------------------------------- 229 229 # Test that the values of bound SQL variables may be used for the LIKE 230 230 # optimization. 231 231 # 232 232 drop_all_tables 233 233 do_test analyze3-2.1 {
Changes to test/analyze6.test.
13 13 # in this file a corner-case query planner optimization involving the 14 14 # join order of two tables of different sizes. 15 15 # 16 16 17 17 set testdir [file dirname $argv0] 18 18 source $testdir/tester.tcl 19 19 20 -ifcapable !stat2 { 20 +ifcapable !stat3 { 21 21 finish_test 22 22 return 23 23 } 24 24 25 25 set testprefix analyze6 26 26 27 27 proc eqp {sql {db db}} {
Changes to test/tkt-cbd054fa6b.test.
12 12 # This file implements tests to verify that ticket [cbd054fa6b] has been 13 13 # fixed. 14 14 # 15 15 16 16 set testdir [file dirname $argv0] 17 17 source $testdir/tester.tcl 18 18 19 -ifcapable !stat2 { 19 +ifcapable !stat3 { 20 20 finish_test 21 21 return 22 22 } 23 23 24 24 do_test tkt-cbd05-1.1 { 25 25 db eval { 26 26 CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT UNIQUE NOT NULL); ................................................................................ 42 42 db eval { 43 43 ANALYZE; 44 44 } 45 45 } {} 46 46 do_test tkt-cbd05-1.3 { 47 47 execsql { 48 48 SELECT tbl,idx,group_concat(sample,' ') 49 - FROM sqlite_stat2 49 + FROM sqlite_stat3 50 50 WHERE idx = 't1_x' 51 51 GROUP BY tbl,idx 52 52 } 53 53 } {t1 t1_x { A B C D E F G H I}} 54 54 55 55 do_test tkt-cbd05-2.1 { 56 56 db eval { ................................................................................ 74 74 db eval { 75 75 ANALYZE; 76 76 } 77 77 } {} 78 78 do_test tkt-cbd05-2.3 { 79 79 execsql { 80 80 SELECT tbl,idx,group_concat(sample,' ') 81 - FROM sqlite_stat2 81 + FROM sqlite_stat3 82 82 WHERE idx = 't1_x' 83 83 GROUP BY tbl,idx 84 84 } 85 85 } {t1 t1_x { A B C D E F G H I}} 86 86 87 87 finish_test