aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/build.c55
-rw-r--r--src/expr.c44
-rw-r--r--src/insert.c9
-rw-r--r--src/parse.y4
-rw-r--r--src/resolve.c147
-rw-r--r--src/select.c333
-rw-r--r--src/sqliteInt.h45
-rw-r--r--src/treeview.c26
-rw-r--r--src/vdbe.c98
-rw-r--r--src/vdbeInt.h5
-rw-r--r--src/vdbeaux.c2
-rw-r--r--src/where.c136
-rw-r--r--src/whereInt.h17
-rw-r--r--src/wherecode.c160
-rw-r--r--src/whereexpr.c8
15 files changed, 771 insertions, 318 deletions
diff --git a/src/build.c b/src/build.c
index da862fa70..1c632c94e 100644
--- a/src/build.c
+++ b/src/build.c
@@ -4672,18 +4672,17 @@ IdList *sqlite3IdListAppend(Parse *pParse, IdList *pList, Token *pToken){
if( pList==0 ){
pList = sqlite3DbMallocZero(db, sizeof(IdList) );
if( pList==0 ) return 0;
+ }else{
+ IdList *pNew;
+ pNew = sqlite3DbRealloc(db, pList,
+ sizeof(IdList) + pList->nId*sizeof(pList->a));
+ if( pNew==0 ){
+ sqlite3IdListDelete(db, pList);
+ return 0;
+ }
+ pList = pNew;
}
- pList->a = sqlite3ArrayAllocate(
- db,
- pList->a,
- sizeof(pList->a[0]),
- &pList->nId,
- &i
- );
- if( i<0 ){
- sqlite3IdListDelete(db, pList);
- return 0;
- }
+ i = pList->nId++;
pList->a[i].zName = sqlite3NameFromToken(db, pToken);
if( IN_RENAME_OBJECT && pList->a[i].zName ){
sqlite3RenameTokenMap(pParse, (void*)pList->a[i].zName, pToken);
@@ -4697,10 +4696,10 @@ IdList *sqlite3IdListAppend(Parse *pParse, IdList *pList, Token *pToken){
void sqlite3IdListDelete(sqlite3 *db, IdList *pList){
int i;
if( pList==0 ) return;
+ assert( pList->eU4!=EU4_EXPR ); /* EU4_EXPR mode is not currently used */
for(i=0; i<pList->nId; i++){
sqlite3DbFree(db, pList->a[i].zName);
}
- sqlite3DbFree(db, pList->a);
sqlite3DbFreeNN(db, pList);
}
@@ -5031,6 +5030,7 @@ SrcList *sqlite3SrcListAppendList(Parse *pParse, SrcList *p1, SrcList *p2){
p1 = pNew;
memcpy(&p1->a[1], p2->a, p2->nSrc*sizeof(SrcItem));
sqlite3DbFree(pParse->db, p2);
+ p1->a[0].fg.jointype |= (JT_LTORJ & p1->a[1].fg.jointype);
}
}
return p1;
@@ -5067,14 +5067,33 @@ void sqlite3SrcListFuncArgs(Parse *pParse, SrcList *p, ExprList *pList){
** The operator is "natural cross join". The A and B operands are stored
** in p->a[0] and p->a[1], respectively. The parser initially stores the
** operator with A. This routine shifts that operator over to B.
+**
+** Additional changes:
+**
+** * All tables to the left of the right-most RIGHT JOIN are tagged with
+** JT_LTORJ (mnemonic: Left Table Of Right Join) so that the
+** code generator can easily tell that the table is part of
+** the left operand of at least one RIGHT JOIN.
*/
-void sqlite3SrcListShiftJoinType(SrcList *p){
- if( p ){
- int i;
- for(i=p->nSrc-1; i>0; i--){
- p->a[i].fg.jointype = p->a[i-1].fg.jointype;
- }
+void sqlite3SrcListShiftJoinType(Parse *pParse, SrcList *p){
+ if( p && p->nSrc>1 ){
+ int i = p->nSrc-1;
+ u8 allFlags = 0;
+ do{
+ allFlags |= p->a[i].fg.jointype = p->a[i-1].fg.jointype;
+ }while( (--i)>0 );
p->a[0].fg.jointype = 0;
+
+ /* All terms to the left of a RIGHT JOIN should be tagged with the
+ ** JT_LTORJ flags */
+ if( allFlags & JT_RIGHT ){
+ for(i=p->nSrc-1; ALWAYS(i>0) && (p->a[i].fg.jointype&JT_RIGHT)==0; i--){}
+ i--;
+ assert( i>=0 );
+ do{
+ p->a[i].fg.jointype |= JT_LTORJ;
+ }while( (--i)>=0 );
+ }
}
}
diff --git a/src/expr.c b/src/expr.c
index f5916c53e..eb3ba35ba 100644
--- a/src/expr.c
+++ b/src/expr.c
@@ -1698,22 +1698,16 @@ IdList *sqlite3IdListDup(sqlite3 *db, const IdList *p){
int i;
assert( db!=0 );
if( p==0 ) return 0;
- pNew = sqlite3DbMallocRawNN(db, sizeof(*pNew) );
+ assert( p->eU4!=EU4_EXPR );
+ pNew = sqlite3DbMallocRawNN(db, sizeof(*pNew)+(p->nId-1)*sizeof(p->a[0]) );
if( pNew==0 ) return 0;
pNew->nId = p->nId;
- pNew->a = sqlite3DbMallocRawNN(db, p->nId*sizeof(p->a[0]) );
- if( pNew->a==0 ){
- sqlite3DbFreeNN(db, pNew);
- return 0;
- }
- /* Note that because the size of the allocation for p->a[] is not
- ** necessarily a power of two, sqlite3IdListAppend() may not be called
- ** on the duplicate created by this function. */
+ pNew->eU4 = p->eU4;
for(i=0; i<p->nId; i++){
struct IdList_item *pNewItem = &pNew->a[i];
- struct IdList_item *pOldItem = &p->a[i];
+ const struct IdList_item *pOldItem = &p->a[i];
pNewItem->zName = sqlite3DbStrDup(db, pOldItem->zName);
- pNewItem->idx = pOldItem->idx;
+ pNewItem->u4 = pOldItem->u4;
}
return pNew;
}
@@ -2179,7 +2173,7 @@ Expr *sqlite3ExprSimplifiedAndOr(Expr *pExpr){
static int exprNodeIsConstant(Walker *pWalker, Expr *pExpr){
/* If pWalker->eCode is 2 then any term of the expression that comes from
- ** the ON or USING clauses of a left join disqualifies the expression
+ ** the ON or USING clauses of an outer join disqualifies the expression
** from being considered constant. */
if( pWalker->eCode==2 && ExprHasProperty(pExpr, EP_FromJoin) ){
pWalker->eCode = 0;
@@ -3181,9 +3175,9 @@ void sqlite3CodeRhsOfIN(
assert( ExprUseYSub(pExpr) );
assert( sqlite3VdbeGetOp(v,pExpr->y.sub.iAddr-1)->opcode==OP_BeginSubrtn
|| pParse->nErr );
- sqlite3VdbeAddOp2(v, OP_Return, pExpr->y.sub.regReturn,
- pExpr->y.sub.iAddr);
- sqlite3VdbeChangeP1(v, pExpr->y.sub.iAddr-1, sqlite3VdbeCurrentAddr(v)-1);
+ sqlite3VdbeAddOp3(v, OP_Return, pExpr->y.sub.regReturn,
+ pExpr->y.sub.iAddr, 1);
+ VdbeCoverage(v);
sqlite3ClearTempRegCache(pParse);
}
}
@@ -3312,9 +3306,9 @@ int sqlite3CodeSubselect(Parse *pParse, Expr *pExpr){
assert( ExprUseYSub(pExpr) );
assert( sqlite3VdbeGetOp(v,pExpr->y.sub.iAddr-1)->opcode==OP_BeginSubrtn
|| pParse->nErr );
- sqlite3VdbeAddOp2(v, OP_Return, pExpr->y.sub.regReturn,
- pExpr->y.sub.iAddr);
- sqlite3VdbeChangeP1(v, pExpr->y.sub.iAddr-1, sqlite3VdbeCurrentAddr(v)-1);
+ sqlite3VdbeAddOp3(v, OP_Return, pExpr->y.sub.regReturn,
+ pExpr->y.sub.iAddr, 1);
+ VdbeCoverage(v);
sqlite3ClearTempRegCache(pParse);
return rReg;
}
@@ -4501,16 +4495,18 @@ expr_code_doover:
}
case TK_SELECT_COLUMN: {
int n;
- if( pExpr->pLeft->iTable==0 ){
- pExpr->pLeft->iTable = sqlite3CodeSubselect(pParse, pExpr->pLeft);
+ Expr *pLeft = pExpr->pLeft;
+ if( pLeft->iTable==0 || pParse->withinRJSubrtn > pLeft->op2 ){
+ pLeft->iTable = sqlite3CodeSubselect(pParse, pLeft);
+ pLeft->op2 = pParse->withinRJSubrtn;
}
- assert( pExpr->pLeft->op==TK_SELECT || pExpr->pLeft->op==TK_ERROR );
- n = sqlite3ExprVectorSize(pExpr->pLeft);
+ assert( pLeft->op==TK_SELECT || pLeft->op==TK_ERROR );
+ n = sqlite3ExprVectorSize(pLeft);
if( pExpr->iTable!=n ){
sqlite3ErrorMsg(pParse, "%d columns assigned %d values",
pExpr->iTable, n);
}
- return pExpr->pLeft->iTable + pExpr->iColumn;
+ return pLeft->iTable + pExpr->iColumn;
}
case TK_IN: {
int destIfFalse = sqlite3VdbeMakeLabel(pParse);
@@ -5818,7 +5814,7 @@ static int impliesNotNullRow(Walker *pWalker, Expr *pExpr){
** in an incorrect answer.
**
** Terms of p that are marked with EP_FromJoin (and hence that come from
-** the ON or USING clauses of LEFT JOINS) are excluded from the analysis.
+** the ON or USING clauses of OUTER JOINS) are excluded from the analysis.
**
** This routine is used to check if a LEFT JOIN can be converted into
** an ordinary JOIN. The p argument is the WHERE clause. If the WHERE
diff --git a/src/insert.c b/src/insert.c
index 2593f353b..9b97b99a3 100644
--- a/src/insert.c
+++ b/src/insert.c
@@ -851,13 +851,15 @@ void sqlite3Insert(
*/
bIdListInOrder = (pTab->tabFlags & (TF_OOOHidden|TF_HasStored))==0;
if( pColumn ){
+ assert( pColumn->eU4!=EU4_EXPR );
+ pColumn->eU4 = EU4_IDX;
for(i=0; i<pColumn->nId; i++){
- pColumn->a[i].idx = -1;
+ pColumn->a[i].u4.idx = -1;
}
for(i=0; i<pColumn->nId; i++){
for(j=0; j<pTab->nCol; j++){
if( sqlite3StrICmp(pColumn->a[i].zName, pTab->aCol[j].zCnName)==0 ){
- pColumn->a[i].idx = j;
+ pColumn->a[i].u4.idx = j;
if( i!=j ) bIdListInOrder = 0;
if( j==pTab->iPKey ){
ipkColumn = i; assert( !withoutRowid );
@@ -1159,7 +1161,8 @@ void sqlite3Insert(
}
}
if( pColumn ){
- for(j=0; j<pColumn->nId && pColumn->a[j].idx!=i; j++){}
+ assert( pColumn->eU4==EU4_IDX );
+ for(j=0; j<pColumn->nId && pColumn->a[j].u4.idx!=i; j++){}
if( j>=pColumn->nId ){
/* A column not named in the insert column list gets its
** default value */
diff --git a/src/parse.y b/src/parse.y
index 4deae14b4..65977bbba 100644
--- a/src/parse.y
+++ b/src/parse.y
@@ -685,7 +685,7 @@ as(X) ::= . {X.n = 0; X.z = 0;}
from(A) ::= . {A = 0;}
from(A) ::= FROM seltablist(X). {
A = X;
- sqlite3SrcListShiftJoinType(A);
+ sqlite3SrcListShiftJoinType(pParse,A);
}
// "seltablist" is a "Select Table List" - the content of the FROM clause
@@ -733,7 +733,7 @@ seltablist(A) ::= stl_prefix(A) nm(Y) dbnm(D) LP exprlist(E) RP as(Z) on_using(N
sqlite3SrcListDelete(pParse->db, F);
}else{
Select *pSubquery;
- sqlite3SrcListShiftJoinType(F);
+ sqlite3SrcListShiftJoinType(pParse,F);
pSubquery = sqlite3SelectNew(pParse,0,F,0,0,0,0,SF_NestedFrom,0);
A = sqlite3SrcListAppendFromTerm(pParse,A,0,0,&Z,pSubquery,&N);
}
diff --git a/src/resolve.c b/src/resolve.c
index 30785ca70..4d5e23dff 100644
--- a/src/resolve.c
+++ b/src/resolve.c
@@ -115,22 +115,6 @@ static void resolveAlias(
}
}
-
-/*
-** Return TRUE if the name zCol occurs anywhere in the USING clause.
-**
-** Return FALSE if the USING clause is NULL or if it does not contain
-** zCol.
-*/
-static int nameInUsingClause(IdList *pUsing, const char *zCol){
- int k;
- assert( pUsing!=0 );
- for(k=0; k<pUsing->nId; k++){
- if( sqlite3StrICmp(pUsing->a[k].zName, zCol)==0 ) return 1;
- }
- return 0;
-}
-
/*
** Subqueries stores the original database, table and column names for their
** result sets in ExprList.a[].zSpan, in the form "DATABASE.TABLE.COLUMN".
@@ -208,6 +192,29 @@ Bitmask sqlite3ExprColUsed(Expr *pExpr){
}
/*
+** Create a new expression term for the column specified by pMatch and
+** iColumn. Append this new expression term to the FULL JOIN Match set
+** in *ppList. Create a new *ppList if this is the first term in the
+** set.
+*/
+static void extendFJMatch(
+ Parse *pParse, /* Parsing context */
+ ExprList **ppList, /* ExprList to extend */
+ SrcItem *pMatch, /* Source table containing the column */
+ i16 iColumn /* The column number */
+){
+ Expr *pNew = sqlite3ExprAlloc(pParse->db, TK_COLUMN, 0, 0);
+ if( pNew ){
+ pNew->iTable = pMatch->iCursor;
+ pNew->iColumn = iColumn;
+ pNew->y.pTab = pMatch->pTab;
+ assert( (pMatch->fg.jointype & (JT_LEFT|JT_LTORJ))!=0 );
+ ExprSetProperty(pNew, EP_CanBeNull);
+ *ppList = sqlite3ExprListAppend(pParse, *ppList, pNew);
+ }
+}
+
+/*
** Given the name of a column of the form X.Y.Z or Y.Z or just Z, look up
** that name in the set of source tables in pSrcList and make the pExpr
** expression node refer back to that source column. The following changes
@@ -252,8 +259,9 @@ static int lookupName(
NameContext *pTopNC = pNC; /* First namecontext in the list */
Schema *pSchema = 0; /* Schema of the expression */
int eNewExprOp = TK_COLUMN; /* New value for pExpr->op on success */
- Table *pTab = 0; /* Table hold the row */
+ Table *pTab = 0; /* Table holding the row */
Column *pCol; /* A column of pTab */
+ ExprList *pFJMatch = 0; /* Matches for FULL JOIN .. USING */
assert( pNC ); /* the name context cannot be NULL. */
assert( zCol ); /* The Z in X.Y.Z cannot be NULL */
@@ -310,6 +318,30 @@ static int lookupName(
pEList = pItem->pSelect->pEList;
for(j=0; j<pEList->nExpr; j++){
if( sqlite3MatchEName(&pEList->a[j], zCol, zTab, zDb) ){
+ if( cnt>0 ){
+ if( pItem->fg.isUsing==0
+ || sqlite3IdListIndex(pItem->u3.pUsing, zCol)<0
+ ){
+ /* Two or more tables have the same column name which is
+ ** not joined by USING. This is an error. Signal as much
+ ** by clearing pFJMatch and letting cnt go above 1. */
+ sqlite3ExprListDelete(db, pFJMatch);
+ pFJMatch = 0;
+ }else
+ if( (pItem->fg.jointype & JT_RIGHT)==0 ){
+ /* An INNER or LEFT JOIN. Use the left-most table */
+ continue;
+ }else
+ if( (pItem->fg.jointype & JT_LEFT)==0 ){
+ /* A RIGHT JOIN. Use the right-most table */
+ cnt = 0;
+ sqlite3ExprListDelete(db, pFJMatch);
+ pFJMatch = 0;
+ }else{
+ /* For a FULL JOIN, we must construct a coalesce() func */
+ extendFJMatch(pParse, &pFJMatch, pMatch, pExpr->iColumn);
+ }
+ }
cnt++;
cntTab = 2;
pMatch = pItem;
@@ -339,16 +371,28 @@ static int lookupName(
if( pCol->hName==hCol
&& sqlite3StrICmp(pCol->zCnName, zCol)==0
){
- /* If there has been exactly one prior match and this match
- ** is for the right-hand table of a NATURAL JOIN or is in a
- ** USING clause, then skip this match.
- */
- if( cnt==1 ){
- if( pItem->fg.jointype & JT_NATURAL ) continue;
- if( pItem->fg.isUsing
- && nameInUsingClause(pItem->u3.pUsing, zCol)
+ if( cnt>0 ){
+ if( pItem->fg.isUsing==0
+ || sqlite3IdListIndex(pItem->u3.pUsing, zCol)<0
){
+ /* Two or more tables have the same column name which is
+ ** not joined by USING. This is an error. Signal as much
+ ** by clearing pFJMatch and letting cnt go above 1. */
+ sqlite3ExprListDelete(db, pFJMatch);
+ pFJMatch = 0;
+ }else
+ if( (pItem->fg.jointype & JT_RIGHT)==0 ){
+ /* An INNER or LEFT JOIN. Use the left-most table */
continue;
+ }else
+ if( (pItem->fg.jointype & JT_LEFT)==0 ){
+ /* A RIGHT JOIN. Use the right-most table */
+ cnt = 0;
+ sqlite3ExprListDelete(db, pFJMatch);
+ pFJMatch = 0;
+ }else{
+ /* For a FULL JOIN, we must construct a coalesce() func */
+ extendFJMatch(pParse, &pFJMatch, pMatch, pExpr->iColumn);
}
}
cnt++;
@@ -367,9 +411,7 @@ static int lookupName(
pExpr->iTable = pMatch->iCursor;
assert( ExprUseYTab(pExpr) );
pExpr->y.pTab = pMatch->pTab;
- /* RIGHT JOIN not (yet) supported */
- assert( (pMatch->fg.jointype & JT_RIGHT)==0 );
- if( (pMatch->fg.jointype & JT_LEFT)!=0 ){
+ if( (pMatch->fg.jointype & (JT_LEFT|JT_LTORJ))!=0 ){
ExprSetProperty(pExpr, EP_CanBeNull);
}
pSchema = pExpr->y.pTab->pSchema;
@@ -610,11 +652,37 @@ static int lookupName(
}
/*
- ** cnt==0 means there was not match. cnt>1 means there were two or
- ** more matches. Either way, we have an error.
+ ** cnt==0 means there was not match.
+ ** cnt>1 means there were two or more matches.
+ **
+ ** cnt==0 is always an error. cnt>1 is often an error, but might
+ ** be multiple matches for a NATURAL LEFT JOIN or a LEFT JOIN USING.
*/
+ assert( pFJMatch==0 || cnt>0 );
+ assert( !ExprHasProperty(pExpr, EP_xIsSelect|EP_IntValue) );
if( cnt!=1 ){
const char *zErr;
+ if( pFJMatch ){
+ if( pFJMatch->nExpr==cnt-1 ){
+ if( ExprHasProperty(pExpr,EP_Leaf) ){
+ ExprClearProperty(pExpr,EP_Leaf);
+ }else{
+ sqlite3ExprDelete(db, pExpr->pLeft);
+ pExpr->pLeft = 0;
+ sqlite3ExprDelete(db, pExpr->pRight);
+ pExpr->pRight = 0;
+ }
+ extendFJMatch(pParse, &pFJMatch, pMatch, pExpr->iColumn);
+ pExpr->op = TK_FUNCTION;
+ pExpr->u.zToken = "coalesce";
+ pExpr->x.pList = pFJMatch;
+ cnt = 1;
+ goto lookupname_end;
+ }else{
+ sqlite3ExprListDelete(db, pFJMatch);
+ pFJMatch = 0;
+ }
+ }
zErr = cnt==0 ? "no such column" : "ambiguous column name";
if( zDb ){
sqlite3ErrorMsg(pParse, "%s: %s.%s.%s", zErr, zDb, zTab, zCol);
@@ -627,6 +695,16 @@ static int lookupName(
pParse->checkSchema = 1;
pTopNC->nNcErr++;
}
+ assert( pFJMatch==0 );
+
+ /* Remove all substructure from pExpr */
+ if( !ExprHasProperty(pExpr,(EP_TokenOnly|EP_Leaf)) ){
+ sqlite3ExprDelete(db, pExpr->pLeft);
+ pExpr->pLeft = 0;
+ sqlite3ExprDelete(db, pExpr->pRight);
+ pExpr->pRight = 0;
+ ExprSetProperty(pExpr, EP_Leaf);
+ }
/* If a column from a table in pSrcList is referenced, then record
** this fact in the pSrcList.a[].colUsed bitmask. Column 0 causes
@@ -646,16 +724,7 @@ static int lookupName(
pMatch->colUsed |= sqlite3ExprColUsed(pExpr);
}
- /* Clean up and return
- */
- if( !ExprHasProperty(pExpr,(EP_TokenOnly|EP_Leaf)) ){
- sqlite3ExprDelete(db, pExpr->pLeft);
- pExpr->pLeft = 0;
- sqlite3ExprDelete(db, pExpr->pRight);
- pExpr->pRight = 0;
- }
pExpr->op = eNewExprOp;
- ExprSetProperty(pExpr, EP_Leaf);
lookupname_end:
if( cnt==1 ){
assert( pNC!=0 );
diff --git a/src/select.c b/src/select.c
index bc6a0b1d1..65ca283b3 100644
--- a/src/select.c
+++ b/src/select.c
@@ -298,13 +298,9 @@ int sqlite3JoinType(Parse *pParse, Token *pA, Token *pB, Token *pC){
const char *zSp2 = " ";
if( pB==0 ){ zSp1++; }
if( pC==0 ){ zSp2++; }
- sqlite3ErrorMsg(pParse, "unknown or unsupported join type: "
+ sqlite3ErrorMsg(pParse, "unknown join type: "
"%T%s%T%s%T", pA, zSp1, pB, zSp2, pC);
jointype = JT_INNER;
- }else if( (jointype & JT_RIGHT)!=0 ){
- sqlite3ErrorMsg(pParse,
- "RIGHT and FULL OUTER JOINs are not currently supported");
- jointype = JT_INNER;
}
return jointype;
}
@@ -324,8 +320,9 @@ int sqlite3ColumnIndex(Table *pTab, const char *zCol){
}
/*
-** Search the first N tables in pSrc, from left to right, looking for a
-** table that has a column named zCol.
+** Search the tables iStart..iEnd (inclusive) in pSrc, looking for a
+** table that has a column named zCol. The search is left-to-right.
+** The first match found is returned.
**
** When found, set *piTab and *piCol to the table index and column index
** of the matching column and return TRUE.
@@ -334,17 +331,21 @@ int sqlite3ColumnIndex(Table *pTab, const char *zCol){
*/
static int tableAndColumnIndex(
SrcList *pSrc, /* Array of tables to search */
- int N, /* Number of tables in pSrc->a[] to search */
+ int iStart, /* First member of pSrc->a[] to check */
+ int iEnd, /* Last member of pSrc->a[] to check */
const char *zCol, /* Name of the column we are looking for */
int *piTab, /* Write index of pSrc->a[] here */
int *piCol, /* Write index of pSrc->a[*piTab].pTab->aCol[] here */
- int bIgnoreHidden /* True to ignore hidden columns */
+ int bIgnoreHidden /* Ignore hidden columns */
){
int i; /* For looping over tables in pSrc */
int iCol; /* Index of column matching zCol */
+ assert( iEnd<pSrc->nSrc );
+ assert( iStart>=0 );
assert( (piTab==0)==(piCol==0) ); /* Both or neither are NULL */
- for(i=0; i<N; i++){
+
+ for(i=iStart; i<=iEnd; i++){
iCol = sqlite3ColumnIndex(pSrc->a[i].pTab, zCol);
if( iCol>=0
&& (bIgnoreHidden==0 || IsHiddenColumn(&pSrc->a[i].pTab->aCol[iCol])==0)
@@ -360,59 +361,12 @@ static int tableAndColumnIndex(
}
/*
-** This function is used to add terms implied by JOIN syntax to the
-** WHERE clause expression of a SELECT statement. The new term, which
-** is ANDed with the existing WHERE clause, is of the form:
-**
-** (tab1.col1 = tab2.col2)
-**
-** where tab1 is the iSrc'th table in SrcList pSrc and tab2 is the
-** (iSrc+1)'th. Column col1 is column iColLeft of tab1, and col2 is
-** column iColRight of tab2.
-*/
-static void addWhereTerm(
- Parse *pParse, /* Parsing context */
- SrcList *pSrc, /* List of tables in FROM clause */
- int iLeft, /* Index of first table to join in pSrc */
- int iColLeft, /* Index of column in first table */
- int iRight, /* Index of second table in pSrc */
- int iColRight, /* Index of column in second table */
- int isOuterJoin, /* True if this is an OUTER join */
- Expr **ppWhere /* IN/OUT: The WHERE clause to add to */
-){
- sqlite3 *db = pParse->db;
- Expr *pE1;
- Expr *pE2;
- Expr *pEq;
-
- assert( iLeft<iRight );
- assert( pSrc->nSrc>iRight );
- assert( pSrc->a[iLeft].pTab );
- assert( pSrc->a[iRight].pTab );
-
- pE1 = sqlite3CreateColumnExpr(db, pSrc, iLeft, iColLeft);
- pE2 = sqlite3CreateColumnExpr(db, pSrc, iRight, iColRight);
-
- pEq = sqlite3PExpr(pParse, TK_EQ, pE1, pE2);
- assert( pE2!=0 || pEq==0 ); /* Due to db->mallocFailed test
- ** in sqlite3DbMallocRawNN() called from
- ** sqlite3PExpr(). */
- if( pEq && isOuterJoin ){
- ExprSetProperty(pEq, EP_FromJoin);
- assert( !ExprHasProperty(pEq, EP_TokenOnly|EP_Reduced) );
- ExprSetVVAProperty(pEq, EP_NoReduce);
- pEq->w.iJoin = pE2->iTable;
- }
- *ppWhere = sqlite3ExprAnd(pParse, *ppWhere, pEq);
-}
-
-/*
** Set the EP_FromJoin property on all terms of the given expression.
** And set the Expr.w.iJoin to iTable for every term in the
** expression.
**
** The EP_FromJoin property is used on terms of an expression to tell
-** the LEFT OUTER JOIN processing logic that this term is part of the
+** the OUTER JOIN processing logic that this term is part of the
** join restriction specified in the ON or USING clause and not a part
** of the more general WHERE clause. These terms are moved over to the
** WHERE clause during join processing but we need to remember that they
@@ -432,9 +386,10 @@ static void addWhereTerm(
** after the t1 loop and rows with t1.x!=5 will never appear in
** the output, which is incorrect.
*/
-void sqlite3SetJoinExpr(Expr *p, int iTable){
+void sqlite3SetJoinExpr(Expr *p, int iTable, u32 joinFlag){
+ assert( joinFlag==EP_FromJoin || joinFlag==EP_InnerJoin );
while( p ){
- ExprSetProperty(p, EP_FromJoin);
+ ExprSetProperty(p, joinFlag);
assert( !ExprHasProperty(p, EP_TokenOnly|EP_Reduced) );
ExprSetVVAProperty(p, EP_NoReduce);
p->w.iJoin = iTable;
@@ -443,11 +398,11 @@ void sqlite3SetJoinExpr(Expr *p, int iTable){
if( p->x.pList ){
int i;
for(i=0; i<p->x.pList->nExpr; i++){
- sqlite3SetJoinExpr(p->x.pList->a[i].pExpr, iTable);
+ sqlite3SetJoinExpr(p->x.pList->a[i].pExpr, iTable, joinFlag);
}
}
}
- sqlite3SetJoinExpr(p->pLeft, iTable);
+ sqlite3SetJoinExpr(p->pLeft, iTable, joinFlag);
p = p->pRight;
}
}
@@ -463,6 +418,7 @@ static void unsetJoinExpr(Expr *p, int iTable){
if( ExprHasProperty(p, EP_FromJoin)
&& (iTable<0 || p->w.iJoin==iTable) ){
ExprClearProperty(p, EP_FromJoin);
+ ExprSetProperty(p, EP_InnerJoin);
}
if( p->op==TK_COLUMN && p->iTable==iTable ){
ExprClearProperty(p, EP_CanBeNull);
@@ -495,7 +451,7 @@ static void unsetJoinExpr(Expr *p, int iTable){
**
** This routine returns the number of errors encountered.
*/
-static int sqliteProcessJoin(Parse *pParse, Select *p){
+static int sqlite3ProcessJoin(Parse *pParse, Select *p){
SrcList *pSrc; /* All tables in the FROM clause */
int i, j; /* Loop counters */
SrcItem *pLeft; /* Left table being joined */
@@ -506,15 +462,16 @@ static int sqliteProcessJoin(Parse *pParse, Select *p){
pRight = &pLeft[1];
for(i=0; i<pSrc->nSrc-1; i++, pRight++, pLeft++){
Table *pRightTab = pRight->pTab;
- int isOuter;
+ u32 joinType;
if( NEVER(pLeft->pTab==0 || pRightTab==0) ) continue;
- isOuter = (pRight->fg.jointype & JT_OUTER)!=0;
+ joinType = (pRight->fg.jointype & JT_OUTER)!=0 ? EP_FromJoin : EP_InnerJoin;
- /* When the NATURAL keyword is present, add WHERE clause terms for
- ** every column that the two tables have in common.
+ /* If this is a NATURAL join, synthesize an approprate USING clause
+ ** to specify which columns should be joined.
*/
if( pRight->fg.jointype & JT_NATURAL ){
+ IdList *pUsing = 0;
if( pRight->fg.isUsing || pRight->u3.pOn ){
sqlite3ErrorMsg(pParse, "a NATURAL join may not have "
"an ON or USING clause", 0);
@@ -522,16 +479,24 @@ static int sqliteProcessJoin(Parse *pParse, Select *p){
}
for(j=0; j<pRightTab->nCol; j++){
char *zName; /* Name of column in the right table */
- int iLeft; /* Matching left table */
- int iLeftCol; /* Matching column in the left table */
if( IsHiddenColumn(&pRightTab->aCol[j]) ) continue;
zName = pRightTab->aCol[j].zCnName;
- if( tableAndColumnIndex(pSrc, i+1, zName, &iLeft, &iLeftCol, 1) ){
- addWhereTerm(pParse, pSrc, iLeft, iLeftCol, i+1, j,
- isOuter, &p->pWhere);
+ if( tableAndColumnIndex(pSrc, 0, i, zName, 0, 0, 1) ){
+ pUsing = sqlite3IdListAppend(pParse, pUsing, 0);
+ if( pUsing ){
+ assert( pUsing->nId>0 );
+ assert( pUsing->a[pUsing->nId-1].zName==0 );
+ pUsing->a[pUsing->nId-1].zName = sqlite3DbStrDup(pParse->db, zName);
+ }
}
}
+ if( pUsing ){
+ pRight->fg.isUsing = 1;
+ pRight->fg.isSynthUsing = 1;
+ pRight->u3.pUsing = pUsing;
+ }
+ if( pParse->nErr ) return 1;
}
/* Create extra terms on the WHERE clause for each column named
@@ -543,24 +508,71 @@ static int sqliteProcessJoin(Parse *pParse, Select *p){
*/
if( pRight->fg.isUsing ){
IdList *pList = pRight->u3.pUsing;
+ sqlite3 *db = pParse->db;
assert( pList!=0 );
for(j=0; j<pList->nId; j++){
char *zName; /* Name of the term in the USING clause */
int iLeft; /* Table on the left with matching column name */
int iLeftCol; /* Column number of matching column on the left */
int iRightCol; /* Column number of matching column on the right */
+ Expr *pE1; /* Reference to the column on the LEFT of the join */
+ Expr *pE2; /* Reference to the column on the RIGHT of the join */
+ Expr *pEq; /* Equality constraint. pE1 == pE2 */
zName = pList->a[j].zName;
iRightCol = sqlite3ColumnIndex(pRightTab, zName);
if( iRightCol<0
- || !tableAndColumnIndex(pSrc, i+1, zName, &iLeft, &iLeftCol, 0)
+ || tableAndColumnIndex(pSrc, 0, i, zName, &iLeft, &iLeftCol,
+ pRight->fg.isSynthUsing)==0
){
sqlite3ErrorMsg(pParse, "cannot join using column %s - column "
"not present in both tables", zName);
return 1;
}
- addWhereTerm(pParse, pSrc, iLeft, iLeftCol, i+1, iRightCol,
- isOuter, &p->pWhere);
+ pE1 = sqlite3CreateColumnExpr(db, pSrc, iLeft, iLeftCol);
+ if( (pSrc->a[0].fg.jointype & JT_LTORJ)!=0 ){
+ /* This branch runs if the query contains one or more RIGHT or FULL
+ ** JOINs. If only a single table on the left side of this join
+ ** contains the zName column, then this routine is branch is
+ ** a no-op. But if there are two or more tables on the left side
+ ** of the join, construct a coalesce() function that gathers all
+ ** such tables. Raise an error if more than one of those references
+ ** to zName is not also within a prior USING clause.
+ **
+ ** We really ought to raise an error if there are two or more
+ ** non-USING references to zName on the left of an INNER or LEFT
+ ** JOIN. But older versions of SQLite do not do that, so we avoid
+ ** adding a new error so as to not break legacy applications.
+ */
+ ExprList *pFuncArgs = 0; /* Arguments to the coalesce() */
+ static const Token tkCoalesce = { "coalesce", 8 };
+ while( tableAndColumnIndex(pSrc, iLeft+1, i, zName, &iLeft, &iLeftCol,
+ pRight->fg.isSynthUsing)!=0 ){
+ if( pSrc->a[iLeft].fg.isUsing==0
+ || sqlite3IdListIndex(pSrc->a[iLeft].u3.pUsing, zName)<0
+ ){
+ sqlite3ErrorMsg(pParse, "ambiguous reference to %s in USING()",
+ zName);
+ break;
+ }
+ pFuncArgs = sqlite3ExprListAppend(pParse, pFuncArgs, pE1);
+ pE1 = sqlite3CreateColumnExpr(db, pSrc, iLeft, iLeftCol);
+ }
+ if( pFuncArgs ){
+ pFuncArgs = sqlite3ExprListAppend(pParse, pFuncArgs, pE1);
+ pE1 = sqlite3ExprFunction(pParse, pFuncArgs, &tkCoalesce, 0);
+ }
+ }
+ pE2 = sqlite3CreateColumnExpr(db, pSrc, i+1, iRightCol);
+ pEq = sqlite3PExpr(pParse, TK_EQ, pE1, pE2);
+ assert( pE2!=0 || pEq==0 );
+ if( pEq ){
+ ExprSetProperty(pEq, joinType);
+ assert( !ExprHasProperty(pEq, EP_TokenOnly|EP_Reduced) );
+ ExprSetVVAProperty(pEq, EP_NoReduce);
+ pEq->w.iJoin = pE2->iTable;
+ }
+ p->pWhere = sqlite3ExprAnd(pParse, p->pWhere, pEq);
}
}
@@ -568,7 +580,7 @@ static int sqliteProcessJoin(Parse *pParse, Select *p){
** an AND operator.
*/
else if( pRight->u3.pOn ){
- if( isOuter ) sqlite3SetJoinExpr(pRight->u3.pOn, pRight->iCursor);
+ sqlite3SetJoinExpr(pRight->u3.pOn, pRight->iCursor, joinType);
p->pWhere = sqlite3ExprAnd(pParse, p->pWhere, pRight->u3.pOn);
pRight->u3.pOn = 0;
}
@@ -3656,12 +3668,40 @@ static int multiSelectOrderBy(
**
** All references to columns in table iTable are to be replaced by corresponding
** expressions in pEList.
+**
+** ## About "isOuterJoin":
+**
+** The isOuterJoin column indicates that the replacement will occur into a
+** position in the parent that NULL-able due to an OUTER JOIN. Either the
+** target slot in the parent is the right operand of a LEFT JOIN, or one of
+** the left operands of a RIGHT JOIN. In either case, we need to potentially
+** bypass the substituted expression with OP_IfNullRow.
+**
+** Suppose the original expression integer constant. Even though the table
+** has the nullRow flag set, because the expression is an integer constant,
+** it will not be NULLed out. So instead, we insert an OP_IfNullRow opcode
+** that checks to see if the nullRow flag is set on the table. If the nullRow
+** flag is set, then the value in the register is set to NULL and the original
+** expression is bypassed. If the nullRow flag is not set, then the original
+** expression runs to populate the register.
+**
+** Example where this is needed:
+**
+** CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT);
+** CREATE TABLE t2(x INT UNIQUE);
+**
+** SELECT a,b,m,x FROM t1 LEFT JOIN (SELECT 59 AS m,x FROM t2) ON b=x;
+**
+** When the subquery on the right side of the LEFT JOIN is flattened, we
+** have to add OP_IfNullRow in front of the OP_Integer that implements the
+** "m" value of the subquery so that a NULL will be loaded instead of 59
+** when processing a non-matched row of the left.
*/
typedef struct SubstContext {
Parse *pParse; /* The parsing context */
int iTable; /* Replace references to this table */
int iNewTable; /* New table number */
- int isLeftJoin; /* Add TK_IF_NULL_ROW opcodes on each replacement */
+ int isOuterJoin; /* Add TK_IF_NULL_ROW opcodes on each replacement */
ExprList *pEList; /* Replacement expressions */
} SubstContext;
@@ -3711,7 +3751,7 @@ static Expr *substExpr(
sqlite3VectorErrorMsg(pSubst->pParse, pCopy);
}else{
sqlite3 *db = pSubst->pParse->db;
- if( pSubst->isLeftJoin && pCopy->op!=TK_COLUMN ){
+ if( pSubst->isOuterJoin && pCopy->op!=TK_COLUMN ){
memset(&ifNullRow, 0, sizeof(ifNullRow));
ifNullRow.op = TK_IF_NULL_ROW;
ifNullRow.pLeft = pCopy;
@@ -3725,11 +3765,12 @@ static Expr *substExpr(
sqlite3ExprDelete(db, pNew);
return pExpr;
}
- if( pSubst->isLeftJoin ){
+ if( pSubst->isOuterJoin ){
ExprSetProperty(pNew, EP_CanBeNull);
}
- if( ExprHasProperty(pExpr,EP_FromJoin) ){
- sqlite3SetJoinExpr(pNew, pExpr->w.iJoin);
+ if( ExprHasProperty(pExpr,EP_FromJoin|EP_InnerJoin) ){
+ sqlite3SetJoinExpr(pNew, pExpr->w.iJoin,
+ pExpr->flags & (EP_FromJoin|EP_InnerJoin));
}
sqlite3ExprDelete(db, pExpr);
pExpr = pNew;
@@ -3979,6 +4020,7 @@ static void renumberCursors(
** table and
** (3c) the outer query may not be an aggregate.
** (3d) the outer query may not be DISTINCT.
+** See also (26) for restrictions on RIGHT JOIN.
**
** (4) The subquery can not be DISTINCT.
**
@@ -4077,6 +4119,12 @@ static void renumberCursors(
** function in the select list or ORDER BY clause, flattening
** is not attempted.
**
+** (26) The subquery may not be the right operand of a RIGHT JOIN.
+** See also (3) for restrictions on LEFT JOIN.
+**
+** (27) The subquery may not contain a FULL or RIGHT JOIN unless it
+** is the first element of the parent query.
+**
**
** In this routine, the "p" parameter is a pointer to the outer query.
** The subquery is p->pSrc->a[iFrom]. isAgg is true if the outer query
@@ -4102,7 +4150,7 @@ static int flattenSubquery(
SrcList *pSubSrc; /* The FROM clause of the subquery */
int iParent; /* VDBE cursor number of the pSub result set temp table */
int iNewParent = -1;/* Replacement table for iParent */
- int isLeftJoin = 0; /* True if pSub is the right side of a LEFT JOIN */
+ int isOuterJoin = 0; /* True if pSub is the right side of a LEFT JOIN */
int i; /* Loop counter */
Expr *pWhere; /* The WHERE clause */
SrcItem *pSubitem; /* The subquery */
@@ -4175,26 +4223,32 @@ static int flattenSubquery(
**
** See also tickets #306, #350, and #3300.
*/
- if( (pSubitem->fg.jointype & JT_OUTER)!=0 ){
- isLeftJoin = 1;
- if( pSubSrc->nSrc>1 /* (3a) */
- || isAgg /* (3b) */
- || IsVirtual(pSubSrc->a[0].pTab) /* (3c) */
- || (p->selFlags & SF_Distinct)!=0 /* (3d) */
+ if( (pSubitem->fg.jointype & (JT_OUTER|JT_LTORJ))!=0 ){
+ if( pSubSrc->nSrc>1 /* (3a) */
+ || isAgg /* (3b) */
+ || IsVirtual(pSubSrc->a[0].pTab) /* (3c) */
+ || (p->selFlags & SF_Distinct)!=0 /* (3d) */
+ || (pSubitem->fg.jointype & JT_RIGHT)!=0 /* (26) */
){
return 0;
}
+ isOuterJoin = 1;
}
#ifdef SQLITE_EXTRA_IFNULLROW
else if( iFrom>0 && !isAgg ){
- /* Setting isLeftJoin to -1 causes OP_IfNullRow opcodes to be generated for
+ /* Setting isOuterJoin to -1 causes OP_IfNullRow opcodes to be generated for
** every reference to any result column from subquery in a join, even
** though they are not necessary. This will stress-test the OP_IfNullRow
** opcode. */
- isLeftJoin = -1;
+ isOuterJoin = -1;
}
#endif
+ assert( pSubSrc->nSrc>0 ); /* True by restriction (7) */
+ if( iFrom>0 && (pSubSrc->a[0].fg.jointype & JT_LTORJ)!=0 ){
+ return 0; /* Restriction (27) */
+ }
+
/* Restriction (17): If the sub-query is a compound SELECT, then it must
** use only the UNION ALL operator. And none of the simple select queries
** that make up the compound SELECT are allowed to be aggregate or distinct
@@ -4204,7 +4258,7 @@ static int flattenSubquery(
if( pSub->pOrderBy ){
return 0; /* Restriction (20) */
}
- if( isAgg || (p->selFlags & SF_Distinct)!=0 || isLeftJoin>0 ){
+ if( isAgg || (p->selFlags & SF_Distinct)!=0 || isOuterJoin>0 ){
return 0; /* (17d1), (17d2), or (17f) */
}
for(pSub1=pSub; pSub1; pSub1=pSub1->pPrior){
@@ -4372,6 +4426,7 @@ static int flattenSubquery(
for(pParent=p; pParent; pParent=pParent->pPrior, pSub=pSub->pPrior){
int nSubSrc;
u8 jointype = 0;
+ u8 ltorj = pSrc->a[iFrom].fg.jointype & JT_LTORJ;
assert( pSub!=0 );
pSubSrc = pSub->pSrc; /* FROM clause of subquery */
nSubSrc = pSubSrc->nSrc; /* Number of terms in subquery FROM clause */
@@ -4410,10 +4465,12 @@ static int flattenSubquery(
if( pItem->fg.isUsing ) sqlite3IdListDelete(db, pItem->u3.pUsing);
assert( pItem->fg.isTabFunc==0 );
*pItem = pSubSrc->a[i];
+ pItem->fg.jointype |= ltorj;
iNewParent = pSubSrc->a[i].iCursor;
memset(&pSubSrc->a[i], 0, sizeof(pSubSrc->a[i]));
}
- pSrc->a[iFrom].fg.jointype = jointype;
+ pSrc->a[iFrom].fg.jointype &= JT_LTORJ;
+ pSrc->a[iFrom].fg.jointype |= jointype | ltorj;
/* Now begin substituting subquery result set expressions for
** references to the iParent in the outer query.
@@ -4448,8 +4505,8 @@ static int flattenSubquery(
}
pWhere = pSub->pWhere;
pSub->pWhere = 0;
- if( isLeftJoin>0 ){
- sqlite3SetJoinExpr(pWhere, iNewParent);
+ if( isOuterJoin>0 ){
+ sqlite3SetJoinExpr(pWhere, iNewParent, EP_FromJoin);
}
if( pWhere ){
if( pParent->pWhere ){
@@ -4463,7 +4520,7 @@ static int flattenSubquery(
x.pParse = pParse;
x.iTable = iParent;
x.iNewTable = iNewParent;
- x.isLeftJoin = isLeftJoin;
+ x.isOuterJoin = isOuterJoin;
x.pEList = pSub->pEList;
substSelect(&x, pParent, 0);
}
@@ -4924,7 +4981,7 @@ static int pushDownWhereTerms(
x.pParse = pParse;
x.iTable = iCursor;
x.iNewTable = iCursor;
- x.isLeftJoin = 0;
+ x.isOuterJoin = 0;
x.pEList = pSubq->pEList;
pNew = substExpr(&x, pNew);
#ifndef SQLITE_OMIT_WINDOWFUNC
@@ -5478,11 +5535,35 @@ int sqlite3ExpandSubquery(Parse *pParse, SrcItem *pFrom){
#else
pTab->tabFlags |= TF_Ephemeral; /* Legacy compatibility mode */
#endif
+ return pParse->nErr ? SQLITE_ERROR : SQLITE_OK;
+}
- return pParse->nErr ? SQLITE_ERROR : SQLITE_OK;
+/*
+** Check the N SrcItem objects to the right of pBase. (N might be zero!)
+** If any of those SrcItem objects have a USING clause containing zName
+** then return true.
+**
+** If N is zero, or none of the N SrcItem objects to the right of pBase
+** contains a USING clause, or if none of the USING clauses contain zName,
+** then return false.
+*/
+static int inAnyUsingClause(
+ const char *zName, /* Name we are looking for */
+ SrcItem *pBase, /* The base SrcItem. Looking at pBase[1] and following */
+ int N /* How many SrcItems to check */
+){
+ while( N>0 ){
+ N--;
+ pBase++;
+ if( pBase->fg.isUsing==0 ) continue;
+ if( NEVER(pBase->u3.pUsing==0) ) continue;
+ if( sqlite3IdListIndex(pBase->u3.pUsing, zName)>=0 ) return 1;
+ }
+ return 0;
}
+
/*
** This routine is a Walker callback for "expanding" a SELECT statement.
** "Expanding" means to do the following:
@@ -5632,7 +5713,7 @@ static int selectExpander(Walker *pWalker, Select *p){
/* Process NATURAL keywords, and ON and USING clauses of joins.
*/
assert( db->mallocFailed==0 || pParse->nErr!=0 );
- if( pParse->nErr || sqliteProcessJoin(pParse, p) ){
+ if( pParse->nErr || sqlite3ProcessJoin(pParse, p) ){
return WRC_Abort;
}
@@ -5737,13 +5818,6 @@ static int selectExpander(Walker *pWalker, Select *p){
tableSeen = 1;
if( i>0 && zTName==0 ){
- if( (pFrom->fg.jointype & JT_NATURAL)!=0
- && tableAndColumnIndex(pTabList, i, zName, 0, 0, 1)
- ){
- /* In a NATURAL join, omit the join columns from the
- ** table to the right of the join */
- continue;
- }
if( pFrom->fg.isUsing
&& sqlite3IdListIndex(pFrom->u3.pUsing, zName)>=0
){
@@ -5753,9 +5827,20 @@ static int selectExpander(Walker *pWalker, Select *p){
}
}
pRight = sqlite3Expr(db, TK_ID, zName);
- zColname = zName;
- zToFree = 0;
- if( longNames || pTabList->nSrc>1 || IN_RENAME_OBJECT ){
+ if( longNames ){
+ zColname = sqlite3MPrintf(db, "%s.%s", zTabName, zName);
+ zToFree = zColname;
+ }else{
+ zColname = zName;
+ zToFree = 0;
+ }
+ if( (pTabList->nSrc>1
+ && ( (pFrom->fg.jointype & JT_LTORJ)==0
+ || !inAnyUsingClause(zName,pFrom,pTabList->nSrc-i-1)
+ )
+ )
+ || IN_RENAME_OBJECT
+ ){
Expr *pLeft;
pLeft = sqlite3Expr(db, TK_ID, zTabName);
pExpr = sqlite3PExpr(pParse, TK_DOT, pLeft, pRight);
@@ -5766,10 +5851,6 @@ static int selectExpander(Walker *pWalker, Select *p){
pLeft = sqlite3Expr(db, TK_ID, zSchemaName);
pExpr = sqlite3PExpr(pParse, TK_DOT, pLeft, pExpr);
}
- if( longNames ){
- zColname = sqlite3MPrintf(db, "%s.%s", zTabName, zName);
- zToFree = zColname;
- }
}else{
pExpr = pRight;
}
@@ -5814,6 +5895,12 @@ static int selectExpander(Walker *pWalker, Select *p){
p->selFlags |= SF_ComplexResult;
}
}
+#if TREETRACE_ENABLED
+ if( sqlite3TreeTrace & 0x100 ){
+ SELECTTRACE(0x100,pParse,p,("After result-set wildcard expansion:\n"));
+ sqlite3TreeViewSelect(0, p, 0);
+ }
+#endif
return WRC_Continue;
}
@@ -6507,7 +6594,7 @@ int sqlite3Select(
/* Convert LEFT JOIN into JOIN if there are terms of the right table
** of the LEFT JOIN used in the WHERE clause.
*/
- if( (pItem->fg.jointype & JT_LEFT)!=0
+ if( (pItem->fg.jointype & (JT_LEFT|JT_RIGHT))==JT_LEFT
&& sqlite3ExprImpliesNonNullRow(p->pWhere, pItem->iCursor)
&& OptimizationEnabled(db, SQLITE_SimplifyJoin)
){
@@ -6593,7 +6680,7 @@ int sqlite3Select(
&& i==0
&& (p->selFlags & SF_ComplexResult)!=0
&& (pTabList->nSrc==1
- || (pTabList->a[1].fg.jointype&(JT_LEFT|JT_CROSS))!=0)
+ || (pTabList->a[1].fg.jointype&(JT_OUTER|JT_CROSS))!=0)
){
continue;
}
@@ -6715,6 +6802,7 @@ int sqlite3Select(
if( OptimizationEnabled(db, SQLITE_PushDown)
&& (pItem->fg.isCte==0
|| (pItem->u2.pCteUse->eM10d!=M10d_Yes && pItem->u2.pCteUse->nUse<2))
+ && (pItem->fg.jointype & JT_RIGHT)==0
&& pushDownWhereTerms(pParse, pSub, p->pWhere, pItem->iCursor,
(pItem->fg.jointype & JT_OUTER)!=0)
){
@@ -6735,18 +6823,19 @@ int sqlite3Select(
/* Generate code to implement the subquery
**
- ** The subquery is implemented as a co-routine if:
+ ** The subquery is implemented as a co-routine all of the following are
+ ** true:
+ **
** (1) the subquery is guaranteed to be the outer loop (so that
** it does not need to be computed more than once), and
** (2) the subquery is not a CTE that should be materialized
- **
- ** TODO: Are there other reasons beside (1) and (2) to use a co-routine
- ** implementation?
+ ** (3) the subquery is not part of a left operand for a RIGHT JOIN
*/
if( i==0
&& (pTabList->nSrc==1
- || (pTabList->a[1].fg.jointype&(JT_LEFT|JT_CROSS))!=0) /* (1) */
- && (pItem->fg.isCte==0 || pItem->u2.pCteUse->eM10d!=M10d_Yes) /* (2) */
+ || (pTabList->a[1].fg.jointype&(JT_OUTER|JT_CROSS))!=0) /* (1) */
+ && (pItem->fg.isCte==0 || pItem->u2.pCteUse->eM10d!=M10d_Yes) /* (2) */
+ && (pTabList->a[0].fg.jointype & JT_LTORJ)==0 /* (3) */
){
/* Implement a co-routine that will return a single row of the result
** set on each invocation.
@@ -7524,7 +7613,9 @@ int sqlite3Select(
updateAccumulator(pParse, regAcc, pAggInfo, eDist);
if( eDist!=WHERE_DISTINCT_NOOP ){
struct AggInfo_func *pF = &pAggInfo->aFunc[0];
- fixDistinctOpenEph(pParse, eDist, pF->iDistinct, pF->iDistAddr);
+ if( pF ){
+ fixDistinctOpenEph(pParse, eDist, pF->iDistinct, pF->iDistAddr);
+ }
}
if( regAcc ) sqlite3VdbeAddOp2(v, OP_Integer, 1, regAcc);
diff --git a/src/sqliteInt.h b/src/sqliteInt.h
index 42554d263..56b885906 100644
--- a/src/sqliteInt.h
+++ b/src/sqliteInt.h
@@ -2887,7 +2887,7 @@ struct Expr {
#define EP_ConstFunc 0x080000 /* A SQLITE_FUNC_CONSTANT or _SLOCHNG function */
#define EP_CanBeNull 0x100000 /* Can be null despite NOT NULL constraint */
#define EP_Subquery 0x200000 /* Tree contains a TK_SELECT operator */
- /* 0x400000 // Available */
+#define EP_InnerJoin 0x400000 /* Originates in ON/USING of an inner join */
#define EP_Leaf 0x800000 /* Expr.pLeft, .pRight, .u.pSelect all NULL */
#define EP_WinFunc 0x1000000 /* TK_FUNCTION with Expr.y.pWin set */
#define EP_Subrtn 0x2000000 /* Uses Expr.y.sub. TK_IN, _SELECT, or _EXISTS */
@@ -3038,14 +3038,26 @@ struct ExprList {
** If "a" is the k-th column of table "t", then IdList.a[0].idx==k.
*/
struct IdList {
+ int nId; /* Number of identifiers on the list */
+ u8 eU4; /* Which element of a.u4 is valid */
struct IdList_item {
char *zName; /* Name of the identifier */
- int idx; /* Index in some Table.aCol[] of a column named zName */
- } *a;
- int nId; /* Number of identifiers on the list */
+ union {
+ int idx; /* Index in some Table.aCol[] of a column named zName */
+ Expr *pExpr; /* Expr to implement a USING variable -- NOT USED */
+ } u4;
+ } a[1];
};
/*
+** Allowed values for IdList.eType, which determines which value of the a.u4
+** is valid.
+*/
+#define EU4_NONE 0 /* Does not use IdList.a.u4 */
+#define EU4_IDX 1 /* Uses IdList.a.u4.idx */
+#define EU4_EXPR 2 /* Uses IdList.a.u4.pExpr -- NOT CURRENTLY USED */
+
+/*
** The SrcItem object represents a single term in the FROM clause of a query.
** The SrcList object is mostly an array of SrcItems.
**
@@ -3078,6 +3090,7 @@ struct SrcItem {
unsigned isCte :1; /* This is a CTE */
unsigned notCte :1; /* This item may not match a CTE */
unsigned isUsing :1; /* u3.pUsing is valid */
+ unsigned isSynthUsing :1; /* u3.pUsing is synthensized from NATURAL */
} fg;
int iCursor; /* The VDBE cursor number used to access this table */
union {
@@ -3132,14 +3145,15 @@ struct SrcList {
/*
** Permitted values of the SrcList.a.jointype field
*/
-#define JT_INNER 0x0001 /* Any kind of inner or cross join */
-#define JT_CROSS 0x0002 /* Explicit use of the CROSS keyword */
-#define JT_NATURAL 0x0004 /* True for a "natural" join */
-#define JT_LEFT 0x0008 /* Left outer join */
-#define JT_RIGHT 0x0010 /* Right outer join */
-#define JT_OUTER 0x0020 /* The "OUTER" keyword is present */
-#define JT_ERROR 0x0040 /* unknown or unsupported join type */
-
+#define JT_INNER 0x01 /* Any kind of inner or cross join */
+#define JT_CROSS 0x02 /* Explicit use of the CROSS keyword */
+#define JT_NATURAL 0x04 /* True for a "natural" join */
+#define JT_LEFT 0x08 /* Left outer join */
+#define JT_RIGHT 0x10 /* Right outer join */
+#define JT_OUTER 0x20 /* The "OUTER" keyword is present */
+#define JT_LTORJ 0x40 /* One of the LEFT operands of a RIGHT JOIN
+ ** Mnemonic: Left Table Of Right Join */
+#define JT_ERROR 0x80 /* unknown or unsupported join type */
/*
** Flags appropriate for the wctrlFlags parameter of sqlite3WhereBegin()
@@ -3162,7 +3176,7 @@ struct SrcList {
#define WHERE_SORTBYGROUP 0x0200 /* Support sqlite3WhereIsSorted() */
#define WHERE_AGG_DISTINCT 0x0400 /* Query is "SELECT agg(DISTINCT ...)" */
#define WHERE_ORDERBY_LIMIT 0x0800 /* ORDERBY+LIMIT on the inner loop */
- /* 0x1000 not currently used */
+#define WHERE_RIGHT_JOIN 0x1000 /* Processing a RIGHT JOIN */
/* 0x2000 not currently used */
#define WHERE_USE_LIMIT 0x4000 /* Use the LIMIT in cost estimates */
/* 0x8000 not currently used */
@@ -3569,6 +3583,7 @@ struct Parse {
u8 okConstFactor; /* OK to factor out constants */
u8 disableLookaside; /* Number of times lookaside has been disabled */
u8 disableVtab; /* Disable all virtual tables for this parse */
+ u8 withinRJSubrtn; /* Nesting level for RIGHT JOIN body subroutines */
#if defined(SQLITE_DEBUG) || defined(SQLITE_COVERAGE_TEST)
u8 earlyCleanup; /* OOM inside sqlite3ParserAddCleanup() */
#endif
@@ -4628,7 +4643,7 @@ SrcList *sqlite3SrcListAppendFromTerm(Parse*, SrcList*, Token*, Token*,
void sqlite3SrcListIndexedBy(Parse *, SrcList *, Token *);
void sqlite3SrcListFuncArgs(Parse*, SrcList*, ExprList*);
int sqlite3IndexedByLookup(Parse *, SrcItem *);
-void sqlite3SrcListShiftJoinType(SrcList*);
+void sqlite3SrcListShiftJoinType(Parse*,SrcList*);
void sqlite3SrcListAssignCursors(Parse*, SrcList*);
void sqlite3IdListDelete(sqlite3*, IdList*);
void sqlite3ClearOnOrUsing(sqlite3*, OnOrUsing*);
@@ -4828,7 +4843,7 @@ void sqlite3MaterializeView(Parse*, Table*, Expr*, ExprList*,Expr*,int);
int sqlite3JoinType(Parse*, Token*, Token*, Token*);
int sqlite3ColumnIndex(Table *pTab, const char *zCol);
-void sqlite3SetJoinExpr(Expr*,int);
+void sqlite3SetJoinExpr(Expr*,int,u32);
void sqlite3CreateForeignKey(Parse*, ExprList*, Token*, ExprList*, int);
void sqlite3DeferForeignKey(Parse*, int);
#ifndef SQLITE_OMIT_AUTHORIZATION
diff --git a/src/treeview.c b/src/treeview.c
index c3ecdefef..ff5558aca 100644
--- a/src/treeview.c
+++ b/src/treeview.c
@@ -133,6 +133,7 @@ void sqlite3TreeViewWith(TreeView *pView, const With *pWith, u8 moreToFollow){
*/
void sqlite3TreeViewSrcList(TreeView *pView, const SrcList *pSrc){
int i;
+ if( pSrc==0 ) return;
for(i=0; i<pSrc->nSrc; i++){
const SrcItem *pItem = &pSrc->a[i];
StrAccum x;
@@ -144,11 +145,18 @@ void sqlite3TreeViewSrcList(TreeView *pView, const SrcList *pSrc){
sqlite3_str_appendf(&x, " tab=%Q nCol=%d ptr=%p used=%llx",
pItem->pTab->zName, pItem->pTab->nCol, pItem->pTab, pItem->colUsed);
}
- if( pItem->fg.jointype & JT_LEFT ){
+ if( (pItem->fg.jointype & (JT_LEFT|JT_RIGHT))==(JT_LEFT|JT_RIGHT) ){
+ sqlite3_str_appendf(&x, " FULL-OUTER-JOIN");
+ }else if( pItem->fg.jointype & JT_LEFT ){
sqlite3_str_appendf(&x, " LEFT-JOIN");
+ }else if( pItem->fg.jointype & JT_RIGHT ){
+ sqlite3_str_appendf(&x, " RIGHT-JOIN");
}else if( pItem->fg.jointype & JT_CROSS ){
sqlite3_str_appendf(&x, " CROSS-JOIN");
}
+ if( pItem->fg.jointype & JT_LTORJ ){
+ sqlite3_str_appendf(&x, " LTORJ");
+ }
if( pItem->fg.fromDDL ){
sqlite3_str_appendf(&x, " DDL");
}
@@ -838,7 +846,21 @@ void sqlite3TreeViewBareIdList(
if( zName==0 ) zName = "(null)";
sqlite3TreeViewPush(&pView, moreToFollow);
sqlite3TreeViewLine(pView, 0);
- fprintf(stdout, "%s (%d)\n", zName, pList->a[i].idx);
+ if( pList->eU4==EU4_NONE ){
+ fprintf(stdout, "%s\n", zName);
+ }else if( pList->eU4==EU4_IDX ){
+ fprintf(stdout, "%s (%d)\n", zName, pList->a[i].u4.idx);
+ }else{
+ assert( pList->eU4==EU4_EXPR );
+ if( pList->a[i].u4.pExpr==0 ){
+ fprintf(stdout, "%s (pExpr=NULL)\n", zName);
+ }else{
+ fprintf(stdout, "%s\n", zName);
+ sqlite3TreeViewPush(&pView, i<pList->nId-1);
+ sqlite3TreeViewExpr(pView, pList->a[i].u4.pExpr, 0);
+ sqlite3TreeViewPop(&pView);
+ }
+ }
sqlite3TreeViewPop(&pView);
}
}
diff --git a/src/vdbe.c b/src/vdbe.c
index 16b6f9bb5..65e843fd2 100644
--- a/src/vdbe.c
+++ b/src/vdbe.c
@@ -988,8 +988,17 @@ case OP_Gosub: { /* jump */
/* Opcode: Return P1 P2 P3 * *
**
-** Jump to the next instruction after the address in register P1. After
-** the jump, register P1 becomes undefined.
+** Jump to the address stored in register P1. If P1 is a return address
+** register, then this accomplishes a return from a subroutine.
+**
+** If P3 is 1, then the jump is only taken if register P1 holds an integer
+** values, otherwise execution falls through to the next opcode, and the
+** OP_Return becomes a no-op. If P3 is 0, then register P1 must hold an
+** integer or else an assert() is raised. P3 should be set to 1 when
+** this opcode is used in combination with OP_BeginSubrtn, and set to 0
+** otherwise.
+**
+** The value in register P1 is unchanged by this opcode.
**
** P2 is not used by the byte-code engine. However, if P2 is positive
** and also less than the current address, then the "EXPLAIN" output
@@ -998,16 +1007,15 @@ case OP_Gosub: { /* jump */
** in the subroutine from which this opcode is returnning. Thus the P2
** value is a byte-code indentation hint. See tag-20220407a in
** wherecode.c and shell.c.
-**
-** P3 is not used by the byte-code engine. However, the code generator
-** sets P3 to address of the associated OP_BeginSubrtn opcode, if there is
-** one.
*/
case OP_Return: { /* in1 */
pIn1 = &aMem[pOp->p1];
- assert( pIn1->flags==MEM_Int );
- pOp = &aOp[pIn1->u.i];
- pIn1->flags = MEM_Undefined;
+ if( pIn1->flags & MEM_Int ){
+ if( pOp->p3 ) VdbeBranchTaken(1, 2);
+ pOp = &aOp[pIn1->u.i];
+ }else if( ALWAYS(pOp->p3) ){
+ VdbeBranchTaken(0, 2);
+ }
break;
}
@@ -1193,22 +1201,11 @@ case OP_Halt: {
goto vdbe_return;
}
-/* Opcode: BeginSubrtn P1 P2 * * *
-** Synopsis: r[P2]=P1
-**
-** Mark the beginning of a subroutine by loading the integer value P1
-** into register r[P2]. The P2 register is used to store the return
-** address of the subroutine call.
-**
-** This opcode is identical to OP_Integer. It has a different name
-** only to make the byte code easier to read and verify.
-*/
/* Opcode: Integer P1 P2 * * *
** Synopsis: r[P2]=P1
**
** The 32-bit integer value P1 is written into register P2.
*/
-case OP_BeginSubrtn:
case OP_Integer: { /* out2 */
pOut = out2Prerelease(p, pOp);
pOut->u.i = pOp->p1;
@@ -1315,6 +1312,28 @@ case OP_String: { /* out2 */
break;
}
+/* Opcode: BeginSubrtn * P2 * * *
+** Synopsis: r[P2]=NULL
+**
+** Mark the beginning of a subroutine that can be entered in-line
+** or that can be called using OP_Gosub. The subroutine should
+** be terminated by an OP_Return instruction that has a P1 operand that
+** is the same as the P2 operand to this opcode and that has P3 set to 1.
+** If the subroutine is entered in-line, then the OP_Return will simply
+** fall through. But if the subroutine is entered using OP_Gosub, then
+** the OP_Return will jump back to the first instruction after the OP_Gosub.
+**
+** This routine works by loading a NULL into the P2 register. When the
+** return address register contains a NULL, the OP_Return instruction is
+** a no-op that simply falls through to the next instruction (assuming that
+** the OP_Return opcode has a P3 value of 1). Thus if the subroutine is
+** entered in-line, then the OP_Return will cause in-line execution to
+** continue. But if the subroutine is entered via OP_Gosub, then the
+** OP_Return will cause a return to the address following the OP_Gosub.
+**
+** This opcode is identical to OP_Null. It has a different name
+** only to make the byte code easier to read and verify.
+*/
/* Opcode: Null P1 P2 P3 * *
** Synopsis: r[P2..P3]=NULL
**
@@ -1327,6 +1346,7 @@ case OP_String: { /* out2 */
** NULL values will not compare equal even if SQLITE_NULLEQ is set on
** OP_Ne or OP_Eq.
*/
+case OP_BeginSubrtn:
case OP_Null: { /* out2 */
int cnt;
u16 nullFlag;
@@ -2701,7 +2721,8 @@ case OP_Column: {
op_column_restart:
assert( pC!=0 );
- assert( p2<(u32)pC->nField );
+ assert( p2<(u32)pC->nField
+ || (pC->eCurType==CURTYPE_PSEUDO && pC->seekResult==0) );
aOffset = pC->aOffset;
assert( aOffset==pC->aType+pC->nField );
assert( pC->eCurType!=CURTYPE_VTAB );
@@ -2710,10 +2731,9 @@ op_column_restart:
if( pC->cacheStatus!=p->cacheCtr ){ /*OPTIMIZATION-IF-FALSE*/
if( pC->nullRow ){
- if( pC->eCurType==CURTYPE_PSEUDO ){
+ if( pC->eCurType==CURTYPE_PSEUDO && pC->seekResult>0 ){
/* For the special case of as pseudo-cursor, the seekResult field
** identifies the register that holds the record */
- assert( pC->seekResult>0 );
pReg = &aMem[pC->seekResult];
assert( pReg->flags & MEM_Blob );
assert( memIsValid(pReg) );
@@ -4990,15 +5010,14 @@ case OP_Found: { /* jump, in3 */
#ifdef SQLITE_DEBUG
pC->seekOp = pOp->opcode;
#endif
- pIn3 = &aMem[pOp->p3];
+ r.aMem = &aMem[pOp->p3];
assert( pC->eCurType==CURTYPE_BTREE );
assert( pC->uc.pCursor!=0 );
assert( pC->isTable==0 );
- if( pOp->p4.i>0 ){
+ r.nField = (u16)pOp->p4.i;
+ if( r.nField>0 ){
/* Key values in an array of registers */
- r.nField = (u16)pOp->p4.i;
r.pKeyInfo = pC->pKeyInfo;
- r.aMem = pIn3;
r.default_rc = 0;
#ifdef SQLITE_DEBUG
for(ii=0; ii<r.nField; ii++){
@@ -5010,14 +5029,14 @@ case OP_Found: { /* jump, in3 */
rc = sqlite3BtreeIndexMoveto(pC->uc.pCursor, &r, &pC->seekResult);
}else{
/* Composite key generated by OP_MakeRecord */
- assert( pIn3->flags & MEM_Blob );
+ assert( r.aMem->flags & MEM_Blob );
assert( pOp->opcode!=OP_NoConflict );
- rc = ExpandBlob(pIn3);
+ rc = ExpandBlob(r.aMem);
assert( rc==SQLITE_OK || rc==SQLITE_NOMEM );
if( rc ) goto no_mem;
pIdxKey = sqlite3VdbeAllocUnpackedRecord(pC->pKeyInfo);
if( pIdxKey==0 ) goto no_mem;
- sqlite3VdbeRecordUnpack(pC->pKeyInfo, pIn3->n, pIn3->z, pIdxKey);
+ sqlite3VdbeRecordUnpack(pC->pKeyInfo, r.aMem->n, r.aMem->z, pIdxKey);
pIdxKey->default_rc = 0;
rc = sqlite3BtreeIndexMoveto(pC->uc.pCursor, pIdxKey, &pC->seekResult);
sqlite3DbFreeNN(db, pIdxKey);
@@ -5798,16 +5817,23 @@ case OP_Rowid: { /* out2 */
** that occur while the cursor is on the null row will always
** write a NULL.
**
-** Or, if P1 is a Pseudo-Cursor (a cursor opened using OP_OpenPseudo)
-** just reset the cache for that cursor. This causes the row of
-** content held by the pseudo-cursor to be reparsed.
+** If cursor P1 is not previously opened, open it now to a special
+** pseudo-cursor that always returns NULL for every column.
*/
case OP_NullRow: {
VdbeCursor *pC;
assert( pOp->p1>=0 && pOp->p1<p->nCursor );
pC = p->apCsr[pOp->p1];
- assert( pC!=0 );
+ if( pC==0 ){
+ /* If the cursor is not already open, create a special kind of
+ ** pseudo-cursor that always gives null rows. */
+ pC = allocateCursor(p, pOp->p1, 1, CURTYPE_PSEUDO);
+ if( pC==0 ) goto no_mem;
+ pC->seekResult = 0;
+ pC->isTable = 1;
+ pC->uc.pCursor = sqlite3BtreeFakeValidCursor();
+ }
pC->nullRow = 1;
pC->cacheStatus = CACHE_STALE;
if( pC->eCurType==CURTYPE_BTREE ){
@@ -6254,9 +6280,9 @@ case OP_IdxRowid: { /* out2 */
assert( pOp->p1>=0 && pOp->p1<p->nCursor );
pC = p->apCsr[pOp->p1];
assert( pC!=0 );
- assert( pC->eCurType==CURTYPE_BTREE );
+ assert( pC->eCurType==CURTYPE_BTREE || IsNullCursor(pC) );
assert( pC->uc.pCursor!=0 );
- assert( pC->isTable==0 );
+ assert( pC->isTable==0 || IsNullCursor(pC) );
assert( pC->deferredMoveto==0 );
assert( !pC->nullRow || pOp->opcode==OP_IdxRowid );
diff --git a/src/vdbeInt.h b/src/vdbeInt.h
index b07aee0c0..2a2e40317 100644
--- a/src/vdbeInt.h
+++ b/src/vdbeInt.h
@@ -134,6 +134,11 @@ struct VdbeCursor {
u32 aType[1]; /* Type values record decode. MUST BE LAST */
};
+/* Return true if P is a null-only cursor
+*/
+#define IsNullCursor(P) \
+ ((P)->eCurType==CURTYPE_PSEUDO && (P)->nullRow && (P)->seekResult==0)
+
/*
** A value for VdbeCursor.cacheStatus that means the cache is always invalid.
diff --git a/src/vdbeaux.c b/src/vdbeaux.c
index a67bc68f9..eaf5780ab 100644
--- a/src/vdbeaux.c
+++ b/src/vdbeaux.c
@@ -3554,7 +3554,7 @@ int SQLITE_NOINLINE sqlite3VdbeHandleMovedCursor(VdbeCursor *p){
** if need be. Return any I/O error from the restore operation.
*/
int sqlite3VdbeCursorRestore(VdbeCursor *p){
- assert( p->eCurType==CURTYPE_BTREE );
+ assert( p->eCurType==CURTYPE_BTREE || IsNullCursor(p) );
if( sqlite3BtreeCursorHasMoved(p->uc.pCursor) ){
return sqlite3VdbeHandleMovedCursor(p);
}
diff --git a/src/where.c b/src/where.c
index 2e97509e0..8526f7c5a 100644
--- a/src/where.c
+++ b/src/where.c
@@ -756,13 +756,13 @@ static int termCanDriveIndex(
char aff;
if( pTerm->leftCursor!=pSrc->iCursor ) return 0;
if( (pTerm->eOperator & (WO_EQ|WO_IS))==0 ) return 0;
- if( (pSrc->fg.jointype & JT_LEFT)
+ if( (pSrc->fg.jointype & (JT_LEFT|JT_LTORJ))!=0
&& !ExprHasProperty(pTerm->pExpr, EP_FromJoin)
&& (pTerm->eOperator & WO_IS)
){
/* Cannot use an IS term from the WHERE clause as an index driver for
- ** the RHS of a LEFT JOIN. Such a term can only be used if it is from
- ** the ON clause. */
+ ** the RHS of a LEFT JOIN or for the LHS of a RIGHT JOIN. Such a term
+ ** can only be used if it is from the ON clause. */
return 0;
}
if( (pTerm->prereqRight & notReady)!=0 ) return 0;
@@ -832,7 +832,8 @@ static SQLITE_NOINLINE void constructAutomaticIndex(
** WHERE clause (or the ON clause of a LEFT join) that constrain which
** rows of the target table (pSrc) that can be used. */
if( (pTerm->wtFlags & TERM_VIRTUAL)==0
- && ((pSrc->fg.jointype&JT_LEFT)==0 || ExprHasProperty(pExpr,EP_FromJoin))
+ && ((pSrc->fg.jointype&(JT_LEFT|JT_LTORJ))==0
+ || ExprHasProperty(pExpr,EP_FromJoin))
&& sqlite3ExprIsTableConstant(pExpr, pSrc->iCursor)
){
pPartial = sqlite3ExprAnd(pParse, pPartial,
@@ -1105,7 +1106,7 @@ static SQLITE_NOINLINE void sqlite3ConstructBloomFilter(
const SrcItem *pTabItem;
pLevel = &pWInfo->a[iLevel];
pTabItem = &pWInfo->pTabList->a[pLevel->iFrom];
- if( pTabItem->fg.jointype & JT_LEFT ) continue;
+ if( pTabItem->fg.jointype & (JT_LEFT|JT_LTORJ) ) continue;
pLoop = pLevel->pWLoop;
if( NEVER(pLoop==0) ) continue;
if( pLoop->prereq & notReady ) continue;
@@ -1178,9 +1179,10 @@ static sqlite3_index_info *allocateIndexInfo(
assert( pTerm->u.x.leftColumn<pTab->nCol );
/* tag-20191211-002: WHERE-clause constraints are not useful to the
- ** right-hand table of a LEFT JOIN. See tag-20191211-001 for the
+ ** right-hand table of a LEFT JOIN nor to the left-hand table of a
+ ** RIGHT JOIN. See tag-20191211-001 for the
** equivalent restriction for ordinary tables. */
- if( (pSrc->fg.jointype & JT_LEFT)!=0
+ if( (pSrc->fg.jointype & (JT_LEFT|JT_LTORJ))!=0
&& !ExprHasProperty(pTerm->pExpr, EP_FromJoin)
){
continue;
@@ -2621,10 +2623,11 @@ static void whereLoopOutputAdjust(
**
** 2022-03-24: Self-culling only applies if either the extra terms
** are straight comparison operators that are non-true with NULL
- ** operand, or if the loop is not a LEFT JOIN.
+ ** operand, or if the loop is not an OUTER JOIN.
*/
if( (pTerm->eOperator & 0x3f)!=0
- || (pWC->pWInfo->pTabList->a[pLoop->iTab].fg.jointype & JT_LEFT)==0
+ || (pWC->pWInfo->pTabList->a[pLoop->iTab].fg.jointype
+ & (JT_LEFT|JT_LTORJ))==0
){
pLoop->wsFlags |= WHERE_SELFCULL;
}
@@ -2831,9 +2834,10 @@ static int whereLoopAddBtreeIndex(
if( pTerm->wtFlags & TERM_LIKEOPT && pTerm->eOperator==WO_LT ) continue;
/* tag-20191211-001: Do not allow constraints from the WHERE clause to
- ** be used by the right table of a LEFT JOIN. Only constraints in the
+ ** be used by the right table of a LEFT JOIN nor by the left table of a
+ ** RIGHT JOIN. Only constraints in the
** ON clause are allowed. See tag-20191211-002 for the vtab equivalent. */
- if( (pSrc->fg.jointype & JT_LEFT)!=0
+ if( (pSrc->fg.jointype & (JT_LEFT|JT_LTORJ))!=0
&& !ExprHasProperty(pTerm->pExpr, EP_FromJoin)
){
continue;
@@ -3313,13 +3317,14 @@ static int whereLoopAddBtree(
#ifndef SQLITE_OMIT_AUTOMATIC_INDEX
/* Automatic indexes */
if( !pBuilder->pOrSet /* Not part of an OR optimization */
- && (pWInfo->wctrlFlags & WHERE_OR_SUBCLAUSE)==0
+ && (pWInfo->wctrlFlags & (WHERE_RIGHT_JOIN|WHERE_OR_SUBCLAUSE))==0
&& (pWInfo->pParse->db->flags & SQLITE_AutoIndex)!=0
&& !pSrc->fg.isIndexedBy /* Has no INDEXED BY clause */
&& !pSrc->fg.notIndexed /* Has no NOT INDEXED clause */
&& HasRowid(pTab) /* Not WITHOUT ROWID table. (FIXME: Why not?) */
&& !pSrc->fg.isCorrelated /* Not a correlated subquery */
&& !pSrc->fg.isRecursive /* Not a recursive common table expression. */
+ && (pSrc->fg.jointype & JT_RIGHT)==0 /* Not the right tab of a RIGHT JOIN */
){
/* Generate auto-index WhereLoops */
LogEst rLogSize; /* Logarithm of the number of rows in the table */
@@ -4011,6 +4016,9 @@ static int whereLoopAddOr(
pItem = pWInfo->pTabList->a + pNew->iTab;
iCur = pItem->iCursor;
+ /* The multi-index OR optimization does not work for RIGHT and FULL JOIN */
+ if( pItem->fg.jointype & JT_RIGHT ) return SQLITE_OK;
+
for(pTerm=pWC->a; pTerm<pWCEnd && rc==SQLITE_OK; pTerm++){
if( (pTerm->eOperator & WO_OR)!=0
&& (pTerm->u.pOrInfo->indexable & pNew->maskSelf)!=0
@@ -4135,9 +4143,9 @@ static int whereLoopAddAll(WhereLoopBuilder *pBuilder){
pNew->iTab = iTab;
pBuilder->iPlanLimit += SQLITE_QUERY_PLANNER_LIMIT_INCR;
pNew->maskSelf = sqlite3WhereGetMask(&pWInfo->sMaskSet, pItem->iCursor);
- if( (pItem->fg.jointype & (JT_LEFT|JT_CROSS))!=0 ){
+ if( (pItem->fg.jointype & (JT_OUTER|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. */
+ ** right-hand-side of a OUTER or CROSS JOIN. */
mPrereq = mPrior;
}else{
mPrereq = 0;
@@ -4146,7 +4154,7 @@ static int whereLoopAddAll(WhereLoopBuilder *pBuilder){
if( IsVirtual(pItem->pTab) ){
SrcItem *p;
for(p=&pItem[1]; p<pEnd; p++){
- if( mUnusable || (p->fg.jointype & (JT_LEFT|JT_CROSS)) ){
+ if( mUnusable || (p->fg.jointype & (JT_OUTER|JT_CROSS)) ){
mUnusable |= sqlite3WhereGetMask(&pWInfo->sMaskSet, p->iCursor);
}
}
@@ -5762,8 +5770,10 @@ WhereInfo *sqlite3WhereBegin(
/* noop */
}else
#endif
- if( (pLoop->wsFlags & WHERE_IDX_ONLY)==0
- && (wctrlFlags & WHERE_OR_SUBCLAUSE)==0 ){
+ if( ((pLoop->wsFlags & WHERE_IDX_ONLY)==0
+ && (wctrlFlags & WHERE_OR_SUBCLAUSE)==0)
+ || (pTabItem->fg.jointype & (JT_LTORJ|JT_RIGHT))!=0
+ ){
int op = OP_OpenRead;
if( pWInfo->eOnePass!=ONEPASS_OFF ){
op = OP_OpenWrite;
@@ -5865,6 +5875,37 @@ WhereInfo *sqlite3WhereBegin(
}
}
if( iDb>=0 ) sqlite3CodeVerifySchema(pParse, iDb);
+ if( (pTabItem->fg.jointype & JT_RIGHT)!=0
+ && (pLevel->pRJ = sqlite3WhereMalloc(pWInfo, sizeof(WhereRightJoin)))!=0
+ ){
+ WhereRightJoin *pRJ = pLevel->pRJ;
+ pRJ->iMatch = pParse->nTab++;
+ pRJ->regBloom = ++pParse->nMem;
+ sqlite3VdbeAddOp2(v, OP_Blob, 65536, pRJ->regBloom);
+ pRJ->regReturn = ++pParse->nMem;
+ sqlite3VdbeAddOp2(v, OP_Null, 0, pRJ->regReturn);
+ assert( pTab==pTabItem->pTab );
+ if( HasRowid(pTab) ){
+ KeyInfo *pInfo;
+ sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pRJ->iMatch, 1);
+ pInfo = sqlite3KeyInfoAlloc(pParse->db, 1, 0);
+ if( pInfo ){
+ pInfo->aColl[0] = 0;
+ pInfo->aSortFlags[0] = 0;
+ sqlite3VdbeAppendP4(v, pInfo, P4_KEYINFO);
+ }
+ }else{
+ Index *pPk = sqlite3PrimaryKeyIndex(pTab);
+ sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pRJ->iMatch, pPk->nKeyCol);
+ sqlite3VdbeSetP4KeyInfo(pParse, pPk);
+ }
+ pLoop->wsFlags &= ~WHERE_IDX_ONLY;
+ /* The nature of RIGHT JOIN processing is such that it messes up
+ ** the output order. So omit any ORDER BY/GROUP BY elimination
+ ** optimizations. We need to do an actual sort for RIGHT JOIN. */
+ pWInfo->nOBSat = 0;
+ pWInfo->eDistinct = WHERE_DISTINCT_UNORDERED;
+ }
}
pWInfo->iTop = sqlite3VdbeCurrentAddr(v);
if( db->mallocFailed ) goto whereBeginError;
@@ -5977,6 +6018,17 @@ void sqlite3WhereEnd(WhereInfo *pWInfo){
for(i=pWInfo->nLevel-1; i>=0; i--){
int addr;
pLevel = &pWInfo->a[i];
+ if( pLevel->pRJ ){
+ /* Terminate the subroutine that forms the interior of the loop of
+ ** the RIGHT JOIN table */
+ WhereRightJoin *pRJ = pLevel->pRJ;
+ sqlite3VdbeResolveLabel(v, pLevel->addrCont);
+ pLevel->addrCont = 0;
+ sqlite3VdbeAddOp3(v, OP_Return, pRJ->regReturn, pRJ->addrSubrtn, 1);
+ VdbeCoverage(v);
+ assert( pParse->withinRJSubrtn>0 );
+ pParse->withinRJSubrtn--;
+ }
pLoop = pLevel->pWLoop;
if( pLevel->op!=OP_Noop ){
#ifndef SQLITE_DISABLE_SKIPAHEAD_DISTINCT
@@ -6004,7 +6056,7 @@ void sqlite3WhereEnd(WhereInfo *pWInfo){
}
#endif /* SQLITE_DISABLE_SKIPAHEAD_DISTINCT */
/* The common case: Advance to the next row */
- sqlite3VdbeResolveLabel(v, pLevel->addrCont);
+ if( pLevel->addrCont ) sqlite3VdbeResolveLabel(v, pLevel->addrCont);
sqlite3VdbeAddOp3(v, pLevel->op, pLevel->p1, pLevel->p2, pLevel->p3);
sqlite3VdbeChangeP5(v, pLevel->p5);
VdbeCoverage(v);
@@ -6019,7 +6071,7 @@ void sqlite3WhereEnd(WhereInfo *pWInfo){
#ifndef SQLITE_DISABLE_SKIPAHEAD_DISTINCT
if( addrSeek ) sqlite3VdbeJumpHere(v, addrSeek);
#endif
- }else{
+ }else if( pLevel->addrCont ){
sqlite3VdbeResolveLabel(v, pLevel->addrCont);
}
if( (pLoop->wsFlags & WHERE_IN_ABLE)!=0 && pLevel->u.in.nIn>0 ){
@@ -6069,6 +6121,10 @@ void sqlite3WhereEnd(WhereInfo *pWInfo){
}
}
sqlite3VdbeResolveLabel(v, pLevel->addrBrk);
+ if( pLevel->pRJ ){
+ sqlite3VdbeAddOp3(v, OP_Return, pLevel->pRJ->regReturn, 0, 1);
+ VdbeCoverage(v);
+ }
if( pLevel->addrSkip ){
sqlite3VdbeGoto(v, pLevel->addrSkip);
VdbeComment((v, "next skip-scan on %s", pLoop->u.btree.pIndex->zName));
@@ -6112,11 +6168,6 @@ void sqlite3WhereEnd(WhereInfo *pWInfo){
pWInfo->pTabList->a[pLevel->iFrom].pTab->zName));
}
- /* The "break" point is here, just past the end of the outer loop.
- ** Set it.
- */
- sqlite3VdbeResolveLabel(v, pWInfo->iBreak);
-
assert( pWInfo->nLevel<=pTabList->nSrc );
for(i=0, pLevel=pWInfo->a; i<pWInfo->nLevel; i++, pLevel++){
int k, last;
@@ -6127,6 +6178,15 @@ void sqlite3WhereEnd(WhereInfo *pWInfo){
assert( pTab!=0 );
pLoop = pLevel->pWLoop;
+ /* Do RIGHT JOIN processing. Generate code that will output the
+ ** unmatched rows of the right operand of the RIGHT JOIN with
+ ** all of the columns of the left operand set to NULL.
+ */
+ if( pLevel->pRJ ){
+ sqlite3WhereRightJoinLoop(pWInfo, i, pLevel);
+ continue;
+ }
+
/* For a co-routine, change all OP_Column references to the table of
** the co-routine into OP_Copy of result contained in a register.
** OP_Rowid becomes OP_Null.
@@ -6138,29 +6198,6 @@ void sqlite3WhereEnd(WhereInfo *pWInfo){
continue;
}
-#ifdef SQLITE_ENABLE_EARLY_CURSOR_CLOSE
- /* Close all of the cursors that were opened by sqlite3WhereBegin.
- ** Except, do not close cursors that will be reused by the OR optimization
- ** (WHERE_OR_SUBCLAUSE). And do not close the OP_OpenWrite cursors
- ** created for the ONEPASS optimization.
- */
- if( (pTab->tabFlags & TF_Ephemeral)==0
- && !IsView(pTab)
- && (pWInfo->wctrlFlags & WHERE_OR_SUBCLAUSE)==0
- ){
- int ws = pLoop->wsFlags;
- if( pWInfo->eOnePass==ONEPASS_OFF && (ws & WHERE_IDX_ONLY)==0 ){
- sqlite3VdbeAddOp1(v, OP_Close, pTabItem->iCursor);
- }
- if( (ws & WHERE_INDEXED)!=0
- && (ws & (WHERE_IPK|WHERE_AUTO_INDEX))==0
- && pLevel->iIdxCur!=pWInfo->aiCurOnePass[1]
- ){
- sqlite3VdbeAddOp1(v, OP_Close, pLevel->iIdxCur);
- }
- }
-#endif
-
/* If this scan uses an index, make VDBE code substitutions to read data
** from the index instead of from the table where possible. In some cases
** this optimization prevents the table from ever being read, which can
@@ -6261,6 +6298,11 @@ void sqlite3WhereEnd(WhereInfo *pWInfo){
}
}
+ /* The "break" point is here, just past the end of the outer loop.
+ ** Set it.
+ */
+ sqlite3VdbeResolveLabel(v, pWInfo->iBreak);
+
/* Final cleanup
*/
if( pWInfo->pExprMods ) whereUndoExprMods(pWInfo);
diff --git a/src/whereInt.h b/src/whereInt.h
index e0f44d6ba..acc9ec3dd 100644
--- a/src/whereInt.h
+++ b/src/whereInt.h
@@ -33,6 +33,7 @@ typedef struct WhereScan WhereScan;
typedef struct WhereOrCost WhereOrCost;
typedef struct WhereOrSet WhereOrSet;
typedef struct WhereMemBlock WhereMemBlock;
+typedef struct WhereRightJoin WhereRightJoin;
/*
** This object is a header on a block of allocated memory that will be
@@ -44,6 +45,16 @@ struct WhereMemBlock {
};
/*
+** Extra information attached to a WhereLevel that is a RIGHT JOIN.
+*/
+struct WhereRightJoin {
+ int iMatch; /* Cursor used to determine prior matched rows */
+ int regBloom; /* Bloom filter for iRJMatch */
+ int regReturn; /* Return register for the interior subroutine */
+ int addrSubrtn; /* Starting address for the interior subroutine */
+};
+
+/*
** This object contains information needed to implement a single nested
** loop in WHERE clause.
**
@@ -75,6 +86,7 @@ struct WhereLevel {
int addrLikeRep; /* LIKE range processing address */
#endif
int regFilter; /* Bloom filter */
+ WhereRightJoin *pRJ; /* Extra information for RIGHT JOIN */
u8 iFrom; /* Which entry in the FROM clause */
u8 op, p3, p5; /* Opcode, P3 & P5 of the opcode that ends the loop */
int p1, p2; /* Operands of the opcode used to end the loop */
@@ -552,6 +564,11 @@ Bitmask sqlite3WhereCodeOneLoopStart(
WhereLevel *pLevel, /* The current level pointer */
Bitmask notReady /* Which tables are currently available */
);
+SQLITE_NOINLINE void sqlite3WhereRightJoinLoop(
+ WhereInfo *pWInfo,
+ int iLevel,
+ WhereLevel *pLevel
+);
/* whereexpr.c: */
void sqlite3WhereClauseInit(WhereClause*,WhereInfo*);
diff --git a/src/wherecode.c b/src/wherecode.c
index 158fb806c..9ba987ca6 100644
--- a/src/wherecode.c
+++ b/src/wherecode.c
@@ -204,6 +204,9 @@ int sqlite3WhereExplainOneScan(
pLoop->u.vtab.idxNum, pLoop->u.vtab.idxStr);
}
#endif
+ if( pItem->fg.jointype & JT_LEFT ){
+ sqlite3_str_appendf(&str, " LEFT-JOIN");
+ }
#ifdef SQLITE_EXPLAIN_ESTIMATED_ROWS
if( pLoop->nOut>=10 ){
sqlite3_str_appendf(&str, " (~%llu rows)",
@@ -1150,7 +1153,7 @@ static void codeDeferredSeek(
pWInfo->bDeferredSeek = 1;
sqlite3VdbeAddOp3(v, OP_DeferredSeek, iIdxCur, 0, iCur);
- if( (pWInfo->wctrlFlags & WHERE_OR_SUBCLAUSE)
+ if( (pWInfo->wctrlFlags & (WHERE_OR_SUBCLAUSE|WHERE_RIGHT_JOIN))
&& DbMaskAllZero(sqlite3ParseToplevel(pParse)->writeMask)
){
int i;
@@ -1502,7 +1505,7 @@ Bitmask sqlite3WhereCodeOneLoopStart(
** initialize a memory cell that records if this table matches any
** row of the left table of the join.
*/
- assert( (pWInfo->wctrlFlags & WHERE_OR_SUBCLAUSE)
+ assert( (pWInfo->wctrlFlags & (WHERE_OR_SUBCLAUSE|WHERE_RIGHT_JOIN))
|| pLevel->iFrom>0 || (pTabItem[0].fg.jointype & JT_LEFT)==0
);
if( pLevel->iFrom>0 && (pTabItem[0].fg.jointype & JT_LEFT)!=0 ){
@@ -1513,7 +1516,10 @@ Bitmask sqlite3WhereCodeOneLoopStart(
/* Compute a safe address to jump to if we discover that the table for
** this loop is empty and can never contribute content. */
- for(j=iLevel; j>0 && pWInfo->a[j].iLeftJoin==0; j--){}
+ for(j=iLevel; j>0; j--){
+ if( pWInfo->a[j].iLeftJoin ) break;
+ if( pWInfo->a[j].pRJ ) break;
+ }
addrHalt = pWInfo->a[j].addrBrk;
/* Special case of a FROM clause subquery implemented as a co-routine */
@@ -2140,7 +2146,7 @@ Bitmask sqlite3WhereCodeOneLoopStart(
/* Seek the table cursor, if required */
omitTable = (pLoop->wsFlags & WHERE_IDX_ONLY)!=0
- && (pWInfo->wctrlFlags & WHERE_OR_SUBCLAUSE)==0;
+ && (pWInfo->wctrlFlags & (WHERE_OR_SUBCLAUSE|WHERE_RIGHT_JOIN))==0;
if( omitTable ){
/* pIdx is a covering index. No need to access the main table. */
}else if( HasRowid(pIdx->pTable) ){
@@ -2174,7 +2180,7 @@ Bitmask sqlite3WhereCodeOneLoopStart(
** move forward to the next index.
** https://sqlite.org/src/info/4e8e4857d32d401f
*/
- if( (pWInfo->wctrlFlags & WHERE_OR_SUBCLAUSE)==0 ){
+ if( (pWInfo->wctrlFlags & (WHERE_OR_SUBCLAUSE|WHERE_RIGHT_JOIN))==0 ){
whereIndexExprTrans(pIdx, iCur, iIdxCur, pWInfo);
}
@@ -2193,7 +2199,7 @@ Bitmask sqlite3WhereCodeOneLoopStart(
/* The following assert() is not a requirement, merely an observation:
** The OR-optimization doesn't work for the right hand table of
** a LEFT JOIN: */
- assert( (pWInfo->wctrlFlags & WHERE_OR_SUBCLAUSE)==0 );
+ assert( (pWInfo->wctrlFlags & (WHERE_OR_SUBCLAUSE|WHERE_RIGHT_JOIN))==0 );
}
/* Record the instruction used to terminate the loop. */
@@ -2605,7 +2611,9 @@ Bitmask sqlite3WhereCodeOneLoopStart(
}
pE = pTerm->pExpr;
assert( pE!=0 );
- if( (pTabItem->fg.jointype&JT_LEFT) && !ExprHasProperty(pE,EP_FromJoin) ){
+ if( (pTabItem->fg.jointype & (JT_LEFT|JT_LTORJ))
+ && !ExprHasProperty(pE,EP_FromJoin)
+ ){
continue;
}
@@ -2667,7 +2675,7 @@ Bitmask sqlite3WhereCodeOneLoopStart(
if( (pTerm->eOperator & (WO_EQ|WO_IS))==0 ) continue;
if( (pTerm->eOperator & WO_EQUIV)==0 ) continue;
if( pTerm->leftCursor!=iCur ) continue;
- if( pTabItem->fg.jointype & JT_LEFT ) continue;
+ if( pTabItem->fg.jointype & (JT_LEFT|JT_LTORJ) ) continue;
pE = pTerm->pExpr;
#ifdef WHERETRACE_ENABLED /* 0x800 */
if( sqlite3WhereTrace & 0x800 ){
@@ -2698,6 +2706,47 @@ Bitmask sqlite3WhereCodeOneLoopStart(
pAlt->wtFlags |= TERM_CODED;
}
+ /* For a RIGHT OUTER JOIN, record the fact that the current row has
+ ** been matched at least once.
+ */
+ if( pLevel->pRJ ){
+ Table *pTab;
+ int nPk;
+ int r;
+ int jmp1 = 0;
+ WhereRightJoin *pRJ = pLevel->pRJ;
+
+ /* pTab is the right-hand table of the RIGHT JOIN. Generate code that
+ ** will record that the current row of that table has been matched at
+ ** least once. This is accomplished by storing the PK for the row in
+ ** both the iMatch index and the regBloom Bloom filter.
+ */
+ pTab = pWInfo->pTabList->a[pLevel->iFrom].pTab;
+ if( HasRowid(pTab) ){
+ r = sqlite3GetTempRange(pParse, 2);
+ sqlite3ExprCodeGetColumnOfTable(v, pTab, pLevel->iTabCur, -1, r+1);
+ nPk = 1;
+ }else{
+ int iPk;
+ Index *pPk = sqlite3PrimaryKeyIndex(pTab);
+ nPk = pPk->nKeyCol;
+ r = sqlite3GetTempRange(pParse, nPk+1);
+ for(iPk=0; iPk<nPk; iPk++){
+ int iCol = pPk->aiColumn[iPk];
+ sqlite3ExprCodeGetColumnOfTable(v, pTab, iCur, iCol,r+1+iPk);
+ }
+ }
+ jmp1 = sqlite3VdbeAddOp4Int(v, OP_Found, pRJ->iMatch, 0, r+1, nPk);
+ VdbeCoverage(v);
+ VdbeComment((v, "match against %s", pTab->zName));
+ sqlite3VdbeAddOp3(v, OP_MakeRecord, r+1, nPk, r);
+ sqlite3VdbeAddOp4Int(v, OP_IdxInsert, pRJ->iMatch, r, r+1, nPk);
+ sqlite3VdbeAddOp4Int(v, OP_FilterAdd, pRJ->regBloom, 0, r+1, nPk);
+ sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT);
+ sqlite3VdbeJumpHere(v, jmp1);
+ sqlite3ReleaseTempRange(pParse, r, nPk+1);
+ }
+
/* For a LEFT OUTER JOIN, generate code that will record the fact that
** at least one row of the right table has matched the left table.
*/
@@ -2713,12 +2762,27 @@ Bitmask sqlite3WhereCodeOneLoopStart(
assert( pWInfo->untestedTerms );
continue;
}
+ if( pTabItem->fg.jointype & JT_LTORJ ) continue;
assert( pTerm->pExpr );
sqlite3ExprIfFalse(pParse, pTerm->pExpr, addrCont, SQLITE_JUMPIFNULL);
pTerm->wtFlags |= TERM_CODED;
}
}
+ if( pLevel->pRJ ){
+ /* Create a subroutine used to process all interior loops and code
+ ** of the RIGHT JOIN. During normal operation, the subroutine will
+ ** be in-line with the rest of the code. But at the end, a separate
+ ** loop will run that invokes this subroutine for unmatched rows
+ ** of pTab, with all tables to left begin set to NULL.
+ */
+ WhereRightJoin *pRJ = pLevel->pRJ;
+ sqlite3VdbeAddOp2(v, OP_BeginSubrtn, 0, pRJ->regReturn);
+ pRJ->addrSubrtn = sqlite3VdbeCurrentAddr(v);
+ assert( pParse->withinRJSubrtn < 255 );
+ pParse->withinRJSubrtn++;
+ }
+
#if WHERETRACE_ENABLED /* 0x20800 */
if( sqlite3WhereTrace & 0x20000 ){
sqlite3DebugPrintf("All WHERE-clause terms after coding level %d:\n",
@@ -2732,3 +2796,83 @@ Bitmask sqlite3WhereCodeOneLoopStart(
#endif
return pLevel->notReady;
}
+
+/*
+** Generate the code for the loop that finds all non-matched terms
+** for a RIGHT JOIN.
+*/
+SQLITE_NOINLINE void sqlite3WhereRightJoinLoop(
+ WhereInfo *pWInfo,
+ int iLevel,
+ WhereLevel *pLevel
+){
+ Parse *pParse = pWInfo->pParse;
+ Vdbe *v = pParse->pVdbe;
+ WhereRightJoin *pRJ = pLevel->pRJ;
+ Expr *pSubWhere = 0;
+ WhereClause *pWC = &pWInfo->sWC;
+ WhereInfo *pSubWInfo;
+ WhereLoop *pLoop = pLevel->pWLoop;
+ SrcItem *pTabItem = &pWInfo->pTabList->a[pLevel->iFrom];
+ SrcList sFrom;
+ Bitmask mAll = 0;
+ int k;
+
+ ExplainQueryPlan((pParse, 1, "RIGHT-JOIN %s", pTabItem->pTab->zName));
+ for(k=0; k<iLevel; k++){
+ int iIdxCur;
+ mAll |= pWInfo->a[k].pWLoop->maskSelf;
+ sqlite3VdbeAddOp1(v, OP_NullRow, pWInfo->a[k].iTabCur);
+ iIdxCur = pWInfo->a[k].iIdxCur;
+ if( iIdxCur ){
+ sqlite3VdbeAddOp1(v, OP_NullRow, iIdxCur);
+ }
+ }
+ if( (pTabItem->fg.jointype & JT_LTORJ)==0 ){
+ mAll |= pLoop->maskSelf;
+ for(k=0; k<pWC->nTerm; k++){
+ WhereTerm *pTerm = &pWC->a[k];
+ if( pTerm->wtFlags & TERM_VIRTUAL ) break;
+ if( pTerm->prereqAll & ~mAll ) continue;
+ if( ExprHasProperty(pTerm->pExpr, EP_FromJoin|EP_InnerJoin) ) continue;
+ pSubWhere = sqlite3ExprAnd(pParse, pSubWhere,
+ sqlite3ExprDup(pParse->db, pTerm->pExpr, 0));
+ }
+ }
+ sFrom.nSrc = 1;
+ sFrom.nAlloc = 1;
+ memcpy(&sFrom.a[0], pTabItem, sizeof(SrcItem));
+ sFrom.a[0].fg.jointype = 0;
+ pSubWInfo = sqlite3WhereBegin(pParse, &sFrom, pSubWhere, 0, 0, 0,
+ WHERE_RIGHT_JOIN, 0);
+ if( pSubWInfo ){
+ int iCur = pLevel->iTabCur;
+ int r = ++pParse->nMem;
+ int nPk;
+ int jmp;
+ int addrCont = sqlite3WhereContinueLabel(pSubWInfo);
+ Table *pTab = pTabItem->pTab;
+ if( HasRowid(pTab) ){
+ sqlite3ExprCodeGetColumnOfTable(v, pTab, iCur, -1, r);
+ nPk = 1;
+ }else{
+ int iPk;
+ Index *pPk = sqlite3PrimaryKeyIndex(pTab);
+ nPk = pPk->nKeyCol;
+ pParse->nMem += nPk - 1;
+ for(iPk=0; iPk<nPk; iPk++){
+ int iCol = pPk->aiColumn[iPk];
+ sqlite3ExprCodeGetColumnOfTable(v, pTab, iCur, iCol,r+iPk);
+ }
+ }
+ jmp = sqlite3VdbeAddOp4Int(v, OP_Filter, pRJ->regBloom, 0, r, nPk);
+ VdbeCoverage(v);
+ sqlite3VdbeAddOp4Int(v, OP_Found, pRJ->iMatch, addrCont, r, nPk);
+ VdbeCoverage(v);
+ sqlite3VdbeJumpHere(v, jmp);
+ sqlite3VdbeAddOp2(v, OP_Gosub, pRJ->regReturn, pRJ->addrSubrtn);
+ sqlite3WhereEnd(pSubWInfo);
+ }
+ sqlite3ExprDelete(pParse->db, pSubWhere);
+ ExplainQueryPlanPop(pParse);
+}
diff --git a/src/whereexpr.c b/src/whereexpr.c
index adcb90d0b..90c344806 100644
--- a/src/whereexpr.c
+++ b/src/whereexpr.c
@@ -1809,6 +1809,7 @@ void sqlite3WhereTabFuncArgs(
if( pArgs==0 ) return;
for(j=k=0; j<pArgs->nExpr; j++){
Expr *pRhs;
+ u32 joinType;
while( k<pTab->nCol && (pTab->aCol[k].colFlags & COLFLAG_HIDDEN)==0 ){k++;}
if( k>=pTab->nCol ){
sqlite3ErrorMsg(pParse, "too many arguments on %s() - max %d",
@@ -1825,9 +1826,12 @@ void sqlite3WhereTabFuncArgs(
pRhs = sqlite3PExpr(pParse, TK_UPLUS,
sqlite3ExprDup(pParse->db, pArgs->a[j].pExpr, 0), 0);
pTerm = sqlite3PExpr(pParse, TK_EQ, pColRef, pRhs);
- if( pItem->fg.jointype & JT_LEFT ){
- sqlite3SetJoinExpr(pTerm, pItem->iCursor);
+ if( pItem->fg.jointype & (JT_LEFT|JT_LTORJ) ){
+ joinType = EP_FromJoin;
+ }else{
+ joinType = EP_InnerJoin;
}
+ sqlite3SetJoinExpr(pTerm, pItem->iCursor, joinType);
whereClauseInsert(pWC, pTerm, TERM_DYNAMIC);
}
}
generated by cgit v1.2.3 (git 2.25.1) at 2025-07-19 05:47:31 +0000