SQLite

/*
** Context object passed through sqlite3WalkExprList() to
** selectWindowRewriteExprCb() by selectWindowRewriteEList().
*/
typedef struct WindowRewrite WindowRewrite;
struct WindowRewrite {
  Window *pWin;
  SrcList *pSrc;
  ExprList *pSub;
  Select *pSubSelect;             /* Current sub-select, if any */
};

/*
** Callback function used by selectWindowRewriteEList(). If necessary,
** this function appends to the output expression-list and updates 
** expression (*ppExpr) in place.
*/
static int selectWindowRewriteExprCb(Walker *pWalker, Expr *pExpr){
  struct WindowRewrite *p = pWalker->u.pRewrite;
  Parse *pParse = pWalker->pParse;

  /* If this function is being called from within a scalar sub-select
  ** that used by the SELECT statement being processed, only process
  ** TK_COLUMN expressions that refer to it (the outer SELECT). Do
  ** not process aggregates or window functions at all, as they belong
  ** to the scalar sub-select.  */
  if( p->pSubSelect ){
    if( pExpr->op!=TK_COLUMN ){
      return WRC_Continue;
    }else{
      int nSrc = p->pSrc->nSrc;
      int i;
      for(i=0; i<nSrc; i++){
        if( pExpr->iTable==p->pSrc->a[i].iCursor ) break;
      }
      if( i==nSrc ) return WRC_Continue;
    }
  }

  switch( pExpr->op ){

    case TK_FUNCTION:
      if( pExpr->pWin==0 ){
        break;
      }else{

    default: /* no-op */
      break;
  }

  return WRC_Continue;
}
static int selectWindowRewriteSelectCb(Walker *pWalker, Select *pSelect){
  struct WindowRewrite *p = pWalker->u.pRewrite;
  Select *pSave = p->pSubSelect;
  if( pSave==pSelect ){
    return WRC_Continue;
  }else{
    p->pSubSelect = pSelect;
    sqlite3WalkSelect(pWalker, pSelect);
    p->pSubSelect = pSave;
  }
  return WRC_Prune;
}


/*
** Iterate through each expression in expression-list pEList. For each:
**
**   * TK_COLUMN,
**   * aggregate function, or
**   * window function with a Window object that is not a member of the 
**     Window list passed as the second argument (pWin).
**
** Append the node to output expression-list (*ppSub). And replace it
** with a TK_COLUMN that reads the (N-1)th element of table 
** pWin->iEphCsr, where N is the number of elements in (*ppSub) after
** appending the new one.
*/
static void selectWindowRewriteEList(
  Parse *pParse, 
  Window *pWin,
  SrcList *pSrc,
  ExprList *pEList,               /* Rewrite expressions in this list */
  ExprList **ppSub                /* IN/OUT: Sub-select expression-list */
){
  Walker sWalker;
  WindowRewrite sRewrite;

  memset(&sWalker, 0, sizeof(Walker));
  memset(&sRewrite, 0, sizeof(WindowRewrite));

  sRewrite.pSub = *ppSub;
  sRewrite.pWin = pWin;
  sRewrite.pSrc = pSrc;

  sWalker.pParse = pParse;
  sWalker.xExprCallback = selectWindowRewriteExprCb;
  sWalker.xSelectCallback = selectWindowRewriteSelectCb;
  sWalker.u.pRewrite = &sRewrite;

  (void)sqlite3WalkExprList(&sWalker, pEList);

    }

    /* Assign a cursor number for the ephemeral table used to buffer rows.
    ** The OpenEphemeral instruction is coded later, after it is known how
    ** many columns the table will have.  */
    pMWin->iEphCsr = pParse->nTab++;

    selectWindowRewriteEList(pParse, pMWin, pSrc, p->pEList, &pSublist);
    selectWindowRewriteEList(pParse, pMWin, pSrc, p->pOrderBy, &pSublist);
    pMWin->nBufferCol = (pSublist ? pSublist->nExpr : 0);

    /* Append the PARTITION BY and ORDER BY expressions to the to the 
    ** sub-select expression list. They are required to figure out where 
    ** boundaries for partitions and sets of peer rows lie.  */
    pSublist = exprListAppendList(pParse, pSublist, pMWin->pPartition);
    pSublist = exprListAppendList(pParse, pSublist, pMWin->pOrderBy);

} {
  do_execsql_test 10.2.$tn "
    WITH t1(a,b) AS ( VALUES(1, 2), (2, 3), (3, 4) )
    $stmt
  " $res
}


#-------------------------------------------------------------------------
#
reset_db
do_execsql_test 11.0 {
  CREATE TABLE t1(a INT);
  INSERT INTO t1 VALUES(10),(15),(20),(20),(25),(30),(30),(50);
  CREATE TABLE t3(x INT, y VARCHAR);
  INSERT INTO t3(x,y) VALUES(10,'ten'),('15','fifteen'),(30,'thirty');
}

do_execsql_test 11.1 {
  SELECT a, (SELECT y FROM t3 WHERE x=a) FROM t1 ORDER BY a;
} {
  10 ten 15 fifteen 20 {} 20 {} 25 {} 30 thirty 30 thirty 50 {}
}

do_execsql_test 11.2 {
  SELECT a, (SELECT y FROM t3 WHERE x=a), sum(a) OVER (ORDER BY a)
    FROM t1 ORDER BY a;
} {
  10 ten 10   15 fifteen 25   20 {} 65        20 {} 65   
  25 {} 90    30 thirty 150   30 thirty 150   50 {} 200
}

finish_test