/ Check-in [fa653805]
Login

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

Overview
Comment:Use separate opcodes, OP_AggValue and OP_AggInverse, for the new callbacks associated with Window Functions, for improved readability of EXPLAIN output.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256:fa6538050938ffeb042fbee31512514d6d3ace514b2a3dfd8365bbeb13f35a78
User & Date: drh 2018-07-05 21:22:57
Context
2018-07-06
07:42
Return an error if DISTINCT is used with a window-function (e.g. "count(DISTINCT <expr>) OVER (...)"). check-in: d59bcc8e user: dan tags: trunk
2018-07-05
21:22
Use separate opcodes, OP_AggValue and OP_AggInverse, for the new callbacks associated with Window Functions, for improved readability of EXPLAIN output. check-in: fa653805 user: drh tags: trunk
20:33
Update the recipe for resetting a database using SQLITE_DBCONFIG_RESET_DATABASE. check-in: c43dd23f user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

  5183   5183         }
  5184   5184         if( !pColl ){
  5185   5185           pColl = pParse->db->pDfltColl;
  5186   5186         }
  5187   5187         if( regHit==0 && pAggInfo->nAccumulator ) regHit = ++pParse->nMem;
  5188   5188         sqlite3VdbeAddOp4(v, OP_CollSeq, regHit, 0, 0, (char *)pColl, P4_COLLSEQ);
  5189   5189       }
  5190         -    sqlite3VdbeAddOp3(v, OP_AggStep0, 0, regAgg, pF->iMem);
         5190  +    sqlite3VdbeAddOp3(v, OP_AggStep, 0, regAgg, pF->iMem);
  5191   5191       sqlite3VdbeAppendP4(v, pF->pFunc, P4_FUNCDEF);
  5192   5192       sqlite3VdbeChangeP5(v, (u8)nArg);
  5193   5193       sqlite3ExprCacheAffinityChange(pParse, regAgg, nArg);
  5194   5194       sqlite3ReleaseTempRange(pParse, regAgg, nArg);
  5195   5195       if( addrNext ){
  5196   5196         sqlite3VdbeResolveLabel(v, addrNext);
  5197   5197         sqlite3ExprCacheClear(pParse);

Changes to src/vdbe.c.

  6284   6284     if( pIn1->u.i>SMALLEST_INT64 ) pIn1->u.i--;
  6285   6285     VdbeBranchTaken(pIn1->u.i==0, 2);
  6286   6286     if( pIn1->u.i==0 ) goto jump_to_p2;
  6287   6287     break;
  6288   6288   }
  6289   6289   
  6290   6290   
  6291         -/* Opcode: AggStep0 P1 P2 P3 P4 P5
         6291  +/* Opcode: AggStep * P2 P3 P4 P5
  6292   6292   ** Synopsis: accum=r[P3] step(r[P2@P5])
  6293   6293   **
  6294         -** Execute the xStep (if P1==0) or xInverse (if P1!=0) function for an
  6295         -** aggregate.  The function has P5 arguments.  P4 is a pointer to the 
         6294  +** Execute the xStep function for an aggregate.
         6295  +** The function has P5 arguments.  P4 is a pointer to the 
         6296  +** FuncDef structure that specifies the function.  Register P3 is the
         6297  +** accumulator.
         6298  +**
         6299  +** The P5 arguments are taken from register P2 and its
         6300  +** successors.
         6301  +*/
         6302  +/* Opcode: AggInverse * P2 P3 P4 P5
         6303  +** Synopsis: accum=r[P3] inverse(r[P2@P5])
         6304  +**
         6305  +** Execute the xInverse function for an aggregate.
         6306  +** The function has P5 arguments.  P4 is a pointer to the 
  6296   6307   ** FuncDef structure that specifies the function.  Register P3 is the
  6297   6308   ** accumulator.
  6298   6309   **
  6299   6310   ** The P5 arguments are taken from register P2 and its
  6300   6311   ** successors.
  6301   6312   */
  6302         -/* Opcode: AggStep P1 P2 P3 P4 P5
         6313  +/* Opcode: AggStep1 P1 P2 P3 P4 P5
  6303   6314   ** Synopsis: accum=r[P3] step(r[P2@P5])
  6304   6315   **
  6305   6316   ** Execute the xStep (if P1==0) or xInverse (if P1!=0) function for an
  6306   6317   ** aggregate.  The function has P5 arguments.  P4 is a pointer to the 
  6307   6318   ** FuncDef structure that specifies the function.  Register P3 is the
  6308   6319   ** accumulator.
  6309   6320   **
................................................................................
  6312   6323   **
  6313   6324   ** This opcode is initially coded as OP_AggStep0.  On first evaluation,
  6314   6325   ** the FuncDef stored in P4 is converted into an sqlite3_context and
  6315   6326   ** the opcode is changed.  In this way, the initialization of the
  6316   6327   ** sqlite3_context only happens once, instead of on each call to the
  6317   6328   ** step function.
  6318   6329   */
  6319         -case OP_AggStep0: {
         6330  +case OP_AggInverse:
         6331  +case OP_AggStep: {
  6320   6332     int n;
  6321   6333     sqlite3_context *pCtx;
  6322   6334   
  6323   6335     assert( pOp->p4type==P4_FUNCDEF );
  6324   6336     n = pOp->p5;
  6325   6337     assert( pOp->p3>0 && pOp->p3<=(p->nMem+1 - p->nCursor) );
  6326   6338     assert( n==0 || (pOp->p2>0 && pOp->p2+n<=(p->nMem+1 - p->nCursor)+1) );
................................................................................
  6335   6347     pCtx->iOp = (int)(pOp - aOp);
  6336   6348     pCtx->pVdbe = p;
  6337   6349     pCtx->skipFlag = 0;
  6338   6350     pCtx->isError = 0;
  6339   6351     pCtx->argc = n;
  6340   6352     pOp->p4type = P4_FUNCCTX;
  6341   6353     pOp->p4.pCtx = pCtx;
  6342         -  pOp->opcode = OP_AggStep;
         6354  +  assert( pOp->p1==(pOp->opcode==OP_AggInverse) );
         6355  +  pOp->opcode = OP_AggStep1;
  6343   6356     /* Fall through into OP_AggStep */
  6344   6357   }
  6345         -case OP_AggStep: {
         6358  +case OP_AggStep1: {
  6346   6359     int i;
  6347   6360     sqlite3_context *pCtx;
  6348   6361     Mem *pMem;
  6349   6362   
  6350   6363     assert( pOp->p4type==P4_FUNCCTX );
  6351   6364     pCtx = pOp->p4.pCtx;
  6352   6365     pMem = &aMem[pOp->p3];
................................................................................
  6395   6408       if( rc ) goto abort_due_to_error;
  6396   6409     }
  6397   6410     assert( pCtx->pOut->flags==MEM_Null );
  6398   6411     assert( pCtx->skipFlag==0 );
  6399   6412     break;
  6400   6413   }
  6401   6414   
  6402         -/* Opcode: AggFinal P1 P2 P3 P4 *
         6415  +/* Opcode: AggFinal P1 P2 * P4 *
  6403   6416   ** Synopsis: accum=r[P1] N=P2
  6404   6417   **
  6405   6418   ** P1 is the memory location that is the accumulator for an aggregate
  6406         -** or window function. If P3 is zero, then execute the finalizer function 
  6407         -** for an aggregate and store the result in P1. Or, if P3 is non-zero,
  6408         -** invoke the xValue() function and store the result in register P3.
         6419  +** or window function.  Execute the finalizer function 
         6420  +** for an aggregate and store the result in P1.
         6421  +**
         6422  +** P2 is the number of arguments that the step function takes and
         6423  +** P4 is a pointer to the FuncDef for this function.  The P2
         6424  +** argument is not used by this opcode.  It is only there to disambiguate
         6425  +** functions that can take varying numbers of arguments.  The
         6426  +** P4 argument is only needed for the case where
         6427  +** the step function was not previously called.
         6428  +*/
         6429  +/* Opcode: AggValue * P2 P3 P4 *
         6430  +** Synopsis: r[P3]=value N=P2
         6431  +**
         6432  +** Invoke the xValue() function and store the result in register P3.
  6409   6433   **
  6410   6434   ** P2 is the number of arguments that the step function takes and
  6411   6435   ** P4 is a pointer to the FuncDef for this function.  The P2
  6412   6436   ** argument is not used by this opcode.  It is only there to disambiguate
  6413   6437   ** functions that can take varying numbers of arguments.  The
  6414   6438   ** P4 argument is only needed for the case where
  6415   6439   ** the step function was not previously called.
  6416   6440   */
         6441  +case OP_AggValue:
  6417   6442   case OP_AggFinal: {
  6418   6443     Mem *pMem;
  6419   6444     assert( pOp->p1>0 && pOp->p1<=(p->nMem+1 - p->nCursor) );
         6445  +  assert( pOp->p3==0 || pOp->opcode==OP_AggValue );
  6420   6446     pMem = &aMem[pOp->p1];
  6421   6447     assert( (pMem->flags & ~(MEM_Null|MEM_Agg))==0 );
  6422   6448   #ifndef SQLITE_OMIT_WINDOWFUNC
  6423   6449     if( pOp->p3 ){
  6424   6450       rc = sqlite3VdbeMemAggValue(pMem, &aMem[pOp->p3], pOp->p4.pFunc);
  6425   6451       pMem = &aMem[pOp->p3];
  6426   6452     }else
  6427   6453   #endif
  6428         -  rc = sqlite3VdbeMemFinalize(pMem, pOp->p4.pFunc);
         6454  +  {
         6455  +    rc = sqlite3VdbeMemFinalize(pMem, pOp->p4.pFunc);
         6456  +  }
  6429   6457     
  6430   6458     if( rc ){
  6431   6459       sqlite3VdbeError(p, "%s", sqlite3_value_text(pMem));
  6432   6460       goto abort_due_to_error;
  6433   6461     }
  6434   6462     sqlite3VdbeChangeEncoding(pMem, encoding);
  6435   6463     UPDATE_MAX_BLOBSIZE(pMem);

Changes to src/vdbemem.c.

   431    431     assert( pFunc->xValue!=0 );
   432    432     assert( (pAccum->flags & MEM_Null)!=0 || pFunc==pAccum->u.pDef );
   433    433     assert( pAccum->db==0 || sqlite3_mutex_held(pAccum->db->mutex) );
   434    434     memset(&ctx, 0, sizeof(ctx));
   435    435     memset(&t, 0, sizeof(t));
   436    436     t.flags = MEM_Null;
   437    437     t.db = pAccum->db;
          438  +  sqlite3VdbeMemSetNull(pOut);
   438    439     ctx.pOut = pOut;
   439    440     ctx.pMem = pAccum;
   440    441     ctx.pFunc = pFunc;
   441    442     pFunc->xValue(&ctx);
   442    443     return ctx.isError;
   443    444   }
   444    445   #endif /* SQLITE_OMIT_WINDOWFUNC */

Changes to src/window.c.

   984    984   ** that do not use the standard function API, generate the required
   985    985   ** inline VM code.
   986    986   **
   987    987   ** If argument csr is greater than or equal to 0, then argument reg is
   988    988   ** the first register in an array of registers guaranteed to be large
   989    989   ** enough to hold the array of arguments for each function. In this case
   990    990   ** the arguments are extracted from the current row of csr into the
   991         -** array of registers before invoking OP_AggStep.
          991  +** array of registers before invoking OP_AggStep or OP_AggInverse
   992    992   **
   993    993   ** Or, if csr is less than zero, then the array of registers at reg is
   994    994   ** already populated with all columns from the current row of the sub-query.
   995    995   **
   996    996   ** If argument regPartSize is non-zero, then it is a register containing the
   997    997   ** number of rows in the current partition.
   998    998   */
................................................................................
  1072   1072           }
  1073   1073         }
  1074   1074         if( pWin->pFunc->funcFlags & SQLITE_FUNC_NEEDCOLL ){
  1075   1075           CollSeq *pColl;
  1076   1076           pColl = sqlite3ExprNNCollSeq(pParse, pWin->pOwner->x.pList->a[0].pExpr);
  1077   1077           sqlite3VdbeAddOp4(v, OP_CollSeq, 0,0,0, (const char*)pColl, P4_COLLSEQ);
  1078   1078         }
  1079         -      sqlite3VdbeAddOp3(v, OP_AggStep0, bInverse, regArg, pWin->regAccum);
         1079  +      sqlite3VdbeAddOp3(v, bInverse? OP_AggInverse : OP_AggStep, 
         1080  +                        bInverse, regArg, pWin->regAccum);
  1080   1081         sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
  1081   1082         sqlite3VdbeChangeP5(v, (u8)nArg);
  1082   1083         if( addrIf ) sqlite3VdbeJumpHere(v, addrIf);
  1083   1084       }
  1084   1085     }
  1085   1086   }
  1086   1087   
................................................................................
  1104   1105         sqlite3VdbeAddOp3(v, OP_Column, pWin->csrApp, 0, pWin->regResult);
  1105   1106         sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2);
  1106   1107         if( bFinal ){
  1107   1108           sqlite3VdbeAddOp1(v, OP_ResetSorter, pWin->csrApp);
  1108   1109         }
  1109   1110       }else if( pWin->regApp ){
  1110   1111       }else{
  1111         -      if( bFinal==0 ){
  1112         -        sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regResult);
  1113         -      }
  1114         -      sqlite3VdbeAddOp2(v, OP_AggFinal, pWin->regAccum, windowArgCount(pWin));
  1115         -      sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
  1116   1112         if( bFinal ){
         1113  +        sqlite3VdbeAddOp2(v, OP_AggFinal, pWin->regAccum, windowArgCount(pWin));
         1114  +        sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
  1117   1115           sqlite3VdbeAddOp2(v, OP_Copy, pWin->regAccum, pWin->regResult);
  1118   1116           sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regAccum);
  1119   1117         }else{
  1120         -        sqlite3VdbeChangeP3(v, -1, pWin->regResult);
         1118  +        sqlite3VdbeAddOp3(v, OP_AggValue, pWin->regAccum, windowArgCount(pWin),
         1119  +                             pWin->regResult);
         1120  +        sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
  1121   1121         }
  1122   1122       }
  1123   1123     }
  1124   1124   }
  1125   1125   
  1126   1126   /*
  1127   1127   ** This function generates VM code to invoke the sub-routine at address
................................................................................
  1269   1269   ** from the Window.iEphCsr temp table. This routine generates VM code
  1270   1270   ** similar to:
  1271   1271   **
  1272   1272   **   while( regCtr>0 ){
  1273   1273   **     regCtr--;
  1274   1274   **     windowReturnOneRow()
  1275   1275   **     if( bInverse ){
  1276         -**       AggStep (xInverse)
         1276  +**       AggInverse
  1277   1277   **     }
  1278   1278   **     Next (Window.iEphCsr)
  1279   1279   **   }
  1280   1280   */
  1281   1281   static void windowReturnRows(
  1282   1282     Parse *pParse,
  1283   1283     Window *pMWin,                  /* List of window functions */
................................................................................
  1363   1363   **       Next(csrEnd)                    // if EOF skip Aggstep
  1364   1364   **       Aggstep (csrEnd)
  1365   1365   **       if( (regEnd--)<=0 ){
  1366   1366   **         AggFinal (xValue)
  1367   1367   **         Gosub addrGosub
  1368   1368   **         Next(csr)                // if EOF goto flush_partition_done
  1369   1369   **         if( (regStart--)<=0 ){
  1370         -**           AggStep (csrStart, xInverse)
         1370  +**           AggInverse (csrStart)
  1371   1371   **           Next(csrStart)
  1372   1372   **         }
  1373   1373   **       }
  1374   1374   **   flush_partition_done:
  1375   1375   **     ResetSorter (csr)
  1376   1376   **     Return
  1377   1377   **
................................................................................
  1391   1391   **       Aggstep (csrEnd)
  1392   1392   **     }
  1393   1393   **     while( 1 ){
  1394   1394   **       AggFinal (xValue)
  1395   1395   **       Gosub addrGosub
  1396   1396   **       Next(csr)                     // if EOF goto flush_partition_done
  1397   1397   **       if( (regStart--)<=0 ){
  1398         -**         AggStep (csrStart, xInverse)
         1398  +**         AggInverse (csrStart)
  1399   1399   **         Next(csrStart)
  1400   1400   **       }
  1401   1401   **     }
  1402   1402   **
  1403   1403   **   For the "CURRENT ROW AND UNBOUNDED FOLLOWING" case, the final if() 
  1404   1404   **   condition is always true (as if regStart were initialized to 0).
  1405   1405   **
................................................................................
  1414   1414   **       while( 1 ){
  1415   1415   **         regPeer++
  1416   1416   **         Gosub addrGosub
  1417   1417   **         Next(csr)                     // if EOF goto flush_partition_done
  1418   1418   **         if( new peer ) break;
  1419   1419   **       }
  1420   1420   **       while( (regPeer--)>0 ){
  1421         -**         AggStep (csrStart, xInverse)
         1421  +**         AggInverse (csrStart)
  1422   1422   **         Next(csrStart)
  1423   1423   **       }
  1424   1424   **     }
  1425   1425   **
  1426   1426   ** ROWS BETWEEN <expr> FOLLOWING    AND <expr> FOLLOWING
  1427   1427   **
  1428   1428   **   regEnd = regEnd - regStart
................................................................................
  1431   1431   **     Next(csrEnd)                 // if EOF fall-through
  1432   1432   **     if( (regEnd--)<=0 ){
  1433   1433   **       if( (regStart--)<=0 ){
  1434   1434   **         AggFinal (xValue)
  1435   1435   **         Gosub addrGosub
  1436   1436   **         Next(csr)              // if EOF goto flush_partition_done
  1437   1437   **       }
  1438         -**       AggStep (csrStart, xInverse)
         1438  +**       AggInverse (csrStart)
  1439   1439   **       Next (csrStart)
  1440   1440   **     }
  1441   1441   **
  1442   1442   ** ROWS BETWEEN <expr> PRECEDING    AND <expr> PRECEDING
  1443   1443   **
  1444   1444   **   Replace the bit after "Rewind" in the above with:
  1445   1445   **
................................................................................
  1447   1447   **       AggStep (csrEnd)
  1448   1448   **       Next (csrEnd)
  1449   1449   **     }
  1450   1450   **     AggFinal (xValue)
  1451   1451   **     Gosub addrGosub
  1452   1452   **     Next(csr)                  // if EOF goto flush_partition_done
  1453   1453   **     if( (regStart--)<=0 ){
  1454         -**       AggStep (csr2, xInverse)
         1454  +**       AggInverse (csr2)
  1455   1455   **       Next (csr2)
  1456   1456   **     }
  1457   1457   **
  1458   1458   */
  1459   1459   static void windowCodeRowExprStep(
  1460   1460     Parse *pParse, 
  1461   1461     Select *p,
................................................................................
  1678   1678   **
  1679   1679   ** RANGE BETWEEN CURRENT ROW AND CURRENT ROW 
  1680   1680   **
  1681   1681   **   As above, except that each of the for() loops becomes:
  1682   1682   **
  1683   1683   **         for(i=0; i<ctr; i++){
  1684   1684   **           Gosub addrGosub
  1685         -**           AggStep (xInverse, iEphCsr)
         1685  +**           AggInverse (iEphCsr)
  1686   1686   **           Next iEphCsr
  1687   1687   **         }
  1688   1688   **
  1689   1689   ** RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  1690   1690   **
  1691   1691   **   flush_partition:
  1692   1692   **     Once {
................................................................................
  1711   1711   **     Rewind (csrLead)
  1712   1712   **     Integer ctr 0
  1713   1713   **     foreach row (csrLead){
  1714   1714   **       if( new peer ){
  1715   1715   **         AggFinal (xValue)
  1716   1716   **         for(i=0; i<ctr; i++){
  1717   1717   **           Gosub addrGosub
  1718         -**           AggStep (xInverse, iEphCsr)
         1718  +**           AggInverse (iEphCsr)
  1719   1719   **           Next iEphCsr
  1720   1720   **         }
  1721   1721   **         Integer ctr 0
  1722   1722   **       }
  1723   1723   **       Incr ctr
  1724   1724   **     }
  1725   1725   **