SQLite

    for(i=iEq;i<pLoop->nLTerm; i++){
      if( ALWAYS(pLoop->aLTerm[i]) && pLoop->aLTerm[i]->pExpr==pX ) nEq++;
    }

    if( (pX->flags & EP_xIsSelect)==0 || pX->x.pSelect->pEList->nExpr==1 ){
      eType = sqlite3FindInIndex(pParse, pX, IN_INDEX_LOOP, 0, 0);
    }else{
      Select *pSelect = pX->x.pSelect;
      sqlite3 *db = pParse->db;
      ExprList *pOrigRhs = pSelect->pEList;
      ExprList *pOrigLhs = pX->pLeft->x.pList;
      ExprList *pRhs = 0;         /* New Select.pEList for RHS */
      ExprList *pLhs = 0;         /* New pX->pLeft vector */

      for(i=iEq;i<pLoop->nLTerm; i++){
        if( pLoop->aLTerm[i]->pExpr==pX ){
          int iField = pLoop->aLTerm[i]->iField - 1;
          Expr *pNewRhs = sqlite3ExprDup(db, pOrigRhs->a[iField].pExpr, 0);
          Expr *pNewLhs = sqlite3ExprDup(db, pOrigLhs->a[iField].pExpr, 0);

          pRhs = sqlite3ExprListAppend(pParse, pRhs, pNewRhs);
          pLhs = sqlite3ExprListAppend(pParse, pLhs, pNewLhs);
        }
      }
      if( !db->mallocFailed ){
        Expr *pLeft = pX->pLeft;

        if( pSelect->pOrderBy ){
          /* If the SELECT statement has an ORDER BY clause, zero the 
          ** iOrderByCol variables. These are set to non-zero when an 
          ** ORDER BY term exactly matches one of the terms of the 
          ** result-set. Since the result-set of the SELECT statement may
          ** have been modified or reordered, these variables are no longer 
          ** set correctly.  Since setting them is just an optimization, 
          ** it's easiest just to zero them here.  */
          ExprList *pOrderBy = pSelect->pOrderBy;
          for(i=0; i<pOrderBy->nExpr; i++){
            pOrderBy->a[i].u.x.iOrderByCol = 0;
          }
        }

        /* Take care here not to generate a TK_VECTOR containing only a
        ** single value. Since the parser never creates such a vector, some
        ** of the subroutines do not handle this case.  */
        if( pLhs->nExpr==1 ){
          pX->pLeft = pLhs->a[0].pExpr;
        }else{
          pLeft->x.pList = pLhs;
          aiMap = (int*)sqlite3DbMallocZero(pParse->db, sizeof(int) * nEq);
          testcase( aiMap==0 );
        }
        pSelect->pEList = pRhs;
        eType = sqlite3FindInIndex(pParse, pX, IN_INDEX_LOOP, 0, aiMap);
        testcase( aiMap!=0 && aiMap[0]!=0 );
        pSelect->pEList = pOrigRhs;
        pLeft->x.pList = pOrigLhs;
        pX->pLeft = pLeft;
      }
      sqlite3ExprListDelete(pParse->db, pLhs);
      sqlite3ExprListDelete(pParse->db, pRhs);
    }

} {1}
do_execsql_test 5.2 {
  SELECT rowid FROM e2 WHERE rowid IN (SELECT +c FROM e1);
} {2}
do_execsql_test 5.3 {
  SELECT rowid FROM e2 WHERE rowid IN (SELECT 0+c FROM e1);
} {2}

#-------------------------------------------------------------------------
#
do_execsql_test 6.0 {
  CREATE TABLE f1(a, b);
  CREATE TABLE f2(c, d);
  CREATE TABLE f3(e, f);
}

do_execsql_test 6.1 {
  SELECT * FROM f3 WHERE (e, f) IN (
    SELECT a, b FROM f1 UNION ALL SELECT c, d FROM f2
  );
}
do_execsql_test 6.2 {
  CREATE INDEX f3e ON f3(e);
  SELECT * FROM f3 WHERE (e, f) IN (
    SELECT a, b FROM f1 UNION ALL SELECT c, d FROM f2
  );
}


#-------------------------------------------------------------------------
#
do_execsql_test 7.0 {
  CREATE TABLE g1(a, b); 
  INSERT INTO g1 VALUES
      (1, 1), (1, 2), (1, 3), (1, 'i'), (1, 'j'),
      (1, 6), (1, 7), (1, 8), (1, 9), (1, 10),
      (1, 4), (1, 5);

  CREATE TABLE g2(x, y);
  CREATE INDEX g2x ON g2(x);

  INSERT INTO g2 VALUES(1, 4);
  INSERT INTO g2 VALUES(1, 5);
}

do_execsql_test 7.1 {
  SELECT * FROM g2 WHERE (x, y) IN (
    SELECT a, b FROM g1 ORDER BY +a, +b LIMIT 10
  );
} { 1 4 1 5 }

do_execsql_test 7.2 {
  SELECT * FROM g2 WHERE (x, y) IN (
    SELECT a, b FROM g1 ORDER BY a, b LIMIT 10
  );
} { 1 4 1 5 }

do_execsql_test 7.3 {
  SELECT * FROM g2 WHERE (x, y) IN (
    SELECT a, b FROM g1 ORDER BY 1, 2 LIMIT 10
  );
} { 1 4 1 5 }


finish_test