DBD-SQLite2
view release on metacpan or search on metacpan
sqliteVdbeAddOp(v, OP_OpenTemp, tab2, 1);
sqliteVdbeAddOp(v, OP_KeyAsData, tab2, 1);
p->pPrior = 0;
nLimit = p->nLimit;
p->nLimit = -1;
nOffset = p->nOffset;
p->nOffset = 0;
rc = sqliteSelect(pParse, p, SRT_Union, tab2, 0, 0, 0);
p->pPrior = pPrior;
p->nLimit = nLimit;
p->nOffset = nOffset;
if( rc ) return rc;
/* Generate code to take the intersection of the two temporary
** tables.
*/
assert( p->pEList );
if( eDest==SRT_Callback ){
generateColumnNames(pParse, 0, p->pEList);
generateColumnTypes(pParse, p->pSrc, p->pEList);
}
iBreak = sqliteVdbeMakeLabel(v);
iCont = sqliteVdbeMakeLabel(v);
sqliteVdbeAddOp(v, OP_Rewind, tab1, iBreak);
computeLimitRegisters(pParse, p);
iStart = sqliteVdbeAddOp(v, OP_FullKey, tab1, 0);
sqliteVdbeAddOp(v, OP_NotFound, tab2, iCont);
multiSelectSortOrder(p, p->pOrderBy);
rc = selectInnerLoop(pParse, p, p->pEList, tab1, p->pEList->nExpr,
p->pOrderBy, -1, eDest, iParm,
iCont, iBreak);
if( rc ) return 1;
sqliteVdbeResolveLabel(v, iCont);
sqliteVdbeAddOp(v, OP_Next, tab1, iStart);
sqliteVdbeResolveLabel(v, iBreak);
sqliteVdbeAddOp(v, OP_Close, tab2, 0);
sqliteVdbeAddOp(v, OP_Close, tab1, 0);
if( p->pOrderBy ){
generateSortTail(p, v, p->pEList->nExpr, eDest, iParm);
}
break;
}
}
assert( p->pEList && pPrior->pEList );
if( p->pEList->nExpr!=pPrior->pEList->nExpr ){
sqliteErrorMsg(pParse, "SELECTs to the left and right of %s"
" do not have the same number of result columns", selectOpName(p->op));
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.
*/
static void substExprList(ExprList*,int,ExprList*); /* Forward Decl */
static void substExpr(Expr *pExpr, int iTable, ExprList *pEList){
if( pExpr==0 ) return;
if( pExpr->op==TK_COLUMN && pExpr->iTable==iTable ){
if( pExpr->iColumn<0 ){
pExpr->op = TK_NULL;
}else{
Expr *pNew;
assert( pEList!=0 && pExpr->iColumn<pEList->nExpr );
assert( pExpr->pLeft==0 && pExpr->pRight==0 && pExpr->pList==0 );
pNew = pEList->a[pExpr->iColumn].pExpr;
assert( pNew!=0 );
pExpr->op = pNew->op;
pExpr->dataType = pNew->dataType;
assert( pExpr->pLeft==0 );
pExpr->pLeft = sqliteExprDup(pNew->pLeft);
assert( pExpr->pRight==0 );
pExpr->pRight = sqliteExprDup(pNew->pRight);
assert( pExpr->pList==0 );
pExpr->pList = sqliteExprListDup(pNew->pList);
pExpr->iTable = pNew->iTable;
pExpr->iColumn = pNew->iColumn;
pExpr->iAgg = pNew->iAgg;
sqliteTokenCopy(&pExpr->token, &pNew->token);
sqliteTokenCopy(&pExpr->span, &pNew->span);
}
}else{
substExpr(pExpr->pLeft, iTable, pEList);
substExpr(pExpr->pRight, iTable, pEList);
substExprList(pExpr->pList, iTable, pEList);
}
}
static void
substExprList(ExprList *pList, int iTable, ExprList *pEList){
int i;
if( pList==0 ) return;
for(i=0; i<pList->nExpr; i++){
substExpr(pList->a[i].pExpr, iTable, pEList);
}
}
/*
** 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
** 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(
Parse *pParse, /* The parsing context */
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 */
){
Select *pSub; /* The inner query or "subquery" */
SrcList *pSrc; /* The FROM clause of the outer query */
SrcList *pSubSrc; /* The FROM clause of the subquery */
ExprList *pList; /* The result set of the outer query */
int iParent; /* VDBE cursor number of the pSub result set temp table */
int i;
Expr *pWhere;
/* Check to see if flattening is permitted. Return 0 if not.
*/
if( p==0 ) return 0;
pSrc = p->pSrc;
assert( pSrc && iFrom>=0 && iFrom<pSrc->nSrc );
pSub = pSrc->a[iFrom].pSelect;
assert( pSub!=0 );
if( isAgg && subqueryIsAgg ) return 0;
if( subqueryIsAgg && pSrc->nSrc>1 ) return 0;
pSubSrc = pSub->pSrc;
assert( pSubSrc );
if( pSubSrc->nSrc==0 ) return 0;
if( (pSub->isDistinct || pSub->nLimit>=0) && (pSrc->nSrc>1 || isAgg) ){
return 0;
}
if( (p->isDistinct || p->nLimit>=0) && subqueryIsAgg ) return 0;
if( p->pOrderBy && pSub->pOrderBy ) 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.
*/
if( pSubSrc->nSrc>1 && iFrom>0 && (pSrc->a[iFrom-1].jointype & JT_OUTER)!=0 ){
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.
*/
if( iFrom>0 && (pSrc->a[iFrom-1].jointype & JT_OUTER)!=0
&& pSub->pWhere!=0 ){
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.
*/
iParent = pSrc->a[iFrom].iCursor;
{
int nSubSrc = pSubSrc->nSrc;
int jointype = pSrc->a[iFrom].jointype;
if( pSrc->a[iFrom].pTab && pSrc->a[iFrom].pTab->isTransient ){
sqliteDeleteTable(0, pSrc->a[iFrom].pTab);
}
sqliteFree(pSrc->a[iFrom].zDatabase);
sqliteFree(pSrc->a[iFrom].zName);
sqliteFree(pSrc->a[iFrom].zAlias);
if( nSubSrc>1 ){
int extra = nSubSrc - 1;
for(i=1; i<nSubSrc; i++){
pSrc = sqliteSrcListAppend(pSrc, 0, 0);
}
p->pSrc = pSrc;
for(i=pSrc->nSrc-1; i-extra>=iFrom; i--){
pSrc->a[i] = pSrc->a[i-extra];
}
}
for(i=0; i<nSubSrc; i++){
pSrc->a[i+iFrom] = pSubSrc->a[i];
memset(&pSubSrc->a[i], 0, sizeof(pSubSrc->a[i]));
}
pSrc->a[iFrom+nSubSrc-1].jointype = jointype;
}
/* 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".
*/
substExprList(p->pEList, iParent, pSub->pEList);
pList = p->pEList;
for(i=0; i<pList->nExpr; i++){
Expr *pExpr;
if( pList->a[i].zName==0 && (pExpr = pList->a[i].pExpr)->span.z!=0 ){
pList->a[i].zName = sqliteStrNDup(pExpr->span.z, pExpr->span.n);
}
}
if( isAgg ){
substExprList(p->pGroupBy, iParent, pSub->pEList);
substExpr(p->pHaving, iParent, pSub->pEList);
}
if( pSub->pOrderBy ){
assert( p->pOrderBy==0 );
p->pOrderBy = pSub->pOrderBy;
pSub->pOrderBy = 0;
}
/* If the output is destined for a temporary table, open that table.
*/
if( eDest==SRT_TempTable ){
sqliteVdbeAddOp(v, OP_OpenTemp, iParm, 0);
}
/* 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.
*/
sqliteCodeVerifySchema(pParse, pTab->iDb);
base = pSrc->a[0].iCursor;
computeLimitRegisters(pParse, p);
if( pSrc->a[0].pSelect==0 ){
sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0);
sqliteVdbeOp3(v, OP_OpenRead, base, pTab->tnum, pTab->zName, 0);
}
cont = sqliteVdbeMakeLabel(v);
if( pIdx==0 ){
sqliteVdbeAddOp(v, seekOp, base, 0);
}else{
sqliteVdbeAddOp(v, OP_Integer, pIdx->iDb, 0);
sqliteVdbeOp3(v, OP_OpenRead, base+1, pIdx->tnum, pIdx->zName, P3_STATIC);
if( seekOp==OP_Rewind ){
sqliteVdbeAddOp(v, OP_String, 0, 0);
sqliteVdbeAddOp(v, OP_MakeKey, 1, 0);
sqliteVdbeAddOp(v, OP_IncrKey, 0, 0);
seekOp = OP_MoveTo;
}
sqliteVdbeAddOp(v, seekOp, base+1, 0);
sqliteVdbeAddOp(v, OP_IdxRecno, base+1, 0);
sqliteVdbeAddOp(v, OP_Close, base+1, 0);
sqliteVdbeAddOp(v, OP_MoveTo, base, 0);
}
eList.nExpr = 1;
memset(&eListItem, 0, sizeof(eListItem));
eList.a = &eListItem;
eList.a[0].pExpr = pExpr;
selectInnerLoop(pParse, p, &eList, 0, 0, 0, -1, eDest, iParm, cont, cont);
sqliteVdbeResolveLabel(v, cont);
sqliteVdbeAddOp(v, OP_Close, base, 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(
Parse *pParse, /* The parser context */
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 */
Select *pParent, /* Another SELECT for which this is a sub-query */
int parentTab, /* Index in pParent->pSrc of this query */
int *pParentAgg /* True if pParent uses aggregate functions */
){
int i;
WhereInfo *pWInfo;
Vdbe *v;
int isAgg = 0; /* True for select lists like "count(*)" */
ExprList *pEList; /* List of columns to extract. */
SrcList *pTabList; /* List of tables to select from */
Expr *pWhere; /* The WHERE clause. May be NULL */
ExprList *pOrderBy; /* The ORDER BY clause. May be NULL */
ExprList *pGroupBy; /* The GROUP BY clause. May be NULL */
Expr *pHaving; /* The HAVING clause. May be NULL */
int isDistinct; /* True if the DISTINCT keyword is present */
int distinct; /* Table to use for the distinct set */
int rc = 1; /* Value to return from this function */
if( sqlite_malloc_failed || pParse->nErr || p==0 ) return 1;
if( sqliteAuthCheck(pParse, SQLITE_SELECT, 0, 0, 0) ) return 1;
/* If there is are a sequence of queries, do the earlier ones first.
*/
if( p->pPrior ){
return multiSelect(pParse, p, eDest, iParm);
}
/* Make local copies of the parameters for this query.
*/
pTabList = p->pSrc;
pWhere = p->pWhere;
pOrderBy = p->pOrderBy;
pGroupBy = p->pGroupBy;
pHaving = p->pHaving;
isDistinct = p->isDistinct;
/* Allocate VDBE cursors for each table in the FROM clause
*/
sqliteSrcListAssignCursors(pParse, pTabList);
/*
** Do not even attempt to generate any code if we have already seen
** errors before this routine starts.
*/
if( pParse->nErr>0 ) goto select_end;
/* 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;
}
pWhere = p->pWhere;
pEList = p->pEList;
if( pEList==0 ) goto select_end;
/* If writing to memory or generating a set
** only a single column may be output.
*/
if( (eDest==SRT_Mem || eDest==SRT_Set) && pEList->nExpr>1 ){
sqliteErrorMsg(pParse, "only a single result allowed for "
"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.
*/
for(i=0; i<pEList->nExpr; i++){
if( sqliteExprResolveIds(pParse, pTabList, 0, pEList->a[i].pExpr) ){
goto select_end;
}
if( sqliteExprCheck(pParse, pEList->a[i].pExpr, 1, &isAgg) ){
goto select_end;
}
}
if( pWhere ){
if( sqliteExprResolveIds(pParse, pTabList, pEList, pWhere) ){
goto select_end;
}
if( sqliteExprCheck(pParse, pWhere, 0, 0) ){
goto select_end;
}
}
if( pHaving ){
if( pGroupBy==0 ){
sqliteErrorMsg(pParse, "a GROUP BY clause is required before HAVING");
goto select_end;
}
if( sqliteExprResolveIds(pParse, pTabList, pEList, pHaving) ){
goto select_end;
}
if( sqliteExprCheck(pParse, pHaving, 1, &isAgg) ){
goto select_end;
}
}
if( pOrderBy ){
for(i=0; i<pOrderBy->nExpr; i++){
int iCol;
Expr *pE = pOrderBy->a[i].pExpr;
if( sqliteExprIsInteger(pE, &iCol) && iCol>0 && iCol<=pEList->nExpr ){
sqliteExprDelete(pE);
pE = pOrderBy->a[i].pExpr = sqliteExprDup(pEList->a[iCol-1].pExpr);
}
if( sqliteExprResolveIds(pParse, pTabList, pEList, pE) ){
goto select_end;
}
if( sqliteExprCheck(pParse, pE, isAgg, 0) ){
goto select_end;
}
if( sqliteExprIsConstant(pE) ){
if( sqliteExprIsInteger(pE, &iCol)==0 ){
sqliteErrorMsg(pParse,
"ORDER BY terms must not be non-integer constants");
goto select_end;
}else if( iCol<=0 || iCol>pEList->nExpr ){
sqliteErrorMsg(pParse,
"ORDER BY column number %d out of range - should be "
"between 1 and %d", iCol, pEList->nExpr);
goto select_end;
}
}
}
}
( run in 0.622 second using v1.01-cache-2.11-cpan-13bb782fe5a )