** 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(  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;  }else if( p->pOrderBy ){    substExprList(p->pOrderBy, iParent, pSub->pEList);  }  if( pSub->pWhere ){    pWhere = sqliteExprDup(pSub->pWhere);  }else{    pWhere = 0;  }  if( subqueryIsAgg ){    assert( p->pHaving==0 );    p->pHaving = p->pWhere;    p->pWhere = pWhere;    substExpr(p->pHaving, iParent, pSub->pEList);    if( pSub->pHaving ){      Expr *pHaving = sqliteExprDup(pSub->pHaving);      if( p->pHaving ){        p->pHaving = sqliteExpr(TK_AND, p->pHaving, pHaving, 0);      }else{        p->pHaving = pHaving;      }    }    assert( p->pGroupBy==0 );    p->pGroupBy = sqliteExprListDup(pSub->pGroupBy);  }else if( p->pWhere==0 ){    p->pWhere = pWhere;  }else{    substExpr(p->pWhere, iParent, pSub->pEList);    if( pWhere ){      p->pWhere = sqliteExpr(TK_AND, p->pWhere, pWhere, 0);    }  }  /* The flattened query is distinct if either the inner or the  ** outer query is distinct.   */  p->isDistinct = p->isDistinct || pSub->isDistinct;  /* Transfer the limit expression from the subquery to the outer  ** query.  */  if( pSub->nLimit>=0 ){    if( p->nLimit<0 ){      p->nLimit = pSub->nLimit;    }else if( p->nLimit+p->nOffset > pSub->nLimit+pSub->nOffset ){      p->nLimit = pSub->nLimit + pSub->nOffset - p->nOffset;    }  }  p->nOffset += pSub->nOffset;  /* Finially, delete what is left of the subquery and return  ** success.  */  sqliteSelectDelete(pSub);  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.*/static int simpleMinMaxQuery(Parse *pParse, Select *p, int eDest, int iParm){  Expr *pExpr;  int iCol;  Table *pTab;  Index *pIdx;  int base;  Vdbe *v;  int seekOp;  int cont;  ExprList eList;  struct ExprList_item eListItem;  /* Check to see if this query is a simple min() or max() query.  Return  ** zero if it is  not.  */  if( p->pGroupBy || p->pHaving || p->pWhere ) return 0;  if( p->pSrc->nSrc!=1 ) return 0;  if( p->pEList->nExpr!=1 ) return 0;  pExpr = p->pEList->a[0].pExpr;  if( pExpr->op!=TK_AGG_FUNCTION ) return 0;  if( pExpr->pList==0 || pExpr->pList->nExpr!=1 ) return 0;  if( pExpr->token.n!=3 ) return 0;  if( sqliteStrNICmp(pExpr->token.z,"min",3)==0 ){    seekOp = OP_Rewind;  }else if( sqliteStrNICmp(pExpr->token.z,"max",3)==0 ){    seekOp = OP_Last;  }else{    return 0;  }  pExpr = pExpr->pList->a[0].pExpr;  if( pExpr->op!=TK_COLUMN ) return 0;  iCol = pExpr->iColumn;  pTab = p->pSrc->a[0].pTab;  /* 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{    for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){      assert( pIdx->nColumn>=1 );      if( pIdx->aiColumn[0]==iCol ) break;    }    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 ){    generateColumnTypes(pParse, p->pSrc, p->pEList);  }  /* 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 = p->pSrc->a[0].iCursor;  computeLimitRegisters(pParse, p);  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);    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** pPars