SQLite

    /* If the LHS and RHS of the IN operator do not match, that
    ** error will have been caught long before we reach this point. */
    if( ALWAYS(pEList->nExpr==nVal) ){
      Select *pCopy;
      SelectDest dest;
      int i;
      int rc;
      int addrBloom = 0;
      sqlite3SelectDestInit(&dest, SRT_Set, iTab);
      dest.zAffSdst = exprINAffinity(pParse, pExpr);
      pSelect->iLimit = 0;
      if( addrOnce && OptimizationEnabled(pParse->db, SQLITE_BloomFilter) ){
        int regBloom = ++pParse->nMem;
        addrBloom = sqlite3VdbeAddOp2(v, OP_Blob, 10000, regBloom);
        VdbeComment((v, "Bloom filter"));
        dest.iSDParm2 = regBloom;
      }
      testcase( pSelect->selFlags & SF_Distinct );
      testcase( pKeyInfo==0 ); /* Caused by OOM in sqlite3KeyInfoAlloc() */
      pCopy = sqlite3SelectDup(pParse->db, pSelect, 0);
      rc = pParse->db->mallocFailed ? 1 :sqlite3Select(pParse, pCopy, &dest);
      sqlite3SelectDelete(pParse->db, pCopy);
      sqlite3DbFree(pParse->db, dest.zAffSdst);
      if( addrBloom ){
        sqlite3VdbeGetOp(v, addrOnce)->p3 = dest.iSDParm2;
        if( dest.iSDParm2==0 ){
          sqlite3VdbeChangeToNoop(v, addrBloom);
        }else{
          sqlite3VdbeGetOp(v, addrOnce)->p3 = dest.iSDParm2;
        }
      }
      if( rc ){
        sqlite3KeyInfoUnref(pKeyInfo);
        return;
      }
      assert( pKeyInfo!=0 ); /* OOM will cause exit after sqlite3Select() */
      assert( pEList!=0 );
      assert( pEList->nExpr>0 );
      assert( sqlite3KeyInfoIsWriteable(pKeyInfo) );
      for(i=0; i<nVal; i++){
        Expr *p = sqlite3VectorFieldSubexpr(pLeft, i);
        pKeyInfo->aColl[i] = sqlite3BinaryCompareCollSeq(

    sqlite3VdbeAddOp3(v, OP_SeekRowid, iTab, destIfFalse, rLhs);
    VdbeCoverage(v);
    addrTruthOp = sqlite3VdbeAddOp0(v, OP_Goto);  /* Return True */
  }else{
    sqlite3VdbeAddOp4(v, OP_Affinity, rLhs, nVector, 0, zAff, nVector);
    if( destIfFalse==destIfNull ){
      /* Combine Step 3 and Step 5 into a single opcode */
      if( ExprHasProperty(pExpr, EP_Subrtn) ){
        const VdbeOp *pOp = sqlite3VdbeGetOp(v, pExpr->y.sub.iAddr);
        assert( pOp->opcode==OP_Once || pParse->nErr );
        if( pOp->opcode==OP_Once && pOp->p3>0 ){
          assert( OptimizationEnabled(pParse->db, SQLITE_BloomFilter) );
          sqlite3VdbeAddOp4Int(v, OP_Filter, pOp->p3, destIfFalse,
                               rLhs, nVector); VdbeCoverage(v);
        }
      }
      sqlite3VdbeAddOp4Int(v, OP_NotFound, iTab, destIfFalse,
                           rLhs, nVector); VdbeCoverage(v);
      goto sqlite3ExprCodeIN_finished;
    }
    /* Ordinary Step 3, for the case where FALSE and NULL are distinct */
    addrTruthOp = sqlite3VdbeAddOp4Int(v, OP_Found, iTab, 0,
                                      rLhs, nVector); VdbeCoverage(v);

      if( pSort ){
        /* At first glance you would think we could optimize out the
        ** ORDER BY in this case since the order of entries in the set
        ** does not matter.  But there might be a LIMIT clause, in which
        ** case the order does matter */
        pushOntoSorter(
            pParse, pSort, p, regResult, regOrig, nResultCol, nPrefixReg);
        pDest->iSDParm2 = 0; /* Signal that any Bloom filter is unpopulated */
      }else{
        int r1 = sqlite3GetTempReg(pParse);
        assert( sqlite3Strlen30(pDest->zAffSdst)==nResultCol );
        sqlite3VdbeAddOp4(v, OP_MakeRecord, regResult, nResultCol,
            r1, pDest->zAffSdst, nResultCol);
        sqlite3VdbeAddOp4Int(v, OP_IdxInsert, iParm, r1, regResult, nResultCol);
        if( pDest->iSDParm2 ){
          sqlite3VdbeAddOp4Int(v, OP_FilterAdd, pDest->iSDParm2, 0,
                               regResult, nResultCol);
          ExplainQueryPlan((pParse, 0, "CREATE BLOOM FILTER"));
        }
        sqlite3ReleaseTempReg(pParse, r1);
      }
      break;
    }


    /* If any row exist in the result set, record that fact and abort.

      int r1;
      testcase( pIn->nSdst>1 );
      r1 = sqlite3GetTempReg(pParse);
      sqlite3VdbeAddOp4(v, OP_MakeRecord, pIn->iSdst, pIn->nSdst,
          r1, pDest->zAffSdst, pIn->nSdst);
      sqlite3VdbeAddOp4Int(v, OP_IdxInsert, pDest->iSDParm, r1,
                           pIn->iSdst, pIn->nSdst);
      if( pDest->iSDParm2>0 ){
        sqlite3VdbeAddOp4Int(v, OP_FilterAdd, pDest->iSDParm2, 0,
                             pIn->iSdst, pIn->nSdst);
        ExplainQueryPlan((pParse, 0, "CREATE BLOOM FILTER"));
      }
      sqlite3ReleaseTempReg(pParse, r1);
      break;
    }

    /* If this is a scalar select that is part of an expression, then
    ** store the results in the appropriate memory cell and break out
    ** of the scan loop.  Note that the select might return multiple columns

**                     Store the first column of the first result row
**                     in register pDest->iSDParm then abandon the rest
**                     of the query.  This destination implies "LIMIT 1".
**
**     SRT_Set         The result must be a single column.  Store each
**                     row of result as the key in table pDest->iSDParm.
**                     Apply the affinity pDest->affSdst before storing
**                     results.  if pDest->iSDParm2 is positive, then it is
**                     a regsiter holding a Bloom filter for the IN operator
**                     that should be populated in addition to the 
**                     pDest->iSDParm table.  This SRT is used to
**                     implement "IN (SELECT ...)".
**
**     SRT_EphemTab    Create an temporary table pDest->iSDParm and store
**                     the result there. The cursor is left open after
**                     returning.  This is like SRT_Table except that
**                     this destination uses OP_OpenEphemeral to create
**                     the table first.
**

  assert( pC->isTable==0 );
  r.nField = (u16)pOp->p4.i;
  if( r.nField>0 ){
    /* Key values in an array of registers */
    r.pKeyInfo = pC->pKeyInfo;
    r.default_rc = 0;
#ifdef SQLITE_DEBUG
    (void)sqlite3FaultSim(50);  /* For use by --counter in TH3 */
    for(ii=0; ii<r.nField; ii++){
      assert( memIsValid(&r.aMem[ii]) );
      assert( (r.aMem[ii].flags & MEM_Zero)==0 || r.aMem[ii].n==0 );
      if( ii ) REGISTER_TRACE(pOp->p3+ii, &r.aMem[ii]);
    }
#endif
    rc = sqlite3BtreeIndexMoveto(pC->uc.pCursor, &r, &pC->seekResult);

do_eqp_test autoindex1-500.1 {
  SELECT b FROM t501
   WHERE t501.a IN (SELECT x FROM t502 WHERE y=?);
} {
  QUERY PLAN
  |--SEARCH t501 USING INTEGER PRIMARY KEY (rowid=?)
  `--LIST SUBQUERY xxxxxx
     |--SCAN t502
     `--CREATE BLOOM FILTER
}
do_eqp_test autoindex1-501 {
  SELECT b FROM t501
   WHERE t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
} {
  QUERY PLAN
  |--SCAN t501

det 3.3.1 {
  SELECT * FROM t1 WHERE y IN (SELECT y FROM t2)
} {
  QUERY PLAN
  |--SCAN t1
  `--LIST SUBQUERY xxxxxx
     |--SCAN t2
     `--CREATE BLOOM FILTER
}
det 3.3.2 {
  SELECT * FROM t1 WHERE y IN (SELECT y FROM t2 WHERE t1.x!=t2.x)
} {
  QUERY PLAN
  |--SCAN t1
  `--CORRELATED LIST SUBQUERY xxxxxx

  |--CO-ROUTINE t0
  |  `--COMPOUND QUERY
  |     |--LEFT-MOST SUBQUERY
  |     |  |--SEARCH t01 USING INDEX t01x (w=? AND x=? AND y>? AND y<?)
  |     |  `--LIST SUBQUERY xxxxxx
  |     |     |--MATERIALIZE k
  |     |     |  `--SCAN 3 CONSTANT ROWS
  |     |     |--SCAN k
  |     |     `--CREATE BLOOM FILTER
  |     `--UNION ALL
  |        |--SEARCH t02 USING INDEX t02x (w=? AND x=? AND y>? AND y<?)
  |        `--LIST SUBQUERY xxxxxx
  |           |--SCAN k
  |           `--CREATE BLOOM FILTER
  |--SEARCH t0
  `--LIST SUBQUERY xxxxxx
     |--SCAN k
     `--CREATE BLOOM FILTER
}
# ^^^^--- The key feature above is that the SEARCH for each subquery
# uses all three fields of the index w, x, and y.  Prior to the push-down
# of "expr IN table", only the w term of the index would be used.  Similar
# for the following tests:
#
do_eqp_test 6.2 {
  SELECT max(z) FROM t0 WHERE w=123 AND x IN v1 AND y BETWEEN 44 AND 55;
} {
  QUERY PLAN
  |--CO-ROUTINE t0
  |  `--COMPOUND QUERY
  |     |--LEFT-MOST SUBQUERY
  |     |  |--SEARCH t01 USING INDEX t01x (w=? AND x=? AND y>? AND y<?)
  |     |  `--LIST SUBQUERY xxxxxx
  |     |     |--CO-ROUTINE v1
  |     |     |  `--SCAN 3 CONSTANT ROWS
  |     |     |--SCAN v1
  |     |     `--CREATE BLOOM FILTER
  |     `--UNION ALL
  |        |--SEARCH t02 USING INDEX t02x (w=? AND x=? AND y>? AND y<?)
  |        `--LIST SUBQUERY xxxxxx
  |           |--CO-ROUTINE v1
  |           |  `--SCAN 3 CONSTANT ROWS
  |           |--SCAN v1
  |           `--CREATE BLOOM FILTER
  |--SEARCH t0
  `--LIST SUBQUERY xxxxxx
     |--CO-ROUTINE v1
     |  `--SCAN 3 CONSTANT ROWS
     |--SCAN v1
     `--CREATE BLOOM FILTER
}
do_eqp_test 6.3 {
  SELECT max(z) FROM t0 WHERE w=123 AND x IN k1 AND y BETWEEN 44 AND 55;
} {
  QUERY PLAN
  |--CO-ROUTINE t0
  |  `--COMPOUND QUERY
  |     |--LEFT-MOST SUBQUERY
  |     |  |--SEARCH t01 USING INDEX t01x (w=? AND x=? AND y>? AND y<?)
  |     |  `--LIST SUBQUERY xxxxxx
  |     |     |--SCAN k1
  |     |     `--CREATE BLOOM FILTER
  |     `--UNION ALL
  |        |--SEARCH t02 USING INDEX t02x (w=? AND x=? AND y>? AND y<?)
  |        `--LIST SUBQUERY xxxxxx
  |           |--SCAN k1
  |           `--CREATE BLOOM FILTER
  |--SEARCH t0
  `--LIST SUBQUERY xxxxxx
     |--SCAN k1
     `--CREATE BLOOM FILTER
}

finish_test

  SELECT * FROM d2 WHERE 
    (a, b) IN (SELECT x, y FROM d1) AND
    (c) IN (SELECT y FROM d1)
} {
  QUERY PLAN
  |--SEARCH d2 USING INDEX d2ab (a=? AND b=?)
  |--LIST SUBQUERY xxxxxx
  |  |--SCAN d1
  |  `--CREATE BLOOM FILTER
  `--LIST SUBQUERY xxxxxx
     |--SCAN d1
     `--CREATE BLOOM FILTER
}

do_execsql_test 6.0 {
  CREATE TABLE e1(a, b, c, d, e);
  CREATE INDEX e1ab ON e1(a, b);
  CREATE INDEX e1cde ON e1(c, d, e);
}