**    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.136 2003/05/02 14:32:13 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
**
** Flattening is only attempted if all of the following are true:
**
**   (1)  The subquery and the outer query do not both use aggregates.
**
**   (2)  The subquery is not an aggregate or the outer query is not a join.
**
**   (3)  The subquery is not a join.
**
**   (4)  The subquery is not DISTINCT or the outer query is not a join.
**
**   (5)  The subquery is not DISTINCT or the outer query does not use
**        aggregates.
**
**   (6)  The subquery does not use aggregates or the outer query is not
................................................................................
  assert( pSrc && iFrom>=0 && iFrom<pSrc->nSrc );
  pSub = pSrc->a[iFrom].pSelect;
  assert( pSub!=0 );
  if( isAgg && subqueryIsAgg ) return 0;
  if( subqueryIsAgg && pSrc->nSrc>1 ) return 0;
  pSubSrc = pSub->pSrc;
  assert( pSubSrc );
  if( pSubSrc->nSrc!=1 ) return 0;
  if( (pSub->isDistinct || pSub->nLimit>=0) &&  (pSrc->nSrc>1 || isAgg) ){
     return 0;
  }
  if( (p->isDistinct || p->nLimit>=0) && subqueryIsAgg ) return 0;
  if( p->pOrderBy && pSub->pOrderBy ) return 0;

  /* If we reach this point, it means flattening is permitted for the
  ** iFrom-th entry of the FROM clause in the outer query.
  */









  iParent = pSrc->a[iFrom].iCursor;






  pSrc->a[iFrom].iCursor = pSubSrc->a[0].iCursor;





























  substExprList(p->pEList, iParent, pSub->pEList);
  pList = p->pEList;
  for(i=0; i<pList->nExpr; i++){
    Expr *pExpr;
    if( pList->a[i].zName==0 && (pExpr = pList->a[i].pExpr)->span.z!=0 ){
      pList->a[i].zName = sqliteStrNDup(pExpr->span.z, pExpr->span.n);
    }
................................................................................
    p->pWhere = pWhere;
  }else{
    substExpr(p->pWhere, iParent, pSub->pEList);
    if( pWhere ){
      p->pWhere = sqliteExpr(TK_AND, p->pWhere, pWhere, 0);
    }
  }




  p->isDistinct = p->isDistinct || pSub->isDistinct;




  if( pSub->nLimit>=0 ){
    if( p->nLimit<0 ){
      p->nLimit = pSub->nLimit;
    }else if( p->nLimit+p->nOffset > pSub->nLimit+pSub->nOffset ){
      p->nLimit = pSub->nLimit + pSub->nOffset - p->nOffset;
    }
  }
  p->nOffset += pSub->nOffset;

  if( pSrc->a[iFrom].pTab && pSrc->a[iFrom].pTab->isTransient ){
    sqliteDeleteTable(0, pSrc->a[iFrom].pTab);
  }
  pSrc->a[iFrom].pTab = pSubSrc->a[0].pTab;
  pSubSrc->a[0].pTab = 0;
  assert( pSrc->a[iFrom].pSelect==pSub );
  pSrc->a[iFrom].pSelect = pSubSrc->a[0].pSelect;
  pSubSrc->a[0].pSelect = 0;

  sqliteSelectDelete(pSub);
  return 1;
}

/*
** Analyze the SELECT statement passed in as an argument to see if it
** is a simple min() or max() query.  If it is and this query can be
................................................................................
    }
  }

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


    if( pTabList->a[i].pSelect==0 ) continue;
    if( pTabList->a[i].zName!=0 ){
      zSavedAuthContext = pParse->zAuthContext;
      pParse->zAuthContext = pTabList->a[i].zName;



    }
    sqliteSelect(pParse, pTabList->a[i].pSelect, SRT_TempTable, 
                 pTabList->a[i].iCursor, p, i, &isAgg);
    if( pTabList->a[i].zName!=0 ){
      pParse->zAuthContext = zSavedAuthContext;
    }
    pTabList = p->pSrc;
    pWhere = p->pWhere;
    if( eDest==SRT_Callback ){
      pOrderBy = p->pOrderBy;
    }
    pGroupBy = p->pGroupBy;
    pHaving = p->pHaving;
    isDistinct = p->isDistinct;
  }

**    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.137 2003/05/02 16:04:17 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
**
** Flattening is only attempted if all of the following are true:
**
**   (1)  The subquery and the outer query do not both use aggregates.
**
**   (2)  The subquery is not an aggregate or the outer query is not a join.
**
**   (3)  (No longer a restriction)
**
**   (4)  The subquery is not DISTINCT or the outer query is not a join.
**
**   (5)  The subquery is not DISTINCT or the outer query does not use
**        aggregates.
**
**   (6)  The subquery does not use aggregates or the outer query is not
................................................................................
  assert( pSrc && iFrom>=0 && iFrom<pSrc->nSrc );
  pSub = pSrc->a[iFrom].pSelect;
  assert( pSub!=0 );
  if( isAgg && subqueryIsAgg ) return 0;
  if( subqueryIsAgg && pSrc->nSrc>1 ) return 0;
  pSubSrc = pSub->pSrc;
  assert( pSubSrc );
  if( pSubSrc->nSrc==0 ) return 0;
  if( (pSub->isDistinct || pSub->nLimit>=0) &&  (pSrc->nSrc>1 || isAgg) ){
     return 0;
  }
  if( (p->isDistinct || p->nLimit>=0) && subqueryIsAgg ) return 0;
  if( p->pOrderBy && pSub->pOrderBy ) return 0;

  /* If we reach this point, it means flattening is permitted for the
  ** iFrom-th entry of the FROM clause in the outer query.
  */

  /* Move all of the FROM elements of the subquery into the
  ** the FROM clause of the outer query.  Before doing this, remember
  ** the cursor number for the original outer query FROM element in
  ** iParent.  The iParent cursor will never be used.  Subsequent code
  ** will scan expressions looking for iParent references and replace
  ** those references with expressions that resolve to the subquery FROM
  ** elements we are now copying in.
  */
  iParent = pSrc->a[iFrom].iCursor;
  {
    int nSubSrc = pSubSrc->nSrc;

    if( pSrc->a[iFrom].pTab && pSrc->a[iFrom].pTab->isTransient ){
      sqliteDeleteTable(0, pSrc->a[iFrom].pTab);
    }
    sqliteFree(pSrc->a[iFrom].zName);
    sqliteFree(pSrc->a[iFrom].zAlias);
    if( nSubSrc>1 ){
      int extra = nSubSrc - 1;
      for(i=1; i<nSubSrc; i++){
        pSrc = sqliteSrcListAppend(pSrc, 0, 0);
      }
      p->pSrc = pSrc;
      for(i=pSrc->nSrc-1; i-extra>=iFrom; i--){
        pSrc->a[i] = pSrc->a[i-extra];
      }
    }
    for(i=0; i<nSubSrc; i++){
      pSrc->a[i+iFrom] = pSubSrc->a[i];
      memset(&pSubSrc->a[i], 0, sizeof(pSubSrc->a[i]));
    }
  }

  /* Now begin substituting subquery result set expressions for 
  ** references to the iParent in the outer query.
  ** 
  ** Example:
  **
  **   SELECT a+5, b*10 FROM (SELECT x*3 AS a, y+10 AS b FROM t1) WHERE a>b;
  **   \                     \_____________ subquery __________/          /
  **    \_____________________ outer query ______________________________/
  **
  ** We look at every expression in the outer query and every place we see
  ** "a" we substitute "x*3" and every place we see "b" we substitute "y+10".
  */
  substExprList(p->pEList, iParent, pSub->pEList);
  pList = p->pEList;
  for(i=0; i<pList->nExpr; i++){
    Expr *pExpr;
    if( pList->a[i].zName==0 && (pExpr = pList->a[i].pExpr)->span.z!=0 ){
      pList->a[i].zName = sqliteStrNDup(pExpr->span.z, pExpr->span.n);
    }
................................................................................
    p->pWhere = pWhere;
  }else{
    substExpr(p->pWhere, iParent, pSub->pEList);
    if( pWhere ){
      p->pWhere = sqliteExpr(TK_AND, p->pWhere, pWhere, 0);
    }
  }

  /* The flattened query is distinct if either the inner or the
  ** outer query is distinct. 
  */
  p->isDistinct = p->isDistinct || pSub->isDistinct;

  /* Transfer the limit expression from the subquery to the outer
  ** query.
  */
  if( pSub->nLimit>=0 ){
    if( p->nLimit<0 ){
      p->nLimit = pSub->nLimit;
    }else if( p->nLimit+p->nOffset > pSub->nLimit+pSub->nOffset ){
      p->nLimit = pSub->nLimit + pSub->nOffset - p->nOffset;
    }
  }
  p->nOffset += pSub->nOffset;

  /* Finially, delete what is left of the subquery and return
  ** success.






  */
  sqliteSelectDelete(pSub);
  return 1;
}

/*
** Analyze the SELECT statement passed in as an argument to see if it
** is a simple min() or max() query.  If it is and this query can be
................................................................................
    }
  }

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

    if( pTabList->a[i].pSelect==0 ) continue;
    if( pTabList->a[i].zName!=0 ){
      zSavedAuthContext = pParse->zAuthContext;
      pParse->zAuthContext = pTabList->a[i].zName;
      needRestoreContext = 1;
    }else{
      needRestoreContext = 0;
    }
    sqliteSelect(pParse, pTabList->a[i].pSelect, SRT_TempTable, 
                 pTabList->a[i].iCursor, p, i, &isAgg);
    if( needRestoreContext ){
      pParse->zAuthContext = zSavedAuthContext;
    }
    pTabList = p->pSrc;
    pWhere = p->pWhere;
    if( eDest!=SRT_Union && eDest!=SRT_Except && eDest!=SRT_Discard ){
      pOrderBy = p->pOrderBy;
    }
    pGroupBy = p->pGroupBy;
    pHaving = p->pHaving;
    isDistinct = p->isDistinct;
  }

#    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 VIEW statements.
#
# $Id: view.test,v 1.14 2003/05/02 14:32:15 drh Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl

do_test view-1.0 {
  execsql {
    CREATE TABLE t1(a,b,c);
    INSERT INTO t1 VALUES(1,2,3);
................................................................................
    INSERT INTO t2 VALUES(55,5);
    SELECT * FROM t2;
  }
} {22 2 33 3 44 4 55 5}
do_test view-5.2 {
  execsql {
    CREATE VIEW v5 AS
      SELECT t1.x, t2.y FROM t1 JOIN t2 USING(a);
    SELECT * FROM v5;
  }
} {1 22 4 55}







































do_test view-6.1 {
  execsql {
    SELECT min(x), min(a), min(b), min(c), min(a+b+c) FROM v2;
  }
} {7 8 9 10 27}
do_test view-6.2 {

#    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 VIEW statements.
#
# $Id: view.test,v 1.15 2003/05/02 16:04:17 drh Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl

do_test view-1.0 {
  execsql {
    CREATE TABLE t1(a,b,c);
    INSERT INTO t1 VALUES(1,2,3);
................................................................................
    INSERT INTO t2 VALUES(55,5);
    SELECT * FROM t2;
  }
} {22 2 33 3 44 4 55 5}
do_test view-5.2 {
  execsql {
    CREATE VIEW v5 AS
      SELECT t1.x AS v, t2.y AS w FROM t1 JOIN t2 USING(a);
    SELECT * FROM v5;
  }
} {1 22 4 55}

# Verify that the view v5 gets flattened.  see sqliteFlattenSubquery().
# Ticket #272
do_test view-5.3 {
  lsearch [execsql {
    EXPLAIN SELECT * FROM v5;
  }] OpenTemp
} {-1}
do_test view-5.4 {
  execsql {
    SELECT * FROM v5 AS a, t2 AS b WHERE a.w=b.y;
  }
} {1 22 22 2 4 55 55 5}
do_test view-5.5 {
  lsearch [execsql {
    EXPLAIN SELECT * FROM v5 AS a, t2 AS b WHERE a.w=b.y;
  }] OpenTemp
} {-1}
do_test view-5.6 {
  execsql {
    SELECT * FROM t2 AS b, v5 AS a WHERE a.w=b.y;
  }
} {22 2 1 22 55 5 4 55}
do_test view-5.7 {
  lsearch [execsql {
    EXPLAIN SELECT * FROM t2 AS b, v5 AS a WHERE a.w=b.y;
  }] OpenTemp
} {-1}
do_test view-5.8 {
  execsql {
    SELECT * FROM t1 AS a, v5 AS b, t2 AS c WHERE a.x=b.v AND b.w=c.y;
  }
} {1 2 3 4 1 22 22 2 4 5 6 7 4 55 55 5}
do_test view-5.9 {
  lsearch [execsql {
    EXPLAIN SELECT * FROM t1 AS a, v5 AS b, t2 AS c WHERE a.x=b.v AND b.w=c.y;
  }] OpenTemp
} {-1}

do_test view-6.1 {
  execsql {
    SELECT min(x), min(a), min(b), min(c), min(a+b+c) FROM v2;
  }
} {7 8 9 10 27}
do_test view-6.2 {