Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add the "queryplanner" test permutation. Continuing refinements to NGQP. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | nextgen-query-plan-exp |
Files: | files | file ages | folders |
SHA1: |
25e2cde105a19293bdb9c001b48624e5 |
User & Date: | drh 2013-06-12 14:52:39.194 |
Context
2013-06-12
| ||
17:08 | "make test" now passing. (check-in: addd7f466d user: drh tags: nextgen-query-plan-exp) | |
14:52 | Add the "queryplanner" test permutation. Continuing refinements to NGQP. (check-in: 25e2cde105 user: drh tags: nextgen-query-plan-exp) | |
03:48 | Continue refining the NGQP (check-in: 40567fddd4 user: drh tags: nextgen-query-plan-exp) | |
Changes
Changes to src/where.c.
︙ | ︙ | |||
600 601 602 603 604 605 606 | whereSplit(pWC, pExpr->pRight, op); } } /* ** Initialize an expression mask set (a WhereMaskSet object) */ | | | 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 | whereSplit(pWC, pExpr->pRight, op); } } /* ** Initialize an expression mask set (a WhereMaskSet object) */ #define initMaskSet(P) (P)->n=0 /* ** Return the bitmask for the given cursor number. Return 0 if ** iCursor is not in the set. */ static Bitmask getMask(WhereMaskSet *pMaskSet, int iCursor){ int i; |
︙ | ︙ | |||
4357 4358 4359 4360 4361 4362 4363 | return 0; } /* ** Return a bitmask where 1s indicate that the corresponding column of ** the table is used by an index. Only the first 63 columns are considered. */ | | | 4357 4358 4359 4360 4361 4362 4363 4364 4365 4366 4367 4368 4369 4370 4371 | return 0; } /* ** Return a bitmask where 1s indicate that the corresponding column of ** the table is used by an index. Only the first 63 columns are considered. */ static Bitmask columnsInIndex(Index *pIdx){ Bitmask m = 0; int j; for(j=pIdx->nColumn-1; j>=0; j--){ int x = pIdx->aiColumn[j]; if( x<BMS-1 ) m |= MASKBIT(x); } return m; |
︙ | ︙ | |||
4427 4428 4429 4430 4431 4432 4433 | pProbe = &sPk; } rSize = whereCostFromInt(pSrc->pTab->nRowEst); rLogSize = estLog(rSize); /* Automatic indexes */ if( !pBuilder->pBest | < | 4427 4428 4429 4430 4431 4432 4433 4434 4435 4436 4437 4438 4439 4440 | pProbe = &sPk; } rSize = whereCostFromInt(pSrc->pTab->nRowEst); rLogSize = estLog(rSize); /* Automatic indexes */ if( !pBuilder->pBest && (pWInfo->pParse->db->flags & SQLITE_AutoIndex)!=0 && !pSrc->viaCoroutine && !pSrc->notIndexed && !pSrc->isCorrelated ){ /* Generate auto-index WhereLoops */ WhereClause *pWC = pBuilder->pWC; |
︙ | ︙ | |||
4472 4473 4474 4475 4476 4477 4478 | if( pProbe->tnum<=0 ){ /* Integer primary key index */ pNew->wsFlags = WHERE_IPK; /* Full table scan */ pNew->iSortIdx = b ? iSortIdx : 0; pNew->nOut = rSize; | | | | < | 4471 4472 4473 4474 4475 4476 4477 4478 4479 4480 4481 4482 4483 4484 4485 4486 4487 4488 4489 4490 4491 4492 4493 4494 4495 4496 4497 4498 4499 4500 4501 4502 | if( pProbe->tnum<=0 ){ /* Integer primary key index */ pNew->wsFlags = WHERE_IPK; /* Full table scan */ pNew->iSortIdx = b ? iSortIdx : 0; pNew->nOut = rSize; pNew->rRun = whereCostAdd(rSize,rLogSize) + 16 - b; rc = whereLoopInsert(pBuilder, pNew); if( rc ) break; }else{ Bitmask m = pSrc->colUsed & ~columnsInIndex(pProbe); pNew->wsFlags = (m==0) ? (WHERE_IDX_ONLY|WHERE_INDEXED) : WHERE_INDEXED; /* Full scan via index */ if( (m==0 || b) && pProbe->bUnordered==0 && (pWInfo->wctrlFlags & WHERE_ONEPASS_DESIRED)==0 && sqlite3GlobalConfig.bUseCis && OptimizationEnabled(pWInfo->pParse->db, SQLITE_CoverIdxScan) ){ pNew->iSortIdx = b ? iSortIdx : 0; pNew->nOut = rSize; pNew->rRun = whereCostAdd(rSize,rLogSize); pNew->rRun += ((m!=0) ? rLogSize : 10) - b; rc = whereLoopInsert(pBuilder, pNew); if( rc ) break; } } rc = whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, 0); /* If there was an INDEXED BY clause, then only that one index is |
︙ | ︙ | |||
4733 4734 4735 4736 4737 4738 4739 | if( sBest.maskSelf==0 ) break; assert( sBest.rSetup==0 ); rTotal = whereCostAdd(rTotal, sBest.rRun); nRow = whereCostAdd(nRow, sBest.nOut); prereq |= sBest.prereq; } assert( pNew->nLSlot>=1 ); | > | | | | | | | | | > | 4731 4732 4733 4734 4735 4736 4737 4738 4739 4740 4741 4742 4743 4744 4745 4746 4747 4748 4749 4750 4751 4752 4753 4754 4755 | if( sBest.maskSelf==0 ) break; assert( sBest.rSetup==0 ); rTotal = whereCostAdd(rTotal, sBest.rRun); nRow = whereCostAdd(nRow, sBest.nOut); prereq |= sBest.prereq; } assert( pNew->nLSlot>=1 ); if( sBest.maskSelf ){ pNew->nLTerm = 1; pNew->aLTerm[0] = pTerm; pNew->wsFlags = WHERE_MULTI_OR; pNew->rSetup = 0; pNew->rRun = rTotal; pNew->nOut = nRow; pNew->prereq = prereq; memset(&pNew->u, 0, sizeof(pNew->u)); rc = whereLoopInsert(pBuilder, pNew); } whereLoopClear(pWInfo->pParse->db, &sBest); } } return rc; } /* |
︙ | ︙ | |||
5323 5324 5325 5326 5327 5328 5329 | pTerm = findTerm(pWC, iCur, pIdx->aiColumn[j], 0, WO_EQ, pIdx); if( pTerm==0 ) break; whereLoopResize(pWInfo->pParse->db, pLoop, j); pLoop->aLTerm[j] = pTerm; } if( j!=pIdx->nColumn ) continue; pLoop->wsFlags = WHERE_COLUMN_EQ|WHERE_ONEROW|WHERE_INDEXED; | | | 5323 5324 5325 5326 5327 5328 5329 5330 5331 5332 5333 5334 5335 5336 5337 | pTerm = findTerm(pWC, iCur, pIdx->aiColumn[j], 0, WO_EQ, pIdx); if( pTerm==0 ) break; whereLoopResize(pWInfo->pParse->db, pLoop, j); pLoop->aLTerm[j] = pTerm; } if( j!=pIdx->nColumn ) continue; pLoop->wsFlags = WHERE_COLUMN_EQ|WHERE_ONEROW|WHERE_INDEXED; if( (pItem->colUsed & ~columnsInIndex(pIdx))==0 ){ pLoop->wsFlags |= WHERE_IDX_ONLY; } pLoop->nLTerm = j; pLoop->u.btree.nEq = j; pLoop->u.btree.pIndex = pIdx; pLoop->rRun = 39; /* 39 == whereCostFromInt(15) */ break; |
︙ | ︙ |
Changes to test/permutations.test.
︙ | ︙ | |||
208 209 210 211 212 213 214 215 216 217 218 219 220 221 | sqlite3_shutdown install_malloc_faultsim 0 sqlite3_initialize autoinstall_test_functions } -shutdown { unset -nocomplain ::G(valgrind) } lappend ::testsuitelist xxx #------------------------------------------------------------------------- # Define the coverage related test suites: # # coverage-wal # | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 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 | sqlite3_shutdown install_malloc_faultsim 0 sqlite3_initialize autoinstall_test_functions } -shutdown { unset -nocomplain ::G(valgrind) } test_suite "queryplanner" -prefix "" -description { Tests of the query planner and query optimizer } -files { alter2.test alter3.test alter4.test alter.test analyze3.test analyze4.test analyze5.test analyze6.test analyze7.test analyze8.test analyze.test attach2.test attach3.test attach4.test attachmalloc.test attach.test autoinc.test autoindex1.test between.test cast.test check.test closure01.test coalesce.test collate1.test collate2.test collate3.test collate4.test collate5.test collate6.test collate7.test collate8.test collate9.test collateA.test colmeta.test colname.test conflict.test count.test coveridxscan.test createtab.test cse.test date.test dbstatus2.test dbstatus.test default.test delete2.test delete3.test delete.test descidx1.test descidx2.test descidx3.test distinctagg.test distinct.test e_createtable.test e_delete.test e_droptrigger.test e_dropview.test e_expr.test e_fkey.test e_insert.test eqp.test e_reindex.test e_resolve.test e_select2.test e_select.test e_update.test exists.test expr.test fkey1.test fkey2.test fkey3.test fkey4.test fkey5.test fkey_malloc.test format4.test func2.test func3.test func.test fuzz2.test fuzz3.test fuzzer1.test fuzz-oss1.test fuzz.test in2.test in3.test in4.test in5.test index2.test index3.test index4.test index5.test indexedby.test indexfault.test index.test insert2.test insert3.test insert4.test insert5.test insert.test instr.test in.test intpkey.test join2.test join3.test join4.test join5.test join6.test join.test like2.test like.test limit.test minmax2.test minmax3.test minmax4.test minmax.test misc1.test misc2.test misc3.test misc4.test misc5.test misc6.test misc7.test orderby1.test orderby2.test orderby3.test orderby4.test randexpr1.test regexp1.test reindex.test rowhash.test rowid.test schema2.test schema3.test schema4.test schema5.test schema.test securedel2.test securedel.test select1.test select2.test select3.test select4.test select5.test select6.test select7.test select8.test select9.test selectA.test selectB.test selectC.test selectD.test selectE.test sidedelete.test sort.test spellfix.test subquery2.test subquery.test subselect.test substr.test tkt-02a8e81d44.test tkt1435.test tkt1443.test tkt1444.test tkt1449.test tkt1473.test tkt1501.test tkt1512.test tkt1514.test tkt1536.test tkt1537.test tkt1567.test tkt1644.test tkt1667.test tkt1873.test tkt2141.test tkt2192.test tkt2213.test tkt2251.test tkt2285.test tkt2332.test tkt2339.test tkt2391.test tkt2409.test tkt2450.test tkt2565.test tkt2640.test tkt2643.test tkt2686.test tkt-26ff0c2d1e.test tkt2767.test tkt2817.test tkt2820.test tkt2822.test tkt2832.test tkt2854.test tkt2920.test tkt2927.test tkt2942.test tkt-2a5629202f.test tkt-2d1a5c67d.test tkt-2ea2425d34.test tkt3080.test tkt3093.test tkt3121.test tkt-31338dca7e.test tkt-313723c356.test tkt3201.test tkt3292.test tkt3298.test tkt3334.test tkt3346.test tkt3357.test tkt3419.test tkt3424.test tkt3442.test tkt3457.test tkt3461.test tkt3493.test tkt3508.test tkt3522.test tkt3527.test tkt3541.test tkt3554.test tkt3581.test tkt35xx.test tkt3630.test tkt3718.test tkt3731.test tkt3757.test tkt3761.test tkt3762.test tkt3773.test tkt3791.test tkt3793.test tkt3810.test tkt3824.test tkt3832.test tkt3838.test tkt3841.test tkt-385a5b56b9.test tkt3871.test tkt3879.test tkt-38cb5df375.test tkt3911.test tkt3918.test tkt3922.test tkt3929.test tkt3935.test tkt3992.test tkt3997.test tkt-3998683a16.test tkt-3a77c9714e.test tkt-3fe897352e.test tkt4018.test tkt-4a03edc4c8.test tkt-4dd95f6943.test tkt-54844eea3f.test tkt-5d863f876e.test tkt-5e10420e8d.test tkt-5ee23731f.test tkt-6bfb98dfc0.test tkt-752e1646fc.test tkt-78e04e52ea.test tkt-7a31705a7e6.test tkt-7bbfb7d442.test tkt-80ba201079.test tkt-80e031a00f.test tkt-8454a207b9.test tkt-91e2e8ba6f.test tkt-94c04eaadb.test tkt-9d68c883.test tkt-a7b7803e.test tkt-b1d3a2e531.test tkt-b351d95f9.test tkt-b72787b1.test tkt-bd484a090c.test tkt-bdc6bbbb38.test tkt-c48d99d690.test tkt-cbd054fa6b.test tkt-d11f09d36e.test tkt-d635236375.test tkt-d82e3f3721.test tkt-f3e5abed55.test tkt-f777251dc7a.test tkt-f7b4edec.test tkt-f973c7ac31.test tkt-fa7bf5ec.test tkt-fc62af4523.test tkt-fc7bd6358f.test trigger1.test trigger2.test trigger3.test trigger4.test trigger5.test trigger6.test trigger7.test trigger8.test trigger9.test triggerA.test triggerB.test triggerC.test triggerD.test types2.test types3.test types.test unique.test unordered.test update.test view.test vtab1.test vtab2.test vtab3.test vtab4.test vtab5.test vtab6.test vtab7.test vtab8.test vtab9.test vtab_alter.test vtabA.test vtabB.test vtabC.test vtabD.test vtab_err.test vtabE.test vtabF.test where2.test where3.test where4.test where5.test where6.test where7.test where8m.test where8.test where9.test whereA.test whereB.test whereC.test whereD.test whereE.test whereF.test wherelimit.test where.test } lappend ::testsuitelist xxx #------------------------------------------------------------------------- # Define the coverage related test suites: # # coverage-wal # |
︙ | ︙ |
Changes to test/where8.test.
︙ | ︙ | |||
264 265 266 267 268 269 270 | SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND a<5 ORDER BY a } } {1 1 2 2 3 3 4 2 4 4 0 0} do_test where8-3.12 { execsql_status { SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND +a<5 ORDER BY a } | | | 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 | SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND a<5 ORDER BY a } } {1 1 2 2 3 3 4 2 4 4 0 0} do_test where8-3.12 { execsql_status { SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND +a<5 ORDER BY a } } {1 1 2 2 3 3 4 2 4 4 9 0} do_test where8-3.13 { execsql_status { SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND +a<5 } } {1 1 2 2 3 3 4 2 4 4 9 0} do_test where8-3.14 { |
︙ | ︙ |
Changes to test/where9.test.
︙ | ︙ | |||
416 417 418 419 420 421 422 | do_test where9-4.5 { catchsql { SELECT a FROM t1 INDEXED BY t1b WHERE +b>1000 AND (c=31031 OR d IS NULL) ORDER BY +a } | | | | | 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 | do_test where9-4.5 { catchsql { SELECT a FROM t1 INDEXED BY t1b WHERE +b>1000 AND (c=31031 OR d IS NULL) ORDER BY +a } } {1 {no query solution}} do_test where9-4.6 { count_steps { SELECT a FROM t1 NOT INDEXED WHERE b>1000 AND (c=31031 OR d IS NULL) ORDER BY +a } } {92 93 97 scan 98 sort 1} do_test where9-4.7 { catchsql { SELECT a FROM t1 INDEXED BY t1c WHERE b>1000 AND (c=31031 OR d IS NULL) ORDER BY +a } } {1 {no query solution}} do_test where9-4.8 { catchsql { SELECT a FROM t1 INDEXED BY t1d WHERE b>1000 AND (c=31031 OR d IS NULL) ORDER BY +a } } {1 {no query solution}} ifcapable explain { # The (c=31031 OR d IS NULL) clause is preferred over b>1000 because # the former is an equality test which is expected to return fewer rows. # do_execsql_test where9-5.1 { EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c=31031 OR d IS NULL) |
︙ | ︙ | |||
594 595 596 597 598 599 600 | count_steps { BEGIN; DELETE FROM t1 WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL) OR (b NOT NULL AND c IS NULL AND d NOT NULL) OR (b NOT NULL AND c NOT NULL AND d IS NULL) } | | | | 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 | count_steps { BEGIN; DELETE FROM t1 WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL) OR (b NOT NULL AND c IS NULL AND d NOT NULL) OR (b NOT NULL AND c NOT NULL AND d IS NULL) } } {scan 0 sort 0} ;# DELETEs rows 90 91 92 97 do_test where9-6.3.6 { db eval { SELECT count(*) FROM t1 UNION ALL SELECT a FROM t1 WHERE a BETWEEN 85 AND 100; ROLLBACK; } } {95 85 86 87 88 89 93 94 95 96 98 99} do_test where9-6.3.7 { count_steps { BEGIN; UPDATE t1 SET a=a+100 WHERE (b IS NULL AND c NOT NULL AND d NOT NULL) OR (b NOT NULL AND +c IS NULL AND d NOT NULL) OR (b NOT NULL AND c NOT NULL AND d IS NULL) } } {scan 0 sort 0} ;# Add 100 to rowids 90 91 92 97 do_test where9-6.3.8 { db eval { SELECT count(*) FROM t1 UNION ALL SELECT a FROM t1 WHERE a BETWEEN 85 AND 100; ROLLBACK; } } {99 85 86 87 88 89 93 94 95 96 98 99} |
︙ | ︙ | |||
701 702 703 704 705 706 707 | count_steps { BEGIN; DELETE FROM t1 WHERE (b IS NULL AND c NOT NULL AND d NOT NULL) OR (b NOT NULL AND +c IS NULL AND d NOT NULL) OR (b NOT NULL AND c NOT NULL AND d IS NULL) } | | | | 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 | count_steps { BEGIN; DELETE FROM t1 WHERE (b IS NULL AND c NOT NULL AND d NOT NULL) OR (b NOT NULL AND +c IS NULL AND d NOT NULL) OR (b NOT NULL AND c NOT NULL AND d IS NULL) } } {scan 0 sort 0} ;# DELETEs rows 90 91 92 97 do_test where9-6.6.2 { db eval { SELECT count(*) FROM t1 UNION ALL SELECT a FROM t1 WHERE a BETWEEN 85 AND 100; ROLLBACK; } } {95 85 86 87 88 89 93 94 95 96 98 99} do_test where9-6.6.3 { count_steps { BEGIN; UPDATE t1 SET a=a+100 WHERE (b IS NULL AND c NOT NULL AND d NOT NULL) OR (b NOT NULL AND +c IS NULL AND d NOT NULL) OR (b NOT NULL AND c NOT NULL AND d IS NULL) } } {scan 0 sort 0} ;# Add 100 to rowids 90 91 92 97 do_test where9-6.6.4 { db eval { SELECT count(*) FROM t1 UNION ALL SELECT a FROM t1 WHERE a BETWEEN 85 AND 200; ROLLBACK; } } {99 85 86 87 88 89 93 94 95 96 98 99 190 191 192 197} |
︙ | ︙ | |||
764 765 766 767 768 769 770 | ROLLBACK; } } {99 85 86 87 88 89 93 94 95 96 98 99 190 191 192 197} do_test where9-6.8.1 { catchsql { DELETE FROM t1 INDEXED BY t1b | | > > > > > > > > | | | > > > > > > > > | 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 | ROLLBACK; } } {99 85 86 87 88 89 93 94 95 96 98 99 190 191 192 197} do_test where9-6.8.1 { catchsql { DELETE FROM t1 INDEXED BY t1b WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL) OR (b NOT NULL AND c IS NULL AND d NOT NULL) OR (b NOT NULL AND c NOT NULL AND d IS NULL) } } {1 {no query solution}} do_test where9-6.8.2 { catchsql { UPDATE t1 INDEXED BY t1b SET a=a+100 WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL) OR (b NOT NULL AND c IS NULL AND d NOT NULL) OR (b NOT NULL AND c NOT NULL AND d IS NULL) } } {1 {no query solution}} do_test where9-6.8.3 { catchsql { UPDATE t1 INDEXED BY t1b SET a=a+100 WHERE (b IS NULL AND c NOT NULL AND d NOT NULL) OR (b NOT NULL AND c IS NULL AND d NOT NULL) OR (b NOT NULL AND c NOT NULL AND d IS NULL) } } {0 {}} do_test where9-6.8.4 { catchsql { DELETE FROM t1 INDEXED BY t1b WHERE (b IS NULL AND c NOT NULL AND d NOT NULL) OR (b NOT NULL AND c IS NULL AND d NOT NULL) OR (b NOT NULL AND c NOT NULL AND d IS NULL) } } {0 {}} ############################################################################ # Test cases where terms inside an OR series are combined with AND terms # external to the OR clause. In other words, cases where # # x AND (y OR z) # |
︙ | ︙ |
Changes to test/whereF.test.
︙ | ︙ | |||
42 43 44 45 46 47 48 | # # In order to make them more predictable, automatic indexes are turned off for # the tests in this file. # set testdir [file dirname $argv0] source $testdir/tester.tcl | | | | | 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 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 94 | # # In order to make them more predictable, automatic indexes are turned off for # the tests in this file. # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix whereF do_execsql_test 1.0 { PRAGMA automatic_index = 0; CREATE TABLE t1(a, b, c); CREATE TABLE t2(d, e, f); CREATE UNIQUE INDEX i1 ON t1(a); CREATE UNIQUE INDEX i2 ON t2(d); } {} foreach {tn sql} { 1 "SELECT * FROM t1, t2 WHERE t1.a=t2.e AND t2.d<t1.b AND t1.c!=10" 2 "SELECT * FROM t2, t1 WHERE t1.a=t2.e AND t2.d<t1.b AND t1.c!=10" 3 "SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a=t2.e AND t2.d<t1.b AND t1.c!=10" } { do_test 1.$tn { db eval "EXPLAIN QUERY PLAN $sql" } {/.*SCAN TABLE t2\y.*SEARCH TABLE t1\y.*/} } do_execsql_test 2.0 { DROP TABLE t1; DROP TABLE t2; CREATE TABLE t1(a, b, c); CREATE TABLE t2(d, e, f); CREATE UNIQUE INDEX i1 ON t1(a); CREATE UNIQUE INDEX i2 ON t1(b); CREATE UNIQUE INDEX i3 ON t2(d); } {} foreach {tn sql} { 1 "SELECT * FROM t1, t2 WHERE t1.a>? AND t2.d>t1.c AND t1.b=t2.e" 2 "SELECT * FROM t2, t1 WHERE t1.a>? AND t2.d>t1.c AND t1.b=t2.e" 3 "SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a>? AND t2.d>t1.c AND t1.b=t2.e" } { do_test 2.$tn { db eval "EXPLAIN QUERY PLAN $sql" } {/.*SCAN TABLE t2\y.*SEARCH TABLE t1\y.*/} } do_execsql_test 3.0 { DROP TABLE t1; DROP TABLE t2; CREATE TABLE t1(a, b, c); CREATE TABLE t2(d, e, f); |
︙ | ︙ | |||
105 106 107 108 109 110 111 | WHERE t2.d=t1.b AND t1.a=(t2.d+1) AND t1.b = (t2.e+1)} 3 {SELECT t1.a, t1.b, t2.d, t2.e FROM t2 CROSS JOIN t1 WHERE t2.d=t1.b AND t1.a=(t2.d+1) AND t1.b = (t2.e+1)} } { do_test 3.$tn { db eval "EXPLAIN QUERY PLAN $sql" | | | 105 106 107 108 109 110 111 112 113 114 115 | WHERE t2.d=t1.b AND t1.a=(t2.d+1) AND t1.b = (t2.e+1)} 3 {SELECT t1.a, t1.b, t2.d, t2.e FROM t2 CROSS JOIN t1 WHERE t2.d=t1.b AND t1.a=(t2.d+1) AND t1.b = (t2.e+1)} } { do_test 3.$tn { db eval "EXPLAIN QUERY PLAN $sql" } {/.*SCAN TABLE t2\y.*SEARCH TABLE t1\y.*/} } finish_test |