/ Check-in [1a06e57a]
Login

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

Overview
Comment:Throw an error if the second argument passed to nth_value() is not a positive integer.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256:1a06e57a0b4279fa580c7ff4f152645f005794aaf86eeabf694637b7da11f763
User & Date: dan 2018-07-09 13:31:18
Context
2018-07-09
16:24
Simplification to the grammar rules for window functions. Fix a memory leak that can follow an OOM while parsing a comma-separated list of window definitions. check-in: a568f9c9 user: drh tags: trunk
13:31
Throw an error if the second argument passed to nth_value() is not a positive integer. check-in: 1a06e57a user: dan tags: trunk
06:51
Fix a bad assert() in window.c. check-in: fe8aaf0c user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/window.c.

998
999
1000
1001
1002
1003
1004
1005

1006
1007
1008
1009
1010
1011
1012
1013

1014

1015
1016

1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
....
1274
1275
1276
1277
1278
1279
1280

1281
1282
1283
1284
1285
1286
1287
....
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
      pWin->csrApp = pParse->nTab++;
      sqlite3VdbeAddOp2(v, OP_OpenDup, pWin->csrApp, pMWin->iEphCsr);
    }
  }
}

/*
** A "PRECEDING <expr>" (bEnd==0) or "FOLLOWING <expr>" (bEnd==1) has just 

** been evaluated and the result left in register reg. This function generates
** VM code to check that the value is a non-negative integer and throws
** an exception if it is not.
*/
static void windowCheckFrameOffset(Parse *pParse, int reg, int bEnd){
  static const char *azErr[] = {
    "frame starting offset must be a non-negative integer",
    "frame ending offset must be a non-negative integer"

  };

  Vdbe *v = sqlite3GetVdbe(pParse);
  int regZero = sqlite3GetTempReg(pParse);

  sqlite3VdbeAddOp2(v, OP_Integer, 0, regZero);
  sqlite3VdbeAddOp2(v, OP_MustBeInt, reg, sqlite3VdbeCurrentAddr(v)+2);
  VdbeCoverage(v);
  sqlite3VdbeAddOp3(v, OP_Ge, regZero, sqlite3VdbeCurrentAddr(v)+2, reg);
  VdbeCoverage(v);
  sqlite3VdbeAddOp2(v, OP_Halt, SQLITE_ERROR, OE_Abort);
  sqlite3VdbeAppendP4(v, (void*)azErr[bEnd], P4_STATIC);
  sqlite3ReleaseTempReg(pParse, regZero);
}

/*
** Return the number of arguments passed to the window-function associated
** with the object passed as the only argument to this function.
*/
................................................................................
      int csr = pWin->csrApp;
      int lbl = sqlite3VdbeMakeLabel(v);
      int tmpReg = sqlite3GetTempReg(pParse);
      sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regResult);

      if( pFunc->zName==nth_valueName ){
        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);
      VdbeCoverage(v);
      sqlite3VdbeAddOp3(v, OP_SeekRowid, csr, lbl, tmpReg);
................................................................................
  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);
    windowCheckFrameOffset(pParse, regStart, 0);
  }
  if( pMWin->pEnd ){
    sqlite3ExprCode(pParse, pMWin->pEnd, regEnd);
    windowCheckFrameOffset(pParse, regEnd, 1);
  }

  /* If this is "ROWS <expr1> FOLLOWING AND ROWS <expr2> FOLLOWING", do:
  **
  **   if( regEnd<regStart ){
  **     // The frame always consists of 0 rows
  **     regStart = regSize;







|
>
|
|
|

|


|
>

>


>



|


|







 







>







 







|



|







998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
....
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
....
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
      pWin->csrApp = pParse->nTab++;
      sqlite3VdbeAddOp2(v, OP_OpenDup, pWin->csrApp, pMWin->iEphCsr);
    }
  }
}

/*
** A "PRECEDING <expr>" (eCond==0) or "FOLLOWING <expr>" (eCond==1) or the
** value of the second argument to nth_value() (eCond==2) has just been
** evaluated and the result left in register reg. This function generates VM
** code to check that the value is a non-negative integer and throws an
** exception if it is not.
*/
static void windowCheckIntValue(Parse *pParse, int reg, int eCond){
  static const char *azErr[] = {
    "frame starting offset must be a non-negative integer",
    "frame ending offset must be a non-negative integer",
    "second argument to nth_value must be a positive integer"
  };
  static int aOp[] = { OP_Ge, OP_Ge, OP_Gt };
  Vdbe *v = sqlite3GetVdbe(pParse);
  int regZero = sqlite3GetTempReg(pParse);
  assert( eCond==0 || eCond==1 || eCond==2 );
  sqlite3VdbeAddOp2(v, OP_Integer, 0, regZero);
  sqlite3VdbeAddOp2(v, OP_MustBeInt, reg, sqlite3VdbeCurrentAddr(v)+2);
  VdbeCoverage(v);
  sqlite3VdbeAddOp3(v, aOp[eCond], regZero, sqlite3VdbeCurrentAddr(v)+2, reg);
  VdbeCoverage(v);
  sqlite3VdbeAddOp2(v, OP_Halt, SQLITE_ERROR, OE_Abort);
  sqlite3VdbeAppendP4(v, (void*)azErr[eCond], P4_STATIC);
  sqlite3ReleaseTempReg(pParse, regZero);
}

/*
** Return the number of arguments passed to the window-function associated
** with the object passed as the only argument to this function.
*/
................................................................................
      int csr = pWin->csrApp;
      int lbl = sqlite3VdbeMakeLabel(v);
      int tmpReg = sqlite3GetTempReg(pParse);
      sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regResult);

      if( pFunc->zName==nth_valueName ){
        sqlite3VdbeAddOp3(v, OP_Column, pMWin->iEphCsr, pWin->iArgCol+1,tmpReg);
        windowCheckIntValue(pParse, tmpReg, 2);
      }else{
        sqlite3VdbeAddOp2(v, OP_Integer, 1, tmpReg);
      }
      sqlite3VdbeAddOp3(v, OP_Add, tmpReg, pWin->regApp, tmpReg);
      sqlite3VdbeAddOp3(v, OP_Gt, pWin->regApp+1, lbl, tmpReg);
      VdbeCoverage(v);
      sqlite3VdbeAddOp3(v, OP_SeekRowid, csr, lbl, tmpReg);
................................................................................
  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);
    windowCheckIntValue(pParse, regStart, 0);
  }
  if( pMWin->pEnd ){
    sqlite3ExprCode(pParse, pMWin->pEnd, regEnd);
    windowCheckIntValue(pParse, regEnd, 1);
  }

  /* If this is "ROWS <expr1> FOLLOWING AND ROWS <expr2> FOLLOWING", do:
  **
  **   if( regEnd<regStart ){
  **     // The frame always consists of 0 rows
  **     regStart = regSize;

Changes to test/window4.tcl.

346
347
348
349
350
351
352








353
354
355
356
  INSERT INTO t7(id, a, b) VALUES
    (1, 1, 2), (2, 1, NULL), (3, 1, 4),
    (4, 3, NULL), (5, 3, 8), (6, 3, 1);
}
execsql_test 10.1 {
  SELECT id, min(b) OVER (PARTITION BY a ORDER BY id) FROM t7;
}










finish_test








>
>
>
>
>
>
>
>




346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
  INSERT INTO t7(id, a, b) VALUES
    (1, 1, 2), (2, 1, NULL), (3, 1, 4),
    (4, 3, NULL), (5, 3, 8), (6, 3, 1);
}
execsql_test 10.1 {
  SELECT id, min(b) OVER (PARTITION BY a ORDER BY id) FROM t7;
}

execsql_test 10.2 {
  SELECT id, lead(b, -1) OVER (PARTITION BY a ORDER BY id) FROM t7;
}
execsql_test 10.3 {
  SELECT id, lag(b, -1) OVER (PARTITION BY a ORDER BY id) FROM t7;
}



finish_test

Changes to test/window4.test.

1257
1258
1259
1260
1261
1262
1263








1264
    (4, 3, NULL), (5, 3, 8), (6, 3, 1);
} {}

do_execsql_test 10.1 {
  SELECT id, min(b) OVER (PARTITION BY a ORDER BY id) FROM t7;
} {1 2   2 2   3 2   4 {}   5 8   6 1}









finish_test







>
>
>
>
>
>
>
>

1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
    (4, 3, NULL), (5, 3, 8), (6, 3, 1);
} {}

do_execsql_test 10.1 {
  SELECT id, min(b) OVER (PARTITION BY a ORDER BY id) FROM t7;
} {1 2   2 2   3 2   4 {}   5 8   6 1}

do_execsql_test 10.2 {
  SELECT id, lead(b, -1) OVER (PARTITION BY a ORDER BY id) FROM t7;
} {1 {}   2 2   3 {}   4 {}   5 {}   6 8}

do_execsql_test 10.3 {
  SELECT id, lag(b, -1) OVER (PARTITION BY a ORDER BY id) FROM t7;
} {1 {}   2 4   3 {}   4 8   5 1   6 {}}

finish_test

Changes to test/window6.test.

278
279
280
281
282
283
284
285



























286

do_execsql_test 10.0 {
  WITH t1(a,b) AS (VALUES(1,2))
  SELECT count() FILTER (where b<>5) OVER w1
    FROM t1
    WINDOW w1 AS (ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING);
} {1}




























finish_test








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

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

do_execsql_test 10.0 {
  WITH t1(a,b) AS (VALUES(1,2))
  SELECT count() FILTER (where b<>5) OVER w1
    FROM t1
    WINDOW w1 AS (ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING);
} {1}

foreach {tn stmt} {
  1 "SELECT nth_value(b, 0) OVER (ORDER BY a) FROM t1"
  2 "SELECT nth_value(b, -1) OVER (ORDER BY a) FROM t1"
  3 "SELECT nth_value(b, '4ab') OVER (ORDER BY a) FROM t1"
  4 "SELECT nth_value(b, NULL) OVER (ORDER BY a) FROM t1"
  5 "SELECT nth_value(b, 8.5) OVER (ORDER BY a) FROM t1"
} {
  do_catchsql_test 10.1.$tn "
    WITH t1(a,b) AS ( VALUES(1, 2), (2, 3), (3, 4) )
    $stmt
  " {1 {second argument to nth_value must be a positive integer}}
}

foreach {tn stmt res} {
  1 "SELECT nth_value(b, 1) OVER (ORDER BY a) FROM t1"         {2 2 2}
  2 "SELECT nth_value(b, 2) OVER (ORDER BY a) FROM t1"         {{} 3 3}
  3 "SELECT nth_value(b, '2') OVER (ORDER BY a) FROM t1"       {{} 3 3}
  4 "SELECT nth_value(b, 2.0) OVER (ORDER BY a) FROM t1"       {{} 3 3}
  5 "SELECT nth_value(b, '2.0') OVER (ORDER BY a) FROM t1"     {{} 3 3}
  6 "SELECT nth_value(b, 10000000) OVER (ORDER BY a) FROM t1"  {{} {} {}}
} {
  do_execsql_test 10.2.$tn "
    WITH t1(a,b) AS ( VALUES(1, 2), (2, 3), (3, 4) )
    $stmt
  " $res
}

finish_test