select.c revision 7c478bd95313f5f23a4c958a745db2134aa03244
#pragma ident "%Z%%M% %I% %E% SMI"
/*
** 2001 September 15
**
** 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 contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.161.2.4 2004/07/20 01:45:49 drh Exp $
*/
#include "sqliteInt.h"
/*
** Allocate a new Select structure and return a pointer to that
** structure.
*/
int isDistinct, /* true if the DISTINCT keyword is present */
int nLimit, /* LIMIT value. -1 means not used */
int nOffset /* OFFSET value. 0 means no offset */
){
if( pNew==0 ){
}else{
if( pEList==0 ){
}
}
return pNew;
}
/*
** Given 1 to 3 identifiers preceeding the JOIN keyword, determine the
** type of join. Return an integer constant that expresses that type
** in terms of the following bit values:
**
** JT_INNER
** JT_OUTER
** JT_NATURAL
** JT_LEFT
** JT_RIGHT
**
** A full outer join is the combination of JT_LEFT and JT_RIGHT.
**
** If an illegal or unsupported join type is seen, then still return
** a join type, but put an error in the pParse structure.
*/
int jointype = 0;
Token *p;
static struct {
const char *zKeyword;
int nChar;
int code;
} keywords[] = {
};
int i, j;
for(i=0; i<3 && apAll[i]; i++){
p = apAll[i];
break;
}
}
break;
}
}
if(
){
"RIGHT and FULL OUTER JOINs are not currently supported");
}
return jointype;
}
/*
** Return the index of a column in a table. Return -1 if the column
** is not contained in the table.
*/
int i;
}
return -1;
}
/*
** Add a term to the WHERE expression in *ppExpr that requires the
** zCol column to be equal in the two tables pTab1 and pTab2.
*/
static void addWhereTerm(
const char *zCol, /* Name of the column */
){
if( *ppExpr ){
}else{
}
}
/*
** Set the EP_FromJoin property on all terms of the given expression.
**
** The EP_FromJoin property is used on terms of an expression to tell
** the LEFT OUTER JOIN processing logic that this term is part of the
** join restriction specified in the ON or USING clause and not a part
** of the more general WHERE clause. These terms are moved over to the
** WHERE clause during join processing but we need to remember that they
** originated in the ON or USING clause.
*/
static void setJoinExpr(Expr *p){
while( p ){
setJoinExpr(p->pLeft);
p = p->pRight;
}
}
/*
** This routine processes the join information for a SELECT statement.
** ON and USING clauses are converted into extra terms of the WHERE clause.
** NATURAL joins also create extra WHERE clause terms.
**
** This routine returns the number of errors encountered.
*/
int i, j;
/* When the NATURAL keyword is present, add WHERE clause terms for
** every column that the two tables have in common.
*/
"an ON or USING clause", 0);
return 1;
}
}
}
}
/* Disallow both ON and USING clauses in the same join
*/
"clauses in the same join");
return 1;
}
/* Add the ON clause to the end of the WHERE clause, connected by
** and AND operator.
*/
if( p->pWhere==0 ){
}else{
}
}
/* Create extra terms on the WHERE clause for each column named
** in the USING clause. Example: If the two tables to be joined are
** A and B and the USING clause names X, Y, and Z, then add this
** to the WHERE clause: A.X=B.X AND A.Y=B.Y AND A.Z=B.Z
** Report an error if any column mentioned in the USING clause is
** not contained in both tables to be joined.
*/
int j;
return 1;
}
}
}
}
return 0;
}
/*
** Delete the given Select structure and all of its substructures.
*/
void sqliteSelectDelete(Select *p){
if( p==0 ) return;
sqliteSrcListDelete(p->pSrc);
sqliteExprDelete(p->pWhere);
sqliteExprDelete(p->pHaving);
sqliteSelectDelete(p->pPrior);
sqliteFree(p->zSelect);
sqliteFree(p);
}
/*
** Delete the aggregate information from the parse structure.
*/
}
/*
** Insert code into "v" that will push the record on the top of the
** stack into the sorter.
*/
char *zSortOrder;
int i;
if( zSortOrder==0 ) return;
int type;
int c;
}else{
}
}else{
}
zSortOrder[i] = c;
}
sqliteVdbeAddOp(v, OP_SortPut, 0, 0);
}
/*
** This routine adds a P3 argument to the last VDBE opcode that was
** inserted. The P3 argument added is a string suitable for the
** OP_MakeKey or OP_MakeIdxKey opcodes. The string consists of
** characters 't' or 'n' depending on whether or not the various
** fields of the key to be generated should be treated as numeric
** or as text. See the OP_MakeKey and OP_MakeIdxKey opcode
** documentation for additional information about the P3 string.
** See also the sqliteAddIdxKeyType() routine.
*/
int i;
if( zType==0 ) return;
for(i=0; i<nColumn; i++){
}
zType[i] = 0;
}
/*
** Add code to implement the OFFSET and LIMIT
*/
static void codeLimiter(
Vdbe *v, /* Generate code into this VM */
Select *p, /* The SELECT statement being coded */
int iContinue, /* Jump here to skip the current record */
int iBreak, /* Jump here to end the loop */
int nPop /* Number of times to pop stack when jumping */
){
if( p->iOffset>=0 ){
if( nPop>0 ){
}
}
if( p->iLimit>=0 ){
}
}
/*
** This routine generates the code for the inside of the inner loop
** of a SELECT.
**
** If srcTab and nColumn are both zero, then the pEList expressions
** are evaluated in order to get the data for this row. If nColumn>0
** then data is pulled from srcTab and pEList is used only to get the
** datatypes for each column.
*/
static int selectInnerLoop(
Select *p, /* The complete select statement being coded */
int srcTab, /* Pull data from this table */
int nColumn, /* Number of columns in the source table */
int distinct, /* If >=0, make sure results are distinct */
int eDest, /* How to dispose of the results */
int iParm, /* An argument to the disposal method */
int iContinue, /* Jump here to continue with next row */
int iBreak /* Jump here to break out of the inner loop */
){
int i;
int hasDistinct; /* True if the DISTINCT keyword is present */
if( v==0 ) return 0;
/* If there was a LIMIT clause on the SELECT statement, then do the check
** to see if this row should be output.
*/
if( pOrderBy==0 && !hasDistinct ){
}
/* Pull the requested columns.
*/
if( nColumn>0 ){
for(i=0; i<nColumn; i++){
}
}else{
}
}
/* If the DISTINCT keyword was present on the SELECT statement
** and this row has been seen before, then do not make this row
** part of the result.
*/
if( hasDistinct ){
#endif
sqliteVdbeAddOp(v, OP_String, 0, 0);
if( pOrderBy==0 ){
}
}
switch( eDest ){
/* In this mode, write each query result to the key of the temporary
** table iParm.
*/
case SRT_Union: {
sqliteVdbeAddOp(v, OP_String, 0, 0);
break;
}
/* Store the result as data using a unique key.
*/
case SRT_Table:
case SRT_TempTable: {
if( pOrderBy ){
}else{
}
break;
}
/* Construct a record from the query result, but instead of
** saving that record, use it as a key to delete elements from
** the temporary table iParm.
*/
case SRT_Except: {
int addr;
break;
}
/* If we are creating a set for an "expr IN (SELECT ...)" construct,
** then there should be a single item on the stack. Write this
** item into the set table with bogus data.
*/
case SRT_Set: {
int addr1 = sqliteVdbeCurrentAddr(v);
int addr2;
if( pOrderBy ){
}else{
sqliteVdbeAddOp(v, OP_String, 0, 0);
}
break;
}
/* If this is a scalar select that is part of an expression, then
** store the results in the appropriate memory cell and break out
** of the scan loop.
*/
case SRT_Mem: {
if( pOrderBy ){
}else{
}
break;
}
/* Send the data to the callback function.
*/
case SRT_Callback:
case SRT_Sorter: {
if( pOrderBy ){
}else{
}
break;
}
/* Invoke a subroutine to handle the results. The subroutine itself
** is responsible for popping the results off of the stack.
*/
case SRT_Subroutine: {
if( pOrderBy ){
}else{
}
break;
}
/* Discard the results. This is used for SELECT statements inside
** the body of a TRIGGER. The purpose of such selects is to call
** user-defined functions that have side effects. We do not care
** about the actual results of the select.
*/
default: {
break;
}
}
return 0;
}
/*
** If the inner loop was generated using a non-null pOrderBy argument,
** then the results were placed in a sorter. After the loop is terminated
** we need to run the sorter and output the results. The following
** routine generates the code needed to do that.
*/
static void generateSortTail(
Select *p, /* The SELECT statement */
Vdbe *v, /* Generate code into this VDBE */
int nColumn, /* Number of columns of data */
int eDest, /* Write the sorted results here */
int iParm /* Optional parameter associated with eDest */
){
int end1 = sqliteVdbeMakeLabel(v);
int end2 = sqliteVdbeMakeLabel(v);
int addr;
if( eDest==SRT_Sorter ) return;
sqliteVdbeAddOp(v, OP_Sort, 0, 0);
switch( eDest ){
case SRT_Callback: {
break;
}
case SRT_Table:
case SRT_TempTable: {
break;
}
case SRT_Set: {
sqliteVdbeAddOp(v, OP_String, 0, 0);
break;
}
case SRT_Mem: {
break;
}
case SRT_Subroutine: {
int i;
for(i=0; i<nColumn; i++){
}
break;
}
default: {
/* Do nothing */
break;
}
}
sqliteVdbeAddOp(v, OP_SortReset, 0, 0);
}
/*
** Generate code that will tell the VDBE the datatypes of
** columns in the result set.
**
** This routine only generates code if the "PRAGMA show_datatypes=on"
** has been executed. The datatypes are reported out in the azCol
** parameter to the callback function. The first N azCol[] entries
** are the names of the columns, and the second N entries are the
** datatypes for the columns.
**
** The "datatype" for a result that is a column of a type is the
** datatype definition extracted from the CREATE TABLE statement.
** The datatype for an expression is either TEXT or NUMERIC. The
** datatype for a ROWID field is INTEGER.
*/
static void generateColumnTypes(
){
int i, j;
char *zType = 0;
if( p==0 ) continue;
if( iCol<0 ){
zType = "INTEGER";
}else{
}
}else{
if( sqliteExprType(p)==SQLITE_SO_TEXT ){
zType = "TEXT";
}else{
zType = "NUMERIC";
}
}
}
}
/*
** Generate code that will tell the VDBE the names of columns
** in the result set. This information is used to provide the
** azCol[] values in the callback.
*/
static void generateColumnNames(
){
int i, j;
int fullNames, shortNames;
assert( v!=0 );
Expr *p;
if( p==0 ) continue;
continue;
}
char *zCol;
if( iCol<0 ){
zCol = "_ROWID_";
}else{
}
char *zName = 0;
char *zTab;
}else{
}
}else{
char zName[30];
}
}
}
/*
** Name of the connection operator, used for error messages.
*/
static const char *selectOpName(int id){
char *z;
switch( id ){
case TK_ALL: z = "UNION ALL"; break;
case TK_INTERSECT: z = "INTERSECT"; break;
case TK_EXCEPT: z = "EXCEPT"; break;
default: z = "UNION"; break;
}
return z;
}
/*
** Forward declaration
*/
/*
** Given a SELECT statement, generate a Table structure that describes
** the result set of that SELECT.
*/
int i, j;
return 0;
}
if( pTab==0 ){
return 0;
}
int cnt;
for(j=cnt=0; j<i; j++){
int n;
char zBuf[30];
j = -1;
}
}
}else{
char zBuf[30];
}
}
return pTab;
}
/*
** For the given SELECT statement, do three things.
**
** (1) Fill in the pTabList->a[].pTab fields in the SrcList that
** defines the set of tables that should be scanned. For views,
** fill pTabList->a[].pSelect with a copy of the SELECT statement
** that implements the view. A copy is made of the view's SELECT
** statement so that we can freely modify or delete that statement
** without worrying about messing up the presistent representation
** of the view.
**
** (2) Add terms to the WHERE clause to accomodate the NATURAL keyword
** on joins and the ON and USING clause of joins.
**
** (3) Scan the list of columns in the result set (pEList) looking
** for instances of the "*" operator or the TABLE.* operator.
** If found, expand each "*" to be every column in every table
** and TABLE.* to be every column in TABLE.
**
** Return 0 on success. If there are problems, leave an error message
** in pParse and return non-zero.
*/
int i, j, k, rc;
if( p==0 || p->pSrc==0 ) return 1;
/* Look up every table in the table list.
*/
/* This routine has run before! No need to continue */
return 0;
}
/* A sub-query in the FROM clause of a SELECT */
char zFakeName[60];
}
if( pTab==0 ){
return 1;
}
/* The isTransient flag indicates that the Table structure has been
** dynamically allocated and may be freed at any time. In other words,
** pTab is not pointing to a persistent table structure that defines
** part of the schema. */
}else{
/* An ordinary table or view name in the FROM clause */
if( pTab==0 ){
return 1;
}
/* We reach here if the named table is a really a view */
return 1;
}
/* If pTabList->a[i].pSelect!=0 it means we are dealing with a
** view within a view. The SELECT structure has already been
** copied by the outer view so we can skip the copy step here
** in the inner view.
*/
}
}
}
}
/* Process NATURAL keywords, and ON and USING clauses of joins.
*/
/* For every "*" that occurs in the column list, insert the names of
** all columns in all tables. And for every TABLE.* insert the names
** of all columns in TABLE. The parser inserted a special expression
** with the TK_ALL operator for each "*" that it found in the column list.
** The following code just has to locate the TK_ALL expressions and expand
** each one to the list of all columns in all tables.
**
** The first loop just checks to see if there are any "*" operators
** that need expanding.
*/
}
rc = 0;
/*
** If we get here it means the result set contains one or more "*"
** operators that need to be expanded. Loop through each expression
** in the result set and expand them one by one.
*/
struct ExprList_item *a = pEList->a;
/* This particular expression does not need to be expanded.
*/
a[k].pExpr = 0;
a[k].zName = 0;
}else{
/* This expression is a "*" or a "TABLE.*" and needs to be
** expanded. */
int tableSeen = 0; /* Set to 1 when TABLE matches */
char *zTName; /* text of name of TABLE */
}else{
zTName = 0;
}
}
continue;
}
tableSeen = 1;
/* In a NATURAL join, omit the join columns from the
** table on the right */
continue;
}
/* In a join with a USING clause, omit columns in the
** using clause from the table on the right. */
continue;
}
if( pRight==0 ) break;
if( pExpr==0 ) break;
}else{
}
}
}
if( !tableSeen ){
if( zTName ){
}else{
}
rc = 1;
}
}
}
}
return rc;
}
/*
** This routine recursively unlinks the Select.pSrc.a[].pTab pointers
** in a select structure. It just sets the pointers to NULL. This
** routine is recursive in the sense that if the Select.pSrc.a[].pSelect
** pointer is not NULL, this routine is called recursively on that pointer.
**
** This routine is called on the Select structure that defines a
** VIEW in order to undo any bindings to tables. This is necessary
** because those tables might be DROPed by a subsequent SQL command.
** If the bindings are not removed, then the Select.pSrc->a[].pTab field
** will be left pointing to a deallocated Table structure after the
** DROP and a coredump will occur the next time the VIEW is used.
*/
void sqliteSelectUnbind(Select *p){
int i;
if( p==0 ) return;
if( pTab->isTransient ){
sqliteDeleteTable(0, pTab);
}
}
}
}
}
/*
** This routine associates entries in an ORDER BY expression list with
** columns in a result. For each ORDER BY expression, the opcode of
** the top-level node is changed to TK_COLUMN and the iColumn value of
** the top-level node is filled in with column number and the iTable
** value of the top-level node is filled with iTable parameter.
**
** If there are prior SELECT clauses, they are processed first. A match
** in an earlier SELECT takes precedence over a later SELECT.
**
** Any entry that does not match is flagged as an error. The number
** of errors is returned.
**
** This routine does NOT correctly initialize the Expr.dataType field
** of the ORDER BY expressions. The multiSelectSortOrder() routine
** must be called to do that after the individual select statements
** have all been analyzed. This routine is unable to compute Expr.dataType
** because it must be called before the individual select statements
** have been analyzed.
*/
static int matchOrderbyToColumn(
int iTable, /* Insert this value in iTable */
int mustComplete /* If TRUE all ORDER BYs must match */
){
int nErr = 0;
int i, j;
if( mustComplete ){
}
return 1;
}
return 1;
}
}
int iCol = -1;
"ORDER BY position %d should be between 1 and %d",
nErr++;
break;
}
if( !mustComplete ) continue;
iCol--;
}
iCol = j;
}
}
iCol = j;
}
}
if( iCol>=0 ){
}
if( iCol<0 && mustComplete ){
"ORDER BY term number %d does not match any result column", i+1);
nErr++;
break;
}
}
return nErr;
}
/*
** Get a VDBE for the given parser context. Create a new one if necessary.
** If an error occurs, return NULL and leave a message in pParse.
*/
if( v==0 ){
}
return v;
}
/*
** This routine sets the Expr.dataType field on all elements of
** the pOrderBy expression list. The pOrderBy list will have been
** set up by matchOrderbyToColumn(). Hence each expression has
** a TK_COLUMN as its root node. The Expr.iColumn refers to a
** column in the result set. The datatype is set to SQLITE_SO_TEXT
** if the corresponding column in p and every SELECT to the left of
** p has a datatype of SQLITE_SO_TEXT. If the cooressponding column
** in p or any of the left SELECTs is SQLITE_SO_NUM, then the datatype
** of the order-by expression is set to SQLITE_SO_NUM.
**
** Examples:
**
** CREATE TABLE one(a INTEGER, b TEXT);
** CREATE TABLE two(c VARCHAR(5), d FLOAT);
**
** SELECT b, b FROM one UNION SELECT d, c FROM two ORDER BY 1, 2;
**
** The primary sort key will use SQLITE_SO_NUM because the "d" in
** the second SELECT is numeric. The 1st column of the first SELECT
** is text but that does not matter because a numeric always overrides
** a text.
**
** The secondary key will use the SQLITE_SO_TEXT sort order because
** both the (second) "b" in the first SELECT and the "c" in the second
** SELECT have a datatype of text.
*/
int i;
if( pOrderBy==0 ) return;
if( p==0 ){
}
return;
}
}
}
}
/*
** Compute the iLimit and iOffset fields of the SELECT based on the
** nLimit and nOffset fields. nLimit and nOffset hold the integers
** that appear in the original SQL statement after the LIMIT and OFFSET
** keywords. Or that hold -1 and 0 if those keywords are omitted.
** iLimit and iOffset are the integer memory register numbers for
** counters used to compute the limit and offset. If there is no
**
** This routine changes the values if iLimit and iOffset only if
** a limit or offset is defined by nLimit and nOffset. iLimit and
** iOffset should have been preset to appropriate default values
** (usually but not always -1) prior to calling this routine.
** Only if nLimit>=0 or nOffset>0 do the limit registers get
** redefined. The UNION ALL operator uses this property to force
** the reuse of the same limit and offset registers across multiple
** SELECT statements.
*/
/*
** If the comparison is p->nLimit>0 then "LIMIT 0" shows
** all rows. It is the same as no limit. If the comparision is
** p->nLimit>=0 then "LIMIT 0" show no rows at all.
** "LIMIT -1" always shows all rows. There is some
** contraversy about what the correct behavior should be.
** The current implementation interprets "LIMIT 0" to mean
** no rows.
*/
if( p->nLimit>=0 ){
if( v==0 ) return;
}
if( p->nOffset>0 ){
if( v==0 ) return;
}
}
/*
** This routine is called to process a query that is really the union
** or intersection of two or more separate queries.
**
** "p" points to the right-most of the two queries. the query on the
** left is p->pPrior. The left query could also be a compound query
** in which case this routine will be called recursively.
**
** The results of the total query are to be written into a destination
** of type eDest with parameter iParm.
**
** Example 1: Consider a three-way compound SQL statement.
**
** SELECT a FROM t1 UNION SELECT b FROM t2 UNION SELECT c FROM t3
**
** This statement is parsed up as follows:
**
** SELECT c FROM t3
** |
** `-----> SELECT b FROM t2
** |
** `------> SELECT a FROM t1
**
** The arrows in the diagram above represent the Select.pPrior pointer.
** So if this routine is called with p equal to the t3 query, then
** pPrior will be the t2 query. p->op will be TK_UNION in this case.
**
** Notice that because of the way SQLite parses compound SELECTs, the
** individual selects always group from left to right.
*/
int rc; /* Success code from a subroutine */
Vdbe *v; /* Generate code to this VDBE */
/* Make sure there is no ORDER BY or LIMIT clause on prior SELECTs. Only
** the last SELECT in the series may have an ORDER BY or LIMIT.
*/
if( p==0 || p->pPrior==0 ) return 1;
selectOpName(p->op));
return 1;
}
selectOpName(p->op));
return 1;
}
/* Make sure we have a valid query engine. If not, create a new one.
*/
v = sqliteGetVdbe(pParse);
if( v==0 ) return 1;
/* Create the destination temporary table if necessary
*/
if( eDest==SRT_TempTable ){
}
/* Generate code for the left and right SELECT statements.
*/
switch( p->op ){
case TK_ALL: {
if( p->pOrderBy==0 ){
p->pPrior = 0;
p->nLimit = -1;
p->nOffset = 0;
break;
}
/* For UNION ALL ... ORDER BY fall through to the next case */
}
case TK_EXCEPT:
case TK_UNION: {
int unionTab; /* Cursor number of the temporary table holding result */
int op; /* One of the SRT_ operations to apply to self */
int priorOp; /* The SRT_ operation to apply to prior selects */
/* We can reuse a temporary table generated by a SELECT to our
** right.
*/
}else{
/* We will need to create our own temporary table to hold the
** intermediate results.
*/
if( p->pOrderBy
return 1;
}
}else{
}
}
/* Code the SELECT statements to our left
*/
/* Code the current SELECT statement
*/
switch( p->op ){
}
p->pPrior = 0;
p->pOrderBy = 0;
p->nLimit = -1;
p->nOffset = 0;
/* Convert the data in the temporary table into whatever form
** it is that we currently need.
*/
if( eDest==SRT_Callback ){
}
iBreak = sqliteVdbeMakeLabel(v);
iCont = sqliteVdbeMakeLabel(v);
iStart = sqliteVdbeCurrentAddr(v);
multiSelectSortOrder(p, p->pOrderBy);
if( rc ) return 1;
if( p->pOrderBy ){
}
}
break;
}
case TK_INTERSECT: {
/* INTERSECT is different from the others since it requires
** two temporary tables. Hence it has its own case. Begin
** by allocating the tables we will need.
*/
return 1;
}
/* Code the SELECTs to our left into temporary table "tab1".
*/
/* Code the current SELECT into temporary table "tab2"
*/
p->pPrior = 0;
p->nLimit = -1;
p->nOffset = 0;
/* Generate code to take the intersection of the two temporary
** tables.
*/
if( eDest==SRT_Callback ){
}
iBreak = sqliteVdbeMakeLabel(v);
iCont = sqliteVdbeMakeLabel(v);
multiSelectSortOrder(p, p->pOrderBy);
if( rc ) return 1;
if( p->pOrderBy ){
}
break;
}
}
return 1;
}
return 0;
}
/*
** Scan through the expression pExpr. Replace every reference to
** a column in table number iTable with a copy of the iColumn-th
** entry in pEList. (But leave references to the ROWID column
** unchanged.)
**
** This routine is part of the flattening procedure. A subquery
** whose result set is defined by pEList appears as entry in the
** FROM clause of a SELECT such that the VDBE cursor assigned to that
** FORM clause entry is iTable. This routine make the necessary
** changes to pExpr so that it refers directly to the source table
** of the subquery rather the result set of the subquery.
*/
if( pExpr==0 ) return;
}else{
}
}else{
}
}
static void
int i;
if( pList==0 ) return;
}
}
/*
** This routine attempts to flatten subqueries in order to speed
** execution. It returns 1 if it makes changes and 0 if no flattening
** occurs.
**
** To understand the concept of flattening, consider the following
** query:
**
** SELECT a FROM (SELECT x+y AS a FROM t1 WHERE z<100) WHERE a>5
**
** The default way of implementing this query is to execute the
** subquery first and store the results in a temporary table, then
** run the outer query on that temporary table. This requires two
** passes over the data. Furthermore, because the temporary table
** has no indices, the WHERE clause on the outer query cannot be
** optimized.
**
** This routine attempts to rewrite queries such as the above into
** a single flat select, like this:
**
** SELECT x+y AS a FROM t1 WHERE z<100 AND a>5
**
** The code generated for this simpification gives the same result
** but only has to scan the data once. And because indices might
** exist on the table t1, a complete scan of the data might be
** avoided.
**
** Flattening is only attempted if all of the following are true:
**
** (1) The subquery and the outer query do not both use aggregates.
**
** (2) The subquery is not an aggregate or the outer query is not a join.
**
** (3) The subquery is not the right operand of a left outer join, or
** the subquery is not itself a join. (Ticket #306)
**
** (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.
**
** (8) The subquery does not use LIMIT or the outer query is not a join.
**
** (9) The subquery does not use LIMIT or the outer query does not use
** aggregates.
**
** (10) The subquery does not use aggregates or the outer query does not
** use LIMIT.
**
** (11) The subquery and the outer query do not both have ORDER BY clauses.
**
** (12) The subquery is not the right term of a LEFT OUTER JOIN or the
** subquery has no WHERE clause. (added by ticket #350)
**
** 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.
**
** All of the expression analysis must occur on both the outer query and
** the subquery before this routine runs.
*/
static int flattenSubquery(
Select *p, /* The parent or outer SELECT statement */
int iFrom, /* Index in p->pSrc->a[] of the inner subquery */
int isAgg, /* True if outer SELECT uses aggregate functions */
int subqueryIsAgg /* True if the subquery uses aggregate functions */
){
int iParent; /* VDBE cursor number of the pSub result set temp table */
int i;
/* Check to see if flattening is permitted. Return 0 if not.
*/
if( p==0 ) return 0;
if( isAgg && subqueryIsAgg ) return 0;
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)
**
** If we flatten the above, we would get
**
** (t1 LEFT OUTER JOIN t2) JOIN t3
**
** which is not at all the same thing.
*/
return 0;
}
/* Restriction 12: If the subquery is the right operand of a left outer
** join, make sure the subquery has no WHERE clause.
** An examples of why this is not allowed:
**
** t1 LEFT OUTER JOIN (SELECT * FROM t2 WHERE t2.x>0)
**
** If we flatten the above, we would get
**
** (t1 LEFT OUTER JOIN t2) WHERE t2.x>0
**
** But the t2.x>0 test will always fail on a NULL row of t2, which
** effectively converts the OUTER JOIN into an INNER JOIN.
*/
return 0;
}
/* If we reach this point, it means flattening is permitted for the
** iFrom-th entry of the FROM clause in the outer query.
*/
/* Move all of the FROM elements of the subquery into the
** the FROM clause of the outer query. Before doing this, remember
** the cursor number for the original outer query FROM element in
** iParent. The iParent cursor will never be used. Subsequent code
** will scan expressions looking for iParent references and replace
** those references with expressions that resolve to the subquery FROM
** elements we are now copying in.
*/
{
}
if( nSubSrc>1 ){
for(i=1; i<nSubSrc; i++){
}
}
}
for(i=0; i<nSubSrc; i++){
}
}
/* Now begin substituting subquery result set expressions for
** references to the iParent in the outer query.
**
** Example:
**
** SELECT a+5, b*10 FROM (SELECT x*3 AS a, y+10 AS b FROM t1) WHERE a>b;
** \ \_____________ subquery __________/ /
** \_____________________ outer query ______________________________/
**
** We look at every expression in the outer query and every place we see
** "a" we substitute "x*3" and every place we see "b" we substitute "y+10".
*/
}
}
if( isAgg ){
}
}else if( p->pOrderBy ){
}
}else{
pWhere = 0;
}
if( subqueryIsAgg ){
if( p->pHaving ){
}else{
}
}
}else if( p->pWhere==0 ){
}else{
if( pWhere ){
}
}
/* The flattened query is distinct if either the inner or the
** outer query is distinct.
*/
/* Transfer the limit expression from the subquery to the outer
** query.
*/
if( p->nLimit<0 ){
}
}
/* Finially, delete what is left of the subquery and return
** success.
*/
return 1;
}
/*
** Analyze the SELECT statement passed in as an argument to see if it
** is a simple min() or max() query. If it is and this query can be
** satisfied using a single seek to the beginning or end of an index,
** then generate the code for this SELECT and return 1. If this is not a
** simple min() or max() query, then return 0;
**
** A simply min() or max() query looks like this:
**
** SELECT min(a) FROM table;
** SELECT max(a) FROM table;
**
** The query may have only a single table in its FROM argument. There
** can be no GROUP BY or HAVING or WHERE clauses. The result set must
** be the min() or max() of a single column of the table. The column
** in the min() or max() function must be indexed.
**
** The parameters to this routine are the same as for sqliteSelect().
** See the header comment on that routine for additional information.
*/
int iCol;
int base;
Vdbe *v;
int seekOp;
int cont;
struct ExprList_item eListItem;
/* Check to see if this query is a simple min() or max() query. Return
** zero if it is not.
*/
}else{
return 0;
}
/* If we get to here, it means the query is of the correct form.
** Check to make sure we have an index and make pIdx point to the
** appropriate index. If the min() or max() is on an INTEGER PRIMARY
** key column, no index is necessary so set pIdx to NULL. If no
** usable index is found, return 0.
*/
if( iCol<0 ){
pIdx = 0;
}else{
}
if( pIdx==0 ) return 0;
}
/* Identify column types if we will be using the callback. This
** step is skipped if the output is going to a table or a memory cell.
** The column names have already been generated in the calling function.
*/
v = sqliteGetVdbe(pParse);
if( v==0 ) return 0;
if( eDest==SRT_Callback ){
}
/* If the output is destined for a temporary table, open that table.
*/
if( eDest==SRT_TempTable ){
}
/* Generating code to find the min or the max. Basically all we have
** to do is find the first or the last entry in the chosen index. If
** the min() or max() is on the INTEGER PRIMARY KEY, then find the first
** or last entry in the main table.
*/
}
cont = sqliteVdbeMakeLabel(v);
if( pIdx==0 ){
}else{
sqliteVdbeAddOp(v, OP_String, 0, 0);
sqliteVdbeAddOp(v, OP_IncrKey, 0, 0);
}
}
return 1;
}
/*
** Generate code for the given SELECT statement.
**
** The results are distributed in various ways depending on the
** value of eDest and iParm.
**
** eDest Value Result
** ------------ -------------------------------------------
** SRT_Callback Invoke the callback for each row of the result.
**
** SRT_Mem Store first result in memory cell iParm
**
** SRT_Set Store results as keys of a table with cursor iParm
**
** SRT_Union Store results as a key in a temporary table iParm
**
** SRT_Except Remove results from the temporary table iParm.
**
** SRT_Table Store results in temporary table iParm
**
** The table above is incomplete. Additional eDist value have be added
** since this comment was written. See the selectInnerLoop() function for
** a complete listing of the allowed values of eDest and their meanings.
**
** This routine returns the number of errors. If any errors are
** encountered, then an appropriate error message is left in
** pParse->zErrMsg.
**
** This routine does NOT free the Select structure passed in. The
** calling function needs to do that.
**
** The pParent, parentTab, and *pParentAgg fields are filled in if this
** SELECT is a subquery. This routine may try to combine this SELECT
** with its parent to form a single flat query. In so doing, it might
** change the parent query from a non-aggregate to an aggregate query.
** For that reason, the pParentAgg flag is passed as a pointer, so it
** can be changed.
**
** Example 1: The meaning of the pParent parameter.
**
** SELECT * FROM t1 JOIN (SELECT x, count(*) FROM t2) JOIN t3;
** \ \_______ subquery _______/ /
** \ /
** \____________________ outer query ___________________/
**
** This routine is called for the outer query first. For that call,
** pParent will be NULL. During the processing of the outer query, this
** routine is called recursively to handle the subquery. For the recursive
** call, pParent will point to the outer query. Because the subquery is
** the second element in a three-way join, the parentTab parameter will
** be 1 (the 2nd value of a 0-indexed array.)
*/
int sqliteSelect(
Select *p, /* The SELECT statement being coded. */
int eDest, /* How to dispose of the results */
int iParm, /* A parameter used by the eDest disposal method */
int parentTab, /* Index in pParent->pSrc of this query */
int *pParentAgg /* True if pParent uses aggregate functions */
){
int i;
Vdbe *v;
int isAgg = 0; /* True for select lists like "count(*)" */
int isDistinct; /* True if the DISTINCT keyword is present */
int distinct; /* Table to use for the distinct set */
/* If there is are a sequence of queries, do the earlier ones first.
*/
if( p->pPrior ){
}
/* Make local copies of the parameters for this query.
*/
isDistinct = p->isDistinct;
/* Allocate VDBE cursors for each table in the FROM clause
*/
/*
** Do not even attempt to generate any code if we have already seen
** errors before this routine starts.
*/
/* Expand any "*" terms in the result set. (For example the "*" in
** "SELECT * FROM t1") The fillInColumnlist() routine also does some
** other housekeeping - see the header comment for details.
*/
if( fillInColumnList(pParse, p) ){
goto select_end;
}
if( pEList==0 ) goto select_end;
/* If writing to memory or generating a set
** only a single column may be output.
*/
"a SELECT that is part of an expression");
goto select_end;
}
/* ORDER BY is ignored for some destinations.
*/
switch( eDest ){
case SRT_Union:
case SRT_Except:
case SRT_Discard:
pOrderBy = 0;
break;
default:
break;
}
/* At this point, we should have allocated all the cursors that we
** need to handle subquerys and temporary tables.
**
** Resolve the column names and do a semantics check on all the expressions.
*/
goto select_end;
}
goto select_end;
}
}
if( pWhere ){
goto select_end;
}
goto select_end;
}
}
if( pHaving ){
if( pGroupBy==0 ){
goto select_end;
}
goto select_end;
}
goto select_end;
}
}
if( pOrderBy ){
int iCol;
}
goto select_end;
}
goto select_end;
}
if( sqliteExprIsConstant(pE) ){
"ORDER BY terms must not be non-integer constants");
goto select_end;
"ORDER BY column number %d out of range - should be "
goto select_end;
}
}
}
}
if( pGroupBy ){
int iCol;
}
goto select_end;
}
goto select_end;
}
if( sqliteExprIsConstant(pE) ){
"GROUP BY terms must not be non-integer constants");
goto select_end;
"GROUP BY column number %d out of range - should be "
goto select_end;
}
}
}
}
/* Begin generating code.
*/
v = sqliteGetVdbe(pParse);
if( v==0 ) goto select_end;
/* Identify column names if we will be using them in a callback. This
** step is skipped if the output is going to some other destination.
*/
if( eDest==SRT_Callback ){
}
/* Generate code for all sub-queries in the FROM clause
*/
const char *zSavedAuthContext;
int needRestoreContext;
needRestoreContext = 1;
}else{
needRestoreContext = 0;
}
if( needRestoreContext ){
}
}
isDistinct = p->isDistinct;
}
/* Check for the special case of a min() or max() function by itself
** in the result set.
*/
rc = 0;
goto select_end;
}
/* Check to see if this is a subquery that can be "flattened" into its parent.
** If flattening is a possiblity, do so and return immediately.
*/
if( pParent && pParentAgg &&
return rc;
}
/* Set the limiter.
*/
/* Identify column types if we will be using a callback. This
** step is skipped if the output is going to a destination other
** than a callback.
**
** We have to do this separately from the creation of column names
** above because if the pTabList contains views then they will not
** have been resolved and we will not know the column types until
** now.
*/
if( eDest==SRT_Callback ){
}
/* If the output is destined for a temporary table, open that table.
*/
if( eDest==SRT_TempTable ){
}
/* Do an analysis of aggregate expressions.
*/
isAgg = 1;
goto select_end;
}
}
if( pGroupBy ){
goto select_end;
}
}
}
goto select_end;
}
if( pOrderBy ){
goto select_end;
}
}
}
}
/* Reset the aggregator
*/
if( isAgg ){
}
}
if( pGroupBy==0 ){
sqliteVdbeAddOp(v, OP_String, 0, 0);
sqliteVdbeAddOp(v, OP_AggFocus, 0, 0);
}
}
/* Initialize the memory cell to NULL
*/
sqliteVdbeAddOp(v, OP_String, 0, 0);
}
/* Open a temporary table to use for the distinct set.
*/
if( isDistinct ){
}else{
distinct = -1;
}
/* Begin the database scan
*/
if( pWInfo==0 ) goto select_end;
/* Use the standard inner loop if we are not dealing with
** aggregates
*/
if( !isAgg ){
goto select_end;
}
}
/* If we are dealing with aggregates, then do the special aggregate
** processing.
*/
else{
if( pGroupBy ){
int lbl1;
}
lbl1 = sqliteVdbeMakeLabel(v);
sqliteVdbeAddOp(v, OP_AggSet, 0, i);
}
}
int nExpr;
sqliteVdbeAddOp(v, OP_Integer, i, 0);
}
}
/* End the database scan loop.
*/
/* If we are processing aggregates, we need to set up a second loop
** over all of the aggregate values and process them.
*/
if( isAgg ){
int endagg = sqliteVdbeMakeLabel(v);
int startagg;
if( pHaving ){
}
goto select_end;
}
sqliteVdbeAddOp(v, OP_Noop, 0, 0);
}
/* If there is an ORDER BY clause, then we need to sort the results
** and send them to the callback one by one.
*/
if( pOrderBy ){
}
/* If this was a subquery, we have now converted the subquery into a
** temporary table. So delete the subquery structure from the parent
** to prevent this subquery from being evaluated again and to force the
** the use of the temporary table.
*/
if( pParent ){
}
/* The SELECT was successfully coded. Set the return code to 0
** to indicate no errors.
*/
rc = 0;
/* Control jumps to here if an error is encountered above, or upon
** successful coding of the SELECT.
*/
return rc;
}