2.4.9

2.4.10

**    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.79 2002/04/23 17:10:18 drh Exp $
*/
#include "sqliteInt.h"

/*
** Allocate a new Select structure and return a pointer to that
** structure.
*/
................................................................................
**   (4)  The subquery is not DISTINCT or the outer query is not a join.
**
**   (5)  The subquery is not DISTINCT or the outer query does not use
**        aggregates.
**
**   (6)  The subquery does not use aggregates or the outer query is not
**        DISTINCT.


**
** 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 return 0.
** If flattening is attempted this routine returns 1.
................................................................................
  assert( pSrc && iFrom>=0 && iFrom<pSrc->nId );
  pSub = pSrc->a[iFrom].pSelect;
  assert( pSub!=0 );
  if( isAgg && subqueryIsAgg ) return 0;
  if( subqueryIsAgg && pSrc->nId>1 ) return 0;
  pSubSrc = pSub->pSrc;
  assert( pSubSrc );
  if( pSubSrc->nId>1 ) return 0;
  if( pSub->isDistinct && pSrc->nId>1 ) return 0;
  if( pSub->isDistinct && isAgg ) return 0;
  if( p->isDistinct && subqueryIsAgg ) return 0;

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

**    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.80 2002/04/30 19:20:29 drh Exp $
*/
#include "sqliteInt.h"

/*
** Allocate a new Select structure and return a pointer to that
** structure.
*/
................................................................................
**   (4)  The subquery is not DISTINCT or the outer query is not a join.
**
**   (5)  The subquery is not DISTINCT or the outer query does not use
**        aggregates.
**
**   (6)  The subquery does not use aggregates or the outer query is not
**        DISTINCT.
**
**   (7)  The subquery has a FROM clause.
**
** 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 return 0.
** If flattening is attempted this routine returns 1.
................................................................................
  assert( pSrc && iFrom>=0 && iFrom<pSrc->nId );
  pSub = pSrc->a[iFrom].pSelect;
  assert( pSub!=0 );
  if( isAgg && subqueryIsAgg ) return 0;
  if( subqueryIsAgg && pSrc->nId>1 ) return 0;
  pSubSrc = pSub->pSrc;
  assert( pSubSrc );
  if( pSubSrc->nId!=1 ) return 0;
  if( pSub->isDistinct && pSrc->nId>1 ) return 0;
  if( pSub->isDistinct && isAgg ) return 0;
  if( p->isDistinct && subqueryIsAgg ) return 0;

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

**    May you share freely, never taking more than you give.
**
*************************************************************************
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  Also found here are subroutines
** to generate VDBE code to evaluate expressions.
**
** $Id: where.c,v 1.40 2002/04/02 13:26:11 drh Exp $
*/
#include "sqliteInt.h"

/*
** The query generator uses an array of instances of this structure to
** help it analyze the subexpressions of the WHERE clause.  Each WHERE
** clause subexpression is separated from the others by an AND operator.
................................................................................
    sqliteFree(pWInfo);
    return 0;
  }
  pWInfo->pParse = pParse;
  pWInfo->pTabList = pTabList;
  pWInfo->base = base;
  pWInfo->peakNTab = pWInfo->savedNTab = pParse->nTab;









  /* Split the WHERE clause into as many as 32 separate subexpressions
  ** where each subexpression is separated by an AND operator.  Any additional
  ** subexpressions are attached in the aExpr[32] and will not enter
  ** into the query optimizer computations.  32 is chosen as the cutoff
  ** since that is the number of bits in an integer that we use for an
  ** expression-used mask.  
................................................................................
      sqliteVdbeChangeP3(v, -1, pWInfo->a[i].pIdx->zName, P3_STATIC);
    }
  }

  /* Generate the code to do the search
  */
  loopMask = 0;
  pWInfo->iBreak = sqliteVdbeMakeLabel(v);
  for(i=0; i<pTabList->nId; i++){
    int j, k;
    int idx = aOrder[i];
    Index *pIdx;
    WhereLevel *pLevel = &pWInfo->a[i];

    pIdx = pLevel->pIdx;

**    May you share freely, never taking more than you give.
**
*************************************************************************
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  Also found here are subroutines
** to generate VDBE code to evaluate expressions.
**
** $Id: where.c,v 1.41 2002/04/30 19:20:29 drh Exp $
*/
#include "sqliteInt.h"

/*
** The query generator uses an array of instances of this structure to
** help it analyze the subexpressions of the WHERE clause.  Each WHERE
** clause subexpression is separated from the others by an AND operator.
................................................................................
    sqliteFree(pWInfo);
    return 0;
  }
  pWInfo->pParse = pParse;
  pWInfo->pTabList = pTabList;
  pWInfo->base = base;
  pWInfo->peakNTab = pWInfo->savedNTab = pParse->nTab;
  pWInfo->iBreak = sqliteVdbeMakeLabel(v);

  /* Special case: a WHERE clause that is constant.  Evaluate the
  ** expression and either jump over all of the code or fall thru.
  */
  if( pWhere && sqliteExprIsConstant(pWhere) ){
    sqliteExprIfFalse(pParse, pWhere, pWInfo->iBreak);
  }

  /* Split the WHERE clause into as many as 32 separate subexpressions
  ** where each subexpression is separated by an AND operator.  Any additional
  ** subexpressions are attached in the aExpr[32] and will not enter
  ** into the query optimizer computations.  32 is chosen as the cutoff
  ** since that is the number of bits in an integer that we use for an
  ** expression-used mask.  
................................................................................
      sqliteVdbeChangeP3(v, -1, pWInfo->a[i].pIdx->zName, P3_STATIC);
    }
  }

  /* Generate the code to do the search
  */
  loopMask = 0;

  for(i=0; i<pTabList->nId; i++){
    int j, k;
    int idx = aOrder[i];
    Index *pIdx;
    WhereLevel *pLevel = &pWInfo->a[i];

    pIdx = pLevel->pIdx;

#    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 SELECT statements that contain
# subqueries in their FROM clause.
#
# $Id: select6.test,v 1.8 2002/03/23 00:31:29 drh Exp $

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

do_test select6-1.0 {
  execsql {
    BEGIN;
................................................................................
do_test select6-6.6 {
  execsql {
    SELECT * FROM (
      SELECT x AS 'a' FROM t1 EXCEPT SELECT x*2 AS 'a' FROM t1
    ) ORDER BY a;
  }
} {1 3}
























finish_test

#    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 SELECT statements that contain
# subqueries in their FROM clause.
#
# $Id: select6.test,v 1.9 2002/04/30 19:20:29 drh Exp $

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

do_test select6-1.0 {
  execsql {
    BEGIN;
................................................................................
do_test select6-6.6 {
  execsql {
    SELECT * FROM (
      SELECT x AS 'a' FROM t1 EXCEPT SELECT x*2 AS 'a' FROM t1
    ) ORDER BY a;
  }
} {1 3}

# Subselects with no FROM clause
#
do_test select6-7.1 {
  execsql {
    SELECT * FROM (SELECT 1)
  }
} {1}
do_test select6-7.2 {
  execsql {
    SELECT c,b,a,* FROM (SELECT 1 AS 'a', 2 AS 'b', 'abc' AS 'c')
  }
} {abc 2 1 1 2 abc}
do_test select6-7.3 {
  execsql {
    SELECT c,b,a,* FROM (SELECT 1 AS 'a', 2 AS 'b', 'abc' AS 'c' WHERE 0)
  }
} {}
do_test select6-7.4 {
  execsql2 {
    SELECT c,b,a,* FROM (SELECT 1 AS 'a', 2 AS 'b', 'abc' AS 'c' WHERE 1)
  }
} {c abc b 2 a 1 a 1 b 2 c abc}

finish_test

#    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.  The
# focus of this file is testing the use of indices in WHERE clases.
#
# $Id: where.test,v 1.5 2001/12/22 14:49:26 drh Exp $

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

# Build some test data
#
do_test where-1.0 {
................................................................................
} {12 89 12 9}
do_test where-3.3 {
  count {
    SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
    WHERE A.w=15 AND B.p=C.w AND B.r=10202-A.y
  }
} {15 86 86 9}


























finish_test

#    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.  The
# focus of this file is testing the use of indices in WHERE clases.
#
# $Id: where.test,v 1.6 2002/04/30 19:20:29 drh Exp $

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

# Build some test data
#
do_test where-1.0 {
................................................................................
} {12 89 12 9}
do_test where-3.3 {
  count {
    SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
    WHERE A.w=15 AND B.p=C.w AND B.r=10202-A.y
  }
} {15 86 86 9}

# Test to see that the special case of a constant WHERE clause is
# handled.
#
do_test where-4.1 {
  count {
    SELECT * FROM t1 WHERE 0
  }
} {0}
do_test where-4.2 {
  count {
    SELECT * FROM t1 WHERE 1 LIMIT 1
  }
} {1 0 4 0}
do_test where-4.3 {
  execsql {
    SELECT 99 WHERE 0
  }
} {}
do_test where-4.4 {
  execsql {
    SELECT 99 WHERE 1
  }
} {99}


finish_test