SQLite

      i = -1;
    }else{
      /* Generate a subroutine that will fill an ephemeral table with
      ** the content of this subquery.  pItem->addrFillSub will point
      ** to the address of the generated subroutine.  pItem->regReturn
      ** is a register allocated to hold the subroutine return address
      */
      int topAddr;
      int onceAddr = 0;
      int retAddr;
      assert( pItem->addrFillSub==0 );
      pItem->regReturn = ++pParse->nMem;
      topAddr = sqlite3VdbeAddOp2(v, OP_Integer, 0, pItem->regReturn);
      pItem->addrFillSub = topAddr+1;
      VdbeNoopComment((v, "materialize %s", pItem->pTab->zName));
      if( pItem->isCorrelated==0 && pParse->pTriggerTab==0 ){
        /* If the subquery is no correlated and if we are not inside of
        ** a trigger, then we only need to compute the value of the subquery
        ** once. */
        int regOnce = ++pParse->nMem;
        onceAddr = sqlite3VdbeAddOp1(v, OP_Once, regOnce);
      }
      sqlite3SelectDestInit(&dest, SRT_EphemTab, pItem->iCursor);
      explainSetInteger(pItem->iSelectId, (u8)pParse->iNextSelectId);
      sqlite3Select(pParse, pSub, &dest);
      pItem->pTab->nRowEst = (unsigned)pSub->nSelectRow;
      if( onceAddr ) sqlite3VdbeJumpHere(v, onceAddr);
      retAddr = sqlite3VdbeAddOp1(v, OP_Return, pItem->regReturn);
      VdbeComment((v, "end %s", pItem->pTab->zName));
      sqlite3VdbeChangeP1(v, topAddr, retAddr);

    }
    if( /*pParse->nErr ||*/ db->mallocFailed ){
      goto select_end;
    }
    pParse->nHeight -= sqlite3SelectExprHeight(p);
    pTabList = p->pSrc;
    if( !IgnorableOrderby(pDest) ){

     WHERE (a=1 AND h=4)
         OR (b=2 AND b NOT IN (
               SELECT x+1 FROM (SELECT e+f AS x, e FROM t2 ORDER BY 1 LIMIT 2)
               GROUP BY e
            ));
  }    
} {4 1 2 3 4 {}}
do_test tkt-31338-3.3 {
  db eval {    
    SELECT * FROM t3 LEFT JOIN t1 ON d=g LEFT JOIN t4 ON c=h
     WHERE (+a=1 AND h=4)
         OR (b IN (
               SELECT x FROM (SELECT e+f AS x, e FROM t2 ORDER BY 1 LIMIT 2)
               GROUP BY e
            ));
  }    
} {4 1 2 3 4 {}}
do_test tkt-31338-3.4 {
  db eval {    
    SELECT * FROM t3 LEFT JOIN t1 ON d=g LEFT JOIN t4 ON c=h
     WHERE (a=1 AND h=4)
         OR (+b IN (
               SELECT x FROM (SELECT e+f AS x, e FROM t2 ORDER BY 1 LIMIT 2)
               GROUP BY e
            ));
  }    
} {4 1 2 3 4 {}}

do_test tkt-31338-3.5 {
  db eval {
    CREATE TABLE t5(a,b,c,d,e,f);
    CREATE TABLE t6(g,h);
    CREATE TRIGGER t6r AFTER INSERT ON t6 BEGIN
      INSERT INTO t5    
        SELECT * FROM t3 LEFT JOIN t1 ON d=g LEFT JOIN t4 ON c=h
         WHERE (a=1 AND h=4)
            OR (b IN (
               SELECT x FROM (SELECT e+f AS x, e FROM t2 ORDER BY 1 LIMIT 2)
               GROUP BY e
            ));
    END;
    INSERT INTO t6 VALUES(88,99);
    SELECT * FROM t5;
  }    
} {4 1 2 3 4 {}}

do_test tkt-31338-3.6 {
  db eval {    
    INSERT INTO t1 VALUES(2,4,3,4);
    INSERT INTO t1 VALUES(99,101,3,4);
    INSERT INTO t1 VALUES(98,97,3,4);
    SELECT * FROM t3 LEFT JOIN t1 ON d=g LEFT JOIN t4 ON c=h
     WHERE (a=1 AND h=4)
         OR (b IN (
               SELECT x+a FROM (SELECT e+f AS x, e FROM t2 ORDER BY 1 LIMIT 2)
               GROUP BY e
            ));
  }    
} {4 2 4 3 4 {} 4 99 101 3 4 {}}

finish_test