/ Check-in [4f5b2d9381]
Login

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

Overview
Comment:Ensure that SQLite does not attempt to process incompatible window functions in a single scan. Fix for [256741a1].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 4f5b2d938194fab7627486e2ced633def2c90d9d3328e3700612feb9dbfa3d9a
User & Date: dan 2019-08-15 13:53:22
Context
2019-08-15
14:35
Ensure that the optional "sz=N" parameter that can be manually added to the end of an sqlite_stat1 entry does not have an N value that is too small. Ticket [e4598ecbdd18bd82] check-in: 98357d8c12 user: drh tags: trunk
13:53
Ensure that SQLite does not attempt to process incompatible window functions in a single scan. Fix for [256741a1]. check-in: 4f5b2d9381 user: dan tags: trunk
13:46
Avoid downgrading SQLITE_CORRUPT errors detected by the schema parser into SQLITE_NOMEM or SQLITE_ERROR errors. check-in: b2e79f8ff0 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c.

1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
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) );
    assert( pWin->ppThis==0 );
    if( pSelect->pWin ){
      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){







|
<
<
<
<
<







1321
1322
1323
1324
1325
1326
1327
1328





1329
1330
1331
1332
1333
1334
1335
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) );
    assert( pWin->ppThis==0 );
    sqlite3WindowLink(pSelect, 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.

904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
          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;
          pExpr->op2 = 0;







|
<
<
<
<
<
<
<
<
<







904
905
906
907
908
909
910
911









912
913
914
915
916
917
918
          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);
          sqlite3WindowLink(pSel, pWin);









          pNC->ncFlags |= NC_HasWin;
        }else
#endif /* SQLITE_OMIT_WINDOWFUNC */
        {
          NameContext *pNC2 = pNC;
          pExpr->op = TK_AGG_FUNCTION;
          pExpr->op2 = 0;

Changes to src/sqliteInt.h.

3605
3606
3607
3608
3609
3610
3611

3612
3613
3614
3615
3616
3617
3618

#ifndef SQLITE_OMIT_WINDOWFUNC
void sqlite3WindowDelete(sqlite3*, Window*);
void sqlite3WindowUnlinkFromSelect(Window*);
void sqlite3WindowListDelete(sqlite3 *db, Window *p);
Window *sqlite3WindowAlloc(Parse*, int, int, Expr*, int , Expr*, u8);
void sqlite3WindowAttach(Parse*, Expr*, Window*);

int sqlite3WindowCompare(Parse*, Window*, Window*, int);
void sqlite3WindowCodeInit(Parse*, Window*);
void sqlite3WindowCodeStep(Parse*, Select*, WhereInfo*, int, int);
int sqlite3WindowRewrite(Parse*, Select*);
int sqlite3ExpandSubquery(Parse*, struct SrcList_item*);
void sqlite3WindowUpdate(Parse*, Window*, Window*, FuncDef*);
Window *sqlite3WindowDup(sqlite3 *db, Expr *pOwner, Window *p);







>







3605
3606
3607
3608
3609
3610
3611
3612
3613
3614
3615
3616
3617
3618
3619

#ifndef SQLITE_OMIT_WINDOWFUNC
void sqlite3WindowDelete(sqlite3*, Window*);
void sqlite3WindowUnlinkFromSelect(Window*);
void sqlite3WindowListDelete(sqlite3 *db, Window *p);
Window *sqlite3WindowAlloc(Parse*, int, int, Expr*, int , Expr*, u8);
void sqlite3WindowAttach(Parse*, Expr*, Window*);
void sqlite3WindowLink(Select *pSel, Window *pWin);
int sqlite3WindowCompare(Parse*, Window*, Window*, int);
void sqlite3WindowCodeInit(Parse*, Window*);
void sqlite3WindowCodeStep(Parse*, Select*, WhereInfo*, int, int);
int sqlite3WindowRewrite(Parse*, Select*);
int sqlite3ExpandSubquery(Parse*, struct SrcList_item*);
void sqlite3WindowUpdate(Parse*, Window*, Window*, FuncDef*);
Window *sqlite3WindowDup(sqlite3 *db, Expr *pOwner, Window *p);

Changes to src/window.c.

1224
1225
1226
1227
1228
1229
1230



















1231
1232
1233
1234
1235
1236
1237
....
1411
1412
1413
1414
1415
1416
1417


1418
1419
1420
1421
1422
1423
1424
          "DISTINCT is not supported for window functions"
      );
    }
  }else{
    sqlite3WindowDelete(pParse->db, pWin);
  }
}




















/*
** Return 0 if the two window objects are identical, or non-zero otherwise.
** Identical window objects can be processed in a single scan.
*/
int sqlite3WindowCompare(Parse *pParse, Window *p1, Window *p2, int bFilter){
  if( p1->eFrmType!=p2->eFrmType ) return 1;
................................................................................
  Vdbe *v = sqlite3GetVdbe(pParse);
  Window *pWin;
  for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
    FuncDef *pFunc = pWin->pFunc;
    int regArg;
    int nArg = windowArgCount(pWin);
    int i;



    for(i=0; i<nArg; i++){
      if( i!=1 || pFunc->zName!=nth_valueName ){
        sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol+i, reg+i);
      }else{
        sqlite3VdbeAddOp3(v, OP_Column, pMWin->iEphCsr, pWin->iArgCol+i, reg+i);
      }







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







 







>
>







1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
....
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
          "DISTINCT is not supported for window functions"
      );
    }
  }else{
    sqlite3WindowDelete(pParse->db, pWin);
  }
}

/*
** Possibly link window pWin into the list at pSel->pWin (window functions
** to be processed as part of SELECT statement pSel). The window is linked
** in if either (a) there are no other windows already linked to this
** SELECT, or (b) the windows already linked use a compatible window frame.
*/
void sqlite3WindowLink(Select *pSel, Window *pWin){
  if( 0==pSel->pWin 
   || 0==sqlite3WindowCompare(0, pSel->pWin, pWin, 0)
  ){
    pWin->pNextWin = pSel->pWin;
    if( pSel->pWin ){
      pSel->pWin->ppThis = &pWin->pNextWin;
    }
    pSel->pWin = pWin;
    pWin->ppThis = &pSel->pWin;
  }
}

/*
** Return 0 if the two window objects are identical, or non-zero otherwise.
** Identical window objects can be processed in a single scan.
*/
int sqlite3WindowCompare(Parse *pParse, Window *p1, Window *p2, int bFilter){
  if( p1->eFrmType!=p2->eFrmType ) return 1;
................................................................................
  Vdbe *v = sqlite3GetVdbe(pParse);
  Window *pWin;
  for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
    FuncDef *pFunc = pWin->pFunc;
    int regArg;
    int nArg = windowArgCount(pWin);
    int i;

    assert( bInverse==0 || pWin->eStart!=TK_UNBOUNDED );

    for(i=0; i<nArg; i++){
      if( i!=1 || pFunc->zName!=nth_valueName ){
        sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol+i, reg+i);
      }else{
        sqlite3VdbeAddOp3(v, OP_Column, pMWin->iEphCsr, pWin->iArgCol+i, reg+i);
      }

Changes to test/window9.test.

167
168
169
170
171
172
173























174
175
176
  }
} {
  do_test 5.1.$tn {
    execsql "EXPLAIN QUERY PLAN $sql"
  } {~/ORDER/}
}


























finish_test







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



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
  }
} {
  do_test 5.1.$tn {
    execsql "EXPLAIN QUERY PLAN $sql"
  } {~/ORDER/}
}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 6.0 {
  CREATE TABLE t0(c0);
  INSERT INTO t0(c0) VALUES (0);
}

do_execsql_test 6.1 {
  SELECT * FROM t0 WHERE 
  EXISTS (
    SELECT MIN(c0) OVER (), CUME_DIST() OVER () FROM t0
  ) >=1 AND 
  EXISTS (
    SELECT MIN(c0) OVER (), CUME_DIST() OVER () FROM t0
  ) <=1;
} {0}

do_execsql_test 6.2 {
  SELECT * FROM t0 WHERE EXISTS (
    SELECT MIN(c0) OVER (), CUME_DIST() OVER () FROM t0
  ) 
  BETWEEN 1 AND 1;
} {0}


finish_test