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.73 2002/03/03 03:03:53 drh Exp $
*/
#include "sqliteInt.h"

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

    sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, 0);
    sqliteVdbeAddOp(v, OP_String, iParm, 0);
    sqliteVdbeAddOp(v, OP_PutStrKey, iParm, 0);
  }else 

  /* Store the result as data using a unique key.
  */
  if( eDest==SRT_Table || eDest==SRT_TempTable ){
    sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, 0);
    sqliteVdbeAddOp(v, OP_NewRecno, iParm, 0);
    sqliteVdbeAddOp(v, OP_Pull, 1, 0);
    sqliteVdbeAddOp(v, OP_PutIntKey, iParm, 0);
  }else 

  /* Construct a record from the query result, but instead of

  v = sqliteGetVdbe(pParse);
  if( v==0 ) goto select_end;

  /* 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);
    pTabList = p->pSrc;
    pWhere = p->pWhere;
    pOrderBy = p->pOrderBy;
    pGroupBy = p->pGroupBy;
    pHaving = p->pHaving;
    isDistinct = p->isDistinct;
  }

  /* Check to see if this is a subquery that can be "flattened" into its parent.
  ** If flattening is a possiblity, do so and return immediately.  
  */
  if( pParent && pParentAgg &&
      flattenSubquery(pParent, parentTab, *pParentAgg, isAgg) ){
    if( isAgg ) *pParentAgg = 1;
    return rc;
  }

  /* If the output is destined for a temporary table, open that table.
  */
  if( eDest==SRT_TempTable ){
    sqliteVdbeAddOp(v, OP_OpenTemp, iParm, 0);
  }

  /* Do an analysis of aggregate expressions.
  */
  sqliteAggregateInfoReset(pParse);
  if( isAgg ){
    assert( pParse->nAgg==0 );
    for(i=0; i<pEList->nExpr; i++){

/*
** 2001 September 15
**
** 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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.99 2002/03/03 03:03:53 drh Exp $
*/
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
#include <stdio.h>

*/
#define SRT_Callback     1  /* Invoke a callback with each row of result */
#define SRT_Mem          2  /* Store result in a memory cell */
#define SRT_Set          3  /* Store result as unique keys in a table */
#define SRT_Union        5  /* Store result as keys in a table */
#define SRT_Except       6  /* Remove result from a UNION table */
#define SRT_Table        7  /* Store result as data with a unique key */
#define SRT_TempTable    8  /* Store result in a trasient table */

/*
** When a SELECT uses aggregate functions (like "count(*)" or "avg(f1)")
** we have to do some additional analysis of expressions.  An instance
** of the following structure holds information about a single subexpression
** somewhere in the SELECT statement.  An array of these structures holds
** all the information we need to generate code for aggregate

#    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 SELECT statements that contain
# subqueries in their FROM clause.
#
# $Id: select6.test,v 1.4 2002/03/03 03:03:54 drh Exp $

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

do_test select6-1.0 {
  execsql {
    BEGIN;

  }
} {1 2 3 4}
do_test select6-4.3 {
  execsql {
    SELECT DISTINCT y FROM (SELECT y FROM t1) WHERE y<5 ORDER BY y
  }
} {1 2 3 4}
do_test select6-4.4 {
  execsql {
    SELECT avg(y) FROM (SELECT DISTINCT y FROM t1) WHERE y<5 ORDER BY y
  }
} {2.5}
do_test select6-4.5 {
  execsql {
    SELECT avg(y) FROM (SELECT DISTINCT y FROM t1 WHERE y<5) ORDER BY y
  }
} {2.5}


finish_test