SQLite

      */
      codeTableLocks(pParse);

      /* Initialize any AUTOINCREMENT data structures required.
      */
      sqlite3AutoincrementBegin(pParse);

      /* Code constant expressions that where factored out of inner loops.
      **
      ** The pConstExpr list might also contain expressions that we simply
      ** want to keep around until the Parse object is deleted.  Such
      ** expressions have iConstExprReg==0.  Do not generate code for
      ** those expressions, of course.
      */
      if( pParse->pConstExpr ){
        ExprList *pEL = pParse->pConstExpr;
        pParse->okConstFactor = 0;
        for(i=0; i<pEL->nExpr; i++){
          int iReg = pEL->a[i].u.iConstExprReg;
          if( iReg>0 ){
            sqlite3ExprCode(pParse, pEL->a[i].pExpr, iReg);
          }
        }
      }

      /* Finally, jump back to the beginning of the executable code. */
      sqlite3VdbeGoto(v, 1);
    }
  }

#ifndef SQLITE_OMIT_WINDOWFUNC
  if( ExprHasProperty(pExpr, EP_WinFunc) ){
    sqlite3WalkExpr(&w, pExpr->y.pWin->pFilter);
  }
#endif
  return cnt.nThis>0 || cnt.nOther==0;
}

/*
** This is a Walker expression node callback.
**
** For Expr nodes that contain pAggInfo pointers, make sure the AggInfo
** object that is referenced does not refer directly to the Expr.  If
** it does, make a copy.  This is done because the pExpr argument is
** subject to change.
**
** The copy is stored on pParse->pConstExpr with a register number of 0.
** This will cause the expression to be deleted automatically when the
** Parse object is destroyed, but the zero register number means that it
** will not generate any code in the preamble.
*/
static int agginfoPersistExprCb(Walker *pWalker, Expr *pExpr){
  if( !ExprHasProperty(pExpr, EP_TokenOnly|EP_Reduced)
   && pExpr->pAggInfo!=0
  ){
    AggInfo *pAggInfo = pExpr->pAggInfo;
    int iAgg = pExpr->iAgg;
    Parse *pParse = pWalker->pParse;
    sqlite3 *db = pParse->db;
    assert( pAggInfo->iAggMagic==AggInfoMagic );
    assert( pExpr->op==TK_COLUMN || pExpr->op==TK_AGG_COLUMN
            || pExpr->op==TK_FUNCTION || pExpr->op==TK_AGG_FUNCTION );
    if( pExpr->op==TK_COLUMN || pExpr->op==TK_AGG_COLUMN ){
      assert( iAgg>=0 && iAgg<pAggInfo->nColumn );
      if( pAggInfo->aCol[iAgg].pExpr==pExpr ){
        pExpr = sqlite3ExprDup(db, pExpr, 0);
        if( pExpr ){
          pAggInfo->aCol[iAgg].pExpr = pExpr;
          pParse->pConstExpr = 
             sqlite3ExprListAppend(pParse, pParse->pConstExpr, pExpr);
        }
      }
    }else{
      assert( iAgg>=0 && iAgg<pAggInfo->nFunc );
      if( pAggInfo->aFunc[iAgg].pExpr==pExpr ){
        pExpr = sqlite3ExprDup(db, pExpr, 0);
        if( pExpr ){
          pAggInfo->aFunc[iAgg].pExpr = pExpr;
          pParse->pConstExpr = 
             sqlite3ExprListAppend(pParse, pParse->pConstExpr, pExpr);
        }
      }
    }
  }
  return WRC_Continue;
}

/*
** Initialize a Walker object so that will persist AggInfo entries referenced
** by the tree that is walked.
*/
void sqlite3AggInfoPersistWalkerInit(Walker *pWalker, Parse *pParse){
  memset(pWalker, 0, sizeof(*pWalker));
  pWalker->pParse = pParse;
  pWalker->xExprCallback = agginfoPersistExprCb;
  pWalker->xSelectCallback = sqlite3SelectWalkNoop;
}

/*
** Add a new element to the pAggInfo->aCol[] array.  Return the index of
** the new element.  Return a negative number if malloc fails.
*/
static int addAggInfoColumn(sqlite3 *db, AggInfo *pInfo){
  int i;

       db, 
       pInfo->aFunc,
       sizeof(pInfo->aFunc[0]),
       &pInfo->nFunc,
       &i
  );
  return i;
}

/*
** This is the xExprCallback for a tree walker.  It is used to
** implement sqlite3ExprAnalyzeAggregates().  See sqlite3ExprAnalyzeAggregates
** for additional information.
*/
static int analyzeAggregate(Walker *pWalker, Expr *pExpr){

  int iParent;        /* VDBE cursor number of the pSub result set temp table */
  int iNewParent = -1;/* Replacement table for iParent */
  int isLeftJoin = 0; /* True if pSub is the right side of a LEFT JOIN */    
  int i;              /* Loop counter */
  Expr *pWhere;                    /* The WHERE clause */
  struct SrcList_item *pSubitem;   /* The subquery */
  sqlite3 *db = pParse->db;
  Walker w;                        /* Walker to persist agginfo data */

  /* Check to see if flattening is permitted.  Return 0 if not.
  */
  assert( p!=0 );
  assert( p->pPrior==0 );
  if( OptimizationDisabled(db, SQLITE_QueryFlattener) ) return 0;
  pSrc = p->pSrc;

      recomputeColumnsUsed(pParent, &pSrc->a[i+iFrom]);
    }
  }

  /* Finially, delete what is left of the subquery and return
  ** success.
  */
  sqlite3AggInfoPersistWalkerInit(&w, pParse);
  sqlite3WalkSelect(&w,pSub1);
  sqlite3SelectDelete(db, pSub1);

#if SELECTTRACE_ENABLED
  if( sqlite3SelectTrace & 0x100 ){
    SELECTTRACE(0x100,pParse,p,("After flattening:\n"));
    sqlite3TreeViewSelect(0, p, 0);
  }

  db = pParse->db;
  v = sqlite3GetVdbe(pParse);
  if( p==0 || db->mallocFailed || pParse->nErr ){
    return 1;
  }
  if( sqlite3AuthCheck(pParse, SQLITE_SELECT, 0, 0, 0) ) return 1;
  memset(&sAggInfo, 0, sizeof(sAggInfo));
#ifdef SQLITE_DEBUG
  sAggInfo.iAggMagic = AggInfoMagic;
#endif
#if SELECTTRACE_ENABLED
  SELECTTRACE(1,pParse,p, ("begin processing:\n", pParse->addrExplain));
  if( sqlite3SelectTrace & 0x100 ){
    sqlite3TreeViewSelect(0, p, 0);
  }
#endif

#if SELECTTRACE_ENABLED
    SELECTTRACE(0x1,pParse,p,("end compound-select processing\n"));
    if( (sqlite3SelectTrace & 0x2000)!=0 && ExplainQueryPlanParent(pParse)==0 ){
      sqlite3TreeViewSelect(0, p, 0);
    }
#endif
    if( p->pNext==0 ) ExplainQueryPlanPop(pParse);
    assert( sAggInfo.nFunc==0 && sAggInfo.nColumn==0 );
    return rc;
  }
#endif

  /* Do the WHERE-clause constant propagation optimization if this is
  ** a join.  No need to speed time on this operation for non-join queries
  ** as the equivalent optimization will be handled by query planner in

  rc = (pParse->nErr>0);

  /* Control jumps to here if an error is encountered above, or upon
  ** successful coding of the SELECT.
  */
select_end:
  sqlite3ExprListDelete(db, pMinMaxOrderBy);
  if( sAggInfo.aCol ){
#ifdef SQLITE_DEBUG
    for(i=0; i<sAggInfo.nColumn; i++){
      Expr *pExpr = sAggInfo.aCol[i].pExpr;
      assert( pExpr!=0 || pParse->db->mallocFailed );
      if( pExpr==0 ) continue;
      assert( pExpr->pAggInfo==&sAggInfo );
      assert( pExpr->iAgg==i );
    }
#endif
    sqlite3DbFree(db, sAggInfo.aCol);
  }
  if( sAggInfo.aFunc ){
#ifdef SQLITE_DEBUG
    for(i=0; i<sAggInfo.nFunc; i++){
      Expr *pExpr = sAggInfo.aFunc[i].pExpr;
      assert( pExpr!=0 || pParse->db->mallocFailed );
      if( pExpr==0 ) continue;
      assert( pExpr->pAggInfo==&sAggInfo );
      assert( pExpr->iAgg==i );
    }
#endif
    sqlite3DbFree(db, sAggInfo.aFunc);
  }
#ifdef SQLITE_DEBUG
  sAggInfo.iAggMagic = 0;
#endif

#if SELECTTRACE_ENABLED
  SELECTTRACE(0x1,pParse,p,("end processing\n"));
  if( (sqlite3SelectTrace & 0x2000)!=0 && ExplainQueryPlanParent(pParse)==0 ){
    sqlite3TreeViewSelect(0, p, 0);
  }
#endif
  ExplainQueryPlanPop(pParse);
  return rc;
}

  int sortingIdx;         /* Cursor number of the sorting index */
  int sortingIdxPTab;     /* Cursor number of pseudo-table */
  int nSortingColumn;     /* Number of columns in the sorting index */
  int mnReg, mxReg;       /* Range of registers allocated for aCol and aFunc */
  ExprList *pGroupBy;     /* The group by clause */
  struct AggInfo_col {    /* For each column used in source tables */
    Table *pTab;             /* Source table */
    Expr *pExpr;             /* The original expression */
    int iTable;              /* Cursor number of the source table */
    int iMem;                /* Memory location that acts as accumulator */
    i16 iColumn;             /* Column number within the source table */
    i16 iSorterColumn;       /* Column number in the sorting index */


  } *aCol;
  int nColumn;            /* Number of used entries in aCol[] */
  int nAccumulator;       /* Number of columns that show through to the output.
                          ** Additional columns are used only as parameters to
                          ** aggregate functions */
  struct AggInfo_func {   /* For each aggregate function */
    Expr *pExpr;             /* Expression encoding the function */
    FuncDef *pFunc;          /* The aggregate function implementation */
    int iMem;                /* Memory location that acts as accumulator */
    int iDistinct;           /* Ephemeral table used to enforce DISTINCT */
  } *aFunc;
  int nFunc;              /* Number of entries in aFunc[] */
#ifdef SQLITE_DEBUG
  int iAggMagic;          /* Magic number when valid */
#endif
};

/*
** Value for AggInfo.iAggMagic when the structure is valid
*/
#define AggInfoMagic  0x2059e99e

/*
** The datatype ynVar is a signed integer, either 16-bit or 32-bit.
** Usually it is 16-bits.  But if SQLITE_MAX_VARIABLE_NUMBER is greater
** than 32767 we have to make it 32-bit.  16-bit is preferred because
** it uses less memory in the Expr object, which is a big memory user
** in systems with lots of prepared statements.  And few applications
** need more than about 10 or 20 variables.  But some extreme users want

int sqlite3RunVacuum(char**, sqlite3*, int, sqlite3_value*);
char *sqlite3NameFromToken(sqlite3*, Token*);
int sqlite3ExprCompare(Parse*,Expr*, Expr*, int);
int sqlite3ExprCompareSkip(Expr*, Expr*, int);
int sqlite3ExprListCompare(ExprList*, ExprList*, int);
int sqlite3ExprImpliesExpr(Parse*,Expr*, Expr*, int);
int sqlite3ExprImpliesNonNullRow(Expr*,int);
void sqlite3AggInfoPersistWalkerInit(Walker*,Parse*);
void sqlite3ExprAnalyzeAggregates(NameContext*, Expr*);
void sqlite3ExprAnalyzeAggList(NameContext*,ExprList*);
int sqlite3ExprCoveredByIndex(Expr*, int iCur, Index *pIdx);
int sqlite3FunctionUsesThisSrc(Expr*, SrcList*);
Vdbe *sqlite3GetVdbe(Parse*);
#ifndef SQLITE_UNTESTABLE
void sqlite3PrngSaveState(void);

    Expr *pHaving = p->pHaving;
    ExprList *pSort = 0;

    ExprList *pSublist = 0;       /* Expression list for sub-query */
    Window *pMWin = p->pWin;      /* Master window object */
    Window *pWin;                 /* Window object iterator */
    Table *pTab;
    Walker w;

    u32 selFlags = p->selFlags;

    pTab = sqlite3DbMallocZero(db, sizeof(Table));
    if( pTab==0 ){
      return sqlite3ErrorToParser(db, SQLITE_NOMEM);
    }
    sqlite3AggInfoPersistWalkerInit(&w, pParse);
    sqlite3WalkSelect(&w, p);

    p->pSrc = 0;
    p->pWhere = 0;
    p->pGroupBy = 0;
    p->pHaving = 0;
    p->selFlags &= ~SF_Aggregate;
    p->selFlags |= SF_WinRewrite;

    pSub = sqlite3SelectNew(
        pParse, pSublist, pSrc, pWhere, pGroupBy, pHaving, pSort, 0, 0
    );
    p->pSrc = sqlite3SrcListAppend(pParse, 0, 0, 0);
    if( p->pSrc ){
      Table *pTab2;

      p->pSrc->a[0].pSelect = pSub;
      sqlite3SrcListAssignCursors(pParse, p->pSrc);
      pSub->selFlags |= SF_Expanded;
      pTab2 = sqlite3ResultSetOfSelect(pParse, pSub, SQLITE_AFF_NONE);
      pSub->selFlags |= (selFlags & SF_Aggregate);
      if( pTab2==0 ){
        /* Might actually be some other kind of error, but in that case

    FROM a
    JOIN a AS b ON a.c=4
    JOIN a AS e ON a.c=e.c
   WHERE a.c=(SELECT (SELECT coalesce(lead(2) OVER(),0) + sum(d.c))
                FROM a AS d
               WHERE a.c);
} {4 4 4 4}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 54.1 {
  CREATE TABLE t1(a VARCHAR(20), b FLOAT);
  INSERT INTO t1 VALUES('1',10.0);
}

do_catchsql_test 54.2 {
  SELECT * FROM ( 
    SELECT sum(b) OVER() AS c FROM t1 
      UNION
    SELECT b AS c FROM t1
  ) WHERE c>10;
} {1 {misuse of window function sum()}}

do_execsql_test 54.3 {
  INSERT INTO t1 VALUES('2',5.0);
  INSERT INTO t1 VALUES('3',15.0);
}

do_catchsql_test 54.4 {
  SELECT * FROM ( 
    SELECT sum(b) OVER() AS c FROM t1 
      UNION
    SELECT b AS c FROM t1
  ) WHERE c>10;
} {1 {misuse of window function sum()}}

# 2020-06-05 ticket c8d3b9f0a750a529
reset_db
do_execsql_test 55.1 {
   CREATE TABLE a(b);
   SELECT
      (SELECT b FROM a
        GROUP BY b
        HAVING (SELECT COUNT()OVER() + lead(b)OVER(ORDER BY SUM(DISTINCT b) + b))
      ) 
    FROM a
  UNION
   SELECT 99
    ORDER BY 1;
} {99}

#------------------------------------------------------------------------
reset_db
do_execsql_test 56.1 {
  CREATE TABLE t1(a, b INTEGER); 
  CREATE TABLE t2(c, d); 
}
do_catchsql_test 56.2 {
  SELECT avg(b) FROM t1 
    UNION ALL 
  SELECT min(c) OVER () FROM t2 
  ORDER BY nosuchcolumn;
} {1 {1st ORDER BY term does not match any column in the result set}}

reset_db
do_execsql_test 57.1 {
  CREATE TABLE t4(a, b, c, d, e);
}

do_catchsql_test 57.2  {
  SELECT b FROM t4
  UNION
  SELECT a FROM t4
  ORDER BY (
    SELECT sum(x) OVER() FROM (
      SELECT c AS x FROM t4
      UNION
      SELECT d FROM t4
      ORDER BY (SELECT e FROM t4)
    )
  );
} {1 {1st ORDER BY term does not match any column in the result set}}

# 2020-06-06 various dbsqlfuzz finds and
# ticket 0899cf62f597d7e7
#
reset_db
do_execsql_test 57.1 {
  CREATE TABLE t1(a, b, c);
  INSERT INTO t1 VALUES(NULL,NULL,NULL);
  SELECT 
    sum(a),
    min(b) OVER (),
    count(c) OVER (ORDER BY b)
  FROM t1;
} {{} {} 0}
do_execsql_test 57.2 {
  CREATE TABLE v0 ( v1 INTEGER PRIMARY KEY ) ; 
  INSERT INTO v0 VALUES ( 10 ) ; 
  SELECT DISTINCT v1, lead(v1) OVER() FROM v0 GROUP BY v1 ORDER BY 2;
} {10 {}}
do_catchsql_test 57.3 {
  DROP TABLE t1;
  CREATE TABLE t1(a);
  INSERT INTO t1(a) VALUES(22);
  CREATE TABLE t3(y);
  INSERT INTO t3(y) VALUES(5),(11),(-9);
  SELECT (
    SELECT max(y) OVER( ORDER BY (SELECT x FROM (SELECT sum(y) AS x FROM t1)))
  )
  FROM t3;
} {1 {misuse of aggregate: sum()}}

# 2020-06-06 ticket 1f6f353b684fc708
reset_db
do_execsql_test 58.1 {
  CREATE TABLE a(a, b, c);
  INSERT INTO a VALUES(1, 2, 3);
  INSERT INTO a VALUES(4, 5, 6);
  SELECT sum(345+b)      OVER (ORDER BY b),
         sum(avg(678)) OVER (ORDER BY c) FROM a;
} {347 678.0}

# 2020-06-06 ticket e5504e987e419fb0
do_catchsql_test 59.1 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(x INTEGER PRIMARY KEY);
  INSERT INTO t1 VALUES (123);
  SELECT
     ntile( (SELECT sum(x)) ) OVER(ORDER BY x),
     min(x) OVER(ORDER BY x)
    FROM t1; 
} {1 {misuse of aggregate: sum()}}

# 2020-06-07 ticket f7d890858f361402
do_execsql_test 60.1 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1 (x INTEGER PRIMARY KEY);
  INSERT INTO t1 VALUES (99);
  SELECT EXISTS(SELECT count(*) OVER() FROM t1 ORDER BY sum(x) OVER());
} {1}

reset_db


finish_test