SQLite

  }else
#endif
  {
    sqlite3ErrorMsg(pParse, "row value misused");
  }
}

#ifndef SQLITE_OMIT_SUBQUERY
/*
** Scan all previously generated bytecode looking for an OP_BeginSubrtn
** that is compatible with pExpr.  If found, add the y.sub values
** to pExpr and return true.  If not found, return false.
*/
static int findCompatibleInRhsSubrtn(
  Parse *pParse,          /* Parsing context */
  Expr *pExpr,            /* IN operator with RHS that we want to reuse */
  SubrtnSig *pNewSig      /* Signature for the IN operator */
){
  VdbeOp *pOp, *pEnd;
  SubrtnSig *pSig;
  Vdbe *v;

  if( pNewSig==0 ) return 0;
  if( pParse->bHasSubrtn==0 ) return 0;
  assert( pExpr->op==TK_IN );
  assert( !ExprUseYSub(pExpr) );
  assert( ExprUseXSelect(pExpr) );
  assert( pExpr->x.pSelect!=0 );
  assert( (pExpr->x.pSelect->selFlags & SF_All)==0 );
  v = pParse->pVdbe;
  assert( v!=0 );
  pOp = sqlite3VdbeGetOp(v, 1);
  pEnd = sqlite3VdbeGetLastOp(v);
  for(; pOp<pEnd; pOp++){
    if( pOp->p4type!=P4_SUBRTNSIG ) continue;
    assert( pOp->opcode==OP_BeginSubrtn );
    pSig = pOp->p4.pSubrtnSig;
    assert( pSig!=0 );
    if( pNewSig->selId!=pSig->selId ) continue;
    if( strcmp(pNewSig->zAff,pSig->zAff)!=0 ) continue;
    pExpr->y.sub.iAddr = pSig->iAddr;
    pExpr->y.sub.regReturn = pSig->regReturn;
    pExpr->iTable = pSig->iTable;
    ExprSetProperty(pExpr, EP_Subrtn);
    return 1;
  }
  return 0;
}
#endif /* SQLITE_OMIT_SUBQUERY */

#ifndef SQLITE_OMIT_SUBQUERY
/*
** Generate code that will construct an ephemeral table containing all terms
** in the RHS of an IN operator.  The IN operator can be in either of two
** forms:
**
**     x IN (4,5,11)              -- IN operator with list on right-hand side

  **    *  The right-hand side is an expression list containing variables
  **    *  We are inside a trigger
  **
  ** If all of the above are false, then we can compute the RHS just once
  ** and reuse it many names.
  */
  if( !ExprHasProperty(pExpr, EP_VarSelect) && pParse->iSelfTab==0 ){
    /* Reuse of the RHS is allowed
    **
    ** Compute a signature for the RHS of the IN operator to facility
    ** finding and reusing prior instances of the same IN operator.
    */
    SubrtnSig *pSig = 0;
    assert( !ExprUseXSelect(pExpr) || pExpr->x.pSelect!=0 );
    if( ExprUseXSelect(pExpr) && (pExpr->x.pSelect->selFlags & SF_All)==0 ){
      pSig = sqlite3DbMallocRawNN(pParse->db, sizeof(pSig[0]));
      if( pSig ){
        pSig->selId = pExpr->x.pSelect->selId;
        pSig->zAff = exprINAffinity(pParse, pExpr);
      }
    }

    /* Check to see if there is a prior materialization of the RHS of
    ** this IN operator.  If there is, then make use of that prior
    ** materialization rather than recomputing it.
    */
    if( ExprHasProperty(pExpr, EP_Subrtn) 
     || findCompatibleInRhsSubrtn(pParse, pExpr, pSig)
    ){
      addrOnce = sqlite3VdbeAddOp0(v, OP_Once); VdbeCoverage(v);
      if( ExprUseXSelect(pExpr) ){
        ExplainQueryPlan((pParse, 0, "REUSE LIST SUBQUERY %d",
              pExpr->x.pSelect->selId));
      }
      assert( ExprUseYSub(pExpr) );
      sqlite3VdbeAddOp2(v, OP_Gosub, pExpr->y.sub.regReturn,
                        pExpr->y.sub.iAddr);
      assert( iTab!=pExpr->iTable );
      sqlite3VdbeAddOp2(v, OP_OpenDup, iTab, pExpr->iTable);
      sqlite3VdbeJumpHere(v, addrOnce);
      if( pSig ){
        sqlite3DbFree(pParse->db, pSig->zAff);
        sqlite3DbFree(pParse->db, pSig);
      }
      return;
    }

    /* Begin coding the subroutine */
    assert( !ExprUseYWin(pExpr) );
    ExprSetProperty(pExpr, EP_Subrtn);
    assert( !ExprHasProperty(pExpr, EP_TokenOnly|EP_Reduced) );
    pExpr->y.sub.regReturn = ++pParse->nMem;
    pExpr->y.sub.iAddr =
      sqlite3VdbeAddOp2(v, OP_BeginSubrtn, 0, pExpr->y.sub.regReturn) + 1;
    if( pSig ){
      pSig->iAddr = pExpr->y.sub.iAddr;
      pSig->regReturn = pExpr->y.sub.regReturn;
      pSig->iTable = iTab;
      sqlite3VdbeChangeP4(v, -1, (const char*)pSig, P4_SUBRTNSIG);
      pParse->bHasSubrtn = 1;
    }
    addrOnce = sqlite3VdbeAddOp0(v, OP_Once); VdbeCoverage(v);
  }

  /* Check to see if this is a vector IN operator */
  pLeft = pExpr->pLeft;
  nVal = sqlite3ExprVectorSize(pLeft);

  u8 mayAbort;         /* True if statement may throw an ABORT exception */
  u8 hasCompound;      /* Need to invoke convertCompoundSelectToSubquery() */
  u8 okConstFactor;    /* OK to factor out constants */
  u8 disableLookaside; /* Number of times lookaside has been disabled */
  u8 prepFlags;        /* SQLITE_PREPARE_* flags */
  u8 withinRJSubrtn;   /* Nesting level for RIGHT JOIN body subroutines */
  u8 bHasWith;         /* True if statement contains WITH */
  u8 bHasSubrtn;       /* True if any P4_SUBRTNSIG has been set */
#if defined(SQLITE_DEBUG) || defined(SQLITE_COVERAGE_TEST)
  u8 earlyCleanup;     /* OOM inside sqlite3ParserAddCleanup() */
#endif
#ifdef SQLITE_DEBUG
  u8 ifNotExists;      /* Might be true if IF NOT EXISTS.  Assert()s only */
#endif
  int nRangeReg;       /* Size of the temporary register block */

/*
** The names of the following types declared in vdbeInt.h are required
** for the VdbeOp definition.
*/
typedef struct sqlite3_value Mem;
typedef struct SubProgram SubProgram;
typedef struct SubrtnSig SubrtnSig;

/*
** A signature for a reusable subroutine that materializes the RHS of
** an IN operator.
*/
struct SubrtnSig {
  int selId;          /* SELECT-id for the SELECT statement on the RHS */
  char *zAff;         /* Affinity of the overall IN expression */
  int iTable;         /* Ephemeral table generated by the subroutine */
  int iAddr;          /* Subroutine entry address */
  int regReturn;      /* Register used to hold return address */
};

/*
** A single instruction of the virtual machine has an opcode
** and as many as three operands.  The instruction is recorded
** as an instance of the following structure:
*/
struct VdbeOp {

    CollSeq *pColl;        /* Used when p4type is P4_COLLSEQ */
    Mem *pMem;             /* Used when p4type is P4_MEM */
    VTable *pVtab;         /* Used when p4type is P4_VTAB */
    KeyInfo *pKeyInfo;     /* Used when p4type is P4_KEYINFO */
    u32 *ai;               /* Used when p4type is P4_INTARRAY */
    SubProgram *pProgram;  /* Used when p4type is P4_SUBPROGRAM */
    Table *pTab;           /* Used when p4type is P4_TABLE */
    SubrtnSig *pSubrtnSig; /* Used when p4type is P4_SUBRTNSIG */
#ifdef SQLITE_ENABLE_CURSOR_HINTS
    Expr *pExpr;           /* Used when p4type is P4_EXPR */
#endif
  } p4;
#ifdef SQLITE_ENABLE_EXPLAIN_COMMENTS
  char *zComment;          /* Comment to improve readability */
#endif

#define P4_MEM        (-10) /* P4 is a pointer to a Mem*    structure */
#define P4_VTAB       (-11) /* P4 is a pointer to an sqlite3_vtab structure */
#define P4_REAL       (-12) /* P4 is a 64-bit floating point value */
#define P4_INT64      (-13) /* P4 is a 64-bit signed integer */
#define P4_INTARRAY   (-14) /* P4 is a vector of 32-bit integers */
#define P4_FUNCCTX    (-15) /* P4 is a pointer to an sqlite3_context object */
#define P4_TABLEREF   (-16) /* Like P4_TABLE, but reference counted */
#define P4_SUBRTNSIG  (-17) /* P4 is a SubrtnSig pointer */

/* Error message codes for OP_Halt */
#define P5_ConstraintNotNull 1
#define P5_ConstraintUnique  2
#define P5_ConstraintCheck   3
#define P5_ConstraintFK      4

      if( db->pnBytesFreed==0 ) sqlite3VtabUnlock((VTable *)p4);
      break;
    }
    case P4_TABLEREF: {
      if( db->pnBytesFreed==0 ) sqlite3DeleteTable(db, (Table*)p4);
      break;
    }
    case P4_SUBRTNSIG: {
      SubrtnSig *pSig = (SubrtnSig*)p4;
      sqlite3DbFree(db, pSig->zAff);
      sqlite3DbFree(db, pSig);
      break;
    }
  }
}

/*
** Free the space allocated for aOp and any p4 values allocated for the
** opcodes contained within. If aOp is not NULL it is assumed to contain
** nOp entries.

    case P4_SUBPROGRAM: {
      zP4 = "program";
      break;
    }
    case P4_TABLE: {
      zP4 = pOp->p4.pTab->zName;
      break;
    }
    case P4_SUBRTNSIG: {
      SubrtnSig *pSig = pOp->p4.pSubrtnSig;
      sqlite3_str_appendf(&x, "subrtnsig:%d,%s", pSig->selId, pSig->zAff);
      break;
    }
    default: {
      zP4 = pOp->p4.z;
    }
  }
  if( zP4 ) sqlite3_str_appendall(&x, zP4);
  if( (x.accError & SQLITE_NOMEM)!=0 ){

  INSERT INTO t1 VALUES('2', NULL);
  INSERT INTO t1 VALUES('3', 'three');
}

do_execsql_test 2.1 {
  SELECT b FROM t1 WHERE a IN (1,2,3) ORDER BY b ASC NULLS LAST;
} {one three {}}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 3.0 {
  CREATE TABLE x1(a);
  INSERT INTO x1 VALUES(1), (2), (3);

  CREATE TABLE x2(b);
  INSERT INTO x2 VALUES(4), (5), (6);

  CREATE TABLE t1(u);
  INSERT INTO t1 VALUES(1), (2), (3), (4), (5), (6);

  CREATE VIEW v1 AS SELECT u FROM t1 WHERE u IN (
    SELECT a FROM x1
  );
  CREATE VIEW v2 AS SELECT u FROM t1 WHERE u IN (
    SELECT b FROM x2
  );
}

do_execsql_test 3.1 {
  SELECT * FROM v1
} {
  1 2 3
}

do_execsql_test 3.2 {
  SELECT * FROM v2
} {
  4 5 6 
}

do_execsql_test 3.3 {
  SELECT * FROM v2
  UNION ALL
  SELECT * FROM v1
} {
  4 5 6
  1 2 3 
}

do_execsql_test 3.4 {
  WITH w1 AS (
    SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
  ),
  w2 AS (
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
  )
  SELECT * FROM v1 WHERE u IN w1
  UNION ALL
  SELECT * FROM v2 WHERE u IN w2
} {
  1 2 3 4 5 6
}



finish_test

  |     |  `--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<?)
  |        `--REUSE LIST SUBQUERY xxxxxx


  |--SEARCH t0
  `--REUSE LIST SUBQUERY xxxxxx


}
# ^^^^--- 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 {

  |     |  `--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<?)
  |        `--REUSE LIST SUBQUERY xxxxxx




  |--SEARCH t0
  `--REUSE LIST SUBQUERY xxxxxx




}
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<?)
  |        `--REUSE LIST SUBQUERY xxxxxx


  |--SEARCH t0
  `--REUSE LIST SUBQUERY xxxxxx


}

finish_test