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.443 2008/07/01 16:05:26 danielk1977 Exp $
*/
#include "sqliteInt.h"


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

  Select *pPrior;       /* Another SELECT immediately to our left */
  Vdbe *v;              /* Generate code to this VDBE */
  int nCol;             /* Number of columns in the result set */
  ExprList *pOrderBy;   /* The ORDER BY clause on p */
  int aSetP2[2];        /* Set P2 value of these op to number of columns */
  int nSetP2 = 0;       /* Number of slots in aSetP2[] used */
  SelectDest dest;      /* Alternative data destination */
  Select *pDelete = 0;  /* Chain of simple selects to delete */

  /* Make sure there is no ORDER BY or LIMIT clause on prior SELECTs.  Only
  ** the last (right-most) SELECT in the series may have an ORDER BY or LIMIT.
  */
  if( p==0 || p->pPrior==0 ){
    rc = 1;
    goto multi_select_end;

        p->iLimit = pPrior->iLimit;
        p->iOffset = pPrior->iOffset;
        if( p->iLimit ){
          addr = sqlite3VdbeAddOp1(v, OP_IfZero, p->iLimit);
          VdbeComment((v, "Jump ahead if LIMIT reached"));
        }
        rc = sqlite3Select(pParse, p, &dest, 0, 0, 0, aff);
        pDelete = p->pPrior;
        p->pPrior = pPrior;
        if( rc ){
          goto multi_select_end;
        }
        if( addr ){
          sqlite3VdbeJumpHere(v, addr);
        }

      pOffset = p->pOffset;
      p->pOffset = 0;
      uniondest.eDest = op;
      rc = sqlite3Select(pParse, p, &uniondest, 0, 0, 0, aff);
      /* Query flattening in sqlite3Select() might refill p->pOrderBy.
      ** Be sure to delete p->pOrderBy, therefore, to avoid a memory leak. */
      sqlite3ExprListDelete(p->pOrderBy);
      pDelete = p->pPrior;
      p->pPrior = pPrior;
      p->pOrderBy = pOrderBy;
      sqlite3ExprDelete(p->pLimit);
      p->pLimit = pLimit;
      p->pOffset = pOffset;
      p->iLimit = 0;
      p->iOffset = 0;

      p->pPrior = 0;
      pLimit = p->pLimit;
      p->pLimit = 0;
      pOffset = p->pOffset;
      p->pOffset = 0;
      intersectdest.iParm = tab2;
      rc = sqlite3Select(pParse, p, &intersectdest, 0, 0, 0, aff);
      pDelete = p->pPrior;
      p->pPrior = pPrior;
      sqlite3ExprDelete(p->pLimit);
      p->pLimit = pLimit;
      p->pOffset = pOffset;
      if( rc ){
        goto multi_select_end;
      }

    sqlite3_free(pKeyInfo);
  }

multi_select_end:
  pDest->iMem = dest.iMem;
  pDest->nMem = dest.nMem;
  sqlite3SelectDelete(pDelete);
  return rc;
}
#endif /* SQLITE_OMIT_COMPOUND_SELECT */

/*
** Code an output subroutine for a coroutine implementation of a
** SELECT statment.

# 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.3 2008/07/01 16:05:26 danielk1977 Exp $

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

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

    INSERT INTO t2 VALUES(3,   6,  9);
    INSERT INTO t2 VALUES(12, 15, 18);
    INSERT INTO t2 VALUES(21, 24, 27);
  }
} {}

for {set ii 1} {$ii <= 2} {incr ii} {

  if {$ii == 2} {
    do_test selectB-2.1 {
      execsql {
        CREATE INDEX i1 ON t1(a);
        CREATE INDEX i2 ON t2(d);
      }
    } {}
  }

  test_transform selectB-$ii.2 {
  SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
} {
  SELECT a FROM t1 UNION ALL SELECT d FROM t2
} {2 8 14 3 12 21}

  test_transform selectB-$ii.3 {
  SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1
} {
  SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1
} {2 3 8 12 14 21}

  test_transform selectB-$ii.4 {
  SELECT * FROM 
    (SELECT a FROM t1 UNION ALL SELECT d FROM t2) 
  WHERE a>10 ORDER BY 1
} {
  SELECT a FROM t1 WHERE a>10 UNION ALL SELECT d FROM t2 WHERE d>10 ORDER BY 1
} {12 14 21}

  test_transform selectB-$ii.5 {
  SELECT * FROM 
    (SELECT a FROM t1 UNION ALL SELECT d FROM t2) 
  WHERE a>10 ORDER BY a
} {
  SELECT a FROM t1 WHERE a>10 
    UNION ALL 
  SELECT d FROM t2 WHERE d>10 
  ORDER BY a
} {12 14 21}

  test_transform selectB-$ii.6 {
  SELECT * FROM 
    (SELECT a FROM t1 UNION ALL SELECT d FROM t2 WHERE d > 12) 
  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-$ii.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-$ii.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}

  test_transform selectB-$ii.9 {
    SELECT * FROM (
      SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
    ) 
  } {
    SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
  } {2 8 14 3 12 21 6 12 18}
  
  test_transform selectB-$ii.10 {
    SELECT * FROM (
      SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
    ) ORDER BY 1
  } {
    SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
    ORDER BY 1
  } {2 3 6 8 12 12 14 18 21}
  
  test_transform selectB-$ii.11 {
    SELECT * FROM (
      SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
    ) WHERE a>=10 ORDER BY 1 LIMIT 3
  } {
    SELECT a FROM t1 WHERE a>=10 UNION ALL SELECT d FROM t2 WHERE d>=10
    UNION ALL SELECT c FROM t1 WHERE c>=10
    ORDER BY 1 LIMIT 3
  } {12 12 14}

}


do_test selectB-2.1 {
  execsql {
    SELECT DISTINCT * FROM 
      (SELECT c FROM t1 UNION ALL SELECT e FROM t2) 
    ORDER BY 1;
  }
} {6 12 15 18 24}

do_test selectB-2.2 {
  execsql {
    SELECT c, count(*) FROM 
      (SELECT c FROM t1 UNION ALL SELECT e FROM t2) 
    GROUP BY c ORDER BY 1;
  }
} {6 2 12 1 15 1 18 1 24 1}
do_test selectB-2.3 {
  execsql {
    SELECT c, count(*) FROM 
      (SELECT c FROM t1 UNION ALL SELECT e FROM t2) 
    GROUP BY c HAVING count(*)>1;
  }
} {6 2}
do_test selectB-2.4 {
  execsql {
    SELECT t4.c, t3.a FROM 
      (SELECT c FROM t1 UNION ALL SELECT e FROM t2) AS t4, t1 AS t3
    WHERE t3.a=14
    ORDER BY 1
  }
} {6 14 6 14 12 14 15 14 18 14 24 14}

do_test selectB-2.5 {
  execsql {
    SELECT d FROM t2 
    EXCEPT 
    SELECT a FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
  }
} {}

do_test selectB-2.6 {
  execsql {
    SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
    EXCEPT 
    SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
  }
} {}

finish_test