Index: src/build.c ================================================================== --- src/build.c +++ src/build.c @@ -5186,11 +5186,15 @@ ** ** A natural cross join B ** ** 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. +** operator with A in p->a[0]. This routine shifts that operator over to +** B in p->a[1]. After this shift, the left-most term (p->a[0].fg.jointype) +** is not used except as a placeholder for JT_LTORJ and/or JT_LATERAL to +** indicate that at least one RIGHT JOIN or LATERAL JOIN (respectively) +** occurs somewhere in the FROM. ** ** 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 Index: src/parse.y ================================================================== --- src/parse.y +++ src/parse.y @@ -732,10 +732,36 @@ sqlite3SrcListFuncArgs(pParse, A, E); } %ifndef SQLITE_OMIT_SUBQUERY seltablist(A) ::= stl_prefix(A) LP select(S) RP as(Z) on_using(N). { A = sqlite3SrcListAppendFromTerm(pParse,A,0,0,&Z,S,&N); + } + seltablist(A) ::= stl_prefix(A) nm(Y) dbnm(D) LP select(S) RP as(Z) on_using(N). { + /* \___________/ + ** | + ** This must be a single identifier token "LATERAL". We cannot make + ** LATERAL a keyword, since there might be legacy databases that + ** use "lateral" as a table name and a table name is valid syntax + ** in this position. + */ + SrcList *pSrc = A; + if( Y.n!=7 || sqlite3StrNICmp(Y.z,"lateral",7)!=0 || D.z!=0 ){ + sqlite3ErrorMsg(pParse, "near \"%T\": syntax error", &Y); + }else + if( pSrc + && ALWAYS(pSrc->nSrc>0) + && (pSrc->a[pSrc->nSrc-1].fg.jointype & JT_RIGHT)!=0 + ){ + sqlite3ErrorMsg(pParse, "join must be INNER or LEFT for a LATERAL reference"); + pParse->db->errByteOffset = (int)(Y.z - pParse->zTail); + } + pSrc = A = sqlite3SrcListAppendFromTerm(pParse,A,0,0,&Z,S,&N); + if( pParse->nErr==0 && ALWAYS(pSrc!=0) && pSrc->nSrc>1 ){ + pSrc->a[pSrc->nSrc-1].fg.isLateral = 1; + pSrc->a[pSrc->nSrc-2].fg.jointype |= JT_LATERAL; + if( ALWAYS(S!=0) ) S->selFlags |= SF_Lateral; + } } seltablist(A) ::= stl_prefix(A) LP seltablist(F) RP as(Z) on_using(N). { if( A==0 && Z.n==0 && N.pOn==0 && N.pUsing==0 ){ A = F; }else if( ALWAYS(F!=0) && F->nSrc==1 ){ Index: src/resolve.c ================================================================== --- src/resolve.c +++ src/resolve.c @@ -1881,21 +1881,20 @@ isCompound = p->pPrior!=0; nCompound = 0; pLeftmost = p; while( p ){ + int nSrc; assert( (p->selFlags & SF_Expanded)!=0 ); assert( (p->selFlags & SF_Resolved)==0 ); p->selFlags |= SF_Resolved; - /* Resolve the expressions in the LIMIT and OFFSET clauses. These - ** are not allowed to refer to any names, so pass an empty NameContext. - */ + /* Resolve the expressions in the LIMIT and OFFSET clauses. */ memset(&sNC, 0, sizeof(sNC)); sNC.pParse = pParse; sNC.pWinSelect = p; - if( sqlite3ResolveExprNames(&sNC, p->pLimit) ){ + if( p->pLimit!=0 && sqlite3ResolveExprNames(&sNC, p->pLimit) ){ return WRC_Abort; } /* If the SF_Converted flags is set, then this Select object was ** was created by the convertCompoundSelectToSubquery() function. @@ -1916,22 +1915,48 @@ } /* Recursively resolve names in all subqueries in the FROM clause */ if( pOuterNC ) pOuterNC->nNestedSelect++; - for(i=0; ipSrc->nSrc; i++){ + nSrc = p->pSrc->nSrc; + for(i=0; ipSrc->a[i]; assert( pItem->zName!=0 || pItem->fg.isSubquery ); /* Test of tag-20240424-1*/ if( pItem->fg.isSubquery && (pItem->u4.pSubq->pSelect->selFlags & SF_Resolved)==0 ){ int nRef = pOuterNC ? pOuterNC->nRef : 0; + int nRef2 = sNC.nRef; + NameContext *pSubNC; const char *zSavedContext = pParse->zAuthContext; - if( pItem->zName ) pParse->zAuthContext = pItem->zName; - sqlite3ResolveSelectNames(pParse, pItem->u4.pSubq->pSelect, pOuterNC); + if( pItem->fg.isLateral ){ + assert( i>0 ); /* Because p->pSub->a[0] is never marked LATERAL */ + assert( pItem->u4.pSubq->pSelect!=0 ); + assert( pItem->u4.pSubq->pSelect->selFlags & SF_Lateral ); + p->pSrc->nSrc = i; + sNC.pSrcList = p->pSrc; + if( pItem->u4.pSubq->pSelect->pLimit ){ + /* If a LIMIT/OFFSET clause exists on a LATERAL subquery, allow + ** variables from other FROM clause terms to the left of the + ** subquery to be used in the LIMIT/OFFSET clause. */ + sNC.pNext = 0; + (void)sqlite3ResolveExprNames(&sNC, pItem->u4.pSubq->pSelect->pLimit); + } + sNC.pNext = pOuterNC; + pSubNC = &sNC; + }else{ + sNC.pSrcList = 0; + sNC.pNext = 0; + pSubNC = pOuterNC; + } + sqlite3ResolveSelectNames(pParse, pItem->u4.pSubq->pSelect, pSubNC); + p->pSrc->nSrc = nSrc; + if( sNC.nRef>nRef2 ){ + pItem->fg.isCorrelated = 1; + } pParse->zAuthContext = zSavedContext; if( pParse->nErr ) return WRC_Abort; assert( db->mallocFailed==0 ); /* If the number of references to the outer context changed when @@ -1938,13 +1963,12 @@ ** expressions in the sub-select were resolved, the sub-select ** is correlated. It is not required to check the refcount on any ** but the innermost outer context object, as lookupName() increments ** the refcount on all contexts between the current one and the ** context containing the column when it resolves a name. */ - if( pOuterNC ){ - assert( pItem->fg.isCorrelated==0 && pOuterNC->nRef>=nRef ); - pItem->fg.isCorrelated = (pOuterNC->nRef>nRef); + if( pOuterNC && pOuterNC->nRef>nRef ){ + pItem->fg.isCorrelated = 1; } } } if( pOuterNC && ALWAYS(pOuterNC->nNestedSelect>0) ){ pOuterNC->nNestedSelect--; Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -291,17 +291,16 @@ break; } } testcase( j==0 || j==1 || j==2 || j==3 || j==4 || j==5 || j==6 ); if( j>=ArraySize(aKeyword) ){ - jointype |= JT_ERROR; + jointype = JT_OUTER; /* Triggers error report below */ break; } } if( (jointype & (JT_INNER|JT_OUTER))==(JT_INNER|JT_OUTER) || - (jointype & JT_ERROR)!=0 || (jointype & (JT_OUTER|JT_LEFT|JT_RIGHT))==JT_OUTER ){ const char *zSp1 = " "; const char *zSp2 = " "; if( pB==0 ){ zSp1++; } @@ -3982,10 +3981,11 @@ if( !p ) return; do{ substExprList(pSubst, p->pEList); substExprList(pSubst, p->pGroupBy); substExprList(pSubst, p->pOrderBy); + p->pLimit = substExpr(pSubst, p->pLimit); p->pHaving = substExpr(pSubst, p->pHaving); p->pWhere = substExpr(pSubst, p->pWhere); pSrc = p->pSrc; assert( pSrc!=0 ); for(i=pSrc->nSrc, pItem=pSrc->a; i>0; i--, pItem++){ @@ -7299,11 +7299,11 @@ ** (iii) The query is not a UPDATE ... FROM ** (2) The subquery is not a CTE that should be materialized because ** (a) the AS MATERIALIZED keyword is used, or ** (b) the CTE is used multiple times and does not have the ** NOT MATERIALIZED keyword -** (3) The subquery is not part of a left operand for a RIGHT JOIN +** (3) The FROM clause does not contain a RIGHT JOIN ** (4) The SQLITE_Coroutine optimization disable flag is not set ** (5) The subquery is not self-joined */ static int fromClauseTermCanBeCoroutine( Parse *pParse, /* Parsing context */ @@ -7328,11 +7328,16 @@ if( selFlags & SF_UpdateFrom ) return 0; /* (1c-iii) */ return 1; } if( selFlags & SF_UpdateFrom ) return 0; /* (1c-iii) */ while( 1 /*exit-by-break*/ ){ - if( pItem->fg.jointype & (JT_OUTER|JT_CROSS) ) return 0; /* (1c-ii) */ + if( pItem->fg.jointype & (JT_OUTER|JT_CROSS|JT_LATERAL) ){ + testcase( pItem->fg.jointype & JT_OUTER ); + testcase( pItem->fg.jointype & JT_CROSS ); + testcase( pItem->fg.jointype & JT_LATERAL ); + return 0; /* (1c-ii) */ + } if( i==0 ) break; i--; pItem--; if( pItem->fg.isSubquery ) return 0; /* (1c-i) */ } @@ -7850,11 +7855,12 @@ /* Implement a co-routine that will return a single row of the result ** set on each invocation. tag-select-0482 */ int addrTop = sqlite3VdbeCurrentAddr(v)+1; - pSubq->regReturn = ++pParse->nMem; + assert( pItem->fg.isLateral==0 ); + pItem->u4.pSubq->regReturn = ++pParse->nMem; sqlite3VdbeAddOp3(v, OP_InitCoroutine, pSubq->regReturn, 0, addrTop); VdbeComment((v, "%!S", pItem)); pSubq->addrFillSub = addrTop; sqlite3SelectDestInit(&dest, SRT_Coroutine, pSubq->regReturn); ExplainQueryPlan((pParse, 1, "CO-ROUTINE %!S", pItem)); @@ -7917,10 +7923,19 @@ sqlite3SelectDestInit(&dest, SRT_EphemTab, pItem->iCursor); ExplainQueryPlan2(addrExplain, (pParse, 1, "MATERIALIZE %!S", pItem)); sqlite3Select(pParse, pSub, &dest); pItem->pSTab->nRowLogEst = pSub->nSelectRow; + if( pItem->fg.isLateral && pItem->fg.isCorrelated ){ + int kk; + for(kk=0; kka[kk]; + if( pX->fg.viaCoroutine==0 ) continue; + sqlite3TranslateColumnToCopy(pParse, topAddr+1, + pX->iCursor, pX->u4.pSubq->regResult, 0); + } + } if( onceAddr ) sqlite3VdbeJumpHere(v, onceAddr); sqlite3VdbeAddOp2(v, OP_Return, pSubq->regReturn, topAddr+1); VdbeComment((v, "end %!S", pItem)); sqlite3VdbeScanStatusRange(v, addrExplain, addrExplain, -1); sqlite3VdbeJumpHere(v, topAddr); Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -3287,14 +3287,14 @@ /* ** The SrcItem object represents a single term in the FROM clause of a query. ** The SrcList object is mostly an array of SrcItems. ** -** The jointype starts out showing the join type between the current table -** and the next table on the list. The parser builds the list this way. +** The jointype starts out showing the join type between the current term +** and the next term on the list. The parser builds the list this way. ** But sqlite3SrcListShiftJoinType() later shifts the jointypes so that each -** jointype expresses the join between the table and the previous table. +** jointype expresses the join between the current term and the previous. ** ** In the colUsed field, the high-order bit (bit 63) is set if the table ** contains more than 63 columns and the 64-th or later column is used. ** ** Aggressive use of "union" helps keep the size of the object small. This @@ -3325,16 +3325,17 @@ struct SrcItem { char *zName; /* Name of the table */ char *zAlias; /* The "B" part of a "A AS B" phrase. zName is the "A" */ Table *pSTab; /* Table object for zName. Mnemonic: Srcitem-TABle */ struct { - u8 jointype; /* Type of join between this table and the previous */ + u8 jointype; /* Type of join. See above for more detail. */ unsigned notIndexed :1; /* True if there is a NOT INDEXED clause */ unsigned isIndexedBy :1; /* True if there is an INDEXED BY clause */ unsigned isSubquery :1; /* True if this term is a subquery */ unsigned isTabFunc :1; /* True if table-valued-function syntax */ unsigned isCorrelated :1; /* True if sub-query is correlated */ + unsigned isLateral :1; /* True if sub-query is LATERAL */ unsigned isMaterialized:1; /* This is a materialized view */ unsigned viaCoroutine :1; /* Implemented as a co-routine */ unsigned isRecursive :1; /* True for recursive reference in WITH */ unsigned fromDDL :1; /* Comes from sqlite_schema */ unsigned isCte :1; /* This is a CTE */ @@ -3400,11 +3401,13 @@ #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 */ +#define JT_LATERAL 0x80 /* A LATERAL join. Or, if on the left-most + ** SrcItem, an indication that at least one + ** correlated LATERAL join exists in the FROM */ /* ** Flags appropriate for the wctrlFlags parameter of sqlite3WhereBegin() ** and the WhereInfo.wctrlFlags member. ** @@ -3608,11 +3611,11 @@ #define SF_MultiValue 0x0000400 /* Single VALUES term with multiple rows */ #define SF_NestedFrom 0x0000800 /* Part of a parenthesized FROM clause */ #define SF_MinMaxAgg 0x0001000 /* Aggregate containing min() or max() */ #define SF_Recursive 0x0002000 /* The recursive part of a recursive CTE */ #define SF_FixedLimit 0x0004000 /* nSelectRow set by a constant LIMIT */ -#define SF_MaybeConvert 0x0008000 /* Need convertCompoundSelectToSubquery() */ +#define SF_Lateral 0x0008000 /* Is of the form: LATERAL(SELECT ...) */ #define SF_Converted 0x0010000 /* By convertCompoundSelectToSubquery() */ #define SF_IncludeHidden 0x0020000 /* Include hidden columns in output */ #define SF_ComplexResult 0x0040000 /* Result contains subquery or function */ #define SF_WhereBegin 0x0080000 /* Really a WhereBegin() call. Debug Only */ #define SF_WinRewrite 0x0100000 /* Window function rewrite accomplished */ @@ -5044,10 +5047,17 @@ void sqlite3Update(Parse*, SrcList*, ExprList*,Expr*,int,ExprList*,Expr*, Upsert*); WhereInfo *sqlite3WhereBegin(Parse*,SrcList*,Expr*,ExprList*, ExprList*,Select*,u16,int); void sqlite3WhereEnd(WhereInfo*); +void sqlite3TranslateColumnToCopy( + Parse *pParse, /* Parsing context */ + int iStart, /* Translate from this opcode to the end */ + int iTabCur, /* OP_Column/OP_Rowid references to this table */ + int iRegister, /* The first column is in this register */ + int iAutoidxCur /* If non-zero, cursor of autoindex being generated */ +); LogEst sqlite3WhereOutputRowCount(WhereInfo*); int sqlite3WhereIsDistinct(WhereInfo*); int sqlite3WhereIsOrdered(WhereInfo*); int sqlite3WhereOrderByLimitOptLabel(WhereInfo*); void sqlite3WhereMinMaxOptEarlyOut(Vdbe*,WhereInfo*); Index: src/treeview.c ================================================================== --- src/treeview.c +++ src/treeview.c @@ -209,10 +209,13 @@ sqlite3_str_appendf(&x, " CROSS-JOIN"); } if( pItem->fg.jointype & JT_LTORJ ){ sqlite3_str_appendf(&x, " LTORJ"); } + if( pItem->fg.isLateral ){ + sqlite3_str_appendf(&x, " LATERAL"); + } if( pItem->fg.fromDDL ){ sqlite3_str_appendf(&x, " DDL"); } if( pItem->fg.isCte ){ sqlite3_str_appendf(&x, " CteUse=0x%p", pItem->u2.pCteUse); Index: src/vdbe.c ================================================================== --- src/vdbe.c +++ src/vdbe.c @@ -2076,10 +2076,13 @@ }else{ goto jump_to_p2; } } } +#ifdef SQLITE_DEBUG + pIn1->pScopyFrom = 0; +#endif VdbeBranchTaken(0, 2); MemSetTypeFlag(pIn1, MEM_Int); break; } Index: src/where.c ================================================================== --- src/where.c +++ src/where.c @@ -706,11 +706,11 @@ ** If the iAutoidxCur is not zero, then any OP_Rowid instructions on ** cursor iTabCur are transformed into OP_Sequence opcode for the ** iAutoidxCur cursor, in order to generate unique rowids for the ** automatic index being generated. */ -static void translateColumnToCopy( +void sqlite3TranslateColumnToCopy( Parse *pParse, /* Parsing context */ int iStart, /* Translate from this opcode to the end */ int iTabCur, /* OP_Column/OP_Rowid references to this table */ int iRegister, /* The first column is in this register */ int iAutoidxCur /* If non-zero, cursor of autoindex being generated */ @@ -1195,12 +1195,12 @@ if( pSrc->fg.viaCoroutine ){ assert( pSrc->fg.isSubquery && pSrc->u4.pSubq!=0 ); sqlite3VdbeChangeP2(v, addrCounter, regBase+n); testcase( pParse->db->mallocFailed ); assert( pLevel->iIdxCur>0 ); - translateColumnToCopy(pParse, addrTop, pLevel->iTabCur, - pSrc->u4.pSubq->regResult, pLevel->iIdxCur); + sqlite3TranslateColumnToCopy(pParse, addrTop, pLevel->iTabCur, + pSrc->u4.pSubq->regResult, pLevel->iIdxCur); sqlite3VdbeGoto(v, addrTop); pSrc->fg.viaCoroutine = 0; }else{ sqlite3VdbeAddOp2(v, OP_Next, pLevel->iTabCur, addrTop+1); VdbeCoverage(v); sqlite3VdbeChangeP5(v, SQLITE_STMTSTATUS_AUTOINDEX); @@ -4804,33 +4804,40 @@ Bitmask mUnusable = 0; pNew->iTab = iTab; pBuilder->iPlanLimit += SQLITE_QUERY_PLANNER_LIMIT_INCR; pNew->maskSelf = sqlite3WhereGetMask(&pWInfo->sMaskSet, pItem->iCursor); if( bFirstPastRJ - || (pItem->fg.jointype & (JT_OUTER|JT_CROSS|JT_LTORJ))!=0 + || (pItem->fg.jointype & (JT_OUTER|JT_CROSS|JT_LTORJ|JT_LATERAL))!=0 ){ /* Add prerequisites to prevent reordering of FROM clause terms - ** across CROSS joins and outer joins. The bFirstPastRJ boolean - ** prevents the right operand of a RIGHT JOIN from being swapped with - ** other elements even further to the right. + ** across CROSS joins, outer joins, and lateral joins. + ** The bFirstPastRJ boolean prevents the right operand of a + ** RIGHT JOIN from being swapped with other elements even further + ** to the right. ** ** The JT_LTORJ case and the hasRightJoin flag work together to ** prevent FROM-clause terms from moving from the right side of ** a LEFT JOIN over to the left side of that join if the LEFT JOIN ** is itself on the left side of a RIGHT JOIN. */ if( pItem->fg.jointype & JT_LTORJ ) hasRightJoin = 1; + testcase( pItem->fg.jointype & JT_CROSS ); + testcase( pItem->fg.jointype & JT_OUTER ); + testcase( pItem->fg.jointype & JT_LATERAL ); mPrereq |= mPrior; bFirstPastRJ = (pItem->fg.jointype & JT_RIGHT)!=0; }else if( !hasRightJoin ){ mPrereq = 0; } #ifndef SQLITE_OMIT_VIRTUALTABLE if( IsVirtual(pItem->pSTab) ){ SrcItem *p; for(p=&pItem[1]; pfg.jointype & (JT_OUTER|JT_CROSS)) ){ + if( mUnusable || (p->fg.jointype & (JT_OUTER|JT_CROSS|JT_LATERAL)) ){ + testcase( p->fg.jointype & JT_CROSS ); + testcase( p->fg.jointype & JT_OUTER ); + testcase( p->fg.jointype & JT_LATERAL ); mUnusable |= sqlite3WhereGetMask(&pWInfo->sMaskSet, p->iCursor); } } rc = whereLoopAddVirtual(pBuilder, mPrereq, mUnusable); }else @@ -6382,10 +6389,34 @@ ){ pWInfo->revMask |= MASKBIT(ii); } } } + +/* +** Adjust the addrBody of all WHERE_IDX_ONLY WhereLoops prior to ii +** so that the addrBody covers the subroutine that computes a LATERAL +** subquery. +** +** This routine is broken out into a separate no-inline subroutine because +** it runs rarely, and by breaking it out it reduces register contention +** in the main sqlite3WhereBegin() routine, helping sqlite3WhereBegin() +** to run faster. +*/ +static SQLITE_NOINLINE void whereAdjustAddrBodyForLateral( + WhereInfo *pWInfo, /* There WHERE loop info */ + SrcItem *pSrc, /* The LATERAL subquery */ + int ii /* Index of pSrc in the FROM clause */ +){ + while( --ii >= 0 ){ + if( pWInfo->a[ii].pWLoop->wsFlags & WHERE_IDX_ONLY + && pWInfo->a[ii].addrBody > pSrc->u4.pSubq->addrFillSub + ){ + pWInfo->a[ii].addrBody = pSrc->u4.pSubq->addrFillSub; + } + } +} /* ** Generate the beginning of the loop used for WHERE clause processing. ** The return value is a pointer to an opaque structure that contains ** information needed to terminate the loop. Later, the calling routine @@ -7077,10 +7108,13 @@ pSubq = pSrc->u4.pSubq; if( pSrc->fg.isCorrelated==0 ){ iOnce = sqlite3VdbeAddOp0(v, OP_Once); VdbeCoverage(v); }else{ iOnce = 0; + if( pSrc->fg.isLateral ){ + whereAdjustAddrBodyForLateral(pWInfo, pSrc, ii); + } } sqlite3VdbeAddOp2(v, OP_Gosub, pSubq->regReturn, pSubq->addrFillSub); VdbeComment((v, "materialize %!S", pSrc)); if( iOnce ) sqlite3VdbeJumpHere(v, iOnce); } @@ -7350,12 +7384,12 @@ */ if( pTabItem->fg.viaCoroutine ){ testcase( pParse->db->mallocFailed ); assert( pTabItem->fg.isSubquery ); assert( pTabItem->u4.pSubq->regResult>=0 ); - translateColumnToCopy(pParse, pLevel->addrBody, pLevel->iTabCur, - pTabItem->u4.pSubq->regResult, 0); + sqlite3TranslateColumnToCopy(pParse, pLevel->addrBody, pLevel->iTabCur, + pTabItem->u4.pSubq->regResult, 0); continue; } /* 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 Index: src/wherecode.c ================================================================== --- src/wherecode.c +++ src/wherecode.c @@ -1459,10 +1459,36 @@ } return 1; } return 0; } + +/* +** Find an appropriate label for iLevel loop to jump to if it the table +** for that loop is empty. +** +** For a simple query, we might as well jump to the break-address of the +** outermost loop, halting the query, since if one of the joined tables +** is empty, the result set will be empty. But that does not work if +** there are outer joins. Nor does it work if the empty table is a +** correlated subquery (with the LATERAL keyword). +*/ +static SQLITE_NOINLINE int haltAddress( + WhereInfo *pWInfo, + int iLevel, + SrcItem *pTabItem +){ + if( pTabItem->fg.isLateral==0 || pTabItem->fg.isCorrelated==0 ){ + while( 1 /*exit-by-break*/ ){ + if( pWInfo->a[iLevel].iLeftJoin ) break; + if( pWInfo->a[iLevel].pRJ ) break; + if( iLevel==0 ) break; + iLevel--; + }; + } + return pWInfo->a[iLevel].addrBrk; +} /* ** Generate code for the start of the iLevel-th loop in the WHERE clause ** implementation described by pWInfo. */ @@ -1482,11 +1508,10 @@ WhereClause *pWC; /* Decomposition of the entire WHERE clause */ WhereTerm *pTerm; /* A WHERE clause term */ sqlite3 *db; /* Database connection */ SrcItem *pTabItem; /* FROM clause term being coded */ int addrBrk; /* Jump here to break out of the loop */ - int addrHalt; /* addrBrk for the outermost loop */ int addrCont; /* Jump here to continue with next cycle */ int iRowidReg = 0; /* Rowid is stored in this register, if not zero */ int iReleaseReg = 0; /* Temp register to free before returning */ Index *pIdx = 0; /* Index used by loop (if any) */ int iLoop; /* Iteration of constraint generator loop */ @@ -1542,18 +1567,10 @@ pLevel->iLeftJoin = ++pParse->nMem; sqlite3VdbeAddOp2(v, OP_Integer, 0, pLevel->iLeftJoin); VdbeComment((v, "init LEFT JOIN match flag")); } - /* 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; 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 */ if( pTabItem->fg.viaCoroutine ){ int regYield; Subquery *pSubq; assert( pTabItem->fg.isSubquery && pTabItem->u4.pSubq!=0 ); @@ -1785,11 +1802,12 @@ VdbeCoverageIf(v, pX->op==TK_LE); VdbeCoverageIf(v, pX->op==TK_LT); VdbeCoverageIf(v, pX->op==TK_GE); sqlite3ReleaseTempReg(pParse, rTemp); }else{ - sqlite3VdbeAddOp2(v, bRev ? OP_Last : OP_Rewind, iCur, addrHalt); + sqlite3VdbeAddOp2(v, bRev ? OP_Last : OP_Rewind, iCur, + haltAddress(pWInfo, iLevel, pTabItem)); VdbeCoverageIf(v, bRev==0); VdbeCoverageIf(v, bRev!=0); } if( pEnd ){ Expr *pX; @@ -2580,11 +2598,12 @@ pLevel->op = OP_Noop; }else{ codeCursorHint(pTabItem, pWInfo, pLevel, 0); pLevel->op = aStep[bRev]; pLevel->p1 = iCur; - pLevel->p2 = 1 + sqlite3VdbeAddOp2(v, aStart[bRev], iCur, addrHalt); + pLevel->p2 = 1 + sqlite3VdbeAddOp2(v, aStart[bRev], iCur, + haltAddress(pWInfo, iLevel, pTabItem)); VdbeCoverageIf(v, bRev==0); VdbeCoverageIf(v, bRev!=0); pLevel->p5 = SQLITE_STMTSTATUS_FULLSCAN_STEP; } } ADDED test/joinL.test Index: test/joinL.test ================================================================== --- /dev/null +++ test/joinL.test @@ -0,0 +1,235 @@ +# 2024-07-20 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# +# Tests for LATERAL JOIN +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix joinL + +db null NULL +do_execsql_test 1.0 { + CREATE TABLE product(product_id INT, price REAL, product TEXT); + INSERT INTO product VALUES + (1, 6.76, 'apple'), + (2, 5.28, 'banana'), + (3, 28.28, 'cherry'), + (4, 13.54, 'dates'), + (5, 30.58, 'elderberry'), + (6, 26.57, 'figs'), + (7, 64.85, 'grapes'), + (8, 21.55, 'huckleberry'), + (9, 29.00, 'jakefruit'), + (10, 17.31, 'kiwi'); + CREATE TABLE wishlist(wishlist_id int, username text, desired_price real); + INSERT INTO wishlist VALUES + (1, 'alice', 45.0), + (2, 'bob', 30.0), + (3, 'cindy', 15.0), + (4, 'david', 4.0); + SELECT * + FROM wishlist AS w LEFT JOIN LATERAL ( + SELECT * FROM product AS p + WHERE p.price < w.desired_price + ORDER BY p.price DESC LIMIT 3 + ) AS x + ORDER BY wishlist_id, price DESC; +} { + 1 alice 45.0 5 30.58 elderberry + 1 alice 45.0 9 29.0 jakefruit + 1 alice 45.0 3 28.28 cherry + 2 bob 30.0 9 29.0 jakefruit + 2 bob 30.0 3 28.28 cherry + 2 bob 30.0 6 26.57 figs + 3 cindy 15.0 4 13.54 dates + 3 cindy 15.0 1 6.76 apple + 3 cindy 15.0 2 5.28 banana + 4 david 4.0 NULL NULL NULL +} +do_execsql_test 1.1 { + SELECT * + FROM wishlist AS w JOIN LATERAL ( + SELECT * FROM product AS p + WHERE p.price < w.desired_price + ORDER BY p.price DESC LIMIT 3 + ) AS x + ORDER BY wishlist_id, price DESC; +} { + 1 alice 45.0 5 30.58 elderberry + 1 alice 45.0 9 29.0 jakefruit + 1 alice 45.0 3 28.28 cherry + 2 bob 30.0 9 29.0 jakefruit + 2 bob 30.0 3 28.28 cherry + 2 bob 30.0 6 26.57 figs + 3 cindy 15.0 4 13.54 dates + 3 cindy 15.0 1 6.76 apple + 3 cindy 15.0 2 5.28 banana +} + + +do_catchsql_test 1.2 { + SELECT * + FROM wishlist AS w RIGHT JOIN LATERAL ( + SELECT * FROM product AS p + WHERE p.price < w.desired_price + ORDER BY p.price DESC LIMIT 3 + ) AS x ON true + ORDER BY wishlist_id, price DESC; +} {1 {join must be INNER or LEFT for a LATERAL reference}} +do_catchsql_test 1.3 { + SELECT * + FROM wishlist AS w FULL OUTER JOIN LATERAL ( + SELECT * FROM product AS p + WHERE p.price < w.desired_price + ORDER BY p.price DESC LIMIT 3 + ) AS x ON true + ORDER BY wishlist_id, price DESC; +} {1 {join must be INNER or LEFT for a LATERAL reference}} +do_catchsql_test 1.4 { + SELECT * + FROM wishlist AS w FULL OUTER JOIN LATERALx ( + SELECT * FROM product AS p + WHERE p.price < w.desired_price + ORDER BY p.price DESC LIMIT 3 + ) AS x ON true + ORDER BY wishlist_id, price DESC; +} {1 {near "LATERALx": syntax error}} +do_catchsql_test 1.5 { + SELECT * + FROM wishlist AS w FULL OUTER JOIN LATERAL.xyz ( + SELECT * FROM product AS p + WHERE p.price < w.desired_price + ORDER BY p.price DESC LIMIT 3 + ) AS x ON true + ORDER BY wishlist_id, price DESC; +} {1 {near "LATERAL": syntax error}} + +do_execsql_test 2.0 { + CREATE TABLE orders(id INTEGER PRIMARY KEY, user_id INT, created_at TEXT); + INSERT INTO orders VALUES + (1,1,'2024-07-20T01:35:03'), + (2,2,'2024-07-20T01:35:07'), + (3,3,'2024-07-20T01:35:10'), + (4,1,'2024-07-20T01:58:10'), + (5,3,'2024-07-20T01:58:17'), + (6,3,'2024-07-20T01:58:25'); + + SELECT user_id, first_order_time, next_order_time, id FROM + (SELECT user_id, min(created_at) AS first_order_time + FROM orders GROUP BY user_id) AS o1 + LEFT JOIN LATERAL + (SELECT id, created_at AS next_order_time + FROM orders + WHERE user_id = o1.user_id AND created_at > o1.first_order_time + ORDER BY created_at ASC LIMIT 1) AS o2 + ON true; +} { + 1 2024-07-20T01:35:03 2024-07-20T01:58:10 4 + 2 2024-07-20T01:35:07 NULL NULL + 3 2024-07-20T01:35:10 2024-07-20T01:58:17 5 +} +do_execsql_test 2.1 { + SELECT user_id, first_order_time, next_order_time, id FROM + LATERAL (SELECT user_id, min(created_at) AS first_order_time + FROM orders GROUP BY user_id) AS o1 + JOIN LATERAL + (SELECT id, created_at AS next_order_time + FROM orders + WHERE user_id = o1.user_id AND created_at > o1.first_order_time + ORDER BY created_at ASC LIMIT 1) AS o2 + ON true; +} { + 1 2024-07-20T01:35:03 2024-07-20T01:58:10 4 + 3 2024-07-20T01:35:10 2024-07-20T01:58:17 5 +} + +# "LATERAL" is a not actually a keyword. It can be used as an indentifier for +# historical compatibility. +# +do_execsql_test 3.0 { + CREATE TABLE lateral(a,lateral lateral); + INSERT INTO lateral VALUES(1,2); + SELECT * FROM lateral; + ATTACH ':memory:' AS lateral; + CREATE TABLE lateral.t2(x,y); + INSERT INTO t2 VALUES(98,99); + SELECT * FROM t2; +} {1 2 98 99} + +# https://sqlite.org/forum/forumpost/dfe2cd37ca3a9a80 +# +do_execsql_test 4.0 { + SELECT * FROM (VALUES (1), (2)) JOIN LATERAL (select COUNT(*), column1); +} {1 1 1 2 1 2} +do_execsql_test 4.1 { + CREATE TABLE t3(x INTEGER PRIMARY KEY); + WITH RECURSIVE c(n) AS (VALUES(0) UNION ALL SELECT n+1 FROM c WHERE n<20) + INSERT INTO t3(x) SELECT n FROM c; + CREATE TABLE t4(y,z); + INSERT INTO t4 VALUES(1,0),(3,2),(5,7); + SELECT * FROM t4, LATERAL(SELECT x FROM t3 ORDER BY x LIMIT y OFFSET z); +} { + 1 0 0 + 3 2 2 + 3 2 3 + 3 2 4 + 5 7 7 + 5 7 8 + 5 7 9 + 5 7 10 + 5 7 11 +} +do_execsql_test 4.2 { + SELECT * FROM (VALUES(1,0),(3,2),(5,7)), + LATERAL(SELECT x FROM t3 ORDER BY x LIMIT column1 OFFSET column2); +} { + 1 0 0 + 3 2 2 + 3 2 3 + 3 2 4 + 5 7 7 + 5 7 8 + 5 7 9 + 5 7 10 + 5 7 11 +} + + +# https://sqlite.org/forum/forumpost/fc29fa4f14 +# +do_execsql_test 5.0 { + CREATE TABLE t1(a,b); INSERT INTO t1 VALUES(1,2); + CREATE TABLE t2(c,d,e,f); INSERT INTO t2 VALUES(3,4,5,6); + CREATE INDEX t2cd ON t2(c,d); + CREATE TABLE dual(dummy TEXT); INSERT INTO dual VALUES('X'); + SELECT c, a, sb + FROM t2, LATERAL( + SELECT a, sum(b) AS sb FROM t1 GROUP BY a HAVING sum(b)