SQLite

Check-in [5ac44872fd]
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
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.361
Context
2018-05-25
09:36
Merge latest trunk changes into this branch. (check-in: 6232519899 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: 5ac44872fd 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: 7b709a989c user: dan tags: exp-window-functions)
Changes
Unified Diff 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

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








<
<





>
>








159
160
161
162
163
164
165


166
167
168
169
170
171
172
173
174
175
176
177
178
179

** 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 ){
︙︙
218
219
220
221
222
223
224

225
226
227
228
229
230
231

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








>








218
219
220
221
222
223
224
225
226
227
228
229
230
231
232

  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 
  );
︙︙
279
280
281
282
283
284
285
286
287
288
289
290
291
292




293
294
295
296
297
298
299

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









<






>
>
>
>








280
281
282
283
284
285
286

287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303

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

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








>
>
>

>








>
>
|
>
|
>
>
>








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

      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