/ Check-in [687fe532]
Login

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

Overview
Comment:Fix a problem with using scalar sub-selects in window function queries.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256:687fe532c274265ca77451f97829743fcb8a714d0f6b1ceb9a147ab9babdc5b5
User & Date: dan 2018-07-10 17:26:12
Context
2018-07-10
18:50
Modify tests in window3.test to be tolerant of rounding errors when comparing floating point values. check-in: 7c26c443 user: dan tags: trunk
17:26
Fix a problem with using scalar sub-selects in window function queries. check-in: 687fe532 user: dan tags: trunk
17:10
Further improvements to bytecode branch testing. Fix cases where the macros said a branch could not be taken when in fact it could be. Alter some window function branch coverage macros to indicate that comparison operands cannot be NULL. check-in: 76e42b70 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/window.c.

   585    585   /*
   586    586   ** Context object passed through sqlite3WalkExprList() to
   587    587   ** selectWindowRewriteExprCb() by selectWindowRewriteEList().
   588    588   */
   589    589   typedef struct WindowRewrite WindowRewrite;
   590    590   struct WindowRewrite {
   591    591     Window *pWin;
          592  +  SrcList *pSrc;
   592    593     ExprList *pSub;
          594  +  Select *pSubSelect;             /* Current sub-select, if any */
   593    595   };
   594    596   
   595    597   /*
   596    598   ** Callback function used by selectWindowRewriteEList(). If necessary,
   597    599   ** this function appends to the output expression-list and updates 
   598    600   ** expression (*ppExpr) in place.
   599    601   */
   600    602   static int selectWindowRewriteExprCb(Walker *pWalker, Expr *pExpr){
   601    603     struct WindowRewrite *p = pWalker->u.pRewrite;
   602    604     Parse *pParse = pWalker->pParse;
          605  +
          606  +  /* If this function is being called from within a scalar sub-select
          607  +  ** that used by the SELECT statement being processed, only process
          608  +  ** TK_COLUMN expressions that refer to it (the outer SELECT). Do
          609  +  ** not process aggregates or window functions at all, as they belong
          610  +  ** to the scalar sub-select.  */
          611  +  if( p->pSubSelect ){
          612  +    if( pExpr->op!=TK_COLUMN ){
          613  +      return WRC_Continue;
          614  +    }else{
          615  +      int nSrc = p->pSrc->nSrc;
          616  +      int i;
          617  +      for(i=0; i<nSrc; i++){
          618  +        if( pExpr->iTable==p->pSrc->a[i].iCursor ) break;
          619  +      }
          620  +      if( i==nSrc ) return WRC_Continue;
          621  +    }
          622  +  }
   603    623   
   604    624     switch( pExpr->op ){
   605    625   
   606    626       case TK_FUNCTION:
   607    627         if( pExpr->pWin==0 ){
   608    628           break;
   609    629         }else{
................................................................................
   639    659       default: /* no-op */
   640    660         break;
   641    661     }
   642    662   
   643    663     return WRC_Continue;
   644    664   }
   645    665   static int selectWindowRewriteSelectCb(Walker *pWalker, Select *pSelect){
   646         -  UNUSED_PARAMETER(pWalker);
   647         -  UNUSED_PARAMETER(pSelect);
          666  +  struct WindowRewrite *p = pWalker->u.pRewrite;
          667  +  Select *pSave = p->pSubSelect;
          668  +  if( pSave==pSelect ){
          669  +    return WRC_Continue;
          670  +  }else{
          671  +    p->pSubSelect = pSelect;
          672  +    sqlite3WalkSelect(pWalker, pSelect);
          673  +    p->pSubSelect = pSave;
          674  +  }
   648    675     return WRC_Prune;
   649    676   }
   650    677   
   651    678   
   652    679   /*
   653    680   ** Iterate through each expression in expression-list pEList. For each:
   654    681   **
   655    682   **   * TK_COLUMN,
   656    683   **   * aggregate function, or
   657    684   **   * window function with a Window object that is not a member of the 
   658         -**     linked list passed as the second argument (pWin)
          685  +**     Window list passed as the second argument (pWin).
   659    686   **
   660    687   ** Append the node to output expression-list (*ppSub). And replace it
   661    688   ** with a TK_COLUMN that reads the (N-1)th element of table 
   662    689   ** pWin->iEphCsr, where N is the number of elements in (*ppSub) after
   663    690   ** appending the new one.
   664    691   */
   665    692   static void selectWindowRewriteEList(
   666    693     Parse *pParse, 
   667    694     Window *pWin,
          695  +  SrcList *pSrc,
   668    696     ExprList *pEList,               /* Rewrite expressions in this list */
   669    697     ExprList **ppSub                /* IN/OUT: Sub-select expression-list */
   670    698   ){
   671    699     Walker sWalker;
   672    700     WindowRewrite sRewrite;
   673    701   
   674    702     memset(&sWalker, 0, sizeof(Walker));
   675    703     memset(&sRewrite, 0, sizeof(WindowRewrite));
   676    704   
   677    705     sRewrite.pSub = *ppSub;
   678    706     sRewrite.pWin = pWin;
          707  +  sRewrite.pSrc = pSrc;
   679    708   
   680    709     sWalker.pParse = pParse;
   681    710     sWalker.xExprCallback = selectWindowRewriteExprCb;
   682    711     sWalker.xSelectCallback = selectWindowRewriteSelectCb;
   683    712     sWalker.u.pRewrite = &sRewrite;
   684    713   
   685    714     (void)sqlite3WalkExprList(&sWalker, pEList);
................................................................................
   749    778       }
   750    779   
   751    780       /* Assign a cursor number for the ephemeral table used to buffer rows.
   752    781       ** The OpenEphemeral instruction is coded later, after it is known how
   753    782       ** many columns the table will have.  */
   754    783       pMWin->iEphCsr = pParse->nTab++;
   755    784   
   756         -    selectWindowRewriteEList(pParse, pMWin, p->pEList, &pSublist);
   757         -    selectWindowRewriteEList(pParse, pMWin, p->pOrderBy, &pSublist);
          785  +    selectWindowRewriteEList(pParse, pMWin, pSrc, p->pEList, &pSublist);
          786  +    selectWindowRewriteEList(pParse, pMWin, pSrc, p->pOrderBy, &pSublist);
   758    787       pMWin->nBufferCol = (pSublist ? pSublist->nExpr : 0);
   759    788   
   760    789       /* Append the PARTITION BY and ORDER BY expressions to the to the 
   761    790       ** sub-select expression list. They are required to figure out where 
   762    791       ** boundaries for partitions and sets of peer rows lie.  */
   763    792       pSublist = exprListAppendList(pParse, pSublist, pMWin->pPartition);
   764    793       pSublist = exprListAppendList(pParse, pSublist, pMWin->pOrderBy);

Changes to test/window6.test.

   306    306   } {
   307    307     do_execsql_test 10.2.$tn "
   308    308       WITH t1(a,b) AS ( VALUES(1, 2), (2, 3), (3, 4) )
   309    309       $stmt
   310    310     " $res
   311    311   }
   312    312   
          313  +
          314  +#-------------------------------------------------------------------------
          315  +#
          316  +reset_db
          317  +do_execsql_test 11.0 {
          318  +  CREATE TABLE t1(a INT);
          319  +  INSERT INTO t1 VALUES(10),(15),(20),(20),(25),(30),(30),(50);
          320  +  CREATE TABLE t3(x INT, y VARCHAR);
          321  +  INSERT INTO t3(x,y) VALUES(10,'ten'),('15','fifteen'),(30,'thirty');
          322  +}
          323  +
          324  +do_execsql_test 11.1 {
          325  +  SELECT a, (SELECT y FROM t3 WHERE x=a) FROM t1 ORDER BY a;
          326  +} {
          327  +  10 ten 15 fifteen 20 {} 20 {} 25 {} 30 thirty 30 thirty 50 {}
          328  +}
          329  +
          330  +do_execsql_test 11.2 {
          331  +  SELECT a, (SELECT y FROM t3 WHERE x=a), sum(a) OVER (ORDER BY a)
          332  +    FROM t1 ORDER BY a;
          333  +} {
          334  +  10 ten 10   15 fifteen 25   20 {} 65        20 {} 65   
          335  +  25 {} 90    30 thirty 150   30 thirty 150   50 {} 200
          336  +}
          337  +
   313    338   finish_test
          339  +