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.341 2007/05/06 20:04:25 drh Exp $
*/
#include "sqliteInt.h"


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

**
**  (12)  The subquery is not the right term of a LEFT OUTER JOIN or the
**        subquery has no WHERE clause.  (added by ticket #350)
**
**  (13)  The subquery and outer query do not both use LIMIT
**
**  (14)  The subquery does not use OFFSET
**
**  (15)  The outer query is not part of a compound select or the
**        subquery does not have both an ORDER BY and a LIMIT clause.
**        (See ticket #2339)
**
** In this routine, the "p" parameter is a pointer to the outer query.
** The subquery is p->pSrc->a[iFrom].  isAgg is true if the outer query
** uses aggregates and subqueryIsAgg is true if the subquery uses aggregates.
**
** If flattening is not attempted, this routine is a no-op and returns 0.
** If flattening is attempted this routine returns 1.

  /* Prior to version 3.1.2, when LIMIT and OFFSET had to be simple constants,
  ** not arbitrary expresssions, we allowed some combining of LIMIT and OFFSET
  ** because they could be computed at compile-time.  But when LIMIT and OFFSET
  ** became arbitrary expressions, we were forced to add restrictions (13)
  ** and (14). */
  if( pSub->pLimit && p->pLimit ) return 0;              /* Restriction (13) */
  if( pSub->pOffset ) return 0;                          /* Restriction (14) */
  if( p->pRightmost && pSub->pLimit && pSub->pOrderBy ){
    return 0;                                            /* Restriction (15) */
  }
  if( pSubSrc->nSrc==0 ) return 0;                       /* Restriction (7)  */
  if( (pSub->isDistinct || pSub->pLimit) 
         && (pSrc->nSrc>1 || isAgg) ){          /* Restrictions (4)(5)(8)(9) */
     return 0;       
  }
  if( p->isDistinct && subqueryIsAgg ) return 0;         /* Restriction (6)  */
  if( (p->disallowOrderBy || p->pOrderBy) && pSub->pOrderBy ){

# 2007 May 6
#
# 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.
#
#***********************************************************************
#
# $Id: tkt2339.test,v 1.1 2007/05/06 20:04:25 drh Exp $
#

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

do_test tkt2339.1 {
  execsql {
    create table t1(num int);
    insert into t1 values (1);
    insert into t1 values (2);
    insert into t1 values (3);
    insert into t1 values (4);
    
    create table t2(num int);
    insert into t2 values (11);
    insert into t2 values (12);
    insert into t2 values (13);
    insert into t2 values (14);
    
    SELECT * FROM (SELECT * FROM t1 ORDER BY num DESC LIMIT 2)
    UNION
    SELECT * FROM (SELECT * FROM t2 ORDER BY num DESC LIMIT 2)
  }
} {3 4 13 14}
do_test tkt2339.2 {
  execsql {
    SELECT * FROM (SELECT * FROM t1 ORDER BY num DESC LIMIT 2)
    UNION ALL
    SELECT * FROM (SELECT * FROM t2 ORDER BY num DESC LIMIT 2)
  }
} {4 3 14 13}
do_test tkt2339.3 {
  execsql {
    SELECT * FROM (SELECT * FROM t1 ORDER BY num DESC)
    UNION ALL
    SELECT * FROM (SELECT * FROM t2 ORDER BY num DESC LIMIT 2)
  }
} {4 3 2 1 14 13}
do_test tkt2339.4 {
  execsql {
    SELECT * FROM (SELECT * FROM t1 ORDER BY num DESC LIMIT 2)
    UNION ALL
    SELECT * FROM (SELECT * FROM t2 ORDER BY num DESC)
  }
} {4 3 14 13 12 11}
do_test tkt2339.5 {
  execsql {
    SELECT * FROM (SELECT * FROM t1 ORDER BY num DESC LIMIT 2)
    UNION
    SELECT * FROM (SELECT * FROM t2 ORDER BY num DESC)
  }
} {3 4 11 12 13 14}
do_test tkt2339.6 {
  execsql {
    SELECT * FROM (SELECT * FROM t1 ORDER BY num DESC LIMIT 2)
    EXCEPT
    SELECT * FROM (SELECT * FROM t2 ORDER BY num DESC)
  }
} {3 4}
do_test tkt2339.7 {
  execsql {
    SELECT * FROM (SELECT * FROM t1 LIMIT 2)
    UNION
    SELECT * FROM (SELECT * FROM t2 ORDER BY num DESC LIMIT 2)
  }
} {1 2 13 14}
do_test tkt2339.8 {
  execsql {
    SELECT * FROM (SELECT * FROM t1 LIMIT 2)
    UNION
    SELECT * FROM (SELECT * FROM t2 LIMIT 2)
  }
} {1 2 11 12}
do_test tkt2339.9 {
  execsql {
    SELECT * FROM (SELECT * FROM t1 ORDER BY num DESC LIMIT 2)
    UNION
    SELECT * FROM (SELECT * FROM t2 LIMIT 2)
  }
} {3 4 11 12}


finish_test