Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | If a query contains "FROM t1 LEFT JOIN t2, t3, t4", ensure that tables t3 and t4 are not scanned before t2. The trunk already does this. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | vtab-left-join |
| Files: | files | file ages | folders |
| SHA1: |
0d9edfab9fb61322620f188b48ae2a17 |
| User & Date: | dan 2015-06-08 18:48:29 |
Context
|
2015-06-09
| ||
| 10:58 | Remove some repeated lines of source code. Probably introduced by careless cut'n'pasting. (Closed-Leaf check-in: a34cd71c user: dan tags: vtab-left-join) | |
|
2015-06-08
| ||
| 18:48 | If a query contains "FROM t1 LEFT JOIN t2, t3, t4", ensure that tables t3 and t4 are not scanned before t2. The trunk already does this. (check-in: 0d9edfab user: dan tags: vtab-left-join) | |
| 18:05 | Avoid passing constraints that are unusable due to LEFT or CROSS joins to virtual table xBestIndex() methods. (check-in: 80ee56dd user: dan tags: vtab-left-join) | |
Changes
Changes to src/where.c.
| ︙ | ︙ | |||
2993 2994 2995 2996 2997 2998 2999 3000 3001 3002 3003 3004 3005 3006 3007 3008 3009 3010 |
int iTab;
SrcList *pTabList = pWInfo->pTabList;
struct SrcList_item *pItem;
struct SrcList_item *pEnd = &pTabList->a[pWInfo->nLevel];
sqlite3 *db = pWInfo->pParse->db;
int rc = SQLITE_OK;
WhereLoop *pNew;
pNew = pBuilder->pNew;
whereLoopInit(pNew);
/* Loop over the tables in the join, from left to right */
pNew = pBuilder->pNew;
whereLoopInit(pNew);
for(iTab=0, pItem=pTabList->a; pItem<pEnd; iTab++, pItem++){
Bitmask mUnusable = 0;
pNew->iTab = iTab;
pNew->maskSelf = sqlite3WhereGetMask(&pWInfo->sMaskSet, pItem->iCursor);
| > | > | 2993 2994 2995 2996 2997 2998 2999 3000 3001 3002 3003 3004 3005 3006 3007 3008 3009 3010 3011 3012 3013 3014 3015 3016 3017 3018 3019 3020 3021 3022 3023 3024 |
int iTab;
SrcList *pTabList = pWInfo->pTabList;
struct SrcList_item *pItem;
struct SrcList_item *pEnd = &pTabList->a[pWInfo->nLevel];
sqlite3 *db = pWInfo->pParse->db;
int rc = SQLITE_OK;
WhereLoop *pNew;
u8 priorJointype = 0;
pNew = pBuilder->pNew;
whereLoopInit(pNew);
/* Loop over the tables in the join, from left to right */
pNew = pBuilder->pNew;
whereLoopInit(pNew);
for(iTab=0, pItem=pTabList->a; pItem<pEnd; iTab++, pItem++){
Bitmask mUnusable = 0;
pNew->iTab = iTab;
pNew->maskSelf = sqlite3WhereGetMask(&pWInfo->sMaskSet, pItem->iCursor);
if( ((pItem->jointype|priorJointype) & (JT_LEFT|JT_CROSS))!=0 ){
/* This condition is true when pItem is the FROM clause term on the
** right-hand-side of a LEFT or CROSS JOIN. */
mExtra = mPrior;
}
priorJointype = pItem->jointype;
if( IsVirtual(pItem->pTab) ){
struct SrcList_item *p;
for(p=&pItem[1]; p<pEnd; p++){
if( mUnusable || (p->jointype & (JT_LEFT|JT_CROSS)) ){
mUnusable |= sqlite3WhereGetMask(&pWInfo->sMaskSet, p->iCursor);
}
}
|
| ︙ | ︙ |
Changes to test/join.test.
| ︙ | ︙ | |||
682 683 684 685 686 687 688 689 690 |
if {[lsearch [db eval {PRAGMA compile_options}] MEMDEBUG]<0} {
jointest join-12.10 65534 {1 {at most 64 tables in a join}}
jointest join-12.11 65535 {1 {too many references to "t14": max 65535}}
jointest join-12.12 65536 {1 {too many references to "t14": max 65535}}
jointest join-12.13 65537 {1 {too many references to "t14": max 65535}}
}
}
finish_test
| > > > > > > > > > > > > > > > > > > > > > > > > > > | 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 |
if {[lsearch [db eval {PRAGMA compile_options}] MEMDEBUG]<0} {
jointest join-12.10 65534 {1 {at most 64 tables in a join}}
jointest join-12.11 65535 {1 {too many references to "t14": max 65535}}
jointest join-12.12 65536 {1 {too many references to "t14": max 65535}}
jointest join-12.13 65537 {1 {too many references to "t14": max 65535}}
}
}
#-------------------------------------------------------------------------
# Test a problem with reordering tables following a LEFT JOIN.
#
do_execsql_test join-13.0 {
CREATE TABLE aa(a);
CREATE TABLE bb(b);
CREATE TABLE cc(c);
INSERT INTO aa VALUES(45);
INSERT INTO cc VALUES(45);
INSERT INTO cc VALUES(45);
}
do_execsql_test join-13.1 {
SELECT * FROM aa LEFT JOIN bb, cc WHERE cc.c=aa.a;
} {45 {} 45 45 {} 45}
# In the following, the order of [cc] and [bb] must not be exchanged, even
# though this would be helpful if the query used an inner join.
do_execsql_test join-13.2 {
CREATE INDEX ccc ON cc(c);
SELECT * FROM aa LEFT JOIN bb, cc WHERE cc.c=aa.a;
} {45 {} 45 45 {} 45}
finish_test
|