/ Check-in [d23f3316]
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:Remove Window objects from the corresponding Select.pWin list when they are deleted.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | winfunc-in-orderby
Files: files | file ages | folders
SHA3-256: d23f33168222dfa40a67dc7de58057418151989e81429e4af47617e86db04667
User & Date: dan 2019-07-22 16:20:03
Context
2019-07-22
16:33
Remove Window objects from the corresponding Select.pWin list when they are deleted, as they are, for example, when the ORDER BY clause is optimized out. check-in: fd7316cd user: dan tags: trunk
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
Changes
Hide Diffs Unified Diffs Show Whitespace Changes Patch

Changes to src/expr.c.

1322
1323
1324
1325
1326
1327
1328

1329

1330




1331

1332
1333
1334
1335
1336
1337
1338
1339
** The gatherSelectWindows() procedure and its helper routine
** gatherSelectWindowsCallback() are used to scan all the expressions
** an a newly duplicated SELECT statement and gather all of the Window
** objects found there, assembling them onto the linked list at Select->pWin.
*/
static int gatherSelectWindowsCallback(Walker *pWalker, Expr *pExpr){
  if( pExpr->op==TK_FUNCTION && ExprHasProperty(pExpr, EP_WinFunc) ){

    assert( pExpr->y.pWin );

    assert( IsWindowFunc(pExpr) );




    pExpr->y.pWin->pNextWin = pWalker->u.pSelect->pWin;

    pWalker->u.pSelect->pWin = pExpr->y.pWin;
  }
  return WRC_Continue;
}
static int gatherSelectWindowsSelectCallback(Walker *pWalker, Select *p){
  return p==pWalker->u.pSelect ? WRC_Continue : WRC_Prune;
}
static void gatherSelectWindows(Select *p){







>
|
>

>
>
>
>
|
>
|







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
** The gatherSelectWindows() procedure and its helper routine
** gatherSelectWindowsCallback() are used to scan all the expressions
** an a newly duplicated SELECT statement and gather all of the Window
** objects found there, assembling them onto the linked list at Select->pWin.
*/
static int gatherSelectWindowsCallback(Walker *pWalker, Expr *pExpr){
  if( pExpr->op==TK_FUNCTION && ExprHasProperty(pExpr, EP_WinFunc) ){
    Select *pSelect = pWalker->u.pSelect;
    Window *pWin = pExpr->y.pWin;
    assert( pWin );
    assert( IsWindowFunc(pExpr) );
    if( pSelect->pWin ){
      *pSelect->pWin->ppThis = pSelect->pWin->pNextWin;
      pSelect->pWin->ppThis = &pWin->pNextWin;
    }
    pWin->pNextWin = pSelect->pWin;
    pWin->ppThis = &pSelect->pWin;
    pSelect->pWin = pWin;
  }
  return WRC_Continue;
}
static int gatherSelectWindowsSelectCallback(Walker *pWalker, Select *p){
  return p==pWalker->u.pSelect ? WRC_Continue : WRC_Prune;
}
static void gatherSelectWindows(Select *p){

Changes to src/resolve.c.

897
898
899
900
901
902
903

904
905
906
907
908
909
910
911
912
913



914

915
916
917
918
919
920
921
....
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
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
....
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
      }
#endif
      sqlite3WalkExprList(pWalker, pList);
      if( is_agg ){
#ifndef SQLITE_OMIT_WINDOWFUNC
        if( pWin ){
          Select *pSel = pNC->pWinSelect;

          if( IN_RENAME_OBJECT==0 ){
            sqlite3WindowUpdate(pParse, pSel->pWinDefn, pWin, pDef);
          }
          sqlite3WalkExprList(pWalker, pWin->pPartition);
          sqlite3WalkExprList(pWalker, pWin->pOrderBy);
          sqlite3WalkExpr(pWalker, pWin->pFilter);
          if( 0==pSel->pWin 
           || 0==sqlite3WindowCompare(pParse, pSel->pWin, pWin, 0)
          ){
            pExpr->y.pWin->pNextWin = pSel->pWin;



            pSel->pWin = pExpr->y.pWin;

          }
          pNC->ncFlags |= NC_HasWin;
        }else
#endif /* SQLITE_OMIT_WINDOWFUNC */
        {
          NameContext *pNC2 = pNC;
          pExpr->op = TK_AGG_FUNCTION;
................................................................................
    }
  }
  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;
        break;
      }    
    }
  }
  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.
**
................................................................................
      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);
}








>









|
>
>
>
|
>







 







|
<



|
|
|
|
|
<









|








<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
>
>







 







|







897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
....
1296
1297
1298
1299
1300
1301
1302
1303

1304
1305
1306
1307
1308
1309
1310
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
....
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
      }
#endif
      sqlite3WalkExprList(pWalker, pList);
      if( is_agg ){
#ifndef SQLITE_OMIT_WINDOWFUNC
        if( pWin ){
          Select *pSel = pNC->pWinSelect;
          assert( pWin==pExpr->y.pWin );
          if( IN_RENAME_OBJECT==0 ){
            sqlite3WindowUpdate(pParse, pSel->pWinDefn, pWin, pDef);
          }
          sqlite3WalkExprList(pWalker, pWin->pPartition);
          sqlite3WalkExprList(pWalker, pWin->pOrderBy);
          sqlite3WalkExpr(pWalker, pWin->pFilter);
          if( 0==pSel->pWin 
           || 0==sqlite3WindowCompare(pParse, pSel->pWin, pWin, 0)
          ){
            pWin->pNextWin = pSel->pWin;
            if( pSel->pWin ){
              pSel->pWin->ppThis = &pWin->pNextWin;
            }
            pSel->pWin = pWin;
            pWin->ppThis = &pSel->pWin;
          }
          pNC->ncFlags |= NC_HasWin;
        }else
#endif /* SQLITE_OMIT_WINDOWFUNC */
        {
          NameContext *pNC2 = pNC;
          pExpr->op = TK_AGG_FUNCTION;
................................................................................
    }
  }
  return 0;
}

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

*/
static int resolveRemoveWindowsCb(Walker *pWalker, Expr *pExpr){
  if( ExprHasProperty(pExpr, EP_WinFunc) ){
    Window *pWin = pExpr->y.pWin;
    if( pWin->ppThis ){
      *pWin->ppThis = pWin->pNextWin;
      if( pWin->pNextWin ) pWin->pNextWin->ppThis = pWin->ppThis;
      pWin->ppThis = 0;

    }
  }
  return WRC_Continue;
}

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

















#else
# define windowRemoveExprFromSelect(a, b)
#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.
**
................................................................................
      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. */
        windowRemoveExprFromSelect(pSelect, pE);
        pItem->u.x.iOrderByCol = j+1;
      }
    }
  }
  return sqlite3ResolveOrderGroupBy(pParse, pSelect, pOrderBy, zType);
}

Changes to src/select.c.

98
99
100
101
102
103
104

105
106
107
108
109
110
111
....
5657
5658
5659
5660
5661
5662
5663
5664
5665
5666
5667
5668
5669
5670
5671
    sqlite3ExprDelete(db, p->pLimit);
#ifndef SQLITE_OMIT_WINDOWFUNC
    if( OK_IF_ALWAYS_TRUE(p->pWinDefn) ){
      sqlite3WindowListDelete(db, p->pWinDefn);
    }
#endif
    if( OK_IF_ALWAYS_TRUE(p->pWith) ) sqlite3WithDelete(db, p->pWith);

    if( bFree ) sqlite3DbFreeNN(db, p);
    p = pPrior;
    bFree = 1;
  }
}

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







>







 







<







98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
....
5658
5659
5660
5661
5662
5663
5664

5665
5666
5667
5668
5669
5670
5671
    sqlite3ExprDelete(db, p->pLimit);
#ifndef SQLITE_OMIT_WINDOWFUNC
    if( OK_IF_ALWAYS_TRUE(p->pWinDefn) ){
      sqlite3WindowListDelete(db, p->pWinDefn);
    }
#endif
    if( OK_IF_ALWAYS_TRUE(p->pWith) ) sqlite3WithDelete(db, p->pWith);
    assert( p->pWin==0 );
    if( bFree ) sqlite3DbFreeNN(db, p);
    p = pPrior;
    bFree = 1;
  }
}

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

Changes to src/sqliteInt.h.

3590
3591
3592
3593
3594
3595
3596

3597
3598
3599
3600
3601
3602
3603
....
3623
3624
3625
3626
3627
3628
3629
3630
3631
3632
3633
3634
3635
3636
3637
3638
3639
3640
3641
3642
3643
3644
  u8 eFrmType;            /* TK_RANGE, TK_GROUPS, TK_ROWS, or 0 */
  u8 eStart;              /* UNBOUNDED, CURRENT, PRECEDING or FOLLOWING */
  u8 eEnd;                /* UNBOUNDED, CURRENT, PRECEDING or FOLLOWING */
  u8 bImplicitFrame;      /* True if frame was implicitly specified */
  u8 eExclude;            /* TK_NO, TK_CURRENT, TK_TIES, TK_GROUP, or 0 */
  Expr *pStart;           /* Expression for "<expr> PRECEDING" */
  Expr *pEnd;             /* Expression for "<expr> FOLLOWING" */

  Window *pNextWin;       /* Next window function belonging to this SELECT */
  Expr *pFilter;          /* The FILTER expression */
  FuncDef *pFunc;         /* The function */
  int iEphCsr;            /* Partition buffer or Peer buffer */
  int regAccum;
  int regResult;
  int csrApp;             /* Function cursor (used by min/max) */
................................................................................
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) {                        \







>







 







<
<




<
<







3590
3591
3592
3593
3594
3595
3596
3597
3598
3599
3600
3601
3602
3603
3604
....
3624
3625
3626
3627
3628
3629
3630


3631
3632
3633
3634


3635
3636
3637
3638
3639
3640
3641
  u8 eFrmType;            /* TK_RANGE, TK_GROUPS, TK_ROWS, or 0 */
  u8 eStart;              /* UNBOUNDED, CURRENT, PRECEDING or FOLLOWING */
  u8 eEnd;                /* UNBOUNDED, CURRENT, PRECEDING or FOLLOWING */
  u8 bImplicitFrame;      /* True if frame was implicitly specified */
  u8 eExclude;            /* TK_NO, TK_CURRENT, TK_TIES, TK_GROUP, or 0 */
  Expr *pStart;           /* Expression for "<expr> PRECEDING" */
  Expr *pEnd;             /* Expression for "<expr> FOLLOWING" */
  Window **ppThis;        /* Pointer to this object in Select.pWin list */
  Window *pNextWin;       /* Next window function belonging to this SELECT */
  Expr *pFilter;          /* The FILTER expression */
  FuncDef *pFunc;         /* The function */
  int iEphCsr;            /* Partition buffer or Peer buffer */
  int regAccum;
  int regResult;
  int csrApp;             /* Function cursor (used by min/max) */
................................................................................
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) {                        \

Changes to src/window.c.

1018
1019
1020
1021
1022
1023
1024




1025
1026
1027
1028
1029
1030
1031
}

/*
** Free the Window object passed as the second argument.
*/
void sqlite3WindowDelete(sqlite3 *db, Window *p){
  if( p ){




    sqlite3ExprDelete(db, p->pFilter);
    sqlite3ExprListDelete(db, p->pPartition);
    sqlite3ExprListDelete(db, p->pOrderBy);
    sqlite3ExprDelete(db, p->pEnd);
    sqlite3ExprDelete(db, p->pStart);
    sqlite3DbFree(db, p->zName);
    sqlite3DbFree(db, p->zBase);







>
>
>
>







1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
}

/*
** Free the Window object passed as the second argument.
*/
void sqlite3WindowDelete(sqlite3 *db, Window *p){
  if( p ){
    if( p->ppThis ){
      *p->ppThis = p->pNextWin;
      if( p->pNextWin ) p->pNextWin->ppThis = p->ppThis;
    }
    sqlite3ExprDelete(db, p->pFilter);
    sqlite3ExprListDelete(db, p->pPartition);
    sqlite3ExprListDelete(db, p->pOrderBy);
    sqlite3ExprDelete(db, p->pEnd);
    sqlite3ExprDelete(db, p->pStart);
    sqlite3DbFree(db, p->zName);
    sqlite3DbFree(db, p->zBase);

Changes to test/window9.test.

93
94
95
96
97
98
99
100
























101
102

do_execsql_test 2.2.1 {
  SELECT b=='2' FROM t1
} {1     0}
do_execsql_test 2.2.2 {
  SELECT b=='2', rank() OVER (ORDER BY a) FROM t1
} {1 1   0 2}

























finish_test









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


93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126

do_execsql_test 2.2.1 {
  SELECT b=='2' FROM t1
} {1     0}
do_execsql_test 2.2.2 {
  SELECT b=='2', rank() OVER (ORDER BY a) FROM t1
} {1 1   0 2}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 3.0 {
  CREATE TABLE t1(a);
  CREATE TABLE t2(a,b,c);
}

do_execsql_test 3.1 {
  SELECT EXISTS(SELECT 1 FROM t1 ORDER BY sum(a) OVER ()) FROM t1;
}

do_execsql_test 3.2 {
  SELECT sum(a) OVER () FROM t2
   ORDER BY EXISTS(SELECT 1 FROM t2 ORDER BY sum(a) OVER ());
}

do_catchsql_test 3.3 {
  SELECT a, sum(a) OVER (ORDER BY a DESC) FROM t2 
  ORDER BY EXISTS(
    SELECT 1 FROM t2 ORDER BY sum(a) OVER (ORDER BY a)
  ) OVER (ORDER BY a);
} {1 {near "OVER": syntax error}}


finish_test