/ Check-in [5ac44872]
Login

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

Overview
Comment:Fixes for "ROWS BETWEEN <expr> FOLLOWING AND <expr> FOLLOWING" and "ROWS BETWEEN <expr> FOLLOWING AND UNBOUNDED FOLLOWING"
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | exp-window-functions
Files: files | file ages | folders
SHA3-256: 5ac44872fd5c4f92851e7bf57d7207bb4d67de88ea2b5c746ff97f20bd6352e1
User & Date: dan 2018-05-25 09:29:11
Context
2018-05-25
09:36
Merge latest trunk changes into this branch. check-in: 62325198 user: dan tags: exp-window-functions
09:29
Fixes for "ROWS BETWEEN <expr> FOLLOWING AND <expr> FOLLOWING" and "ROWS BETWEEN <expr> FOLLOWING AND UNBOUNDED FOLLOWING" check-in: 5ac44872 user: dan tags: exp-window-functions
2018-05-24
21:10
Allow "<expr> PRECEDING" to be used to specify the end of a window frame. check-in: 7b709a98 user: dan tags: exp-window-functions
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/window.c.

159
160
161
162
163
164
165
166
167
168
169
170
171
172


173
174
175
176
177
178
179
...
218
219
220
221
222
223
224

225
226
227
228
229
230
231
...
279
280
281
282
283
284
285
286
287
288
289
290
291
292




293
294
295
296
297
298
299
...
323
324
325
326
327
328
329



330

331
332
333
334
335
336
337
338


339


340


341
342
343
344
345
346
347
** ROWS BETWEEN <expr> FOLLOWING    AND <expr> FOLLOWING
**
**   regEnd = regEnd - regStart
**   Rewind (csr,csrStart,csrEnd)   // if EOF goto flush_partition_done
**     Aggstep (csrEnd)
**     Next(csrEnd)                 // if EOF fall-through
**     if( (regEnd--)<=0 ){
**       AggStep (csrStart, xInverse)
**       Next (csrStart)
**       if( (regStart--)<=0 ){
**         AggFinal (xValue)
**         Gosub addrGosub
**         Next(csr)              // if EOF goto flush_partition_done
**       }


**     }
**
** ROWS BETWEEN <expr> PRECEDING    AND <expr> PRECEDING
**
**   Replace the bit after "Rewind" in the above with:
**
**     if( (regEnd--)<=0 ){
................................................................................
  int addrGoto;
  int addrTop;
  int addrIfPos1;
  int addrIfPos2;

  assert( pMWin->eStart==TK_PRECEDING 
       || pMWin->eStart==TK_CURRENT 

       || pMWin->eStart==TK_UNBOUNDED 
  );
  assert( pMWin->eEnd==TK_FOLLOWING 
       || pMWin->eEnd==TK_CURRENT 
       || pMWin->eEnd==TK_UNBOUNDED 
       || pMWin->eEnd==TK_PRECEDING 
  );
................................................................................
  sqlite3VdbeAddOp2(v, OP_Once, 0, sqlite3VdbeCurrentAddr(v)+3);
  sqlite3VdbeAddOp2(v, OP_OpenDup, csrStart, pMWin->iEphCsr);
  sqlite3VdbeAddOp2(v, OP_OpenDup, csrEnd, pMWin->iEphCsr);

  /* If either regStart or regEnd are not non-negative integers, throw 
  ** an exception.  */
  if( pMWin->pStart ){
    assert( pMWin->eStart==TK_PRECEDING );
    sqlite3ExprCode(pParse, pMWin->pStart, regStart);
    windowCheckFrameValue(pParse, regStart, 0);
  }
  if( pMWin->pEnd ){
    sqlite3ExprCode(pParse, pMWin->pEnd, regEnd);
    windowCheckFrameValue(pParse, regEnd, 1);




  }

  for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
    sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regResult);
    sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regAccum);
  }

................................................................................
      sqlite3VdbeJumpHere(v, addrIfPos1);
    }
  }

  if( pMWin->eEnd==TK_FOLLOWING ){
    addrIfPos1 = sqlite3VdbeAddOp3(v, OP_IfPos, regEnd, 0 , 1);
  }



  for(pWin=pMWin; pWin; pWin=pWin->pNextWin){

    sqlite3VdbeAddOp3(v, 
        OP_AggFinal, pWin->regAccum, pWin->nArg, pWin->regResult
    );
    sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
  }
  sqlite3VdbeAddOp2(v, OP_Gosub, regGosub, addrGosub);
  sqlite3VdbeAddOp2(v, OP_Next, pMWin->iEphCsr, sqlite3VdbeCurrentAddr(v)+2);
  sqlite3VdbeAddOp2(v, OP_Goto, 0, lblFlushDone);





  if( pMWin->eStart==TK_CURRENT || pMWin->eStart==TK_PRECEDING ){


    if( pMWin->eStart==TK_PRECEDING ){
      addrIfPos2 = sqlite3VdbeAddOp3(v, OP_IfPos, regStart, 0 , 1);
    }
    sqlite3VdbeAddOp2(v, OP_Next, csrStart, sqlite3VdbeCurrentAddr(v)+1);
    windowAggStep(pParse, pMWin, csrStart, 1, reg);
    if( pMWin->eStart==TK_PRECEDING ){
      sqlite3VdbeJumpHere(v, addrIfPos2);







<
<





>
>







 







>







 







<






>
>
>
>







 







>
>
>

>








>
>
|
>
>
|
>
>







159
160
161
162
163
164
165


166
167
168
169
170
171
172
173
174
175
176
177
178
179
...
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
...
280
281
282
283
284
285
286

287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
...
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
** ROWS BETWEEN <expr> FOLLOWING    AND <expr> FOLLOWING
**
**   regEnd = regEnd - regStart
**   Rewind (csr,csrStart,csrEnd)   // if EOF goto flush_partition_done
**     Aggstep (csrEnd)
**     Next(csrEnd)                 // if EOF fall-through
**     if( (regEnd--)<=0 ){


**       if( (regStart--)<=0 ){
**         AggFinal (xValue)
**         Gosub addrGosub
**         Next(csr)              // if EOF goto flush_partition_done
**       }
**       AggStep (csrStart, xInverse)
**       Next (csrStart)
**     }
**
** ROWS BETWEEN <expr> PRECEDING    AND <expr> PRECEDING
**
**   Replace the bit after "Rewind" in the above with:
**
**     if( (regEnd--)<=0 ){
................................................................................
  int addrGoto;
  int addrTop;
  int addrIfPos1;
  int addrIfPos2;

  assert( pMWin->eStart==TK_PRECEDING 
       || pMWin->eStart==TK_CURRENT 
       || pMWin->eStart==TK_FOLLOWING 
       || pMWin->eStart==TK_UNBOUNDED 
  );
  assert( pMWin->eEnd==TK_FOLLOWING 
       || pMWin->eEnd==TK_CURRENT 
       || pMWin->eEnd==TK_UNBOUNDED 
       || pMWin->eEnd==TK_PRECEDING 
  );
................................................................................
  sqlite3VdbeAddOp2(v, OP_Once, 0, sqlite3VdbeCurrentAddr(v)+3);
  sqlite3VdbeAddOp2(v, OP_OpenDup, csrStart, pMWin->iEphCsr);
  sqlite3VdbeAddOp2(v, OP_OpenDup, csrEnd, pMWin->iEphCsr);

  /* If either regStart or regEnd are not non-negative integers, throw 
  ** an exception.  */
  if( pMWin->pStart ){

    sqlite3ExprCode(pParse, pMWin->pStart, regStart);
    windowCheckFrameValue(pParse, regStart, 0);
  }
  if( pMWin->pEnd ){
    sqlite3ExprCode(pParse, pMWin->pEnd, regEnd);
    windowCheckFrameValue(pParse, regEnd, 1);
    if( pMWin->pStart && pMWin->eStart==TK_FOLLOWING ){
      assert( pMWin->eEnd==TK_FOLLOWING );
      sqlite3VdbeAddOp3(v, OP_Subtract, regStart, regEnd, regEnd);
    }
  }

  for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
    sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regResult);
    sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regAccum);
  }

................................................................................
      sqlite3VdbeJumpHere(v, addrIfPos1);
    }
  }

  if( pMWin->eEnd==TK_FOLLOWING ){
    addrIfPos1 = sqlite3VdbeAddOp3(v, OP_IfPos, regEnd, 0 , 1);
  }
  if( pMWin->eStart==TK_FOLLOWING ){
    addrIfPos2 = sqlite3VdbeAddOp3(v, OP_IfPos, regStart, 0 , 1);
  }
  for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
    sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regResult);
    sqlite3VdbeAddOp3(v, 
        OP_AggFinal, pWin->regAccum, pWin->nArg, pWin->regResult
    );
    sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
  }
  sqlite3VdbeAddOp2(v, OP_Gosub, regGosub, addrGosub);
  sqlite3VdbeAddOp2(v, OP_Next, pMWin->iEphCsr, sqlite3VdbeCurrentAddr(v)+2);
  sqlite3VdbeAddOp2(v, OP_Goto, 0, lblFlushDone);
  if( pMWin->eStart==TK_FOLLOWING ){
    sqlite3VdbeJumpHere(v, addrIfPos2);
  }

  if( pMWin->eStart==TK_CURRENT 
   || pMWin->eStart==TK_PRECEDING 
   || pMWin->eStart==TK_FOLLOWING 
  ){
    if( pMWin->eStart==TK_PRECEDING ){
      addrIfPos2 = sqlite3VdbeAddOp3(v, OP_IfPos, regStart, 0 , 1);
    }
    sqlite3VdbeAddOp2(v, OP_Next, csrStart, sqlite3VdbeCurrentAddr(v)+1);
    windowAggStep(pParse, pMWin, csrStart, 1, reg);
    if( pMWin->eStart==TK_PRECEDING ){
      sqlite3VdbeJumpHere(v, addrIfPos2);

Changes to test/window2.tcl.

254
255
256
257
258
259
260






261
262
263
264
265





266

267
268
269
270
271
272
273
274
275

276
277
278
279
280


















281
282
283
284
285
286

287
288
289
290
  SELECT a, sum(d) OVER (
    PARTITION BY b
    ORDER BY d 
    ROWS BETWEEN UNBOUNDED PRECEDING AND 2 PRECEDING
  ) FROM t1
}








==========

puts $::fd finish_test
==========







execsql_test 3.1 {
  SELECT a, sum(d) OVER (
    PARTITION BY b ORDER BY d
    RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  ) FROM t1
}

execsql_test 3.2 {
  SELECT a, sum(d) OVER (

    ORDER BY b
    RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  ) FROM t1
}



















execsql_test 3.3 {
  SELECT a, sum(d) OVER (
    ORDER BY d
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) FROM t1
}


finish_test









>
>
>
>
>
>
|
<

<
<
>
>
>
>
>
|
>
|

|
|



|

>
|
|



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




254
255
256
257
258
259
260
261
262
263
264
265
266
267

268


269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313

314
315
316
317
318
  SELECT a, sum(d) OVER (
    PARTITION BY b
    ORDER BY d 
    ROWS BETWEEN UNBOUNDED PRECEDING AND 2 PRECEDING
  ) FROM t1
}

execsql_test 2.19 {
  SELECT a, sum(d) OVER (
    PARTITION BY b
    ORDER BY d 
    ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING
  ) FROM t1
}




execsql_test 2.20 {
  SELECT a, sum(d) OVER (
    ORDER BY d 
    ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING
  ) FROM t1
}

execsql_test 2.21 {
  SELECT a, sum(d) OVER (
    ORDER BY d 
    ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
  ) FROM t1
}

execsql_test 2.22 {
  SELECT a, sum(d) OVER (
    PARTITION BY b
    ORDER BY d 
    ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
  ) FROM t1
}

==========
puts $::fd finish_test
==========

# execsql_test 3.1 {
#   SELECT a, sum(d) OVER (
#     PARTITION BY b ORDER BY d
#     RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
#   ) FROM t1
# }
# 
# execsql_test 3.2 {
#   SELECT a, sum(d) OVER (
#     ORDER BY b
#     RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
#   ) FROM t1
# }
# 
# execsql_test 3.3 {
#   SELECT a, sum(d) OVER (
#     ORDER BY d
#     ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
#   ) FROM t1

# }

finish_test


Changes to test/window2.test.

165
166
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



  SELECT a, sum(d) OVER (
    PARTITION BY b
    ORDER BY d 
    ROWS BETWEEN UNBOUNDED PRECEDING AND 2 PRECEDING
  ) FROM t1
} {2 {}   4 {}   6 2   1 {}   3 {}   5 1}

#==========================================================================








finish_test
#==========================================================================

do_execsql_test 3.1 {
  SELECT a, sum(d) OVER (
    PARTITION BY b ORDER BY d
    RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

  ) FROM t1
} {2 12   4 10   6 6   1 9   3 8   5 5}


do_execsql_test 3.2 {
  SELECT a, sum(d) OVER (
    ORDER BY b
    RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  ) FROM t1
} {2 21   4 21   6 21   1 9   3 9   5 9}


do_execsql_test 3.3 {
  SELECT a, sum(d) OVER (

    ORDER BY d
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) FROM t1
} {1 21   2 21   3 21   4 21   5 21   6 21}




finish_test










<
>
>
>
>
>
>
>

<
<
<
|

|
<
>

<
>

|

|
|

<
>

|

>
|
|

<
>

>
>

>
>
>
165
166
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

200
201
202
203
204
205
206
207
  SELECT a, sum(d) OVER (
    PARTITION BY b
    ORDER BY d 
    ROWS BETWEEN UNBOUNDED PRECEDING AND 2 PRECEDING
  ) FROM t1
} {2 {}   4 {}   6 2   1 {}   3 {}   5 1}


do_execsql_test 2.19 {
  SELECT a, sum(d) OVER (
    PARTITION BY b
    ORDER BY d 
    ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING
  ) FROM t1
} {2 10   4 6   6 {}   1 8   3 5   5 {}}




do_execsql_test 2.20 {
  SELECT a, sum(d) OVER (
    ORDER BY d 

    ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING
  ) FROM t1

} {1 5   2 7   3 9   4 11   5 6   6 {}}

do_execsql_test 2.21 {
  SELECT a, sum(d) OVER (
    ORDER BY d 
    ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
  ) FROM t1

} {1 20   2 18   3 15   4 11   5 6   6 {}}

do_execsql_test 2.22 {
  SELECT a, sum(d) OVER (
    PARTITION BY b
    ORDER BY d 
    ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
  ) FROM t1

} {2 10   4 6   6 {}   1 8   3 5   5 {}}

#==========================================================================

finish_test
#==========================================================================

finish_test