**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.204 2004/08/29 01:31:05 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
  if( keyAsData ){
    sqlite3VdbeAddOp(v, OP_KeyAsData, iTab, 1);
  }
  return addr;
}

/*
** FIX ME:
**    +  Omit the ppOpenTemp parameter from multiSelectOpenTempAddr().
**    +  Attach pOpenList to the right-most term always.
**    +  Make sure Select.ppOpenTemp is initialized to NULL
*/

/*
** Add the address "addr" to the set of all opcode addresses contained
** in the pOpenTemp list for the whole compound select.  If no pOpenTemp
** list has been created yet, then create a new one and make *ppOpenTemp
** point to it.  If the pOpenTemp list already exists, leave *ppOpenTemp
** unchanged and just add the new address to the existing list.

*/
static int multiSelectOpenTempAddr(Select *p, int addr, IdList **ppOpenTemp){
  IdList *pList;
  if( !p->ppOpenTemp ){
    /* Create a new list */
    *ppOpenTemp = sqlite3IdListAppend(0, 0);
    p->ppOpenTemp = ppOpenTemp;
  }else{
    /* Add a new element onto the end of the existing list */
    *p->ppOpenTemp = sqlite3IdListAppend(*p->ppOpenTemp, 0);
  }
  pList = *p->ppOpenTemp;
  if( pList==0 ){
    return SQLITE_NOMEM;
  }
  pList->a[pList->nId-1].idx = addr;
  return SQLITE_OK;
}

................................................................................
  int iParm,            /* /     by these two parameters.         */
  char *aff             /* If eDest is SRT_Union, the affinity string */
){
  int rc = SQLITE_OK;   /* Success code from a subroutine */
  Select *pPrior;       /* Another SELECT immediately to our left */
  Vdbe *v;              /* Generate code to this VDBE */
  IdList *pOpenTemp = 0;/* OP_OpenTemp opcodes that need a KeyInfo */




  /* Make sure there is no ORDER BY or LIMIT clause on prior SELECTs.  Only
  ** the last (right-most) SELECT in the series may have an ORDER BY or LIMIT.
  */
  if( p==0 || p->pPrior==0 ){
    rc = 1;
    goto multi_select_end;
................................................................................
  */
  v = sqlite3GetVdbe(pParse);
  if( v==0 ){
    rc = 1;
    goto multi_select_end;
  }

  /* PART OF FIX:




  */
  if( p->ppOpenTemp==0 ){
    p->ppOpenTemp = &pOpenTemp;
  }
  pPrior->ppOpenTemp = p->ppOpenTemp;

  /* Create the destination temporary table if necessary
  */
  if( eDest==SRT_TempTable ){
    assert( p->pEList );
    sqlite3VdbeAddOp(v, OP_OpenTemp, iParm, 0);

    /* FIX ME:
    ** p->pEList->nExpr might contain a "*" and so might not be the 
    ** correct number.  Go ahead and code the SetNumColumns instruction
    ** here, but also record its address.   Change the P2 value of the
    ** instruction to the number of columns after sqlite3Select() has
    ** been called to code the subquery and has modified pEList->nExpr
    ** to be the correct value. */

    sqlite3VdbeAddOp(v, OP_SetNumColumns, iParm, p->pEList->nExpr);
    eDest = SRT_Table;
  }

  /* Generate code for the left and right SELECT statements.
  */
  switch( p->op ){
    case TK_ALL: {
      if( p->pOrderBy==0 ){
        pPrior->nLimit = p->nLimit;
        pPrior->nOffset = p->nOffset;
        /* pPrior->ppOpenTemp = p->ppOpenTemp; // FIX */
        rc = sqlite3Select(pParse, pPrior, eDest, iParm, 0, 0, 0, aff);
        if( rc ){
          goto multi_select_end;
        }
        p->pPrior = 0;
        p->iLimit = pPrior->iLimit;
        p->iOffset = pPrior->iOffset;
................................................................................
        if( p->pOrderBy 
        && matchOrderbyToColumn(pParse, p, p->pOrderBy, unionTab, 1) ){
          rc = 1;
          goto multi_select_end;
        }
        addr = sqlite3VdbeAddOp(v, OP_OpenTemp, unionTab, 0);
        if( p->op!=TK_ALL ){
          rc = multiSelectOpenTempAddr(p, addr, &pOpenTemp);
          if( rc!=SQLITE_OK ){
            goto multi_select_end;
          }
          sqlite3VdbeAddOp(v, OP_KeyAsData, unionTab, 1);
        }


        assert( p->pEList );
      }

      /* Code the SELECT statements to our left
      */
      /* pPrior->ppOpenTemp = p->ppOpenTemp; // FIX */
      rc = sqlite3Select(pParse, pPrior, priorOp, unionTab, 0, 0, 0, aff);
      if( rc ){
        goto multi_select_end;
      }
      if( p->op==TK_ALL ){
        sqlite3VdbeAddOp(v, OP_SetNumColumns, unionTab, pPrior->pEList->nExpr);
      }

      /* Code the current SELECT statement
      */
      switch( p->op ){
         case TK_EXCEPT:  op = SRT_Except;   break;
         case TK_UNION:   op = SRT_Union;    break;
         case TK_ALL:     op = SRT_Table;    break;
................................................................................
      tab2 = pParse->nTab++;
      if( p->pOrderBy && matchOrderbyToColumn(pParse,p,p->pOrderBy,tab1,1) ){
        rc = 1;
        goto multi_select_end;
      }

      addr = sqlite3VdbeAddOp(v, OP_OpenTemp, tab1, 0);
      rc = multiSelectOpenTempAddr(p, addr, &pOpenTemp);
      if( rc!=SQLITE_OK ){
        goto multi_select_end;
      }
      sqlite3VdbeAddOp(v, OP_KeyAsData, tab1, 1);


      assert( p->pEList );

      /* Code the SELECTs to our left into temporary table "tab1".
      */
      /* pPrior->ppOpenTemp = p->ppOpenTemp; // FIX */
      rc = sqlite3Select(pParse, pPrior, SRT_Union, tab1, 0, 0, 0, aff);
      if( rc ){
        goto multi_select_end;
      }

      /* Code the current SELECT into temporary table "tab2"
      */
      addr = sqlite3VdbeAddOp(v, OP_OpenTemp, tab2, 0);
      rc = multiSelectOpenTempAddr(p, addr, &pOpenTemp);
      if( rc!=SQLITE_OK ){
        goto multi_select_end;
      }
      sqlite3VdbeAddOp(v, OP_KeyAsData, tab2, 1);


      p->pPrior = 0;
      nLimit = p->nLimit;
      p->nLimit = -1;
      nOffset = p->nOffset;
      p->nOffset = 0;
      rc = sqlite3Select(pParse, p, SRT_Union, tab2, 0, 0, 0, aff);
      p->pPrior = pPrior;
................................................................................
      sqlite3VdbeAddOp(v, OP_Next, tab1, iStart);
      sqlite3VdbeResolveLabel(v, iBreak);
      sqlite3VdbeAddOp(v, OP_Close, tab2, 0);
      sqlite3VdbeAddOp(v, OP_Close, tab1, 0);
      break;
    }
  }




  assert( p->pEList && pPrior->pEList );
  if( p->pEList->nExpr!=pPrior->pEList->nExpr ){
    sqlite3ErrorMsg(pParse, "SELECTs to the left and right of %s"
      " do not have the same number of result columns", selectOpName(p->op));
    rc = 1;
    goto multi_select_end;
  }









  /* Compute collating sequences used by either the ORDER BY clause or
  ** by any temporary tables needed to implement the compound select.
  ** Attach the KeyInfo structure to all temporary tables.  Invoke the
  ** ORDER BY processing if there is an ORDER BY clause.





  */
  if( p->pOrderBy || (pOpenTemp && pOpenTemp->nId>0) ){
    int nCol = p->pEList->nExpr;  /* Number of columns in the result set */
    int i;                        /* Loop counter */
    KeyInfo *pKeyInfo;            /* Collating sequence for the result set */


    pKeyInfo = sqliteMalloc(sizeof(*pKeyInfo)+nCol*sizeof(CollSeq*));
    if( !pKeyInfo ){
      rc = SQLITE_NOMEM;
      goto multi_select_end;
    }

    pKeyInfo->enc = pParse->db->enc;
................................................................................
  /* Identify column names if we will be using them in a callback.  This
  ** step is skipped if the output is going to some other destination.
  */
  if( eDest==SRT_Callback ){
    generateColumnNames(pParse, pTabList, pEList);
  }

  /* If the destination is SRT_Union, then set the number of columns in
  ** the records that will be inserted into the temporary table. The caller
  ** couldn't do this, in case the select statement is of the form 
  ** "SELECT * FROM ....". 
  **
  ** We need to do this before we start inserting records into the 
  ** temporary table (which has had OP_KeyAsData executed on it), because
  ** it is required by the key comparison function. So do it now, even
  ** though this means that OP_SetNumColumns may be executed on the same
  ** cursor more than once.
  */
  if( eDest==SRT_Union ){
    sqlite3VdbeAddOp(v, OP_SetNumColumns, iParm, pEList->nExpr);
  }

  /* Generate code for all sub-queries in the FROM clause
  */
  for(i=0; i<pTabList->nSrc; i++){
    const char *zSavedAuthContext = 0;
    int needRestoreContext;

    if( pTabList->a[i].pSelect==0 ) continue;

**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.205 2004/08/29 16:25:04 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
  if( keyAsData ){
    sqlite3VdbeAddOp(v, OP_KeyAsData, iTab, 1);
  }
  return addr;
}

/*







** Add the address "addr" to the set of all OpenTemp opcode addresses




** that are being accumulated in p->ppOpenTemp.
*/
static int multiSelectOpenTempAddr(Select *p, int addr){







  IdList *pList = *p->ppOpenTemp = sqlite3IdListAppend(*p->ppOpenTemp, 0);


  if( pList==0 ){
    return SQLITE_NOMEM;
  }
  pList->a[pList->nId-1].idx = addr;
  return SQLITE_OK;
}

................................................................................
  int iParm,            /* /     by these two parameters.         */
  char *aff             /* If eDest is SRT_Union, the affinity string */
){
  int rc = SQLITE_OK;   /* Success code from a subroutine */
  Select *pPrior;       /* Another SELECT immediately to our left */
  Vdbe *v;              /* Generate code to this VDBE */
  IdList *pOpenTemp = 0;/* OP_OpenTemp opcodes that need a KeyInfo */
  int aAddr[5];         /* Addresses of SetNumColumns operators */
  int nAddr = 0;        /* Number used */
  int nCol;             /* Number of columns in the result set */

  /* Make sure there is no ORDER BY or LIMIT clause on prior SELECTs.  Only
  ** the last (right-most) SELECT in the series may have an ORDER BY or LIMIT.
  */
  if( p==0 || p->pPrior==0 ){
    rc = 1;
    goto multi_select_end;
................................................................................
  */
  v = sqlite3GetVdbe(pParse);
  if( v==0 ){
    rc = 1;
    goto multi_select_end;
  }

  /* If *p this is the right-most select statement, then initialize
  ** p->ppOpenTemp to point to pOpenTemp.  If *p is not the right most
  ** statement then p->ppOpenTemp will have already been initialized
  ** by a prior call to this same procedure.  Pass along the pOpenTemp
  ** pointer to pPrior, the next statement to our left.
  */
  if( p->ppOpenTemp==0 ){
    p->ppOpenTemp = &pOpenTemp;
  }
  pPrior->ppOpenTemp = p->ppOpenTemp;

  /* Create the destination temporary table if necessary
  */
  if( eDest==SRT_TempTable ){
    assert( p->pEList );
    sqlite3VdbeAddOp(v, OP_OpenTemp, iParm, 0);








    assert( nAddr==0 );
    aAddr[nAddr++] = sqlite3VdbeAddOp(v, OP_SetNumColumns, iParm, 0);
    eDest = SRT_Table;
  }

  /* Generate code for the left and right SELECT statements.
  */
  switch( p->op ){
    case TK_ALL: {
      if( p->pOrderBy==0 ){
        pPrior->nLimit = p->nLimit;
        pPrior->nOffset = p->nOffset;

        rc = sqlite3Select(pParse, pPrior, eDest, iParm, 0, 0, 0, aff);
        if( rc ){
          goto multi_select_end;
        }
        p->pPrior = 0;
        p->iLimit = pPrior->iLimit;
        p->iOffset = pPrior->iOffset;
................................................................................
        if( p->pOrderBy 
        && matchOrderbyToColumn(pParse, p, p->pOrderBy, unionTab, 1) ){
          rc = 1;
          goto multi_select_end;
        }
        addr = sqlite3VdbeAddOp(v, OP_OpenTemp, unionTab, 0);
        if( p->op!=TK_ALL ){
          rc = multiSelectOpenTempAddr(p, addr);
          if( rc!=SQLITE_OK ){
            goto multi_select_end;
          }
          sqlite3VdbeAddOp(v, OP_KeyAsData, unionTab, 1);
        }
	assert( nAddr<sizeof(aAddr)/sizeof(aAddr[0]) );
        aAddr[nAddr++] = sqlite3VdbeAddOp(v, OP_SetNumColumns, unionTab, 0);
        assert( p->pEList );
      }

      /* Code the SELECT statements to our left
      */

      rc = sqlite3Select(pParse, pPrior, priorOp, unionTab, 0, 0, 0, aff);
      if( rc ){
        goto multi_select_end;
      }




      /* Code the current SELECT statement
      */
      switch( p->op ){
         case TK_EXCEPT:  op = SRT_Except;   break;
         case TK_UNION:   op = SRT_Union;    break;
         case TK_ALL:     op = SRT_Table;    break;
................................................................................
      tab2 = pParse->nTab++;
      if( p->pOrderBy && matchOrderbyToColumn(pParse,p,p->pOrderBy,tab1,1) ){
        rc = 1;
        goto multi_select_end;
      }

      addr = sqlite3VdbeAddOp(v, OP_OpenTemp, tab1, 0);
      rc = multiSelectOpenTempAddr(p, addr);
      if( rc!=SQLITE_OK ){
        goto multi_select_end;
      }
      sqlite3VdbeAddOp(v, OP_KeyAsData, tab1, 1);
      assert( nAddr<sizeof(aAddr)/sizeof(aAddr[0]) );
      aAddr[nAddr++] = sqlite3VdbeAddOp(v, OP_SetNumColumns, tab1, 0);
      assert( p->pEList );

      /* Code the SELECTs to our left into temporary table "tab1".
      */

      rc = sqlite3Select(pParse, pPrior, SRT_Union, tab1, 0, 0, 0, aff);
      if( rc ){
        goto multi_select_end;
      }

      /* Code the current SELECT into temporary table "tab2"
      */
      addr = sqlite3VdbeAddOp(v, OP_OpenTemp, tab2, 0);
      rc = multiSelectOpenTempAddr(p, addr);
      if( rc!=SQLITE_OK ){
        goto multi_select_end;
      }
      sqlite3VdbeAddOp(v, OP_KeyAsData, tab2, 1);
      assert( nAddr<sizeof(aAddr)/sizeof(aAddr[0]) );
      aAddr[nAddr++] = sqlite3VdbeAddOp(v, OP_SetNumColumns, tab2, 0);
      p->pPrior = 0;
      nLimit = p->nLimit;
      p->nLimit = -1;
      nOffset = p->nOffset;
      p->nOffset = 0;
      rc = sqlite3Select(pParse, p, SRT_Union, tab2, 0, 0, 0, aff);
      p->pPrior = pPrior;
................................................................................
      sqlite3VdbeAddOp(v, OP_Next, tab1, iStart);
      sqlite3VdbeResolveLabel(v, iBreak);
      sqlite3VdbeAddOp(v, OP_Close, tab2, 0);
      sqlite3VdbeAddOp(v, OP_Close, tab1, 0);
      break;
    }
  }

  /* Make sure all SELECTs in the statement have the same number of elements
  ** in their result sets.
  */
  assert( p->pEList && pPrior->pEList );
  if( p->pEList->nExpr!=pPrior->pEList->nExpr ){
    sqlite3ErrorMsg(pParse, "SELECTs to the left and right of %s"
      " do not have the same number of result columns", selectOpName(p->op));
    rc = 1;
    goto multi_select_end;
  }

  /* Set the number of columns in temporary tables
  */
  nCol = p->pEList->nExpr;
  while( nAddr>0 ){
    nAddr--;
    sqlite3VdbeChangeP2(v, aAddr[nAddr], nCol);
  }

  /* Compute collating sequences used by either the ORDER BY clause or
  ** by any temporary tables needed to implement the compound select.
  ** Attach the KeyInfo structure to all temporary tables.  Invoke the
  ** ORDER BY processing if there is an ORDER BY clause.
  **
  ** This section is run by the right-most SELECT statement only.
  ** SELECT statements to the left always skip this part.  The right-most
  ** SELECT might also skip this part if it has no ORDER BY clause and
  ** no temp tables are required.
  */
  if( p->pOrderBy || (pOpenTemp && pOpenTemp->nId>0) ){

    int i;                        /* Loop counter */
    KeyInfo *pKeyInfo;            /* Collating sequence for the result set */

    assert( p->ppOpenTemp == &pOpenTemp );
    pKeyInfo = sqliteMalloc(sizeof(*pKeyInfo)+nCol*sizeof(CollSeq*));
    if( !pKeyInfo ){
      rc = SQLITE_NOMEM;
      goto multi_select_end;
    }

    pKeyInfo->enc = pParse->db->enc;
................................................................................
  /* Identify column names if we will be using them in a callback.  This
  ** step is skipped if the output is going to some other destination.
  */
  if( eDest==SRT_Callback ){
    generateColumnNames(pParse, pTabList, pEList);
  }
















  /* Generate code for all sub-queries in the FROM clause
  */
  for(i=0; i<pTabList->nSrc; i++){
    const char *zSavedAuthContext = 0;
    int needRestoreContext;

    if( pTabList->a[i].pSelect==0 ) continue;

# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing compute SELECT statements and nested
# views.
#
# $Id: select7.test,v 1.1 2004/08/29 16:25:04 drh Exp $


set testdir [file dirname $argv0]
source $testdir/tester.tcl

# A 3-way INTERSECT.  Ticket #875
do_test select7-1.1 {
  execsql {
    create temp table t1(x);
    insert into t1 values('amx');
    insert into t1 values('anx');
    insert into t1 values('amy');
    insert into t1 values('bmy');
    select * from t1 where x like 'a__'
      intersect select * from t1 where x like '_m_'
      intersect select * from t1 where x like '__x';
  }
} {amx}


# Nested views do not handle * properly.  Ticket #826.
#
do_test select7-2.1 {
  execsql {
    CREATE TABLE x(id integer primary key, a TEXT NULL);
    INSERT INTO x (a) VALUES ('first');
    CREATE TABLE tempx(id integer primary key, a TEXT NULL);
    INSERT INTO tempx (a) VALUES ('t-first');
    CREATE VIEW tv1 AS SELECT x.id, tx.id FROM x JOIN tempx tx ON tx.id=x.id;
    CREATE VIEW tv1b AS SELECT x.id, tx.id FROM x JOIN tempx tx on tx.id=x.id;
    CREATE VIEW tv2 AS SELECT * FROM tv1 UNION SELECT * FROM tv1b;
    SELECT * FROM tv2;
  }
} {1 1}


finish_test