/ 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 Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/window.c.

   998    998         pWin->csrApp = pParse->nTab++;
   999    999         sqlite3VdbeAddOp2(v, OP_OpenDup, pWin->csrApp, pMWin->iEphCsr);
  1000   1000       }
  1001   1001     }
  1002   1002   }
  1003   1003   
  1004   1004   /*
  1005         -** A "PRECEDING <expr>" (bEnd==0) or "FOLLOWING <expr>" (bEnd==1) has just 
  1006         -** been evaluated and the result left in register reg. This function generates
  1007         -** VM code to check that the value is a non-negative integer and throws
  1008         -** an exception if it is not.
         1005  +** A "PRECEDING <expr>" (eCond==0) or "FOLLOWING <expr>" (eCond==1) or the
         1006  +** value of the second argument to nth_value() (eCond==2) has just been
         1007  +** evaluated and the result left in register reg. This function generates VM
         1008  +** code to check that the value is a non-negative integer and throws an
         1009  +** exception if it is not.
  1009   1010   */
  1010         -static void windowCheckFrameOffset(Parse *pParse, int reg, int bEnd){
         1011  +static void windowCheckIntValue(Parse *pParse, int reg, int eCond){
  1011   1012     static const char *azErr[] = {
  1012   1013       "frame starting offset must be a non-negative integer",
  1013         -    "frame ending offset must be a non-negative integer"
         1014  +    "frame ending offset must be a non-negative integer",
         1015  +    "second argument to nth_value must be a positive integer"
  1014   1016     };
         1017  +  static int aOp[] = { OP_Ge, OP_Ge, OP_Gt };
  1015   1018     Vdbe *v = sqlite3GetVdbe(pParse);
  1016   1019     int regZero = sqlite3GetTempReg(pParse);
         1020  +  assert( eCond==0 || eCond==1 || eCond==2 );
  1017   1021     sqlite3VdbeAddOp2(v, OP_Integer, 0, regZero);
  1018   1022     sqlite3VdbeAddOp2(v, OP_MustBeInt, reg, sqlite3VdbeCurrentAddr(v)+2);
  1019   1023     VdbeCoverage(v);
  1020         -  sqlite3VdbeAddOp3(v, OP_Ge, regZero, sqlite3VdbeCurrentAddr(v)+2, reg);
         1024  +  sqlite3VdbeAddOp3(v, aOp[eCond], regZero, sqlite3VdbeCurrentAddr(v)+2, reg);
  1021   1025     VdbeCoverage(v);
  1022   1026     sqlite3VdbeAddOp2(v, OP_Halt, SQLITE_ERROR, OE_Abort);
  1023         -  sqlite3VdbeAppendP4(v, (void*)azErr[bEnd], P4_STATIC);
         1027  +  sqlite3VdbeAppendP4(v, (void*)azErr[eCond], P4_STATIC);
  1024   1028     sqlite3ReleaseTempReg(pParse, regZero);
  1025   1029   }
  1026   1030   
  1027   1031   /*
  1028   1032   ** Return the number of arguments passed to the window-function associated
  1029   1033   ** with the object passed as the only argument to this function.
  1030   1034   */
................................................................................
  1274   1278         int csr = pWin->csrApp;
  1275   1279         int lbl = sqlite3VdbeMakeLabel(v);
  1276   1280         int tmpReg = sqlite3GetTempReg(pParse);
  1277   1281         sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regResult);
  1278   1282   
  1279   1283         if( pFunc->zName==nth_valueName ){
  1280   1284           sqlite3VdbeAddOp3(v, OP_Column, pMWin->iEphCsr, pWin->iArgCol+1,tmpReg);
         1285  +        windowCheckIntValue(pParse, tmpReg, 2);
  1281   1286         }else{
  1282   1287           sqlite3VdbeAddOp2(v, OP_Integer, 1, tmpReg);
  1283   1288         }
  1284   1289         sqlite3VdbeAddOp3(v, OP_Add, tmpReg, pWin->regApp, tmpReg);
  1285   1290         sqlite3VdbeAddOp3(v, OP_Gt, pWin->regApp+1, lbl, tmpReg);
  1286   1291         VdbeCoverage(v);
  1287   1292         sqlite3VdbeAddOp3(v, OP_SeekRowid, csr, lbl, tmpReg);
................................................................................
  1572   1577     sqlite3VdbeAddOp2(v, OP_OpenDup, csrStart, pMWin->iEphCsr);
  1573   1578     sqlite3VdbeAddOp2(v, OP_OpenDup, csrEnd, pMWin->iEphCsr);
  1574   1579   
  1575   1580     /* If either regStart or regEnd are not non-negative integers, throw 
  1576   1581     ** an exception.  */
  1577   1582     if( pMWin->pStart ){
  1578   1583       sqlite3ExprCode(pParse, pMWin->pStart, regStart);
  1579         -    windowCheckFrameOffset(pParse, regStart, 0);
         1584  +    windowCheckIntValue(pParse, regStart, 0);
  1580   1585     }
  1581   1586     if( pMWin->pEnd ){
  1582   1587       sqlite3ExprCode(pParse, pMWin->pEnd, regEnd);
  1583         -    windowCheckFrameOffset(pParse, regEnd, 1);
         1588  +    windowCheckIntValue(pParse, regEnd, 1);
  1584   1589     }
  1585   1590   
  1586   1591     /* If this is "ROWS <expr1> FOLLOWING AND ROWS <expr2> FOLLOWING", do:
  1587   1592     **
  1588   1593     **   if( regEnd<regStart ){
  1589   1594     **     // The frame always consists of 0 rows
  1590   1595     **     regStart = regSize;

Changes to test/window4.tcl.

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

Changes to test/window4.test.

  1257   1257       (4, 3, NULL), (5, 3, 8), (6, 3, 1);
  1258   1258   } {}
  1259   1259   
  1260   1260   do_execsql_test 10.1 {
  1261   1261     SELECT id, min(b) OVER (PARTITION BY a ORDER BY id) FROM t7;
  1262   1262   } {1 2   2 2   3 2   4 {}   5 8   6 1}
  1263   1263   
         1264  +do_execsql_test 10.2 {
         1265  +  SELECT id, lead(b, -1) OVER (PARTITION BY a ORDER BY id) FROM t7;
         1266  +} {1 {}   2 2   3 {}   4 {}   5 {}   6 8}
         1267  +
         1268  +do_execsql_test 10.3 {
         1269  +  SELECT id, lag(b, -1) OVER (PARTITION BY a ORDER BY id) FROM t7;
         1270  +} {1 {}   2 4   3 {}   4 8   5 1   6 {}}
         1271  +
  1264   1272   finish_test

Changes to test/window6.test.

   278    278   
   279    279   do_execsql_test 10.0 {
   280    280     WITH t1(a,b) AS (VALUES(1,2))
   281    281     SELECT count() FILTER (where b<>5) OVER w1
   282    282       FROM t1
   283    283       WINDOW w1 AS (ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING);
   284    284   } {1}
          285  +
          286  +foreach {tn stmt} {
          287  +  1 "SELECT nth_value(b, 0) OVER (ORDER BY a) FROM t1"
          288  +  2 "SELECT nth_value(b, -1) OVER (ORDER BY a) FROM t1"
          289  +  3 "SELECT nth_value(b, '4ab') OVER (ORDER BY a) FROM t1"
          290  +  4 "SELECT nth_value(b, NULL) OVER (ORDER BY a) FROM t1"
          291  +  5 "SELECT nth_value(b, 8.5) OVER (ORDER BY a) FROM t1"
          292  +} {
          293  +  do_catchsql_test 10.1.$tn "
          294  +    WITH t1(a,b) AS ( VALUES(1, 2), (2, 3), (3, 4) )
          295  +    $stmt
          296  +  " {1 {second argument to nth_value must be a positive integer}}
          297  +}
          298  +
          299  +foreach {tn stmt res} {
          300  +  1 "SELECT nth_value(b, 1) OVER (ORDER BY a) FROM t1"         {2 2 2}
          301  +  2 "SELECT nth_value(b, 2) OVER (ORDER BY a) FROM t1"         {{} 3 3}
          302  +  3 "SELECT nth_value(b, '2') OVER (ORDER BY a) FROM t1"       {{} 3 3}
          303  +  4 "SELECT nth_value(b, 2.0) OVER (ORDER BY a) FROM t1"       {{} 3 3}
          304  +  5 "SELECT nth_value(b, '2.0') OVER (ORDER BY a) FROM t1"     {{} 3 3}
          305  +  6 "SELECT nth_value(b, 10000000) OVER (ORDER BY a) FROM t1"  {{} {} {}}
          306  +} {
          307  +  do_execsql_test 10.2.$tn "
          308  +    WITH t1(a,b) AS ( VALUES(1, 2), (2, 3), (3, 4) )
          309  +    $stmt
          310  +  " $res
          311  +}
   285    312   
   286    313   finish_test