Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add LEFT JOIN test cases for multi-index OR in where9.test. (CVS 6085) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
96f3b62914adde34079f08428b4e2fe8 |
User & Date: | drh 2008-12-30 16:35:53.000 |
Context
2008-12-30
| ||
17:55 | Fix a bug in the multi-index OR cost estimator. Remove leftover "breakpoint" commands from test scripts. (CVS 6086) (check-in: b090d5736d user: drh tags: trunk) | |
16:35 | Add LEFT JOIN test cases for multi-index OR in where9.test. (CVS 6085) (check-in: 96f3b62914 user: drh tags: trunk) | |
16:18 | Get EXPLAIN QUERY PLAN working with the multi-index OR optimization. Added new test script "where9.test". (CVS 6084) (check-in: 4b64602210 user: drh tags: trunk) | |
Changes
Changes to test/where9.test.
1 2 3 4 5 6 7 8 9 10 11 12 13 | # 2008 December 30 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the multi-index OR clause optimizer. # | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | # 2008 December 30 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the multi-index OR clause optimizer. # # $Id: where9.test,v 1.2 2008/12/30 16:35:53 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable !or_opt { finish_test return |
︙ | ︙ | |||
144 145 146 147 148 149 150 151 152 153 154 155 156 157 | INSERT INTO t2 SELECT * FROM t1; CREATE INDEX t2b ON t2(b,c); CREATE INDEX t2c ON t2(c,e); CREATE INDEX t2d ON t2(d,g); CREATE INDEX t2e ON t2(e,f,g); CREATE INDEX t2f ON t2(f,b,d,c); CREATE INDEX t2g ON t2(g,f); } } {} do_test where9-1.2.1 { count_steps { SELECT a FROM t1 WHERE b IS NULL | > > > | 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 | INSERT INTO t2 SELECT * FROM t1; CREATE INDEX t2b ON t2(b,c); CREATE INDEX t2c ON t2(c,e); CREATE INDEX t2d ON t2(d,g); CREATE INDEX t2e ON t2(e,f,g); CREATE INDEX t2f ON t2(f,b,d,c); CREATE INDEX t2g ON t2(g,f); CREATE TABLE t3(x,y); INSERT INTO t3 VALUES(1,80); INSERT INTO t3 VALUES(2,80); } } {} do_test where9-1.2.1 { count_steps { SELECT a FROM t1 WHERE b IS NULL |
︙ | ︙ | |||
266 267 268 269 270 271 272 273 274 275 276 277 278 279 | count_steps { SELECT coalesce(t2.a,9999) FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f WHERE t1.a=80 ORDER BY 1 } } {9999 scan 0 sort 1} ifcapable explain { do_test where9-3.1 { set r [db eval { EXPLAIN QUERY PLAN SELECT t2.a FROM t1, t2 | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 269 270 271 272 273 274 275 276 277 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 | count_steps { SELECT coalesce(t2.a,9999) FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f WHERE t1.a=80 ORDER BY 1 } } {9999 scan 0 sort 1} do_test where9-2.5 { count_steps { SELECT t1.a, coalesce(t2.a,9999) FROM t1 LEFT JOIN t2 ON (t1.c=t2.c AND t1.d=t2.d) OR (t1.f)=t2.f WHERE t1.a=80 OR t1.b=880 OR (t1.c=27027 AND round(t1.d)==80) ORDER BY 1 } } {80 2 80 28 80 54 80 80 scan 0 sort 0} do_test where9-2.6 { count_steps { SELECT t1.a, coalesce(t2.a,9999) FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f WHERE t1.a=80 OR t1.b=880 OR (t1.c=27027 AND round(t1.d)==80) ORDER BY 1 } } {80 9999 scan 0 sort 0} do_test where9-2.7 { count_steps { SELECT t3.x, t1.a, coalesce(t2.a,9999) FROM t3 JOIN t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f WHERE t1.a=t3.y OR t1.b=t3.y*11 OR (t1.c=27027 AND round(t1.d)==80) ORDER BY 1, 2 } } {1 80 9999 2 80 9999 scan 1 sort 1} do_test where9-2.8 { count_steps { SELECT t3.x, t1.a, coalesce(t2.a,9999) FROM t3 JOIN t1 LEFT JOIN t2 ON (t1.c=t2.c AND t1.d=t2.d) OR (t1.f)=t2.f WHERE t1.a=t3.y OR t1.b=t3.y*11 OR (t1.c=27027 AND round(t1.d)==80) ORDER BY 1, 2 } } {1 80 2 1 80 28 1 80 54 1 80 80 2 80 2 2 80 28 2 80 54 2 80 80 scan 1 sort 1} ifcapable explain { do_test where9-3.1 { set r [db eval { EXPLAIN QUERY PLAN SELECT t2.a FROM t1, t2 |
︙ | ︙ |