/ Check-in [fadd4dc1]
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 | SQL 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
Context
2018-06-15
20:46
Add extra OOM test. check-in: ac251f72 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: fadd4dc1 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: 3839fb18 user: dan tags: exp-window-functions
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/parse.y.

   531    531   oneselect(A) ::= SELECT(S) distinct(D) selcollist(W) from(X) where_opt(Y)
   532    532                    groupby_opt(P) having_opt(Q) windowdefn_opt(R)
   533    533                    orderby_opt(Z) limit_opt(L). {
   534    534   #if SELECTTRACE_ENABLED
   535    535     Token s = S; /*A-overwrites-S*/
   536    536   #endif
   537    537     A = sqlite3SelectNew(pParse,W,X,Y,P,Q,Z,D,L);
   538         -  if( A ) A->pWinDefn = R;
          538  +  if( A ){
          539  +    A->pWinDefn = R;
          540  +  }else{
          541  +    sqlite3WindowListDelete(pParse->db, R);
          542  +  }
   539    543   #if SELECTTRACE_ENABLED
   540    544     /* Populate the Select.zSelName[] string that is used to help with
   541    545     ** query planner debugging, to differentiate between multiple Select
   542    546     ** objects in a complex query.
   543    547     **
   544    548     ** If the SELECT keyword is immediately followed by a C-style comment
   545    549     ** then extract the first few alphanumeric characters from within that

Changes to src/window.c.

   388    388     sqlite3_value **apArg
   389    389   ){
   390    390     struct LastValueCtx *p;
   391    391     p = (struct LastValueCtx *)sqlite3_aggregate_context(pCtx, sizeof(*p));
   392    392     if( p ){
   393    393       sqlite3_value_free(p->pVal);
   394    394       p->pVal = sqlite3_value_dup(apArg[0]);
   395         -    p->nVal++;
          395  +    if( p->pVal==0 ){
          396  +      sqlite3_result_error_nomem(pCtx);
          397  +    }else{
          398  +      p->nVal++;
          399  +    }
   396    400     }
   397    401   }
   398    402   static void last_valueInvFunc(
   399    403     sqlite3_context *pCtx, 
   400    404     int nArg,
   401    405     sqlite3_value **apArg
   402    406   ){
................................................................................
   737    741         sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regAccum);
   738    742       }
   739    743   
   740    744       pSub = sqlite3SelectNew(
   741    745           pParse, pSublist, pSrc, pWhere, pGroupBy, pHaving, pSort, 0, 0
   742    746       );
   743    747       p->pSrc = sqlite3SrcListAppend(db, 0, 0, 0);
          748  +    assert( p->pSrc || db->mallocFailed );
   744    749       if( p->pSrc ){
   745    750         int iTab;
   746    751         ExprList *pList = 0;
   747    752         p->pSrc->a[0].pSelect = pSub;
   748    753         sqlite3SrcListAssignCursors(pParse, p->pSrc);
   749    754         if( sqlite3ExpandSubquery(pParse, &p->pSrc->a[0]) ){
   750    755           rc = SQLITE_NOMEM;
   751    756         }else{
   752    757           pSub->selFlags |= SF_Expanded;
   753    758           p->selFlags &= ~SF_Aggregate;
   754    759           sqlite3SelectPrep(pParse, pSub, 0);
   755    760         }
          761  +
          762  +      sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pMWin->iEphCsr, pSublist->nExpr);
          763  +    }else{
          764  +      sqlite3SelectDelete(db, pSub);
   756    765       }
   757         -
   758         -    sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pMWin->iEphCsr, pSublist->nExpr);
          766  +    if( db->mallocFailed ) rc = SQLITE_NOMEM;
   759    767     }
   760    768   
   761    769     return rc;
   762    770   }
   763    771   
   764    772   /*
   765    773   ** Free the Window object passed as the second argument.
................................................................................
  1158   1166       ){
  1159   1167         int csr = pWin->csrApp;
  1160   1168         int lbl = sqlite3VdbeMakeLabel(v);
  1161   1169         int tmpReg = sqlite3GetTempReg(pParse);
  1162   1170         sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regResult);
  1163   1171   
  1164   1172         if( pFunc->xSFunc==nth_valueStepFunc ){
  1165         -        sqlite3VdbeAddOp3(v, OP_Column, pWin->iEphCsr, pWin->iArgCol+1, tmpReg);
         1173  +        sqlite3VdbeAddOp3(v, OP_Column, pMWin->iEphCsr, pWin->iArgCol+1,tmpReg);
  1166   1174         }else{
  1167   1175           sqlite3VdbeAddOp2(v, OP_Integer, 1, tmpReg);
  1168   1176         }
  1169   1177         sqlite3VdbeAddOp3(v, OP_Add, tmpReg, pWin->regApp, tmpReg);
  1170   1178         sqlite3VdbeAddOp3(v, OP_Gt, pWin->regApp+1, lbl, tmpReg);
  1171   1179         sqlite3VdbeAddOp3(v, OP_SeekRowid, csr, lbl, tmpReg);
  1172   1180         sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol, pWin->regResult);

Changes to test/window4.tcl.

   267    267       lead(y) OVER win, 
   268    268       lead(y, 2) OVER win, 
   269    269       lead(y, 3, -1) OVER win
   270    270     FROM t1
   271    271     WINDOW win AS (ORDER BY x)
   272    272   }
   273    273   
          274  +==========
          275  +
          276  +execsql_test 8.0 {
          277  +  DROP TABLE IF EXISTS t1;
          278  +  CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER, d INTEGER);
          279  +  INSERT INTO t1 VALUES(1, 2, 3, 4);
          280  +  INSERT INTO t1 VALUES(5, 6, 7, 8);
          281  +  INSERT INTO t1 VALUES(9, 10, 11, 12);
          282  +}
          283  +
          284  +execsql_test 8.1 {
          285  +  SELECT row_number() OVER win,
          286  +         nth_value(d,2) OVER win,
          287  +         lead(d) OVER win
          288  +  FROM t1
          289  +  WINDOW win AS (ORDER BY a)
          290  +}
          291  +
          292  +execsql_test 8.2 {
          293  +    SELECT row_number() OVER win,
          294  +           rank() OVER win,
          295  +           dense_rank() OVER win,
          296  +           ntile(2) OVER win,
          297  +           first_value(d) OVER win,
          298  +           last_value(d) OVER win,
          299  +           nth_value(d,2) OVER win,
          300  +           lead(d) OVER win,
          301  +           lag(d) OVER win,
          302  +           max(d) OVER win,
          303  +           min(d) OVER win
          304  +    FROM t1
          305  +    WINDOW win AS (ORDER BY a)
          306  +}
   274    307   
   275    308   finish_test
   276    309   

Changes to test/window4.test.

  1162   1162       lead(y) OVER win, 
  1163   1163       lead(y, 2) OVER win, 
  1164   1164       lead(y, 3, -1) OVER win
  1165   1165     FROM t1
  1166   1166     WINDOW win AS (ORDER BY x)
  1167   1167   } {4 6 8   6 8 10   8 10 -1   10 {} -1   {} {} -1}
  1168   1168   
         1169  +#==========================================================================
         1170  +
         1171  +do_execsql_test 8.0 {
         1172  +  DROP TABLE IF EXISTS t1;
         1173  +  CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER, d INTEGER);
         1174  +  INSERT INTO t1 VALUES(1, 2, 3, 4);
         1175  +  INSERT INTO t1 VALUES(5, 6, 7, 8);
         1176  +  INSERT INTO t1 VALUES(9, 10, 11, 12);
         1177  +} {}
         1178  +
         1179  +do_execsql_test 8.1 {
         1180  +  SELECT row_number() OVER win,
         1181  +         nth_value(d,2) OVER win,
         1182  +         lead(d) OVER win
         1183  +  FROM t1
         1184  +  WINDOW win AS (ORDER BY a)
         1185  +} {1 {} 8   2 8 12   3 8 {}}
         1186  +
         1187  +do_execsql_test 8.2 {
         1188  +  SELECT row_number() OVER win,
         1189  +           rank() OVER win,
         1190  +           dense_rank() OVER win,
         1191  +           ntile(2) OVER win,
         1192  +           first_value(d) OVER win,
         1193  +           last_value(d) OVER win,
         1194  +           nth_value(d,2) OVER win,
         1195  +           lead(d) OVER win,
         1196  +           lag(d) OVER win,
         1197  +           max(d) OVER win,
         1198  +           min(d) OVER win
         1199  +    FROM t1
         1200  +    WINDOW win AS (ORDER BY a)
         1201  +} {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}
         1202  +
  1169   1203   finish_test

Added test/windowfault.test.

            1  +# 2018 May 8
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library.
           12  +#
           13  +
           14  +set testdir [file dirname $argv0]
           15  +source $testdir/tester.tcl
           16  +set testprefix windowfault
           17  +
           18  +
           19  +do_execsql_test 1.0 {
           20  +  CREATE TABLE t1(a, b, c, d);
           21  +  INSERT INTO t1 VALUES(1, 2, 3, 4);
           22  +  INSERT INTO t1 VALUES(5, 6, 7, 8);
           23  +  INSERT INTO t1 VALUES(9, 10, 11, 12);
           24  +}
           25  +faultsim_save_and_close
           26  +
           27  +do_faultsim_test 1 -start 1 -faults oom-* -prep {
           28  +  faultsim_restore_and_reopen
           29  +} -body {
           30  +  execsql {
           31  +    SELECT row_number() OVER win,
           32  +           rank() OVER win,
           33  +           dense_rank() OVER win,
           34  +           ntile(2) OVER win,
           35  +           first_value(d) OVER win,
           36  +           last_value(d) OVER win,
           37  +           nth_value(d,2) OVER win,
           38  +           lead(d) OVER win,
           39  +           lag(d) OVER win,
           40  +           max(d) OVER win,
           41  +           min(d) OVER win
           42  +    FROM t1
           43  +    WINDOW win AS (ORDER BY a)
           44  +  }
           45  +} -test {
           46  +  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}}
           47  +}
           48  +
           49  +
           50  +finish_test
           51  +