SQLite

  }else if( rc==SQLITE_EMPTY ){
    assert( pCur->pgnoRoot==0 || (pCur->pPage!=0 && pCur->pPage->nCell==0) );
    *pRes = 1;
    rc = SQLITE_OK;
  }
  return rc;
}

/* Set *pRes to 1 (true) if the BTree pointed to by cursor pCur contains zero
** rows of content.  Set *pRes to 0 (false) if the table contains content.
** Return SQLITE_OK on success or some error code (ex: SQLITE_NOMEM) if
** something goes wrong.
*/
int sqlite3BtreeIsEmpty(BtCursor *pCur, int *pRes){
  int rc;

  assert( cursorOwnsBtShared(pCur) );
  assert( sqlite3_mutex_held(pCur->pBtree->db->mutex) );
  if( pCur->eState==CURSOR_VALID ){
    *pRes = 0;
    return SQLITE_OK;
  }
  rc = moveToRoot(pCur);
  if( rc==SQLITE_EMPTY ){
    *pRes = 1;
    rc = SQLITE_OK;
  }else{
    *pRes = 0;
  }
  return rc;
}

#ifdef SQLITE_DEBUG
/* The cursors is CURSOR_VALID and has BTCF_AtLast set.  Verify that
** this flags are true for a consistent database.
**
** This routine is is called from within assert() statements only.
** It is an internal verification routine and does not appear in production

  int nData;              /* Size of pData.  0 if none. */
  int nZero;              /* Extra zero data appended after pData,nData */
};

int sqlite3BtreeInsert(BtCursor*, const BtreePayload *pPayload,
                       int flags, int seekResult);
int sqlite3BtreeFirst(BtCursor*, int *pRes);
int sqlite3BtreeIsEmpty(BtCursor *pCur, int *pRes);
int sqlite3BtreeLast(BtCursor*, int *pRes);
int sqlite3BtreeNext(BtCursor*, int flags);
int sqlite3BtreeEof(BtCursor*);
int sqlite3BtreePrevious(BtCursor*, int flags);
i64 sqlite3BtreeIntegerKey(BtCursor*);
void sqlite3BtreeCursorPin(BtCursor*);
void sqlite3BtreeCursorUnpin(BtCursor*);

/*
** Append the contents of SrcList p2 to SrcList p1 and return the resulting
** SrcList. Or, if an error occurs, return NULL. In all cases, p1 and p2
** are deleted by this function.
*/
SrcList *sqlite3SrcListAppendList(Parse *pParse, SrcList *p1, SrcList *p2){
  assert( p1 );
  assert( p2 || pParse->nErr );
  assert( p2==0 || p2->nSrc>=1 );
  testcase( p1->nSrc==0 );
  if( p2 ){
    int nOld = p1->nSrc;
    SrcList *pNew = sqlite3SrcListEnlarge(pParse, p1, p2->nSrc, nOld);
    if( pNew==0 ){
      sqlite3SrcListDelete(pParse->db, p2);
    }else{
      p1 = pNew;
      memcpy(&p1->a[nOld], p2->a, p2->nSrc*sizeof(SrcItem));
      assert( nOld==1 || (p2->a[0].fg.jointype & JT_LTORJ)==0 );
      assert( p1->nSrc>=1 );
      p1->a[0].fg.jointype |= (JT_LTORJ & p2->a[0].fg.jointype);
      sqlite3DbFree(pParse->db, p2);

    }
  }
  return p1;
}

/*
** Add the list of function arguments to the SrcList entry for a

      }
      /* FIX ME:  Compute pExpr->affinity based on the expected return
      ** type of the function
      */
      return WRC_Prune;
    }
#ifndef SQLITE_OMIT_SUBQUERY
    case TK_EXISTS:
    case TK_SELECT:
#endif
    case TK_IN: {
      testcase( pExpr->op==TK_IN );
      testcase( pExpr->op==TK_EXISTS );
      testcase( pExpr->op==TK_SELECT );
      if( ExprUseXSelect(pExpr) ){
        int nRef = pNC->nRef;
        testcase( pNC->ncFlags & NC_IsCheck );
        testcase( pNC->ncFlags & NC_PartIdx );
        testcase( pNC->ncFlags & NC_IdxExpr );
        testcase( pNC->ncFlags & NC_GenCol );
        assert( pExpr->x.pSelect );
        if( pExpr->op==TK_EXISTS )  pParse->bHasExists = 1;
        if( pNC->ncFlags & NC_SelfRef ){
          notValidImpl(pParse, pNC, "subqueries", pExpr, pExpr);
        }else{
          sqlite3WalkSelect(pWalker, pExpr->x.pSelect);
        }
        assert( pNC->nRef>=nRef );
        if( nRef!=pNC->nRef ){

*/
static int tableAndColumnIndex(
  SrcList *pSrc,       /* Array of tables to search */
  int iStart,          /* First member of pSrc->a[] to check */
  int iEnd,            /* Last member of pSrc->a[] to check */
  const char *zCol,    /* Name of the column we are looking for */
  int *piTab,          /* Write index of pSrc->a[] here */
  int *piCol,          /* Write index of pSrc->a[*piTab].pSTab->aCol[] here */
  int bIgnoreHidden    /* Ignore hidden columns */
){
  int i;               /* For looping over tables in pSrc */
  int iCol;            /* Index of column matching zCol */

  assert( iEnd<pSrc->nSrc );
  assert( iStart>=0 );

      case TK_EXCEPT:
      case TK_UNION: {
        int unionTab;    /* Cursor number of the temp table holding result */
        u8 op = 0;       /* One of the SRT_ operations to apply to self */
        int priorOp;     /* The SRT_ operation to apply to prior selects */
        Expr *pLimit;    /* Saved values of p->nLimit  */
        int addr;
        int emptyBypass = 0;   /* IfEmpty opcode to bypass RHS */
        SelectDest uniondest;

 
        testcase( p->op==TK_EXCEPT );
        testcase( p->op==TK_UNION );
        priorOp = SRT_Union;
        if( dest.eDest==priorOp ){
          /* We can reuse a temporary table generated by a SELECT to our
          ** right.

          goto multi_select_end;
        }
 
        /* Code the current SELECT statement
        */
        if( p->op==TK_EXCEPT ){
          op = SRT_Except;
          emptyBypass = sqlite3VdbeAddOp1(v, OP_IfEmpty, unionTab);
          VdbeCoverage(v);
        }else{
          assert( p->op==TK_UNION );
          op = SRT_Union;
        }
        p->pPrior = 0;
        pLimit = p->pLimit;
        p->pLimit = 0;
        uniondest.eDest = op;
        ExplainQueryPlan((pParse, 1, "%s USING TEMP B-TREE",
                          sqlite3SelectOpName(p->op)));
        TREETRACE(0x200, pParse, p, ("multiSelect EXCEPT/UNION right...\n"));
        rc = sqlite3Select(pParse, p, &uniondest);
        testcase( rc!=SQLITE_OK );
        assert( p->pOrderBy==0 );
        pDelete = p->pPrior;
        p->pPrior = pPrior;
        p->pOrderBy = 0;
        if( p->op==TK_UNION ){
          p->nSelectRow = sqlite3LogEstAdd(p->nSelectRow, pPrior->nSelectRow);
        }
        if( emptyBypass ) sqlite3VdbeJumpHere(v, emptyBypass);
        sqlite3ExprDelete(db, p->pLimit);
        p->pLimit = pLimit;
        p->iLimit = 0;
        p->iOffset = 0;
 
        /* Convert the data in the temporary table into whatever form
        ** it is that we currently need.

      default: assert( p->op==TK_INTERSECT ); {
        int tab1, tab2;
        int iCont, iBreak, iStart;
        Expr *pLimit;
        int addr;
        SelectDest intersectdest;
        int r1;
        int emptyBypass;
 
        /* INTERSECT is different from the others since it requires
        ** two temporary tables.  Hence it has its own case.  Begin
        ** by allocating the tables we will need.
        */
        tab1 = pParse->nTab++;
        tab2 = pParse->nTab++;

        */
        sqlite3SelectDestInit(&intersectdest, SRT_Union, tab1);
        TREETRACE(0x400, pParse, p, ("multiSelect INTERSECT left...\n"));
        rc = sqlite3Select(pParse, pPrior, &intersectdest);
        if( rc ){
          goto multi_select_end;
        }
        emptyBypass = sqlite3VdbeAddOp1(v, OP_IfEmpty, tab1); VdbeCoverage(v);
 
        /* Code the current SELECT into temporary table "tab2"
        */
        addr = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, tab2, 0);
        assert( p->addrOpenEphm[1] == -1 );
        p->addrOpenEphm[1] = addr;
        p->pPrior = 0;

        ** tables.
        */
        if( rc ) break;
        assert( p->pEList );
        iBreak = sqlite3VdbeMakeLabel(pParse);
        iCont = sqlite3VdbeMakeLabel(pParse);
        computeLimitRegisters(pParse, p, iBreak);
        sqlite3VdbeAddOp1(v, OP_Rewind, tab1);
        r1 = sqlite3GetTempReg(pParse);
        iStart = sqlite3VdbeAddOp2(v, OP_RowData, tab1, r1);
        sqlite3VdbeAddOp4Int(v, OP_NotFound, tab2, iCont, r1, 0);
        VdbeCoverage(v);
        sqlite3ReleaseTempReg(pParse, r1);
        selectInnerLoop(pParse, p, tab1,
                        0, 0, &dest, iCont, iBreak);
        sqlite3VdbeResolveLabel(v, iCont);
        sqlite3VdbeAddOp2(v, OP_Next, tab1, iStart); VdbeCoverage(v);
        sqlite3VdbeResolveLabel(v, iBreak);
        sqlite3VdbeAddOp2(v, OP_Close, tab2, 0);
        sqlite3VdbeJumpHere(v, emptyBypass);
        sqlite3VdbeAddOp2(v, OP_Close, tab1, 0);
        break;
      }
    }
 
  #ifndef SQLITE_OMIT_EXPLAIN
    if( p->pNext==0 ){

  }

  /* Defer deleting the Table object associated with the
  ** subquery until code generation is
  ** complete, since there may still exist Expr.pTab entries that
  ** refer to the subquery even after flattening.  Ticket #3346.
  **
  ** pSubitem->pSTab is always non-NULL by test restrictions and tests above.
  */
  if( ALWAYS(pSubitem->pSTab!=0) ){
    Table *pTabToDel = pSubitem->pSTab;
    if( pTabToDel->nTabRef==1 ){
      Parse *pToplevel = sqlite3ParseToplevel(pParse);
      sqlite3ParserAddCleanup(pToplevel, sqlite3DeleteTableGeneric, pTabToDel);
      testcase( pToplevel->earlyCleanup );

/*
** This function checks if argument pFrom refers to a CTE declared by
** a WITH clause on the stack currently maintained by the parser (on the
** pParse->pWith linked list).  And if currently processing a CTE
** CTE expression, through routine checks to see if the reference is
** a recursive reference to the CTE.
**
** If pFrom matches a CTE according to either of these two above, pFrom->pSTab
** and other fields are populated accordingly.
**
** Return 0 if no match is found.
** Return 1 if a match is found.
** Return 2 if an error condition is detected.
*/
static int resolveFromTermToCte(

    if( i==0 ) break;
    i--;
    pItem--;
    if( pItem->fg.isSubquery ) return 0;                          /* (1c-i) */
  }
  return 1;
}

/*
** Argument pWhere is the WHERE clause belonging to SELECT statement p. This
** function attempts to transform expressions of the form:
**
**     EXISTS (SELECT ...)
**
** into joins. For example, given
**
**    CREATE TABLE sailors(sid INTEGER PRIMARY KEY, name TEXT);
**    CREATE TABLE reserves(sid INT, day DATE, PRIMARY KEY(sid, day));
**
**    SELECT name FROM sailors AS S WHERE EXISTS (
**      SELECT * FROM reserves AS R WHERE S.sid = R.sid AND R.day = '2022-10-25'
**    );
**
** the SELECT statement may be transformed as follows:
**
**    SELECT name FROM sailors AS S, reserves AS R
**      WHERE S.sid = R.sid AND R.day = '2022-10-25';
**
** **Approximately**.  Really, we have to ensure that the FROM-clause term
** that was formerly inside the EXISTS is only executed once.  This is handled
** by setting the SrcItem.fg.fromExists flag, which then causes code in
** the where.c file to exit the corresponding loop after the first successful
** match (if any).
*/
static SQLITE_NOINLINE void existsToJoin(
  Parse *pParse,  /* Parsing context */
  Select *p,      /* The SELECT statement being optimized */
  Expr *pWhere    /* part of the WHERE clause currently being examined */
){
  if( pWhere 
   && !ExprHasProperty(pWhere, EP_OuterON|EP_InnerON) 
   && p->pSrc->nSrc<BMS
   && pParse->db->mallocFailed==0 
  ){
    if( pWhere->op==TK_AND ){
      Expr *pRight = pWhere->pRight;
      existsToJoin(pParse, p, pWhere->pLeft);
      existsToJoin(pParse, p, pRight);
    }
    else if( pWhere->op==TK_EXISTS ){
      Select *pSub = pWhere->x.pSelect;
      Expr *pSubWhere = pSub->pWhere;
      if( pSub->pSrc->nSrc==1
       && (pSub->selFlags & SF_Aggregate)==0
       && !pSub->pSrc->a[0].fg.isSubquery
      ){
        memset(pWhere, 0, sizeof(*pWhere));
        pWhere->op = TK_INTEGER;
        pWhere->u.iValue = 1;
        ExprSetProperty(pWhere, EP_IntValue);

        assert( p->pWhere!=0 );
        pSub->pSrc->a[0].fg.fromExists = 1;
        pSub->pSrc->a[0].fg.jointype |= JT_CROSS;
        p->pSrc = sqlite3SrcListAppendList(pParse, p->pSrc, pSub->pSrc);
        if( pSubWhere ){
          p->pWhere = sqlite3PExpr(pParse, TK_AND, p->pWhere, pSubWhere);
          pSub->pWhere = 0;
        }
        pSub->pSrc = 0;
        sqlite3ParserAddCleanup(pParse, sqlite3SelectDeleteGeneric, pSub);
#if TREETRACE_ENABLED
        if( sqlite3TreeTrace & 0x100000 ){
          TREETRACE(0x100000,pParse,p,
                    ("After EXISTS-to-JOIN optimization:\n"));
          sqlite3TreeViewSelect(0, p, 0);
        }
#endif
        existsToJoin(pParse, p, pSubWhere);
      }
    }
  }
}

/*
** Generate byte-code for the SELECT statement given in the p argument. 
**
** The results are returned according to the SelectDest structure.
** See comments in sqliteInt.h for further information.
**

      sqlite3TreeViewSelect(0, p, 0);
    }
#endif
    if( p->pNext==0 ) ExplainQueryPlanPop(pParse);
    return rc;
  }
#endif

  /* If there may be an "EXISTS (SELECT ...)" in the WHERE clause, attempt
  ** to change it into a join.  */
  if( pParse->bHasExists && OptimizationEnabled(db,SQLITE_ExistsToJoin) ){
    existsToJoin(pParse, p, p->pWhere);
    pTabList = p->pSrc;
  }

  /* Do the WHERE-clause constant propagation optimization if this is
  ** a join.  No need to spend time on this operation for non-join queries
  ** as the equivalent optimization will be handled by query planner in
  ** sqlite3WhereBegin().  tag-select-0330
  */
  if( p->pWhere!=0

            { 0x00800000, 1, "FlttnUnionAll" },
            { 0x01000000, 1, "IndexedEXpr" },
            { 0x02000000, 1, "Coroutines" },
            { 0x04000000, 1, "NullUnusedCols" },
            { 0x08000000, 1, "OnePass" },
            { 0x10000000, 1, "OrderBySubq" },
            { 0x20000000, 1, "StarQuery" },
            { 0x40000000, 1, "ExistsToJoin" },
            { 0xffffffff, 0, "All" },
          };
          unsigned int curOpt;
          unsigned int newOpt;
          unsigned int m;
          int ii;
          int nOff;

**   0x00004000     Push-down optimization
**   0x00008000     After all FROM-clause analysis
**   0x00010000     Beginning of DELETE/INSERT/UPDATE processing
**   0x00020000     Transform DISTINCT into GROUP BY
**   0x00040000     SELECT tree dump after all code has been generated
**   0x00080000     NOT NULL strength reduction
**   0x00100000     Pointers are all shown as zero
**   0x00200000     EXISTS-to-JOIN optimization
*/

/*
** Macros for "wheretrace"
*/
extern u32 sqlite3WhereTrace;
#if defined(SQLITE_DEBUG) \

   /* TH3 expects this value  ^^^^^^^^^^ See flatten04.test */
#define SQLITE_IndexedExpr    0x01000000 /* Pull exprs from index when able */
#define SQLITE_Coroutines     0x02000000 /* Co-routines for subqueries */
#define SQLITE_NullUnusedCols 0x04000000 /* NULL unused columns in subqueries */
#define SQLITE_OnePass        0x08000000 /* Single-pass DELETE and UPDATE */
#define SQLITE_OrderBySubq    0x10000000 /* ORDER BY in subquery helps outer */
#define SQLITE_StarQuery      0x20000000 /* Heurists for star queries */
#define SQLITE_ExistsToJoin   0x40000000 /* The EXISTS-to-JOIN optimization */
#define SQLITE_AllOpts        0xffffffff /* All optimizations */

/*
** Macros for testing whether or not optimizations are enabled or disabled.
*/
#define OptimizationDisabled(db, mask)  (((db)->dbOptFlags&(mask))!=0)
#define OptimizationEnabled(db, mask)   (((db)->dbOptFlags&(mask))==0)

    unsigned isUsing :1;       /* u3.pUsing is valid */
    unsigned isOn :1;          /* u3.pOn was once valid and non-NULL */
    unsigned isSynthUsing :1;  /* u3.pUsing is synthesized from NATURAL */
    unsigned isNestedFrom :1;  /* pSelect is a SF_NestedFrom subquery */
    unsigned rowidUsed :1;     /* The ROWID of this table is referenced */
    unsigned fixedSchema :1;   /* Uses u4.pSchema, not u4.zDatabase */
    unsigned hadSchema :1;     /* Had u4.zDatabase before u4.pSchema */
    unsigned fromExists :1;    /* Comes from WHERE EXISTS(...) */
  } fg;
  int iCursor;      /* The VDBE cursor number used to access this table */
  Bitmask colUsed;  /* Bit N set if column N used. Details above for N>62 */
  union {
    char *zIndexedBy;    /* Identifier from "INDEXED BY <zIndex>" clause */
    ExprList *pFuncArg;  /* Arguments to table-valued-function */
    u32 nRow;            /* Number of rows in a VALUES clause */

  u8 nTempReg;         /* Number of temporary registers in aTempReg[] */
  u8 isMultiWrite;     /* True if statement may modify/insert multiple rows */
  u8 mayAbort;         /* True if statement may throw an ABORT exception */
  u8 hasCompound;      /* Need to invoke convertCompoundSelectToSubquery() */
  u8 disableLookaside; /* Number of times lookaside has been disabled */
  u8 prepFlags;        /* SQLITE_PREPARE_* flags */
  u8 withinRJSubrtn;   /* Nesting level for RIGHT JOIN body subroutines */
  u8 bHasExists;       /* Has a correlated "EXISTS (SELECT ....)" expression */
  u8 mSubrtnSig;       /* mini Bloom filter on available SubrtnSig.selId */
  u8 eTriggerOp;       /* TK_UPDATE, TK_INSERT or TK_DELETE */
  u8 bReturning;       /* Coding a RETURNING trigger */
  u8 eOrconf;          /* Default ON CONFLICT policy for trigger steps */
  u8 disableTriggers;  /* True to disable triggers */
#if defined(SQLITE_DEBUG) || defined(SQLITE_COVERAGE_TEST)
  u8 earlyCleanup;     /* OOM inside sqlite3ParserAddCleanup() */

  pC->nullRow = (u8)res;
  if( pOp->p2>0 ){
    VdbeBranchTaken(res!=0,2);
    if( res ) goto jump_to_p2;
  }
  break;
}

/* Opcode: IfEmpty P1 P2 * * *
** Synopsis: if( empty(P1) ) goto P2
**
** Check to see if the b-tree table that cursor P1 references is empty
** and jump to P2 if it is.
*/
case OP_IfEmpty: {        /* jump */
  VdbeCursor *pC;
  BtCursor *pCrsr;
  int res;

  assert( pOp->p1>=0 && pOp->p1<p->nCursor );
  assert( pOp->p2>=0 && pOp->p2<p->nOp );

  pC = p->apCsr[pOp->p1];
  assert( pC!=0 );
  assert( pC->eCurType==CURTYPE_BTREE );
  pCrsr = pC->uc.pCursor;
  assert( pCrsr );
  rc = sqlite3BtreeIsEmpty(pCrsr, &res);
  if( rc ) goto abort_due_to_error;
  VdbeBranchTaken(res!=0,2);
  if( res ) goto jump_to_p2;
  break;
}

/* Opcode: Next P1 P2 P3 * P5
**
** Advance cursor P1 so that it points to the next key/data pair in its
** table or index.  If there are no more key/value pairs then fall through
** to the following instruction.  But if the cursor advance was successful,
** jump immediately to P2.

  if( saved_nEq==saved_nSkip
   && saved_nEq+1<pProbe->nKeyCol
   && saved_nEq==pNew->nLTerm
   && pProbe->noSkipScan==0
   && pProbe->hasStat1!=0
   && OptimizationEnabled(db, SQLITE_SkipScan)
   && pProbe->aiRowLogEst[saved_nEq+1]>=42  /* TUNING: Minimum for skip-scan */
   && pSrc->fg.fromExists==0
   && (rc = whereLoopResize(db, pNew, pNew->nLTerm+1))==SQLITE_OK
  ){
    LogEst nIter;
    pNew->u.btree.nEq++;
    pNew->nSkip++;
    pNew->aLTerm[pNew->nLTerm++] = 0;
    pNew->wsFlags |= WHERE_SKIPSCAN;

      {
        sqlite3VdbeChangeP5(v, bFordelete);
      }
#ifdef SQLITE_ENABLE_COLUMN_USED_MASK
      sqlite3VdbeAddOp4Dup8(v, OP_ColumnsUsed, pTabItem->iCursor, 0, 0,
                            (const u8*)&pTabItem->colUsed, P4_INT64);
#endif
      if( ii>=2
       && (pTabItem[0].fg.jointype & (JT_LTORJ|JT_LEFT))==0 
       && pLevel->addrHalt==pWInfo->a[0].addrHalt
      ){
        sqlite3VdbeAddOp2(v, OP_IfEmpty, pTabItem->iCursor, pWInfo->iBreak);
        VdbeCoverage(v);
      }
    }else{
      sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);
    }
    if( pLoop->wsFlags & WHERE_INDEXED ){
      Index *pIx = pLoop->u.btree.pIndex;
      int iIndexCur;
      int op = OP_OpenRead;

        op = pLevel->op==OP_Prev ? OP_SeekLT : OP_SeekGT;
        addrSeek = sqlite3VdbeAddOp4Int(v, op, pLevel->iIdxCur, 0, r1, n);
        VdbeCoverageIf(v, op==OP_SeekLT);
        VdbeCoverageIf(v, op==OP_SeekGT);
        sqlite3VdbeAddOp2(v, OP_Goto, 1, pLevel->p2);
      }
#endif /* SQLITE_DISABLE_SKIPAHEAD_DISTINCT */
      if( pTabList->a[pLevel->iFrom].fg.fromExists ){
        sqlite3VdbeAddOp2(v, OP_Goto, 0, sqlite3VdbeCurrentAddr(v)+2);
      }
      /* The common case: Advance to the next row */
      if( pLevel->addrCont ) sqlite3VdbeResolveLabel(v, pLevel->addrCont);
      sqlite3VdbeAddOp3(v, pLevel->op, pLevel->p1, pLevel->p2, pLevel->p3);
      sqlite3VdbeChangeP5(v, pLevel->p5);
      VdbeCoverage(v);
      VdbeCoverageIf(v, pLevel->op==OP_Next);
      VdbeCoverageIf(v, pLevel->op==OP_Prev);

  u16 wctrlFlags                  /* Flags passed to sqlite3WhereBegin() */
){
#if !defined(SQLITE_DEBUG)
  if( sqlite3ParseToplevel(pParse)->explain==2 || IS_STMT_SCANSTATUS(pParse->db) )
#endif
  {
    VdbeOp *pOp = sqlite3VdbeGetOp(pParse->pVdbe, addr);

    SrcItem *pItem = &pTabList->a[pLevel->iFrom];
    sqlite3 *db = pParse->db;     /* Database handle */
    int isSearch;                 /* True for a SEARCH. False for SCAN. */
    WhereLoop *pLoop;             /* The controlling WhereLoop object */
    u32 flags;                    /* Flags that describe this loop */
#if defined(SQLITE_DEBUG) && !defined(SQLITE_OMIT_EXPLAIN)
    char *zMsg;                   /* Text to add to EQP output */

    isSearch = (flags&(WHERE_BTM_LIMIT|WHERE_TOP_LIMIT))!=0
            || ((flags&WHERE_VIRTUALTABLE)==0 && (pLoop->u.btree.nEq>0))
            || (wctrlFlags&(WHERE_ORDERBY_MIN|WHERE_ORDERBY_MAX));

    sqlite3StrAccumInit(&str, db, zBuf, sizeof(zBuf), SQLITE_MAX_LENGTH);
    str.printfFlags = SQLITE_PRINTF_INTERNAL;
    sqlite3_str_appendf(&str, "%s %S%s",
       isSearch ? "SEARCH" : "SCAN",
       pItem,
       pItem->fg.fromExists ? " EXISTS" : "");
    if( (flags & (WHERE_IPK|WHERE_VIRTUALTABLE))==0 ){
      const char *zFmt = 0;
      Index *pIdx;

      assert( pLoop->u.btree.pIndex!=0 );
      pIdx = pLoop->u.btree.pIndex;
      assert( !(flags&WHERE_AUTO_INDEX) || (flags&WHERE_IDX_ONLY) );

     `--SCAN t2
}
det 3.3.3 {
  SELECT * FROM t1 WHERE EXISTS (SELECT y FROM t2 WHERE t1.x!=t2.x)
} {
  QUERY PLAN
  |--SCAN t1

  `--SCAN t2 EXISTS
}

#-------------------------------------------------------------------------
# Test cases eqp-4.* - tests for composite select statements.
#
do_eqp_test 4.1.1 {
  SELECT * FROM t1 UNION ALL SELECT * FROM t2

# 2024 May 25
#
# 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.
#
#***********************************************************************
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
source $testdir/lock_common.tcl
set testprefix existsexpr


do_execsql_test 1.0 {
  CREATE TABLE x1(a, b, PRIMARY KEY(a)) WITHOUT ROWID;
  INSERT INTO x1 VALUES(1, 2), (3, 4), (5, 6);
  CREATE INDEX x1b ON x1(b);

  CREATE TABLE x2(x, y);
  INSERT INTO x2 VALUES(1, 2), (3, 4), (5, 6);
}

do_execsql_test 1.1 {
  SELECT 1 WHERE EXISTS (SELECT 1 FROM x1 WHERE a=5)
} {1}

do_execsql_test 1.2 {
  SELECT * FROM x2 WHERE EXISTS (SELECT 1 FROM x1 WHERE a=x)
} {1 2 3 4 5 6}

# With "a=x", the UNIQUE index means the EXIST can be transformed to a join.
# So no "SUBQUERY". With "b=x", the index is not UNIQUE and so there is a
# "SUBQUERY".
do_execsql_test 1.3.1 {
  EXPLAIN QUERY PLAN
  SELECT * FROM x2 WHERE EXISTS (SELECT 1 FROM x1 WHERE a=x)
} {~/SUBQUERY/}
do_execsql_test 1.3.2 {
  EXPLAIN QUERY PLAN
  SELECT * FROM x2 WHERE EXISTS (SELECT 1 FROM x1 WHERE b=x)
} {~/SUBQUERY/}

do_execsql_test 1.4.1 {
  EXPLAIN QUERY PLAN
  SELECT * FROM x2 WHERE x=1 AND EXISTS (SELECT 1 FROM x1 WHERE a=x)
} {~/SUBQUERY/}
do_execsql_test 1.4.2 {
  EXPLAIN QUERY PLAN
  SELECT * FROM x2 WHERE EXISTS (SELECT 1 FROM x1 WHERE a=x) AND y=2
} {~/SUBQUERY/}

do_execsql_test 1.5 {
  SELECT count(*) FROM x2 WHERE EXISTS (SELECT 1 FROM x1 WHERE a=x)
} {3}

#-------------------------------------------------------------------------
do_execsql_test 2.0 {
  CREATE TABLE t1(a, b);
  WITH s(i) AS (
    SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<1000
  ) INSERT INTO t1 SELECT i, i FROM s;

  CREATE TABLE t2(c, d);
  WITH s(i) AS (
    SELECT 10 UNION ALL SELECT i+10 FROM s WHERE i<1000
  ) INSERT INTO t2 SELECT i, i FROM s;
}

do_execsql_test 2.1 {
  SELECT count(*) FROM t1;
  SELECT count(*) FROM t2;
} {1000 100}

do_execsql_test 2.2 {
  SELECT count(*) FROM t1, t2 WHERE a=c;
} {100}

do_execsql_test 2.3 {
  SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c=a)
} {100}
do_eqp_test 2.4 {
  SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c=a)
} {SCAN t1}

do_execsql_test 2.4.0 {
  CREATE UNIQUE INDEX t2c ON t2(c);
  CREATE UNIQUE INDEX t1a ON t1(a);
}

do_eqp_test 2.4.1 {
  SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c=a);
} {SCAN t1*t2 EXISTS}
do_execsql_test 2.4.2 {
  ANALYZE;
}
do_eqp_test 2.4.3 {
  SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c=a);
} {SCAN t1*t2 EXISTS}
do_execsql_test 2.4.4 {
  SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c=a);
} {100}

do_execsql_test 2.5.1 {
  EXPLAIN QUERY PLAN
  SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.rowid=a);
} {~/SUBQUERY/}

#-------------------------------------------------------------------------
proc do_subquery_test {tn bSub sql res} {
  set r1(0) ~/SUBQUERY/
  set r1(1) /SUBQUERY/
  do_execsql_test $tn.1 "explain query plan $sql" $r1($bSub)
  do_execsql_test $tn.2 $sql $res
}

do_execsql_test 3.0 {
  CREATE TABLE y1(a, b, c);
  CREATE TABLE y2(x, y, z);
  CREATE UNIQUE INDEX y2zy ON y2(z, y);

  INSERT INTO y1 VALUES(1, 1, 1);
  INSERT INTO y1 VALUES(2, 2, 2);
  INSERT INTO y1 VALUES(3, 3, 3);
  INSERT INTO y1 VALUES(4, 4, 4);

  INSERT INTO y2 VALUES(1, 1, 1);
  INSERT INTO y2 VALUES(3, 3, 3);
}

do_subquery_test 3.1 0 {
  SELECT * FROM y1 WHERE EXISTS ( 
      SELECT 1 FROM y2 WHERE z=a AND y=b AND x=z
  )
} {
  1 1 1          3 3 3
}    

do_subquery_test 3.2 0 {
  SELECT * FROM y1 WHERE EXISTS ( 
      SELECT 1 FROM y2 WHERE z=max(a,b) AND y=min(b,a) AND x=z
  )
} {
  1 1 1          3 3 3
}    

do_subquery_test 3.3 0 {
  SELECT * FROM y1 WHERE EXISTS ( 
      SELECT 1 FROM y2 WHERE z=max(a,b) AND y=min(b,a) AND c!=3
  )
} {
  1 1 1
}    

do_subquery_test 3.4 0 {
  SELECT * FROM y1 WHERE EXISTS ( 
      SELECT 1 FROM y2 WHERE z=max(a,b) AND b=3
  )
} {
  3 3 3
}    

do_subquery_test 3.5 0 {
  SELECT * FROM y1 WHERE EXISTS ( 
      SELECT 1 FROM y2 WHERE z=a-1 AND y=a-1
  )
} {
  2 2 2
  4 4 4
}    

do_subquery_test 3.6 0 {
  SELECT * FROM y1 WHERE EXISTS ( 
      SELECT 1 FROM y2 WHERE z=a-1 AND y+1=a
  )
} {
  2 2 2
  4 4 4
}    

do_subquery_test 3.7 1 {
  SELECT * FROM y1 WHERE EXISTS ( 
      SELECT count(*) FROM y2 WHERE z=a-1 AND y=a-1
  )
} {
  1 1 1
  2 2 2
  3 3 3
  4 4 4
}    

do_subquery_test 3.8 0 {
  SELECT * FROM y1 WHERE EXISTS ( SELECT a+1 FROM y2 )
} {
  1 1 1
  2 2 2
  3 3 3
  4 4 4
}    

do_subquery_test 3.9 1 {
  SELECT * FROM y1 WHERE EXISTS ( 
      SELECT 1 FROM y2 one, y2 two WHERE one.z=a-1 AND one.y=a-1
  )
} {
  2 2 2
  4 4 4
}    

#-------------------------------------------------------------------------
reset_db
do_execsql_test 4.0 {
  CREATE TABLE tx1(a TEXT COLLATE nocase, b TEXT);
  CREATE UNIQUE INDEX tx1ab ON tx1(a, b);

  INSERT INTO tx1 VALUES('a', 'a');
  INSERT INTO tx1 VALUES('B', 'b');
  INSERT INTO tx1 VALUES('c', 'c');
  INSERT INTO tx1 VALUES('D', 'd');
  INSERT INTO tx1 VALUES('e', 'e');

  CREATE TABLE tx2(x, y);
  INSERT INTO tx2 VALUES('A', 'a');
  INSERT INTO tx2 VALUES('b', 'b');
  INSERT INTO tx2 VALUES('C', 'c');
  INSERT INTO tx2 VALUES('D', 'd');
}

do_subquery_test 4.1 0 {
  SELECT * FROM tx2 WHERE EXISTS (
    SELECT 1 FROM tx1 WHERE a=x AND b=y
  )
} {
  A a
  b b
  C c
  D d
}

do_subquery_test 4.1.1 0 {
  SELECT * FROM tx2 WHERE EXISTS (
    SELECT 1 FROM tx1 WHERE (a COLLATE nocase)=x AND b=y
  )
} {
  A a   b b   C c   D d
}
do_subquery_test 4.1.2 0 {
  SELECT * FROM tx2 WHERE EXISTS (
    SELECT 1 FROM tx1 WHERE a=x AND (b COLLATE binary)=y
  )
} {
  A a   b b   C c   D d
}
do_subquery_test 4.1.1 0 {
  SELECT * FROM tx2 WHERE EXISTS (
    SELECT 1 FROM tx1 WHERE x=(a COLLATE nocase) AND b=y
  )
} {
  A a   b b   C c   D d
}
do_subquery_test 4.1.2 0 {
  SELECT * FROM tx2 WHERE EXISTS (
    SELECT 1 FROM tx1 WHERE a=x AND y=(b COLLATE binary)
  )
} {
  A a   b b   C c   D d
}

do_subquery_test 4.2 0 {
  SELECT * FROM tx2 WHERE EXISTS (
    SELECT 1 FROM tx1 WHERE a=x AND b=y COLLATE nocase
  )
} {
  A a
  b b
  C c
  D d
}

do_execsql_test 4.3 {
  DROP INDEX tx1ab;
  CREATE UNIQUE INDEX tx1ab ON tx1(a COLLATE binary, b);
}

do_subquery_test 4.4 0 {
  SELECT * FROM tx2 WHERE EXISTS (
    SELECT 1 FROM tx1 WHERE a=x AND b=y
  )
} {
  A a
  b b
  C c
  D d
}

do_subquery_test 4.4 0 {
  SELECT * FROM tx2 WHERE EXISTS (
    SELECT 1 FROM tx1 WHERE a=x COLLATE binary AND b=y
  )
} {
  D d
}

do_subquery_test 4.4 1 {
  SELECT EXISTS ( SELECT x FROM tx1 ) FROM tx2
} {
  1 1 1 1
}

do_subquery_test 4.4 1 {
  SELECT (SELECT EXISTS ( SELECT x FROM tx1 ) WHERE 1) FROM tx2
} {
  1 1 1 1
}

#-------------------------------------------------------------------------
proc cols {s f} {
  set lCols [list]
  for {set i $s} {$i<=$f} {incr i} {
    lappend lCols [format "c%02d" $i]
  }
  join $lCols ", "
}
proc vals {n val} {
  set lVal [list]
  for {set i 0} {$i<$n} {incr i} {
    lappend lVal $val
  }
  join $lVal ", "
}
proc exprs {s f} {
  set lExpr [list]
  for {set i $s} {$i<=$f} {incr i} {
    lappend lExpr [format "c%02d = o" $i]
  }
  join $lExpr " AND "
}


do_execsql_test 5.0 "
  CREATE TABLE a1( [cols 0 99] );
"
do_execsql_test 5.1 "
  -- 63 column index
  CREATE UNIQUE INDEX a1idx1 ON a1( [cols 0 62] );
"
do_execsql_test 5.2 "
  -- 64 column index
  CREATE UNIQUE INDEX a1idx2 ON a1( [cols 10 73] );
"
do_execsql_test 5.2 "
  -- 65 column index
  CREATE UNIQUE INDEX a1idx3 ON a1( [cols 20 84] );
"

do_test 5.3 {
  foreach v {1 2 3 4 5 6} {
    execsql "INSERT INTO a1 VALUES( [vals 100 $v] )"
  }
} {}

do_execsql_test 5.4 {
  CREATE TABLE a2(o);
  INSERT INTO a2 VALUES(2), (5); 
}

do_subquery_test 5.5 0 "
  SELECT o FROM a2 WHERE EXISTS (
    SELECT 1 FROM a1 WHERE [exprs 0 62]
  )
" {
  2 5
}

do_subquery_test 5.6 0 "
  SELECT o FROM a2 WHERE EXISTS (
    SELECT 1 FROM a1 WHERE [exprs 10 73]
  )
" {
  2 5
}

do_subquery_test 5.7 0 "
  SELECT o FROM a2 WHERE EXISTS (
    SELECT 1 FROM a1 WHERE [exprs 20 84]
  )
" {
  2 5
}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 6.0 {
  CREATE TABLE t1(a, b UNIQUE, c UNIQUE);
  CREATE TABLE t2(a INfEGER PRIMARY KEY, b);
  CREATE UNIQUE INDEX t2b ON t2(b);
}

do_catchsql_test 6.1 {
  SELECT a FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c COLLATE f = a)
} {1 {no such collation sequence: f}}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 7.0 {
  CREATE TABLE t1(x);
  CREATE TABLE t2(y UNIQUE);

  INSERT INTO t1 VALUES(1), (2);
  INSERT INTO t2 VALUES(1), (3);

  SELECT * FROM t1 one LEFT JOIN t1 two ON (one.x=two.x AND EXISTS (
    SELECT 1 FROM t2 WHERE y=one.x
  ));
} {
  1 1
  2 {}
}



finish_test

# 2024 June 14
#
# 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.
#
#***********************************************************************
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
source $testdir/lock_common.tcl
set testprefix existsexpr2


do_execsql_test 1.0 {
  CREATE TABLE x1(a, b, PRIMARY KEY(a)) WITHOUT ROWID;
  INSERT INTO x1 VALUES(1, 2), (3, 4), (5, 6);
  CREATE INDEX x1b ON x1(b);

  CREATE TABLE x2(x, y);
  INSERT INTO x2 VALUES(1, 2), (3, 4), (5, 6);
}

do_execsql_test 1.1 {
  SELECT * FROM x1 WHERE EXISTS (SELECT 1 FROM x2 WHERE a!=123)
} {1 2   3 4   5 6}

do_execsql_test 1.2 {
  CREATE TABLE x3(u, v);
  CREATE INDEX x3u ON x3(u);
  INSERT INTO x3 VALUES
    (1, 1), (1, 2), (1, 3),
    (2, 1), (2, 2), (2, 3);
}

do_execsql_test 1.3 {
  SELECT * FROM x1 WHERE EXISTS (
    SELECT 1 FROM x3 WHERE u IN (1, 2, 3, 4) AND v=b
  );
} {
  1 2
}

#-------------------------------------------------------------------------
#
reset_db
do_execsql_test 2.0 {
  CREATE TABLE t1(a, b, c);
  CREATE INDEX t1ab ON t1(a,b);

  INSERT INTO t1 VALUES
      ('abc', 1, 1),
      ('abc', 2, 2),
      ('abc', 2, 3),

      ('def', 1, 1),
      ('def', 2, 2),
      ('def', 2, 3);

  CREATE TABLE t2(x, y);
  INSERT INTO t2 VALUES(1, 1), (2, 2), (3, 3);

  ANALYZE;
  DELETE FROM sqlite_stat1;
  INSERT INTO sqlite_stat1 VALUES('t1','t1ab','10000 5000 2');
  ANALYZE sqlite_master;
}


do_execsql_test 2.1 {
  SELECT a,b,c FROM t1 WHERE b=2 ORDER BY a
} {
  abc 2 2
  abc 2 3
  def 2 2
  def 2 3
}

do_execsql_test 2.2 {
  SELECT x, y FROM t2 WHERE EXISTS (
    SELECT 1 FROM t1 WHERE b=x
  )
} {
  1 1
  2 2
}



finish_test

# 2024 May 25
#
# 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.
#
#***********************************************************************
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
source $testdir/lock_common.tcl
source $testdir/malloc_common.tcl
set testprefix existsfault

db close
sqlite3_shutdown
sqlite3_config_lookaside 0 0
sqlite3_initialize
autoinstall_test_functions
sqlite3 db test.db

do_execsql_test 1.0 {
  CREATE TABLE x1(a, b);
  INSERT INTO x1 VALUES(1, 2), (3, 4), (5, 6);
  CREATE UNIQUE INDEX x1a ON x1(a);
  CREATE INDEX x1b ON x1(b);

  CREATE TABLE x2(x, y);
  INSERT INTO x2 VALUES(1, 2), (3, 4), (5, 6);
}

do_faultsim_test 1 -faults oom* -prep {
  sqlite3 db test.db
  execsql { SELECT * FROM sqlite_schema }
} -body {
  execsql {
    SELECT count(*) FROM x2 WHERE EXISTS (SELECT 1 FROM x1 WHERE a=x) AND y!=11
  }
} -test {
  faultsim_test_result {0 3}
}

finish_test

  INSERT INTO t1(id,json) VALUES(1,'{"items":[3,5]}');
  CREATE TABLE t2(id, json);
  INSERT INTO t2(id,json) VALUES(2,'{"value":2}');
  INSERT INTO t2(id,json) VALUES(3,'{"value":3}');
  INSERT INTO t2(id,json) VALUES(4,'{"value":4}');
  INSERT INTO t2(id,json) VALUES(5,'{"value":5}');
  INSERT INTO t2(id,json) VALUES(6,'{"value":6}');
  SELECT *, 'NL' FROM t1 CROSS JOIN t2
   WHERE EXISTS(SELECT 1 FROM json_each(t1.json,'$.items') AS Z
                 WHERE Z.value==t2.id);
} {1 {{"items":[3,5]}} 3 {{"value":3}} NL 1 {{"items":[3,5]}} 5 {{"value":5}} NL}
do_execsql_test json101-13.110 {
  SELECT *, 'NL' FROM t2 CROSS JOIN t1
   WHERE EXISTS(SELECT 1 FROM json_each(t1.json,'$.items') AS Z
                 WHERE Z.value==t2.id);
} {3 {{"value":3}} 1 {{"items":[3,5]}} NL 5 {{"value":5}} 1 {{"items":[3,5]}} NL}

# 2018-05-16
# Incorrect fullkey output from json_each()
# when the input JSON is not an array or object.
#
do_execsql_test json101-14.100 {
  SELECT fullkey FROM json_each('123');

    SELECT 1 FROM t1 WHERE t1.a=450 AND t2.d IS NULL
  )
} 7000 {0}
do_vmstep_test 1.5.2 {
  SELECT count(*) FROM t2 WHERE EXISTS(
    SELECT 1 FROM t1 WHERE t1.a=450 AND t2.c IS NULL
  )
} 4000 {0}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 2.0 {
  CREATE TABLE T1(a INTEGER PRIMARY KEY, b);
  CREATE TABLE T3(k, v);
}