/ Check-in [23b11967]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Make sure any window definitions in an ORDER BY clause are removed from the SELECT statement if the ORDER BY clause gets optimized out.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | winfunc-in-orderby
Files: files | file ages | folders
SHA3-256: 23b119671f0be3c6b72cf2dc5f7707a0626766db7aa56529ab00d33d1a0a1bee
User & Date: drh 2019-07-20 21:12:31
Context
2019-07-22
16:20
Remove Window objects from the corresponding Select.pWin list when they are deleted. Closed-Leaf check-in: d23f3316 user: dan tags: winfunc-in-orderby
2019-07-20
21:12
Make sure any window definitions in an ORDER BY clause are removed from the SELECT statement if the ORDER BY clause gets optimized out. check-in: 23b11967 user: drh tags: winfunc-in-orderby
18:00
Early detection of a corrupt R-Tree in the R-Tree search algorithm. check-in: 2bf5a4c1 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/resolve.c.

1291
1292
1293
1294
1295
1296
1297
1298

1299
1300
1301
1302
1303
1304
1305
....
1310
1311
1312
1313
1314
1315
1316
1317

1318
1319
1320
1321
1322
1323
1324











1325
1326







1327
1328
1329
1330
1331
1332
1333
....
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
    }
  }
  return 0;
}

#ifndef SQLITE_OMIT_WINDOWFUNC
/*
** Walker callback for resolveRemoveWindows().

*/
static int resolveRemoveWindowsCb(Walker *pWalker, Expr *pExpr){
  if( ExprHasProperty(pExpr, EP_WinFunc) ){
    Window **pp;
    for(pp=&pWalker->u.pSelect->pWin; *pp; pp=&(*pp)->pNextWin){
      if( *pp==pExpr->y.pWin ){
        *pp = (*pp)->pNextWin;
................................................................................
  return WRC_Continue;
}

/*
** Remove any Window objects owned by the expression pExpr from the
** Select.pWin list of Select object pSelect.
*/
static void resolveRemoveWindows(Select *pSelect, Expr *pExpr){

  Walker sWalker;
  memset(&sWalker, 0, sizeof(Walker));
  sWalker.xExprCallback = resolveRemoveWindowsCb;
  sWalker.u.pSelect = pSelect;
  sqlite3WalkExpr(&sWalker, pExpr);
}
#else











# define resolveRemoveWindows(x,y)
#endif








/*
** pOrderBy is an ORDER BY or GROUP BY clause in SELECT statement pSelect.
** The Name context of the SELECT statement is pNC.  zType is either
** "ORDER" or "GROUP" depending on which type of clause pOrderBy is.
**
** This routine resolves each term of the clause into an expression.
................................................................................
      return 1;
    }
    for(j=0; j<pSelect->pEList->nExpr; j++){
      if( sqlite3ExprCompare(0, pE, pSelect->pEList->a[j].pExpr, -1)==0 ){
        /* Since this expresion is being changed into a reference
        ** to an identical expression in the result set, remove all Window
        ** objects belonging to the expression from the Select.pWin list. */
        resolveRemoveWindows(pSelect, pE);
        pItem->u.x.iOrderByCol = j+1;
      }
    }
  }
  return sqlite3ResolveOrderGroupBy(pParse, pSelect, pOrderBy, zType);
}








|
>







 







|
>
|
|
|
|
|
|
<
>
>
>
>
>
>
>
>
>
>
>
|
<
>
>
>
>
>
>
>







 







|







1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
....
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325

1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337

1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
....
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
    }
  }
  return 0;
}

#ifndef SQLITE_OMIT_WINDOWFUNC
/*
** Walker callback for sqlite3WindowRemoveExprFromSelect() and
** sqlite3WindowRemoveExprListFromSelect()
*/
static int resolveRemoveWindowsCb(Walker *pWalker, Expr *pExpr){
  if( ExprHasProperty(pExpr, EP_WinFunc) ){
    Window **pp;
    for(pp=&pWalker->u.pSelect->pWin; *pp; pp=&(*pp)->pNextWin){
      if( *pp==pExpr->y.pWin ){
        *pp = (*pp)->pNextWin;
................................................................................
  return WRC_Continue;
}

/*
** Remove any Window objects owned by the expression pExpr from the
** Select.pWin list of Select object pSelect.
*/
void sqlite3WindowRemoveExprFromSelect(Select *pSelect, Expr *pExpr){
  if( pSelect->pWin ){
    Walker sWalker;
    memset(&sWalker, 0, sizeof(Walker));
    sWalker.xExprCallback = resolveRemoveWindowsCb;
    sWalker.u.pSelect = pSelect;
    sqlite3WalkExpr(&sWalker, pExpr);
  }

}

/*
** Remove any Window objects owned by the expression list from the
** Select.pWin list of Select object pSelect.
*/
void sqlite3WindowRemoveExprListFromSelect(Select *pSelect, ExprList *pList){
  if( pList && pSelect->pWin ){
    int i;
    Walker sWalker;
    memset(&sWalker, 0, sizeof(Walker));
    sWalker.xExprCallback = resolveRemoveWindowsCb;

    sWalker.u.pSelect = pSelect;
    for(i=0; i<pList->nExpr; i++){
      sqlite3WalkExpr(&sWalker, pList->a[i].pExpr);
    }
  }
}
#endif /* SQLITE_OMIT_WINDOWFUNC */

/*
** pOrderBy is an ORDER BY or GROUP BY clause in SELECT statement pSelect.
** The Name context of the SELECT statement is pNC.  zType is either
** "ORDER" or "GROUP" depending on which type of clause pOrderBy is.
**
** This routine resolves each term of the clause into an expression.
................................................................................
      return 1;
    }
    for(j=0; j<pSelect->pEList->nExpr; j++){
      if( sqlite3ExprCompare(0, pE, pSelect->pEList->a[j].pExpr, -1)==0 ){
        /* Since this expresion is being changed into a reference
        ** to an identical expression in the result set, remove all Window
        ** objects belonging to the expression from the Select.pWin list. */
        sqlite3WindowRemoveExprFromSelect(pSelect, pE);
        pItem->u.x.iOrderByCol = j+1;
      }
    }
  }
  return sqlite3ResolveOrderGroupBy(pParse, pSelect, pOrderBy, zType);
}

Changes to src/select.c.

5657
5658
5659
5660
5661
5662
5663

5664
5665
5666
5667
5668
5669
5670
  if( IgnorableOrderby(pDest) ){
    assert(pDest->eDest==SRT_Exists || pDest->eDest==SRT_Union || 
           pDest->eDest==SRT_Except || pDest->eDest==SRT_Discard ||
           pDest->eDest==SRT_Queue  || pDest->eDest==SRT_DistFifo ||
           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;







>







5657
5658
5659
5660
5661
5662
5663
5664
5665
5666
5667
5668
5669
5670
5671
  if( IgnorableOrderby(pDest) ){
    assert(pDest->eDest==SRT_Exists || pDest->eDest==SRT_Union || 
           pDest->eDest==SRT_Except || pDest->eDest==SRT_Discard ||
           pDest->eDest==SRT_Queue  || pDest->eDest==SRT_DistFifo ||
           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. */
    sqlite3WindowRemoveExprListFromSelect(p, p->pOrderBy);
    sqlite3ExprListDelete(db, p->pOrderBy);
    p->pOrderBy = 0;
    p->selFlags &= ~SF_Distinct;
  }
  sqlite3SelectPrep(pParse, p, 0);
  if( pParse->nErr || db->mallocFailed ){
    goto select_end;

Changes to src/sqliteInt.h.

3623
3624
3625
3626
3627
3628
3629


3630
3631
3632
3633


3634
3635
3636
3637
3638
3639
3640
int sqlite3ExpandSubquery(Parse*, struct SrcList_item*);
void sqlite3WindowUpdate(Parse*, Window*, Window*, FuncDef*);
Window *sqlite3WindowDup(sqlite3 *db, Expr *pOwner, Window *p);
Window *sqlite3WindowListDup(sqlite3 *db, Window *p);
void sqlite3WindowFunctions(void);
void sqlite3WindowChain(Parse*, Window*, Window*);
Window *sqlite3WindowAssemble(Parse*, Window*, ExprList*, ExprList*, Token*);


#else
# define sqlite3WindowDelete(a,b)
# define sqlite3WindowFunctions()
# define sqlite3WindowAttach(a,b,c)


#endif

/*
** Assuming zIn points to the first byte of a UTF-8 character,
** advance zIn to point to the first byte of the next UTF-8 character.
*/
#define SQLITE_SKIP_UTF8(zIn) {                        \







>
>




>
>







3623
3624
3625
3626
3627
3628
3629
3630
3631
3632
3633
3634
3635
3636
3637
3638
3639
3640
3641
3642
3643
3644
int sqlite3ExpandSubquery(Parse*, struct SrcList_item*);
void sqlite3WindowUpdate(Parse*, Window*, Window*, FuncDef*);
Window *sqlite3WindowDup(sqlite3 *db, Expr *pOwner, Window *p);
Window *sqlite3WindowListDup(sqlite3 *db, Window *p);
void sqlite3WindowFunctions(void);
void sqlite3WindowChain(Parse*, Window*, Window*);
Window *sqlite3WindowAssemble(Parse*, Window*, ExprList*, ExprList*, Token*);
void sqlite3WindowRemoveExprFromSelect(Select*,Expr*);
void sqlite3WindowRemoveExprListFromSelect(Select*,ExprList*);
#else
# define sqlite3WindowDelete(a,b)
# define sqlite3WindowFunctions()
# define sqlite3WindowAttach(a,b,c)
# define sqlite3WindowRemoveExprFromSelect(Select*,Expr*);
# define sqlite3WindowRemoveExprListFromSelect(Select*,ExprList*);
#endif

/*
** Assuming zIn points to the first byte of a UTF-8 character,
** advance zIn to point to the first byte of the next UTF-8 character.
*/
#define SQLITE_SKIP_UTF8(zIn) {                        \