Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Remove more vestiges of sqlite_query_plan from the test cases. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | nextgen-query-plan-exp |
Files: | files | file ages | folders |
SHA1: |
eb27086e8a8a4d5fcb2ea358256a555e |
User & Date: | drh 2013-06-03 22:08:20.442 |
Context
2013-06-04
| ||
12:42 | Refactor the ORDER BY optimizer in the NGQP so that it is easier to maintain and so that it can support optimizing out GROUP BY and DISTINCT clauses. (check-in: e605c468e3 user: drh tags: nextgen-query-plan-exp) | |
2013-06-03
| ||
22:08 | Remove more vestiges of sqlite_query_plan from the test cases. (check-in: eb27086e8a user: drh tags: nextgen-query-plan-exp) | |
21:25 | Adjust the xBestIndex methods on both the fuzzer and transitive_closure virtual tables so that an unused MATCH operator gets a really large cost. Remove ambiguities from the fuzzer test cases. (check-in: e2c1af78b6 user: drh tags: nextgen-query-plan-exp) | |
Changes
Changes to test/between.test.
︙ | ︙ | |||
44 45 46 47 48 49 50 | CREATE INDEX i1zyx ON t1(z,y,x); COMMIT; } } {} # This procedure executes the SQL. Then it appends to the result the # "sort" or "nosort" keyword depending on whether or not any sorting | | | 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 | CREATE INDEX i1zyx ON t1(z,y,x); COMMIT; } } {} # This procedure executes the SQL. Then it appends to the result the # "sort" or "nosort" keyword depending on whether or not any sorting # is done. Then it appends the names of the table and index used. # proc queryplan {sql} { set ::sqlite_sort_count 0 set data [execsql $sql] if {$::sqlite_sort_count} {set x sort} {set x nosort} lappend data $x set eqp [execsql "EXPLAIN QUERY PLAN $sql"] |
︙ | ︙ |
Changes to test/intpkey.test.
︙ | ︙ | |||
121 122 123 124 125 126 127 | # do_test intpkey-1.12.1 { execsql { SELECT * FROM t1 WHERE a==4; } } {4 one two} do_test intpkey-1.12.2 { | | > > > | | 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 | # do_test intpkey-1.12.1 { execsql { SELECT * FROM t1 WHERE a==4; } } {4 one two} do_test intpkey-1.12.2 { execsql { EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a==4; } } {/SEARCH TABLE t1 /} # Try to insert a non-integer value into the primary key field. This # should result in a data type mismatch. # do_test intpkey-1.13.1 { set r [catch {execsql { INSERT INTO t1 VALUES('x','y','z'); |
︙ | ︙ |
Changes to test/like.test.
︙ | ︙ | |||
152 153 154 155 156 157 158 | ifcapable !like_opt { finish_test return } # This procedure executes the SQL. Then it appends to the result the # "sort" or "nosort" keyword (as in the cksort procedure above) then | | > > > > > > > > > > > > > | | | 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 | ifcapable !like_opt { finish_test return } # This procedure executes the SQL. Then it appends to the result the # "sort" or "nosort" keyword (as in the cksort procedure above) then # it appends the names of the table and index used. # proc queryplan {sql} { set ::sqlite_sort_count 0 set data [execsql $sql] if {$::sqlite_sort_count} {set x sort} {set x nosort} lappend data $x set eqp [execsql "EXPLAIN QUERY PLAN $sql"] # puts eqp=$eqp foreach {a b c x} $eqp { if {[regexp { TABLE (\w+ AS )?(\w+) USING COVERING INDEX (\w+)\W} \ $x all as tab idx]} { lappend data {} $idx } elseif {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\W} \ $x all as tab idx]} { lappend data $tab $idx } elseif {[regexp { TABLE (\w+ AS )?(\w+)\W} $x all as tab]} { lappend data $tab * } } return $data } # Perform tests on the like optimization. # # With no index on t1.x and with case sensitivity turned off, no optimization # is performed. # do_test like-3.1 { set sqlite_like_count 0 queryplan { SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1; } } {ABC {ABC abc xyz} abc abcd sort t1 *} do_test like-3.2 { set sqlite_like_count } {12} # With an index on t1.x and case sensitivity on, optimize completely. # do_test like-3.3 { |
︙ | ︙ | |||
265 266 267 268 269 270 271 272 | set sqlite_like_count } 12 # No optimization for case insensitive LIKE # do_test like-3.13 { set sqlite_like_count 0 queryplan { | > < | > > | | > < > < | 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 | set sqlite_like_count } 12 # No optimization for case insensitive LIKE # do_test like-3.13 { set sqlite_like_count 0 db eval {PRAGMA case_sensitive_like=off;} queryplan { SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1; } } {ABC {ABC abc xyz} abc abcd nosort {} i1} do_test like-3.14 { set sqlite_like_count } 12 # No optimization without an index. # do_test like-3.15 { set sqlite_like_count 0 db eval { PRAGMA case_sensitive_like=on; DROP INDEX i1; } queryplan { SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1; } } {abc abcd sort t1 *} do_test like-3.16 { set sqlite_like_count } 12 # No GLOB optimization without an index. # do_test like-3.17 { set sqlite_like_count 0 queryplan { SELECT x FROM t1 WHERE x GLOB 'abc*' ORDER BY 1; } } {abc abcd sort t1 *} do_test like-3.18 { set sqlite_like_count } 12 # GLOB is optimized regardless of the case_sensitive_like setting. # do_test like-3.19 { set sqlite_like_count 0 db eval {CREATE INDEX i1 ON t1(x);} queryplan { SELECT x FROM t1 WHERE x GLOB 'abc*' ORDER BY 1; } } {abc abcd nosort {} i1} do_test like-3.20 { set sqlite_like_count } 0 do_test like-3.21 { set sqlite_like_count 0 db eval {PRAGMA case_sensitive_like=on;} queryplan { SELECT x FROM t1 WHERE x GLOB 'abc*' ORDER BY 1; } } {abc abcd nosort {} i1} do_test like-3.22 { set sqlite_like_count } 0 do_test like-3.23 { set sqlite_like_count 0 db eval {PRAGMA case_sensitive_like=off;} queryplan { SELECT x FROM t1 WHERE x GLOB 'a[bc]d' ORDER BY 1; } } {abd acd nosort {} i1} do_test like-3.24 { set sqlite_like_count } 6 |
︙ | ︙ | |||
805 806 807 808 809 810 811 812 | INSERT INTO t11 VALUES(10, 'yz','yz'); INSERT INTO t11 VALUES(11, 'X','X'); INSERT INTO t11 VALUES(12, 'YZ','YZ'); SELECT count(*) FROM t11; } } {12} do_test like-11.1 { queryplan { | > < > < | > > > < | > > > < > < > < | > > | 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897 | INSERT INTO t11 VALUES(10, 'yz','yz'); INSERT INTO t11 VALUES(11, 'X','X'); INSERT INTO t11 VALUES(12, 'YZ','YZ'); SELECT count(*) FROM t11; } } {12} do_test like-11.1 { db eval {PRAGMA case_sensitive_like=OFF;} queryplan { SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY a; } } {abc abcd ABC ABCD nosort t11 *} do_test like-11.2 { db eval {PRAGMA case_sensitive_like=ON;} queryplan { SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY a; } } {abc abcd nosort t11 *} do_test like-11.3 { db eval { PRAGMA case_sensitive_like=OFF; CREATE INDEX t11b ON t11(b); } queryplan { SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY +a; } } {abc abcd ABC ABCD sort {} t11b} do_test like-11.4 { db eval {PRAGMA case_sensitive_like=ON;} queryplan { SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY a; } } {abc abcd nosort t11 *} do_test like-11.5 { db eval { PRAGMA case_sensitive_like=OFF; DROP INDEX t11b; CREATE INDEX t11bnc ON t11(b COLLATE nocase); } queryplan { SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY +a; } } {abc abcd ABC ABCD sort {} t11bnc} do_test like-11.6 { db eval {CREATE INDEX t11bb ON t11(b COLLATE binary);} queryplan { SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY +a; } } {abc abcd ABC ABCD sort {} t11bnc} do_test like-11.7 { db eval {PRAGMA case_sensitive_like=ON;} queryplan { SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY +a; } } {abc abcd sort {} t11bb} do_test like-11.8 { db eval {PRAGMA case_sensitive_like=OFF;} queryplan { SELECT b FROM t11 WHERE b GLOB 'abc*' ORDER BY +a; } } {abc abcd sort {} t11bb} do_test like-11.9 { db eval { CREATE INDEX t11cnc ON t11(c COLLATE nocase); CREATE INDEX t11cb ON t11(c COLLATE binary); } queryplan { SELECT c FROM t11 WHERE c LIKE 'abc%' ORDER BY +a; } } {abc abcd ABC ABCD sort {} t11cnc} do_test like-11.10 { queryplan { SELECT c FROM t11 WHERE c GLOB 'abc*' ORDER BY +a; } } {abc abcd sort {} t11cb} finish_test |
Changes to test/subquery.test.
︙ | ︙ | |||
237 238 239 240 241 242 243 | execsql { CREATE INDEX t4i ON t4(x); SELECT * FROM t4 WHERE x IN (SELECT a FROM t3); } } {10.0} do_test subquery-2.5.3.2 { # Verify that the t4i index was not used in the previous query | | > > > | | 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 | execsql { CREATE INDEX t4i ON t4(x); SELECT * FROM t4 WHERE x IN (SELECT a FROM t3); } } {10.0} do_test subquery-2.5.3.2 { # Verify that the t4i index was not used in the previous query execsql { EXPLAIN QUERY PLAN SELECT * FROM t4 WHERE x IN (SELECT a FROM t3); } } {/SCAN TABLE t4 /} do_test subquery-2.5.4 { execsql { DROP TABLE t3; DROP TABLE t4; } } {} |
︙ | ︙ |
Changes to test/where2.test.
︙ | ︙ | |||
62 63 64 65 66 67 68 | if {[db status sort]} {set x sort} {set x nosort} lappend data $x return $data } # This procedure executes the SQL. Then it appends to the result the # "sort" or "nosort" keyword (as in the cksort procedure above) then | | < | 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 | if {[db status sort]} {set x sort} {set x nosort} lappend data $x return $data } # This procedure executes the SQL. Then it appends to the result the # "sort" or "nosort" keyword (as in the cksort procedure above) then # it appends the name of the table and index used. # proc queryplan {sql} { set ::sqlite_sort_count 0 set data [execsql $sql] if {$::sqlite_sort_count} {set x sort} {set x nosort} lappend data $x set eqp [execsql "EXPLAIN QUERY PLAN $sql"] # puts eqp=$eqp foreach {a b c x} $eqp { if {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\W} \ $x all as tab idx]} { lappend data $tab $idx } elseif {[regexp { TABLE (\w+ AS )?(\w+)\W} $x all as tab]} { lappend data $tab * } } return $data } # Prefer a UNIQUE index over another index. # do_test where2-1.1 { queryplan { |
︙ | ︙ |
Changes to test/where3.test.
︙ | ︙ | |||
99 100 101 102 103 104 105 | FROM parent1 LEFT OUTER JOIN child1 ON parent1.child1key = child1.child1key INNER JOIN child2 ON child2.child2key = parent1.child2key; }] } # This procedure executes the SQL. Then it appends | | > > > > > > > > > > | | 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 | FROM parent1 LEFT OUTER JOIN child1 ON parent1.child1key = child1.child1key INNER JOIN child2 ON child2.child2key = parent1.child2key; }] } # This procedure executes the SQL. Then it appends # the names of the table and index used # proc queryplan {sql} { set ::sqlite_sort_count 0 set data [execsql $sql] set eqp [execsql "EXPLAIN QUERY PLAN $sql"] # puts eqp=$eqp foreach {a b c x} $eqp { if {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\W} \ $x all as tab idx]} { lappend data $tab $idx } elseif {[regexp { TABLE (\w+ AS )?(\w+)\W} $x all as tab]} { lappend data $tab * } } return $data } # If you have a from clause of the form: A B C left join D # then make sure the query optimizer is able to reorder the # A B C part anyway it wants. # |
︙ | ︙ | |||
140 141 142 143 144 145 146 | CREATE TABLE tC(cpk integer primary key, cx); CREATE TABLE tD(dpk integer primary key, dx); } queryplan { SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx WHERE cpk=bx AND bpk=ax } | | | | | | | | 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 | CREATE TABLE tC(cpk integer primary key, cx); CREATE TABLE tD(dpk integer primary key, dx); } queryplan { SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx WHERE cpk=bx AND bpk=ax } } {tA * tB * tC * tD *} do_test where3-2.1.1 { queryplan { SELECT * FROM tA, tB, tC LEFT JOIN tD ON cx=dpk WHERE cpk=bx AND bpk=ax } } {tA * tB * tC * tD *} do_test where3-2.1.2 { queryplan { SELECT * FROM tA, tB, tC LEFT JOIN tD ON cx=dpk WHERE bx=cpk AND bpk=ax } } {tA * tB * tC * tD *} do_test where3-2.1.3 { queryplan { SELECT * FROM tA, tB, tC LEFT JOIN tD ON cx=dpk WHERE bx=cpk AND ax=bpk } } {tA * tB * tC * tD *} do_test where3-2.1.4 { queryplan { SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx WHERE bx=cpk AND ax=bpk } } {tA * tB * tC * tD *} do_test where3-2.1.5 { queryplan { SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx WHERE cpk=bx AND ax=bpk } } {tA * tB * tC * tD *} do_test where3-2.2 { queryplan { SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx WHERE cpk=bx AND apk=bx } } {tB {} tA * tC * tD *} do_test where3-2.3 { |
︙ | ︙ |