SQLite

**    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