/ Check-in [2879a691]
Login

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

Overview
Comment:Always evaluate window functions using the alternative path usually only used by EXCLUDE frames if the SQLITE_QueryFlattener test flag is set.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | window-functions
Files: files | file ages | folders
SHA3-256:2879a691aca9304aea5acb46bab8e82bb2e08eb54201f3679d60bfc0e8383845
User & Date: dan 2019-03-18 18:55:35
Wiki:window-functions
Context
2019-03-18
21:19
Add further tests for new window function functionality. check-in: 1fbddf01 user: dan tags: window-functions
18:55
Always evaluate window functions using the alternative path usually only used by EXCLUDE frames if the SQLITE_QueryFlattener test flag is set. check-in: 2879a691 user: dan tags: window-functions
16:51
Fixes for RANGE windows and NULL values. check-in: 723c84be user: dan tags: window-functions
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/vdbe.c.

6536
6537
6538
6539
6540
6541
6542

6543
6544
6545
6546
6547
6548
6549
  Mem *pMem;
  assert( pOp->p1>0 && pOp->p1<=(p->nMem+1 - p->nCursor) );
  assert( pOp->p3==0 || pOp->opcode==OP_AggValue );
  pMem = &aMem[pOp->p1];
  assert( (pMem->flags & ~(MEM_Null|MEM_Agg))==0 );
#ifndef SQLITE_OMIT_WINDOWFUNC
  if( pOp->p3 ){

    rc = sqlite3VdbeMemAggValue(pMem, &aMem[pOp->p3], pOp->p4.pFunc);
    pMem = &aMem[pOp->p3];
  }else
#endif
  {
    rc = sqlite3VdbeMemFinalize(pMem, pOp->p4.pFunc);
  }







>







6536
6537
6538
6539
6540
6541
6542
6543
6544
6545
6546
6547
6548
6549
6550
  Mem *pMem;
  assert( pOp->p1>0 && pOp->p1<=(p->nMem+1 - p->nCursor) );
  assert( pOp->p3==0 || pOp->opcode==OP_AggValue );
  pMem = &aMem[pOp->p1];
  assert( (pMem->flags & ~(MEM_Null|MEM_Agg))==0 );
#ifndef SQLITE_OMIT_WINDOWFUNC
  if( pOp->p3 ){
    memAboutToChange(p, &aMem[pOp->p3]);
    rc = sqlite3VdbeMemAggValue(pMem, &aMem[pOp->p3], pOp->p4.pFunc);
    pMem = &aMem[pOp->p3];
  }else
#endif
  {
    rc = sqlite3VdbeMemFinalize(pMem, pOp->p4.pFunc);
  }

Changes to src/window.c.

211
212
213
214
215
216
217



218












219
220
221



222
223
224
225






226
227
228
229
230
231
232
...
247
248
249
250
251
252
253



254
255
256
257
258
259
260
....
1068
1069
1070
1071
1072
1073
1074



1075
1076
1077
1078
1079
1080
1081
....
1532
1533
1534
1535
1536
1537
1538

1539
1540
1541
1542
1543


1544
1545
1546
1547
1548
1549
1550
1551
....
1664
1665
1666
1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
....
1760
1761
1762
1763
1764
1765
1766
1767
1768
1769
1770
1771
1772
1773
1774
....
2332
2333
2334
2335
2336
2337
2338




2339
2340
2341
2342
2343
2344
2345
  sqlite3_context *pCtx, 
  int nArg,
  sqlite3_value **apArg
){
  struct NthValueCtx *p;
  p = (struct NthValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p));
  if( p ){



    i64 iVal = sqlite3_value_int64(apArg[1]);












    p->nStep++;
    if( iVal==p->nStep ){
      p->pValue = sqlite3_value_dup(apArg[0]);



    }
  }
  UNUSED_PARAMETER(nArg);
  UNUSED_PARAMETER(apArg);






}
static void nth_valueValueFunc(sqlite3_context *pCtx){
  struct NthValueCtx *p;
  p = (struct NthValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p));
  if( p && p->pValue ){
    sqlite3_result_value(pCtx, p->pValue);
  }
................................................................................
  int nArg,
  sqlite3_value **apArg
){
  struct NthValueCtx *p;
  p = (struct NthValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p));
  if( p && p->pValue==0 ){
    p->pValue = sqlite3_value_dup(apArg[0]);



  }
  UNUSED_PARAMETER(nArg);
  UNUSED_PARAMETER(apArg);
}
static void first_valueValueFunc(sqlite3_context *pCtx){
  struct NthValueCtx *p;
  p = (struct NthValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p));
................................................................................
  }

  pWin = (Window*)sqlite3DbMallocZero(pParse->db, sizeof(Window));
  if( pWin==0 ) goto windowAllocErr;
  pWin->eType = eType;
  pWin->eStart = eStart;
  pWin->eEnd = eEnd;



  pWin->eExclude = eExclude;
  pWin->bImplicitFrame = bImplicitFrame;
  pWin->pEnd = sqlite3WindowOffsetExpr(pParse, pEnd);
  pWin->pStart = sqlite3WindowOffsetExpr(pParse, pStart);
  return pWin;

windowAllocErr:
................................................................................
    sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regAccum);
  }

  sqlite3VdbeAddOp3(v, OP_SeekGE, csr, lblBrk, pMWin->regStartRowid);
  addrNext = sqlite3VdbeCurrentAddr(v);
  sqlite3VdbeAddOp2(v, OP_Rowid, csr, regRowid);
  sqlite3VdbeAddOp3(v, OP_Gt, pMWin->regEndRowid, lblBrk, regRowid);

  if( pMWin->eExclude==TK_CURRENT ){
    sqlite3VdbeAddOp3(v, OP_Eq, regCRowid, lblNext, regRowid);
  }else if( pMWin->eExclude!=TK_NO ){
    int addr;
    int addrEq = 0;;


    KeyInfo *pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pMWin->pOrderBy,0,0);
    if( pMWin->eExclude==TK_TIES ){
      addrEq = sqlite3VdbeAddOp3(v, OP_Eq, regCRowid, lblNext, regRowid);
    }
    windowReadPeerValues(p, csr, regPeer);
    sqlite3VdbeAddOp3(v, OP_Compare, regPeer, regCPeer, nPeer);
    sqlite3VdbeAppendP4(v, (void*)pKeyInfo, P4_KEYINFO);
    addr = sqlite3VdbeCurrentAddr(v)+1;
................................................................................
  int regArg;
  int nArg = 0;
  Window *pWin;
  for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
    FuncDef *pFunc = pWin->pFunc;
    sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regAccum);
    nArg = MAX(nArg, windowArgCount(pWin));
    if( pWin->eExclude==0 ){
      if( pFunc->zName==nth_valueName || pFunc->zName==first_valueName ){
        sqlite3VdbeAddOp2(v, OP_Integer, 0, pWin->regApp);
        sqlite3VdbeAddOp2(v, OP_Integer, 0, pWin->regApp+1);
      }

      if( (pFunc->funcFlags & SQLITE_FUNC_MINMAX) && pWin->csrApp ){
        assert( pWin->eStart!=TK_UNBOUNDED );
................................................................................
){
  Parse *pParse = p->pParse;
  Vdbe *v = sqlite3GetVdbe(pParse);
  int reg1 = sqlite3GetTempReg(pParse);
  int reg2 = sqlite3GetTempReg(pParse);
  int arith = OP_Add;
  int addrGe;
  int addrNotNull;

  int regString = ++pParse->nMem;

  assert( op==OP_Ge || op==OP_Gt || op==OP_Le );
  assert( p->pMWin->pOrderBy && p->pMWin->pOrderBy->nExpr==1 );
  if( p->pMWin->pOrderBy->a[0].sortOrder ){
    switch( op ){
................................................................................
  int lblWhereEnd;                /* Label just before sqlite3WhereEnd() code */

  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 




  );

  lblWhereEnd = sqlite3VdbeMakeLabel(pParse);

  /* Fill in the context object */
  memset(&s, 0, sizeof(WindowCodeArg));
  s.pParse = pParse;







>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>



>
>
>




>
>
>
>
>
>







 







>
>
>







 







>
>
>







 







>




|
>
>
|







 







|







 







<







 







>
>
>
>







211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
...
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
....
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
....
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
....
1697
1698
1699
1700
1701
1702
1703
1704
1705
1706
1707
1708
1709
1710
1711
....
1793
1794
1795
1796
1797
1798
1799

1800
1801
1802
1803
1804
1805
1806
....
2364
2365
2366
2367
2368
2369
2370
2371
2372
2373
2374
2375
2376
2377
2378
2379
2380
2381
  sqlite3_context *pCtx, 
  int nArg,
  sqlite3_value **apArg
){
  struct NthValueCtx *p;
  p = (struct NthValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p));
  if( p ){
    i64 iVal;
    switch( sqlite3_value_numeric_type(apArg[1]) ){
      case SQLITE_INTEGER:
        iVal = sqlite3_value_int64(apArg[1]);
        break;
      case SQLITE_FLOAT: {
        double fVal = sqlite3_value_double(apArg[1]);
        if( ((i64)fVal)!=fVal ) goto error_out;
        iVal = (i64)fVal;
        break;
      }
      default:
        goto error_out;
    }
    if( iVal<=0 ) goto error_out;

    p->nStep++;
    if( iVal==p->nStep ){
      p->pValue = sqlite3_value_dup(apArg[0]);
      if( !p->pValue ){
        sqlite3_result_error_nomem(pCtx);
      }
    }
  }
  UNUSED_PARAMETER(nArg);
  UNUSED_PARAMETER(apArg);
  return;

 error_out:
  sqlite3_result_error(
      pCtx, "second argument to nth_value must be a positive integer", -1
  );
}
static void nth_valueValueFunc(sqlite3_context *pCtx){
  struct NthValueCtx *p;
  p = (struct NthValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p));
  if( p && p->pValue ){
    sqlite3_result_value(pCtx, p->pValue);
  }
................................................................................
  int nArg,
  sqlite3_value **apArg
){
  struct NthValueCtx *p;
  p = (struct NthValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p));
  if( p && p->pValue==0 ){
    p->pValue = sqlite3_value_dup(apArg[0]);
    if( !p->pValue ){
      sqlite3_result_error_nomem(pCtx);
    }
  }
  UNUSED_PARAMETER(nArg);
  UNUSED_PARAMETER(apArg);
}
static void first_valueValueFunc(sqlite3_context *pCtx){
  struct NthValueCtx *p;
  p = (struct NthValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p));
................................................................................
  }

  pWin = (Window*)sqlite3DbMallocZero(pParse->db, sizeof(Window));
  if( pWin==0 ) goto windowAllocErr;
  pWin->eType = eType;
  pWin->eStart = eStart;
  pWin->eEnd = eEnd;
  if( eExclude==0 && OptimizationDisabled(pParse->db, SQLITE_QueryFlattener) ){
    eExclude = TK_NO;
  }
  pWin->eExclude = eExclude;
  pWin->bImplicitFrame = bImplicitFrame;
  pWin->pEnd = sqlite3WindowOffsetExpr(pParse, pEnd);
  pWin->pStart = sqlite3WindowOffsetExpr(pParse, pStart);
  return pWin;

windowAllocErr:
................................................................................
    sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regAccum);
  }

  sqlite3VdbeAddOp3(v, OP_SeekGE, csr, lblBrk, pMWin->regStartRowid);
  addrNext = sqlite3VdbeCurrentAddr(v);
  sqlite3VdbeAddOp2(v, OP_Rowid, csr, regRowid);
  sqlite3VdbeAddOp3(v, OP_Gt, pMWin->regEndRowid, lblBrk, regRowid);

  if( pMWin->eExclude==TK_CURRENT ){
    sqlite3VdbeAddOp3(v, OP_Eq, regCRowid, lblNext, regRowid);
  }else if( pMWin->eExclude!=TK_NO ){
    int addr;
    int addrEq = 0;
    KeyInfo *pKeyInfo;

    pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pMWin->pOrderBy, 0, 0);
    if( pMWin->eExclude==TK_TIES ){
      addrEq = sqlite3VdbeAddOp3(v, OP_Eq, regCRowid, lblNext, regRowid);
    }
    windowReadPeerValues(p, csr, regPeer);
    sqlite3VdbeAddOp3(v, OP_Compare, regPeer, regCPeer, nPeer);
    sqlite3VdbeAppendP4(v, (void*)pKeyInfo, P4_KEYINFO);
    addr = sqlite3VdbeCurrentAddr(v)+1;
................................................................................
  int regArg;
  int nArg = 0;
  Window *pWin;
  for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
    FuncDef *pFunc = pWin->pFunc;
    sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regAccum);
    nArg = MAX(nArg, windowArgCount(pWin));
    if( pMWin->regStartRowid==0 ){
      if( pFunc->zName==nth_valueName || pFunc->zName==first_valueName ){
        sqlite3VdbeAddOp2(v, OP_Integer, 0, pWin->regApp);
        sqlite3VdbeAddOp2(v, OP_Integer, 0, pWin->regApp+1);
      }

      if( (pFunc->funcFlags & SQLITE_FUNC_MINMAX) && pWin->csrApp ){
        assert( pWin->eStart!=TK_UNBOUNDED );
................................................................................
){
  Parse *pParse = p->pParse;
  Vdbe *v = sqlite3GetVdbe(pParse);
  int reg1 = sqlite3GetTempReg(pParse);
  int reg2 = sqlite3GetTempReg(pParse);
  int arith = OP_Add;
  int addrGe;


  int regString = ++pParse->nMem;

  assert( op==OP_Ge || op==OP_Gt || op==OP_Le );
  assert( p->pMWin->pOrderBy && p->pMWin->pOrderBy->nExpr==1 );
  if( p->pMWin->pOrderBy->a[0].sortOrder ){
    switch( op ){
................................................................................
  int lblWhereEnd;                /* Label just before sqlite3WhereEnd() code */

  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 
  );
  assert( pMWin->eExclude==0 || pMWin->eExclude==TK_CURRENT
       || pMWin->eExclude==TK_GROUP || pMWin->eExclude==TK_TIES
       || pMWin->eExclude==TK_NO
  );

  lblWhereEnd = sqlite3VdbeMakeLabel(pParse);

  /* Fill in the context object */
  memset(&s, 0, sizeof(WindowCodeArg));
  s.pParse = pParse;

Changes to test/permutations.test.

1028
1029
1030
1031
1032
1033
1034
1035


1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
} -files [
  test_set [glob -nocomplain $::testdir/../ext/rbu/*.test] -exclude rbu.test
]

test_suite "no_optimization" -description {
  Run test scripts with optimizations disabled using the
  sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS) interface.
} -files {


  where.test where2.test where3.test where4.test where5.test
  where6.test where7.test where8.test where9.test
  whereA.test whereB.test wherelimit.test
  select1.test select2.test select3.test select4.test select5.test
  select7.test select8.test selectA.test selectC.test
} -dbconfig {
  optimization_control $::dbhandle all 0
}

test_suite "prepare" -description {
  Run tests with the db connection using sqlite3_prepare() instead of _v2().
} -dbconfig {
  $::dbhandle version -use-legacy-prepare 1







|
>
>
|
|
|
|
|
|







1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
} -files [
  test_set [glob -nocomplain $::testdir/../ext/rbu/*.test] -exclude rbu.test
]

test_suite "no_optimization" -description {
  Run test scripts with optimizations disabled using the
  sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS) interface.
} -files [
  test_set \
    [glob -nocomplain $::testdir/window*.test]                       \
    where.test where2.test where3.test where4.test where5.test       \
    where6.test where7.test where8.test where9.test                  \
    whereA.test whereB.test wherelimit.test                          \
    select1.test select2.test select3.test select4.test select5.test \
    select7.test select8.test selectA.test selectC.test     
] -dbconfig {
  optimization_control $::dbhandle all 0
}

test_suite "prepare" -description {
  Run tests with the db connection using sqlite3_prepare() instead of _v2().
} -dbconfig {
  $::dbhandle version -use-legacy-prepare 1

Changes to test/window4.test.

146
147
148
149
150
151
152
153

154
155
156
157
158
159
160
  SELECT a, lag(b, 3, 'abc') OVER (ORDER BY a) FROM t4
} {1 abc   2 abc   3 abc   4 A   5 B   6 C   7 D   8 E   9 F   10 G}

do_execsql_test 2.4.1 {
  SELECT group_concat(b, '.') OVER (
    ORDER BY a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  ) FROM t4
} {A.B.C.D.E.F.G.H.I.J   B.C.D.E.F.G.H.I.J   C.D.E.F.G.H.I.J   D.E.F.G.H.I.J   E.F.G.H.I.J   F.G.H.I.J   G.H.I.J   H.I.J   I.J   J}


do_execsql_test 3.0 {
  DROP TABLE IF EXISTS t5;
  CREATE TABLE t5(a INTEGER PRIMARY KEY, b TEXT, c TEXT, d INTEGER);
  INSERT INTO t5 VALUES(1, 'A', 'one',   5);
  INSERT INTO t5 VALUES(2, 'B', 'two',   4);
  INSERT INTO t5 VALUES(3, 'A', 'three', 3);







|
>







146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
  SELECT a, lag(b, 3, 'abc') OVER (ORDER BY a) FROM t4
} {1 abc   2 abc   3 abc   4 A   5 B   6 C   7 D   8 E   9 F   10 G}

do_execsql_test 2.4.1 {
  SELECT group_concat(b, '.') OVER (
    ORDER BY a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  ) FROM t4
} {A.B.C.D.E.F.G.H.I.J   B.C.D.E.F.G.H.I.J   C.D.E.F.G.H.I.J   D.E.F.G.H.I.J
  E.F.G.H.I.J   F.G.H.I.J   G.H.I.J   H.I.J   I.J   J}

do_execsql_test 3.0 {
  DROP TABLE IF EXISTS t5;
  CREATE TABLE t5(a INTEGER PRIMARY KEY, b TEXT, c TEXT, d INTEGER);
  INSERT INTO t5 VALUES(1, 'A', 'one',   5);
  INSERT INTO t5 VALUES(2, 'B', 'two',   4);
  INSERT INTO t5 VALUES(3, 'A', 'three', 3);

Changes to test/windowfault.test.

24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
  CREATE TABLE t1(a, b, c, d);
  INSERT INTO t1 VALUES(1, 2, 3, 4);
  INSERT INTO t1 VALUES(5, 6, 7, 8);
  INSERT INTO t1 VALUES(9, 10, 11, 12);
}
faultsim_save_and_close

do_faultsim_test 1 -start 1 -faults oom-* -prep {
  faultsim_restore_and_reopen
} -body {
  execsql {
    SELECT row_number() OVER win,
           rank() OVER win,
           dense_rank() OVER win,
           ntile(2) OVER win,







|







24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
  CREATE TABLE t1(a, b, c, d);
  INSERT INTO t1 VALUES(1, 2, 3, 4);
  INSERT INTO t1 VALUES(5, 6, 7, 8);
  INSERT INTO t1 VALUES(9, 10, 11, 12);
}
faultsim_save_and_close

do_faultsim_test 1 -start 1 -faults oom-t* -prep {
  faultsim_restore_and_reopen
} -body {
  execsql {
    SELECT row_number() OVER win,
           rank() OVER win,
           dense_rank() OVER win,
           ntile(2) OVER win,