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.61 2002/02/18 03:21:46 drh Exp $
*/
#include "sqliteInt.h"

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

  pTab->aCol = sqliteMalloc( sizeof(pTab->aCol[0])*pTab->nCol );
  for(i=0; i<pTab->nCol; i++){
    Expr *p;
    if( pEList->a[i].zName ){
      pTab->aCol[i].zName = sqliteStrDup(pEList->a[i].zName);
    }else if( (p=pEList->a[i].pExpr)->span.z && p->span.z[0] ){
      sqliteSetNString(&pTab->aCol[i].zName, p->span.z, p->span.n, 0);
    }else if( p->op==TK_DOT && p->pRight && p->pRight->token.z &&
           p->pRight->token.z[0] ){
      sqliteSetNString(&pTab->aCol[i].zName, 
           p->pRight->token.z, p->pRight->token.n, 0);
    }else{
      char zBuf[30];
      sprintf(zBuf, "column%d", i+1);
      pTab->aCol[i].zName = sqliteStrDup(zBuf);
    }
  }
  pTab->iPKey = -1;

  base = pParse->nTab;

  /* 
  ** Do not even attempt to generate any code if we have already seen
  ** errors before this routine starts.
  */
  if( pParse->nErr>0 ) goto select_end;


  /* Look up every table in the table list and create an appropriate
  ** columnlist in pEList if there isn't one already.  (The parser leaves
  ** a NULL in the p->pEList if the SQL said "SELECT * FROM ...")
  */
  if( fillInColumnList(pParse, p) ){
    goto select_end;

    if( sqliteExprResolveIds(pParse, pTabList, pEList, pHaving) ){
      goto select_end;
    }
    if( sqliteExprCheck(pParse, pHaving, isAgg, 0) ){
      goto select_end;
    }
  }

  /* Begin generating code.
  */
  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++){
    int oldNTab;
    Table *pTab = pTabList->a[i].pTab;
    if( !pTab->isTransient ) continue;
    assert( pTabList->a[i].pSelect!=0 );
    oldNTab = pParse->nTab;
    pParse->nTab += i+1;
    sqliteVdbeAddOp(v, OP_OpenTemp, oldNTab+i, 0);
    sqliteSelect(pParse, pTabList->a[i].pSelect, SRT_Table, oldNTab+i);
    pParse->nTab = oldNTab;
  }

  /* Do an analysis of aggregate expressions.
  */
  sqliteAggregateInfoReset(pParse);
  if( isAgg ){
    assert( pParse->nAgg==0 && pParse->iAggCount<0 );
    for(i=0; i<pEList->nExpr; i++){
      if( sqliteExprAnalyzeAggregates(pParse, pEList->a[i].pExpr) ){
        goto select_end;
      }
    }

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




















  /* 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);

# 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.
#
#***********************************************************************
# 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.1 2002/02/18 03:21:47 drh Exp $

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

# Build some test data
#
set fd [open data1.txt w]
for {set i 1} {$i<32} {incr i} {
  for {set j 0} {pow(2,$j)<$i} {incr j} {}
  puts $fd "[expr {32-$i}]\t[expr {10-$j}]"
}
close $fd
execsql {
  CREATE TABLE t1(x int, y int);
  COPY t1 FROM 'data1.txt'
}
file delete data1.txt

do_test select6-1.0 {
  execsql {SELECT DISTINCT y FROM t1 ORDER BY y}
} {5 6 7 8 9 10}

do_test select6-1.1 {
  execsql2 {SELECT * FROM (SELECT x, y FROM t1 ORDER BY x LIMIT 1)}
} {x 31 y 10}
do_test select6-1.2 {
  execsql {SELECT count(*) FROM (SELECT y FROM t1)}
} {31}
do_test select6-1.3 {
  execsql {SELECT count(*) FROM (SELECT DISTINCT y FROM t1)}
} {6}
do_test select6-1.4 {
  execsql {SELECT count(*) FROM (SELECT DISTINCT * FROM (SELECT y FROM t1))}
} {6}
do_test select6-1.5 {
  execsql {SELECT count(*) FROM (SELECT * FROM (SELECT DISTINCT y FROM t1))}
} {6}



finish_test

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.23 2002/02/18 03:21:47 drh Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>

<a href="#insert">INSERT command</a> documentation for additional
information.</p>  
}

Section SELECT select

Syntax {sql-statement} {
SELECT <result> FROM <table-list>
[WHERE <expression>]
[GROUP BY <expr-list>]
[HAVING <expression>]
[<compound-op> <select>]*
[ORDER BY <sort-expr-list>]
[LIMIT <integer> [OFFSET <integer>]]
} {result} {
<result-column> [, <result-column>]*
} {result-column} {
STAR | <expression> [ [AS] <string> ]
} {table-list} {
<table> [, <table>]*
} {table} {
<table-name> [AS <alias>] |
( <select> ) [AS <alias>]
} {sort-expr-list} {
<expr> [<sort-order>] [, <expr> [<sort-order>]]*
} {sort-order} {
ASC | DESC
} {compound_op} {
UNION | UNION ALL | INTERSECT | EXCEPT
}

puts {
<p>The SELECT statement is used to query the database.  The
result of a SELECT is zero or more rows of data where each row
has a fixed number of columns.  The number of columns in the
result is specified by the expression list in between the
SELECT and FROM keywords.  Any arbitrary expression can be used
as a result.  If a result expression is }
puts "[Operator *] then all columns of all tables are substituted"
puts {for that one expression.</p>

<p>The query is executed again one or more tables specified after
the FROM keyword.  If more than one table is specified, then the
query is against the (inner) join of the various tables.  A sub-query
in parentheses may be substituted for any table name in the FROM clause.
</p>

<p>The WHERE clause can be used to limit the number of rows over
which the query operates.  In the current implementation,
indices will only be used to
optimize the query if WHERE expression contains equality comparisons
connected by the AND operator.</p>