/ Check-in [d23f3316]
Login

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 Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

  1322   1322   ** The gatherSelectWindows() procedure and its helper routine
  1323   1323   ** gatherSelectWindowsCallback() are used to scan all the expressions
  1324   1324   ** an a newly duplicated SELECT statement and gather all of the Window
  1325   1325   ** objects found there, assembling them onto the linked list at Select->pWin.
  1326   1326   */
  1327   1327   static int gatherSelectWindowsCallback(Walker *pWalker, Expr *pExpr){
  1328   1328     if( pExpr->op==TK_FUNCTION && ExprHasProperty(pExpr, EP_WinFunc) ){
  1329         -    assert( pExpr->y.pWin );
         1329  +    Select *pSelect = pWalker->u.pSelect;
         1330  +    Window *pWin = pExpr->y.pWin;
         1331  +    assert( pWin );
  1330   1332       assert( IsWindowFunc(pExpr) );
  1331         -    pExpr->y.pWin->pNextWin = pWalker->u.pSelect->pWin;
  1332         -    pWalker->u.pSelect->pWin = pExpr->y.pWin;
         1333  +    if( pSelect->pWin ){
         1334  +      *pSelect->pWin->ppThis = pSelect->pWin->pNextWin;
         1335  +      pSelect->pWin->ppThis = &pWin->pNextWin;
         1336  +    }
         1337  +    pWin->pNextWin = pSelect->pWin;
         1338  +    pWin->ppThis = &pSelect->pWin;
         1339  +    pSelect->pWin = pWin;
  1333   1340     }
  1334   1341     return WRC_Continue;
  1335   1342   }
  1336   1343   static int gatherSelectWindowsSelectCallback(Walker *pWalker, Select *p){
  1337   1344     return p==pWalker->u.pSelect ? WRC_Continue : WRC_Prune;
  1338   1345   }
  1339   1346   static void gatherSelectWindows(Select *p){

Changes to src/resolve.c.

   897    897         }
   898    898   #endif
   899    899         sqlite3WalkExprList(pWalker, pList);
   900    900         if( is_agg ){
   901    901   #ifndef SQLITE_OMIT_WINDOWFUNC
   902    902           if( pWin ){
   903    903             Select *pSel = pNC->pWinSelect;
          904  +          assert( pWin==pExpr->y.pWin );
   904    905             if( IN_RENAME_OBJECT==0 ){
   905    906               sqlite3WindowUpdate(pParse, pSel->pWinDefn, pWin, pDef);
   906    907             }
   907    908             sqlite3WalkExprList(pWalker, pWin->pPartition);
   908    909             sqlite3WalkExprList(pWalker, pWin->pOrderBy);
   909    910             sqlite3WalkExpr(pWalker, pWin->pFilter);
   910    911             if( 0==pSel->pWin 
   911    912              || 0==sqlite3WindowCompare(pParse, pSel->pWin, pWin, 0)
   912    913             ){
   913         -            pExpr->y.pWin->pNextWin = pSel->pWin;
   914         -            pSel->pWin = pExpr->y.pWin;
          914  +            pWin->pNextWin = pSel->pWin;
          915  +            if( pSel->pWin ){
          916  +              pSel->pWin->ppThis = &pWin->pNextWin;
          917  +            }
          918  +            pSel->pWin = pWin;
          919  +            pWin->ppThis = &pSel->pWin;
   915    920             }
   916    921             pNC->ncFlags |= NC_HasWin;
   917    922           }else
   918    923   #endif /* SQLITE_OMIT_WINDOWFUNC */
   919    924           {
   920    925             NameContext *pNC2 = pNC;
   921    926             pExpr->op = TK_AGG_FUNCTION;
................................................................................
  1291   1296       }
  1292   1297     }
  1293   1298     return 0;
  1294   1299   }
  1295   1300   
  1296   1301   #ifndef SQLITE_OMIT_WINDOWFUNC
  1297   1302   /*
  1298         -** Walker callback for sqlite3WindowRemoveExprFromSelect() and
  1299         -** sqlite3WindowRemoveExprListFromSelect()
         1303  +** Walker callback for windowRemoveExprFromSelect().
  1300   1304   */
  1301   1305   static int resolveRemoveWindowsCb(Walker *pWalker, Expr *pExpr){
  1302   1306     if( ExprHasProperty(pExpr, EP_WinFunc) ){
  1303         -    Window **pp;
  1304         -    for(pp=&pWalker->u.pSelect->pWin; *pp; pp=&(*pp)->pNextWin){
  1305         -      if( *pp==pExpr->y.pWin ){
  1306         -        *pp = (*pp)->pNextWin;
  1307         -        break;
  1308         -      }    
         1307  +    Window *pWin = pExpr->y.pWin;
         1308  +    if( pWin->ppThis ){
         1309  +      *pWin->ppThis = pWin->pNextWin;
         1310  +      if( pWin->pNextWin ) pWin->pNextWin->ppThis = pWin->ppThis;
         1311  +      pWin->ppThis = 0;
  1309   1312       }
  1310   1313     }
  1311   1314     return WRC_Continue;
  1312   1315   }
  1313   1316   
  1314   1317   /*
  1315   1318   ** Remove any Window objects owned by the expression pExpr from the
  1316   1319   ** Select.pWin list of Select object pSelect.
  1317   1320   */
  1318         -void sqlite3WindowRemoveExprFromSelect(Select *pSelect, Expr *pExpr){
         1321  +static void windowRemoveExprFromSelect(Select *pSelect, Expr *pExpr){
  1319   1322     if( pSelect->pWin ){
  1320   1323       Walker sWalker;
  1321   1324       memset(&sWalker, 0, sizeof(Walker));
  1322   1325       sWalker.xExprCallback = resolveRemoveWindowsCb;
  1323   1326       sWalker.u.pSelect = pSelect;
  1324   1327       sqlite3WalkExpr(&sWalker, pExpr);
  1325   1328     }
  1326   1329   }
  1327         -
  1328         -/*
  1329         -** Remove any Window objects owned by the expression list from the
  1330         -** Select.pWin list of Select object pSelect.
  1331         -*/
  1332         -void sqlite3WindowRemoveExprListFromSelect(Select *pSelect, ExprList *pList){
  1333         -  if( pList && pSelect->pWin ){
  1334         -    int i;
  1335         -    Walker sWalker;
  1336         -    memset(&sWalker, 0, sizeof(Walker));
  1337         -    sWalker.xExprCallback = resolveRemoveWindowsCb;
  1338         -    sWalker.u.pSelect = pSelect;
  1339         -    for(i=0; i<pList->nExpr; i++){
  1340         -      sqlite3WalkExpr(&sWalker, pList->a[i].pExpr);
  1341         -    }
  1342         -  }
  1343         -}
         1330  +#else
         1331  +# define windowRemoveExprFromSelect(a, b)
  1344   1332   #endif /* SQLITE_OMIT_WINDOWFUNC */
  1345   1333   
  1346   1334   /*
  1347   1335   ** pOrderBy is an ORDER BY or GROUP BY clause in SELECT statement pSelect.
  1348   1336   ** The Name context of the SELECT statement is pNC.  zType is either
  1349   1337   ** "ORDER" or "GROUP" depending on which type of clause pOrderBy is.
  1350   1338   **
................................................................................
  1408   1396         return 1;
  1409   1397       }
  1410   1398       for(j=0; j<pSelect->pEList->nExpr; j++){
  1411   1399         if( sqlite3ExprCompare(0, pE, pSelect->pEList->a[j].pExpr, -1)==0 ){
  1412   1400           /* Since this expresion is being changed into a reference
  1413   1401           ** to an identical expression in the result set, remove all Window
  1414   1402           ** objects belonging to the expression from the Select.pWin list. */
  1415         -        sqlite3WindowRemoveExprFromSelect(pSelect, pE);
         1403  +        windowRemoveExprFromSelect(pSelect, pE);
  1416   1404           pItem->u.x.iOrderByCol = j+1;
  1417   1405         }
  1418   1406       }
  1419   1407     }
  1420   1408     return sqlite3ResolveOrderGroupBy(pParse, pSelect, pOrderBy, zType);
  1421   1409   }
  1422   1410   

Changes to src/select.c.

    98     98       sqlite3ExprDelete(db, p->pLimit);
    99     99   #ifndef SQLITE_OMIT_WINDOWFUNC
   100    100       if( OK_IF_ALWAYS_TRUE(p->pWinDefn) ){
   101    101         sqlite3WindowListDelete(db, p->pWinDefn);
   102    102       }
   103    103   #endif
   104    104       if( OK_IF_ALWAYS_TRUE(p->pWith) ) sqlite3WithDelete(db, p->pWith);
          105  +    assert( p->pWin==0 );
   105    106       if( bFree ) sqlite3DbFreeNN(db, p);
   106    107       p = pPrior;
   107    108       bFree = 1;
   108    109     }
   109    110   }
   110    111   
   111    112   /*
................................................................................
  5657   5658     if( IgnorableOrderby(pDest) ){
  5658   5659       assert(pDest->eDest==SRT_Exists || pDest->eDest==SRT_Union || 
  5659   5660              pDest->eDest==SRT_Except || pDest->eDest==SRT_Discard ||
  5660   5661              pDest->eDest==SRT_Queue  || pDest->eDest==SRT_DistFifo ||
  5661   5662              pDest->eDest==SRT_DistQueue || pDest->eDest==SRT_Fifo);
  5662   5663       /* If ORDER BY makes no difference in the output then neither does
  5663   5664       ** DISTINCT so it can be removed too. */
  5664         -    sqlite3WindowRemoveExprListFromSelect(p, p->pOrderBy);
  5665   5665       sqlite3ExprListDelete(db, p->pOrderBy);
  5666   5666       p->pOrderBy = 0;
  5667   5667       p->selFlags &= ~SF_Distinct;
  5668   5668     }
  5669   5669     sqlite3SelectPrep(pParse, p, 0);
  5670   5670     if( pParse->nErr || db->mallocFailed ){
  5671   5671       goto select_end;

Changes to src/sqliteInt.h.

  3590   3590     u8 eFrmType;            /* TK_RANGE, TK_GROUPS, TK_ROWS, or 0 */
  3591   3591     u8 eStart;              /* UNBOUNDED, CURRENT, PRECEDING or FOLLOWING */
  3592   3592     u8 eEnd;                /* UNBOUNDED, CURRENT, PRECEDING or FOLLOWING */
  3593   3593     u8 bImplicitFrame;      /* True if frame was implicitly specified */
  3594   3594     u8 eExclude;            /* TK_NO, TK_CURRENT, TK_TIES, TK_GROUP, or 0 */
  3595   3595     Expr *pStart;           /* Expression for "<expr> PRECEDING" */
  3596   3596     Expr *pEnd;             /* Expression for "<expr> FOLLOWING" */
         3597  +  Window **ppThis;        /* Pointer to this object in Select.pWin list */
  3597   3598     Window *pNextWin;       /* Next window function belonging to this SELECT */
  3598   3599     Expr *pFilter;          /* The FILTER expression */
  3599   3600     FuncDef *pFunc;         /* The function */
  3600   3601     int iEphCsr;            /* Partition buffer or Peer buffer */
  3601   3602     int regAccum;
  3602   3603     int regResult;
  3603   3604     int csrApp;             /* Function cursor (used by min/max) */
................................................................................
  3623   3624   int sqlite3ExpandSubquery(Parse*, struct SrcList_item*);
  3624   3625   void sqlite3WindowUpdate(Parse*, Window*, Window*, FuncDef*);
  3625   3626   Window *sqlite3WindowDup(sqlite3 *db, Expr *pOwner, Window *p);
  3626   3627   Window *sqlite3WindowListDup(sqlite3 *db, Window *p);
  3627   3628   void sqlite3WindowFunctions(void);
  3628   3629   void sqlite3WindowChain(Parse*, Window*, Window*);
  3629   3630   Window *sqlite3WindowAssemble(Parse*, Window*, ExprList*, ExprList*, Token*);
  3630         -void sqlite3WindowRemoveExprFromSelect(Select*,Expr*);
  3631         -void sqlite3WindowRemoveExprListFromSelect(Select*,ExprList*);
  3632   3631   #else
  3633   3632   # define sqlite3WindowDelete(a,b)
  3634   3633   # define sqlite3WindowFunctions()
  3635   3634   # define sqlite3WindowAttach(a,b,c)
  3636         -# define sqlite3WindowRemoveExprFromSelect(Select*,Expr*);
  3637         -# define sqlite3WindowRemoveExprListFromSelect(Select*,ExprList*);
  3638   3635   #endif
  3639   3636   
  3640   3637   /*
  3641   3638   ** Assuming zIn points to the first byte of a UTF-8 character,
  3642   3639   ** advance zIn to point to the first byte of the next UTF-8 character.
  3643   3640   */
  3644   3641   #define SQLITE_SKIP_UTF8(zIn) {                        \

Changes to src/window.c.

  1018   1018   }
  1019   1019   
  1020   1020   /*
  1021   1021   ** Free the Window object passed as the second argument.
  1022   1022   */
  1023   1023   void sqlite3WindowDelete(sqlite3 *db, Window *p){
  1024   1024     if( p ){
         1025  +    if( p->ppThis ){
         1026  +      *p->ppThis = p->pNextWin;
         1027  +      if( p->pNextWin ) p->pNextWin->ppThis = p->ppThis;
         1028  +    }
  1025   1029       sqlite3ExprDelete(db, p->pFilter);
  1026   1030       sqlite3ExprListDelete(db, p->pPartition);
  1027   1031       sqlite3ExprListDelete(db, p->pOrderBy);
  1028   1032       sqlite3ExprDelete(db, p->pEnd);
  1029   1033       sqlite3ExprDelete(db, p->pStart);
  1030   1034       sqlite3DbFree(db, p->zName);
  1031   1035       sqlite3DbFree(db, p->zBase);

Changes to test/window9.test.

    93     93   
    94     94   do_execsql_test 2.2.1 {
    95     95     SELECT b=='2' FROM t1
    96     96   } {1     0}
    97     97   do_execsql_test 2.2.2 {
    98     98     SELECT b=='2', rank() OVER (ORDER BY a) FROM t1
    99     99   } {1 1   0 2}
          100  +
          101  +#-------------------------------------------------------------------------
          102  +reset_db
          103  +do_execsql_test 3.0 {
          104  +  CREATE TABLE t1(a);
          105  +  CREATE TABLE t2(a,b,c);
          106  +}
          107  +
          108  +do_execsql_test 3.1 {
          109  +  SELECT EXISTS(SELECT 1 FROM t1 ORDER BY sum(a) OVER ()) FROM t1;
          110  +}
          111  +
          112  +do_execsql_test 3.2 {
          113  +  SELECT sum(a) OVER () FROM t2
          114  +   ORDER BY EXISTS(SELECT 1 FROM t2 ORDER BY sum(a) OVER ());
          115  +}
          116  +
          117  +do_catchsql_test 3.3 {
          118  +  SELECT a, sum(a) OVER (ORDER BY a DESC) FROM t2 
          119  +  ORDER BY EXISTS(
          120  +    SELECT 1 FROM t2 ORDER BY sum(a) OVER (ORDER BY a)
          121  +  ) OVER (ORDER BY a);
          122  +} {1 {near "OVER": syntax error}}
          123  +
   100    124   
   101    125   finish_test
   102    126