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.284 2007/04/13 16:06:33 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

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

        **
        ** Generate code to write the results of the select into the temporary
        ** table allocated and opened above.
        */
        int iParm = pExpr->iTable +  (((int)affinity)<<16);
        ExprList *pEList;
        assert( (pExpr->iTable&0x0000FFFF)==pExpr->iTable );
        if( sqlite3Select(pParse, pExpr->pSelect, SRT_Set, iParm, 0, 0, 0, 0) ){
          return;
        }
        pEList = pExpr->pSelect->pEList;
        if( pEList && pEList->nExpr>0 ){ 
          keyInfo.aColl[0] = binaryCompareCollSeq(pParse, pExpr->pLeft,
              pEList->a[0].pExpr);
        }
      }else if( pExpr->pList ){
        /* Case 2:     expr IN (exprlist)

      }else{
        sop = SRT_Exists;
        sqlite3VdbeAddOp(v, OP_MemInt, 0, iMem);
        VdbeComment((v, "# Init EXISTS result"));
      }
      sqlite3ExprDelete(pSel->pLimit);
      pSel->pLimit = sqlite3Expr(TK_INTEGER, 0, 0, &one);
      if( sqlite3Select(pParse, pSel, sop, iMem, 0, 0, 0, 0) ){
        return;
      }
      break;
    }
  }

  if( testAddr ){
    sqlite3VdbeJumpHere(v, testAddr);
  }

**    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.336 2007/04/13 16:06:33 drh Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.

  if( pSelect->pPrior ){
    if( matchOrderbyToColumn(pParse, pSelect->pPrior, pOrderBy, iTable, 0) ){
      return 1;
    }
  }
  pEList = pSelect->pEList;
  for(i=0; i<pOrderBy->nExpr; i++){
    struct ExprList_item *pItem;
    Expr *pE = pOrderBy->a[i].pExpr;
    int iCol = -1;
    char *zLabel;

    if( pOrderBy->a[i].done ) continue;
    if( sqlite3ExprIsInteger(pE, &iCol) ){
      if( iCol<=0 || iCol>pEList->nExpr ){
        sqlite3ErrorMsg(pParse,
          "ORDER BY position %d should be between 1 and %d",
          iCol, pEList->nExpr);
        nErr++;
        break;
      }
      if( !mustComplete ) continue;
      iCol--;
    }
    if( iCol<0 && (zLabel = sqlite3NameFromToken(&pE->token))!=0 ){
      for(j=0, pItem=pEList->a; j<pEList->nExpr; j++, pItem++){

        char *zName;
        if( pItem->zName ){
          zName = sqlite3StrDup(pItem->zName);
        }else{
          zName = sqlite3NameFromToken(&pItem->pExpr->token);

        }
        if( zName && sqlite3StrICmp(zName, zLabel)==0 ){
          iCol = j;
          break;
        }
        sqliteFree(zName);
      }
      sqliteFree(zLabel);


    }
    if( iCol>=0 ){
      pE->op = TK_COLUMN;
      pE->iColumn = iCol;
      pE->iTable = iTable;
      pE->iAgg = -1;
      pOrderBy->a[i].done = 1;

    }else if( mustComplete ){
      sqlite3ErrorMsg(pParse,
        "ORDER BY term number %d does not match any result column", i+1);
      nErr++;
      break;
    }
  }
  return nErr;  

# 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.53 2007/04/13 16:06:34 drh Exp $

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

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

} {0 {f1 11 f1 22 f1 33 f1 44}}
do_test select1-6.11 {
  set v [catch {execsql2 {
    SELECT f1 FROM test1 UNION SELECT f2+100 FROM test1
    ORDER BY f2+100;
  }} msg]
  lappend v $msg
} {1 {ORDER BY term number 1 does not match any result column}}

# Ticket #2296
do_test select1-6.20 {
   execsql {
     CREATE TABLE t6(a TEXT, b TEXT);
     INSERT INTO t6 VALUES('a','0');
     INSERT INTO t6 VALUES('b','1');
     INSERT INTO t6 VALUES('c','2');
     INSERT INTO t6 VALUES('d','3');
     SELECT a FROM t6 WHERE b IN 
        (SELECT b FROM t6 WHERE a<='b' UNION SELECT '3' AS x
                 ORDER BY 1 LIMIT 1)
   }
} {a}
do_test select1-6.21 {
   execsql {
     SELECT a FROM t6 WHERE b IN 
        (SELECT b FROM t6 WHERE a<='b' UNION SELECT '3' AS x
                 ORDER BY 1 DESC LIMIT 1)
   }
} {d}
do_test select1-6.22 {
   execsql {
     SELECT a FROM t6 WHERE b IN 
        (SELECT b FROM t6 WHERE a<='b' UNION SELECT '3' AS x
                 ORDER BY b LIMIT 2)
     ORDER BY a;
   }
} {a b}
do_test select1-6.23 {
   execsql {
     SELECT a FROM t6 WHERE b IN 
        (SELECT b FROM t6 WHERE a<='b' UNION SELECT '3' AS x
                 ORDER BY x DESC LIMIT 2)
     ORDER BY a;
   }
} {b d}

} ;#ifcapable compound

do_test select1-7.1 {
  set v [catch {execsql {
     SELECT f1 FROM test1 WHERE f2=;
  }} msg]
  lappend v $msg