/ Check-in [a2c0e1be]
Login

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

Overview
Comment:Enhance the TreeView mechanism so that it shows the window function data structures as part of the abstract syntax tree.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: a2c0e1bec0d8a6f982572c4c5a8166319b8db0fe586057f7900f0ab72af6554e
User & Date: drh 2018-07-10 06:32:53
Context
2018-07-10
06:47
Enhance the sqlite3ExprCompare() routine so that it knows to compare the OVER clause of window functions. check-in: 0a7649af user: drh tags: trunk
06:32
Enhance the TreeView mechanism so that it shows the window function data structures as part of the abstract syntax tree. check-in: a2c0e1be user: drh tags: trunk
05:11
New VDBE comments and coverage macros. check-in: a9a30726 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/sqliteInt.h.

  3486   3486   struct TreeView {
  3487   3487     int iLevel;             /* Which level of the tree we are on */
  3488   3488     u8  bLine[100];         /* Draw vertical in column i if bLine[i] is true */
  3489   3489   };
  3490   3490   #endif /* SQLITE_DEBUG */
  3491   3491   
  3492   3492   /*
  3493         -** Object used to encode the OVER() clause attached to a window-function
  3494         -** invocation. And some fields used while generating VM code for the same.
         3493  +** This object is used in varioius ways, all related to window functions
         3494  +**
         3495  +**   (1) A single instance of this structure is attached to the
         3496  +**       the Expr.pWin field for each window function in an expression tree.
         3497  +**       This object holds the information contained in the OVER clause,
         3498  +**       plus additional fields used during code generation.
         3499  +**
         3500  +**   (2) All window functions in a single SELECT form a linked-list
         3501  +**       attached to Select.pWin.  The Window.pFunc and Window.pExpr
         3502  +**       fields point back to the expression that is the window function.
         3503  +**
         3504  +**   (3) The terms of the WINDOW clause of a SELECT are instances of this
         3505  +**       object on a linked list attached to Select.pWinDefn.
         3506  +**
         3507  +** The uses (1) and (2) are really the same Window object that just happens
         3508  +** to be accessible in two different ways.  Use (3) is are separate objects.
  3495   3509   */
  3496   3510   struct Window {
  3497   3511     char *zName;            /* Name of window (may be NULL) */
  3498   3512     ExprList *pPartition;   /* PARTITION BY clause */
  3499   3513     ExprList *pOrderBy;     /* ORDER BY clause */
  3500   3514     u8 eType;               /* TK_RANGE or TK_ROWS */
  3501   3515     u8 eStart;              /* UNBOUNDED, CURRENT, PRECEDING or FOLLOWING */
  3502   3516     u8 eEnd;                /* UNBOUNDED, CURRENT, PRECEDING or FOLLOWING */
  3503   3517     Expr *pStart;           /* Expression for "<expr> PRECEDING" */
  3504   3518     Expr *pEnd;             /* Expression for "<expr> FOLLOWING" */
  3505         -
  3506   3519     Window *pNextWin;       /* Next window function belonging to this SELECT */
  3507         -
  3508         -  Expr *pFilter;
  3509         -  FuncDef *pFunc;
  3510         -
         3520  +  Expr *pFilter;          /* The FILTER expression */
         3521  +  FuncDef *pFunc;         /* The function */
  3511   3522     int iEphCsr;            /* Temp table used by this window */
  3512   3523     int regAccum;
  3513   3524     int regResult;
  3514         -
  3515   3525     int csrApp;             /* Function cursor (used by min/max) */
  3516   3526     int regApp;             /* Function register (also used by min/max) */
  3517         -
  3518         -  int regPart;
         3527  +  int regPart;            /* First in a set of registers holding PARTITION BY
         3528  +                          ** and ORDER BY values for the window */
  3519   3529     Expr *pOwner;           /* Expression object this window is attached to */
  3520   3530     int nBufferCol;         /* Number of columns in buffer table */
  3521   3531     int iArgCol;            /* Offset of first argument for this function */
  3522   3532   };
  3523   3533   
  3524   3534   #ifndef SQLITE_OMIT_WINDOWFUNC
  3525   3535   void sqlite3WindowDelete(sqlite3*, Window*);
................................................................................
  3751   3761   
  3752   3762   #if defined(SQLITE_DEBUG)
  3753   3763     void sqlite3TreeViewExpr(TreeView*, const Expr*, u8);
  3754   3764     void sqlite3TreeViewBareExprList(TreeView*, const ExprList*, const char*);
  3755   3765     void sqlite3TreeViewExprList(TreeView*, const ExprList*, u8, const char*);
  3756   3766     void sqlite3TreeViewSelect(TreeView*, const Select*, u8);
  3757   3767     void sqlite3TreeViewWith(TreeView*, const With*, u8);
         3768  +#ifndef SQLITE_OMIT_WINDOWFUNC
         3769  +  void sqlite3TreeViewWindow(TreeView*, const Window*, u8);
         3770  +  void sqlite3TreeViewWinFunc(TreeView*, const Window*, u8);
         3771  +#endif
  3758   3772   #endif
  3759   3773   
  3760   3774   
  3761   3775   void sqlite3SetString(char **, sqlite3*, const char*);
  3762   3776   void sqlite3ErrorMsg(Parse*, const char*, ...);
  3763   3777   void sqlite3Dequote(char*);
  3764   3778   void sqlite3TokenInit(Token*,char*);

Changes to src/treeview.c.

   161    161         n = 0;
   162    162         if( p->pSrc && p->pSrc->nSrc ) n++;
   163    163         if( p->pWhere ) n++;
   164    164         if( p->pGroupBy ) n++;
   165    165         if( p->pHaving ) n++;
   166    166         if( p->pOrderBy ) n++;
   167    167         if( p->pLimit ) n++;
          168  +#ifndef SQLITE_OMIT_WINDOWFUNC
          169  +      if( p->pWin ) n++;
          170  +      if( p->pWinDefn ) n++;
          171  +#endif
   168    172       }
   169    173       sqlite3TreeViewExprList(pView, p->pEList, (n--)>0, "result-set");
          174  +#ifndef SQLITE_OMIT_WINDOWFUNC
          175  +    if( p->pWin ){
          176  +      Window *pX;
          177  +      pView = sqlite3TreeViewPush(pView, (n--)>0);
          178  +      sqlite3TreeViewLine(pView, "window-functions");
          179  +      for(pX=p->pWin; pX; pX=pX->pNextWin){
          180  +        sqlite3TreeViewWinFunc(pView, pX, pX->pNextWin!=0);
          181  +      }
          182  +      sqlite3TreeViewPop(pView);
          183  +    }
          184  +#endif
   170    185       if( p->pSrc && p->pSrc->nSrc ){
   171    186         int i;
   172    187         pView = sqlite3TreeViewPush(pView, (n--)>0);
   173    188         sqlite3TreeViewLine(pView, "FROM");
   174    189         for(i=0; i<p->pSrc->nSrc; i++){
   175    190           struct SrcList_item *pItem = &p->pSrc->a[i];
   176    191           StrAccum x;
................................................................................
   212    227         sqlite3TreeViewExprList(pView, p->pGroupBy, (n--)>0, "GROUPBY");
   213    228       }
   214    229       if( p->pHaving ){
   215    230         sqlite3TreeViewItem(pView, "HAVING", (n--)>0);
   216    231         sqlite3TreeViewExpr(pView, p->pHaving, 0);
   217    232         sqlite3TreeViewPop(pView);
   218    233       }
          234  +#ifndef SQLITE_OMIT_WINDOWFUNC
          235  +    if( p->pWinDefn ){
          236  +      Window *pX;
          237  +      sqlite3TreeViewItem(pView, "WINDOW", (n--)>0);
          238  +      for(pX=p->pWinDefn; pX; pX=pX->pNextWin){
          239  +        sqlite3TreeViewWindow(pView, pX, pX->pNextWin!=0);
          240  +      }
          241  +      sqlite3TreeViewPop(pView);
          242  +    }
          243  +#endif
   219    244       if( p->pOrderBy ){
   220    245         sqlite3TreeViewExprList(pView, p->pOrderBy, (n--)>0, "ORDERBY");
   221    246       }
   222    247       if( p->pLimit ){
   223    248         sqlite3TreeViewItem(pView, "LIMIT", (n--)>0);
   224    249         sqlite3TreeViewExpr(pView, p->pLimit->pLeft, p->pLimit->pRight!=0);
   225    250         if( p->pLimit->pRight ){
................................................................................
   239    264         sqlite3TreeViewItem(pView, zOp, 1);
   240    265       }
   241    266       p = p->pPrior;
   242    267     }while( p!=0 );
   243    268     sqlite3TreeViewPop(pView);
   244    269   }
   245    270   
          271  +#ifndef SQLITE_OMIT_WINDOWFUNC
          272  +/*
          273  +** Generate a description of starting or stopping bounds
          274  +*/
          275  +void sqlite3TreeViewBound(
          276  +  TreeView *pView,        /* View context */
          277  +  u8 eBound,              /* UNBOUNDED, CURRENT, PRECEDING, FOLLOWING */
          278  +  Expr *pExpr,            /* Value for PRECEDING or FOLLOWING */
          279  +  u8 moreToFollow         /* True if more to follow */
          280  +){
          281  +  switch( eBound ){
          282  +    case TK_UNBOUNDED: {
          283  +      sqlite3TreeViewItem(pView, "UNBOUNDED", moreToFollow);
          284  +      sqlite3TreeViewPop(pView);
          285  +      break;
          286  +    }
          287  +    case TK_CURRENT: {
          288  +      sqlite3TreeViewItem(pView, "CURRENT", moreToFollow);
          289  +      sqlite3TreeViewPop(pView);
          290  +      break;
          291  +    }
          292  +    case TK_PRECEDING: {
          293  +      sqlite3TreeViewItem(pView, "PRECEDING", moreToFollow);
          294  +      sqlite3TreeViewExpr(pView, pExpr, 0);
          295  +      sqlite3TreeViewPop(pView);
          296  +      break;
          297  +    }
          298  +    case TK_FOLLOWING: {
          299  +      sqlite3TreeViewItem(pView, "FOLLOWING", moreToFollow);
          300  +      sqlite3TreeViewExpr(pView, pExpr, 0);
          301  +      sqlite3TreeViewPop(pView);
          302  +      break;
          303  +    }
          304  +  }
          305  +}
          306  +#endif /* SQLITE_OMIT_WINDOWFUNC */
          307  +
          308  +#ifndef SQLITE_OMIT_WINDOWFUNC
          309  +/*
          310  +** Generate a human-readable explanation for a Window object
          311  +*/
          312  +void sqlite3TreeViewWindow(TreeView *pView, const Window *pWin, u8 more){
          313  +  pView = sqlite3TreeViewPush(pView, more);
          314  +  if( pWin->zName ){
          315  +    sqlite3TreeViewLine(pView, "OVER %s", pWin->zName);
          316  +  }else{
          317  +    sqlite3TreeViewLine(pView, "OVER");
          318  +  }
          319  +  if( pWin->pPartition ){
          320  +    sqlite3TreeViewExprList(pView, pWin->pPartition, 1, "PARTITION-BY");
          321  +  }
          322  +  if( pWin->pOrderBy ){
          323  +    sqlite3TreeViewExprList(pView, pWin->pOrderBy, 1, "ORDER-BY");
          324  +  }
          325  +  if( pWin->eType ){
          326  +    sqlite3TreeViewItem(pView, pWin->eType==TK_RANGE ? "RANGE" : "ROWS", 0);
          327  +    sqlite3TreeViewBound(pView, pWin->eStart, pWin->pStart, 1);
          328  +    sqlite3TreeViewBound(pView, pWin->eEnd, pWin->pEnd, 0);
          329  +    sqlite3TreeViewPop(pView);
          330  +  }
          331  +  sqlite3TreeViewPop(pView);
          332  +}
          333  +#endif /* SQLITE_OMIT_WINDOWFUNC */
          334  +
          335  +#ifndef SQLITE_OMIT_WINDOWFUNC
          336  +/*
          337  +** Generate a human-readable explanation for a Window Function object
          338  +*/
          339  +void sqlite3TreeViewWinFunc(TreeView *pView, const Window *pWin, u8 more){
          340  +  pView = sqlite3TreeViewPush(pView, more);
          341  +  sqlite3TreeViewLine(pView, "WINFUNC %s(%d)",
          342  +                       pWin->pFunc->zName, pWin->pFunc->nArg);
          343  +  sqlite3TreeViewWindow(pView, pWin, 0);
          344  +  sqlite3TreeViewPop(pView);
          345  +}
          346  +#endif /* SQLITE_OMIT_WINDOWFUNC */
          347  +
   246    348   /*
   247    349   ** Generate a human-readable explanation of an expression tree.
   248    350   */
   249    351   void sqlite3TreeViewExpr(TreeView *pView, const Expr *pExpr, u8 moreToFollow){
   250    352     const char *zBinOp = 0;   /* Binary operator */
   251    353     const char *zUniOp = 0;   /* Unary operator */
   252    354     char zFlgs[60];
................................................................................
   389    491         sqlite3TreeViewExpr(pView, pExpr->pLeft, 0);
   390    492         break;
   391    493       }
   392    494   
   393    495       case TK_AGG_FUNCTION:
   394    496       case TK_FUNCTION: {
   395    497         ExprList *pFarg;       /* List of function arguments */
          498  +      Window *pWin;
   396    499         if( ExprHasProperty(pExpr, EP_TokenOnly) ){
   397    500           pFarg = 0;
          501  +        pWin = 0;
   398    502         }else{
   399    503           pFarg = pExpr->x.pList;
          504  +#ifndef SQLITE_OMIT_WINDOWFUNC
          505  +        pWin = pExpr->pWin;
          506  +#else
          507  +        pWin = 0;
          508  +#endif 
   400    509         }
   401    510         if( pExpr->op==TK_AGG_FUNCTION ){
   402    511           sqlite3TreeViewLine(pView, "AGG_FUNCTION%d %Q",
   403    512                                pExpr->op2, pExpr->u.zToken);
   404    513         }else{
   405    514           sqlite3TreeViewLine(pView, "FUNCTION %Q", pExpr->u.zToken);
   406    515         }
   407    516         if( pFarg ){
   408         -        sqlite3TreeViewExprList(pView, pFarg, 0, 0);
          517  +        sqlite3TreeViewExprList(pView, pFarg, pWin!=0, 0);
          518  +      }
          519  +#ifndef SQLITe_OMIT_WINDOWFUNC
          520  +      if( pWin ){
          521  +        sqlite3TreeViewWindow(pView, pWin, 0);
   409    522         }
          523  +#endif
   410    524         break;
   411    525       }
   412    526   #ifndef SQLITE_OMIT_SUBQUERY
   413    527       case TK_EXISTS: {
   414    528         sqlite3TreeViewLine(pView, "EXISTS-expr flags=0x%x", pExpr->flags);
   415    529         sqlite3TreeViewSelect(pView, pExpr->x.pSelect, 0);
   416    530         break;