SQLite

Check-in [fadd4dc119]
Login

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

Overview
Comment:Fix another problem in lead()/lag(). And some errors that could occur following OOM faults.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | exp-window-functions
Files: files | file ages | folders
SHA3-256: fadd4dc119d8df0d871f4d839b7a11070e2ffb8927e84b3e7a94f34196db3de3
User & Date: dan 2018-06-15 19:01:35.254
Context
2018-06-15
20:46
Add extra OOM test. (check-in: ac251f7260 user: dan tags: exp-window-functions)
19:01
Fix another problem in lead()/lag(). And some errors that could occur following OOM faults. (check-in: fadd4dc119 user: dan tags: exp-window-functions)
16:10
Fix a bug in the lead() and lag() window functions causing them to fail when used in queries featuring multiple window functions. (check-in: 3839fb18f9 user: dan tags: exp-window-functions)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/parse.y.
︙︙
531
532
533
534
535
536
537

538



539
540
541
542
543
544
545

oneselect(A) ::= SELECT(S) distinct(D) selcollist(W) from(X) where_opt(Y)
                 groupby_opt(P) having_opt(Q) windowdefn_opt(R)
                 orderby_opt(Z) limit_opt(L). {
#if SELECTTRACE_ENABLED
  Token s = S; /*A-overwrites-S*/
#endif
  A = sqlite3SelectNew(pParse,W,X,Y,P,Q,Z,D,L);

  if( A ) A->pWinDefn = R;



#if SELECTTRACE_ENABLED
  /* Populate the Select.zSelName[] string that is used to help with
  ** query planner debugging, to differentiate between multiple Select
  ** objects in a complex query.
  **
  ** If the SELECT keyword is immediately followed by a C-style comment
  ** then extract the first few alphanumeric characters from within that








>
|
>
>
>








531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549

oneselect(A) ::= SELECT(S) distinct(D) selcollist(W) from(X) where_opt(Y)
                 groupby_opt(P) having_opt(Q) windowdefn_opt(R)
                 orderby_opt(Z) limit_opt(L). {
#if SELECTTRACE_ENABLED
  Token s = S; /*A-overwrites-S*/
#endif
  A = sqlite3SelectNew(pParse,W,X,Y,P,Q,Z,D,L);
  if( A ){
    A->pWinDefn = R;
  }else{
    sqlite3WindowListDelete(pParse->db, R);
  }
#if SELECTTRACE_ENABLED
  /* Populate the Select.zSelName[] string that is used to help with
  ** query planner debugging, to differentiate between multiple Select
  ** objects in a complex query.
  **
  ** If the SELECT keyword is immediately followed by a C-style comment
  ** then extract the first few alphanumeric characters from within that
︙︙
Changes to src/window.c.
︙︙
388
389
390
391
392
393
394



395

396
397
398
399
400
401
402

  sqlite3_value **apArg
){
  struct LastValueCtx *p;
  p = (struct LastValueCtx *)sqlite3_aggregate_context(pCtx, sizeof(*p));
  if( p ){
    sqlite3_value_free(p->pVal);
    p->pVal = sqlite3_value_dup(apArg[0]);



    p->nVal++;

  }
}
static void last_valueInvFunc(
  sqlite3_context *pCtx, 
  int nArg,
  sqlite3_value **apArg
){








>
>
>
|
>








388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406

  sqlite3_value **apArg
){
  struct LastValueCtx *p;
  p = (struct LastValueCtx *)sqlite3_aggregate_context(pCtx, sizeof(*p));
  if( p ){
    sqlite3_value_free(p->pVal);
    p->pVal = sqlite3_value_dup(apArg[0]);
    if( p->pVal==0 ){
      sqlite3_result_error_nomem(pCtx);
    }else{
      p->nVal++;
    }
  }
}
static void last_valueInvFunc(
  sqlite3_context *pCtx, 
  int nArg,
  sqlite3_value **apArg
){
︙︙
737
738
739
740
741
742
743

744
745
746
747
748
749
750
751
752
753
754
755
756
757
758




759
760
761
762
763
764
765

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

    pSub = sqlite3SelectNew(
        pParse, pSublist, pSrc, pWhere, pGroupBy, pHaving, pSort, 0, 0
    );
    p->pSrc = sqlite3SrcListAppend(db, 0, 0, 0);

    if( p->pSrc ){
      int iTab;
      ExprList *pList = 0;
      p->pSrc->a[0].pSelect = pSub;
      sqlite3SrcListAssignCursors(pParse, p->pSrc);
      if( sqlite3ExpandSubquery(pParse, &p->pSrc->a[0]) ){
        rc = SQLITE_NOMEM;
      }else{
        pSub->selFlags |= SF_Expanded;
        p->selFlags &= ~SF_Aggregate;
        sqlite3SelectPrep(pParse, pSub, 0);
      }
    }

    sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pMWin->iEphCsr, pSublist->nExpr);




  }

  return rc;
}

/*
** Free the Window object passed as the second argument.








>












|
<
|
>
>
>
>








741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761

762
763
764
765
766
767
768
769
770
771
772
773

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

    pSub = sqlite3SelectNew(
        pParse, pSublist, pSrc, pWhere, pGroupBy, pHaving, pSort, 0, 0
    );
    p->pSrc = sqlite3SrcListAppend(db, 0, 0, 0);
    assert( p->pSrc || db->mallocFailed );
    if( p->pSrc ){
      int iTab;
      ExprList *pList = 0;
      p->pSrc->a[0].pSelect = pSub;
      sqlite3SrcListAssignCursors(pParse, p->pSrc);
      if( sqlite3ExpandSubquery(pParse, &p->pSrc->a[0]) ){
        rc = SQLITE_NOMEM;
      }else{
        pSub->selFlags |= SF_Expanded;
        p->selFlags &= ~SF_Aggregate;
        sqlite3SelectPrep(pParse, pSub, 0);
      }


      sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pMWin->iEphCsr, pSublist->nExpr);
    }else{
      sqlite3SelectDelete(db, pSub);
    }
    if( db->mallocFailed ) rc = SQLITE_NOMEM;
  }

  return rc;
}

/*
** Free the Window object passed as the second argument.
︙︙
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172

    ){
      int csr = pWin->csrApp;
      int lbl = sqlite3VdbeMakeLabel(v);
      int tmpReg = sqlite3GetTempReg(pParse);
      sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regResult);

      if( pFunc->xSFunc==nth_valueStepFunc ){
        sqlite3VdbeAddOp3(v, OP_Column, pWin->iEphCsr, pWin->iArgCol+1, tmpReg);
      }else{
        sqlite3VdbeAddOp2(v, OP_Integer, 1, tmpReg);
      }
      sqlite3VdbeAddOp3(v, OP_Add, tmpReg, pWin->regApp, tmpReg);
      sqlite3VdbeAddOp3(v, OP_Gt, pWin->regApp+1, lbl, tmpReg);
      sqlite3VdbeAddOp3(v, OP_SeekRowid, csr, lbl, tmpReg);
      sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol, pWin->regResult);








|








1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180

    ){
      int csr = pWin->csrApp;
      int lbl = sqlite3VdbeMakeLabel(v);
      int tmpReg = sqlite3GetTempReg(pParse);
      sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regResult);

      if( pFunc->xSFunc==nth_valueStepFunc ){
        sqlite3VdbeAddOp3(v, OP_Column, pMWin->iEphCsr, pWin->iArgCol+1,tmpReg);
      }else{
        sqlite3VdbeAddOp2(v, OP_Integer, 1, tmpReg);
      }
      sqlite3VdbeAddOp3(v, OP_Add, tmpReg, pWin->regApp, tmpReg);
      sqlite3VdbeAddOp3(v, OP_Gt, pWin->regApp+1, lbl, tmpReg);
      sqlite3VdbeAddOp3(v, OP_SeekRowid, csr, lbl, tmpReg);
      sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol, pWin->regResult);
︙︙
Changes to test/window4.tcl.
︙︙
267
268
269
270
271
272
273

































274
275
276

    lead(y) OVER win, 
    lead(y, 2) OVER win, 
    lead(y, 3, -1) OVER win
  FROM t1
  WINDOW win AS (ORDER BY x)
}



































finish_test









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




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

    lead(y) OVER win, 
    lead(y, 2) OVER win, 
    lead(y, 3, -1) OVER win
  FROM t1
  WINDOW win AS (ORDER BY x)
}

==========

execsql_test 8.0 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER, d INTEGER);
  INSERT INTO t1 VALUES(1, 2, 3, 4);
  INSERT INTO t1 VALUES(5, 6, 7, 8);
  INSERT INTO t1 VALUES(9, 10, 11, 12);
}

execsql_test 8.1 {
  SELECT row_number() OVER win,
         nth_value(d,2) OVER win,
         lead(d) OVER win
  FROM t1
  WINDOW win AS (ORDER BY a)
}

execsql_test 8.2 {
    SELECT row_number() OVER win,
           rank() OVER win,
           dense_rank() OVER win,
           ntile(2) OVER win,
           first_value(d) OVER win,
           last_value(d) OVER win,
           nth_value(d,2) OVER win,
           lead(d) OVER win,
           lag(d) OVER win,
           max(d) OVER win,
           min(d) OVER win
    FROM t1
    WINDOW win AS (ORDER BY a)
}

finish_test
Changes to test/window4.test.
︙︙
1162
1163
1164
1165
1166
1167
1168


































1169

    lead(y) OVER win, 
    lead(y, 2) OVER win, 
    lead(y, 3, -1) OVER win
  FROM t1
  WINDOW win AS (ORDER BY x)
} {4 6 8   6 8 10   8 10 -1   10 {} -1   {} {} -1}



































finish_test








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


1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203

    lead(y) OVER win, 
    lead(y, 2) OVER win, 
    lead(y, 3, -1) OVER win
  FROM t1
  WINDOW win AS (ORDER BY x)
} {4 6 8   6 8 10   8 10 -1   10 {} -1   {} {} -1}

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

do_execsql_test 8.0 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER, d INTEGER);
  INSERT INTO t1 VALUES(1, 2, 3, 4);
  INSERT INTO t1 VALUES(5, 6, 7, 8);
  INSERT INTO t1 VALUES(9, 10, 11, 12);
} {}

do_execsql_test 8.1 {
  SELECT row_number() OVER win,
         nth_value(d,2) OVER win,
         lead(d) OVER win
  FROM t1
  WINDOW win AS (ORDER BY a)
} {1 {} 8   2 8 12   3 8 {}}

do_execsql_test 8.2 {
  SELECT row_number() OVER win,
           rank() OVER win,
           dense_rank() OVER win,
           ntile(2) OVER win,
           first_value(d) OVER win,
           last_value(d) OVER win,
           nth_value(d,2) OVER win,
           lead(d) OVER win,
           lag(d) OVER win,
           max(d) OVER win,
           min(d) OVER win
    FROM t1
    WINDOW win AS (ORDER BY a)
} {1 1 1 1 4 4 {} 8 {} 4 4   2 2 2 1 4 8 8 12 4 8 4   3 3 3 2 4 12 8 {} 8 12 4}

finish_test
Added test/windowfault.test. 








































































































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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

# 2018 May 8
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix windowfault


do_execsql_test 1.0 {
  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,
           first_value(d) OVER win,
           last_value(d) OVER win,
           nth_value(d,2) OVER win,
           lead(d) OVER win,
           lag(d) OVER win,
           max(d) OVER win,
           min(d) OVER win
    FROM t1
    WINDOW win AS (ORDER BY a)
  }
} -test {
  faultsim_test_result {0 {1 1 1 1 4 4 {} 8 {} 4 4 2 2 2 1 4 8 8 12 4 8 4 3 3 3 2 4 12 8 {} 8 12 4}}
}


finish_test