Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Fix a bug in the optimizer that was causing it to miss an OR optimization opportunity. (CVS 3502) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
9bf153b54c6e9ba16914dedd9e2949f3 |
User & Date: | drh 2006-11-06 15:10:05.000 |
Context
2006-11-06
| ||
21:20 | Use the difference between the SQLITE_IOERR_SHORT_READ and SQLITE_IOERR_READ returns from sqlite3OsRead() to make decisions about what to do with the error. (CVS 3503) (check-in: 6324ea811e user: drh tags: trunk) | |
15:10 | Fix a bug in the optimizer that was causing it to miss an OR optimization opportunity. (CVS 3502) (check-in: 9bf153b54c user: drh tags: trunk) | |
2006-11-01
| ||
12:20 | Modify Makefile.in to install sqlite3ext.h. (CVS 3501) (check-in: 35c8c47817 user: drh tags: trunk) | |
Changes
Changes to src/where.c.
︙ | ︙ | |||
12 13 14 15 16 17 18 | ** This module contains C code that generates VDBE code used to process ** the WHERE clause of SQL statements. This module is reponsible for ** generating the code that loops through a table looking for applicable ** rows. Indices are selected and used to speed the search when doing ** so is applicable. Because this module is responsible for selecting ** indices, you might also think of this module as the "query optimizer". ** | | | 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | ** This module contains C code that generates VDBE code used to process ** the WHERE clause of SQL statements. This module is reponsible for ** generating the code that loops through a table looking for applicable ** rows. Indices are selected and used to speed the search when doing ** so is applicable. Because this module is responsible for selecting ** indices, you might also think of this module as the "query optimizer". ** ** $Id: where.c,v 1.232 2006/11/06 15:10:05 drh Exp $ */ #include "sqliteInt.h" /* ** The number of bits in a Bitmask. "BMS" means "BitMask Size". */ #define BMS (sizeof(Bitmask)*8) |
︙ | ︙ | |||
723 724 725 726 727 728 729 730 731 732 733 734 | ok = 0; } } }while( !ok && (sOr.a[j++].flags & TERM_COPIED)!=0 && j<sOr.nTerm ); if( ok ){ ExprList *pList = 0; Expr *pNew, *pDup; for(i=sOr.nTerm-1, pOrTerm=sOr.a; i>=0 && ok; i--, pOrTerm++){ if( (pOrTerm->flags & TERM_OR_OK)==0 ) continue; pDup = sqlite3ExprDup(pOrTerm->pExpr->pRight); pList = sqlite3ExprListAppend(pList, pDup, 0); } | > > > | < < < < | 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 | ok = 0; } } }while( !ok && (sOr.a[j++].flags & TERM_COPIED)!=0 && j<sOr.nTerm ); if( ok ){ ExprList *pList = 0; Expr *pNew, *pDup; Expr *pLeft = 0; for(i=sOr.nTerm-1, pOrTerm=sOr.a; i>=0 && ok; i--, pOrTerm++){ if( (pOrTerm->flags & TERM_OR_OK)==0 ) continue; pDup = sqlite3ExprDup(pOrTerm->pExpr->pRight); pList = sqlite3ExprListAppend(pList, pDup, 0); pLeft = pOrTerm->pExpr->pLeft; } assert( pLeft!=0 ); pDup = sqlite3ExprDup(pLeft); pNew = sqlite3Expr(TK_IN, pDup, 0, 0); if( pNew ){ int idxNew; transferJoinMarkings(pNew, pExpr); pNew->pList = pList; idxNew = whereClauseInsert(pWC, pNew, TERM_VIRTUAL|TERM_DYNAMIC); exprAnalyze(pSrc, pMaskSet, pWC, idxNew); |
︙ | ︙ |
Changes to test/where2.test.
︙ | ︙ | |||
8 9 10 11 12 13 14 | # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the use of indices in WHERE clauses # based on recent changes to the optimizer. # | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the use of indices in WHERE clauses # based on recent changes to the optimizer. # # $Id: where2.test,v 1.10 2006/11/06 15:10:06 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Build some test data # do_test where2-1.0 { |
︙ | ︙ | |||
447 448 449 450 451 452 453 454 | SELECT w FROM tx WHERE x IN (SELECT x FROM t1 WHERE +w BETWEEN 200 AND 300) AND y IN (SELECT y FROM t1 WHERE +w BETWEEN 10 AND 20) AND z IN (SELECT z FROM t1 WHERE +w BETWEEN 10 AND 20) } } {} } finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 | SELECT w FROM tx WHERE x IN (SELECT x FROM t1 WHERE +w BETWEEN 200 AND 300) AND y IN (SELECT y FROM t1 WHERE +w BETWEEN 10 AND 20) AND z IN (SELECT z FROM t1 WHERE +w BETWEEN 10 AND 20) } } {} } # Make sure WHERE clauses of the form A=1 AND (B=2 OR B=3) are optimized # when we have an index on A and B. # ifcapable or_opt { do_test where2-9.1 { execsql { BEGIN; CREATE TABLE t10(a,b,c); INSERT INTO t10 VALUES(1,1,1); INSERT INTO t10 VALUES(1,2,2); INSERT INTO t10 VALUES(1,3,3); } for {set i 4} {$i<=1000} {incr i} { execsql {INSERT INTO t10 VALUES(1,$i,$i)} } execsql { CREATE INDEX i10 ON t10(a,b); COMMIT; SELECT count(*) FROM t10; } } 1000 do_test where2-9.2 { count { SELECT * FROM t10 WHERE a=1 AND (b=2 OR b=3) } } {1 2 2 1 3 3 7} } finish_test |