SQLite

    sqlite3SelectDelete(db, pQuery);
    sqlite3DbFree(db, zName);
    pNew = pWith;
  }else{
    pNew->a[pNew->nCte].pSelect = pQuery;
    pNew->a[pNew->nCte].pCols = pArglist;
    pNew->a[pNew->nCte].zName = zName;
    pNew->a[pNew->nCte].zCteErr = 0;
    pNew->nCte++;
  }

  return pNew;
}

/*

            testcase( pDef->funcFlags & OPFLAG_LENGTHARG );
            pFarg->a[0].pExpr->op2 = 
                  pDef->funcFlags & (OPFLAG_LENGTHARG|OPFLAG_TYPEOFARG);
          }
        }

        sqlite3ExprCachePush(pParse);     /* Ticket 2ea2425d34be */
        sqlite3ExprCodeExprList(pParse, pFarg, r1, 0,
                                SQLITE_ECEL_DUP|SQLITE_ECEL_FACTOR);
        sqlite3ExprCachePop(pParse);      /* Ticket 2ea2425d34be */
      }else{
        r1 = 0;
      }
#ifndef SQLITE_OMIT_VIRTUALTABLE
      /* Possibly overload the function if the first argument is

** The SQLITE_ECEL_FACTOR argument allows constant arguments to be
** factored out into initialization code.
*/
int sqlite3ExprCodeExprList(
  Parse *pParse,     /* Parsing context */
  ExprList *pList,   /* The expression list to be coded */
  int target,        /* Where to write results */
  int srcReg,        /* Source registers if SQLITE_ECEL_REF */
  u8 flags           /* SQLITE_ECEL_* flags */
){
  struct ExprList_item *pItem;
  int i, j, n;
  u8 copyOp = (flags & SQLITE_ECEL_DUP) ? OP_Copy : OP_SCopy;
  Vdbe *v = pParse->pVdbe;
  assert( pList!=0 );
  assert( target>0 );
  assert( pParse->pVdbe!=0 );  /* Never gets this far otherwise */
  n = pList->nExpr;
  if( !ConstFactorOk(pParse) ) flags &= ~SQLITE_ECEL_FACTOR;
  for(pItem=pList->a, i=0; i<n; i++, pItem++){
    Expr *pExpr = pItem->pExpr;
    if( (flags & SQLITE_ECEL_REF)!=0 && (j = pList->a[i].u.x.iOrderByCol)>0 ){
      sqlite3VdbeAddOp2(v, copyOp, j+srcReg-1, target+i);
    }else if( (flags & SQLITE_ECEL_FACTOR)!=0 && sqlite3ExprIsConstant(pExpr) ){
      sqlite3ExprCodeAtInit(pParse, pExpr, target+i, 0);
    }else{
      int inReg = sqlite3ExprCodeTarget(pParse, pExpr, target+i);
      if( inReg!=target+i ){
        VdbeOp *pOp;

        if( copyOp==OP_Copy
         && (pOp=sqlite3VdbeGetOp(v, -1))->opcode==OP_Copy
         && pOp->p1+pOp->p3+1==inReg
         && pOp->p2+pOp->p3+1==target+i
        ){
          pOp->p3++;
        }else{

    }
    pIdx->zColAff[n] = 0;
  }
 
  return pIdx->zColAff;
}











/*
** Compute the affinity string for table pTab, if it has not already been
** computed.  As an optimization, omit trailing SQLITE_AFF_BLOB affinities.
**
** If the affinity exists (if it is no entirely SQLITE_AFF_BLOB values) and
** if iReg>0 then code an OP_Affinity opcode that will set the affinities
** for register iReg and following.  Or if affinities exists and iReg==0,

    **
    ** In cases like this, replace pExpr with a copy of the expression that
    ** forms the result set entry ("a+b" in the example) and return immediately.
    ** Note that the expression in the result set should have already been
    ** resolved by the time the WHERE clause is resolved.
    **
    ** The ability to use an output result-set column in the WHERE, GROUP BY,
    ** or HAVING clauses, or as part of a larger expression in the ORDER BY
    ** clause is not standard SQL.  This is a (goofy) SQLite extension, that
    ** is supported for backwards compatibility only. Hence, we issue a warning
    ** on sqlite3_log() whenever the capability is used.
    */
    if( (pEList = pNC->pEList)!=0
     && zTab==0
     && cnt==0
    ){
      for(j=0; j<pEList->nExpr; j++){

** through regData+nData-1 onto the sorter.
*/
static void pushOntoSorter(
  Parse *pParse,         /* Parser context */
  SortCtx *pSort,        /* Information about the ORDER BY clause */
  Select *pSelect,       /* The whole SELECT statement */
  int regData,           /* First register holding data to be sorted */
  int regOrigData,       /* First register holding data before packing */
  int nData,             /* Number of elements in the data array */
  int nPrefixReg         /* No. of reg prior to regData available for use */
){
  Vdbe *v = pParse->pVdbe;                         /* Stmt under construction */
  int bSeq = ((pSort->sortFlags & SORTFLAG_UseSorter)==0);
  int nExpr = pSort->pOrderBy->nExpr;              /* No. of ORDER BY terms */
  int nBase = nExpr + bSeq + nData;                /* Fields in sorter record */
  int regBase;                                     /* Regs for sorter record */
  int regRecord = ++pParse->nMem;                  /* Assembled sorter record */
  int nOBSat = pSort->nOBSat;                      /* ORDER BY terms to skip */
  int op;                            /* Opcode to add sorter record to sorter */

  assert( bSeq==0 || bSeq==1 );
  assert( nData==1 || regData==regOrigData );
  if( nPrefixReg ){
    assert( nPrefixReg==nExpr+bSeq );
    regBase = regData - nExpr - bSeq;
  }else{
    regBase = pParse->nMem + 1;
    pParse->nMem += nBase;
  }
  sqlite3ExprCodeExprList(pParse, pSort->pOrderBy, regBase, regOrigData,
                          SQLITE_ECEL_DUP|SQLITE_ECEL_REF);
  if( bSeq ){
    sqlite3VdbeAddOp2(v, OP_Sequence, pSort->iECursor, regBase+nExpr);
  }
  if( nPrefixReg==0 ){
    sqlite3ExprCodeMove(pParse, regData, regBase+nExpr+bSeq, nData);
  }

    */
    u8 ecelFlags;
    if( eDest==SRT_Mem || eDest==SRT_Output || eDest==SRT_Coroutine ){
      ecelFlags = SQLITE_ECEL_DUP;
    }else{
      ecelFlags = 0;
    }
    sqlite3ExprCodeExprList(pParse, pEList, regResult, 0, ecelFlags);
  }

  /* 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 ){

        sqlite3VdbeAddOp4Int(v, OP_Found, iParm+1, addr, r1, 0);
        VdbeCoverage(v);
        sqlite3VdbeAddOp2(v, OP_IdxInsert, iParm+1, r1);
        assert( pSort==0 );
      }
#endif
      if( pSort ){
        pushOntoSorter(pParse, pSort, p, r1+nPrefixReg,regResult,1,nPrefixReg);
      }else{
        int r2 = sqlite3GetTempReg(pParse);
        sqlite3VdbeAddOp2(v, OP_NewRowid, iParm, r2);
        sqlite3VdbeAddOp3(v, OP_Insert, iParm, r1, r2);
        sqlite3VdbeChangeP5(v, OPFLAG_APPEND);
        sqlite3ReleaseTempReg(pParse, r2);
      }

      pDest->affSdst =
                  sqlite3CompareAffinity(pEList->a[0].pExpr, pDest->affSdst);
      if( pSort ){
        /* At first glance you would think we could optimize out the
        ** ORDER BY in this case since the order of entries in the set
        ** does not matter.  But there might be a LIMIT clause, in which
        ** case the order does matter */
        pushOntoSorter(pParse, pSort, p, regResult, regResult, 1, nPrefixReg);
      }else{
        int r1 = sqlite3GetTempReg(pParse);
        sqlite3VdbeAddOp4(v, OP_MakeRecord, regResult,1,r1, &pDest->affSdst, 1);
        sqlite3ExprCacheAffinityChange(pParse, regResult, 1);
        sqlite3VdbeAddOp2(v, OP_IdxInsert, iParm, r1);
        sqlite3ReleaseTempReg(pParse, r1);
      }

    /* 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: {
      assert( nResultCol==1 );
      if( pSort ){
        pushOntoSorter(pParse, pSort, p, regResult, regResult, 1, nPrefixReg);
      }else{
        assert( regResult==iParm );
        /* The LIMIT clause will jump out of the loop for us */
      }
      break;
    }
#endif /* #ifndef SQLITE_OMIT_SUBQUERY */

    case SRT_Coroutine:       /* Send data to a co-routine */
    case SRT_Output: {        /* Return the results */
      testcase( eDest==SRT_Coroutine );
      testcase( eDest==SRT_Output );
      if( pSort ){
        pushOntoSorter(pParse, pSort, p, regResult, regResult, nResultCol,
                       nPrefixReg);
      }else if( eDest==SRT_Coroutine ){
        sqlite3VdbeAddOp1(v, OP_Yield, pDest->iSDParm);
      }else{
        sqlite3VdbeAddOp2(v, OP_ResultRow, regResult, nResultCol);
        sqlite3ExprCacheAffinityChange(pParse, regResult, nResultCol);
      }
      break;

    Table *pTab;
    ExprList *pEList;
    Select *pSel;
    Select *pLeft;                /* Left-most SELECT statement */
    int bMayRecursive;            /* True if compound joined by UNION [ALL] */
    With *pSavedWith;             /* Initial value of pParse->pWith */

    /* If pCte->zCteErr is non-NULL at this point, then this is an illegal
    ** recursive reference to CTE pCte. Leave an error in pParse and return
    ** early. If pCte->zCteErr is NULL, then this is not a recursive reference.
    ** In this case, proceed.  */
    if( pCte->zCteErr ){
      sqlite3ErrorMsg(pParse, pCte->zCteErr, pCte->zName);
      return SQLITE_ERROR;
    }

    assert( pFrom->pTab==0 );
    pFrom->pTab = pTab = sqlite3DbMallocZero(db, sizeof(Table));
    if( pTab==0 ) return WRC_Abort;
    pTab->nRef = 1;

      sqlite3ErrorMsg(
          pParse, "multiple references to recursive table: %s", pCte->zName
      );
      return SQLITE_ERROR;
    }
    assert( pTab->nRef==1 || ((pSel->selFlags&SF_Recursive) && pTab->nRef==2 ));

    pCte->zCteErr = "circular reference: %s";
    pSavedWith = pParse->pWith;
    pParse->pWith = pWith;
    sqlite3WalkSelect(pWalker, bMayRecursive ? pSel->pPrior : pSel);

    for(pLeft=pSel; pLeft->pPrior; pLeft=pLeft->pPrior);
    pEList = pLeft->pEList;
    if( pCte->pCols ){
      if( pEList && pEList->nExpr!=pCte->pCols->nExpr ){
        sqlite3ErrorMsg(pParse, "table %s has %d values for %d columns",
            pCte->zName, pEList->nExpr, pCte->pCols->nExpr
        );
        pParse->pWith = pSavedWith;
        return SQLITE_ERROR;
      }
      pEList = pCte->pCols;
    }

    sqlite3ColumnsFromExprList(pParse, pEList, &pTab->nCol, &pTab->aCol);
    if( bMayRecursive ){
      if( pSel->selFlags & SF_Recursive ){
        pCte->zCteErr = "multiple recursive references: %s";
      }else{
        pCte->zCteErr = "recursive reference in a subquery: %s";
      }
      sqlite3WalkSelect(pWalker, pSel);
    }
    pCte->zCteErr = 0;
    pParse->pWith = pSavedWith;
  }

  return SQLITE_OK;
}
#endif

    int addrNext = 0;
    int regAgg;
    ExprList *pList = pF->pExpr->x.pList;
    assert( !ExprHasProperty(pF->pExpr, EP_xIsSelect) );
    if( pList ){
      nArg = pList->nExpr;
      regAgg = sqlite3GetTempRange(pParse, nArg);
      sqlite3ExprCodeExprList(pParse, pList, regAgg, 0, SQLITE_ECEL_DUP);
    }else{
      nArg = 0;
      regAgg = 0;
    }
    if( pF->iDistinct>=0 ){
      addrNext = sqlite3VdbeMakeLabel(v);
      testcase( nArg==0 );  /* Error condition */

          if( sAggInfo.aCol[i].iSorterColumn>=j ){
            nCol++;
            j++;
          }
        }
        regBase = sqlite3GetTempRange(pParse, nCol);
        sqlite3ExprCacheClear(pParse);
        sqlite3ExprCodeExprList(pParse, pGroupBy, regBase, 0, 0);
        j = nGroupBy;
        for(i=0; i<sAggInfo.nColumn; i++){
          struct AggInfo_col *pCol = &sAggInfo.aCol[i];
          if( pCol->iSorterColumn>=j ){
            int r1 = j + regBase;
            int r2;

struct With {
  int nCte;                       /* Number of CTEs in the WITH clause */
  With *pOuter;                   /* Containing WITH clause, or NULL */
  struct Cte {                    /* For each CTE in the WITH clause.... */
    char *zName;                    /* Name of this CTE */
    ExprList *pCols;                /* List of explicit column names, or NULL */
    Select *pSelect;                /* The definition of this CTE */
    const char *zCteErr;            /* Error message for circular references */
  } a[1];
};

#ifdef SQLITE_DEBUG
/*
** An instance of the TreeView object is used for printing the content of
** data structures on sqlite3DebugPrintf() using a tree-like view.

void sqlite3ExprCacheAffinityChange(Parse*, int, int);
void sqlite3ExprCode(Parse*, Expr*, int);
void sqlite3ExprCodeFactorable(Parse*, Expr*, int);
void sqlite3ExprCodeAtInit(Parse*, Expr*, int, u8);
int sqlite3ExprCodeTemp(Parse*, Expr*, int*);
int sqlite3ExprCodeTarget(Parse*, Expr*, int);
void sqlite3ExprCodeAndCache(Parse*, Expr*, int);
int sqlite3ExprCodeExprList(Parse*, ExprList*, int, int, u8);
#define SQLITE_ECEL_DUP      0x01  /* Deep, not shallow copies */
#define SQLITE_ECEL_FACTOR   0x02  /* Factor out constant terms */
#define SQLITE_ECEL_REF      0x04  /* Use ExprList.u.x.iOrderByCol */
void sqlite3ExprIfTrue(Parse*, Expr*, int, int);
void sqlite3ExprIfFalse(Parse*, Expr*, int, int);
void sqlite3ExprIfFalseDup(Parse*, Expr*, int, int);
Table *sqlite3FindTable(sqlite3*,const char*, const char*);
Table *sqlite3LocateTable(Parse*,int isView,const char*, const char*);
Table *sqlite3LocateTableItem(Parse*,int isView,struct SrcList_item *);
Index *sqlite3FindIndex(sqlite3*,const char*, const char*);

  (u8)(((u32)(B)<(u32)0x80)?(*(A)=(unsigned char)(B)),1:\
  sqlite3PutVarint((A),(B)))
#define getVarint    sqlite3GetVarint
#define putVarint    sqlite3PutVarint


const char *sqlite3IndexAffinityStr(sqlite3*, Index*);

void sqlite3TableAffinity(Vdbe*, Table*, int);
char sqlite3CompareAffinity(Expr *pExpr, char aff2);
int sqlite3IndexAffinityOk(Expr *pExpr, char idx_affinity);
char sqlite3ExprAffinity(Expr *pExpr);
int sqlite3Atoi64(const char*, i64*, int, u8);
int sqlite3DecOrHexToI64(const char*, i64*);
void sqlite3ErrorWithMsg(sqlite3*, int, const char*,...);

/*
** Generate a human-readable description of a the Select object.
*/
void sqlite3TreeViewSelect(TreeView *pView, const Select *p, u8 moreToFollow){
  int n = 0;
  int cnt = 0;
  pView = sqlite3TreeViewPush(pView, moreToFollow);
  do{
    sqlite3TreeViewLine(pView, "SELECT%s%s (0x%p) selFlags=0x%x",
      ((p->selFlags & SF_Distinct) ? " DISTINCT" : ""),
      ((p->selFlags & SF_Aggregate) ? " agg_flag" : ""), p, p->selFlags
    );
    if( cnt++ ) sqlite3TreeViewPop(pView);
    if( p->pPrior ){
      n = 1000;
    }else{
      n = 0;
      if( p->pSrc && p->pSrc->nSrc ) n++;
      if( p->pWhere ) n++;
      if( p->pGroupBy ) n++;
      if( p->pHaving ) n++;
      if( p->pOrderBy ) n++;
      if( p->pLimit ) n++;
      if( p->pOffset ) n++;

    }
    sqlite3TreeViewExprList(pView, p->pEList, (n--)>0, "result-set");
    if( p->pSrc && p->pSrc->nSrc ){
      int i;
      pView = sqlite3TreeViewPush(pView, (n--)>0);
      sqlite3TreeViewLine(pView, "FROM");
      for(i=0; i<p->pSrc->nSrc; i++){
        struct SrcList_item *pItem = &p->pSrc->a[i];
        StrAccum x;
        char zLine[100];
        sqlite3StrAccumInit(&x, 0, zLine, sizeof(zLine), 0);
        sqlite3XPrintf(&x, 0, "{%d,*}", pItem->iCursor);
        if( pItem->zDatabase ){
          sqlite3XPrintf(&x, 0, " %s.%s", pItem->zDatabase, pItem->zName);
        }else if( pItem->zName ){
          sqlite3XPrintf(&x, 0, " %s", pItem->zName);
        }
        if( pItem->pTab ){
          sqlite3XPrintf(&x, 0, " tabname=%Q", pItem->pTab->zName);
        }
        if( pItem->zAlias ){
          sqlite3XPrintf(&x, 0, " (AS %s)", pItem->zAlias);
        }
        if( pItem->fg.jointype & JT_LEFT ){
          sqlite3XPrintf(&x, 0, " LEFT-JOIN");
        }
        sqlite3StrAccumFinish(&x);
        sqlite3TreeViewItem(pView, zLine, i<p->pSrc->nSrc-1); 
        if( pItem->pSelect ){
          sqlite3TreeViewSelect(pView, pItem->pSelect, 0);
        }
        if( pItem->fg.isTabFunc ){
          sqlite3TreeViewExprList(pView, pItem->u1.pFuncArg, 0, "func-args:");
        }
        sqlite3TreeViewPop(pView);
      }
      sqlite3TreeViewPop(pView);
    }
    if( p->pWhere ){
      sqlite3TreeViewItem(pView, "WHERE", (n--)>0);
      sqlite3TreeViewExpr(pView, p->pWhere, 0);
      sqlite3TreeViewPop(pView);
    }
    if( p->pGroupBy ){
      sqlite3TreeViewExprList(pView, p->pGroupBy, (n--)>0, "GROUPBY");
    }
    if( p->pHaving ){
      sqlite3TreeViewItem(pView, "HAVING", (n--)>0);
      sqlite3TreeViewExpr(pView, p->pHaving, 0);
      sqlite3TreeViewPop(pView);
    }
    if( p->pOrderBy ){
      sqlite3TreeViewExprList(pView, p->pOrderBy, (n--)>0, "ORDERBY");
    }
    if( p->pLimit ){
      sqlite3TreeViewItem(pView, "LIMIT", (n--)>0);
      sqlite3TreeViewExpr(pView, p->pLimit, 0);
      sqlite3TreeViewPop(pView);
    }
    if( p->pOffset ){
      sqlite3TreeViewItem(pView, "OFFSET", (n--)>0);
      sqlite3TreeViewExpr(pView, p->pOffset, 0);
      sqlite3TreeViewPop(pView);
    }
    if( p->pPrior ){
      const char *zOp = "UNION";
      switch( p->op ){
        case TK_ALL:         zOp = "UNION ALL";  break;
        case TK_INTERSECT:   zOp = "INTERSECT";  break;
        case TK_EXCEPT:      zOp = "EXCEPT";     break;
      }
      sqlite3TreeViewItem(pView, zOp, 1);


    }
    p = p->pPrior;
  }while( p!=0 );
  sqlite3TreeViewPop(pView);
}

/*
** Generate a human-readable explanation of an expression tree.
*/
void sqlite3TreeViewExpr(TreeView *pView, const Expr *pExpr, u8 moreToFollow){

  pView = sqlite3TreeViewPush(pView, moreToFollow);
  if( zLabel==0 || zLabel[0]==0 ) zLabel = "LIST";
  if( pList==0 ){
    sqlite3TreeViewLine(pView, "%s (empty)", zLabel);
  }else{
    sqlite3TreeViewLine(pView, "%s", zLabel);
    for(i=0; i<pList->nExpr; i++){
      int j = pList->a[i].u.x.iOrderByCol;
      if( j ){
        sqlite3TreeViewPush(pView, 0);
        sqlite3TreeViewLine(pView, "iOrderByCol=%d", j);
      }
      sqlite3TreeViewExpr(pView, pList->a[i].pExpr, i<pList->nExpr-1);
      if( j ) sqlite3TreeViewPop(pView);
    }
  }
  sqlite3TreeViewPop(pView);
}

#endif /* SQLITE_DEBUG */

      nRet += pTerm->truthProb;
    }else if( (pTerm->wtFlags & TERM_VNULL)==0 ){
      nRet -= 20;        assert( 20==sqlite3LogEst(4) );
    }
  }
  return nRet;
}


#ifdef SQLITE_ENABLE_STAT3_OR_STAT4
/*
** Return the affinity for a single column of an index.
*/
static char sqlite3IndexColumnAffinity(sqlite3 *db, Index *pIdx, int iCol){
  if( !pIdx->zColAff ){
    if( sqlite3IndexAffinityStr(db, pIdx)==0 ) return SQLITE_AFF_BLOB;
  }
  return pIdx->zColAff[iCol];
}
#endif


#ifdef SQLITE_ENABLE_STAT3_OR_STAT4
/* 
** This function is called to estimate the number of rows visited by a
** range-scan on a skip-scan index. For example:
**
**   CREATE INDEX i1 ON t1(a, b, c);

# 2015-08-26
#
# 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.
# 
# This file seeks to verify that expressions (and especially functions)
# that are in both the ORDER BY clause and the result set are only
# evaluated once.
#

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


do_execsql_test setup {
  -- create a table with many entries
  CREATE TABLE t1(x);
  WITH RECURSIVE
     c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100)
  INSERT INTO t1 SELECT x FROM c;
}
do_test 1.0 {
  set l1 {}
  # If random() is only evaluated once and then reused for each row, then
  # the output should appear in sorted order.  If random() is evaluated 
  # separately for the result set and the ORDER BY clause, then the output
  # order will be random.
  db eval {SELECT random() AS y FROM t1 ORDER BY 1;} {lappend l1 $y}
  expr {$l1==[lsort -int $l1]}
} {1}

do_test 1.1 {
  set l1 {}
  db eval {SELECT random() AS y FROM t1 ORDER BY random();} {lappend l1 $y}
  expr {$l1==[lsort -int $l1]}
} {1}

do_test 1.2 {
  set l1 {}
  db eval {SELECT random() AS y FROM t1 ORDER BY +random();} {lappend l1 $y}
  expr {$l1==[lsort -int $l1]}
} {0}

finish_test