/ Check-in [2879a691]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Changes to src/vdbe.c.

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

Changes to src/window.c.

   211    211     sqlite3_context *pCtx, 
   212    212     int nArg,
   213    213     sqlite3_value **apArg
   214    214   ){
   215    215     struct NthValueCtx *p;
   216    216     p = (struct NthValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p));
   217    217     if( p ){
   218         -    i64 iVal = sqlite3_value_int64(apArg[1]);
          218  +    i64 iVal;
          219  +    switch( sqlite3_value_numeric_type(apArg[1]) ){
          220  +      case SQLITE_INTEGER:
          221  +        iVal = sqlite3_value_int64(apArg[1]);
          222  +        break;
          223  +      case SQLITE_FLOAT: {
          224  +        double fVal = sqlite3_value_double(apArg[1]);
          225  +        if( ((i64)fVal)!=fVal ) goto error_out;
          226  +        iVal = (i64)fVal;
          227  +        break;
          228  +      }
          229  +      default:
          230  +        goto error_out;
          231  +    }
          232  +    if( iVal<=0 ) goto error_out;
          233  +
   219    234       p->nStep++;
   220    235       if( iVal==p->nStep ){
   221    236         p->pValue = sqlite3_value_dup(apArg[0]);
          237  +      if( !p->pValue ){
          238  +        sqlite3_result_error_nomem(pCtx);
          239  +      }
   222    240       }
   223    241     }
   224    242     UNUSED_PARAMETER(nArg);
   225    243     UNUSED_PARAMETER(apArg);
          244  +  return;
          245  +
          246  + error_out:
          247  +  sqlite3_result_error(
          248  +      pCtx, "second argument to nth_value must be a positive integer", -1
          249  +  );
   226    250   }
   227    251   static void nth_valueValueFunc(sqlite3_context *pCtx){
   228    252     struct NthValueCtx *p;
   229    253     p = (struct NthValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p));
   230    254     if( p && p->pValue ){
   231    255       sqlite3_result_value(pCtx, p->pValue);
   232    256     }
................................................................................
   247    271     int nArg,
   248    272     sqlite3_value **apArg
   249    273   ){
   250    274     struct NthValueCtx *p;
   251    275     p = (struct NthValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p));
   252    276     if( p && p->pValue==0 ){
   253    277       p->pValue = sqlite3_value_dup(apArg[0]);
          278  +    if( !p->pValue ){
          279  +      sqlite3_result_error_nomem(pCtx);
          280  +    }
   254    281     }
   255    282     UNUSED_PARAMETER(nArg);
   256    283     UNUSED_PARAMETER(apArg);
   257    284   }
   258    285   static void first_valueValueFunc(sqlite3_context *pCtx){
   259    286     struct NthValueCtx *p;
   260    287     p = (struct NthValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p));
................................................................................
  1068   1095     }
  1069   1096   
  1070   1097     pWin = (Window*)sqlite3DbMallocZero(pParse->db, sizeof(Window));
  1071   1098     if( pWin==0 ) goto windowAllocErr;
  1072   1099     pWin->eType = eType;
  1073   1100     pWin->eStart = eStart;
  1074   1101     pWin->eEnd = eEnd;
         1102  +  if( eExclude==0 && OptimizationDisabled(pParse->db, SQLITE_QueryFlattener) ){
         1103  +    eExclude = TK_NO;
         1104  +  }
  1075   1105     pWin->eExclude = eExclude;
  1076   1106     pWin->bImplicitFrame = bImplicitFrame;
  1077   1107     pWin->pEnd = sqlite3WindowOffsetExpr(pParse, pEnd);
  1078   1108     pWin->pStart = sqlite3WindowOffsetExpr(pParse, pStart);
  1079   1109     return pWin;
  1080   1110   
  1081   1111   windowAllocErr:
................................................................................
  1532   1562       sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regAccum);
  1533   1563     }
  1534   1564   
  1535   1565     sqlite3VdbeAddOp3(v, OP_SeekGE, csr, lblBrk, pMWin->regStartRowid);
  1536   1566     addrNext = sqlite3VdbeCurrentAddr(v);
  1537   1567     sqlite3VdbeAddOp2(v, OP_Rowid, csr, regRowid);
  1538   1568     sqlite3VdbeAddOp3(v, OP_Gt, pMWin->regEndRowid, lblBrk, regRowid);
         1569  +
  1539   1570     if( pMWin->eExclude==TK_CURRENT ){
  1540   1571       sqlite3VdbeAddOp3(v, OP_Eq, regCRowid, lblNext, regRowid);
  1541   1572     }else if( pMWin->eExclude!=TK_NO ){
  1542   1573       int addr;
  1543         -    int addrEq = 0;;
  1544         -    KeyInfo *pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pMWin->pOrderBy,0,0);
         1574  +    int addrEq = 0;
         1575  +    KeyInfo *pKeyInfo;
         1576  +
         1577  +    pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pMWin->pOrderBy, 0, 0);
  1545   1578       if( pMWin->eExclude==TK_TIES ){
  1546   1579         addrEq = sqlite3VdbeAddOp3(v, OP_Eq, regCRowid, lblNext, regRowid);
  1547   1580       }
  1548   1581       windowReadPeerValues(p, csr, regPeer);
  1549   1582       sqlite3VdbeAddOp3(v, OP_Compare, regPeer, regCPeer, nPeer);
  1550   1583       sqlite3VdbeAppendP4(v, (void*)pKeyInfo, P4_KEYINFO);
  1551   1584       addr = sqlite3VdbeCurrentAddr(v)+1;
................................................................................
  1664   1697     int regArg;
  1665   1698     int nArg = 0;
  1666   1699     Window *pWin;
  1667   1700     for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
  1668   1701       FuncDef *pFunc = pWin->pFunc;
  1669   1702       sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regAccum);
  1670   1703       nArg = MAX(nArg, windowArgCount(pWin));
  1671         -    if( pWin->eExclude==0 ){
         1704  +    if( pMWin->regStartRowid==0 ){
  1672   1705         if( pFunc->zName==nth_valueName || pFunc->zName==first_valueName ){
  1673   1706           sqlite3VdbeAddOp2(v, OP_Integer, 0, pWin->regApp);
  1674   1707           sqlite3VdbeAddOp2(v, OP_Integer, 0, pWin->regApp+1);
  1675   1708         }
  1676   1709   
  1677   1710         if( (pFunc->funcFlags & SQLITE_FUNC_MINMAX) && pWin->csrApp ){
  1678   1711           assert( pWin->eStart!=TK_UNBOUNDED );
................................................................................
  1760   1793   ){
  1761   1794     Parse *pParse = p->pParse;
  1762   1795     Vdbe *v = sqlite3GetVdbe(pParse);
  1763   1796     int reg1 = sqlite3GetTempReg(pParse);
  1764   1797     int reg2 = sqlite3GetTempReg(pParse);
  1765   1798     int arith = OP_Add;
  1766   1799     int addrGe;
  1767         -  int addrNotNull;
  1768   1800   
  1769   1801     int regString = ++pParse->nMem;
  1770   1802   
  1771   1803     assert( op==OP_Ge || op==OP_Gt || op==OP_Le );
  1772   1804     assert( p->pMWin->pOrderBy && p->pMWin->pOrderBy->nExpr==1 );
  1773   1805     if( p->pMWin->pOrderBy->a[0].sortOrder ){
  1774   1806       switch( op ){
................................................................................
  2332   2364     int lblWhereEnd;                /* Label just before sqlite3WhereEnd() code */
  2333   2365   
  2334   2366     assert( pMWin->eStart==TK_PRECEDING || pMWin->eStart==TK_CURRENT 
  2335   2367          || pMWin->eStart==TK_FOLLOWING || pMWin->eStart==TK_UNBOUNDED 
  2336   2368     );
  2337   2369     assert( pMWin->eEnd==TK_FOLLOWING || pMWin->eEnd==TK_CURRENT 
  2338   2370          || pMWin->eEnd==TK_UNBOUNDED || pMWin->eEnd==TK_PRECEDING 
         2371  +  );
         2372  +  assert( pMWin->eExclude==0 || pMWin->eExclude==TK_CURRENT
         2373  +       || pMWin->eExclude==TK_GROUP || pMWin->eExclude==TK_TIES
         2374  +       || pMWin->eExclude==TK_NO
  2339   2375     );
  2340   2376   
  2341   2377     lblWhereEnd = sqlite3VdbeMakeLabel(pParse);
  2342   2378   
  2343   2379     /* Fill in the context object */
  2344   2380     memset(&s, 0, sizeof(WindowCodeArg));
  2345   2381     s.pParse = pParse;

Changes to test/permutations.test.

  1028   1028   } -files [
  1029   1029     test_set [glob -nocomplain $::testdir/../ext/rbu/*.test] -exclude rbu.test
  1030   1030   ]
  1031   1031   
  1032   1032   test_suite "no_optimization" -description {
  1033   1033     Run test scripts with optimizations disabled using the
  1034   1034     sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS) interface.
  1035         -} -files {
  1036         -  where.test where2.test where3.test where4.test where5.test
  1037         -  where6.test where7.test where8.test where9.test
  1038         -  whereA.test whereB.test wherelimit.test
  1039         -  select1.test select2.test select3.test select4.test select5.test
  1040         -  select7.test select8.test selectA.test selectC.test
  1041         -} -dbconfig {
         1035  +} -files [
         1036  +  test_set \
         1037  +    [glob -nocomplain $::testdir/window*.test]                       \
         1038  +    where.test where2.test where3.test where4.test where5.test       \
         1039  +    where6.test where7.test where8.test where9.test                  \
         1040  +    whereA.test whereB.test wherelimit.test                          \
         1041  +    select1.test select2.test select3.test select4.test select5.test \
         1042  +    select7.test select8.test selectA.test selectC.test     
         1043  +] -dbconfig {
  1042   1044     optimization_control $::dbhandle all 0
  1043   1045   }
  1044   1046   
  1045   1047   test_suite "prepare" -description {
  1046   1048     Run tests with the db connection using sqlite3_prepare() instead of _v2().
  1047   1049   } -dbconfig {
  1048   1050     $::dbhandle version -use-legacy-prepare 1

Changes to test/window4.test.

   146    146     SELECT a, lag(b, 3, 'abc') OVER (ORDER BY a) FROM t4
   147    147   } {1 abc   2 abc   3 abc   4 A   5 B   6 C   7 D   8 E   9 F   10 G}
   148    148   
   149    149   do_execsql_test 2.4.1 {
   150    150     SELECT group_concat(b, '.') OVER (
   151    151       ORDER BY a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
   152    152     ) FROM t4
   153         -} {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}
          153  +} {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
          154  +  E.F.G.H.I.J   F.G.H.I.J   G.H.I.J   H.I.J   I.J   J}
   154    155   
   155    156   do_execsql_test 3.0 {
   156    157     DROP TABLE IF EXISTS t5;
   157    158     CREATE TABLE t5(a INTEGER PRIMARY KEY, b TEXT, c TEXT, d INTEGER);
   158    159     INSERT INTO t5 VALUES(1, 'A', 'one',   5);
   159    160     INSERT INTO t5 VALUES(2, 'B', 'two',   4);
   160    161     INSERT INTO t5 VALUES(3, 'A', 'three', 3);

Changes to test/windowfault.test.

    24     24     CREATE TABLE t1(a, b, c, d);
    25     25     INSERT INTO t1 VALUES(1, 2, 3, 4);
    26     26     INSERT INTO t1 VALUES(5, 6, 7, 8);
    27     27     INSERT INTO t1 VALUES(9, 10, 11, 12);
    28     28   }
    29     29   faultsim_save_and_close
    30     30   
    31         -do_faultsim_test 1 -start 1 -faults oom-* -prep {
           31  +do_faultsim_test 1 -start 1 -faults oom-t* -prep {
    32     32     faultsim_restore_and_reopen
    33     33   } -body {
    34     34     execsql {
    35     35       SELECT row_number() OVER win,
    36     36              rank() OVER win,
    37     37              dense_rank() OVER win,
    38     38              ntile(2) OVER win,