**    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.
................................................................................
  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.
**
................................................................................
  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.
................................................................................
  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);

**    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.367 2007/12/13 03:45:08 drh Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
  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) ){
    if( isCompound ){
      sqlite3ErrorClear(pParse);
      return 0;
    }else{
      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;
      }
    }





  }
  return 0;
}


/*
** Analyze and ORDER BY or GROUP BY clause in a simple SELECT statement.
** Return the number of errors seen.
**
................................................................................
  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;
  sqlite3 *db;
  int moreToDo = 1;

  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;
  }
  db = pParse->db;
  for(i=0; i<pOrderBy->nExpr; i++){
    pOrderBy->a[i].done = 0;
  }
  while( pSelect->pPrior ){
    pSelect = pSelect->pPrior;
  }




  while( pSelect && moreToDo ){
    moreToDo = 0;
    for(i=0; i<pOrderBy->nExpr; i++){
      int iCol;
      Expr *pE;
      if( pOrderBy->a[i].done ) continue;
      pE = pOrderBy->a[i].pExpr;
      Expr *pDup = sqlite3ExprDup(db, pE);
      if( pDup==0 ){
        return 1;
      }
      iCol = matchOrderByTermToExprList(pParse, pSelect, pDup, i+1, 1, 0);
      sqlite3ExprDelete(pDup);
      if( iCol<0 ){
        return 1;
      }
      pEList = pSelect->pEList;
      if( pEList==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;
      }
      if( iCol>0 ){
        pE->op = TK_COLUMN;
        pE->iTable = iTable;
        pE->iAgg = -1;
        pE->iColumn = iCol-1;
        pE->pTab = 0;
        pOrderBy->a[i].done = 1;
      }else{
        moreToDo = 1;
      }
    }
    pSelect = pSelect->pNext;
  }
  for(i=0; i<pOrderBy->nExpr; i++){
    if( pOrderBy->a[i].done==0 ){
      sqlite3ErrorMsg(pParse, "%r ORDER BY term does not match any "
            "column in the result set", i+1);
      return 1;
    }
  }
  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.
................................................................................
  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, *pRight = 0;
      int cnt = 0;
      for(pLoop=p; pLoop; pLoop=pLoop->pPrior, cnt++){
        pLoop->pRightmost = p;
        pLoop->pNext = pRight;
        pRight = pLoop;
      }
      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);

**    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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.625 2007/12/11 19:34:45 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** The macro unlikely() is a hint that surrounds a boolean
** expression that is usually false.  Macro likely() surrounds
................................................................................
  char affinity;         /* MakeRecord with this affinity for SRT_Set */
  SrcList *pSrc;         /* The FROM clause */
  Expr *pWhere;          /* The WHERE clause */
  ExprList *pGroupBy;    /* The GROUP BY clause */
  Expr *pHaving;         /* The HAVING clause */
  ExprList *pOrderBy;    /* The ORDER BY clause */
  Select *pPrior;        /* Prior select in a compound select statement */

  Select *pRightmost;    /* Right-most select in a compound select statement */
  Expr *pLimit;          /* LIMIT expression. NULL means not used. */
  Expr *pOffset;         /* OFFSET expression. NULL means not used. */
  int iLimit, iOffset;   /* Memory registers holding LIMIT & OFFSET counters */
  int addrOpenEphm[3];   /* OP_OpenEphem opcodes related to this select */
};

**    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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.626 2007/12/13 03:45:08 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** The macro unlikely() is a hint that surrounds a boolean
** expression that is usually false.  Macro likely() surrounds
................................................................................
  char affinity;         /* MakeRecord with this affinity for SRT_Set */
  SrcList *pSrc;         /* The FROM clause */
  Expr *pWhere;          /* The WHERE clause */
  ExprList *pGroupBy;    /* The GROUP BY clause */
  Expr *pHaving;         /* The HAVING clause */
  ExprList *pOrderBy;    /* The ORDER BY clause */
  Select *pPrior;        /* Prior select in a compound select statement */
  Select *pNext;         /* Next select to the left in a compound */
  Select *pRightmost;    /* Right-most select in a compound select statement */
  Expr *pLimit;          /* LIMIT expression. NULL means not used. */
  Expr *pOffset;         /* OFFSET expression. NULL means not used. */
  int iLimit, iOffset;   /* Memory registers holding LIMIT & OFFSET counters */
  int addrOpenEphm[3];   /* OP_OpenEphem opcodes related to this select */
};

do_test null-5.1 {
  execsql {
    select distinct b from t1 order by b;
  }
} {{} 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}
................................................................................
      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 {

do_test null-5.1 {
  execsql {
    select distinct b from t1 order by b;
  }
} {{} 0 1}

# A UNION to two queries should treat NULL values
# as distinct.
#
# (Later:)  We also take this opportunity to test the ability
# of an ORDER BY clause to bind to either SELECT of a UNION.
# The left-most SELECT is preferred.  In standard SQL, only
# the left SELECT can be used.  The ability to match an ORDER
# BY term to the right SELECT is an SQLite extension.
#
ifcapable compound {
  do_test null-6.1 {
    execsql {
      select b from t1 union select c from t1 order by b;
    }
  } {{} 0 1}
................................................................................
      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}
  do_test null-6.5 {
    execsql {
      select b from t1 union select c from t1 order by t1.a;
    }
  } {{} 0 1}
  do_test null-6.6 {
    execsql {
      select b from t1 union select c from t1 order by main.t1.a;
    }
  } {{} 0 1}
} ;# ifcapable compound

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

#    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.22 2007/12/10 18:51:48 danielk1977 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.
#
................................................................................
    SELECT n FROM t1 WHERE log=3
    ORDER BY log;
  }
} {5}

do_test select4-4.1.2 {
  execsql {
    SELECT DISTINCT log FROM t1 UNION ALL SELECT 6


    INTERSECT
    SELECT n FROM t1 WHERE log=3
    ORDER BY log;
  }
} {5 6}

do_test select4-4.1.3 {
  execsql {
    CREATE TABLE t2 AS
      SELECT DISTINCT log FROM t1 UNION ALL SELECT 6
      INTERSECT
      SELECT n FROM t1 WHERE log=3
      ORDER BY log;

#    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.23 2007/12/13 03:45:08 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.
#
................................................................................
    SELECT n FROM t1 WHERE log=3
    ORDER BY log;
  }
} {5}

do_test select4-4.1.2 {
  execsql {
    SELECT DISTINCT log FROM t1
    UNION ALL
    SELECT 6
    INTERSECT
    SELECT n FROM t1 WHERE log=3
    ORDER BY t1.log;
  }
} {5 6}

do_test select4-4.1.3 {
  execsql {
    CREATE TABLE t2 AS
      SELECT DISTINCT log FROM t1 UNION ALL SELECT 6
      INTERSECT
      SELECT n FROM t1 WHERE log=3
      ORDER BY log;