From a76ac88af86bb5a3753b7fec6c86bfcfaf64f439 Mon Sep 17 00:00:00 2001 From: drh <> Date: Fri, 8 Apr 2022 19:20:12 +0000 Subject: Preliminary code to support RIGHT JOIN. Everything seems to work, except that the code to compute the unmatched rows for the RIGHT JOIN has not yet been added, so the result of a RIGHT JOIN is currently the same as an INNER JOIN. FossilOrigin-Name: 415abd6731b8e8a605adabfa6066c8a852a8531c300df41325d5f7e75cae5a70 --- src/build.c | 14 +++++++++- src/resolve.c | 4 +-- src/select.c | 10 +++----- src/sqliteInt.h | 15 ++++++----- src/treeview.c | 6 ++++- src/where.c | 79 +++++++++++++++++++++++++++------------------------------ src/wherecode.c | 6 +++-- src/whereexpr.c | 2 +- 8 files changed, 72 insertions(+), 64 deletions(-) (limited to 'src') diff --git a/src/build.c b/src/build.c index da862fa70..7181011c3 100644 --- a/src/build.c +++ b/src/build.c @@ -5071,10 +5071,22 @@ void sqlite3SrcListFuncArgs(Parse *pParse, SrcList *p, ExprList *pList){ void sqlite3SrcListShiftJoinType(SrcList *p){ if( p ){ int i; + u8 allFlags = 0; for(i=p->nSrc-1; i>0; i--){ - p->a[i].fg.jointype = p->a[i-1].fg.jointype; + allFlags |= p->a[i].fg.jointype = p->a[i-1].fg.jointype; } 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/resolve.c b/src/resolve.c index 30785ca70..ac00564da 100644 --- a/src/resolve.c +++ b/src/resolve.c @@ -367,9 +367,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; diff --git a/src/select.c b/src/select.c index 7ac23bd38..b9321aece 100644 --- a/src/select.c +++ b/src/select.c @@ -301,10 +301,6 @@ int sqlite3JoinType(Parse *pParse, Token *pA, Token *pB, Token *pC){ sqlite3ErrorMsg(pParse, "unknown or unsupported 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; } @@ -6589,7 +6585,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; } @@ -6741,8 +6737,8 @@ int sqlite3Select( */ 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) */ ){ /* Implement a co-routine that will return a single row of the result ** set on each invocation. diff --git a/src/sqliteInt.h b/src/sqliteInt.h index ead20c9a2..264a9d373 100644 --- a/src/sqliteInt.h +++ b/src/sqliteInt.h @@ -3132,13 +3132,14 @@ 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 */ +#define JT_ERROR 0x80 /* unknown or unsupported join type */ /* diff --git a/src/treeview.c b/src/treeview.c index 91aff8ddf..f32064cd8 100644 --- a/src/treeview.c +++ b/src/treeview.c @@ -144,8 +144,12 @@ 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"); } diff --git a/src/where.c b/src/where.c index f08c350b0..60e314e75 100644 --- a/src/where.c +++ b/src/where.c @@ -732,13 +732,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; @@ -808,7 +808,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, @@ -1081,7 +1082,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; @@ -1154,9 +1155,10 @@ static sqlite3_index_info *allocateIndexInfo( assert( pTerm->u.x.leftColumnnCol ); /* 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; @@ -2600,10 +2602,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; } @@ -2810,9 +2813,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; @@ -4114,9 +4118,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; @@ -4125,7 +4129,7 @@ static int whereLoopAddAll(WhereLoopBuilder *pBuilder){ if( IsVirtual(pItem->pTab) ){ SrcItem *p; for(p=&pItem[1]; pfg.jointype & (JT_LEFT|JT_CROSS)) ){ + if( mUnusable || (p->fg.jointype & (JT_OUTER|JT_CROSS)) ){ mUnusable |= sqlite3WhereGetMask(&pWInfo->sMaskSet, p->iCursor); } } @@ -5844,6 +5848,10 @@ WhereInfo *sqlite3WhereBegin( } } if( iDb>=0 ) sqlite3CodeVerifySchema(pParse, iDb); + if( pTabItem->fg.jointype & JT_RIGHT ){ + VdbeModuleComment((v, "TO-DO: Setup for the RIGHT JOIN of %s", + pTab->zName)); + } } pWInfo->iTop = sqlite3VdbeCurrentAddr(v); if( db->mallocFailed ) goto whereBeginError; @@ -6091,11 +6099,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; inLevel; i++, pLevel++){ int k, last; @@ -6106,6 +6109,16 @@ 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( pTabItem->fg.jointype & JT_RIGHT ){ + VdbeModuleComment((v, "TO-DO: RIGHT JOIN post-processing for %s", + pTab->zName)); + + } + /* 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. @@ -6117,29 +6130,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 @@ -6240,6 +6230,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/wherecode.c b/src/wherecode.c index bab514a69..d349a9402 100644 --- a/src/wherecode.c +++ b/src/wherecode.c @@ -2604,7 +2604,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; } @@ -2666,7 +2668,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 ){ diff --git a/src/whereexpr.c b/src/whereexpr.c index 26a521fff..b74164d1f 100644 --- a/src/whereexpr.c +++ b/src/whereexpr.c @@ -1831,7 +1831,7 @@ 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 ){ + if( pItem->fg.jointype & (JT_LEFT|JT_LTORJ) ){ sqlite3SetJoinExpr(pTerm, pItem->iCursor); } whereClauseInsert(pWC, pTerm, TERM_DYNAMIC); -- cgit v1.2.3 From c2308ad2a0cb4f6d957518c615d308ed79b42d04 Mon Sep 17 00:00:00 2001 From: drh <> Date: Sat, 9 Apr 2022 03:16:26 +0000 Subject: A few bits and bobs of code generation toward getting RIGHT JOIN to work. Much more remains to do. FossilOrigin-Name: 55b4543122646997d928598343bc467c993f971e86e9037c85430cc948750576 --- src/where.c | 18 ++++++++++++++++-- src/whereInt.h | 1 + src/wherecode.c | 35 +++++++++++++++++++++++++++++++++++ 3 files changed, 52 insertions(+), 2 deletions(-) (limited to 'src') diff --git a/src/where.c b/src/where.c index 338501090..a5fab2cd5 100644 --- a/src/where.c +++ b/src/where.c @@ -5870,8 +5870,22 @@ WhereInfo *sqlite3WhereBegin( } if( iDb>=0 ) sqlite3CodeVerifySchema(pParse, iDb); if( pTabItem->fg.jointype & JT_RIGHT ){ - VdbeModuleComment((v, "TO-DO: Setup for the RIGHT JOIN of %s", - pTab->zName)); + assert( pTab==pTabItem->pTab ); + pLevel->iRJMatch = pParse->nTab++; + if( HasRowid(pTab) ){ + KeyInfo *pInfo; + sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pLevel->iRJMatch, 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, pLevel->iRJMatch, pPk->nKeyCol); + sqlite3VdbeSetP4KeyInfo(pParse, pPk); + } } } pWInfo->iTop = sqlite3VdbeCurrentAddr(v); diff --git a/src/whereInt.h b/src/whereInt.h index e0f44d6ba..df62db4f1 100644 --- a/src/whereInt.h +++ b/src/whereInt.h @@ -75,6 +75,7 @@ struct WhereLevel { int addrLikeRep; /* LIKE range processing address */ #endif int regFilter; /* Bloom filter */ + int iRJMatch; /* Cursor or rowset used for matched RIGHT JOIN rows */ 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 */ diff --git a/src/wherecode.c b/src/wherecode.c index 74873de09..5b8ed8934 100644 --- a/src/wherecode.c +++ b/src/wherecode.c @@ -2721,6 +2721,41 @@ Bitmask sqlite3WhereCodeOneLoopStart( } } + /* For a RIGHT OUTER JOIN, record the fact that the current row has + ** been matched at least once. + */ + if( pLevel->iRJMatch ){ + Table *pTab; + int nPk; + int r; + int jmp1 = 0; + + 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; iPkaiColumn[iPk]; + sqlite3ExprCodeGetColumnOfTable(v, pTab, iCur, iCol,r+1+iPk); + } + } + jmp1 = sqlite3VdbeAddOp4Int(v, OP_Found, pLevel->iRJMatch, 0, r+1, nPk); + VdbeCoverage(v); + sqlite3VdbeAddOp3(v, OP_MakeRecord, r+1, nPk, r); + sqlite3VdbeAddOp4Int(v, OP_IdxInsert, pLevel->iRJMatch, r, r+1, nPk); + sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT); + sqlite3VdbeJumpHere(v, jmp1); + + /* Release the array of temp registers */ + sqlite3ReleaseTempRange(pParse, r, nPk+1); + } + #if WHERETRACE_ENABLED /* 0x20800 */ if( sqlite3WhereTrace & 0x20000 ){ sqlite3DebugPrintf("All WHERE-clause terms after coding level %d:\n", -- cgit v1.2.3 From 7c1734b09e436176c2aac461e835c29532403ad4 Mon Sep 17 00:00:00 2001 From: drh <> Date: Sat, 9 Apr 2022 12:27:20 +0000 Subject: The interior of the RIGHT JOIN loop is now a subroutine. FossilOrigin-Name: 549f5a7ee639de80f049445002f58e93c805f9a3d3db1987ec9d139ccef4805e --- src/where.c | 19 +++++++++++++++---- src/whereInt.h | 13 ++++++++++++- src/wherecode.c | 24 +++++++++++++++++++----- 3 files changed, 46 insertions(+), 10 deletions(-) (limited to 'src') diff --git a/src/where.c b/src/where.c index a5fab2cd5..afcecd77b 100644 --- a/src/where.c +++ b/src/where.c @@ -5869,12 +5869,18 @@ WhereInfo *sqlite3WhereBegin( } } if( iDb>=0 ) sqlite3CodeVerifySchema(pParse, iDb); - if( pTabItem->fg.jointype & JT_RIGHT ){ + 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; assert( pTab==pTabItem->pTab ); - pLevel->iRJMatch = pParse->nTab++; if( HasRowid(pTab) ){ KeyInfo *pInfo; - sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pLevel->iRJMatch, 1); + sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pRJ->iMatch, 1); pInfo = sqlite3KeyInfoAlloc(pParse->db, 1, 0); if( pInfo ){ pInfo->aColl[0] = 0; @@ -5883,7 +5889,7 @@ WhereInfo *sqlite3WhereBegin( } }else{ Index *pPk = sqlite3PrimaryKeyIndex(pTab); - sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pLevel->iRJMatch, pPk->nKeyCol); + sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pRJ->iMatch, pPk->nKeyCol); sqlite3VdbeSetP4KeyInfo(pParse, pPk); } } @@ -5999,6 +6005,11 @@ void sqlite3WhereEnd(WhereInfo *pWInfo){ for(i=pWInfo->nLevel-1; i>=0; i--){ int addr; pLevel = &pWInfo->a[i]; + if( pLevel->pRJ ){ + WhereRightJoin *pRJ = (WhereRightJoin*)pLevel->pRJ; + sqlite3VdbeChangeP2(v, pRJ->addrSubrtn-1, sqlite3VdbeCurrentAddr(v)); + sqlite3VdbeAddOp2(v, OP_Return, pRJ->regReturn, pRJ->addrSubrtn); + } pLoop = pLevel->pWLoop; if( pLevel->op!=OP_Noop ){ #ifndef SQLITE_DISABLE_SKIPAHEAD_DISTINCT diff --git a/src/whereInt.h b/src/whereInt.h index df62db4f1..7b5be3011 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 @@ -43,6 +44,16 @@ struct WhereMemBlock { u8 sz; /* Bytes of space */ }; +/* +** 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,7 +86,7 @@ struct WhereLevel { int addrLikeRep; /* LIKE range processing address */ #endif int regFilter; /* Bloom filter */ - int iRJMatch; /* Cursor or rowset used for matched RIGHT JOIN rows */ + 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 */ diff --git a/src/wherecode.c b/src/wherecode.c index 5b8ed8934..853646c71 100644 --- a/src/wherecode.c +++ b/src/wherecode.c @@ -2724,12 +2724,18 @@ Bitmask sqlite3WhereCodeOneLoopStart( /* For a RIGHT OUTER JOIN, record the fact that the current row has ** been matched at least once. */ - if( pLevel->iRJMatch ){ + 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); @@ -2745,15 +2751,23 @@ Bitmask sqlite3WhereCodeOneLoopStart( sqlite3ExprCodeGetColumnOfTable(v, pTab, iCur, iCol,r+1+iPk); } } - jmp1 = sqlite3VdbeAddOp4Int(v, OP_Found, pLevel->iRJMatch, 0, r+1, nPk); + jmp1 = sqlite3VdbeAddOp4Int(v, OP_Found, pRJ->iMatch, 0, r+1, nPk); VdbeCoverage(v); sqlite3VdbeAddOp3(v, OP_MakeRecord, r+1, nPk, r); - sqlite3VdbeAddOp4Int(v, OP_IdxInsert, pLevel->iRJMatch, r, r+1, nPk); + 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); - - /* Release the array of temp registers */ sqlite3ReleaseTempRange(pParse, r, nPk+1); + + /* 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. + */ + sqlite3VdbeAddOp2(v, OP_BeginSubrtn, 0, pRJ->regReturn); + pRJ->addrSubrtn = sqlite3VdbeCurrentAddr(v); } #if WHERETRACE_ENABLED /* 0x20800 */ -- cgit v1.2.3 From 86c1beb402ef54c1427d5f2e6bbc38ebe1249843 Mon Sep 17 00:00:00 2001 From: drh <> Date: Sat, 9 Apr 2022 14:48:35 +0000 Subject: Add byte-code that computes unmatched rows on the right table of a RIGHT JOIN. Compiles, and the code looks semi-reasonable, but still does not run. Incremental check-in. FossilOrigin-Name: 2db5a498e74241dd19ef51c601f1a2b3b687faed3e1be2d1e3ada737406ac8e9 --- src/where.c | 51 ++++++++++++++++++++++++++++++++++++++++++++++----- 1 file changed, 46 insertions(+), 5 deletions(-) (limited to 'src') diff --git a/src/where.c b/src/where.c index afcecd77b..eec8ccb98 100644 --- a/src/where.c +++ b/src/where.c @@ -6006,7 +6006,9 @@ void sqlite3WhereEnd(WhereInfo *pWInfo){ int addr; pLevel = &pWInfo->a[i]; if( pLevel->pRJ ){ - WhereRightJoin *pRJ = (WhereRightJoin*)pLevel->pRJ; + /* Terminate the subroutine that forms the interior of the loop of + ** the RIGHT JOIN table */ + WhereRightJoin *pRJ = pLevel->pRJ; sqlite3VdbeChangeP2(v, pRJ->addrSubrtn-1, sqlite3VdbeCurrentAddr(v)); sqlite3VdbeAddOp2(v, OP_Return, pRJ->regReturn, pRJ->addrSubrtn); } @@ -6159,10 +6161,49 @@ void sqlite3WhereEnd(WhereInfo *pWInfo){ ** unmatched rows of the right operand of the RIGHT JOIN with ** all of the columns of the left operand set to NULL. */ - if( pTabItem->fg.jointype & JT_RIGHT ){ - VdbeModuleComment((v, "TO-DO: RIGHT JOIN post-processing for %s", - pTab->zName)); - + if( pLevel->pRJ ){ + WhereRightJoin *pRJ = pLevel->pRJ; + Expr *pSubWhere = 0; + WhereClause *pWC = &pWInfo->sWC; + WhereInfo *pSubWInfo; + SrcList sFrom; + for(k=0; knTerm; k++){ + WhereTerm *pTerm = &pWC->a[i]; + if( pTerm->wtFlags & TERM_VIRTUAL ) break; + if( pTerm->prereqAll & ~pLoop->maskSelf ) continue; + pSubWhere = sqlite3ExprAnd(pParse, pSubWhere, + sqlite3ExprDup(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_OR_SUBCLAUSE, 0); + if( pSubWInfo ){ + int iCur = pLevel->iTabCur; + int r = ++pParse->nMem; + int nPk; + int addrCont = sqlite3WhereContinueLabel(pSubWInfo); + 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; iPkaiColumn[iPk]; + sqlite3ExprCodeGetColumnOfTable(v, pTab, iCur, iCol,r+iPk); + } + } + sqlite3VdbeAddOp4Int(v, OP_Filter, pRJ->regBloom, addrCont, r, nPk); + sqlite3VdbeAddOp4Int(v, OP_Found, pRJ->iMatch, addrCont, r, nPk); + sqlite3VdbeAddOp2(v, OP_Gosub, pRJ->regReturn, pRJ->addrSubrtn); + sqlite3WhereEnd(pSubWInfo); + } + sqlite3ExprDelete(pParse->db, pSubWhere); } /* For a co-routine, change all OP_Column references to the table of -- cgit v1.2.3 From de24dd706e3f5e23389ad314a8941af8f56c8a2f Mon Sep 17 00:00:00 2001 From: drh <> Date: Sat, 9 Apr 2022 18:48:11 +0000 Subject: Bug fixes. A basic FULL OUTER JOIN now works. FossilOrigin-Name: 34bbeeb77bd530b2b1f0390e9e552f65ae35f09a74d80a09dd327e64f9be51a1 --- src/where.c | 6 ++++-- 1 file changed, 4 insertions(+), 2 deletions(-) (limited to 'src') diff --git a/src/where.c b/src/where.c index eec8ccb98..bec9da8db 100644 --- a/src/where.c +++ b/src/where.c @@ -6009,7 +6009,7 @@ void sqlite3WhereEnd(WhereInfo *pWInfo){ /* Terminate the subroutine that forms the interior of the loop of ** the RIGHT JOIN table */ WhereRightJoin *pRJ = pLevel->pRJ; - sqlite3VdbeChangeP2(v, pRJ->addrSubrtn-1, sqlite3VdbeCurrentAddr(v)); + sqlite3VdbeChangeP1(v, pRJ->addrSubrtn-1, sqlite3VdbeCurrentAddr(v)); sqlite3VdbeAddOp2(v, OP_Return, pRJ->regReturn, pRJ->addrSubrtn); } pLoop = pLevel->pWLoop; @@ -6184,6 +6184,7 @@ void sqlite3WhereEnd(WhereInfo *pWInfo){ int iCur = pLevel->iTabCur; int r = ++pParse->nMem; int nPk; + int jmp; int addrCont = sqlite3WhereContinueLabel(pSubWInfo); if( HasRowid(pTab) ){ sqlite3ExprCodeGetColumnOfTable(v, pTab, iCur, -1, r); @@ -6198,8 +6199,9 @@ void sqlite3WhereEnd(WhereInfo *pWInfo){ sqlite3ExprCodeGetColumnOfTable(v, pTab, iCur, iCol,r+iPk); } } - sqlite3VdbeAddOp4Int(v, OP_Filter, pRJ->regBloom, addrCont, r, nPk); + jmp = sqlite3VdbeAddOp4Int(v, OP_Filter, pRJ->regBloom, 0, r, nPk); sqlite3VdbeAddOp4Int(v, OP_Found, pRJ->iMatch, addrCont, r, nPk); + sqlite3VdbeJumpHere(v, jmp); sqlite3VdbeAddOp2(v, OP_Gosub, pRJ->regReturn, pRJ->addrSubrtn); sqlite3WhereEnd(pSubWInfo); } -- cgit v1.2.3 From a20c71e9e8300d9c60a83fb3d47b600c556bd952 Mon Sep 17 00:00:00 2001 From: drh <> Date: Sun, 10 Apr 2022 16:13:37 +0000 Subject: More RIGHT JOIN test cases and a bug fix. FossilOrigin-Name: 19e8ad690a140ca40838bf31a377c19010fcbbc2554a4f1746737543043e334b --- src/where.c | 6 ++++-- 1 file changed, 4 insertions(+), 2 deletions(-) (limited to 'src') diff --git a/src/where.c b/src/where.c index bec9da8db..4216829e2 100644 --- a/src/where.c +++ b/src/where.c @@ -5766,8 +5766,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; -- cgit v1.2.3 From e21e36dd2cf71d4164bc188774fab5e4d2976cb6 Mon Sep 17 00:00:00 2001 From: drh <> Date: Sun, 10 Apr 2022 17:14:48 +0000 Subject: More test cases and bug fixes. FossilOrigin-Name: 140e97fde94fdc3babdd456ce1b22900ead0e40e2afe63d89d21ccdbf141b607 --- src/where.c | 11 +++++++++-- 1 file changed, 9 insertions(+), 2 deletions(-) (limited to 'src') diff --git a/src/where.c b/src/where.c index 4216829e2..56c3a0171 100644 --- a/src/where.c +++ b/src/where.c @@ -5894,6 +5894,7 @@ WhereInfo *sqlite3WhereBegin( sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pRJ->iMatch, pPk->nKeyCol); sqlite3VdbeSetP4KeyInfo(pParse, pPk); } + pLoop->wsFlags &= ~WHERE_IDX_ONLY; } } pWInfo->iTop = sqlite3VdbeCurrentAddr(v); @@ -6169,10 +6170,15 @@ void sqlite3WhereEnd(WhereInfo *pWInfo){ WhereClause *pWC = &pWInfo->sWC; WhereInfo *pSubWInfo; SrcList sFrom; + Bitmask mAll = 0; + for(k=0; k<=i; k++){ + mAll |= pWInfo->a[k].pWLoop->maskSelf; + } for(k=0; knTerm; k++){ - WhereTerm *pTerm = &pWC->a[i]; + WhereTerm *pTerm = &pWC->a[k]; if( pTerm->wtFlags & TERM_VIRTUAL ) break; - if( pTerm->prereqAll & ~pLoop->maskSelf ) continue; + if( pTerm->prereqAll & ~mAll ) continue; + if( ExprHasProperty(pTerm->pExpr, EP_FromJoin) ) continue; pSubWhere = sqlite3ExprAnd(pParse, pSubWhere, sqlite3ExprDup(db, pTerm->pExpr, 0)); } @@ -6208,6 +6214,7 @@ void sqlite3WhereEnd(WhereInfo *pWInfo){ sqlite3WhereEnd(pSubWInfo); } sqlite3ExprDelete(pParse->db, pSubWhere); + continue; } /* For a co-routine, change all OP_Column references to the table of -- cgit v1.2.3 From fcde633f7cd4e8c3de41a4f3250f45cd38f49490 Mon Sep 17 00:00:00 2001 From: drh <> Date: Sun, 10 Apr 2022 19:51:22 +0000 Subject: When the left-most table of a RIGHT JOIN is implemented as a co-routine, make sure all its columns are flushed to NULL when it finishes so that they appear to be NULL during the RIGHT JOIN post-processing. FossilOrigin-Name: f84b2beca719758979d7a5a63c3d16d5121a7518b3fbe5039af474a83dd569c2 --- src/select.c | 3 +++ 1 file changed, 3 insertions(+) (limited to 'src') diff --git a/src/select.c b/src/select.c index b9321aece..3672a7c0f 100644 --- a/src/select.c +++ b/src/select.c @@ -6755,6 +6755,9 @@ int sqlite3Select( pItem->pTab->nRowLogEst = pSub->nSelectRow; pItem->fg.viaCoroutine = 1; pItem->regResult = dest.iSdst; + if( pItem->fg.jointype & JT_LTORJ ){ + sqlite3VdbeAddOp3(v, OP_Null, 0, dest.iSdst, dest.iSdst+dest.nSdst-1); + } sqlite3VdbeEndCoroutine(v, pItem->regReturn); sqlite3VdbeJumpHere(v, addrTop-1); sqlite3ClearTempRegCache(pParse); -- cgit v1.2.3 From 62ed36bb5dd4e8a15042d9752abc4223cae3be14 Mon Sep 17 00:00:00 2001 From: drh <> Date: Sun, 10 Apr 2022 20:28:41 +0000 Subject: Minor improvements to the sqlite3SrcListShiftJoinType() routine. This started out to be an effort to convert RIGHT JOIN to LEFT JOIN if the join was on the first pair of relations, but that messes up the "*" expansion and so won't work. FossilOrigin-Name: a48902c71ed30c83de7dbd26d1c7956136c35dc53b1076bc8b1ebcba568a3fd3 --- src/build.c | 15 +++++++++++---- 1 file changed, 11 insertions(+), 4 deletions(-) (limited to 'src') diff --git a/src/build.c b/src/build.c index 7181011c3..22f7a79e0 100644 --- a/src/build.c +++ b/src/build.c @@ -5067,14 +5067,21 @@ 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; + if( p && p->nSrc>1 ){ + int i = p->nSrc-1; u8 allFlags = 0; - for(i=p->nSrc-1; i>0; i--){ + 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 -- cgit v1.2.3 From 9debb58e4801ad5737ff88fbc117a1fc06695efa Mon Sep 17 00:00:00 2001 From: drh <> Date: Sun, 10 Apr 2022 23:01:20 +0000 Subject: Revisit [f84b2beca7197589]: disallow co-routine implementations of tables that are to the left of a RIGHT JOIN, to avoid other complications. FossilOrigin-Name: cf00ebfc4b77f45ec466b0d57d7c22c7f48acab19e4e55b168eb4b53f390d887 --- src/select.c | 12 +++++------- 1 file changed, 5 insertions(+), 7 deletions(-) (limited to 'src') diff --git a/src/select.c b/src/select.c index 3672a7c0f..80b488777 100644 --- a/src/select.c +++ b/src/select.c @@ -6727,18 +6727,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_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. @@ -6755,9 +6756,6 @@ int sqlite3Select( pItem->pTab->nRowLogEst = pSub->nSelectRow; pItem->fg.viaCoroutine = 1; pItem->regResult = dest.iSdst; - if( pItem->fg.jointype & JT_LTORJ ){ - sqlite3VdbeAddOp3(v, OP_Null, 0, dest.iSdst, dest.iSdst+dest.nSdst-1); - } sqlite3VdbeEndCoroutine(v, pItem->regReturn); sqlite3VdbeJumpHere(v, addrTop-1); sqlite3ClearTempRegCache(pParse); -- cgit v1.2.3 From 529394e5c1399577be9a882b2f8be11fa1966cf5 Mon Sep 17 00:00:00 2001 From: drh <> Date: Sun, 10 Apr 2022 23:48:47 +0000 Subject: Cannot use an automatic index on the right table of a RIGHT JOIN because automatic indexes must be WHERE_IDX_ONLY, but the RIGHT JOIN post-processing does not know how to work with an index-only scan. FossilOrigin-Name: beb4401dc09fb68e85ddcf3f99598527691535d0eb7693168f440e5a5a076e3f --- src/where.c | 1 + 1 file changed, 1 insertion(+) (limited to 'src') diff --git a/src/where.c b/src/where.c index 56c3a0171..adae72a4f 100644 --- a/src/where.c +++ b/src/where.c @@ -3324,6 +3324,7 @@ static int whereLoopAddBtree( && 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 */ -- cgit v1.2.3 From 41798d5bbc742999a8e8808eff7c2e29c9d4f32f Mon Sep 17 00:00:00 2001 From: drh <> Date: Mon, 11 Apr 2022 00:21:53 +0000 Subject: Do not allow query flattening nor the push-down optimization on the right operand of a RIGHT JOIN. FossilOrigin-Name: 5aa0c9ea9cf53c13bf266278b479b2e7af3aa5c6b144bd49ff155a4eb3c23c96 --- src/select.c | 16 +++++++++++----- 1 file changed, 11 insertions(+), 5 deletions(-) (limited to 'src') diff --git a/src/select.c b/src/select.c index 80b488777..2d09085f8 100644 --- a/src/select.c +++ b/src/select.c @@ -3975,6 +3975,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. ** @@ -4073,6 +4074,9 @@ 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. +** ** ** 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 @@ -4172,14 +4176,15 @@ 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( 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; } + isLeftJoin = 1; } #ifdef SQLITE_EXTRA_IFNULLROW else if( iFrom>0 && !isAgg ){ @@ -6707,6 +6712,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) ){ -- cgit v1.2.3 From 8a28ce7bc217871f58e0ce71aa207bbcaf1b338e Mon Sep 17 00:00:00 2001 From: drh <> Date: Mon, 11 Apr 2022 00:54:30 +0000 Subject: Show the JT_LTORJ flag in TreeView debugging output. FossilOrigin-Name: 21eb44919f38abad30b75181ca8aec38b453b94dba2815caf7e946e07faa40d7 --- src/treeview.c | 3 +++ 1 file changed, 3 insertions(+) (limited to 'src') diff --git a/src/treeview.c b/src/treeview.c index f32064cd8..d2ccb545b 100644 --- a/src/treeview.c +++ b/src/treeview.c @@ -153,6 +153,9 @@ void sqlite3TreeViewSrcList(TreeView *pView, const SrcList *pSrc){ }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"); } -- cgit v1.2.3 From 79d26586710bc91822bcaf4c2838e153e62aa6c8 Mon Sep 17 00:00:00 2001 From: drh <> Date: Mon, 11 Apr 2022 10:38:28 +0000 Subject: Ensure that the JT_LTORJ flag is preserved when flattening a subquery that is on the left side of a RIGHT JOIN. FossilOrigin-Name: ccb61fb1f30e2741b19c1a0cbd2951715224852c86234a3c6a4bbd2e1187634a --- src/select.c | 4 +++- 1 file changed, 3 insertions(+), 1 deletion(-) (limited to 'src') diff --git a/src/select.c b/src/select.c index 2d09085f8..08824150e 100644 --- a/src/select.c +++ b/src/select.c @@ -4373,6 +4373,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 */ @@ -4411,10 +4412,11 @@ 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 = jointype | ltorj; /* Now begin substituting subquery result set expressions for ** references to the iParent in the outer query. -- cgit v1.2.3 From b77c07a715dacfb4ffa968c6ac26ac46bf18776c Mon Sep 17 00:00:00 2001 From: drh <> Date: Mon, 11 Apr 2022 11:59:25 +0000 Subject: Fix some comments that refer to LEFT JOIN that should refer to OUTER JOIN. No changes to code. FossilOrigin-Name: 5be5ede5cca1cd5ef863fe0feb2b4a990f4a42865281a6c2e4eb816f48847dc6 --- src/expr.c | 4 ++-- src/select.c | 2 +- 2 files changed, 3 insertions(+), 3 deletions(-) (limited to 'src') diff --git a/src/expr.c b/src/expr.c index 2c00bb498..fcef002f2 100644 --- a/src/expr.c +++ b/src/expr.c @@ -2179,7 +2179,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; @@ -5817,7 +5817,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/select.c b/src/select.c index 1c929d0b9..3e4f783a3 100644 --- a/src/select.c +++ b/src/select.c @@ -408,7 +408,7 @@ static void addWhereTerm( ** 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 -- cgit v1.2.3 From 3a6e4c59c480d0f00ba70b44529ba9925f61064b Mon Sep 17 00:00:00 2001 From: drh <> Date: Mon, 11 Apr 2022 12:38:06 +0000 Subject: Make a distinction between (1) WHERE clause constraints, (2) ON/USING constraints on outer joins, and (3) ON/USING clause constraints on inner joins. Formerly, there was no distinctionb between 1 and 3, but RIGHT JOIN needs to know the difference. Make RIGHT JOIN aware of this difference and add test cases. FossilOrigin-Name: 0f6f61c3664cc87209c2a6f9b6df3a750d1510723fcde209c33db8feaf48bcf3 --- src/select.c | 33 ++++++++++++++++++--------------- src/sqliteInt.h | 4 ++-- src/where.c | 8 ++++++-- src/whereexpr.c | 6 +++++- 4 files changed, 31 insertions(+), 20 deletions(-) (limited to 'src') diff --git a/src/select.c b/src/select.c index 3e4f783a3..79ff500f4 100644 --- a/src/select.c +++ b/src/select.c @@ -373,7 +373,7 @@ static void addWhereTerm( 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 */ + u32 joinType, /* EP_FromJoin or EP_InnerJoin */ Expr **ppWhere /* IN/OUT: The WHERE clause to add to */ ){ sqlite3 *db = pParse->db; @@ -393,8 +393,8 @@ static void addWhereTerm( assert( pE2!=0 || pEq==0 ); /* Due to db->mallocFailed test ** in sqlite3DbMallocRawNN() called from ** sqlite3PExpr(). */ - if( pEq && isOuterJoin ){ - ExprSetProperty(pEq, EP_FromJoin); + if( pEq ){ + ExprSetProperty(pEq, joinType); assert( !ExprHasProperty(pEq, EP_TokenOnly|EP_Reduced) ); ExprSetVVAProperty(pEq, EP_NoReduce); pEq->w.iJoin = pE2->iTable; @@ -428,9 +428,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; @@ -439,11 +440,11 @@ void sqlite3SetJoinExpr(Expr *p, int iTable){ if( p->x.pList ){ int i; for(i=0; ix.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; } } @@ -459,6 +460,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); @@ -502,10 +504,10 @@ static int sqliteProcessJoin(Parse *pParse, Select *p){ pRight = &pLeft[1]; for(i=0; inSrc-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. @@ -525,7 +527,7 @@ static int sqliteProcessJoin(Parse *pParse, Select *p){ 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); + joinType, &p->pWhere); } } } @@ -556,7 +558,7 @@ static int sqliteProcessJoin(Parse *pParse, Select *p){ return 1; } addWhereTerm(pParse, pSrc, iLeft, iLeftCol, i+1, iRightCol, - isOuter, &p->pWhere); + joinType, &p->pWhere); } } @@ -564,7 +566,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; } @@ -3724,8 +3726,9 @@ static Expr *substExpr( if( pSubst->isLeftJoin ){ 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; @@ -4452,7 +4455,7 @@ static int flattenSubquery( pWhere = pSub->pWhere; pSub->pWhere = 0; if( isLeftJoin>0 ){ - sqlite3SetJoinExpr(pWhere, iNewParent); + sqlite3SetJoinExpr(pWhere, iNewParent, EP_FromJoin); } if( pWhere ){ if( pParent->pWhere ){ diff --git a/src/sqliteInt.h b/src/sqliteInt.h index 7f8a8e015..b9eb970c7 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 */ @@ -4829,7 +4829,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/where.c b/src/where.c index 3fbe69e59..1cd8f4ded 100644 --- a/src/where.c +++ b/src/where.c @@ -6172,14 +6172,18 @@ void sqlite3WhereEnd(WhereInfo *pWInfo){ WhereInfo *pSubWInfo; SrcList sFrom; Bitmask mAll = 0; - for(k=0; k<=i; k++){ + for(k=0; ka[k].pWLoop->maskSelf; + iIdxCur = pWInfo->a[k].iIdxCur; + if( iIdxCur ) sqlite3VdbeAddOp1(v, OP_NullRow, iIdxCur); } + mAll |= pLoop->maskSelf; for(k=0; knTerm; k++){ WhereTerm *pTerm = &pWC->a[k]; if( pTerm->wtFlags & TERM_VIRTUAL ) break; if( pTerm->prereqAll & ~mAll ) continue; - if( ExprHasProperty(pTerm->pExpr, EP_FromJoin) ) continue; + if( ExprHasProperty(pTerm->pExpr, EP_FromJoin|EP_InnerJoin) ) continue; pSubWhere = sqlite3ExprAnd(pParse, pSubWhere, sqlite3ExprDup(db, pTerm->pExpr, 0)); } diff --git a/src/whereexpr.c b/src/whereexpr.c index b622eed1b..90c344806 100644 --- a/src/whereexpr.c +++ b/src/whereexpr.c @@ -1809,6 +1809,7 @@ void sqlite3WhereTabFuncArgs( if( pArgs==0 ) return; for(j=k=0; jnExpr; j++){ Expr *pRhs; + u32 joinType; while( knCol && (pTab->aCol[k].colFlags & COLFLAG_HIDDEN)==0 ){k++;} if( k>=pTab->nCol ){ sqlite3ErrorMsg(pParse, "too many arguments on %s() - max %d", @@ -1826,8 +1827,11 @@ void sqlite3WhereTabFuncArgs( sqlite3ExprDup(pParse->db, pArgs->a[j].pExpr, 0), 0); pTerm = sqlite3PExpr(pParse, TK_EQ, pColRef, pRhs); if( pItem->fg.jointype & (JT_LEFT|JT_LTORJ) ){ - sqlite3SetJoinExpr(pTerm, pItem->iCursor); + joinType = EP_FromJoin; + }else{ + joinType = EP_InnerJoin; } + sqlite3SetJoinExpr(pTerm, pItem->iCursor, joinType); whereClauseInsert(pWC, pTerm, TERM_DYNAMIC); } } -- cgit v1.2.3 From c583719b65d2fca51178d40826f3b325329fe12a Mon Sep 17 00:00:00 2001 From: drh <> Date: Mon, 11 Apr 2022 14:26:37 +0000 Subject: Show LEFT and RIGHT JOIN processing in the EXPLAIN QUERY PLAN output. FossilOrigin-Name: d91faeffea5cf0585fb71e5311fdcc6b8be85c7e9c732050b4448e617c970101 --- src/sqliteInt.h | 2 +- src/where.c | 4 +++- src/wherecode.c | 13 ++++++++----- 3 files changed, 12 insertions(+), 7 deletions(-) (limited to 'src') diff --git a/src/sqliteInt.h b/src/sqliteInt.h index b9eb970c7..330d58fa4 100644 --- a/src/sqliteInt.h +++ b/src/sqliteInt.h @@ -3163,7 +3163,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 */ diff --git a/src/where.c b/src/where.c index 1cd8f4ded..3677759ce 100644 --- a/src/where.c +++ b/src/where.c @@ -6191,8 +6191,9 @@ void sqlite3WhereEnd(WhereInfo *pWInfo){ sFrom.nAlloc = 1; memcpy(&sFrom.a[0], pTabItem, sizeof(SrcItem)); sFrom.a[0].fg.jointype = 0; + ExplainQueryPlan((pParse, 1, "RIGHT-JOIN %s", pTab->zName)); pSubWInfo = sqlite3WhereBegin(pParse, &sFrom, pSubWhere, 0, 0, 0, - WHERE_OR_SUBCLAUSE, 0); + WHERE_RIGHT_JOIN, 0); if( pSubWInfo ){ int iCur = pLevel->iTabCur; int r = ++pParse->nMem; @@ -6219,6 +6220,7 @@ void sqlite3WhereEnd(WhereInfo *pWInfo){ sqlite3WhereEnd(pSubWInfo); } sqlite3ExprDelete(pParse->db, pSubWhere); + ExplainQueryPlanPop(pParse); continue; } diff --git a/src/wherecode.c b/src/wherecode.c index 0c8104867..410d6f206 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 ){ @@ -2140,7 +2143,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 +2177,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 +2196,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. */ -- cgit v1.2.3 From ff02ac7f078bbe25c3940ffa35458f0807055fa3 Mon Sep 17 00:00:00 2001 From: drh <> Date: Mon, 11 Apr 2022 14:43:11 +0000 Subject: Do not attempt the LEFT JOIN strength reduction optimization on a FULL JOIN. FossilOrigin-Name: 7ef3e99a73d70405a185d5d31f2d97d3bd99568fd6f10941e75d6c0baa27dc4f --- src/select.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'src') diff --git a/src/select.c b/src/select.c index 79ff500f4..cede26d1c 100644 --- a/src/select.c +++ b/src/select.c @@ -6509,7 +6509,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) ){ -- cgit v1.2.3 From 6134b2dff2d5df510210880f935bbd2b0a08f93c Mon Sep 17 00:00:00 2001 From: drh <> Date: Mon, 11 Apr 2022 17:27:38 +0000 Subject: Fix handling of "continue" and "break" from inside the loop for the right operand of a RIGHT JOIN. FossilOrigin-Name: b6e773a26c2c6ee76ea61acb059b4e676d07ea62f6db9c513638f8986557cf04 --- src/vdbe.c | 10 +++++++--- src/where.c | 11 ++++++++--- src/whereInt.h | 1 + 3 files changed, 16 insertions(+), 6 deletions(-) (limited to 'src') diff --git a/src/vdbe.c b/src/vdbe.c index 18286c297..45a47756e 100644 --- a/src/vdbe.c +++ b/src/vdbe.c @@ -993,8 +993,12 @@ jump_to_p2: /* 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 next instruction after the address stored in register P1. +** +** It used to be that after the jump, register P1 would become undefined. +** However, for the subroutine used for the inner loop of a RIGHT JOIN, +** it is useful for R1 register to be unchanged, so that is what happens +** now. ** ** 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 @@ -1012,7 +1016,7 @@ case OP_Return: { /* in1 */ pIn1 = &aMem[pOp->p1]; assert( pIn1->flags==MEM_Int ); pOp = &aOp[pIn1->u.i]; - pIn1->flags = MEM_Undefined; + /* pIn1->flags = MEM_Undefined; */ break; } diff --git a/src/where.c b/src/where.c index 3677759ce..defbc860f 100644 --- a/src/where.c +++ b/src/where.c @@ -5880,6 +5880,7 @@ WhereInfo *sqlite3WhereBegin( pRJ->regBloom = ++pParse->nMem; sqlite3VdbeAddOp2(v, OP_Blob, 65536, pRJ->regBloom); pRJ->regReturn = ++pParse->nMem; + pRJ->addrInit = sqlite3VdbeAddOp2(v, OP_Integer, 0, pRJ->regReturn); assert( pTab==pTabItem->pTab ); if( HasRowid(pTab) ){ KeyInfo *pInfo; @@ -6013,7 +6014,11 @@ void sqlite3WhereEnd(WhereInfo *pWInfo){ /* Terminate the subroutine that forms the interior of the loop of ** the RIGHT JOIN table */ WhereRightJoin *pRJ = pLevel->pRJ; - sqlite3VdbeChangeP1(v, pRJ->addrSubrtn-1, sqlite3VdbeCurrentAddr(v)); + int addrHere = sqlite3VdbeCurrentAddr(v); + sqlite3VdbeChangeP1(v, pRJ->addrSubrtn-1, addrHere); + sqlite3VdbeChangeP1(v, pRJ->addrInit, addrHere); + sqlite3VdbeResolveLabel(v, pLevel->addrCont); + pLevel->addrCont = 0; sqlite3VdbeAddOp2(v, OP_Return, pRJ->regReturn, pRJ->addrSubrtn); } pLoop = pLevel->pWLoop; @@ -6043,7 +6048,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); @@ -6058,7 +6063,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 ){ diff --git a/src/whereInt.h b/src/whereInt.h index 7b5be3011..6be8234e2 100644 --- a/src/whereInt.h +++ b/src/whereInt.h @@ -52,6 +52,7 @@ struct WhereRightJoin { int regBloom; /* Bloom filter for iRJMatch */ int regReturn; /* Return register for the interior subroutine */ int addrSubrtn; /* Starting address for the interior subroutine */ + int addrInit; /* OP_Integer used for early init of regReturn */ }; /* -- cgit v1.2.3 From c133bab72a60fc020fffce0a329c1a4092f20aa0 Mon Sep 17 00:00:00 2001 From: drh <> Date: Mon, 11 Apr 2022 20:15:52 +0000 Subject: The query flattener must add TK_IF_NULL_ROW opcodes on substituted values that land on the left operand of a RIGHT JOIN, just as it already does for the right operand of a LEFT JOIN. FossilOrigin-Name: 8e02cdf5b1128f5e5b82d93903063415ec312694e5ccdd19e99fa35433f1b68a --- src/select.c | 52 ++++++++++++++++++++++++++++++++++++++++------------ 1 file changed, 40 insertions(+), 12 deletions(-) (limited to 'src') diff --git a/src/select.c b/src/select.c index cede26d1c..7230ff90c 100644 --- a/src/select.c +++ b/src/select.c @@ -3654,12 +3654,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; @@ -3709,7 +3737,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; @@ -3723,7 +3751,7 @@ static Expr *substExpr( sqlite3ExprDelete(db, pNew); return pExpr; } - if( pSubst->isLeftJoin ){ + if( pSubst->isOuterJoin ){ ExprSetProperty(pNew, EP_CanBeNull); } if( ExprHasProperty(pExpr,EP_FromJoin|EP_InnerJoin) ){ @@ -4105,7 +4133,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 */ @@ -4178,7 +4206,7 @@ static int flattenSubquery( ** ** See also tickets #306, #350, and #3300. */ - if( (pSubitem->fg.jointype & JT_OUTER)!=0 ){ + if( (pSubitem->fg.jointype & (JT_OUTER|JT_LTORJ))!=0 ){ if( pSubSrc->nSrc>1 /* (3a) */ || isAgg /* (3b) */ || IsVirtual(pSubSrc->a[0].pTab) /* (3c) */ @@ -4187,15 +4215,15 @@ static int flattenSubquery( ){ return 0; } - isLeftJoin = 1; + 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 @@ -4208,7 +4236,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){ @@ -4454,7 +4482,7 @@ static int flattenSubquery( } pWhere = pSub->pWhere; pSub->pWhere = 0; - if( isLeftJoin>0 ){ + if( isOuterJoin>0 ){ sqlite3SetJoinExpr(pWhere, iNewParent, EP_FromJoin); } if( pWhere ){ @@ -4469,7 +4497,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); } @@ -4930,7 +4958,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 -- cgit v1.2.3 From b087de063bed22932287aff4f12a2e5a3083aa8d Mon Sep 17 00:00:00 2001 From: drh <> Date: Mon, 11 Apr 2022 21:00:38 +0000 Subject: Fix RIGHT JOIN for virtual tables. FossilOrigin-Name: 75a9116e98b9ac5c1a4c62a01143a016d9ba6a0b495ff7af7468c11947a3e888 --- src/where.c | 7 ++++++- 1 file changed, 6 insertions(+), 1 deletion(-) (limited to 'src') diff --git a/src/where.c b/src/where.c index defbc860f..f1d3ad265 100644 --- a/src/where.c +++ b/src/where.c @@ -6181,7 +6181,12 @@ void sqlite3WhereEnd(WhereInfo *pWInfo){ int iIdxCur; mAll |= pWInfo->a[k].pWLoop->maskSelf; iIdxCur = pWInfo->a[k].iIdxCur; - if( iIdxCur ) sqlite3VdbeAddOp1(v, OP_NullRow, iIdxCur); + if( iIdxCur ){ + sqlite3VdbeAddOp1(v, OP_NullRow, iIdxCur); + } + if( pWInfo->a[k].pWLoop->wsFlags & WHERE_VIRTUALTABLE ){ + sqlite3VdbeAddOp1(v, OP_NullRow, pWInfo->a[k].iTabCur); + } } mAll |= pLoop->maskSelf; for(k=0; knTerm; k++){ -- cgit v1.2.3 From a70782407d12bae34240167814b49e1593048aca Mon Sep 17 00:00:00 2001 From: drh <> Date: Tue, 12 Apr 2022 13:46:21 +0000 Subject: Always explicitly set each table cursor to NullRow before doing the RIGHT-JOIN unmatched row pass. This is a cheap opcode, and it adds an extra layer of defense against incorrect results. FossilOrigin-Name: a3d14e61ca22167296fee125a3e9aa63413408955e03bb3f9d85fa9f22df1b79 --- src/where.c | 4 +--- 1 file changed, 1 insertion(+), 3 deletions(-) (limited to 'src') diff --git a/src/where.c b/src/where.c index f1d3ad265..288365331 100644 --- a/src/where.c +++ b/src/where.c @@ -6180,13 +6180,11 @@ void sqlite3WhereEnd(WhereInfo *pWInfo){ for(k=0; ka[k].pWLoop->maskSelf; + sqlite3VdbeAddOp1(v, OP_NullRow, pWInfo->a[k].iTabCur); iIdxCur = pWInfo->a[k].iIdxCur; if( iIdxCur ){ sqlite3VdbeAddOp1(v, OP_NullRow, iIdxCur); } - if( pWInfo->a[k].pWLoop->wsFlags & WHERE_VIRTUALTABLE ){ - sqlite3VdbeAddOp1(v, OP_NullRow, pWInfo->a[k].iTabCur); - } } mAll |= pLoop->maskSelf; for(k=0; knTerm; k++){ -- cgit v1.2.3 From 61dac44eb91f0e9f37eb5ccdfe9877f0cdc39a97 Mon Sep 17 00:00:00 2001 From: drh <> Date: Tue, 12 Apr 2022 14:23:45 +0000 Subject: The multi-index OR optimization does not work for RIGHT join, so disallow it. FossilOrigin-Name: 34c2f7b237fa4e0e1cd94fb9c44ebe194b86b88dc575055cc46c7f3695d02756 --- src/where.c | 3 +++ 1 file changed, 3 insertions(+) (limited to 'src') diff --git a/src/where.c b/src/where.c index 288365331..4c9ba86b1 100644 --- a/src/where.c +++ b/src/where.c @@ -4016,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; pTermeOperator & WO_OR)!=0 && (pTerm->u.pOrInfo->indexable & pNew->maskSelf)!=0 -- cgit v1.2.3 From 949e2ab49a8a815796d491506651043dd7d5fb55 Mon Sep 17 00:00:00 2001 From: drh <> Date: Tue, 12 Apr 2022 18:04:29 +0000 Subject: Factor out the RIGHT JOIN non-matched row loop from sqlite3WhereEnd(). This reduces the register pressure on that routine and helps it to run faster in the common case where there is no RIGHT JOIN. FossilOrigin-Name: beeecf1604d4fb11e45058f48cb2289c6542e0bc218d63a245198113d8d5476b --- src/where.c | 59 +------------------------------------------- src/whereInt.h | 5 ++++ src/wherecode.c | 76 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 3 files changed, 82 insertions(+), 58 deletions(-) (limited to 'src') diff --git a/src/where.c b/src/where.c index 4c9ba86b1..836b7d6dc 100644 --- a/src/where.c +++ b/src/where.c @@ -6174,64 +6174,7 @@ void sqlite3WhereEnd(WhereInfo *pWInfo){ ** all of the columns of the left operand set to NULL. */ if( pLevel->pRJ ){ - WhereRightJoin *pRJ = pLevel->pRJ; - Expr *pSubWhere = 0; - WhereClause *pWC = &pWInfo->sWC; - WhereInfo *pSubWInfo; - SrcList sFrom; - Bitmask mAll = 0; - for(k=0; ka[k].pWLoop->maskSelf; - sqlite3VdbeAddOp1(v, OP_NullRow, pWInfo->a[k].iTabCur); - iIdxCur = pWInfo->a[k].iIdxCur; - if( iIdxCur ){ - sqlite3VdbeAddOp1(v, OP_NullRow, iIdxCur); - } - } - mAll |= pLoop->maskSelf; - for(k=0; knTerm; 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(db, pTerm->pExpr, 0)); - } - sFrom.nSrc = 1; - sFrom.nAlloc = 1; - memcpy(&sFrom.a[0], pTabItem, sizeof(SrcItem)); - sFrom.a[0].fg.jointype = 0; - ExplainQueryPlan((pParse, 1, "RIGHT-JOIN %s", pTab->zName)); - 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); - 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; iPkaiColumn[iPk]; - sqlite3ExprCodeGetColumnOfTable(v, pTab, iCur, iCol,r+iPk); - } - } - jmp = sqlite3VdbeAddOp4Int(v, OP_Filter, pRJ->regBloom, 0, r, nPk); - sqlite3VdbeAddOp4Int(v, OP_Found, pRJ->iMatch, addrCont, r, nPk); - sqlite3VdbeJumpHere(v, jmp); - sqlite3VdbeAddOp2(v, OP_Gosub, pRJ->regReturn, pRJ->addrSubrtn); - sqlite3WhereEnd(pSubWInfo); - } - sqlite3ExprDelete(pParse->db, pSubWhere); - ExplainQueryPlanPop(pParse); + sqlite3WhereRightJoinLoop(pWInfo, i, pLevel); continue; } diff --git a/src/whereInt.h b/src/whereInt.h index 6be8234e2..c8a188f80 100644 --- a/src/whereInt.h +++ b/src/whereInt.h @@ -565,6 +565,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 410d6f206..d2cb85aee 100644 --- a/src/wherecode.c +++ b/src/wherecode.c @@ -2786,3 +2786,79 @@ 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; + + for(k=0; ka[k].pWLoop->maskSelf; + sqlite3VdbeAddOp1(v, OP_NullRow, pWInfo->a[k].iTabCur); + iIdxCur = pWInfo->a[k].iIdxCur; + if( iIdxCur ){ + sqlite3VdbeAddOp1(v, OP_NullRow, iIdxCur); + } + } + mAll |= pLoop->maskSelf; + for(k=0; knTerm; 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; + ExplainQueryPlan((pParse, 1, "RIGHT-JOIN %s", pTabItem->pTab->zName)); + 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; iPkaiColumn[iPk]; + sqlite3ExprCodeGetColumnOfTable(v, pTab, iCur, iCol,r+iPk); + } + } + jmp = sqlite3VdbeAddOp4Int(v, OP_Filter, pRJ->regBloom, 0, r, nPk); + sqlite3VdbeAddOp4Int(v, OP_Found, pRJ->iMatch, addrCont, r, nPk); + sqlite3VdbeJumpHere(v, jmp); + sqlite3VdbeAddOp2(v, OP_Gosub, pRJ->regReturn, pRJ->addrSubrtn); + sqlite3WhereEnd(pSubWInfo); + } + sqlite3ExprDelete(pParse->db, pSubWhere); + ExplainQueryPlanPop(pParse); +} -- cgit v1.2.3 From 0879d5f9e04b3f309d8f9b669d29b12608052ebb Mon Sep 17 00:00:00 2001 From: drh <> Date: Tue, 12 Apr 2022 18:40:14 +0000 Subject: For the bad join type error message "unknown or unsupported join type" remove the "or unsupported" clause, because we now support all valid join types. FossilOrigin-Name: ab0a0562dd3594cf50ee56f6b3a5847fa5dcadf69146d560e3e7a95651b8f405 --- src/select.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'src') diff --git a/src/select.c b/src/select.c index 7230ff90c..af3c30bfe 100644 --- a/src/select.c +++ b/src/select.c @@ -298,7 +298,7 @@ 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; } -- cgit v1.2.3 From f7309bce1050a416e0ed1f4b375149a311abe960 Mon Sep 17 00:00:00 2001 From: drh <> Date: Tue, 12 Apr 2022 20:20:54 +0000 Subject: Ensure that the JT_LTORJ bit in the SrcItem.fg.jointype is preserved during query flattening. FossilOrigin-Name: 61259050152321bc57dbdfdc3edcabb4f18c021b1ee0491c1e04ae24c7a59d89 --- src/select.c | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) (limited to 'src') diff --git a/src/select.c b/src/select.c index af3c30bfe..2e5c1ac97 100644 --- a/src/select.c +++ b/src/select.c @@ -4447,7 +4447,8 @@ static int flattenSubquery( iNewParent = pSubSrc->a[i].iCursor; memset(&pSubSrc->a[i], 0, sizeof(pSubSrc->a[i])); } - pSrc->a[iFrom].fg.jointype = jointype | ltorj; + 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. -- cgit v1.2.3 From 146e64d2e4f9ef6e3ce55eb87ebaeca794ec9396 Mon Sep 17 00:00:00 2001 From: drh <> Date: Wed, 13 Apr 2022 01:52:32 +0000 Subject: Add missing VdbeCoverage() macros on new branch byte-code opcodes. FossilOrigin-Name: 218c7167e562f5c327124f02a92de85079315320a221fb0508310d927596b14c --- src/wherecode.c | 2 ++ 1 file changed, 2 insertions(+) (limited to 'src') diff --git a/src/wherecode.c b/src/wherecode.c index d2cb85aee..a438db530 100644 --- a/src/wherecode.c +++ b/src/wherecode.c @@ -2854,7 +2854,9 @@ SQLITE_NOINLINE void sqlite3WhereRightJoinLoop( } } 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); -- cgit v1.2.3 From 12c35ec322dc2b394a3a6d8b3997ef60574414a0 Mon Sep 17 00:00:00 2001 From: drh <> Date: Wed, 13 Apr 2022 12:12:01 +0000 Subject: The rows of a RIGHT JOIN might come out in any arbitrary order. So disable the ORDER-BY/GROUP-BY optimizations if a RIGHT JOIN is involved. FossilOrigin-Name: d168f245ecf497368feea4697769930c00420ef47a584904dac85371b61fb78a --- src/where.c | 5 +++++ 1 file changed, 5 insertions(+) (limited to 'src') diff --git a/src/where.c b/src/where.c index 836b7d6dc..1ce569d37 100644 --- a/src/where.c +++ b/src/where.c @@ -5900,6 +5900,11 @@ WhereInfo *sqlite3WhereBegin( 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); -- cgit v1.2.3