**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.106 2004/01/15 03:30:25 drh Exp $
*/
%token_prefix TK_
%token_type {Token}
%default_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  if( pParse->zErrMsg==0 ){
................................................................................
    else { sqliteExprDelete(N); }
  }
  if( U ){
    if( A && A->nSrc>1 ){ A->a[A->nSrc-2].pUsing = U; }
    else { sqliteIdListDelete(U); }
  }
}

seltablist(A) ::= stl_prefix(X) LP select(S) RP as(Z) on_opt(N) using_opt(U). {
  A = sqliteSrcListAppend(X,0,0);
  A->a[A->nSrc-1].pSelect = S;
  if( Z.n ) sqliteSrcListAddAlias(A,&Z);
  if( N ){
    if( A && A->nSrc>1 ){ A->a[A->nSrc-2].pOn = N; }
    else { sqliteExprDelete(N); }
  }
  if( U ){
    if( A && A->nSrc>1 ){ A->a[A->nSrc-2].pUsing = U; }
    else { sqliteIdListDelete(U); }
  }
}












%type dbnm {Token}
dbnm(A) ::= .          {A.z=0; A.n=0;}
dbnm(A) ::= DOT nm(X). {A = X;}

%type joinop {int}
%type joinop2 {int}
................................................................................
  A = sqliteExpr(TK_IN, X, 0, 0);
  if( A ) A->pSelect = Y;
  A = sqliteExpr(TK_NOT, A, 0, 0);
  sqliteExprSpan(A,&X->span,&E);
}
expr(A) ::= expr(X) IN nm(Y) dbnm(D). {
  SrcList *pSrc = sqliteSrcListAppend(0, &Y, &D);
  ExprList *pList = sqliteExprListAppend(0, sqliteExpr(TK_ALL,0,0,0), 0);
  A = sqliteExpr(TK_IN, X, 0, 0);
  if( A ) A->pSelect = sqliteSelectNew(pList,pSrc,0,0,0,0,0,-1,0);
  sqliteExprSpan(A,&X->span,D.z?&D:&Y);
}
expr(A) ::= expr(X) NOT IN nm(Y) dbnm(D). {
  SrcList *pSrc = sqliteSrcListAppend(0, &Y, &D);
  ExprList *pList = sqliteExprListAppend(0, sqliteExpr(TK_ALL,0,0,0), 0);
  A = sqliteExpr(TK_IN, X, 0, 0);
  if( A ) A->pSelect = sqliteSelectNew(pList,pSrc,0,0,0,0,0,-1,0);
  A = sqliteExpr(TK_NOT, A, 0, 0);
  sqliteExprSpan(A,&X->span,D.z?&D:&Y);
}


/* CASE expressions */
expr(A) ::= CASE(C) case_operand(X) case_exprlist(Y) case_else(Z) END(E). {

**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.107 2004/01/24 20:18:13 drh Exp $
*/
%token_prefix TK_
%token_type {Token}
%default_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  if( pParse->zErrMsg==0 ){
................................................................................
    else { sqliteExprDelete(N); }
  }
  if( U ){
    if( A && A->nSrc>1 ){ A->a[A->nSrc-2].pUsing = U; }
    else { sqliteIdListDelete(U); }
  }
}
seltablist(A) ::= stl_prefix(X) LP seltablist_paren(S) RP
                  as(Z) on_opt(N) using_opt(U). {
  A = sqliteSrcListAppend(X,0,0);
  A->a[A->nSrc-1].pSelect = S;
  if( Z.n ) sqliteSrcListAddAlias(A,&Z);
  if( N ){
    if( A && A->nSrc>1 ){ A->a[A->nSrc-2].pOn = N; }
    else { sqliteExprDelete(N); }
  }
  if( U ){
    if( A && A->nSrc>1 ){ A->a[A->nSrc-2].pUsing = U; }
    else { sqliteIdListDelete(U); }
  }
}

// A seltablist_paren nonterminal represents anything in a FROM that
// is contained inside parentheses.  This can be either a subquery or
// a grouping of table and subqueries.
//
%type seltablist_paren {Select*}
%destructor seltablist_paren {sqliteSelectDelete($$);}
seltablist_paren(A) ::= select(S).      {A = S;}
seltablist_paren(A) ::= seltablist(F).  {
   A = sqliteSelectNew(0,F,0,0,0,0,0,-1,0);
}

%type dbnm {Token}
dbnm(A) ::= .          {A.z=0; A.n=0;}
dbnm(A) ::= DOT nm(X). {A = X;}

%type joinop {int}
%type joinop2 {int}
................................................................................
  A = sqliteExpr(TK_IN, X, 0, 0);
  if( A ) A->pSelect = Y;
  A = sqliteExpr(TK_NOT, A, 0, 0);
  sqliteExprSpan(A,&X->span,&E);
}
expr(A) ::= expr(X) IN nm(Y) dbnm(D). {
  SrcList *pSrc = sqliteSrcListAppend(0, &Y, &D);

  A = sqliteExpr(TK_IN, X, 0, 0);
  if( A ) A->pSelect = sqliteSelectNew(0,pSrc,0,0,0,0,0,-1,0);
  sqliteExprSpan(A,&X->span,D.z?&D:&Y);
}
expr(A) ::= expr(X) NOT IN nm(Y) dbnm(D). {
  SrcList *pSrc = sqliteSrcListAppend(0, &Y, &D);

  A = sqliteExpr(TK_IN, X, 0, 0);
  if( A ) A->pSelect = sqliteSelectNew(0,pSrc,0,0,0,0,0,-1,0);
  A = sqliteExpr(TK_NOT, A, 0, 0);
  sqliteExprSpan(A,&X->span,D.z?&D:&Y);
}


/* CASE expressions */
expr(A) ::= CASE(C) case_operand(X) case_exprlist(Y) case_else(Z) END(E). {

**    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.148 2004/01/19 04:57:53 jplyon Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
    sqliteExprListDelete(pEList);
    sqliteSrcListDelete(pSrc);
    sqliteExprDelete(pWhere);
    sqliteExprListDelete(pGroupBy);
    sqliteExprDelete(pHaving);
    sqliteExprListDelete(pOrderBy);
  }else{



    pNew->pEList = pEList;
    pNew->pSrc = pSrc;
    pNew->pWhere = pWhere;
    pNew->pGroupBy = pGroupBy;
    pNew->pHaving = pHaving;
    pNew->pOrderBy = pOrderBy;
    pNew->isDistinct = isDistinct;
................................................................................

/*
** Given a SELECT statement, generate a Table structure that describes
** the result set of that SELECT.
*/
Table *sqliteResultSetOfSelect(Parse *pParse, char *zTabName, Select *pSelect){
  Table *pTab;
  int i;
  ExprList *pEList;


  if( fillInColumnList(pParse, pSelect) ){
    return 0;
  }
  pTab = sqliteMalloc( sizeof(Table) );
  if( pTab==0 ){
    return 0;
  }
  pTab->zName = zTabName ? sqliteStrDup(zTabName) : 0;
  pEList = pSelect->pEList;
  pTab->nCol = pEList->nExpr;
  assert( pTab->nCol>0 );
  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;

**    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.149 2004/01/24 20:18:13 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
    sqliteExprListDelete(pEList);
    sqliteSrcListDelete(pSrc);
    sqliteExprDelete(pWhere);
    sqliteExprListDelete(pGroupBy);
    sqliteExprDelete(pHaving);
    sqliteExprListDelete(pOrderBy);
  }else{
    if( pEList==0 ){
      pEList = sqliteExprListAppend(0, sqliteExpr(TK_ALL,0,0,0), 0);
    }
    pNew->pEList = pEList;
    pNew->pSrc = pSrc;
    pNew->pWhere = pWhere;
    pNew->pGroupBy = pGroupBy;
    pNew->pHaving = pHaving;
    pNew->pOrderBy = pOrderBy;
    pNew->isDistinct = isDistinct;
................................................................................

/*
** Given a SELECT statement, generate a Table structure that describes
** the result set of that SELECT.
*/
Table *sqliteResultSetOfSelect(Parse *pParse, char *zTabName, Select *pSelect){
  Table *pTab;
  int i, j;
  ExprList *pEList;
  Column *aCol;

  if( fillInColumnList(pParse, pSelect) ){
    return 0;
  }
  pTab = sqliteMalloc( sizeof(Table) );
  if( pTab==0 ){
    return 0;
  }
  pTab->zName = zTabName ? sqliteStrDup(zTabName) : 0;
  pEList = pSelect->pEList;
  pTab->nCol = pEList->nExpr;
  assert( pTab->nCol>0 );
  pTab->aCol = aCol = sqliteMalloc( sizeof(pTab->aCol[0])*pTab->nCol );
  for(i=0; i<pTab->nCol; i++){
    Expr *p, *pR;
    if( pEList->a[i].zName ){
      aCol[i].zName = sqliteStrDup(pEList->a[i].zName);
    }else if( (p=pEList->a[i].pExpr)->op==TK_DOT 
               && (pR=p->pRight)!=0 && pR->token.z && pR->token.z[0] ){
      int cnt;
      sqliteSetNString(&aCol[i].zName, pR->token.z, pR->token.n, 0);
      for(j=cnt=0; j<i; j++){
        if( sqliteStrICmp(aCol[j].zName, aCol[i].zName)==0 ){
          int n;
          char zBuf[30];
          sprintf(zBuf,"_%d",++cnt);
          n = strlen(zBuf);
          sqliteSetNString(&aCol[i].zName, pR->token.z, pR->token.n, zBuf, n,0);
          j = -1;
        }
      }
    }else if( p->span.z && p->span.z[0] ){
      sqliteSetNString(&pTab->aCol[i].zName, p->span.z, p->span.n, 0);

    }else{
      char zBuf[30];
      sprintf(zBuf, "column%d", i+1);
      pTab->aCol[i].zName = sqliteStrDup(zBuf);
    }
  }
  pTab->iPKey = -1;

# 2002 May 24
#
# 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.
#
# This file implements tests for joins, including outer joins.
#
# $Id: join2.test,v 1.1 2004/01/24 20:18:13 drh Exp $

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

do_test join2-1.1 {
  execsql {
    CREATE TABLE t1(a,b);
    INSERT INTO t1 VALUES(1,11);
    INSERT INTO t1 VALUES(2,22);
    INSERT INTO t1 VALUES(3,33);
    SELECT * FROM t1;
  }  
} {1 11 2 22 3 33}
do_test join2-1.2 {
  execsql {
    CREATE TABLE t2(b,c);
    INSERT INTO t2 VALUES(11,111);
    INSERT INTO t2 VALUES(33,333);
    INSERT INTO t2 VALUES(44,444);
    SELECT * FROM t2;
  }  
} {11 111 33 333 44 444};
do_test join2-1.3 {
  execsql {
    CREATE TABLE t3(c,d);
    INSERT INTO t3 VALUES(111,1111);
    INSERT INTO t3 VALUES(444,4444);
    INSERT INTO t3 VALUES(555,5555);
    SELECT * FROM t3;
  }  
} {111 1111 444 4444 555 5555}

do_test join2-1.4 {
  execsql {
    SELECT * FROM
      t1 NATURAL JOIN t2 NATURAL JOIN t3
  }
} {1 11 111 1111}
do_test join2-1.5 {
  execsql {
    SELECT * FROM
      t1 NATURAL JOIN t2 NATURAL LEFT OUTER JOIN t3
  }
} {1 11 111 1111 3 33 333 {}}
do_test join2-1.6 {
  execsql {
    SELECT * FROM
      t1 NATURAL LEFT OUTER JOIN t2 NATURAL JOIN t3
  }
} {1 11 111 1111}
do_test join2-1.6 {
  execsql {
    SELECT * FROM
      t1 NATURAL LEFT OUTER JOIN (t2 NATURAL JOIN t3)
  }
} {1 11 111 1111 2 22 {} {} 3 33 {} {}}

finish_test

#    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.10 2003/05/02 16:44:25 drh Exp $

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

do_test select6-1.0 {
  execsql {
    BEGIN;
................................................................................
do_test sqlite6-3.2 {
  execsql {
    SELECT * FROM
      (SELECT a.q, a.p, b.r
       FROM (SELECT count(*) as p , b as q FROM t2 GROUP BY q) AS a,
            (SELECT max(a) as r, b as s FROM t2 GROUP BY s) as b
       WHERE a.q=b.s ORDER BY a.q)
    ORDER BY [a.q]
  }
} {1 1 1 2 2 3 3 4 7 4 8 15 5 5 20}
do_test select6-3.3 {
  execsql {
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1)
  }
} {10.5 3.7 14.2}

#    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.11 2004/01/24 20:18:13 drh Exp $

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

do_test select6-1.0 {
  execsql {
    BEGIN;
................................................................................
do_test sqlite6-3.2 {
  execsql {
    SELECT * FROM
      (SELECT a.q, a.p, b.r
       FROM (SELECT count(*) as p , b as q FROM t2 GROUP BY q) AS a,
            (SELECT max(a) as r, b as s FROM t2 GROUP BY s) as b
       WHERE a.q=b.s ORDER BY a.q)
    ORDER BY q
  }
} {1 1 1 2 2 3 3 4 7 4 8 15 5 5 20}
do_test select6-3.3 {
  execsql {
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1)
  }
} {10.5 3.7 14.2}