/ Check-in [73954f93]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Query planner heuristic update: When doing a full table scan on a table that has an equality constraint on an unindexed column, do not allow the estimated number of output rows to be greater than half the total number of rows in the table.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 73954f93c4c6f880c6e01d0d130e3fed40fd4106
User & Date: drh 2014-09-06 01:35:57
Context
2014-09-06
02:00
Fix a couple of typos in comments. No changes to code. check-in: a758465e user: mistachkin tags: trunk
01:35
Query planner heuristic update: When doing a full table scan on a table that has an equality constraint on an unindexed column, do not allow the estimated number of output rows to be greater than half the total number of rows in the table. check-in: 73954f93 user: drh tags: trunk
2014-09-05
05:58
Fix harmless compiler warning. check-in: 73311906 user: mistachkin tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/sqliteInt.h.

  1155   1155   #define SQLITE_DistinctOpt    0x0020   /* DISTINCT using indexes */
  1156   1156   #define SQLITE_CoverIdxScan   0x0040   /* Covering index scans */
  1157   1157   #define SQLITE_OrderByIdxJoin 0x0080   /* ORDER BY of joins via index */
  1158   1158   #define SQLITE_SubqCoroutine  0x0100   /* Evaluate subqueries as coroutines */
  1159   1159   #define SQLITE_Transitive     0x0200   /* Transitive constraints */
  1160   1160   #define SQLITE_OmitNoopJoin   0x0400   /* Omit unused tables in joins */
  1161   1161   #define SQLITE_Stat3          0x0800   /* Use the SQLITE_STAT3 table */
  1162         -#define SQLITE_AdjustOutEst   0x1000   /* Adjust output estimates using WHERE */
  1163   1162   #define SQLITE_AllOpts        0xffff   /* All optimizations */
  1164   1163   
  1165   1164   /*
  1166   1165   ** Macros for testing whether or not optimizations are enabled or disabled.
  1167   1166   */
  1168   1167   #ifndef SQLITE_OMIT_BUILTIN_TEST
  1169   1168   #define OptimizationDisabled(db, mask)  (((db)->dbOptFlags&(mask))!=0)

Changes to src/where.c.

  4217   4217   ** WHERE clause that reference the loop but which are not used by an
  4218   4218   ** index.
  4219   4219   **
  4220   4220   ** In the current implementation, the first extra WHERE clause term reduces
  4221   4221   ** the number of output rows by a factor of 10 and each additional term
  4222   4222   ** reduces the number of output rows by sqrt(2).
  4223   4223   */
  4224         -static void whereLoopOutputAdjust(WhereClause *pWC, WhereLoop *pLoop){
         4224  +static void whereLoopOutputAdjust(
         4225  +  WhereClause *pWC,      /* The WHERE clause */
         4226  +  WhereLoop *pLoop,      /* The loop to adjust downward */
         4227  +  LogEst nRow            /* Number of rows in the entire table */
         4228  +){
  4225   4229     WhereTerm *pTerm, *pX;
  4226   4230     Bitmask notAllowed = ~(pLoop->prereq|pLoop->maskSelf);
  4227   4231     int i, j;
         4232  +  int nEq = 0;    /* Number of = constraints not within likely()/unlike() */
  4228   4233   
  4229         -  if( !OptimizationEnabled(pWC->pWInfo->pParse->db, SQLITE_AdjustOutEst) ){
  4230         -    return;
  4231         -  }
  4232   4234     for(i=pWC->nTerm, pTerm=pWC->a; i>0; i--, pTerm++){
  4233   4235       if( (pTerm->wtFlags & TERM_VIRTUAL)!=0 ) break;
  4234   4236       if( (pTerm->prereqAll & pLoop->maskSelf)==0 ) continue;
  4235   4237       if( (pTerm->prereqAll & notAllowed)!=0 ) continue;
  4236   4238       for(j=pLoop->nLTerm-1; j>=0; j--){
  4237   4239         pX = pLoop->aLTerm[j];
  4238   4240         if( pX==0 ) continue;
  4239   4241         if( pX==pTerm ) break;
  4240   4242         if( pX->iParent>=0 && (&pWC->a[pX->iParent])==pTerm ) break;
  4241   4243       }
  4242   4244       if( j<0 ){
  4243         -      pLoop->nOut += (pTerm->truthProb<=0 ? pTerm->truthProb : -1);
         4245  +      if( pTerm->truthProb<=0 ){
         4246  +        pLoop->nOut += pTerm->truthProb;
         4247  +      }else{
         4248  +        pLoop->nOut--;
         4249  +        if( pTerm->eOperator&WO_EQ ) nEq++;
         4250  +      }
  4244   4251       }
         4252  +  }
         4253  +  /* TUNING:  If there is at least one equality constraint in the WHERE
         4254  +  ** clause that does not have a likelihood() explicitly assigned to it
         4255  +  ** then do not let the estimated number of output rows exceed half 
         4256  +  ** the number of rows in the table. */
         4257  +  if( nEq && pLoop->nOut>nRow-10 ){
         4258  +    pLoop->nOut = nRow - 10;
  4245   4259     }
  4246   4260   }
  4247   4261   
  4248   4262   /*
  4249   4263   ** Adjust the cost C by the costMult facter T.  This only occurs if
  4250   4264   ** compiled with -DSQLITE_ENABLE_COSTMULT
  4251   4265   */
................................................................................
  4284   4298     u16 saved_nLTerm;               /* Original value of pNew->nLTerm */
  4285   4299     u16 saved_nEq;                  /* Original value of pNew->u.btree.nEq */
  4286   4300     u16 saved_nSkip;                /* Original value of pNew->u.btree.nSkip */
  4287   4301     u32 saved_wsFlags;              /* Original value of pNew->wsFlags */
  4288   4302     LogEst saved_nOut;              /* Original value of pNew->nOut */
  4289   4303     int iCol;                       /* Index of the column in the table */
  4290   4304     int rc = SQLITE_OK;             /* Return code */
         4305  +  LogEst rSize;                   /* Number of rows in the table */
  4291   4306     LogEst rLogSize;                /* Logarithm of table size */
  4292   4307     WhereTerm *pTop = 0, *pBtm = 0; /* Top and bottom range constraints */
  4293   4308   
  4294   4309     pNew = pBuilder->pNew;
  4295   4310     if( db->mallocFailed ) return SQLITE_NOMEM;
  4296   4311   
  4297   4312     assert( (pNew->wsFlags & WHERE_VIRTUALTABLE)==0 );
................................................................................
  4313   4328     saved_nEq = pNew->u.btree.nEq;
  4314   4329     saved_nSkip = pNew->u.btree.nSkip;
  4315   4330     saved_nLTerm = pNew->nLTerm;
  4316   4331     saved_wsFlags = pNew->wsFlags;
  4317   4332     saved_prereq = pNew->prereq;
  4318   4333     saved_nOut = pNew->nOut;
  4319   4334     pNew->rSetup = 0;
  4320         -  rLogSize = estLog(pProbe->aiRowLogEst[0]);
         4335  +  rSize = pProbe->aiRowLogEst[0];
         4336  +  rLogSize = estLog(rSize);
  4321   4337   
  4322   4338     /* Consider using a skip-scan if there are no WHERE clause constraints
  4323   4339     ** available for the left-most terms of the index, and if the average
  4324   4340     ** number of repeats in the left-most terms is at least 18. 
  4325   4341     **
  4326   4342     ** The magic number 18 is selected on the basis that scanning 17 rows
  4327   4343     ** is almost always quicker than an index seek (even though if the index
................................................................................
  4490   4506         pNew->rRun = sqlite3LogEstAdd(pNew->rRun, pNew->nOut + 16);
  4491   4507       }
  4492   4508       ApplyCostMultiplier(pNew->rRun, pProbe->pTable->costMult);
  4493   4509   
  4494   4510       nOutUnadjusted = pNew->nOut;
  4495   4511       pNew->rRun += nInMul + nIn;
  4496   4512       pNew->nOut += nInMul + nIn;
  4497         -    whereLoopOutputAdjust(pBuilder->pWC, pNew);
         4513  +    whereLoopOutputAdjust(pBuilder->pWC, pNew, rSize);
  4498   4514       rc = whereLoopInsert(pBuilder, pNew);
  4499   4515   
  4500   4516       if( pNew->wsFlags & WHERE_COLUMN_RANGE ){
  4501   4517         pNew->nOut = saved_nOut;
  4502   4518       }else{
  4503   4519         pNew->nOut = nOutUnadjusted;
  4504   4520       }
................................................................................
  4744   4760         pNew->wsFlags = WHERE_IPK;
  4745   4761   
  4746   4762         /* Full table scan */
  4747   4763         pNew->iSortIdx = b ? iSortIdx : 0;
  4748   4764         /* TUNING: Cost of full table scan is (N*3.0). */
  4749   4765         pNew->rRun = rSize + 16;
  4750   4766         ApplyCostMultiplier(pNew->rRun, pTab->costMult);
  4751         -      whereLoopOutputAdjust(pWC, pNew);
         4767  +      whereLoopOutputAdjust(pWC, pNew, rSize);
  4752   4768         rc = whereLoopInsert(pBuilder, pNew);
  4753   4769         pNew->nOut = rSize;
  4754   4770         if( rc ) break;
  4755   4771       }else{
  4756   4772         Bitmask m;
  4757   4773         if( pProbe->isCovering ){
  4758   4774           pNew->wsFlags = WHERE_IDX_ONLY | WHERE_INDEXED;
................................................................................
  4780   4796           ** index and table rows. If this is a non-covering index scan,
  4781   4797           ** also add the cost of visiting table rows (N*3.0).  */
  4782   4798           pNew->rRun = rSize + 1 + (15*pProbe->szIdxRow)/pTab->szTabRow;
  4783   4799           if( m!=0 ){
  4784   4800             pNew->rRun = sqlite3LogEstAdd(pNew->rRun, rSize+16);
  4785   4801           }
  4786   4802           ApplyCostMultiplier(pNew->rRun, pTab->costMult);
  4787         -        whereLoopOutputAdjust(pWC, pNew);
         4803  +        whereLoopOutputAdjust(pWC, pNew, rSize);
  4788   4804           rc = whereLoopInsert(pBuilder, pNew);
  4789   4805           pNew->nOut = rSize;
  4790   4806           if( rc ) break;
  4791   4807         }
  4792   4808       }
  4793   4809   
  4794   4810       rc = whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, 0);

Changes to test/whereJ.test.

   369    369        AND t3b.id BETWEEN t2b.minChild AND t2b.maxChild
   370    370        AND t4.id BETWEEN t3a.minChild AND t3b.maxChild
   371    371     ORDER BY t4.x;
   372    372   } {~/SCAN/}
   373    373   
   374    374   ############################################################################
   375    375   
   376         -ifcapable stat4 {
   377         -  # Create and populate table.
   378         -  do_execsql_test 3.1 { CREATE TABLE t1(a, b, c) }
   379         -  for {set i 0} {$i < 32} {incr i 2} {
   380         -    for {set x 0} {$x < 100} {incr x} {
   381         -      execsql { INSERT INTO t1 VALUES($i, $x, $c) }
   382         -      incr c
   383         -    }
   384         -    execsql { INSERT INTO t1 VALUES($i+1, 5, $c) }
          376  +# Create and populate table.
          377  +do_execsql_test 3.1 { CREATE TABLE t1(a, b, c) }
          378  +for {set i 0} {$i < 32} {incr i 2} {
          379  +  for {set x 0} {$x < 100} {incr x} {
          380  +    execsql { INSERT INTO t1 VALUES($i, $x, $c) }
   385    381       incr c
   386    382     }
   387         -  
   388         -  do_execsql_test 3.2 {
   389         -    SELECT a, count(*) FROM t1 GROUP BY a HAVING a < 8;
   390         -  } {
   391         -    0 100 1 1 2 100 3 1 4 100 5 1 6 100 7 1
   392         -  }
   393         -  
   394         -  do_execsql_test 3.3 {
   395         -    CREATE INDEX idx_ab ON t1(a, b);
   396         -    CREATE INDEX idx_c ON t1(c);
   397         -    ANALYZE;
   398         -  } {}
   399         -  
   400         -  # This one should use index "idx_c".
   401         -  do_eqp_test 3.4 {
   402         -    SELECT * FROM t1 WHERE 
   403         -      a = 4 AND b BETWEEN 20 AND 80           -- Matches 80 rows
   404         -        AND
   405         -      c BETWEEN 150 AND 160                   -- Matches 10 rows
   406         -  } {
   407         -    0 0 0 {SEARCH TABLE t1 USING INDEX idx_c (c>? AND c<?)}
   408         -  }
   409         -  
   410         -  # This one should use index "idx_ab".
   411         -  do_eqp_test 3.5 {
   412         -    SELECT * FROM t1 WHERE 
   413         -      a = 5 AND b BETWEEN 20 AND 80           -- Matches 1 row
   414         -        AND
   415         -      c BETWEEN 150 AND 160                   -- Matches 10 rows
   416         -  } {
   417         -    0 0 0 {SEARCH TABLE t1 USING INDEX idx_ab (a=? AND b>? AND b<?)}
   418         -  }
          383  +  execsql { INSERT INTO t1 VALUES($i+1, 5, $c) }
          384  +  incr c
          385  +}
          386  +
          387  +do_execsql_test 3.2 {
          388  +  SELECT a, count(*) FROM t1 GROUP BY a HAVING a < 8;
          389  +} {
          390  +  0 100 1 1 2 100 3 1 4 100 5 1 6 100 7 1
          391  +}
          392  +
          393  +do_execsql_test 3.3 {
          394  +  CREATE INDEX idx_ab ON t1(a, b);
          395  +  CREATE INDEX idx_c ON t1(c);
          396  +  ANALYZE;
          397  +} {}
          398  +
          399  +# This one should use index "idx_c".
          400  +do_eqp_test 3.4 {
          401  +  SELECT * FROM t1 WHERE 
          402  +    a = 4 AND b BETWEEN 20 AND 80           -- Matches 80 rows
          403  +      AND
          404  +    c BETWEEN 150 AND 160                   -- Matches 10 rows
          405  +} {
          406  +  0 0 0 {SEARCH TABLE t1 USING INDEX idx_c (c>? AND c<?)}
          407  +}
          408  +
          409  +# This one should use index "idx_ab".
          410  +do_eqp_test 3.5 {
          411  +  SELECT * FROM t1 WHERE 
          412  +    a = 5 AND b BETWEEN 20 AND 80           -- Matches 1 row
          413  +      AND
          414  +    c BETWEEN 150 AND 160                   -- Matches 10 rows
          415  +} {
          416  +  0 0 0 {SEARCH TABLE t1 USING INDEX idx_ab (a=? AND b>? AND b<?)}
   419    417   }
          418  +
          419  +###########################################################################################
          420  +
          421  +# Reset the database and setup for a test case derived from an actual SQLite users
          422  +#
          423  +db close
          424  +sqlite3 db test.db
          425  +do_execsql_test 4.1 {
          426  +  CREATE TABLE le(
          427  +    le_id largeint,
          428  +    xid char(31),
          429  +    type smallint,
          430  +    name char(255) DEFAULT '',
          431  +    mtime largeint DEFAULT 0,
          432  +    muuid int DEFAULT 0
          433  +  );
          434  +  CREATE TABLE cx(
          435  +    cx_id largeint,
          436  +    code char(31),
          437  +    type smallint,
          438  +    name char(31),
          439  +    description varchar,
          440  +    role smallint,
          441  +    mtime largeint DEFAULT 0,
          442  +    muuid int DEFAULT 0,
          443  +    le_id largeint DEFAULT 0,
          444  +    imco smallint DEFAULT 0
          445  +  );
          446  +  CREATE TABLE px(
          447  +    px_id largeint,
          448  +    cx_id largeint,
          449  +    px_tid largeint,
          450  +    name char(31),
          451  +    description varchar DEFAULT '',
          452  +    ia smallint,
          453  +    sl smallint,
          454  +    le_id largeint DEFAULT 0,
          455  +    mtime largeint DEFAULT 0,
          456  +    muuid int DEFAULT 0
          457  +  );
          458  +  CREATE INDEX le_id on le (le_id);
          459  +  CREATE INDEX c_id on cx (cx_id);
          460  +  CREATE INDEX c_leid on cx (le_id);
          461  +  CREATE INDEX p_id on px (px_id);
          462  +  CREATE INDEX p_cid0 on px (cx_id);
          463  +  CREATE INDEX p_pt on px (px_tid);
          464  +  CREATE INDEX p_leid on px (le_id);
          465  +} {}
          466  +do_execsql_test 4.2 {
          467  +  ANALYZE sqlite_master;
          468  +  INSERT INTO sqlite_stat1 VALUES('le','le_id','1979 1');
          469  +  INSERT INTO sqlite_stat1 VALUES('cx','c_leid','852 171');
          470  +  INSERT INTO sqlite_stat1 VALUES('cx','c_id','852 1');
          471  +  INSERT INTO sqlite_stat1 VALUES('px','p_leid','114443 63');
          472  +  INSERT INTO sqlite_stat1 VALUES('px','p_pt','114443 22889');
          473  +  INSERT INTO sqlite_stat1 VALUES('px','p_cid0','114443 181');
          474  +  INSERT INTO sqlite_stat1 VALUES('px','p_id','114443 1');
          475  +  INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','162 162','162 162',X'030202013903fb');
          476  +  INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','208 208','208 208',X'0302020253012d');
          477  +  INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','219 219','219 219',X'030202025e0131');
          478  +  INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','248 248','248 248',X'030202027b014e');
          479  +  INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','265 265','265 265',X'030202028c015f');
          480  +  INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','358 358','358 358',X'03020202e901bc');
          481  +  INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','439 439','439 439',X'030202033a020d');
          482  +  INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','657 657','657 657',X'030202041402b4');
          483  +  INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','659 659','659 659',X'030202041602b6');
          484  +  INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','681 681','681 681',X'030202042c02cc');
          485  +  INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','831 831','831 831',X'03020204c20482');
          486  +  INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','852 852','852 852',X'03020204d70497');
          487  +  INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','870 870','870 870',X'03020204e904a9');
          488  +  INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','879 879','879 879',X'03020204f204b2');
          489  +  INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','1099 1099','1099 1099',X'03020205ce058e');
          490  +  INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','1273 1273','1273 1273',X'030202067c05a9');
          491  +  INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','1319 1319','1319 1319',X'03020206e30730');
          492  +  INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','1330 1330','1330 1330',X'0302020700035b');
          493  +  INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','1539 1539','1539 1539',X'03020207d105d8');
          494  +  INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','1603 1603','1603 1603',X'03020208390780');
          495  +  INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','1759 1759','1759 1759',X'030202092f0618');
          496  +  INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','1843 1843','1843 1843',X'03020209880650');
          497  +  INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','1915 1915','1915 1915',X'03020209d0068b');
          498  +  INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','1927 1927','1927 1927',X'03020209dc0697');
          499  +  INSERT INTO sqlite_stat4 VALUES('cx','c_leid','846 1','0 94','0 94',X'0308015f');
          500  +  INSERT INTO sqlite_stat4 VALUES('cx','c_leid','846 1','0 189','0 189',X'03080200be');
          501  +  INSERT INTO sqlite_stat4 VALUES('cx','c_leid','846 1','0 284','0 284',X'0308020120');
          502  +  INSERT INTO sqlite_stat4 VALUES('cx','c_leid','846 1','0 379','0 379',X'030802017f');
          503  +  INSERT INTO sqlite_stat4 VALUES('cx','c_leid','846 1','0 474','0 474',X'03080201de');
          504  +  INSERT INTO sqlite_stat4 VALUES('cx','c_leid','846 1','0 569','0 569',X'030802023d');
          505  +  INSERT INTO sqlite_stat4 VALUES('cx','c_leid','846 1','0 664','0 664',X'030802029f');
          506  +  INSERT INTO sqlite_stat4 VALUES('cx','c_leid','846 1','0 759','0 759',X'03080202fe');
          507  +  INSERT INTO sqlite_stat4 VALUES('cx','c_leid','3 1','846 847','1 847',X'0301024500e6');
          508  +  INSERT INTO sqlite_stat4 VALUES('cx','c_leid','1 1','849 849','2 849',X'03010246027e');
          509  +  INSERT INTO sqlite_stat4 VALUES('cx','c_leid','1 1','850 850','3 850',X'0301024700c9');
          510  +  INSERT INTO sqlite_stat4 VALUES('cx','c_leid','1 1','851 851','4 851',X'03010248027f');
          511  +  INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','94 94','94 94',X'03020200b801a8');
          512  +  INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','113 113','113 113',X'03020200d101ad');
          513  +  INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','171 171','171 171',X'030201011d2a');
          514  +  INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','177 177','177 177',X'030202012600f2');
          515  +  INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','189 189','189 189',X'030202013501c8');
          516  +  INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','206 206','206 206',X'030201014f2d');
          517  +  INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','231 231','231 231',X'030202016d00fc');
          518  +  INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','284 284','284 284',X'03020201b702d0');
          519  +  INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','291 291','291 291',X'03020101c042');
          520  +  INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','311 311','311 311',X'03020201d801e7');
          521  +  INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','339 339','339 339',X'03020101f74b');
          522  +  INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','347 347','347 347',X'03020202030118');
          523  +  INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','379 379','379 379',X'030202022f01fa');
          524  +  INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','393 393','393 393',X'030201023f55');
          525  +  INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','407 407','407 407',X'03020202500201');
          526  +  INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','413 413','413 413',X'03020102565a');
          527  +  INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','468 468','468 468',X'030201029468');
          528  +  INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','474 474','474 474',X'030202029a0211');
          529  +  INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','517 517','517 517',X'03020102cc76');
          530  +  INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','548 548','548 548',X'03020202f00223');
          531  +  INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','569 569','569 569',X'03020203090087');
          532  +  INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','664 664','664 664',X'03020203740163');
          533  +  INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','759 759','759 759',X'03020203e800b3');
          534  +  INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','803 803','803 803',X'030202041b026f');
          535  +  INSERT INTO sqlite_stat4 VALUES('px','p_leid','110728 1','0 12715','0 12715',X'030802345b');
          536  +  INSERT INTO sqlite_stat4 VALUES('px','p_leid','110728 1','0 25431','0 25431',X'0308026718');
          537  +  INSERT INTO sqlite_stat4 VALUES('px','p_leid','110728 1','0 38147','0 38147',X'030803009a5c');
          538  +  INSERT INTO sqlite_stat4 VALUES('px','p_leid','110728 1','0 50863','0 50863',X'03080300cdbe');
          539  +  INSERT INTO sqlite_stat4 VALUES('px','p_leid','110728 1','0 63579','0 63579',X'0308030100e8');
          540  +  INSERT INTO sqlite_stat4 VALUES('px','p_leid','110728 1','0 76295','0 76295',X'03080301351d');
          541  +  INSERT INTO sqlite_stat4 VALUES('px','p_leid','110728 1','0 89011','0 89011',X'03080301674c');
          542  +  INSERT INTO sqlite_stat4 VALUES('px','p_leid','110728 1','0 101727','0 101727',X'030803019b99');
          543  +  INSERT INTO sqlite_stat4 VALUES('px','p_leid','28 1','110824 110843','16 110843',X'0301037a0107f1');
          544  +  INSERT INTO sqlite_stat4 VALUES('px','p_leid','53 1','110873 110875','25 110875',X'0302020095275a');
          545  +  INSERT INTO sqlite_stat4 VALUES('px','p_leid','32 1','110927 110936','27 110936',X'030203009b009b4a');
          546  +  INSERT INTO sqlite_stat4 VALUES('px','p_leid','51 1','110980 111017','30 111017',X'03020300a4016c00');
          547  +  INSERT INTO sqlite_stat4 VALUES('px','p_leid','67 1','111047 111059','38 111059',X'03020200af2611');
          548  +  INSERT INTO sqlite_stat4 VALUES('px','p_leid','60 1','111136 111156','43 111156',X'03020300bc009aeb');
          549  +  INSERT INTO sqlite_stat4 VALUES('px','p_leid','42 1','111222 111239','59 111239',X'03020300d200b17b');
          550  +  INSERT INTO sqlite_stat4 VALUES('px','p_leid','36 1','111264 111266','60 111266',X'03020200d426d6');
          551  +  INSERT INTO sqlite_stat4 VALUES('px','p_leid','27 1','111733 111757','159 111757',X'030203014e017e1b');
          552  +  INSERT INTO sqlite_stat4 VALUES('px','p_leid','36 1','111760 111773','160 111773',X'030203014f00a2b9');
          553  +  INSERT INTO sqlite_stat4 VALUES('px','p_leid','29 1','111822 111833','167 111833',X'0302030176009c22');
          554  +  INSERT INTO sqlite_stat4 VALUES('px','p_leid','75 1','113031 113095','1190 113095',X'030203068501912c');
          555  +  INSERT INTO sqlite_stat4 VALUES('px','p_leid','132 1','113230 113263','1252 113263',X'0302030711009ee6');
          556  +  INSERT INTO sqlite_stat4 VALUES('px','p_leid','110 1','113851 113918','1572 113918',X'03020308e9011ca2');
          557  +  INSERT INTO sqlite_stat4 VALUES('px','p_leid','78 1','114212 114217','1791 114217',X'03020209e13b24');
          558  +  INSERT INTO sqlite_stat4 VALUES('px','p_leid','112 1','114303 114351','1799 114351',X'03020309ea0128f2');
          559  +  INSERT INTO sqlite_stat4 VALUES('px','p_pt','89824 1','0 12715','0 12715',X'030802477e');
          560  +  INSERT INTO sqlite_stat4 VALUES('px','p_pt','89824 1','0 25431','0 25431',X'0308027c20');
          561  +  INSERT INTO sqlite_stat4 VALUES('px','p_pt','89824 1','0 38147','0 38147',X'03080300c211');
          562  +  INSERT INTO sqlite_stat4 VALUES('px','p_pt','89824 1','0 50863','0 50863',X'03080300fbe5');
          563  +  INSERT INTO sqlite_stat4 VALUES('px','p_pt','89824 1','0 63579','0 63579',X'0308030140ff');
          564  +  INSERT INTO sqlite_stat4 VALUES('px','p_pt','89824 1','0 76295','0 76295',X'03080301792d');
          565  +  INSERT INTO sqlite_stat4 VALUES('px','p_pt','89824 1','0 89011','0 89011',X'03080301bb68');
          566  +  INSERT INTO sqlite_stat4 VALUES('px','p_pt','24217 1','89824 101727','1 101727',X'03090300da12');
          567  +  INSERT INTO sqlite_stat4 VALUES('px','p_pt','154 1','114041 114154','2 114154',X'0301030200e5e9');
          568  +  INSERT INTO sqlite_stat4 VALUES('px','p_pt','198 1','114195 114351','3 114351',X'03010303015cb1');
          569  +  INSERT INTO sqlite_stat4 VALUES('px','p_pt','50 1','114393 114441','4 114441',X'0301030401b2ef');
          570  +  INSERT INTO sqlite_stat4 VALUES('px','p_cid0','3867 1','3 3736','2 3736',X'03010337015c6a');
          571  +  INSERT INTO sqlite_stat4 VALUES('px','p_cid0','4194 1','4177 8209','5 8209',X'0301033b015075');
          572  +  INSERT INTO sqlite_stat4 VALUES('px','p_cid0','4335 1','8371 11129','6 11129',X'0301033d0156fc');
          573  +  INSERT INTO sqlite_stat4 VALUES('px','p_cid0','1740 1','12706 12715','7 12715',X'0301023e34b9');
          574  +  INSERT INTO sqlite_stat4 VALUES('px','p_cid0','1680 1','14446 15487','8 15487',X'0301033f011694');
          575  +  INSERT INTO sqlite_stat4 VALUES('px','p_cid0','7163 1','20116 25431','32 25431',X'03020300a400ed26');
          576  +  INSERT INTO sqlite_stat4 VALUES('px','p_cid0','1525 1','29100 29302','42 29302',X'03020200bb00d1');
          577  +  INSERT INTO sqlite_stat4 VALUES('px','p_cid0','3703 1','30655 33323','45 33323',X'03020300be013fa5');
          578  +  INSERT INTO sqlite_stat4 VALUES('px','p_cid0','2612 1','37767 38147','61 38147',X'03020200e32828');
          579  +  INSERT INTO sqlite_stat4 VALUES('px','p_cid0','1882 1','40545 41584','63 41584',X'03020300ea01a35a');
          580  +  INSERT INTO sqlite_stat4 VALUES('px','p_cid0','6984 1','44110 50863','73 50863',X'0302030102017467');
          581  +  INSERT INTO sqlite_stat4 VALUES('px','p_cid0','1728 1','51230 51680','75 51680',X'030203010400b3e0');
          582  +  INSERT INTO sqlite_stat4 VALUES('px','p_cid0','2805 1','55491 57936','95 57936',X'030203014101a004');
          583  +  INSERT INTO sqlite_stat4 VALUES('px','p_cid0','2837 1','58934 59506','103 59506',X'030203015900a283');
          584  +  INSERT INTO sqlite_stat4 VALUES('px','p_cid0','94 1','63492 63579','137 63579',X'0302030191016319');
          585  +  INSERT INTO sqlite_stat4 VALUES('px','p_cid0','3573 1','63591 64497','140 64497',X'030203019c00822e');
          586  +  INSERT INTO sqlite_stat4 VALUES('px','p_cid0','5037 1','70917 73033','160 73033',X'03020301c70091d9');
          587  +  INSERT INTO sqlite_stat4 VALUES('px','p_cid0','1940 1','75954 76295','161 76295',X'03020201c817f1');
          588  +  INSERT INTO sqlite_stat4 VALUES('px','p_cid0','1927 1','83926 84371','209 84371',X'03020202114295');
          589  +  INSERT INTO sqlite_stat4 VALUES('px','p_cid0','1522 1','86601 88117','213 88117',X'030203021b01b7b5');
          590  +  INSERT INTO sqlite_stat4 VALUES('px','p_cid0','210 1','88906 89011','226 89011',X'030203022800dbbb');
          591  +  INSERT INTO sqlite_stat4 VALUES('px','p_cid0','6165 1','92125 98066','258 98066',X'030203024d0189ac');
          592  +  INSERT INTO sqlite_stat4 VALUES('px','p_cid0','2900 1','100721 101727','293 101727',X'030203027500cf39');
          593  +  INSERT INTO sqlite_stat4 VALUES('px','p_cid0','1501 1','110012 110154','503 110154',X'0302020380493a');
          594  +  INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','11129 11129','11129 11129',X'03030300d84e014d51');
          595  +  INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','12715 12715','12715 12715',X'03030200de816f51');
          596  +  INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','13030 13030','13030 13030',X'03030200e05b6fc4');
          597  +  INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','25431 25431','25431 25431',X'0303030123df00efb0');
          598  +  INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','29302 29302','29302 29302',X'030302013a2812c7');
          599  +  INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','35463 35463','35463 35463',X'03030301666e00f866');
          600  +  INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','38147 38147','38147 38147',X'030302017a391b74');
          601  +  INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','38525 38525','38525 38525',X'030303017c6e00fb58');
          602  +  INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','50863 50863','50863 50863',X'03030201b68724dd');
          603  +  INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','58461 58461','58461 58461',X'03030201d95b2e1e');
          604  +  INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','59506 59506','59506 59506',X'03030301dd7000a0fb');
          605  +  INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','63468 63468','63468 63468',X'03030301ecea011405');
          606  +  INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','63579 63579','63579 63579',X'03030201ed5932d5');
          607  +  INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','64497 64497','64497 64497',X'03030301f0ef00a680');
          608  +  INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','73033 73033','73033 73033',X'0303030225b90190e5');
          609  +  INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','75650 75650','75650 75650',X'030303023a19019362');
          610  +  INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','76295 76295','76295 76295',X'030303023e9801940c');
          611  +  INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','79152 79152','79152 79152',X'030303024be50196b9');
          612  +  INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','83249 83249','83249 83249',X'0303030261750123b1');
          613  +  INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','89011 89011','89011 89011',X'030303027b3900c3af');
          614  +  INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','98066 98066','98066 98066',X'03030302a76500ce54');
          615  +  INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','101590 101590','101590 101590',X'03030302b63d00d3b5');
          616  +  INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','101727 101727','101727 101727',X'03030202b6f24e9b');
          617  +  INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','107960 107960','107960 107960',X'03030302d8ce0136ad');
          618  +  ANALYZE sqlite_master;
          619  +} {}
          620  +
          621  +# The following query should do a ful ltable scan of cx in the outer loop.
          622  +# It is not correct to search table px using indx p_pt in the outer loop
          623  +# with cx in the middle loop.  Test case from Bloomberg on 2014-09-05.
          624  +#
          625  +do_execsql_test 4.2 {
          626  +  EXPLAIN QUERY PLAN
          627  +  SELECT
          628  +     px.name,
          629  +     px.description
          630  +  FROM
          631  +     le,
          632  +     cx,
          633  +     px
          634  +  WHERE
          635  +     cx.code = '2990'
          636  +     AND cx.type=2
          637  +     AND px.cx_id = cx.cx_id
          638  +     AND px.px_tid = 0
          639  +     AND px.le_id = le.le_id;
          640  +} {/.*SCAN TABLE cx.*SEARCH TABLE px.*SEARCH TABLE le.*/}
   420    641   
   421    642   
   422    643   finish_test