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.364 2007/12/08 21:10:20 drh Exp $
*/
#include "sqliteInt.h"


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

  }
  if( db->mallocFailed ){
    rc = SQLITE_NOMEM;
  }
  return rc;
}

/*
** During the process of matching ORDER BY terms to columns of the 
** result set, the Exprlist.a[].done flag can be set to one of the
** following values:
*/
#define ORDERBY_MATCH_NONE     0   /* No match found */
#define ORDERBY_MATCH_PARTIAL  1   /* A good match, but not perfect */
#define ORDERBY_MATCH_EXACT    2   /* An exact match seen */

#ifndef SQLITE_OMIT_COMPOUND_SELECT
/*
** This routine associates entries in an ORDER BY expression list with
** columns in a result.  For each ORDER BY expression, the opcode of
** the top-level node is changed to TK_COLUMN and the iColumn value of
** the top-level node is filled in with column number and the iTable
** value of the top-level node is filled with iTable parameter.
**
** If there are prior SELECT clauses, they are processed first.  A match
** in an earlier SELECT takes precedence over a later SELECT.
**
** Any entry that does not match is flagged as an error.  The number
** of errors is returned.
*/
static int matchOrderbyToColumn(
  Parse *pParse,          /* A place to leave error messages */
  Select *pSelect,        /* Match to result columns of this SELECT */
  ExprList *pOrderBy,     /* The ORDER BY values to match against columns */
  int iTable,             /* Insert this value in iTable */
  int rightMost           /* TRUE for outermost recursive invocation */
){
  int nErr = 0;
  int i, j;
  ExprList *pEList;
  sqlite3 *db = pParse->db;
  NameContext nc;

  if( pSelect==0 || pOrderBy==0 ) return 1;
  if( rightMost ){
    assert( pSelect->pOrderBy==pOrderBy );
    for(i=0; i<pOrderBy->nExpr; i++){
      pOrderBy->a[i].done = ORDERBY_MATCH_NONE;
    }


  }
  if( pSelect->pPrior
      && matchOrderbyToColumn(pParse, pSelect->pPrior, pOrderBy, iTable, 0) ){
    return 1;
  }
  if( sqlite3SelectResolve(pParse, pSelect, 0) ){
    return 1;
  }
  memset(&nc, 0, sizeof(nc));
  nc.pParse = pParse;
  nc.pSrcList = pSelect->pSrc;
  nc.pEList = pEList = pSelect->pEList;
  nc.allowAgg = 1;
  for(i=0; nErr==0 && i<pOrderBy->nExpr; i++){
    struct ExprList_item *pItem;
    Expr *pE = pOrderBy->a[i].pExpr;
    int iCol = -1;
    int match = ORDERBY_MATCH_NONE;
    Expr *pDup;

    if( pOrderBy->a[i].done==ORDERBY_MATCH_EXACT ){
      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( !rightMost ) continue;
      iCol--;
      match = ORDERBY_MATCH_EXACT;
    }
    if( !match && pParse->nErr==0 && (pDup = sqlite3ExprDup(db, pE))!=0 ){

      nc.nErr = 0;
      assert( pParse->zErrMsg==0 );
      if( sqlite3ExprResolveNames(&nc, pDup) ){
        sqlite3ErrorClear(pParse);
      }else{
        for(j=0, pItem=pEList->a; j<pEList->nExpr; j++, pItem++){


          if( sqlite3ExprCompare(pItem->pExpr, pDup) ){

            iCol = j;
            match = ORDERBY_MATCH_PARTIAL;
            break;
          }
        }
      }
      sqlite3ExprDelete(pDup);
    }
    if( match ){
      pE->op = TK_COLUMN;

      pE->iTable = iTable;
      pE->iAgg = -1;
      if( pOrderBy->a[i].done!=ORDERBY_MATCH_NONE && pE->iColumn!=iCol ){
        sqlite3ErrorMsg(pParse,
          "ORDER BY term number %d is ambiguous", i+1);
        nErr++;
      }
      pE->iColumn = iCol;
      pOrderBy->a[i].done = match;
    }else if( rightMost && pOrderBy->a[i].done==ORDERBY_MATCH_NONE ){
      sqlite3ErrorMsg(pParse,
        "ORDER BY term number %d does not match any result column", i+1);
      nErr++;
      break;
    }
  }
  return nErr;  

  ** re-evaluated for each reference to it.
  */
  sNC.pEList = p->pEList;
  if( sqlite3ExprResolveNames(&sNC, p->pWhere) ||
     sqlite3ExprResolveNames(&sNC, p->pHaving) ){
    return SQLITE_ERROR;
  }

  if( p->pPrior==0 && processOrderGroupBy(&sNC, p->pOrderBy, "ORDER") ){

    return SQLITE_ERROR;
  }
  if( processOrderGroupBy(&sNC, pGroupBy, "GROUP") ){
    return SQLITE_ERROR;
  }

  if( pParse->db->mallocFailed ){
    return SQLITE_NOMEM;
  }

  /* Make sure the GROUP BY clause does not contain aggregate functions.

# 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.55 2007/12/08 21:10:20 drh Exp $

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

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

    ORDER BY f2;
  }} msg]
  lappend v $msg
} {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+101;
  }} msg]
  lappend v $msg
} {1 {ORDER BY term number 1 does not match any result column}}

# Ticket #2296
do_test select1-6.20 {
   execsql {

#    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 UNION, INTERSECT and EXCEPT operators
# in SELECT statements.
#
# $Id: select4.test,v 1.21 2007/12/08 21:10:20 drh Exp $

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

# Most tests in this file depend on compound-select. But there are a couple
# right at the end that test DISTINCT, so we cannot omit the entire file.
#

  lappend v $msg
} {0 {0 1 2 3 4 5 5 6 7 8}}
do_test select4-5.2b {
  set v [catch {execsql {
    SELECT DISTINCT log AS xyzzy FROM t1
    UNION ALL
    SELECT n FROM t1 WHERE log=3
    ORDER BY "xyzzy";
  }} msg]
  lappend v $msg
} {0 {0 1 2 3 4 5 5 6 7 8}}
do_test select4-5.2c {
  set v [catch {execsql {
    SELECT DISTINCT log FROM t1
    UNION ALL
    SELECT n FROM t1 WHERE log=3
    ORDER BY "xyzzy";
  }} msg]
  lappend v $msg
} {1 {ORDER BY term number 1 does not match any result column}}
do_test select4-5.2d {
  set v [catch {execsql {
    SELECT DISTINCT log FROM t1
    INTERSECT
    SELECT n FROM t1 WHERE log=3
    ORDER BY "xyzzy";
  }} msg]
  lappend v $msg
} {1 {ORDER BY term number 1 does not match any result column}}
do_test select4-5.2e {
  set v [catch {execsql {
    SELECT DISTINCT log FROM t1
    UNION ALL