SQLite

**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.181 2005/01/18 17:20:10 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**

    **     SELECT a+b AS x FROM table WHERE x<10;
    **
    ** In cases like this, replace pExpr with a copy of the expression that
    ** forms the result set entry ("a+b" in the example) and return immediately.
    ** Note that the expression in the result set should have already been
    ** resolved by the time the WHERE clause is resolved.
    */
    if( cnt==0 && pEList!=0 && zTab==0 ){
      for(j=0; j<pEList->nExpr; j++){
        char *zAs = pEList->a[j].zName;
        if( zAs!=0 && sqlite3StrICmp(zAs, zCol)==0 ){
          assert( pExpr->pLeft==0 && pExpr->pRight==0 );
          pExpr->op = TK_AS;
          pExpr->iColumn = j;
          pExpr->pLeft = sqlite3ExprDup(pEList->a[j].pExpr);

**    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.227 2005/01/18 17:20:10 drh Exp $
*/
#include "sqliteInt.h"


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

  }
  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, pCol=aCol; i<pTab->nCol; i++, pCol++){
    Expr *p, *pR;
    char *zType;
    char *zName;
    char *zBasename;
    int cnt;
    
    /* Get an appropriate name for the column
    */
    p = pEList->a[i].pExpr;
    assert( p->pRight==0 || p->pRight->token.z==0 || p->pRight->token.z[0]!=0 );
    if( (zName = pEList->a[i].zName)!=0 ){
      /* If the column contains an "AS <name>" phrase, use <name> as the name */
      zName = sqliteStrDup(zName);
    }else if( p->op==TK_DOT 
              && (pR=p->pRight)!=0 && pR->token.z && pR->token.z[0] ){

      /* For columns of the from A.B use B as the name */
      zName = sqlite3MPrintf("%T", &pR->token);







    }else if( p->span.z && p->span.z[0] ){
      /* Use the original text of the column expression as its name */
      zName = sqlite3MPrintf("%T", &p->span);
    }else{
      /* If all else fails, make up a name */
      zName = sqlite3MPrintf("column%d", i+1);
    }
    sqlite3Dequote(zName);

    /* Make sure the column name is unique.  If the name is not unique,
    ** append a integer to the name so that it becomes unique.
    */
    zBasename = zName;
    for(j=cnt=0; j<i; j++){
      if( sqlite3StrICmp(aCol[j].zName, zName)==0 ){
        zName = sqlite3MPrintf("%s:%d", zBasename, ++cnt);
        j = -1;
      }
    }
    if( zBasename!=zName ){
      sqliteFree(zBasename);
    }
    pCol->zName = zName;

    /* Get the typename, type affinity, and collating sequence for the
    ** column.
    */
    zType = sqliteStrDup(columnType(pParse, pSelect->pSrc ,p));
    pCol->zType = zType;
    pCol->affinity = SQLITE_AFF_NUMERIC;
    if( zType ){
      pCol->affinity = sqlite3AffinityType(zType, strlen(zType));
    }
    pCol->pColl = sqlite3ExprCollSeq(pParse, p);

              pExpr->token.z = 0;
              pExpr->token.n = 0;
              pExpr->token.dyn = 0;
            }else{
              pExpr = pRight;
              pExpr->span = pExpr->token;
            }
            pNew = sqlite3ExprListAppend(pNew, pExpr, &pRight->token);
          }
        }
        if( !tableSeen ){
          if( zTName ){
            sqlite3ErrorMsg(pParse, "no such table: %s", zTName);
          }else{
            sqlite3ErrorMsg(pParse, "no tables specified");

#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc4.test,v 1.11 2005/01/18 17:20:10 drh Exp $

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

# Prepare a statement that will create a temporary table.  Then do
# a rollback.  Then try to execute the prepared statement.
#

    insert into b values ('+1',4);
    
    select a.*, x.*
      from a, (select key,sum(period) from b group by key) as x
      where a.key=x.key;
  }
} {01 data01 01 3.0 +1 data+1 +1 7.0}

# Ticket #1036.  When creating tables from a SELECT on a view, use the
# short names of columns.
#
do_test misc4-5.1 {
  execsql {
    create table t4(a,b);
    create table t5(a,c);
    insert into t4 values (1,2);
    insert into t5 values (1,3);
    create view myview as select t4.a a from t4 inner join t5 on t4.a=t5.a;
    create table problem as select * from myview; 
  }
  execsql2 {
    select * FROM problem;
  }
} {a 1}
do_test misc4-5.2 {
  execsql2 {
    create table t6 as select * from t4, t5;
    select * from t6;
  }
} {a 1 b 2 a:1 1 c 3}

finish_test

# 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 the SELECT statement.
#
# $Id: select1.test,v 1.40 2005/01/18 17:20:10 drh Exp $

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

# Try to select on a non-existant table.
#
do_test select1-1.1 {

do_test select1-6.1.2 {
  set v [catch {execsql2 {SELECT f1 as 'f1' FROM test1 ORDER BY f2}} msg]
  lappend v $msg
} {0 {f1 11 f1 33}}
do_test select1-6.1.3 {
  set v [catch {execsql2 {SELECT * FROM test1 WHERE f1==11}} msg]
  lappend v $msg
} {0 {f1 11 f2 22}}
do_test select1-6.1.4 {
  set v [catch {execsql2 {SELECT DISTINCT * FROM test1 WHERE f1==11}} msg]
  execsql {PRAGMA full_column_names=off}
  lappend v $msg
} {0 {f1 11 f2 22}}
do_test select1-6.1.5 {
  set v [catch {execsql2 {SELECT * FROM test1 WHERE f1==11}} msg]
  lappend v $msg
} {0 {f1 11 f2 22}}
do_test select1-6.1.6 {
  set v [catch {execsql2 {SELECT DISTINCT * FROM test1 WHERE f1==11}} msg]
  lappend v $msg