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.442 2008/07/01 14:39:35 danielk1977 Exp $
*/
#include "sqliteInt.h"


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

  ** OFFSET clauses and joins them to the left-hand-side of the original
  ** using UNION ALL operators. In this case N is the number of simple
  ** select statements in the compound sub-query.
  */
  for(pSub=pSub->pPrior; pSub; pSub=pSub->pPrior){
    Select *pNew;
    ExprList *pOrderBy = p->pOrderBy;
    Expr *pLimit = p->pLimit;
    Expr *pOffset = p->pOffset;
    Select *pPrior = p->pPrior;
    p->pOrderBy = 0;
    p->pSrc = 0;
    p->pPrior = 0;
    p->pLimit = 0;
    pNew = sqlite3SelectDup(db, p);
    pNew->pPrior = pPrior;
    p->pPrior = pNew;
    p->pOrderBy = pOrderBy;
    p->op = TK_ALL;
    p->pSrc = pSrc;
    p->pLimit = pLimit;
    p->pOffset = pOffset;
    p->pRightmost = 0;
    pNew->pRightmost = 0;
  }

  /* If we reach this point, it means flattening is permitted for the
  ** iFrom-th entry of the FROM clause in the outer query.
  */

# 2008 June 24
#
# 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. 
#
# $Id: select9.test,v 1.4 2008/07/01 14:39:35 danielk1977 Exp $

# The tests in this file are focused on test compound SELECT statements 
# that have any or all of an ORDER BY, LIMIT or OFFSET clauses. As of
# version 3.6.0, SQLite contains code to use SQL indexes where possible 
# to optimize such statements.
#

# can use indexes to run without doing a in-memory sort operation.
# This block of tests (select9-3.*) is used to check if the same 
# is possible with:
#
#     CREATE VIEW v1 AS SELECT a FROM t1 UNION ALL SELECT d FROM t2
#     SELECT a FROM v1 ORDER BY 1
#
# It turns out that it is.
#
do_test select9-3.1 {
  cksort { SELECT a FROM t1 ORDER BY 1 }
} {1 2 3 4 5 6 7 8 9 10 sort}
do_test select9-3.2 {
  execsql { CREATE INDEX i1 ON t1(a) }
  cksort { SELECT a FROM t1 ORDER BY 1 }
} {1 2 3 4 5 6 7 8 9 10 nosort}
do_test select9-3.3 {
  cksort { SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 5 }
} {1 1 2 2 3 sort}
do_test select9-3.4 {
  execsql { CREATE INDEX i2 ON t2(d) }
  cksort { SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 5 }
} {1 1 2 2 3 nosort}
do_test select9-3.5 {
  execsql { CREATE VIEW v1 AS SELECT a FROM t1 UNION ALL SELECT d FROM t2 }
  cksort { SELECT a FROM v1 ORDER BY 1 LIMIT 5 }
} {1 1 2 2 3 nosort}
do_test select9-3.X {
  execsql {
    DROP INDEX i1;
    DROP INDEX i2;
    DROP VIEW v1;
  }
} {}

# 2008 June 24
#
# 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. 
#
# $Id: selectB.test,v 1.2 2008/07/01 14:39:35 danielk1977 Exp $

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

proc test_transform {testname sql1 sql2 results} {
  set ::vdbe1 [list]
  set ::vdbe2 [list]

  WHERE a>10 ORDER BY a
} {
  SELECT a FROM t1 WHERE a>10
    UNION ALL 
  SELECT d FROM t2 WHERE d>12 AND d>10
  ORDER BY a
} {14 21}

test_transform selectB-1.7 {
  SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1 LIMIT 2
} {
  SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 2
} {2 3}

test_transform selectB-1.8 {
  SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1 
  LIMIT 2 OFFSET 3
} {
  SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 2 OFFSET 3
} {12 14}


finish_test