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: |
73954f93c4c6f880c6e01d0d130e3fed |
| 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
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