**    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.270 2005/09/19 15:37:07 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
      switch( p->op ){
         case TK_EXCEPT:  op = SRT_Except;   break;
         case TK_UNION:   op = SRT_Union;    break;
         case TK_ALL:     op = SRT_Table;    break;
      }
      p->pPrior = 0;
      p->pOrderBy = 0;

      pLimit = p->pLimit;
      p->pLimit = 0;
      pOffset = p->pOffset;
      p->pOffset = 0;
      rc = sqlite3Select(pParse, p, op, unionTab, 0, 0, 0, aff);
      p->pPrior = pPrior;
      p->pOrderBy = pOrderBy;
................................................................................
  if( (pSub->pLimit && p->pLimit) || pSub->pOffset || 
      (pSub->pLimit && isAgg) ) return 0;
  if( pSubSrc->nSrc==0 ) return 0;
  if( pSub->isDistinct && (pSrc->nSrc>1 || isAgg) ){
     return 0;
  }
  if( p->isDistinct && subqueryIsAgg ) return 0;
  if( p->pOrderBy && pSub->pOrderBy ) return 0;

  /* Restriction 3:  If the subquery is a join, make sure the subquery is 
  ** not used as the right operand of an outer join.  Examples of why this
  ** is not allowed:
  **
  **         t1 LEFT OUTER JOIN (t2 JOIN t3)
  **

**    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.271 2005/09/19 17:35:53 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
      switch( p->op ){
         case TK_EXCEPT:  op = SRT_Except;   break;
         case TK_UNION:   op = SRT_Union;    break;
         case TK_ALL:     op = SRT_Table;    break;
      }
      p->pPrior = 0;
      p->pOrderBy = 0;
      p->disallowOrderBy = pOrderBy!=0;
      pLimit = p->pLimit;
      p->pLimit = 0;
      pOffset = p->pOffset;
      p->pOffset = 0;
      rc = sqlite3Select(pParse, p, op, unionTab, 0, 0, 0, aff);
      p->pPrior = pPrior;
      p->pOrderBy = pOrderBy;
................................................................................
  if( (pSub->pLimit && p->pLimit) || pSub->pOffset || 
      (pSub->pLimit && isAgg) ) return 0;
  if( pSubSrc->nSrc==0 ) return 0;
  if( pSub->isDistinct && (pSrc->nSrc>1 || isAgg) ){
     return 0;
  }
  if( p->isDistinct && subqueryIsAgg ) return 0;
  if( (p->disallowOrderBy || p->pOrderBy) && pSub->pOrderBy ) return 0;

  /* Restriction 3:  If the subquery is a join, make sure the subquery is 
  ** not used as the right operand of an outer join.  Examples of why this
  ** is not allowed:
  **
  **         t1 LEFT OUTER JOIN (t2 JOIN t3)
  **

**    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.419 2005/09/17 15:20:27 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** Many people are failing to set -DNDEBUG=1 when compiling SQLite.
** Setting NDEBUG makes the code smaller and run faster.  So the following
................................................................................
struct Select {
  ExprList *pEList;      /* The fields of the result */
  u8 op;                 /* One of: TK_UNION TK_ALL TK_INTERSECT TK_EXCEPT */
  u8 isDistinct;         /* True if the DISTINCT keyword is present */
  u8 isResolved;         /* True once sqlite3SelectResolve() has run. */
  u8 isAgg;              /* True if this is an aggregate query */
  u8 usesVirt;           /* True if uses an OpenVirtual opcode */

  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 */

**    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.420 2005/09/19 17:35:53 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** Many people are failing to set -DNDEBUG=1 when compiling SQLite.
** Setting NDEBUG makes the code smaller and run faster.  So the following
................................................................................
struct Select {
  ExprList *pEList;      /* The fields of the result */
  u8 op;                 /* One of: TK_UNION TK_ALL TK_INTERSECT TK_EXCEPT */
  u8 isDistinct;         /* True if the DISTINCT keyword is present */
  u8 isResolved;         /* True once sqlite3SelectResolve() has run. */
  u8 isAgg;              /* True if this is an aggregate query */
  u8 usesVirt;           /* True if uses an OpenVirtual opcode */
  u8 disallowOrderBy;    /* Do not allow an ORDER BY to be attached if TRUE */
  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 */

# 2005 September 19
#
# 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.
#
# This file implements tests to verify that ticket #1444 has been
# fixed.  
#

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

# The use of a VIEW that contained an ORDER BY clause within a UNION ALL
# was causing problems.  See ticket #1444.
#
do_test tkt1444-1.1 {
  execsql {
    CREATE TABLE DemoTable (x INTEGER, TextKey TEXT, DKey Real);
    CREATE INDEX DemoTableIdx ON DemoTable (TextKey);
    INSERT INTO DemoTable VALUES(9,8,7);
    INSERT INTO DemoTable VALUES(1,2,3);
    CREATE VIEW DemoView AS SELECT * FROM DemoTable ORDER BY TextKey;
    SELECT * FROM DemoTable UNION ALL SELECT * FROM DemoView ORDER BY 1;
  }
} {1 2 3 1 2 3 9 8 7 9 8 7}
do_test tkt1444-1.2 {
  execsql {
    SELECT * FROM DemoTable UNION ALL SELECT * FROM DemoView;
  }
} {9 8 7 1 2 3 1 2 3 9 8 7}
do_test tkt1444-1.3 {
  execsql {
    DROP VIEW DemoView;
    CREATE VIEW DemoView AS SELECT * FROM DemoTable;
    SELECT * FROM DemoTable UNION ALL SELECT * FROM DemoView ORDER BY 1;
  }
} {1 2 3 1 2 3 9 8 7 9 8 7}
do_test tkt1444-1.4 {
  execsql {
    SELECT * FROM DemoTable UNION ALL SELECT * FROM DemoView;
  }
} {9 8 7 1 2 3 9 8 7 1 2 3}

finish_test