SQLite

      **      expr1 NOT IN ()
      **
      ** simplify to constants 0 (false) and 1 (true), respectively,
      ** regardless of the value of expr1.
      */
      A.pExpr = sqlite3PExpr(pParse, TK_INTEGER, 0, 0, &sqlite3IntTokens[N]);
      sqlite3ExprDelete(pParse->db, X.pExpr);
    }else if( Y->nExpr==1 ){
      /* Expressions of the form:
      **
      **      expr1 IN (?1)
      **      expr1 NOT IN (?2)
      **
      ** with exactly one value on the RHS can be simplified to:
      **
      **      expr1 == ?1
      **      expr1 <> ?2
      */
      Expr *pRHS = Y->a[0].pExpr;
      Y->a[0].pExpr = 0;
      sqlite3ExprListDelete(pParse->db, Y);
      A.pExpr = sqlite3PExpr(pParse, N ? TK_NE : TK_EQ, X.pExpr, pRHS, 0);
    }else{
      A.pExpr = sqlite3PExpr(pParse, TK_IN, X.pExpr, 0, 0);
      if( A.pExpr ){
        A.pExpr->x.pList = Y;
        sqlite3ExprSetHeight(pParse, A.pExpr);
      }else{
        sqlite3ExprListDelete(pParse->db, Y);

      if( ExprHasProperty(pExpr, EP_xIsSelect) ){
        /* "x IN (SELECT ...)":  TUNING: the SELECT returns 25 rows */
        nIn = 46;  assert( 46==sqlite3LogEst(25) );
      }else if( ALWAYS(pExpr->x.pList && pExpr->x.pList->nExpr) ){
        /* "x IN (value, value, ...)" */
        nIn = sqlite3LogEst(pExpr->x.pList->nExpr);
      }
      assert( nIn>0 );  /* RHS always has 2 or more terms...  The parser
                        ** changes "x IN (?)" into "x=?". */
      pNew->rRun += nIn;
      pNew->u.btree.nEq++;
      pNew->nOut = nRowEst + nInMul + nIn;
    }else if( pTerm->eOperator & (WO_EQ) ){
      assert(
        (pNew->wsFlags & (WHERE_COLUMN_NULL|WHERE_COLUMN_IN|WHERE_SKIPSCAN))!=0
        || nInMul==0

} {}
do_test in4-3.11 {
  execsql { SELECT * FROM t3 WHERE x IN (1, 2) OR y IN ()}
} {1 1 1}
do_test in4-3.12 {
  execsql { SELECT * FROM t3 WHERE x IN (1, 2) AND y IN ()}
} {}

# Tests for "... IN (?)" and "... NOT IN (?)".  In other words, tests
# for when the RHS of IN is a single expression.  This should work the
# same as the == and <> operators.
#
do_execsql_test in4-3.21 {
  SELECT * FROM t3 WHERE x=10 AND y IN (10);
} {10 10 10}
do_execsql_test in4-3.22 {
  SELECT * FROM t3 WHERE x IN (10) AND y=10;
} {10 10 10}
do_execsql_test in4-3.23 {
  SELECT * FROM t3 WHERE x IN (10) AND y IN (10);
} {10 10 10}
do_execsql_test in4-3.24 {
  SELECT * FROM t3 WHERE x=1 AND y NOT IN (10);
} {1 1 1}
do_execsql_test in4-3.25 {
  SELECT * FROM t3 WHERE x  NOT IN (10) AND y=1;
} {1 1 1}
do_execsql_test in4-3.26 {
  SELECT * FROM t3 WHERE x NOT IN (10) AND y NOT IN (10);
} {1 1 1}

# The query planner recognizes that "x IN (?)" only generates a
# single match and can use this information to optimize-out ORDER BY
# clauses.
#
do_execsql_test in4-3.31 {
  DROP INDEX t3i1;
  CREATE UNIQUE INDEX t3xy ON t3(x,y);

  SELECT *, '|' FROM t3 A, t3 B
   WHERE A.x=10 AND A.y IN (10)
     AND B.x=1 AND B.y IN (1);
} {10 10 10 1 1 1 |}
do_execsql_test in4-3.32 {
  EXPLAIN QUERY PLAN
  SELECT *, '|' FROM t3 A, t3 B
   WHERE A.x=10 AND A.y IN (10)
     AND B.x=1 AND B.y IN (1);
} {~/B-TREE/}  ;# No separate sorting pass
do_execsql_test in4-3.33 {
  SELECT *, '|' FROM t3 A, t3 B
   WHERE A.x IN (10) AND A.y=10
     AND B.x IN (1) AND B.y=1;
} {10 10 10 1 1 1 |}
do_execsql_test in4-3.34 {
  EXPLAIN QUERY PLAN
  SELECT *, '|' FROM t3 A, t3 B
   WHERE A.x IN (10) AND A.y=10
     AND B.x IN (1) AND B.y=1;
} {~/B-TREE/}  ;# No separate sorting pass

# An expression of the form "x IN (?,?)" creates an ephemeral table to
# hold the list of values on the RHS.  But "x IN (?)" does not create
# an ephemeral table.
#
do_execsql_test in4-3.41 {
  SELECT * FROM t3 WHERE x IN (10,11);
} {10 10 10}
do_execsql_test in4-3.42 {
  EXPLAIN
  SELECT * FROM t3 WHERE x IN (10,11);
} {/OpenEphemeral/}
do_execsql_test in4-3.43 {
  SELECT * FROM t3 WHERE x IN (10);
} {10 10 10}
do_execsql_test in4-3.44 {
  EXPLAIN
  SELECT * FROM t3 WHERE x IN (10);
} {~/OpenEphemeral/}
do_execsql_test in4-3.45 {
  SELECT * FROM t3 WHERE x NOT IN (10,11);
} {1 1 1}
do_execsql_test in4-3.46 {
  EXPLAIN
  SELECT * FROM t3 WHERE x NOT IN (10,11);
} {/OpenEphemeral/}
do_execsql_test in4-3.47 {
  SELECT * FROM t3 WHERE x NOT IN (10);
} {1 1 1}
do_execsql_test in4-3.48 {
  EXPLAIN
  SELECT * FROM t3 WHERE x NOT IN (10);
} {~/OpenEphemeral/}



finish_test