/ Check-in [25e2cde1]
Login

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 | SQL archive
Timelines: family | ancestors | descendants | both | nextgen-query-plan-exp
Files: files | file ages | folders
SHA1:25e2cde105a19293bdb9c001b48624e5d7f8c4e5
User & Date: drh 2013-06-12 14:52:39
Context
2013-06-12
17:08
"make test" now passing. check-in: addd7f46 user: drh tags: nextgen-query-plan-exp
14:52
Add the "queryplanner" test permutation. Continuing refinements to NGQP. check-in: 25e2cde1 user: drh tags: nextgen-query-plan-exp
03:48
Continue refining the NGQP check-in: 40567fdd user: drh tags: nextgen-query-plan-exp
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

   600    600       whereSplit(pWC, pExpr->pRight, op);
   601    601     }
   602    602   }
   603    603   
   604    604   /*
   605    605   ** Initialize an expression mask set (a WhereMaskSet object)
   606    606   */
   607         -#define initMaskSet(P)  memset(P, 0, sizeof(*P))
          607  +#define initMaskSet(P)  (P)->n=0
   608    608   
   609    609   /*
   610    610   ** Return the bitmask for the given cursor number.  Return 0 if
   611    611   ** iCursor is not in the set.
   612    612   */
   613    613   static Bitmask getMask(WhereMaskSet *pMaskSet, int iCursor){
   614    614     int i;
................................................................................
  4357   4357     return 0;
  4358   4358   }
  4359   4359   
  4360   4360   /*
  4361   4361   ** Return a bitmask where 1s indicate that the corresponding column of
  4362   4362   ** the table is used by an index.  Only the first 63 columns are considered.
  4363   4363   */
  4364         -static Bitmask columnsUsedByIndex(Index *pIdx){
         4364  +static Bitmask columnsInIndex(Index *pIdx){
  4365   4365     Bitmask m = 0;
  4366   4366     int j;
  4367   4367     for(j=pIdx->nColumn-1; j>=0; j--){
  4368   4368       int x = pIdx->aiColumn[j];
  4369   4369       if( x<BMS-1 ) m |= MASKBIT(x);
  4370   4370     }
  4371   4371     return m;
................................................................................
  4427   4427       pProbe = &sPk;
  4428   4428     }
  4429   4429     rSize = whereCostFromInt(pSrc->pTab->nRowEst);
  4430   4430     rLogSize = estLog(rSize);
  4431   4431   
  4432   4432     /* Automatic indexes */
  4433   4433     if( !pBuilder->pBest
  4434         -//   && pTabList->nSrc>1
  4435   4434      && (pWInfo->pParse->db->flags & SQLITE_AutoIndex)!=0 
  4436   4435      && !pSrc->viaCoroutine
  4437   4436      && !pSrc->notIndexed
  4438   4437      && !pSrc->isCorrelated
  4439   4438     ){
  4440   4439       /* Generate auto-index WhereLoops */
  4441   4440       WhereClause *pWC = pBuilder->pWC;
................................................................................
  4472   4471       if( pProbe->tnum<=0 ){
  4473   4472         /* Integer primary key index */
  4474   4473         pNew->wsFlags = WHERE_IPK;
  4475   4474   
  4476   4475         /* Full table scan */
  4477   4476         pNew->iSortIdx = b ? iSortIdx : 0;
  4478   4477         pNew->nOut = rSize;
  4479         -      pNew->rRun = whereCostAdd(rSize,rLogSize) + 16 + b*4;
         4478  +      pNew->rRun = whereCostAdd(rSize,rLogSize) + 16 - b;
  4480   4479         rc = whereLoopInsert(pBuilder, pNew);
  4481   4480         if( rc ) break;
  4482   4481       }else{
  4483         -      Bitmask m = pSrc->colUsed & ~columnsUsedByIndex(pProbe);
         4482  +      Bitmask m = pSrc->colUsed & ~columnsInIndex(pProbe);
  4484   4483         pNew->wsFlags = (m==0) ? (WHERE_IDX_ONLY|WHERE_INDEXED) : WHERE_INDEXED;
  4485   4484   
  4486   4485         /* Full scan via index */
  4487   4486         if( (m==0 || b)
  4488   4487          && pProbe->bUnordered==0
  4489   4488          && (pWInfo->wctrlFlags & WHERE_ONEPASS_DESIRED)==0
  4490   4489          && sqlite3GlobalConfig.bUseCis
  4491   4490          && OptimizationEnabled(pWInfo->pParse->db, SQLITE_CoverIdxScan)
  4492   4491         ){
  4493   4492           pNew->iSortIdx = b ? iSortIdx : 0;
  4494   4493           pNew->nOut = rSize;
  4495   4494           pNew->rRun = whereCostAdd(rSize,rLogSize);
  4496         -        if( m!=0 ) pNew->rRun += rLogSize;
  4497         -        if( b ) pNew->rRun--;
         4495  +        pNew->rRun += ((m!=0) ? rLogSize : 10) - b;
  4498   4496           rc = whereLoopInsert(pBuilder, pNew);
  4499   4497           if( rc ) break;
  4500   4498         }
  4501   4499       }
  4502   4500       rc = whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, 0);
  4503   4501   
  4504   4502       /* If there was an INDEXED BY clause, then only that one index is
................................................................................
  4733   4731           if( sBest.maskSelf==0 ) break;
  4734   4732           assert( sBest.rSetup==0 );
  4735   4733           rTotal = whereCostAdd(rTotal, sBest.rRun);
  4736   4734           nRow = whereCostAdd(nRow, sBest.nOut);
  4737   4735           prereq |= sBest.prereq;
  4738   4736         }
  4739   4737         assert( pNew->nLSlot>=1 );
  4740         -      pNew->nLTerm = 1;
  4741         -      pNew->aLTerm[0] = pTerm;
  4742         -      pNew->wsFlags = WHERE_MULTI_OR;
  4743         -      pNew->rSetup = 0;
  4744         -      pNew->rRun = rTotal;
  4745         -      pNew->nOut = nRow;
  4746         -      pNew->prereq = prereq;
  4747         -      memset(&pNew->u, 0, sizeof(pNew->u));
  4748         -      rc = whereLoopInsert(pBuilder, pNew);
         4738  +      if( sBest.maskSelf ){
         4739  +        pNew->nLTerm = 1;
         4740  +        pNew->aLTerm[0] = pTerm;
         4741  +        pNew->wsFlags = WHERE_MULTI_OR;
         4742  +        pNew->rSetup = 0;
         4743  +        pNew->rRun = rTotal;
         4744  +        pNew->nOut = nRow;
         4745  +        pNew->prereq = prereq;
         4746  +        memset(&pNew->u, 0, sizeof(pNew->u));
         4747  +        rc = whereLoopInsert(pBuilder, pNew);
         4748  +      }
  4749   4749         whereLoopClear(pWInfo->pParse->db, &sBest);
  4750   4750       }
  4751   4751     }
  4752   4752     return rc;
  4753   4753   }
  4754   4754   
  4755   4755   /*
................................................................................
  5323   5323           pTerm = findTerm(pWC, iCur, pIdx->aiColumn[j], 0, WO_EQ, pIdx);
  5324   5324           if( pTerm==0 ) break;
  5325   5325           whereLoopResize(pWInfo->pParse->db, pLoop, j);
  5326   5326           pLoop->aLTerm[j] = pTerm;
  5327   5327         }
  5328   5328         if( j!=pIdx->nColumn ) continue;
  5329   5329         pLoop->wsFlags = WHERE_COLUMN_EQ|WHERE_ONEROW|WHERE_INDEXED;
  5330         -      if( (pItem->colUsed & ~columnsUsedByIndex(pIdx))==0 ){
         5330  +      if( (pItem->colUsed & ~columnsInIndex(pIdx))==0 ){
  5331   5331           pLoop->wsFlags |= WHERE_IDX_ONLY;
  5332   5332         }
  5333   5333         pLoop->nLTerm = j;
  5334   5334         pLoop->u.btree.nEq = j;
  5335   5335         pLoop->u.btree.pIndex = pIdx;
  5336   5336         pLoop->rRun = 39;  /* 39 == whereCostFromInt(15) */
  5337   5337         break;

Changes to test/permutations.test.

   208    208     sqlite3_shutdown
   209    209     install_malloc_faultsim 0
   210    210     sqlite3_initialize
   211    211     autoinstall_test_functions
   212    212   } -shutdown {
   213    213     unset -nocomplain ::G(valgrind)
   214    214   }
          215  +
          216  +test_suite "queryplanner" -prefix "" -description {
          217  +  Tests of the query planner and query optimizer
          218  +} -files {
          219  +  alter2.test alter3.test alter4.test alter.test analyze3.test
          220  +  analyze4.test analyze5.test analyze6.test analyze7.test analyze8.test
          221  +  analyze.test attach2.test attach3.test attach4.test attachmalloc.test
          222  +  attach.test autoinc.test autoindex1.test between.test cast.test
          223  +  check.test closure01.test coalesce.test collate1.test collate2.test
          224  +  collate3.test collate4.test collate5.test collate6.test collate7.test
          225  +  collate8.test collate9.test collateA.test colmeta.test colname.test
          226  +  conflict.test count.test coveridxscan.test createtab.test cse.test
          227  +  date.test dbstatus2.test dbstatus.test default.test delete2.test
          228  +  delete3.test delete.test descidx1.test descidx2.test descidx3.test
          229  +  distinctagg.test distinct.test e_createtable.test e_delete.test
          230  +  e_droptrigger.test e_dropview.test e_expr.test e_fkey.test e_insert.test
          231  +  eqp.test e_reindex.test e_resolve.test e_select2.test e_select.test
          232  +  e_update.test exists.test expr.test fkey1.test fkey2.test fkey3.test
          233  +  fkey4.test fkey5.test fkey_malloc.test format4.test func2.test
          234  +  func3.test func.test fuzz2.test fuzz3.test fuzzer1.test fuzz-oss1.test
          235  +  fuzz.test in2.test in3.test in4.test in5.test index2.test index3.test
          236  +  index4.test index5.test indexedby.test indexfault.test index.test
          237  +  insert2.test insert3.test insert4.test insert5.test insert.test
          238  +  instr.test in.test intpkey.test join2.test join3.test join4.test
          239  +  join5.test join6.test join.test like2.test like.test limit.test
          240  +  minmax2.test minmax3.test minmax4.test minmax.test misc1.test misc2.test
          241  +  misc3.test misc4.test misc5.test misc6.test misc7.test orderby1.test
          242  +  orderby2.test orderby3.test orderby4.test randexpr1.test regexp1.test
          243  +  reindex.test rowhash.test rowid.test schema2.test schema3.test
          244  +  schema4.test schema5.test schema.test securedel2.test securedel.test
          245  +  select1.test select2.test select3.test select4.test select5.test
          246  +  select6.test select7.test select8.test select9.test selectA.test
          247  +  selectB.test selectC.test selectD.test selectE.test sidedelete.test
          248  +  sort.test spellfix.test subquery2.test subquery.test subselect.test
          249  +  substr.test tkt-02a8e81d44.test tkt1435.test tkt1443.test tkt1444.test
          250  +  tkt1449.test tkt1473.test tkt1501.test tkt1512.test tkt1514.test
          251  +  tkt1536.test tkt1537.test tkt1567.test tkt1644.test tkt1667.test
          252  +  tkt1873.test tkt2141.test tkt2192.test tkt2213.test tkt2251.test
          253  +  tkt2285.test tkt2332.test tkt2339.test tkt2391.test tkt2409.test
          254  +  tkt2450.test tkt2565.test tkt2640.test tkt2643.test tkt2686.test
          255  +  tkt-26ff0c2d1e.test tkt2767.test tkt2817.test tkt2820.test tkt2822.test
          256  +  tkt2832.test tkt2854.test tkt2920.test tkt2927.test tkt2942.test
          257  +  tkt-2a5629202f.test tkt-2d1a5c67d.test tkt-2ea2425d34.test tkt3080.test
          258  +  tkt3093.test tkt3121.test tkt-31338dca7e.test tkt-313723c356.test
          259  +  tkt3201.test tkt3292.test tkt3298.test tkt3334.test tkt3346.test
          260  +  tkt3357.test tkt3419.test tkt3424.test tkt3442.test tkt3457.test
          261  +  tkt3461.test tkt3493.test tkt3508.test tkt3522.test tkt3527.test
          262  +  tkt3541.test tkt3554.test tkt3581.test tkt35xx.test tkt3630.test
          263  +  tkt3718.test tkt3731.test tkt3757.test tkt3761.test tkt3762.test
          264  +  tkt3773.test tkt3791.test tkt3793.test tkt3810.test tkt3824.test
          265  +  tkt3832.test tkt3838.test tkt3841.test tkt-385a5b56b9.test tkt3871.test
          266  +  tkt3879.test tkt-38cb5df375.test tkt3911.test tkt3918.test tkt3922.test
          267  +  tkt3929.test tkt3935.test tkt3992.test tkt3997.test tkt-3998683a16.test
          268  +  tkt-3a77c9714e.test tkt-3fe897352e.test tkt4018.test tkt-4a03edc4c8.test
          269  +  tkt-4dd95f6943.test tkt-54844eea3f.test tkt-5d863f876e.test
          270  +  tkt-5e10420e8d.test tkt-5ee23731f.test tkt-6bfb98dfc0.test
          271  +  tkt-752e1646fc.test tkt-78e04e52ea.test tkt-7a31705a7e6.test
          272  +  tkt-7bbfb7d442.test tkt-80ba201079.test tkt-80e031a00f.test
          273  +  tkt-8454a207b9.test tkt-91e2e8ba6f.test tkt-94c04eaadb.test
          274  +  tkt-9d68c883.test tkt-a7b7803e.test tkt-b1d3a2e531.test
          275  +  tkt-b351d95f9.test tkt-b72787b1.test tkt-bd484a090c.test
          276  +  tkt-bdc6bbbb38.test tkt-c48d99d690.test tkt-cbd054fa6b.test
          277  +  tkt-d11f09d36e.test tkt-d635236375.test tkt-d82e3f3721.test
          278  +  tkt-f3e5abed55.test tkt-f777251dc7a.test tkt-f7b4edec.test
          279  +  tkt-f973c7ac31.test tkt-fa7bf5ec.test tkt-fc62af4523.test
          280  +  tkt-fc7bd6358f.test trigger1.test trigger2.test trigger3.test
          281  +  trigger4.test trigger5.test trigger6.test trigger7.test trigger8.test
          282  +  trigger9.test triggerA.test triggerB.test triggerC.test triggerD.test
          283  +  types2.test types3.test types.test unique.test unordered.test
          284  +  update.test view.test vtab1.test vtab2.test vtab3.test vtab4.test
          285  +  vtab5.test vtab6.test vtab7.test vtab8.test vtab9.test vtab_alter.test
          286  +  vtabA.test vtabB.test vtabC.test vtabD.test vtab_err.test vtabE.test
          287  +  vtabF.test where2.test where3.test where4.test where5.test where6.test
          288  +  where7.test where8m.test where8.test where9.test whereA.test whereB.test
          289  +  whereC.test whereD.test whereE.test whereF.test wherelimit.test
          290  +  where.test
          291  +}
   215    292   
   216    293   lappend ::testsuitelist xxx
   217    294   #-------------------------------------------------------------------------
   218    295   # Define the coverage related test suites:
   219    296   #
   220    297   #   coverage-wal
   221    298   #

Changes to test/where8.test.

   264    264       SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND a<5 ORDER BY a
   265    265     }
   266    266   } {1 1 2 2 3 3 4 2 4 4 0 0}
   267    267   do_test where8-3.12 {
   268    268     execsql_status {
   269    269       SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND +a<5 ORDER BY a
   270    270     }
   271         -} {1 1 2 2 3 3 4 2 4 4 0 0}
          271  +} {1 1 2 2 3 3 4 2 4 4 9 0}
   272    272   do_test where8-3.13 {
   273    273     execsql_status {
   274    274       SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND +a<5
   275    275     }
   276    276   } {1 1 2 2 3 3 4 2 4 4 9 0}
   277    277   
   278    278   do_test where8-3.14 {

Changes to test/where9.test.

   416    416   do_test where9-4.5 {
   417    417     catchsql {
   418    418       SELECT a FROM t1 INDEXED BY t1b
   419    419        WHERE +b>1000
   420    420          AND (c=31031 OR d IS NULL)
   421    421        ORDER BY +a
   422    422     }
   423         -} {1 {cannot use index: t1b}}
          423  +} {1 {no query solution}}
   424    424   do_test where9-4.6 {
   425    425     count_steps {
   426    426       SELECT a FROM t1 NOT INDEXED
   427    427        WHERE b>1000
   428    428          AND (c=31031 OR d IS NULL)
   429    429        ORDER BY +a
   430    430     }
................................................................................
   432    432   do_test where9-4.7 {
   433    433     catchsql {
   434    434       SELECT a FROM t1 INDEXED BY t1c
   435    435        WHERE b>1000
   436    436          AND (c=31031 OR d IS NULL)
   437    437        ORDER BY +a
   438    438     }
   439         -} {1 {cannot use index: t1c}}
          439  +} {1 {no query solution}}
   440    440   do_test where9-4.8 {
   441    441     catchsql {
   442    442       SELECT a FROM t1 INDEXED BY t1d
   443    443        WHERE b>1000
   444    444          AND (c=31031 OR d IS NULL)
   445    445        ORDER BY +a
   446    446     }
   447         -} {1 {cannot use index: t1d}}
          447  +} {1 {no query solution}}
   448    448   
   449    449   ifcapable explain {
   450    450     # The (c=31031 OR d IS NULL) clause is preferred over b>1000 because
   451    451     # the former is an equality test which is expected to return fewer rows.
   452    452     #
   453    453     do_execsql_test where9-5.1 {
   454    454       EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c=31031 OR d IS NULL)
................................................................................
   594    594     count_steps {
   595    595       BEGIN;
   596    596       DELETE FROM t1
   597    597        WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL)
   598    598           OR (b NOT NULL AND c IS NULL AND d NOT NULL)
   599    599           OR (b NOT NULL AND c NOT NULL AND d IS NULL)
   600    600     }
   601         -} {scan 98 sort 0}   ;# DELETEs rows 90 91 92 97
          601  +} {scan 0 sort 0}   ;# DELETEs rows 90 91 92 97
   602    602   do_test where9-6.3.6 {
   603    603     db eval {
   604    604       SELECT count(*) FROM t1 UNION ALL
   605    605       SELECT a FROM t1 WHERE a BETWEEN 85 AND 100;
   606    606       ROLLBACK;
   607    607     }
   608    608   } {95 85 86 87 88 89 93 94 95 96 98 99}
................................................................................
   611    611     count_steps {
   612    612       BEGIN;
   613    613       UPDATE t1 SET a=a+100
   614    614        WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
   615    615           OR (b NOT NULL AND +c IS NULL AND d NOT NULL)
   616    616           OR (b NOT NULL AND c NOT NULL AND d IS NULL)
   617    617     }
   618         -} {scan 98 sort 0}   ;# Add 100 to rowids 90 91 92 97
          618  +} {scan 0 sort 0}   ;# Add 100 to rowids 90 91 92 97
   619    619   do_test where9-6.3.8 {
   620    620     db eval {
   621    621       SELECT count(*) FROM t1 UNION ALL
   622    622       SELECT a FROM t1 WHERE a BETWEEN 85 AND 100;
   623    623       ROLLBACK;
   624    624     }
   625    625   } {99 85 86 87 88 89 93 94 95 96 98 99}
................................................................................
   701    701     count_steps {
   702    702       BEGIN;
   703    703       DELETE FROM t1
   704    704        WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
   705    705           OR (b NOT NULL AND +c IS NULL AND d NOT NULL)
   706    706           OR (b NOT NULL AND c NOT NULL AND d IS NULL)
   707    707     }
   708         -} {scan 98 sort 0}   ;# DELETEs rows 90 91 92 97
          708  +} {scan 0 sort 0}   ;# DELETEs rows 90 91 92 97
   709    709   do_test where9-6.6.2 {
   710    710     db eval {
   711    711       SELECT count(*) FROM t1 UNION ALL
   712    712       SELECT a FROM t1 WHERE a BETWEEN 85 AND 100;
   713    713       ROLLBACK;
   714    714     }
   715    715   } {95 85 86 87 88 89 93 94 95 96 98 99}
................................................................................
   718    718     count_steps {
   719    719       BEGIN;
   720    720       UPDATE t1 SET a=a+100
   721    721        WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
   722    722           OR (b NOT NULL AND +c IS NULL AND d NOT NULL)
   723    723           OR (b NOT NULL AND c NOT NULL AND d IS NULL)
   724    724     }
   725         -} {scan 98 sort 0}   ;# Add 100 to rowids 90 91 92 97
          725  +} {scan 0 sort 0}   ;# Add 100 to rowids 90 91 92 97
   726    726   do_test where9-6.6.4 {
   727    727     db eval {
   728    728       SELECT count(*) FROM t1 UNION ALL
   729    729       SELECT a FROM t1 WHERE a BETWEEN 85 AND 200;
   730    730       ROLLBACK;
   731    731     }
   732    732   } {99 85 86 87 88 89 93 94 95 96 98 99 190 191 192 197}
................................................................................
   764    764       ROLLBACK;
   765    765     }
   766    766   } {99 85 86 87 88 89 93 94 95 96 98 99 190 191 192 197}
   767    767   
   768    768   do_test where9-6.8.1 {
   769    769     catchsql {
   770    770       DELETE FROM t1 INDEXED BY t1b
   771         -     WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
          771  +     WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL)
          772  +        OR (b NOT NULL AND c IS NULL AND d NOT NULL)
          773  +        OR (b NOT NULL AND c NOT NULL AND d IS NULL)
          774  +  }
          775  +} {1 {no query solution}}
          776  +do_test where9-6.8.2 {
          777  +  catchsql {
          778  +    UPDATE t1 INDEXED BY t1b SET a=a+100
          779  +     WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL)
   772    780           OR (b NOT NULL AND c IS NULL AND d NOT NULL)
   773    781           OR (b NOT NULL AND c NOT NULL AND d IS NULL)
   774    782     }
   775         -} {1 {cannot use index: t1b}}
   776         -do_test where9-6.8.2 {
          783  +} {1 {no query solution}}
          784  +do_test where9-6.8.3 {
   777    785     catchsql {
   778    786       UPDATE t1 INDEXED BY t1b SET a=a+100
   779    787        WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
   780    788           OR (b NOT NULL AND c IS NULL AND d NOT NULL)
   781    789           OR (b NOT NULL AND c NOT NULL AND d IS NULL)
   782    790     }
   783         -} {1 {cannot use index: t1b}}
          791  +} {0 {}}
          792  +do_test where9-6.8.4 {
          793  +  catchsql {
          794  +    DELETE FROM t1 INDEXED BY t1b
          795  +     WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
          796  +        OR (b NOT NULL AND c IS NULL AND d NOT NULL)
          797  +        OR (b NOT NULL AND c NOT NULL AND d IS NULL)
          798  +  }
          799  +} {0 {}}
   784    800   
   785    801   ############################################################################
   786    802   # Test cases where terms inside an OR series are combined with AND terms
   787    803   # external to the OR clause.  In other words, cases where
   788    804   #
   789    805   #              x AND (y OR z)
   790    806   #

Changes to test/whereF.test.

    42     42   #
    43     43   # In order to make them more predictable, automatic indexes are turned off for
    44     44   # the tests in this file.
    45     45   #
    46     46   
    47     47   set testdir [file dirname $argv0]
    48     48   source $testdir/tester.tcl
    49         -set testprefix x
           49  +set testprefix whereF
    50     50   
    51     51   do_execsql_test 1.0 {
    52     52     PRAGMA automatic_index = 0;
    53     53     CREATE TABLE t1(a, b, c);
    54     54     CREATE TABLE t2(d, e, f);
    55     55     CREATE UNIQUE INDEX i1 ON t1(a);
    56     56     CREATE UNIQUE INDEX i2 ON t2(d);
................................................................................
    59     59   foreach {tn sql} {
    60     60     1 "SELECT * FROM t1,           t2 WHERE t1.a=t2.e AND t2.d<t1.b AND t1.c!=10"
    61     61     2 "SELECT * FROM t2,           t1 WHERE t1.a=t2.e AND t2.d<t1.b AND t1.c!=10"
    62     62     3 "SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a=t2.e AND t2.d<t1.b AND t1.c!=10"
    63     63   } {
    64     64     do_test 1.$tn {
    65     65       db eval "EXPLAIN QUERY PLAN $sql"
    66         -   } {/.*SCAN TABLE t2 .*SEARCH TABLE t1 .*/}
           66  +   } {/.*SCAN TABLE t2\y.*SEARCH TABLE t1\y.*/}
    67     67   }
    68     68   
    69     69   do_execsql_test 2.0 {
    70     70     DROP TABLE t1;
    71     71     DROP TABLE t2;
    72     72     CREATE TABLE t1(a, b, c);
    73     73     CREATE TABLE t2(d, e, f);
................................................................................
    80     80   foreach {tn sql} {
    81     81     1 "SELECT * FROM t1,           t2 WHERE t1.a>? AND t2.d>t1.c AND t1.b=t2.e"
    82     82     2 "SELECT * FROM t2,           t1 WHERE t1.a>? AND t2.d>t1.c AND t1.b=t2.e"
    83     83     3 "SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a>? AND t2.d>t1.c AND t1.b=t2.e"
    84     84   } {
    85     85     do_test 2.$tn {
    86     86       db eval "EXPLAIN QUERY PLAN $sql"
    87         -   } {/.*SCAN TABLE t2 .*SEARCH TABLE t1 .*/}
           87  +   } {/.*SCAN TABLE t2\y.*SEARCH TABLE t1\y.*/}
    88     88   }
    89     89   
    90     90   do_execsql_test 3.0 {
    91     91     DROP TABLE t1;
    92     92     DROP TABLE t2;
    93     93     CREATE TABLE t1(a, b, c);
    94     94     CREATE TABLE t2(d, e, f);
................................................................................
   105    105        WHERE t2.d=t1.b AND t1.a=(t2.d+1) AND t1.b = (t2.e+1)}
   106    106   
   107    107     3 {SELECT t1.a, t1.b, t2.d, t2.e FROM t2 CROSS JOIN t1 
   108    108        WHERE t2.d=t1.b AND t1.a=(t2.d+1) AND t1.b = (t2.e+1)}
   109    109   } {
   110    110     do_test 3.$tn {
   111    111       db eval "EXPLAIN QUERY PLAN $sql"
   112         -   } {/.*SCAN TABLE t2 .*SEARCH TABLE t1 .*/}
          112  +   } {/.*SCAN TABLE t2\y.*SEARCH TABLE t1\y.*/}
   113    113   }
   114    114   
   115    115   finish_test