Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | In joins of the form "A left B, C" make sure they are not transformed into "A left C, B". Ticket #1830. See also #1652. (CVS 3203) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
2baa983653796e16d36739e37b0be167 |
User & Date: | drh 2006-06-06 11:45:55.000 |
Context
2006-06-06
| ||
12:32 | Command "sqlite3 -version" returns 0 instead of 1. Ticket #1771. (CVS 3204) (check-in: fd0fae4835 user: drh tags: trunk) | |
11:45 | In joins of the form "A left B, C" make sure they are not transformed into "A left C, B". Ticket #1830. See also #1652. (CVS 3203) (check-in: 2baa983653 user: drh tags: trunk) | |
2006-06-04
| ||
23:31 | Add comments to the changes of check-in (3200). (CVS 3202) (check-in: 697498d4e8 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.209 2006/06/06 11:45:55 drh Exp $ */ #include "sqliteInt.h" /* ** The number of bits in a Bitmask. "BMS" means "BitMask Size". */ #define BMS (sizeof(Bitmask)*8) |
︙ | ︙ | |||
1513 1514 1515 1516 1517 1518 1519 | double lowestCost; /* Cost of the pBest */ int bestJ = 0; /* The value of j */ Bitmask m; /* Bitmask value for j or bestJ */ int once = 0; /* True when first table is seen */ lowestCost = SQLITE_BIG_DBL; for(j=iFrom, pTabItem=&pTabList->a[j]; j<pTabList->nSrc; j++, pTabItem++){ | > | | | < | < > | 1513 1514 1515 1516 1517 1518 1519 1520 1521 1522 1523 1524 1525 1526 1527 1528 1529 1530 1531 1532 1533 1534 1535 1536 1537 1538 1539 1540 1541 1542 1543 1544 1545 1546 1547 1548 | double lowestCost; /* Cost of the pBest */ int bestJ = 0; /* The value of j */ Bitmask m; /* Bitmask value for j or bestJ */ int once = 0; /* True when first table is seen */ lowestCost = SQLITE_BIG_DBL; for(j=iFrom, pTabItem=&pTabList->a[j]; j<pTabList->nSrc; j++, pTabItem++){ int doNotReorder; /* True if this table should not be reordered */ doNotReorder = (pTabItem->jointype & (JT_LEFT|JT_CROSS))!=0 || (j>0 && (pTabItem[-1].jointype & (JT_LEFT|JT_CROSS))!=0); if( once && doNotReorder ) break; m = getMask(&maskSet, pTabItem->iCursor); if( (m & notReady)==0 ){ if( j==iFrom ) iFrom++; continue; } cost = bestIndex(pParse, &wc, pTabItem, notReady, (i==0 && ppOrderBy) ? *ppOrderBy : 0, &pIdx, &flags, &nEq); if( cost<lowestCost ){ once = 1; lowestCost = cost; pBest = pIdx; bestFlags = flags; bestNEq = nEq; bestJ = j; } if( doNotReorder ) break; } TRACE(("*** Optimizer choose table %d for loop %d\n", bestJ, pLevel-pWInfo->a)); if( (bestFlags & WHERE_ORDERBY)!=0 ){ *ppOrderBy = 0; } andFlags &= bestFlags; |
︙ | ︙ |
Changes to test/where3.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 join reordering optimization # in cases that include a LEFT JOIN. # | | | 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 join reordering optimization # in cases that include a LEFT JOIN. # # $Id: where3.test,v 1.2 2006/06/06 11:45:55 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # The following is from ticket #1652. # # A comma join then a left outer join: A,B left join C. |
︙ | ︙ | |||
41 42 43 44 45 46 47 | INSERT INTO t3 VALUES(999,'nine'); CREATE INDEX t3i1 ON t3(x); SELECT * FROM t1, t2 LEFT JOIN t3 ON q=x WHERE p=2 AND a=q; } } {222 two 2 222 {} {}} | > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 | INSERT INTO t3 VALUES(999,'nine'); CREATE INDEX t3i1 ON t3(x); SELECT * FROM t1, t2 LEFT JOIN t3 ON q=x WHERE p=2 AND a=q; } } {222 two 2 222 {} {}} # Ticket #1830 # # This is similar to the above but with the LEFT JOIN on the # other side. # do_test where3-1.2 { execsql { CREATE TABLE parent1(parent1key, child1key, Child2key, child3key); CREATE TABLE child1 ( child1key NVARCHAR, value NVARCHAR ); CREATE UNIQUE INDEX PKIDXChild1 ON child1 ( child1key ); CREATE TABLE child2 ( child2key NVARCHAR, value NVARCHAR ); INSERT INTO parent1(parent1key,child1key,child2key) VALUES ( 1, 'C1.1', 'C2.1' ); INSERT INTO child1 ( child1key, value ) VALUES ( 'C1.1', 'Value for C1.1' ); INSERT INTO child2 ( child2key, value ) VALUES ( 'C2.1', 'Value for C2.1' ); INSERT INTO parent1 ( parent1key, child1key, child2key ) VALUES ( 2, 'C1.2', 'C2.2' ); INSERT INTO child2 ( child2key, value ) VALUES ( 'C2.2', 'Value for C2.2' ); INSERT INTO parent1 ( parent1key, child1key, child2key ) VALUES ( 3, 'C1.3', 'C2.3' ); INSERT INTO child1 ( child1key, value ) VALUES ( 'C1.3', 'Value for C1.3' ); INSERT INTO child2 ( child2key, value ) VALUES ( 'C2.3', 'Value for C2.3' ); SELECT parent1.parent1key, child1.value, child2.value FROM parent1 LEFT OUTER JOIN child1 ON child1.child1key = parent1.child1key INNER JOIN child2 ON child2.child2key = parent1.child2key; } } {1 {Value for C1.1} {Value for C2.1} 2 {} {Value for C2.2} 3 {Value for C1.3} {Value for C2.3}} finish_test |