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.81 2002/05/08 11:54:15 drh Exp $
*/
#include "sqliteInt.h"

/*
** Allocate a new Select structure and return a pointer to that
** structure.
*/

** If flattening is not attempted, this routine is a no-op and return 0.
** If flattening is attempted this routine returns 1.
**
** All of the expression analysis must occur on both the outer query and
** the subquery before this routine runs.
*/
int flattenSubquery(Select *p, int iFrom, int isAgg, int subqueryIsAgg){
  Select *pSub;       /* The inner query or "subquery" */
  IdList *pSrc;       /* The FROM clause of the outer query */
  IdList *pSubSrc;    /* The FROM clause of the subquery */
  ExprList *pList;    /* The result set of the outer query */
  int i;
  int iParent, iSub;
  Expr *pWhere;

  /* Check to see if flattening is permitted.  Return 0 if not.
  */
  if( p==0 ) return 0;
  pSrc = p->pSrc;
  assert( pSrc && iFrom>=0 && iFrom<pSrc->nId );
  pSub = pSrc->a[iFrom].pSelect;
  assert( pSub!=0 );
  if( isAgg && subqueryIsAgg ) return 0;
  if( subqueryIsAgg && pSrc->nId>1 ) return 0;
  pSubSrc = pSub->pSrc;
  assert( pSubSrc );
  if( pSubSrc->nId!=1 ) return 0;
  if( pSub->isDistinct && pSrc->nId>1 ) return 0;
  if( pSub->isDistinct && isAgg ) return 0;
  if( p->isDistinct && subqueryIsAgg ) return 0;

  /* If we reach this point, it means flattening is permitted for the
  ** i-th entry of the FROM clause in the outer query.
  */
  iParent = p->base + iFrom;
  iSub = pSub->base;
  substExprList(p->pEList, iParent, pSub->pEList, iSub);
  pList = p->pEList;
  for(i=0; i<pList->nExpr; i++){

    goto select_end;
  }

  /* Begin generating code.
  */
  v = sqliteGetVdbe(pParse);
  if( v==0 ) goto select_end;

  /* Identify column names if we will be using in the callback.  This
  ** step is skipped if the output is going to a table or a memory cell.
  */
  if( eDest==SRT_Callback ){
    generateColumnNames(pParse, p->base, pTabList, pEList);
  }

  /* Set the limiter
  */
  if( p->nLimit<=0 ){
    p->nOffset = 0;
  }else{
    if( p->nOffset<0 ) p->nOffset = 0;
    sqliteVdbeAddOp(v, OP_Limit, p->nLimit, p->nOffset);
  }

  /* Generate code for all sub-queries in the FROM clause
  */
  for(i=0; i<pTabList->nId; i++){
    if( pTabList->a[i].pSelect==0 ) continue;
    sqliteSelect(pParse, pTabList->a[i].pSelect, SRT_TempTable, base+i,
                 p, i, &isAgg);

        if( sqliteExprAnalyzeAggregates(pParse, pOrderBy->a[i].pExpr) ){
          goto select_end;
        }
      }
    }
  }


















  /* Reset the aggregator
  */
  if( isAgg ){
    sqliteVdbeAddOp(v, OP_AggReset, 0, pParse->nAgg);
    for(i=0; i<pParse->nAgg; i++){
      FuncDef *pFunc;
      if( (pFunc = pParse->aAgg[i].pFunc)!=0 && pFunc->xFinalize!=0 ){

# 2002 February 26
#
# 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 VIEW statements.
#
# $Id: view.test,v 1.4 2002/05/08 11:54:16 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);

} {7 8 9 10 11 12 13 14}
do_test view-2.6 {
  execsql {
    SELECT x FROM v2 WHERE a>10
  }
} {11}

# Test that column name of views are generated correctly.
#
do_test view-3.1 {
  execsql2 {
    SELECT * FROM v1 LIMIT 1
  }
} {a 2 b 3}
do_test view-3.2 {
  execsql2 {
    SELECT * FROM v2 LIMIT 1
  }
} {x 7 a 8 b 9 c 10}
do_test view-3.3 {
  execsql2 {
    DROP VIEW v1;
    CREATE VIEW v1 AS SELECT a AS 'xyz', b+c AS 'pqr', c-b FROM t1;
    SELECT * FROM v1 LIMIT 1
  }
} {xyz 2 pqr 7 c-b 1}
do_test  view-3.4 {
  execsql2 {
    CREATE VIEW v3 AS SELECT a FROM t1 UNION SELECT b FROM t1 ORDER BY b;
    SELECT * FROM v3 LIMIT 4;
  }
} {b 2 b 3 b 5 b 6}


finish_test