/ Changes On Branch winfunc-in-orderby
Login

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

Changes In Branch winfunc-in-orderby Excluding Merge-Ins

This is equivalent to a diff from 2bf5a4c164 to d23f331682

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: fd7316cda0 user: dan tags: trunk)
16:20
Remove Window objects from the corresponding Select.pWin list when they are deleted. (Closed-Leaf check-in: d23f331682 user: dan tags: winfunc-in-orderby)
11:38
Fix a problem with renaming tables when the schema contains an invocation of a currently unregistered aggregate with a FILTER clause. (check-in: bd37ce3fb8 user: dan tags: trunk)
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: 23b119671f user: drh tags: winfunc-in-orderby)
18:00
Early detection of a corrupt R-Tree in the R-Tree search algorithm. (check-in: 2bf5a4c164 user: drh tags: trunk)
17:43
Performance optimizations in the rtreeStepToLeaf() routine of RTree. (check-in: 4e34e3615f user: drh tags: trunk)

Changes to src/expr.c.

1322
1323
1324
1325
1326
1327
1328


1329

1330




1331
1332



1333
1334
1335
1336
1337
1338
1339
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( pExpr->y.pWin );
    assert( pWin );
    assert( IsWindowFunc(pExpr) );
    if( pSelect->pWin ){
      *pSelect->pWin->ppThis = pSelect->pWin->pNextWin;
      pSelect->pWin->ppThis = &pWin->pNextWin;
    }
    pExpr->y.pWin->pNextWin = pWalker->u.pSelect->pWin;
    pWalker->u.pSelect->pWin = pExpr->y.pWin;
    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
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







+









-
-
+
+
+
+
+
+







      }
#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)
          ){
            pExpr->y.pWin->pNextWin = pSel->pWin;
            pSel->pWin = pExpr->y.pWin;
            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;
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
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







-
+



-
+
-
-
-
+
+
-
-
+
+









-
-
-
-
-
-
-
+
+
+
+
+
+
+
+
+

-
-
+
+







    }
  }
  return 0;
}

#ifndef SQLITE_OMIT_WINDOWFUNC
/*
** Walker callback for resolveRemoveWindows().
** Walker callback for windowRemoveExprFromSelect().
*/
static int resolveRemoveWindowsCb(Walker *pWalker, Expr *pExpr){
  if( ExprHasProperty(pExpr, EP_WinFunc) ){
    Window **pp;
    Window *pWin = pExpr->y.pWin;
    for(pp=&pWalker->u.pSelect->pWin; *pp; pp=&(*pp)->pNextWin){
      if( *pp==pExpr->y.pWin ){
        *pp = (*pp)->pNextWin;
    if( pWin->ppThis ){
      *pWin->ppThis = pWin->pNextWin;
        break;
      }    
      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 resolveRemoveWindows(Select *pSelect, Expr *pExpr){
  Walker sWalker;
  memset(&sWalker, 0, sizeof(Walker));
  sWalker.xExprCallback = resolveRemoveWindowsCb;
  sWalker.u.pSelect = pSelect;
  sqlite3WalkExpr(&sWalker, pExpr);
}
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 resolveRemoveWindows(x,y)
#endif
# 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.
**
** This routine resolves each term of the clause into an expression.
1390
1391
1392
1393
1394
1395
1396
1397

1398
1399
1400
1401
1402
1403
1404
1396
1397
1398
1399
1400
1401
1402

1403
1404
1405
1406
1407
1408
1409
1410







-
+







      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);
        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
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112







+







    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;
  }
}

/*

Changes to src/sqliteInt.h.

3590
3591
3592
3593
3594
3595
3596

3597
3598
3599
3600
3601
3602
3603
3590
3591
3592
3593
3594
3595
3596
3597
3598
3599
3600
3601
3602
3603
3604







+







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

Changes to src/window.c.

1018
1019
1020
1021
1022
1023
1024




1025
1026
1027
1028
1029
1030
1031
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
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