SQLite

Changes On Branch early-winfunc-rewrite-dev
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Changes In Branch early-winfunc-rewrite-dev Excluding Merge-Ins

This is equivalent to a diff from c1c8937a to c0d9dfe5

2020-06-08
11:34
When an Expr object is changed and that Expr is referenced by an AggInfo, then also update the AggInfo. Also, persist all AggInfo objects until the Parse object is destroyed. This is a new fix for ticket [c8d3b9f0a750a529] that avoids the follow-on problems identified by tickets [0899cf62f597d7e7], [1f6f353b684fc708], [e5504e987e419fb0], and [f7d890858f361402]. (check-in: 6e6b3729 user: drh tags: trunk)
2020-06-07
17:33
Alternative fix to ticket [c8d3b9f0a750a529]: Prior to deleting or modifying an Expr not that is referenced by an AggInfo, modify the AggInfo to get its own copy of the original Expr. (check-in: 7682d8a7 user: drh tags: persist-agginfo)
14:42
New debugging output. (Leaf check-in: c0d9dfe5 user: drh tags: early-winfunc-rewrite-dev)
12:26
Improvements to the treeview debugging display. (check-in: a7c5c739 user: drh tags: early-winfunc-rewrite-dev)
2020-06-05
15:26
Do parse-tree transformations required for window functions prior to running aggregate function analysis. Fix for ticket [c8d3b9f0a750a529]. (check-in: 0b42a227 user: drh tags: early-winfunc-rewrite-dev)
14:10
Move the parse tree rewrite for window functions earlier in the process, before sqlite3ExprAnalyzeAggregates() has run. Add new assert()s to verify that aggregate analysis always remains valid until the end of SELECT processing. (check-in: fe702aa0 user: drh tags: early-winfunc-rewrite)
04:01
In the debugging treeview output, change the name of "SELECT-expr" expression nodes to be "subquery-expr", so as to not confuse them with actual SELECT nodes. (check-in: c1c8937a user: drh tags: trunk)
00:54
Always use ?...? to indicate optional arguments in the output of ".help" in the CLI. Change ".mode column" so that it automatically activates ".headers on" if headers have not been previously turned on or off. (check-in: 2827c0a1 user: drh tags: trunk)

Changes to src/build.c.

208
209
210
211
212
213
214


215

216
217
218
219
220
221
222
      sqlite3AutoincrementBegin(pParse);

      /* Code constant expressions that where factored out of inner loops */
      if( pParse->pConstExpr ){
        ExprList *pEL = pParse->pConstExpr;
        pParse->okConstFactor = 0;
        for(i=0; i<pEL->nExpr; i++){


          sqlite3ExprCode(pParse, pEL->a[i].pExpr, pEL->a[i].u.iConstExprReg);

        }
      }

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







>
>
|
>







208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
      sqlite3AutoincrementBegin(pParse);

      /* Code constant expressions that where factored out of inner loops */
      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);
    }
  }

Changes to src/expr.c.

3807
3808
3809
3810
3811
3812
3813

3814
3815
3816
3817
3818
3819
3820
    op = pExpr->op;
  }
  switch( op ){
    case TK_AGG_COLUMN: {
      AggInfo *pAggInfo = pExpr->pAggInfo;
      struct AggInfo_col *pCol;
      assert( pAggInfo!=0 );

      assert( pExpr->iAgg>=0 && pExpr->iAgg<pAggInfo->nColumn );
      pCol = &pAggInfo->aCol[pExpr->iAgg];
      if( !pAggInfo->directMode ){
        assert( pCol->iMem>0 );
        return pCol->iMem;
      }else if( pAggInfo->useSortingIdx ){
        Table *pTab = pCol->pTab;







>







3807
3808
3809
3810
3811
3812
3813
3814
3815
3816
3817
3818
3819
3820
3821
    op = pExpr->op;
  }
  switch( op ){
    case TK_AGG_COLUMN: {
      AggInfo *pAggInfo = pExpr->pAggInfo;
      struct AggInfo_col *pCol;
      assert( pAggInfo!=0 );
      assert( AggInfoValid(pAggInfo) );
      assert( pExpr->iAgg>=0 && pExpr->iAgg<pAggInfo->nColumn );
      pCol = &pAggInfo->aCol[pExpr->iAgg];
      if( !pAggInfo->directMode ){
        assert( pCol->iMem>0 );
        return pCol->iMem;
      }else if( pAggInfo->useSortingIdx ){
        Table *pTab = pCol->pTab;
4115
4116
4117
4118
4119
4120
4121

4122
4123
4124
4125
4126
4127
4128
      if( pInfo==0
       || NEVER(pExpr->iAgg<0)
       || NEVER(pExpr->iAgg>=pInfo->nFunc)
      ){
        assert( !ExprHasProperty(pExpr, EP_IntValue) );
        sqlite3ErrorMsg(pParse, "misuse of aggregate: %s()", pExpr->u.zToken);
      }else{

        return pInfo->aFunc[pExpr->iAgg].iMem;
      }
      break;
    }
    case TK_FUNCTION: {
      ExprList *pFarg;       /* List of function arguments */
      int nFarg;             /* Number of function arguments */







>







4116
4117
4118
4119
4120
4121
4122
4123
4124
4125
4126
4127
4128
4129
4130
      if( pInfo==0
       || NEVER(pExpr->iAgg<0)
       || NEVER(pExpr->iAgg>=pInfo->nFunc)
      ){
        assert( !ExprHasProperty(pExpr, EP_IntValue) );
        sqlite3ErrorMsg(pParse, "misuse of aggregate: %s()", pExpr->u.zToken);
      }else{
        assert( AggInfoValid(pInfo) );
        return pInfo->aFunc[pExpr->iAgg].iMem;
      }
      break;
    }
    case TK_FUNCTION: {
      ExprList *pFarg;       /* List of function arguments */
      int nFarg;             /* Number of function arguments */
5653
5654
5655
5656
5657
5658
5659
5660
5661
5662
5663
5664
5665
5666
5667
5668
5669
5670
5671
5672
5673
  int nOther;      /* Number of references to columns in other FROM clauses */
};

/*
** Count the number of references to columns.
*/
static int exprSrcCount(Walker *pWalker, Expr *pExpr){
  /* There was once a NEVER() on the second term on the grounds that
  ** sqlite3FunctionUsesThisSrc() was always called before 
  ** sqlite3ExprAnalyzeAggregates() and so the TK_COLUMNs have not yet 
  ** been converted into TK_AGG_COLUMN. But this is no longer true due
  ** to window functions - sqlite3WindowRewrite() may now indirectly call
  ** FunctionUsesThisSrc() when creating a new sub-select. */
  if( pExpr->op==TK_COLUMN || pExpr->op==TK_AGG_COLUMN ){
    int i;
    struct SrcCount *p = pWalker->u.pSrcCount;
    SrcList *pSrc = p->pSrc;
    int nSrc = pSrc ? pSrc->nSrc : 0;
    for(i=0; i<nSrc; i++){
      if( pExpr->iTable==pSrc->a[i].iCursor ) break;
    }







<
<
<
<
<
<
|







5655
5656
5657
5658
5659
5660
5661






5662
5663
5664
5665
5666
5667
5668
5669
  int nOther;      /* Number of references to columns in other FROM clauses */
};

/*
** Count the number of references to columns.
*/
static int exprSrcCount(Walker *pWalker, Expr *pExpr){






  if( pExpr->op==TK_COLUMN || NEVER(pExpr->op==TK_AGG_COLUMN) ){
    int i;
    struct SrcCount *p = pWalker->u.pSrcCount;
    SrcList *pSrc = p->pSrc;
    int nSrc = pSrc ? pSrc->nSrc : 0;
    for(i=0; i<nSrc; i++){
      if( pExpr->iTable==pSrc->a[i].iCursor ) break;
    }

Changes to src/global.c.

296
297
298
299
300
301
302





303
304
305
306
307
308
309
** Changing the pending byte during operation will result in undefined
** and incorrect behavior.
*/
#ifndef SQLITE_OMIT_WSD
int sqlite3PendingByte = 0x40000000;
#endif






#include "opcodes.h"
/*
** Properties of opcodes.  The OPFLG_INITIALIZER macro is
** created by mkopcodeh.awk during compilation.  Data is obtained
** from the comments following the "case OP_xxxx:" statements in
** the vdbe.c file.  
*/







>
>
>
>
>







296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
** Changing the pending byte during operation will result in undefined
** and incorrect behavior.
*/
#ifndef SQLITE_OMIT_WSD
int sqlite3PendingByte = 0x40000000;
#endif

/*
** Flags for select tracing and the ".selecttrace" macro of the CLI
*/
/**/ u32 sqlite3SelectTrace = 0;

#include "opcodes.h"
/*
** Properties of opcodes.  The OPFLG_INITIALIZER macro is
** created by mkopcodeh.awk during compilation.  Data is obtained
** from the comments following the "case OP_xxxx:" statements in
** the vdbe.c file.  
*/

Changes to src/resolve.c.

1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
            pNC2 = pNC2->pNext;
          }
          assert( pDef!=0 || IN_RENAME_OBJECT );
          if( pNC2 && pDef ){
            assert( SQLITE_FUNC_MINMAX==NC_MinMaxAgg );
            testcase( (pDef->funcFlags & SQLITE_FUNC_MINMAX)!=0 );
            pNC2->ncFlags |= NC_HasAgg | (pDef->funcFlags & SQLITE_FUNC_MINMAX);

          }
        }
        pNC->ncFlags |= savedAllowFlags;
      }
      /* FIX ME:  Compute pExpr->affinity based on the expected return
      ** type of the function 
      */







<







1020
1021
1022
1023
1024
1025
1026

1027
1028
1029
1030
1031
1032
1033
            pNC2 = pNC2->pNext;
          }
          assert( pDef!=0 || IN_RENAME_OBJECT );
          if( pNC2 && pDef ){
            assert( SQLITE_FUNC_MINMAX==NC_MinMaxAgg );
            testcase( (pDef->funcFlags & SQLITE_FUNC_MINMAX)!=0 );
            pNC2->ncFlags |= NC_HasAgg | (pDef->funcFlags & SQLITE_FUNC_MINMAX);

          }
        }
        pNC->ncFlags |= savedAllowFlags;
      }
      /* FIX ME:  Compute pExpr->affinity based on the expected return
      ** type of the function 
      */
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199


1200




1201
1202

1203

1204
1205
1206
1207
1208
1209
1210
  Expr *pE           /* The specific ORDER BY term */
){
  int i;             /* Loop counter */
  ExprList *pEList;  /* The columns of the result set */
  NameContext nc;    /* Name context for resolving pE */
  sqlite3 *db;       /* Database connection */
  int rc;            /* Return code from subprocedures */
  u8 savedSuppErr;   /* Saved value of db->suppressErr */

  assert( sqlite3ExprIsInteger(pE, &i)==0 );
  pEList = pSelect->pEList;

  /* Resolve all names in the ORDER BY term expression
  */
  memset(&nc, 0, sizeof(nc));
  nc.pParse = pParse;
  nc.pSrcList = pSelect->pSrc;
  nc.uNC.pEList = pEList;
  nc.ncFlags = NC_AllowAgg|NC_UEList;
  nc.nErr = 0;
  db = pParse->db;


  savedSuppErr = db->suppressErr;




  if( IN_RENAME_OBJECT==0 ) db->suppressErr = 1;
  rc = sqlite3ResolveExprNames(&nc, pE);

  db->suppressErr = savedSuppErr;

  if( rc ) return 0;

  /* Try to match the ORDER BY expression against an expression
  ** in the result set.  Return an 1-based index of the matching
  ** result-set entry.
  */
  for(i=0; i<pEList->nExpr; i++){







<













>
>
|
>
>
>
>
|
|
>
|
>







1178
1179
1180
1181
1182
1183
1184

1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
  Expr *pE           /* The specific ORDER BY term */
){
  int i;             /* Loop counter */
  ExprList *pEList;  /* The columns of the result set */
  NameContext nc;    /* Name context for resolving pE */
  sqlite3 *db;       /* Database connection */
  int rc;            /* Return code from subprocedures */


  assert( sqlite3ExprIsInteger(pE, &i)==0 );
  pEList = pSelect->pEList;

  /* Resolve all names in the ORDER BY term expression
  */
  memset(&nc, 0, sizeof(nc));
  nc.pParse = pParse;
  nc.pSrcList = pSelect->pSrc;
  nc.uNC.pEList = pEList;
  nc.ncFlags = NC_AllowAgg|NC_UEList;
  nc.nErr = 0;
  db = pParse->db;
  assert( pParse->nErr==0 );
  rc = sqlite3ResolveExprNames(&nc, pE);

  /* Discard any error. The only error that can occur in the above call
  ** that we care about is SQLITE_NOMEM, the occurence of which is recorded in
  ** db->mallocFailed.  */
  assert( rc!=SQLITE_NOMEM || db->mallocFailed );
  if( IN_RENAME_OBJECT==0 ){
    sqlite3DbFree(db, pParse->zErrMsg);
    pParse->zErrMsg = 0;
    pParse->nErr = 0;
  }
  if( rc ) return 0;

  /* Try to match the ORDER BY expression against an expression
  ** in the result set.  Return an 1-based index of the matching
  ** result-set entry.
  */
  for(i=0; i<pEList->nExpr; i++){
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
          ** case. This allows the code in alter.c to modify column
          ** refererences within the ORDER BY expression as required.  */
          if( IN_RENAME_OBJECT ){
            pDup = pE;
          }else{
            pDup = sqlite3ExprDup(db, pE, 0);
          }
          if( !db->mallocFailed ){
            assert(pDup);
            iCol = resolveOrderByTermToExprList(pParse, pSelect, pDup);
          }
          if( !IN_RENAME_OBJECT ){
            sqlite3ExprDelete(db, pDup);
          }
        }







|







1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
          ** case. This allows the code in alter.c to modify column
          ** refererences within the ORDER BY expression as required.  */
          if( IN_RENAME_OBJECT ){
            pDup = pE;
          }else{
            pDup = sqlite3ExprDup(db, pE, 0);
          }
          if( !db->mallocFailed && !pParse->nErr ){
            assert(pDup);
            iCol = resolveOrderByTermToExprList(pParse, pSelect, pDup);
          }
          if( !IN_RENAME_OBJECT ){
            sqlite3ExprDelete(db, pDup);
          }
        }
1711
1712
1713
1714
1715
1716
1717
















1718
1719
1720
1721
1722
1723
1724
      for(pWin=p->pWinDefn; pWin; pWin=pWin->pNextWin){
        if( sqlite3ResolveExprListNames(&sNC, pWin->pOrderBy)
         || sqlite3ResolveExprListNames(&sNC, pWin->pPartition)
        ){
          return WRC_Abort;
        }
      }
















    }
#endif

    /* If this is part of a compound SELECT, check that it has the right
    ** number of expressions in the select list. */
    if( p->pNext && p->pEList->nExpr!=p->pNext->pEList->nExpr ){
      sqlite3SelectWrongNumTermsError(pParse, p->pNext);







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







1717
1718
1719
1720
1721
1722
1723
1724
1725
1726
1727
1728
1729
1730
1731
1732
1733
1734
1735
1736
1737
1738
1739
1740
1741
1742
1743
1744
1745
1746
      for(pWin=p->pWinDefn; pWin; pWin=pWin->pNextWin){
        if( sqlite3ResolveExprListNames(&sNC, pWin->pOrderBy)
         || sqlite3ResolveExprListNames(&sNC, pWin->pPartition)
        ){
          return WRC_Abort;
        }
      }
    }else if( p->pWin && ALWAYS( (p->selFlags & SF_WinRewrite)==0 ) ){
#if SELECTTRACE_ENABLED
      if( (sqlite3SelectTrace & 0x108)!=0 ){
        SELECTTRACE(0x104,pParse,p,
              ("(%u/%p) before window rewrite:\n",p->selId,p));
        sqlite3TreeViewSelect(0, p, 0);
      }
#endif
      sqlite3WindowRewrite(pParse, p);
#if SELECTTRACE_ENABLED
      if( (sqlite3SelectTrace & 0x108)!=0 ){
        SELECTTRACE(0x104,pParse,p, 
              ("(%u/%p) after window rewrite:\n",p->selId,p));
        sqlite3TreeViewSelect(0, p, 0);
      }
#endif
    }
#endif

    /* If this is part of a compound SELECT, check that it has the right
    ** number of expressions in the select list. */
    if( p->pNext && p->pEList->nExpr!=p->pNext->pEList->nExpr ){
      sqlite3SelectWrongNumTermsError(pParse, p->pNext);

Changes to src/select.c.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
**    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.
*/
#include "sqliteInt.h"

/*
** Trace output macros
*/
#if SELECTTRACE_ENABLED
/***/ int sqlite3SelectTrace = 0;
# define SELECTTRACE(K,P,S,X)  \
  if(sqlite3SelectTrace&(K))   \
    sqlite3DebugPrintf("%u/%d/%p: ",(S)->selId,(P)->addrExplain,(S)),\
    sqlite3DebugPrintf X
#else
# define SELECTTRACE(K,P,S,X)
#endif


/*
** An instance of the following object is used to record information about
** how to process the DISTINCT keyword, to simplify passing that information
** into the selectInnerLoop() routine.
*/
typedef struct DistinctCtx DistinctCtx;







<
<
<
<
<
<
<
<
<
<
<
<
<
<







9
10
11
12
13
14
15














16
17
18
19
20
21
22
**    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.
*/
#include "sqliteInt.h"















/*
** An instance of the following object is used to record information about
** how to process the DISTINCT keyword, to simplify passing that information
** into the selectInnerLoop() routine.
*/
typedef struct DistinctCtx DistinctCtx;
177
178
179
180
181
182
183
184
185
186
187
188
189
190





























191
192
193
194
195
196
197
    pAllocated = 0;
  }else{
    assert( pNew->pSrc!=0 || pParse->nErr>0 );
  }
  return pAllocated;
}


/*
** Delete the given Select structure and all of its substructures.
*/
void sqlite3SelectDelete(sqlite3 *db, Select *p){
  if( OK_IF_ALWAYS_TRUE(p) ) clearSelect(db, p, 1);
}






























/*
** Delete all the substructure for p, but keep p allocated.  Redefine
** p to be a single SELECT where every column of the result set has a
** value of NULL.
*/
void sqlite3SelectReset(Parse *pParse, Select *p){







<






>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







163
164
165
166
167
168
169

170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
    pAllocated = 0;
  }else{
    assert( pNew->pSrc!=0 || pParse->nErr>0 );
  }
  return pAllocated;
}


/*
** Delete the given Select structure and all of its substructures.
*/
void sqlite3SelectDelete(sqlite3 *db, Select *p){
  if( OK_IF_ALWAYS_TRUE(p) ) clearSelect(db, p, 1);
}

/*
** Make arrangements to delete a subquery when the Parse object is
** destroyed.
**
** This works by adding the subquery to the Parse.pConstExpr list, but
** with a register number of zero.  The pConstExpr list is destroyed
** when the Parse object is destroyed.  And the zero register means
** that the expression is not coded.
*/
void sqlite3SelectDeferredDelete(Parse *pParse, Select *p){
  Expr *pNew = sqlite3PExpr(pParse, TK_SELECT, 0, 0);
  if( pNew==0 ){
    clearSelect(pParse->db, p, 1);
  }else{
    sqlite3PExprAddSelect(pParse, pNew, p);
    pParse->pConstExpr =
      sqlite3ExprListAppend(pParse, pParse->pConstExpr, pNew);
    testcase( pParse->pConstExpr==0 ); /* Only true following OOM */
#ifdef SQLITE_DEBUG
    if( pParse->pConstExpr ){
      ExprList *p = pParse->pConstExpr;
      struct ExprList_item *pItem = &p->a[p->nExpr-1];
      assert( pItem->reusable==0 );
      assert( pItem->u.iConstExprReg==0 );
    }
#endif
  }
}

/*
** Delete all the substructure for p, but keep p allocated.  Redefine
** p to be a single SELECT where every column of the result set has a
** value of NULL.
*/
void sqlite3SelectReset(Parse *pParse, Select *p){
4161
4162
4163
4164
4165
4166
4167
4168
4169
4170
4171
4172
4173
4174
4175
      recomputeColumnsUsed(pParent, &pSrc->a[i+iFrom]);
    }
  }

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

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







|







4175
4176
4177
4178
4179
4180
4181
4182
4183
4184
4185
4186
4187
4188
4189
      recomputeColumnsUsed(pParent, &pSrc->a[i+iFrom]);
    }
  }

  /* Finially, delete what is left of the subquery and return
  ** success.
  */
  sqlite3SelectDeferredDelete(pParse, pSub1);

#if SELECTTRACE_ENABLED
  if( sqlite3SelectTrace & 0x100 ){
    SELECTTRACE(0x100,pParse,p,("After flattening:\n"));
    sqlite3TreeViewSelect(0, p, 0);
  }
#endif
4421
4422
4423
4424
4425
4426
4427

4428
4429
4430
4431

4432

4433
4434
4435
4436
4437
4438
4439
  Select *pSubq,        /* The subquery whose WHERE clause is to be augmented */
  Expr *pWhere,         /* The WHERE clause of the outer query */
  int iCursor,          /* Cursor number of the subquery */
  int isLeftJoin        /* True if pSubq is the right term of a LEFT JOIN */
){
  Expr *pNew;
  int nChng = 0;

  if( pWhere==0 ) return 0;
  if( pSubq->selFlags & SF_Recursive ) return 0;  /* restriction (2) */

#ifndef SQLITE_OMIT_WINDOWFUNC

  if( pSubq->pWin ) return 0;    /* restriction (6) */

#endif

#ifdef SQLITE_DEBUG
  /* Only the first term of a compound can have a WITH clause.  But make
  ** sure no other terms are marked SF_Recursive in case something changes
  ** in the future.
  */







>




>
|
>







4435
4436
4437
4438
4439
4440
4441
4442
4443
4444
4445
4446
4447
4448
4449
4450
4451
4452
4453
4454
4455
4456
  Select *pSubq,        /* The subquery whose WHERE clause is to be augmented */
  Expr *pWhere,         /* The WHERE clause of the outer query */
  int iCursor,          /* Cursor number of the subquery */
  int isLeftJoin        /* True if pSubq is the right term of a LEFT JOIN */
){
  Expr *pNew;
  int nChng = 0;
  Select *pSel;
  if( pWhere==0 ) return 0;
  if( pSubq->selFlags & SF_Recursive ) return 0;  /* restriction (2) */

#ifndef SQLITE_OMIT_WINDOWFUNC
  for(pSel=pSubq; pSel; pSel=pSel->pPrior){
    if( pSel->pWin ) return 0;    /* restriction (6) */
  }
#endif

#ifdef SQLITE_DEBUG
  /* Only the first term of a compound can have a WITH clause.  But make
  ** sure no other terms are marked SF_Recursive in case something changes
  ** in the future.
  */
5343
5344
5345
5346
5347
5348
5349
5350
5351
5352
5353
5354
5355
5356
5357
void sqlite3SelectPrep(
  Parse *pParse,         /* The parser context */
  Select *p,             /* The SELECT statement being coded. */
  NameContext *pOuterNC  /* Name context for container */
){
  assert( p!=0 || pParse->db->mallocFailed );
  if( pParse->db->mallocFailed ) return;
  if( p->selFlags & SF_HasTypeInfo ) return;
  sqlite3SelectExpand(pParse, p);
  if( pParse->nErr || pParse->db->mallocFailed ) return;
  sqlite3ResolveSelectNames(pParse, p, pOuterNC);
  if( pParse->nErr || pParse->db->mallocFailed ) return;
  sqlite3SelectAddTypeInfo(pParse, p);
}








<







5360
5361
5362
5363
5364
5365
5366

5367
5368
5369
5370
5371
5372
5373
void sqlite3SelectPrep(
  Parse *pParse,         /* The parser context */
  Select *p,             /* The SELECT statement being coded. */
  NameContext *pOuterNC  /* Name context for container */
){
  assert( p!=0 || pParse->db->mallocFailed );
  if( pParse->db->mallocFailed ) return;

  sqlite3SelectExpand(pParse, p);
  if( pParse->nErr || pParse->db->mallocFailed ) return;
  sqlite3ResolveSelectNames(pParse, p, pOuterNC);
  if( pParse->nErr || pParse->db->mallocFailed ) return;
  sqlite3SelectAddTypeInfo(pParse, p);
}

5761
5762
5763
5764
5765
5766
5767



5768
5769
5770
5771
5772
5773
5774
  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));



#if SELECTTRACE_ENABLED
  SELECTTRACE(1,pParse,p, ("begin processing:\n", pParse->addrExplain));
  if( sqlite3SelectTrace & 0x100 ){
    sqlite3TreeViewSelect(0, p, 0);
  }
#endif








>
>
>







5777
5778
5779
5780
5781
5782
5783
5784
5785
5786
5787
5788
5789
5790
5791
5792
5793
  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 = SQLITE_AGGMAGIC_VALID;
#endif
#if SELECTTRACE_ENABLED
  SELECTTRACE(1,pParse,p, ("begin processing:\n", pParse->addrExplain));
  if( sqlite3SelectTrace & 0x100 ){
    sqlite3TreeViewSelect(0, p, 0);
  }
#endif

5783
5784
5785
5786
5787
5788
5789

5790
5791
5792
5793
5794
5795
5796
5797
5798
5799
5800

5801
5802
5803
5804
5805
5806
5807
5808
5809
5810
5811
5812
5813
5814
5815
5816
5817
5818
5819
5820
5821
5822
5823
5824
5825
           pDest->eDest==SRT_DistQueue || pDest->eDest==SRT_Fifo);
    /* If ORDER BY makes no difference in the output then neither does
    ** DISTINCT so it can be removed too. */
    sqlite3ExprListDelete(db, p->pOrderBy);
    p->pOrderBy = 0;
    p->selFlags &= ~SF_Distinct;
  }

  sqlite3SelectPrep(pParse, p, 0);
  if( pParse->nErr || db->mallocFailed ){
    goto select_end;
  }
  assert( p->pEList!=0 );
#if SELECTTRACE_ENABLED
  if( sqlite3SelectTrace & 0x104 ){
    SELECTTRACE(0x104,pParse,p, ("after name resolution:\n"));
    sqlite3TreeViewSelect(0, p, 0);
  }
#endif


  if( pDest->eDest==SRT_Output ){
    generateColumnNames(pParse, p);
  }

#ifndef SQLITE_OMIT_WINDOWFUNC
  rc = sqlite3WindowRewrite(pParse, p);
  if( rc ){
    assert( db->mallocFailed || pParse->nErr>0 );
    goto select_end;
  }
#if SELECTTRACE_ENABLED
  if( p->pWin && (sqlite3SelectTrace & 0x108)!=0 ){
    SELECTTRACE(0x104,pParse,p, ("after window rewrite:\n"));
    sqlite3TreeViewSelect(0, p, 0);
  }
#endif
#endif /* SQLITE_OMIT_WINDOWFUNC */
  pTabList = p->pSrc;
  isAgg = (p->selFlags & SF_Aggregate)!=0;
  memset(&sSort, 0, sizeof(sSort));
  sSort.pOrderBy = p->pOrderBy;

  /* Try to various optimizations (flattening subqueries, and strength
  ** reduction of join operators) in the FROM clause up into the main query







>
|
|
|
|
|

|
|
|
|

>





<
<
<
<
<
<
<
<
<
<
<
<
<







5802
5803
5804
5805
5806
5807
5808
5809
5810
5811
5812
5813
5814
5815
5816
5817
5818
5819
5820
5821
5822
5823
5824
5825
5826













5827
5828
5829
5830
5831
5832
5833
           pDest->eDest==SRT_DistQueue || pDest->eDest==SRT_Fifo);
    /* If ORDER BY makes no difference in the output then neither does
    ** DISTINCT so it can be removed too. */
    sqlite3ExprListDelete(db, p->pOrderBy);
    p->pOrderBy = 0;
    p->selFlags &= ~SF_Distinct;
  }
  if( (p->selFlags & SF_HasTypeInfo)==0 ){
    sqlite3SelectPrep(pParse, p, 0);
    if( pParse->nErr || db->mallocFailed ){
      goto select_end;
    }
    assert( p->pEList!=0 );
#if SELECTTRACE_ENABLED
    if( sqlite3SelectTrace & 0x104 ){
      SELECTTRACE(0x104,pParse,p, ("after name resolution:\n"));
      sqlite3TreeViewSelect(0, p, 0);
    }
#endif
  }

  if( pDest->eDest==SRT_Output ){
    generateColumnNames(pParse, p);
  }














  pTabList = p->pSrc;
  isAgg = (p->selFlags & SF_Aggregate)!=0;
  memset(&sSort, 0, sizeof(sSort));
  sSort.pOrderBy = p->pOrderBy;

  /* Try to various optimizations (flattening subqueries, and strength
  ** reduction of join operators) in the FROM clause up into the main query
6139
6140
6141
6142
6143
6144
6145
6146
6147
6148
6149
6150
6151
6152
6153
  ** used for both the ORDER BY and DISTINCT processing. As originally 
  ** written the query must use a temp-table for at least one of the ORDER 
  ** BY and DISTINCT, and an index or separate temp-table for the other.
  */
  if( (p->selFlags & (SF_Distinct|SF_Aggregate))==SF_Distinct 
   && sqlite3ExprListCompare(sSort.pOrderBy, pEList, -1)==0
#ifndef SQLITE_OMIT_WINDOWFUNC
   && p->pWin==0
#endif
  ){
    p->selFlags &= ~SF_Distinct;
    pGroupBy = p->pGroupBy = sqlite3ExprListDup(db, pEList, 0);
    p->selFlags |= SF_Aggregate;
    /* Notice that even thought SF_Distinct has been cleared from p->selFlags,
    ** the sDistinct.isTnct is still set.  Hence, isTnct represents the







|







6147
6148
6149
6150
6151
6152
6153
6154
6155
6156
6157
6158
6159
6160
6161
  ** used for both the ORDER BY and DISTINCT processing. As originally 
  ** written the query must use a temp-table for at least one of the ORDER 
  ** BY and DISTINCT, and an index or separate temp-table for the other.
  */
  if( (p->selFlags & (SF_Distinct|SF_Aggregate))==SF_Distinct 
   && sqlite3ExprListCompare(sSort.pOrderBy, pEList, -1)==0
#ifndef SQLITE_OMIT_WINDOWFUNC
   && ALWAYS(p->pWin==0)
#endif
  ){
    p->selFlags &= ~SF_Distinct;
    pGroupBy = p->pGroupBy = sqlite3ExprListDup(db, pEList, 0);
    p->selFlags |= SF_Aggregate;
    /* Notice that even thought SF_Distinct has been cleared from p->selFlags,
    ** the sDistinct.isTnct is still set.  Hence, isTnct represents the
6211
6212
6213
6214
6215
6216
6217









6218
6219
6220
6221
6222
6223
6224
6225
6226
6227
6228
6229
6230
6231
6232
6233
6234
6235
6236
6237
6238
6239
6240
                       (char*)sqlite3KeyInfoFromExprList(pParse, p->pEList,0,0),
                       P4_KEYINFO);
    sqlite3VdbeChangeP5(v, BTREE_UNORDERED);
    sDistinct.eTnctType = WHERE_DISTINCT_UNORDERED;
  }else{
    sDistinct.eTnctType = WHERE_DISTINCT_NOOP;
  }










  if( !isAgg && pGroupBy==0 ){
    /* No aggregate functions and no GROUP BY clause */
    u16 wctrlFlags = (sDistinct.isTnct ? WHERE_WANT_DISTINCT : 0)
                   | (p->selFlags & SF_FixedLimit);
#ifndef SQLITE_OMIT_WINDOWFUNC
    Window *pWin = p->pWin;      /* Master window object (or NULL) */
    if( pWin ){
      sqlite3WindowCodeInit(pParse, p);
    }
#endif
    assert( WHERE_USE_LIMIT==SF_FixedLimit );


    /* Begin the database scan. */
    SELECTTRACE(1,pParse,p,("WhereBegin\n"));
    pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, sSort.pOrderBy,
                               p->pEList, wctrlFlags, p->nSelectRow);
    if( pWInfo==0 ) goto select_end;
    if( sqlite3WhereOutputRowCount(pWInfo) < p->nSelectRow ){
      p->nSelectRow = sqlite3WhereOutputRowCount(pWInfo);
    }
    if( sDistinct.isTnct && sqlite3WhereIsDistinct(pWInfo) ){







>
>
>
>
>
>
>
>
>















|







6219
6220
6221
6222
6223
6224
6225
6226
6227
6228
6229
6230
6231
6232
6233
6234
6235
6236
6237
6238
6239
6240
6241
6242
6243
6244
6245
6246
6247
6248
6249
6250
6251
6252
6253
6254
6255
6256
6257
                       (char*)sqlite3KeyInfoFromExprList(pParse, p->pEList,0,0),
                       P4_KEYINFO);
    sqlite3VdbeChangeP5(v, BTREE_UNORDERED);
    sDistinct.eTnctType = WHERE_DISTINCT_UNORDERED;
  }else{
    sDistinct.eTnctType = WHERE_DISTINCT_NOOP;
  }

#ifndef SQLITE_OMIT_WINDOWFUNC
  /* By the time the Select object reaches this point, it will have been
  ** transformed (mostly by sqlite3WindowRewrite()) so it cannot be both
  ** a single-row aggregate and contain a window function.  In other words,
  ** if the Select has a window function then it may not have either an
  ** aggregate function or a GROUP BY clause. */
  assert( (!isAgg && pGroupBy==0) || p->pWin==0 );
#endif

  if( !isAgg && pGroupBy==0 ){
    /* No aggregate functions and no GROUP BY clause */
    u16 wctrlFlags = (sDistinct.isTnct ? WHERE_WANT_DISTINCT : 0)
                   | (p->selFlags & SF_FixedLimit);
#ifndef SQLITE_OMIT_WINDOWFUNC
    Window *pWin = p->pWin;      /* Master window object (or NULL) */
    if( pWin ){
      sqlite3WindowCodeInit(pParse, p);
    }
#endif
    assert( WHERE_USE_LIMIT==SF_FixedLimit );


    /* Begin the database scan. */
    SELECTTRACE(1,pParse,p,("WhereBegin-A\n"));
    pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, sSort.pOrderBy,
                               p->pEList, wctrlFlags, p->nSelectRow);
    if( pWInfo==0 ) goto select_end;
    if( sqlite3WhereOutputRowCount(pWInfo) < p->nSelectRow ){
      p->nSelectRow = sqlite3WhereOutputRowCount(pWInfo);
    }
    if( sDistinct.isTnct && sqlite3WhereIsDistinct(pWInfo) ){
6457
6458
6459
6460
6461
6462
6463
6464
6465
6466
6467
6468
6469
6470
6471

      /* Begin a loop that will extract all source rows in GROUP BY order.
      ** This might involve two separate loops with an OP_Sort in between, or
      ** it might be a single loop that uses an index to extract information
      ** in the right order to begin with.
      */
      sqlite3VdbeAddOp2(v, OP_Gosub, regReset, addrReset);
      SELECTTRACE(1,pParse,p,("WhereBegin\n"));
      pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, pGroupBy, 0,
          WHERE_GROUPBY | (orderByGrp ? WHERE_SORTBYGROUP : 0), 0
      );
      if( pWInfo==0 ) goto select_end;
      if( sqlite3WhereIsOrdered(pWInfo)==pGroupBy->nExpr ){
        /* The optimizer is able to deliver rows in group by order so
        ** we do not have to sort.  The OP_OpenEphemeral table will be







|







6474
6475
6476
6477
6478
6479
6480
6481
6482
6483
6484
6485
6486
6487
6488

      /* Begin a loop that will extract all source rows in GROUP BY order.
      ** This might involve two separate loops with an OP_Sort in between, or
      ** it might be a single loop that uses an index to extract information
      ** in the right order to begin with.
      */
      sqlite3VdbeAddOp2(v, OP_Gosub, regReset, addrReset);
      SELECTTRACE(1,pParse,p,("WhereBegin-B\n"));
      pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, pGroupBy, 0,
          WHERE_GROUPBY | (orderByGrp ? WHERE_SORTBYGROUP : 0), 0
      );
      if( pWInfo==0 ) goto select_end;
      if( sqlite3WhereIsOrdered(pWInfo)==pGroupBy->nExpr ){
        /* The optimizer is able to deliver rows in group by order so
        ** we do not have to sort.  The OP_OpenEphemeral table will be
6732
6733
6734
6735
6736
6737
6738
6739
6740
6741
6742
6743
6744
6745
6746
        ** minMaxFlag will have been previously set to either
        ** WHERE_ORDERBY_MIN or WHERE_ORDERBY_MAX and pMinMaxOrderBy will
        ** be an appropriate ORDER BY expression for the optimization.
        */
        assert( minMaxFlag==WHERE_ORDERBY_NORMAL || pMinMaxOrderBy!=0 );
        assert( pMinMaxOrderBy==0 || pMinMaxOrderBy->nExpr==1 );

        SELECTTRACE(1,pParse,p,("WhereBegin\n"));
        pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, pMinMaxOrderBy,
                                   0, minMaxFlag, 0);
        if( pWInfo==0 ){
          goto select_end;
        }
        updateAccumulator(pParse, regAcc, &sAggInfo);
        if( regAcc ) sqlite3VdbeAddOp2(v, OP_Integer, 1, regAcc);







|







6749
6750
6751
6752
6753
6754
6755
6756
6757
6758
6759
6760
6761
6762
6763
        ** minMaxFlag will have been previously set to either
        ** WHERE_ORDERBY_MIN or WHERE_ORDERBY_MAX and pMinMaxOrderBy will
        ** be an appropriate ORDER BY expression for the optimization.
        */
        assert( minMaxFlag==WHERE_ORDERBY_NORMAL || pMinMaxOrderBy!=0 );
        assert( pMinMaxOrderBy==0 || pMinMaxOrderBy->nExpr==1 );

        SELECTTRACE(1,pParse,p,("WhereBegin-C\n"));
        pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, pMinMaxOrderBy,
                                   0, minMaxFlag, 0);
        if( pWInfo==0 ){
          goto select_end;
        }
        updateAccumulator(pParse, regAcc, &sAggInfo);
        if( regAcc ) sqlite3VdbeAddOp2(v, OP_Integer, 1, regAcc);
6786
6787
6788
6789
6790
6791
6792










6793
6794
6795
6796
6797
6798
6799
6800
6801
6802

  /* Control jumps to here if an error is encountered above, or upon
  ** successful coding of the SELECT.
  */
select_end:
  sqlite3ExprListDelete(db, pMinMaxOrderBy);
  sqlite3DbFree(db, sAggInfo.aCol);










  sqlite3DbFree(db, sAggInfo.aFunc);
#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;
}







>
>
>
>
>
>
>
>
>
>










6803
6804
6805
6806
6807
6808
6809
6810
6811
6812
6813
6814
6815
6816
6817
6818
6819
6820
6821
6822
6823
6824
6825
6826
6827
6828
6829

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

Changes to src/sqliteInt.h.

972
973
974
975
976
977
978




979

980
981
982
983
984
985
986

/*
** SELECTTRACE_ENABLED will be either 1 or 0 depending on whether or not
** the Select query generator tracing logic is turned on.
*/
#if defined(SQLITE_ENABLE_SELECTTRACE)
# define SELECTTRACE_ENABLED 1




#else

# define SELECTTRACE_ENABLED 0
#endif

/*
** An instance of the following structure is used to store the busy-handler
** callback for a given sqlite handle.
**







>
>
>
>

>







972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991

/*
** SELECTTRACE_ENABLED will be either 1 or 0 depending on whether or not
** the Select query generator tracing logic is turned on.
*/
#if defined(SQLITE_ENABLE_SELECTTRACE)
# define SELECTTRACE_ENABLED 1
# define SELECTTRACE(K,P,S,X)  \
  if(sqlite3SelectTrace&(K))   \
    sqlite3DebugPrintf("%u/%d/%p: ",(S)->selId,(P)->addrExplain,(S)),\
    sqlite3DebugPrintf X
#else
# define SELECTTRACE(K,P,S,X)
# define SELECTTRACE_ENABLED 0
#endif

/*
** An instance of the following structure is used to store the busy-handler
** callback for a given sqlite handle.
**
2519
2520
2521
2522
2523
2524
2525



2526
2527












2528
2529
2530
2531
2532
2533
2534
  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[] */



};













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







>
>
>


>
>
>
>
>
>
>
>
>
>
>
>







2524
2525
2526
2527
2528
2529
2530
2531
2532
2533
2534
2535
2536
2537
2538
2539
2540
2541
2542
2543
2544
2545
2546
2547
2548
2549
2550
2551
2552
2553
2554
  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
  u32 iAggMagic;          /* Sanity checking constant */
#endif
};

/*
** Allowed values for AggInfo.iAggMagic
*/
#define SQLITE_AGGMAGIC_VALID  0x05cadade

/*
** True if the AggInfo object is valid.  Used inside of assert() only.
*/
#ifdef SQLITE_DEBUG
#  define AggInfoValid(P) ((P)->iAggMagic==SQLITE_AGGMAGIC_VALID)
#endif

/*
** 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
4222
4223
4224
4225
4226
4227
4228

4229
4230
4231
4232
4233
4234
4235
void sqlite3CreateIndex(Parse*,Token*,Token*,SrcList*,ExprList*,int,Token*,
                          Expr*, int, int, u8);
void sqlite3DropIndex(Parse*, SrcList*, int);
int sqlite3Select(Parse*, Select*, SelectDest*);
Select *sqlite3SelectNew(Parse*,ExprList*,SrcList*,Expr*,ExprList*,
                         Expr*,ExprList*,u32,Expr*);
void sqlite3SelectDelete(sqlite3*, Select*);

void sqlite3SelectReset(Parse*, Select*);
Table *sqlite3SrcListLookup(Parse*, SrcList*);
int sqlite3IsReadOnly(Parse*, Table*, int);
void sqlite3OpenTable(Parse*, int iCur, int iDb, Table*, int);
#if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY)
Expr *sqlite3LimitWhere(Parse*,SrcList*,Expr*,ExprList*,Expr*,char*);
#endif







>







4242
4243
4244
4245
4246
4247
4248
4249
4250
4251
4252
4253
4254
4255
4256
void sqlite3CreateIndex(Parse*,Token*,Token*,SrcList*,ExprList*,int,Token*,
                          Expr*, int, int, u8);
void sqlite3DropIndex(Parse*, SrcList*, int);
int sqlite3Select(Parse*, Select*, SelectDest*);
Select *sqlite3SelectNew(Parse*,ExprList*,SrcList*,Expr*,ExprList*,
                         Expr*,ExprList*,u32,Expr*);
void sqlite3SelectDelete(sqlite3*, Select*);
void sqlite3SelectDeferredDelete(Parse*, Select*);
void sqlite3SelectReset(Parse*, Select*);
Table *sqlite3SrcListLookup(Parse*, SrcList*);
int sqlite3IsReadOnly(Parse*, Table*, int);
void sqlite3OpenTable(Parse*, int iCur, int iDb, Table*, int);
#if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY)
Expr *sqlite3LimitWhere(Parse*,SrcList*,Expr*,ExprList*,Expr*,char*);
#endif
4542
4543
4544
4545
4546
4547
4548

4549
4550
4551
4552
4553
4554
4555
4556
4557
4558
4559
#ifndef SQLITE_AMALGAMATION
extern const unsigned char sqlite3OpcodeProperty[];
extern const char sqlite3StrBINARY[];
extern const unsigned char sqlite3UpperToLower[];
extern const unsigned char sqlite3CtypeMap[];
extern SQLITE_WSD struct Sqlite3Config sqlite3Config;
extern FuncDefHash sqlite3BuiltinFunctions;

#ifndef SQLITE_OMIT_WSD
extern int sqlite3PendingByte;
#endif
#endif
#ifdef VDBE_PROFILE
extern sqlite3_uint64 sqlite3NProfileCnt;
#endif
void sqlite3RootPageMoved(sqlite3*, int, int, int);
void sqlite3Reindex(Parse*, Token*, Token*);
void sqlite3AlterFunctions(void);
void sqlite3AlterRenameTable(Parse*, SrcList*, Token*);







>



|







4563
4564
4565
4566
4567
4568
4569
4570
4571
4572
4573
4574
4575
4576
4577
4578
4579
4580
4581
#ifndef SQLITE_AMALGAMATION
extern const unsigned char sqlite3OpcodeProperty[];
extern const char sqlite3StrBINARY[];
extern const unsigned char sqlite3UpperToLower[];
extern const unsigned char sqlite3CtypeMap[];
extern SQLITE_WSD struct Sqlite3Config sqlite3Config;
extern FuncDefHash sqlite3BuiltinFunctions;
extern u32 sqlite3SelectTrace;
#ifndef SQLITE_OMIT_WSD
extern int sqlite3PendingByte;
#endif
#endif /* !defined(SQLITE_AMALGAMATION) */
#ifdef VDBE_PROFILE
extern sqlite3_uint64 sqlite3NProfileCnt;
#endif
void sqlite3RootPageMoved(sqlite3*, int, int, int);
void sqlite3Reindex(Parse*, Token*, Token*);
void sqlite3AlterFunctions(void);
void sqlite3AlterRenameTable(Parse*, SrcList*, Token*);

Changes to src/test1.c.

8160
8161
8162
8163
8164
8165
8166
8167
8168
8169
8170
8171
8172
8173
8174
#endif
#ifdef SQLITE_TEST
#ifdef SQLITE_ENABLE_FTS3
  extern int sqlite3_fts3_enable_parentheses;
#endif
#endif
#if defined(SQLITE_ENABLE_SELECTTRACE)
  extern int sqlite3SelectTrace;
#endif

  for(i=0; i<sizeof(aCmd)/sizeof(aCmd[0]); i++){
    Tcl_CreateCommand(interp, aCmd[i].zName, aCmd[i].xProc, 0, 0);
  }
  for(i=0; i<sizeof(aObjCmd)/sizeof(aObjCmd[0]); i++){
    Tcl_CreateObjCommand(interp, aObjCmd[i].zName, 







|







8160
8161
8162
8163
8164
8165
8166
8167
8168
8169
8170
8171
8172
8173
8174
#endif
#ifdef SQLITE_TEST
#ifdef SQLITE_ENABLE_FTS3
  extern int sqlite3_fts3_enable_parentheses;
#endif
#endif
#if defined(SQLITE_ENABLE_SELECTTRACE)
  extern u32 sqlite3SelectTrace;
#endif

  for(i=0; i<sizeof(aCmd)/sizeof(aCmd[0]); i++){
    Tcl_CreateCommand(interp, aCmd[i].zName, aCmd[i].xProc, 0, 0);
  }
  for(i=0; i<sizeof(aObjCmd)/sizeof(aObjCmd[0]); i++){
    Tcl_CreateObjCommand(interp, aObjCmd[i].zName, 

Changes to src/treeview.c.

381
382
383
384
385
386
387
388
389

390
391
392
393
394
395
396

#ifndef SQLITE_OMIT_WINDOWFUNC
/*
** Generate a human-readable explanation for a Window Function object
*/
void sqlite3TreeViewWinFunc(TreeView *pView, const Window *pWin, u8 more){
  pView = sqlite3TreeViewPush(pView, more);
  sqlite3TreeViewLine(pView, "WINFUNC %s(%d)",
                       pWin->pFunc->zName, pWin->pFunc->nArg);

  sqlite3TreeViewWindow(pView, pWin, 0);
  sqlite3TreeViewPop(pView);
}
#endif /* SQLITE_OMIT_WINDOWFUNC */

/*
** Generate a human-readable explanation of an expression tree.







|
|
>







381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397

#ifndef SQLITE_OMIT_WINDOWFUNC
/*
** Generate a human-readable explanation for a Window Function object
*/
void sqlite3TreeViewWinFunc(TreeView *pView, const Window *pWin, u8 more){
  pView = sqlite3TreeViewPush(pView, more);
  sqlite3TreeViewLine(pView, "WINFUNC %s(%d) cur=%d",
                      pWin->pFunc->zName, pWin->pFunc->nArg,
                      pWin->iEphCsr);
  sqlite3TreeViewWindow(pView, pWin, 0);
  sqlite3TreeViewPop(pView);
}
#endif /* SQLITE_OMIT_WINDOWFUNC */

/*
** Generate a human-readable explanation of an expression tree.
592
593
594
595
596
597
598



599
600
601
602
603
604
605
        zOp2 = zBuf;
        if( pExpr->op2==NC_IsCheck ) zOp2 = "NC_IsCheck";
        if( pExpr->op2==NC_IdxExpr ) zOp2 = "NC_IdxExpr";
        if( pExpr->op2==NC_PartIdx ) zOp2 = "NC_PartIdx";
        if( pExpr->op2==NC_GenCol ) zOp2 = "NC_GenCol";
        sqlite3TreeViewLine(pView, "FUNCTION %Q%s op2=%s",
                            pExpr->u.zToken, zFlgs, zOp2);



      }else{
        sqlite3TreeViewLine(pView, "FUNCTION %Q%s", pExpr->u.zToken, zFlgs);
      }
      if( pFarg ){
        sqlite3TreeViewExprList(pView, pFarg, pWin!=0, 0);
      }
#ifndef SQLITE_OMIT_WINDOWFUNC







>
>
>







593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
        zOp2 = zBuf;
        if( pExpr->op2==NC_IsCheck ) zOp2 = "NC_IsCheck";
        if( pExpr->op2==NC_IdxExpr ) zOp2 = "NC_IdxExpr";
        if( pExpr->op2==NC_PartIdx ) zOp2 = "NC_PartIdx";
        if( pExpr->op2==NC_GenCol ) zOp2 = "NC_GenCol";
        sqlite3TreeViewLine(pView, "FUNCTION %Q%s op2=%s",
                            pExpr->u.zToken, zFlgs, zOp2);
      }else if( ExprHasProperty(pExpr, EP_WinFunc) ){
        sqlite3TreeViewLine(pView, "WINDOW-FUNCTION %Q%s cur=%d",
                            pExpr->u.zToken, zFlgs, pExpr->y.pWin->iEphCsr);
      }else{
        sqlite3TreeViewLine(pView, "FUNCTION %Q%s", pExpr->u.zToken, zFlgs);
      }
      if( pFarg ){
        sqlite3TreeViewExprList(pView, pFarg, pWin!=0, 0);
      }
#ifndef SQLITE_OMIT_WINDOWFUNC

Changes to src/window.c.

919
920
921
922
923
924
925





926
927
928
929
930
931





932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
/*
** When rewriting a query, if the new subquery in the FROM clause
** contains TK_AGG_FUNCTION nodes that refer to an outer query,
** then we have to increase the Expr->op2 values of those nodes
** due to the extra subquery layer that was added.
**
** See also the incrAggDepth() routine in resolve.c





*/
static int sqlite3WindowExtraAggFuncDepth(Walker *pWalker, Expr *pExpr){
  if( pExpr->op==TK_AGG_FUNCTION
   && pExpr->op2>=pWalker->walkerDepth
  ){
    pExpr->op2++;





  }
  return WRC_Continue;
}

/*
** If the SELECT statement passed as the second argument does not invoke
** any SQL window functions, this function is a no-op. Otherwise, it 
** rewrites the SELECT statement so that window function xStep functions
** are invoked in the correct order as described under "SELECT REWRITING"
** at the top of this file.
*/
int sqlite3WindowRewrite(Parse *pParse, Select *p){
  int rc = SQLITE_OK;
  if( p->pWin && p->pPrior==0 && (p->selFlags & SF_WinRewrite)==0 ){
    Vdbe *v = sqlite3GetVdbe(pParse);
    sqlite3 *db = pParse->db;
    Select *pSub = 0;             /* The subquery */
    SrcList *pSrc = p->pSrc;
    Expr *pWhere = p->pWhere;
    ExprList *pGroupBy = p->pGroupBy;
    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;
    u32 selFlags = p->selFlags;

    pTab = sqlite3DbMallocZero(db, sizeof(Table));
    if( pTab==0 ){
      return sqlite3ErrorToParser(db, SQLITE_NOMEM);
    }

    p->pSrc = 0;







>
>
>
>
>


|
|
<
|
>
>
>
>
>













|













<







919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934

935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967

968
969
970
971
972
973
974
/*
** When rewriting a query, if the new subquery in the FROM clause
** contains TK_AGG_FUNCTION nodes that refer to an outer query,
** then we have to increase the Expr->op2 values of those nodes
** due to the extra subquery layer that was added.
**
** See also the incrAggDepth() routine in resolve.c
**
** In addition to increasing pExpr->op2, set pWalker->eCode to 1 if
** any top-level aggregate functions are encountered.  This is needed
** to accurately set the SF_Aggregate flag on the new window-function
** subquery.
*/
static int sqlite3WindowExtraAggFuncDepth(Walker *pWalker, Expr *pExpr){
  if( pExpr->op==TK_AGG_FUNCTION ){
    if( pExpr->op2>=pWalker->walkerDepth ){

      pExpr->op2++;
    }else if( pExpr->op2==pWalker->walkerDepth-1 ){
      pWalker->eCode = 1;
    }
  }else if( ExprHasProperty(pExpr, EP_WinFunc) ){
    return WRC_Prune;
  }
  return WRC_Continue;
}

/*
** If the SELECT statement passed as the second argument does not invoke
** any SQL window functions, this function is a no-op. Otherwise, it 
** rewrites the SELECT statement so that window function xStep functions
** are invoked in the correct order as described under "SELECT REWRITING"
** at the top of this file.
*/
int sqlite3WindowRewrite(Parse *pParse, Select *p){
  int rc = SQLITE_OK;
  if( ALWAYS(p->pWin && (p->selFlags & SF_WinRewrite)==0) ){
    Vdbe *v = sqlite3GetVdbe(pParse);
    sqlite3 *db = pParse->db;
    Select *pSub = 0;             /* The subquery */
    SrcList *pSrc = p->pSrc;
    Expr *pWhere = p->pWhere;
    ExprList *pGroupBy = p->pGroupBy;
    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;


    pTab = sqlite3DbMallocZero(db, sizeof(Table));
    if( pTab==0 ){
      return sqlite3ErrorToParser(db, SQLITE_NOMEM);
    }

    p->pSrc = 0;
1018
1019
1020
1021
1022
1023
1024

1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041



1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058


1059
1060
1061
1062
1063
1064
1065

1066
1067
1068
1069
1070
1071
1072
      if( pWin->pFilter ){
        Expr *pFilter = sqlite3ExprDup(db, pWin->pFilter, 0);
        pSublist = sqlite3ExprListAppend(pParse, pSublist, pFilter);
      }
      pWin->regAccum = ++pParse->nMem;
      pWin->regResult = ++pParse->nMem;
      sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regAccum);

    }

    /* If there is no ORDER BY or PARTITION BY clause, and the window
    ** function accepts zero arguments, and there are no other columns
    ** selected (e.g. "SELECT row_number() OVER () FROM t1"), it is possible
    ** that pSublist is still NULL here. Add a constant expression here to 
    ** keep everything legal in this case. 
    */
    if( pSublist==0 ){
      pSublist = sqlite3ExprListAppend(pParse, 0, 
        sqlite3Expr(db, TK_INTEGER, "0")
      );
    }

    pSub = sqlite3SelectNew(
        pParse, pSublist, pSrc, pWhere, pGroupBy, pHaving, pSort, 0, 0
    );



    p->pSrc = sqlite3SrcListAppend(pParse, 0, 0, 0);
    if( p->pSrc ){
      Table *pTab2;
      Walker w;
      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
        ** pParse->nErr will be set, so if SQLITE_NOMEM is set, we will get
        ** the correct error message regardless. */
        rc = SQLITE_NOMEM;
      }else{
        memcpy(pTab, pTab2, sizeof(Table));
        pTab->tabFlags |= TF_Ephemeral;


        p->pSrc->a[0].pTab = pTab;
        pTab = pTab2;
        memset(&w, 0, sizeof(w));
        w.xExprCallback = sqlite3WindowExtraAggFuncDepth;
        w.xSelectCallback = sqlite3WalkerDepthIncrease;
        w.xSelectCallback2 = sqlite3WalkerDepthDecrease;
        sqlite3WalkSelect(&w, pSub);

      }
    }else{
      sqlite3SelectDelete(db, pSub);
    }
    if( db->mallocFailed ) rc = SQLITE_NOMEM;
    sqlite3DbFree(db, pTab);
  }







>

















>
>
>








<








>
>







>







1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061

1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
      if( pWin->pFilter ){
        Expr *pFilter = sqlite3ExprDup(db, pWin->pFilter, 0);
        pSublist = sqlite3ExprListAppend(pParse, pSublist, pFilter);
      }
      pWin->regAccum = ++pParse->nMem;
      pWin->regResult = ++pParse->nMem;
      sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regAccum);
      VdbeComment((v, "acc of %s()", pWin->pFunc->zName));
    }

    /* If there is no ORDER BY or PARTITION BY clause, and the window
    ** function accepts zero arguments, and there are no other columns
    ** selected (e.g. "SELECT row_number() OVER () FROM t1"), it is possible
    ** that pSublist is still NULL here. Add a constant expression here to 
    ** keep everything legal in this case. 
    */
    if( pSublist==0 ){
      pSublist = sqlite3ExprListAppend(pParse, 0, 
        sqlite3Expr(db, TK_INTEGER, "0")
      );
    }

    pSub = sqlite3SelectNew(
        pParse, pSublist, pSrc, pWhere, pGroupBy, pHaving, pSort, 0, 0
    );
    SELECTTRACE(0x1,pParse,p,
                ("allocate cursors %d..%d and subquery (%d/%p) to WINDOW %p\n",
                pMWin->iEphCsr, pParse->nTab-1, pSub->selId, pSub, pMWin));
    p->pSrc = sqlite3SrcListAppend(pParse, 0, 0, 0);
    if( p->pSrc ){
      Table *pTab2;
      Walker w;
      p->pSrc->a[0].pSelect = pSub;
      sqlite3SrcListAssignCursors(pParse, p->pSrc);
      pSub->selFlags |= SF_Expanded;
      pTab2 = sqlite3ResultSetOfSelect(pParse, pSub, SQLITE_AFF_NONE);

      if( pTab2==0 ){
        /* Might actually be some other kind of error, but in that case
        ** pParse->nErr will be set, so if SQLITE_NOMEM is set, we will get
        ** the correct error message regardless. */
        rc = SQLITE_NOMEM;
      }else{
        memcpy(pTab, pTab2, sizeof(Table));
        pTab->tabFlags |= TF_Ephemeral;
        assert( pTab->zName==0 );
        pTab->zName = sqlite3MPrintf(db, "<wintab-%d>", pMWin->iEphCsr);
        p->pSrc->a[0].pTab = pTab;
        pTab = pTab2;
        memset(&w, 0, sizeof(w));
        w.xExprCallback = sqlite3WindowExtraAggFuncDepth;
        w.xSelectCallback = sqlite3WalkerDepthIncrease;
        w.xSelectCallback2 = sqlite3WalkerDepthDecrease;
        sqlite3WalkSelect(&w, pSub);
        if( w.eCode && pSub->pWin==0 ) pSub->selFlags |= SF_Aggregate;
      }
    }else{
      sqlite3SelectDelete(db, pSub);
    }
    if( db->mallocFailed ) rc = SQLITE_NOMEM;
    sqlite3DbFree(db, pTab);
  }

Changes to test/fuzzdata8.db.

cannot compute difference between binary files

Changes to test/window1.test.

1738
1739
1740
1741
1742
1743
1744






























































































































1745
1746
1747
    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}
































































































































finish_test







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



1738
1739
1740
1741
1742
1743
1744
1745
1746
1747
1748
1749
1750
1751
1752
1753
1754
1755
1756
1757
1758
1759
1760
1761
1762
1763
1764
1765
1766
1767
1768
1769
1770
1771
1772
1773
1774
1775
1776
1777
1778
1779
1780
1781
1782
1783
1784
1785
1786
1787
1788
1789
1790
1791
1792
1793
1794
1795
1796
1797
1798
1799
1800
1801
1802
1803
1804
1805
1806
1807
1808
1809
1810
1811
1812
1813
1814
1815
1816
1817
1818
1819
1820
1821
1822
1823
1824
1825
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835
1836
1837
1838
1839
1840
1841
1842
1843
1844
1845
1846
1847
1848
1849
1850
1851
1852
1853
1854
1855
1856
1857
1858
1859
1860
1861
1862
1863
1864
1865
1866
1867
1868
1869
1870
1871
1872
1873
    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_execsql_test 54.2 {
  SELECT * FROM ( 
    SELECT sum(b) OVER() AS c FROM t1 
      UNION
    SELECT b AS c FROM t1
  ) WHERE c>10;
}

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

do_execsql_test 54.4 {
  SELECT * FROM ( 
    SELECT sum(b) OVER() AS c FROM t1 
      UNION
    SELECT b AS c FROM t1
  ) WHERE c>10;
} {15.0 30.0}

# 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_execsql_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;
} {5}

# 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()}}


finish_test