SQLite

#define etSQLESCAPE  11 /* Strings with '\'' doubled.  %q */
#define etSQLESCAPE2 12 /* Strings with '\'' doubled and enclosed in '',
                          NULL pointers replaced by SQL NULL.  %Q */
#define etTOKEN      13 /* a pointer to a Token structure */
#define etSRCLIST    14 /* a pointer to a SrcList */
#define etPOINTER    15 /* The %p conversion */
#define etSQLESCAPE3 16 /* %w -> Strings with '\"' doubled */
#define etORDINAL    17 /* %r -> 1st, 2nd, 3rd, 4th, etc.  English only */


/*
** An "etByte" is an 8-bit unsigned value.
*/
typedef unsigned char etByte;

#endif
  {  'i', 10, 1, etRADIX,      0,  0 },
  {  'n',  0, 0, etSIZE,       0,  0 },
  {  '%',  0, 0, etPERCENT,    0,  0 },
  {  'p', 16, 0, etPOINTER,    0,  1 },
  {  'T',  0, 2, etTOKEN,      0,  0 },
  {  'S',  0, 2, etSRCLIST,    0,  0 },
  {  'r', 10, 3, etORDINAL,    0,  0 },
};
#define etNINFO  (sizeof(fmtinfo)/sizeof(fmtinfo[0]))

/*
** If SQLITE_OMIT_FLOATING_POINT is defined, then none of the floating point
** conversions will work.
*/

    **   infop                       Pointer to the appropriate info struct.
    */
    switch( xtype ){
      case etPOINTER:
        flag_longlong = sizeof(char*)==sizeof(i64);
        flag_long = sizeof(char*)==sizeof(long int);
        /* Fall through into the next case */
      case etORDINAL:
      case etRADIX:
        if( infop->flags & FLAG_SIGNED ){
          i64 v;
          if( flag_longlong )   v = va_arg(ap,i64);
          else if( flag_long )  v = va_arg(ap,long int);
          else                  v = va_arg(ap,int);
          if( v<0 ){

          prefix = 0;
        }
        if( longvalue==0 ) flag_alternateform = 0;
        if( flag_zeropad && precision<width-(prefix!=0) ){
          precision = width-(prefix!=0);
        }
        bufpt = &buf[etBUFSIZE-1];
        if( xtype==etORDINAL ){
          bufpt -= 2;
        }
        {
          register const char *cset;      /* Use registers for speed */
          register int base;
          cset = &aDigits[infop->charset];
          base = infop->base;
          do{                                           /* Convert to ascii */
            *(--bufpt) = cset[longvalue%base];
            longvalue = longvalue/base;
          }while( longvalue>0 );
        }
        if( xtype==etORDINAL ){
          static const char zOrd[] = "thstndrd";
          int x = buf[etBUFSIZE-4] - '0';
          if( x>=4 ) x = 0;
          buf[etBUFSIZE-3] = zOrd[x*2];
          buf[etBUFSIZE-2] = zOrd[x*2+1];
        }
        length = &buf[etBUFSIZE-1]-bufpt;
        for(idx=precision-length; idx>0; idx--){
          *(--bufpt) = '0';                             /* Zero pad */
        }
        if( prefix ) *(--bufpt) = prefix;               /* Add sign */
        if( flag_alternateform && infop->prefix ){      /* Add "0" or "0x" */
          const char *pre;

**    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.366 2007/12/13 02:45:31 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;
}


/*
** pE is a pointer to an expression which is a single term in
** ORDER BY or GROUP BY clause.
**
** If pE evaluates to an integer constant i, then return i.
** This is an indication to the caller that it should sort
** by the i-th column of the result set.
**
** If pE is a well-formed expression and the SELECT statement
** is not compound, then return 0.  This indicates to the
** caller that it should sort by the value of the ORDER BY
** expression.
**
** If the SELECT is compound, then attempt to match pE against
** result set columns in the left-most SELECT statement.  Return
** the index i of the matching column, as an indication to the 
** caller that it should sort by the i-th column.  If there is
** no match, return -1 and leave an error message in pParse.
*/
static int matchOrderByTermToExprList(
  Parse *pParse,     /* Parsing context for error messages */
  Select *pSelect,   /* The SELECT statement with the ORDER BY clause */
  Expr *pE,          /* The specific ORDER BY term */
  int idx,           /* When ORDER BY term is this */
  int isCompound,    /* True if this is a compound SELECT */
  u8 *pHasAgg        /* True if expression contains aggregate functions */
){
  int i;             /* Loop counter */
  ExprList *pEList;  /* The columns of the result set */
  NameContext nc;    /* Name context for resolving pE */


  /* If the term is an integer constant, return the value of that
  ** constant */
  pEList = pSelect->pEList;
  if( sqlite3ExprIsInteger(pE, &i) ){
    if( i<=0 ){
      /* If i is too small, make it too big.  That way the calling
      ** function still sees a value that is out of range, but does
      ** not confuse the column number with 0 or -1 result code.
      */
      i = pEList->nExpr+1;
    }
    return i;
  }

  /* If the term is a simple identifier that try to match that identifier
  ** against a column name in the result set.
  */
  if( pE->op==TK_ID || (pE->op==TK_STRING && pE->token.z[0]!='\'') ){
    sqlite3 *db = pParse->db;
    char *zCol = sqlite3NameFromToken(db, &pE->token);
    if( db->mallocFailed ){
      return -1;
    }
    for(i=0; i<pEList->nExpr; i++){
      char *zAs = pEList->a[i].zName;
      if( zAs!=0 && sqlite3StrICmp(zAs, zCol)==0 ){
        sqlite3_free(zCol);
        return i+1;
      }
    }
    sqlite3_free(zCol);
  }

  /* Resolve all names in the ORDER BY term expression
  */
  memset(&nc, 0, sizeof(nc));
  nc.pParse = pParse;
  nc.pSrcList = pSelect->pSrc;
  nc.pEList = pEList;
  nc.allowAgg = 1;
  nc.nErr = 0;
  if( sqlite3ExprResolveNames(&nc, pE) ){
    return -1;
  }
  if( nc.hasAgg && pHasAgg ){
    *pHasAgg = 1;
  }

  /* For a compound SELECT, we need to try to match the ORDER BY
  ** expression against an expression in the result set
  */
  if( isCompound ){
    for(i=0; i<pEList->nExpr; i++){
      if( sqlite3ExprCompare(pEList->a[i].pExpr, pE) ){
        return i+1;
      }
    }
    sqlite3ErrorMsg(pParse, "%r ORDER BY term does not match any "
       "column in the result set of the left-most SELECT", idx);
    return -1;
  }else{
    return 0;
  }
}


/*
** Analyze and ORDER BY or GROUP BY clause in a simple SELECT statement.
** Return the number of errors seen.
**
** Every term of the ORDER BY or GROUP BY clause needs to be an
** expression.  If any expression is an integer constant, then
** that expression is replaced by the corresponding 
** expression from the result set.
*/
static int processOrderGroupBy(
  Parse *pParse,        /* Parsing context.  Leave error messages here */
  Select *pSelect,      /* The SELECT statement containing the clause */
  ExprList *pOrderBy,   /* The ORDER BY or GROUP BY clause to be processed */
  int isOrder,          /* 1 for ORDER BY.  0 for GROUP BY */
  u8 *pHasAgg           /* Set to TRUE if any term contains an aggregate */
){

  int i;
  sqlite3 *db = pParse->db;
  ExprList *pEList;

  if( pOrderBy==0 ) return 0;
  if( pOrderBy->nExpr>SQLITE_MAX_COLUMN ){
    const char *zType = isOrder ? "ORDER" : "GROUP";
    sqlite3ErrorMsg(pParse, "too many terms in %s BY clause", zType);
    return 1;
  }
  pEList = pSelect->pEList;
  if( pEList==0 ){
    return 0;
  }
  for(i=0; i<pOrderBy->nExpr; i++){
    int iCol;
    Expr *pE = pOrderBy->a[i].pExpr;
    iCol = matchOrderByTermToExprList(pParse, pSelect, pE, i+1, 0, pHasAgg);
    if( iCol<0 ){
      return 1;
    }
    if( iCol>pEList->nExpr ){
      const char *zType = isOrder ? "ORDER" : "GROUP";
      sqlite3ErrorMsg(pParse, 
         "%r %s BY term out of range - should be "
         "between 1 and %d", i+1, zType, pEList->nExpr);
      return 1;
    }
    if( iCol>0 ){
      CollSeq *pColl = pE->pColl;
      int flags = pE->flags & EP_ExpCollate;
      sqlite3ExprDelete(pE);
      pE = sqlite3ExprDup(db, pEList->a[iCol-1].pExpr);
      pOrderBy->a[i].pExpr = pE;
      if( pColl && flags ){
        pE->pColl = pColl;
        pE->flags |= flags;
      }
    }
  }
  return 0;
}

/*
** Analyze and ORDER BY or GROUP BY clause in a SELECT statement.  Return
** the number of errors seen.
**
** The processing depends on whether the SELECT is simple or compound.
** For a simple SELECT statement, evry term of the ORDER BY or GROUP BY
** clause needs to be an expression.  If any expression is an integer
** constant, then that expression is replaced by the corresponding 
** expression from the result set.
**
** For compound SELECT statements, every expression needs to be of
** type TK_COLUMN with a iTable value as given in the 4th parameter.
** If any expression is an integer, that becomes the column number.
** Otherwise, match the expression against result set columns from
** the left-most SELECT.
*/
static int processCompoundOrderBy(
  Parse *pParse,        /* Parsing context.  Leave error messages here */
  Select *pSelect,      /* The SELECT statement containing the ORDER BY */
  int iTable            /* Output table for compound SELECT statements */
){
  int i;
  ExprList *pOrderBy;
  ExprList *pEList;

  pOrderBy = pSelect->pOrderBy;
  if( pOrderBy==0 ) return 0;
  if( pOrderBy->nExpr>SQLITE_MAX_COLUMN ){
    sqlite3ErrorMsg(pParse, "too many terms in ORDER BY clause");
    return 1;
  }
  while( pSelect->pPrior ){
    pSelect = pSelect->pPrior;
  }
  pEList = pSelect->pEList;
  if( pEList==0 ){
    return 1;
  }


  for(i=0; i<pOrderBy->nExpr; i++){

    int iCol;
    Expr *pE = pOrderBy->a[i].pExpr;














    iCol = matchOrderByTermToExprList(pParse, pSelect, pE, i+1, 1, 0);




























    if( iCol<0 ){
      return 1;
    }
    if( iCol>pEList->nExpr ){
      sqlite3ErrorMsg(pParse, 
         "%r ORDER BY term out of range - should be "
         "between 1 and %d", i+1, pEList->nExpr);
      return 1;
    }
    pE->op = TK_COLUMN;
    pE->iTable = iTable;
    pE->iAgg = -1;
    pE->iColumn = iCol-1;


    pE->pTab = 0;


  }


  return 0;
}


/*
** Get a VDBE for the given parser context.  Create a new one if necessary.
** If an error occurs, return NULL and leave a message in pParse.
*/
Vdbe *sqlite3GetVdbe(Parse *pParse){
  Vdbe *v = pParse->pVdbe;

        */
        unionTab = iParm;
      }else{
        /* We will need to create our own temporary table to hold the
        ** intermediate results.
        */
        unionTab = pParse->nTab++;
        if( processCompoundOrderBy(pParse, p, unionTab) ){
          rc = 1;
          goto multi_select_end;
        }
        addr = sqlite3VdbeAddOp(v, OP_OpenEphemeral, unionTab, 0);
        if( priorOp==SRT_Table ){
          assert( nSetP2<sizeof(aSetP2)/sizeof(aSetP2[0]) );
          aSetP2[nSetP2++] = addr;

      /* INTERSECT is different from the others since it requires
      ** two temporary tables.  Hence it has its own case.  Begin
      ** by allocating the tables we will need.
      */
      tab1 = pParse->nTab++;
      tab2 = pParse->nTab++;
      if( processCompoundOrderBy(pParse, p, tab1) ){
        rc = 1;
        goto multi_select_end;
      }
      createSortingIndex(pParse, p, pOrderBy);

      addr = sqlite3VdbeAddOp(v, OP_OpenEphemeral, tab1, 0);
      assert( p->addrOpenEphm[0] == -1 );

  selectInnerLoop(pParse, p, &eList, 0, 0, 0, -1, eDest, iParm, brk, brk, 0);
  sqlite3VdbeResolveLabel(v, brk);
  sqlite3VdbeAddOp(v, OP_Close, base, 0);
  
  return 1;
}




















































/*
** This routine resolves any names used in the result set of the
** supplied SELECT statement. If the SELECT statement being resolved
** is a sub-select, then pOuterNC is a pointer to the NameContext 
** of the parent SELECT.
*/
int sqlite3SelectResolve(

  ** 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 ){
    if( processOrderGroupBy(pParse, p, p->pOrderBy, 0, &sNC.hasAgg) ){
      return SQLITE_ERROR;
    }
  }
  if( processOrderGroupBy(pParse, p, pGroupBy, 0, &sNC.hasAgg) ){
    return SQLITE_ERROR;
  }

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

  db = pParse->db;
  if( p==0 || db->mallocFailed || pParse->nErr ){
    return 1;
  }
  if( sqlite3AuthCheck(pParse, SQLITE_SELECT, 0, 0, 0) ) return 1;
  memset(&sAggInfo, 0, sizeof(sAggInfo));

  pOrderBy = p->pOrderBy;
  if( IgnorableOrderby(eDest) ){
    p->pOrderBy = 0;
  }
  if( sqlite3SelectResolve(pParse, p, 0) ){
    goto select_end;
  }
  p->pOrderBy = pOrderBy;

#ifndef SQLITE_OMIT_COMPOUND_SELECT
  /* If there is are a sequence of queries, do the earlier ones first.
  */
  if( p->pPrior ){
    if( p->pRightmost==0 ){
      Select *pLoop;
      int cnt = 0;
      for(pLoop=p; pLoop; pLoop=pLoop->pPrior, cnt++){
        pLoop->pRightmost = p;
      }
      if( SQLITE_MAX_COMPOUND_SELECT>0 && cnt>SQLITE_MAX_COMPOUND_SELECT ){
        sqlite3ErrorMsg(pParse, "too many terms in compound SELECT");
        return 1;
      }
    }
    return multiSelect(pParse, p, eDest, iParm, aff);
  }
#endif










  /* Make local copies of the parameters for this query.
  */
  pTabList = p->pSrc;
  pWhere = p->pWhere;
  pGroupBy = p->pGroupBy;
  pHaving = p->pHaving;
  isAgg = p->isAgg;

  }
} {{} 0 1}

# A UNION to two queries should treat NULL values
# as distinct
#
ifcapable compound {
  do_test null-6.1 {
    execsql {
      select b from t1 union select c from t1 order by b;
    }
  } {{} 0 1}
  do_test null-6.2 {
    execsql {
      select b from t1 union select c from t1 order by 1;
    }
  } {{} 0 1}
  do_test null-6.3 {
    execsql {
      select b from t1 union select c from t1 order by t1.b;
    }
  } {{} 0 1}
  do_test null-6.4 {
    execsql {
      select b from t1 union select c from t1 order by main.t1.b;
    }
  } {{} 0 1}
} ;# ifcapable compound

# The UNIQUE constraint only applies to non-null values
#
ifcapable conflict {
do_test null-7.1 {
    execsql {