SQLite

**
** Example: Suppose the join is like this:
**
**           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 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
**       code generator can easily tell that the table is part of
**       the left operand of at least one RIGHT JOIN.

seltablist(A) ::= stl_prefix(A) nm(Y) dbnm(D) LP exprlist(E) RP as(Z) on_using(N). {
  A = sqlite3SrcListAppendFromTerm(pParse,A,&Y,&D,&Z,0,&N);
  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 ){
      A = sqlite3SrcListAppendFromTerm(pParse,A,0,0,&Z,0,&N);
      if( A ){

    return pParse->nErr ? WRC_Abort : WRC_Prune;
  }

  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. */


    memset(&sNC, 0, sizeof(sNC));
    sNC.pParse = pParse;
    sNC.pWinSelect = p;
    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.
    ** In this case the ORDER BY clause (p->pOrderBy) should be resolved
    ** as if it were part of the sub-query, not the parent. This block

      pSub->pOrderBy = p->pOrderBy;
      p->pOrderBy = 0;
    }
 
    /* Recursively resolve names in all subqueries in the FROM clause
    */
    if( pOuterNC ) pOuterNC->nNestedSelect++;
    nSrc = p->pSrc->nSrc;
    for(i=0; i<nSrc; i++){
      SrcItem *pItem = &p->pSrc->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;
        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
        ** 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 && pOuterNC->nRef>nRef ){

          pItem->fg.isCorrelated = 1;
        }
      }
    }
    if( pOuterNC && ALWAYS(pOuterNC->nNestedSelect>0) ){
      pOuterNC->nNestedSelect--;
    }
 

          && sqlite3StrNICmp((char*)p->z, &zKeyText[aKeyword[j].i], p->n)==0 ){
        jointype |= aKeyword[j].code;
        break;
      }
    }
    testcase( j==0 || j==1 || j==2 || j==3 || j==4 || j==5 || j==6 );
    if( j>=ArraySize(aKeyword) ){
      jointype = JT_OUTER;  /* Triggers error report below */
      break;
    }
  }
  if(
     (jointype & (JT_INNER|JT_OUTER))==(JT_INNER|JT_OUTER) ||

     (jointype & (JT_OUTER|JT_LEFT|JT_RIGHT))==JT_OUTER
  ){
    const char *zSp1 = " ";
    const char *zSp2 = " ";
    if( pB==0 ){ zSp1++; }
    if( pC==0 ){ zSp2++; }
    sqlite3ErrorMsg(pParse, "unknown join type: "

  SrcItem *pItem;
  int i;
  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++){
      if( pItem->fg.isSubquery ){
        substSelect(pSubst, pItem->u4.pSubq->pSelect, 1);

**                    being used as the outer loop if the sqlite3WhereBegin()
**                    routine nominates it to that position.
**              (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 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 */
  SrcList *pTabList,      /* FROM clause */
  int i,                  /* Which term of the FROM clause holds the subquery */

    if( pTabList->nSrc==1 ) return 1;                             /* (1a) */
    if( pTabList->a[1].fg.jointype & JT_CROSS ) return 1;         /* (1b) */
    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|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) */
  }
  return 1;
}

    */
    if( fromClauseTermCanBeCoroutine(pParse, pTabList, i, p->selFlags) ){
      /* 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;
    
      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));
      sqlite3Select(pParse, pSub, &dest);
      pItem->pSTab->nRowLogEst = pSub->nSelectRow;

        VdbeNoopComment((v, "materialize %!S", pItem));
      }
      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; kk<i; kk++){
          SrcItem *pX = &pTabList->a[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);
      sqlite3ClearTempRegCache(pParse);
      if( pItem->fg.isCte && pItem->fg.isCorrelated==0 ){

  int regResult;    /* Registers holding results of a co-routine */
};

/*
** 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 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 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
** has been shown to boost performance, in addition to saving memory.
** Access to union elements is gated by the following rules which should

** inside the fg struct.
*/
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. 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 */
    unsigned notCte :1;        /* This item may not match a CTE */
    unsigned isUsing :1;       /* u3.pUsing is valid */

#define JT_CROSS     0x02    /* Explicit use of the CROSS keyword */
#define JT_NATURAL   0x04    /* True for a "natural" join */
#define JT_LEFT      0x08    /* Left outer join */
#define JT_RIGHT     0x10    /* Right outer join */
#define JT_OUTER     0x20    /* The "OUTER" keyword is present */
#define JT_LTORJ     0x40    /* One of the LEFT operands of a RIGHT JOIN
                             ** Mnemonic: Left Table Of Right Join */
#define JT_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.
**
** Value constraints (enforced via assert()):
**     WHERE_USE_LIMIT  == SF_FixedLimit

#define SF_Compound      0x0000100 /* Part of a compound query */
#define SF_Values        0x0000200 /* Synthesized from VALUES clause */
#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_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 */
#define SF_View          0x0200000 /* SELECT statement is a view */
#define SF_NoopOrderBy   0x0400000 /* ORDER BY is ignored for this query */

void sqlite3CodeChangeCount(Vdbe*,int,const char*);
void sqlite3DeleteFrom(Parse*, SrcList*, Expr*, ExprList*, Expr*);
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*);
int sqlite3WhereIsSorted(WhereInfo*);
int sqlite3WhereContinueLabel(WhereInfo*);

      sqlite3_str_appendf(&x, " RIGHT-JOIN");
    }else if( pItem->fg.jointype & JT_CROSS ){
      sqlite3_str_appendf(&x, " CROSS-JOIN");
    }
    if( pItem->fg.jointype & JT_LTORJ ){
      sqlite3_str_appendf(&x, " LTORJ");
    }
    if( pItem->fg.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);
    }
    if( pItem->fg.isOn || (pItem->fg.isUsing==0 && pItem->u3.pOn!=0) ){

        rc = SQLITE_MISMATCH;
        goto abort_due_to_error;
      }else{
        goto jump_to_p2;
      }
    }
  }
#ifdef SQLITE_DEBUG
  pIn1->pScopyFrom = 0;
#endif
  VdbeBranchTaken(0, 2);
  MemSetTypeFlag(pIn1, MEM_Int);
  break;
}

#ifndef SQLITE_OMIT_FLOATING_POINT
/* Opcode: RealAffinity P1 * * * *

** instead of via table lookup.
**
** 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.
*/
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 */
){
  Vdbe *v = pParse->pVdbe;

  sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT);
  if( pPartial ) sqlite3VdbeResolveLabel(v, iContinue);
  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 );
    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);
  }
  sqlite3VdbeJumpHere(v, addrTop);

  pBuilder->iPlanLimit = SQLITE_QUERY_PLANNER_LIMIT;
  for(iTab=0, pItem=pTabList->a; pItem<pEnd; iTab++, pItem++){
    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|JT_LATERAL))!=0
    ){
      /* Add prerequisites to prevent reordering of FROM clause terms
      ** 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]; p<pEnd; p++){
        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
#endif /* SQLITE_OMIT_VIRTUALTABLE */
    {

     || NEVER(pItem->fg.isSubquery==0)
     || pItem->u4.pSubq->pSelect->pOrderBy==0
    ){
      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
** should invoke sqlite3WhereEnd() with the return value of this function
** in order to complete the WHERE clause processing.

      int iOnce = 0;
      assert( pSrc->fg.isSubquery );
      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);
    }
    assert( pTabList == pWInfo->pTabList );
    if( (wsFlags & (WHERE_AUTO_INDEX|WHERE_BLOOMFILTER))!=0 ){

    ** the co-routine into OP_Copy of result contained in a register.
    ** OP_Rowid becomes OP_Null.
    */
    if( pTabItem->fg.viaCoroutine ){
      testcase( pParse->db->mallocFailed );
      assert( pTabItem->fg.isSubquery );
      assert( 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
    ** this optimization prevents the table from ever being read, which can
    ** yield a significant performance boost.

        return 0;
      }
    }
    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.
*/
Bitmask sqlite3WhereCodeOneLoopStart(
  Parse *pParse,       /* Parsing context */

  int bRev;            /* True if we need to scan in reverse order */
  WhereLoop *pLoop;    /* The WhereLoop object being coded */
  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 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 */

  pWC = &pWInfo->sWC;

  );
  if( pLevel->iFrom>0 && (pTabItem[0].fg.jointype & JT_LEFT)!=0 ){
    pLevel->iLeftJoin = ++pParse->nMem;
    sqlite3VdbeAddOp2(v, OP_Integer, 0, pLevel->iLeftJoin);
    VdbeComment((v, "init LEFT JOIN match flag"));
  }









  /* 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 );
    pSubq = pTabItem->u4.pSubq;
    regYield = pSubq->regReturn;

      VdbeComment((v, "pk"));
      VdbeCoverageIf(v, pX->op==TK_GT);
      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, 
                        haltAddress(pWInfo, iLevel, pTabItem));
      VdbeCoverageIf(v, bRev==0);
      VdbeCoverageIf(v, bRev!=0);
    }
    if( pEnd ){
      Expr *pX;
      pX = pEnd->pExpr;
      assert( pX!=0 );

      /* Tables marked isRecursive have only a single row that is stored in
      ** a pseudo-cursor.  No need to Rewind or Next such cursors. */
      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, 
                               haltAddress(pWInfo, iLevel, pTabItem));
      VdbeCoverageIf(v, bRev==0);
      VdbeCoverageIf(v, bRev!=0);
      pLevel->p5 = SQLITE_STMTSTATUS_FULLSCAN_STEP;
    }
  }

#ifdef SQLITE_ENABLE_STMT_SCANSTATUS

# 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)<d
         ) AS lx
   ORDER BY a, c;
} {3 1 2}
do_execsql_test 5.1 {
  SELECT c, a, sb
    FROM dual, t2, LATERAL(
           SELECT a, sum(b) AS sb FROM t1 GROUP BY a HAVING sum(b)<d
         ) AS lx
   ORDER BY a, c;
} {3 1 2}
do_execsql_test 5.2 {
  SELECT c, a, sb
    FROM t2, dual, LATERAL(
           SELECT a, sum(b) AS sb FROM t1 GROUP BY a HAVING sum(b)<d
         ) AS lx
   ORDER BY a, c;
} {3 1 2}

finish_test