SQLite

                               pExpr->x.pList->a[0].pExpr,0)==0
              ){
                pItem->bOBPayload = 0;
                pItem->bOBUnique = ExprHasProperty(pExpr, EP_Distinct);
              }else{
                pItem->bOBPayload = 1;
              }
              pItem->bUseSubtype =
                    (pItem->pFunc->funcFlags & SQLITE_SUBTYPE)!=0;
            }else{
              pItem->iOBTab = -1;
            }
            if( ExprHasProperty(pExpr, EP_Distinct) && !pItem->bOBUnique ){
              pItem->iDistinct = pParse->nTab++;
            }else{
              pItem->iDistinct = -1;

    if( pFunc->iOBTab>=0 ){
      ExprList *pOBList;
      KeyInfo *pKeyInfo;
      int nExtra = 0;
      assert( pFunc->pFExpr->pLeft!=0 );
      assert( pFunc->pFExpr->pLeft->op==TK_ORDER );
      assert( ExprUseXList(pFunc->pFExpr->pLeft) );
      assert( pFunc->pFunc!=0 );
      pOBList = pFunc->pFExpr->pLeft->x.pList;
      if( !pFunc->bOBUnique ){
        nExtra++;  /* One extra column for the OP_Sequence */
      }
      if( pFunc->bOBPayload ){
        /* extra columns for the function arguments */
        assert( ExprUseXList(pFunc->pFExpr) );
        nExtra += pFunc->pFExpr->x.pList->nExpr;
      }
      if( pFunc->bUseSubtype ){
        nExtra += pFunc->pFExpr->x.pList->nExpr;
      }
      pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pOBList, 0, nExtra);
      if( !pFunc->bOBUnique && pParse->nErr==0 ){
        pKeyInfo->nKeyField++;
      }
      sqlite3VdbeAddOp4(v, OP_OpenEphemeral,
            pFunc->iOBTab, pOBList->nExpr+nExtra, 0,
            (char*)pKeyInfo, P4_KEYINFO);

  int i;
  struct AggInfo_func *pF;
  for(i=0, pF=pAggInfo->aFunc; i<pAggInfo->nFunc; i++, pF++){
    ExprList *pList;
    assert( ExprUseXList(pF->pFExpr) );
    pList = pF->pFExpr->x.pList;
    if( pF->iOBTab>=0 ){
      /* For an ORDER BY aggregate, calls to OP_AggStep were deferred.  Inputs
      ** were stored in emphermal table pF->iOBTab.  Here, we extract those
      ** inputs (in ORDER BY order) and make all calls to OP_AggStep
      ** before doing the OP_AggFinal call. */
      int iTop;        /* Start of loop for extracting columns */
      int nArg;        /* Number of columns to extract */
      int nKey;        /* Key columns to be skipped */
      int regAgg;      /* Extract into this array */
      int j;           /* Loop counter */
     
      assert( pF->pFunc!=0 );
      nArg = pList->nExpr;
      regAgg = sqlite3GetTempRange(pParse, nArg);

      if( pF->bOBPayload==0 ){
        nKey = 0;
      }else{
        assert( pF->pFExpr->pLeft!=0 );
        assert( ExprUseXList(pF->pFExpr->pLeft) );
        assert( pF->pFExpr->pLeft->x.pList!=0 );
        nKey = pF->pFExpr->pLeft->x.pList->nExpr;
        if( ALWAYS(!pF->bOBUnique) ) nKey++;
      }
      iTop = sqlite3VdbeAddOp1(v, OP_Rewind, pF->iOBTab); VdbeCoverage(v);
      for(j=nArg-1; j>=0; j--){
        sqlite3VdbeAddOp3(v, OP_Column, pF->iOBTab, nKey+j, regAgg+j);
      }
      if( pF->bUseSubtype ){
        int regSubtype = sqlite3GetTempReg(pParse);
        int iBaseCol = nKey + nArg + (pF->bOBPayload==0 && pF->bOBUnique==0);
        for(j=nArg-1; j>=0; j--){
          sqlite3VdbeAddOp3(v, OP_Column, pF->iOBTab, iBaseCol+j, regSubtype);
          sqlite3VdbeAddOp2(v, OP_SetSubtype, regSubtype, regAgg+j);
        }
        sqlite3ReleaseTempReg(pParse, regSubtype);
      }
      sqlite3VdbeAddOp3(v, OP_AggStep, 0, regAgg, AggInfoFuncReg(pAggInfo,i));
      sqlite3VdbeAppendP4(v, pF->pFunc, P4_FUNCDEF);
      sqlite3VdbeChangeP5(v, (u8)nArg);
      sqlite3VdbeAddOp2(v, OP_Next, pF->iOBTab, iTop+1); VdbeCoverage(v);
      sqlite3VdbeJumpHere(v, iTop);
      sqlite3ReleaseTempRange(pParse, regAgg, nArg);
    }

    int addrNext = 0;
    int regAgg;
    int regAggSz = 0;
    int regDistinct = 0;
    ExprList *pList;
    assert( ExprUseXList(pF->pFExpr) );
    assert( !IsWindowFunc(pF->pFExpr) );
    assert( pF->pFunc!=0 );
    pList = pF->pFExpr->x.pList;
    if( ExprHasProperty(pF->pFExpr, EP_WinFunc) ){
      Expr *pFilter = pF->pFExpr->y.pWin->pFilter;
      if( pAggInfo->nAccumulator
       && (pF->pFunc->funcFlags & SQLITE_FUNC_NEEDCOLL)
       && regAcc
      ){

      assert( pOBList->nExpr>0 );
      regAggSz = pOBList->nExpr;
      if( !pF->bOBUnique ){
        regAggSz++;   /* One register for OP_Sequence */
      }
      if( pF->bOBPayload ){
        regAggSz += nArg;
      }
      if( pF->bUseSubtype ){
        regAggSz += nArg;
      }
      regAggSz++;  /* One extra register to hold result of MakeRecord */
      regAgg = sqlite3GetTempRange(pParse, regAggSz);
      regDistinct = regAgg;
      sqlite3ExprCodeExprList(pParse, pOBList, regAgg, 0, SQLITE_ECEL_DUP);
      jj = pOBList->nExpr;
      if( !pF->bOBUnique ){
        sqlite3VdbeAddOp2(v, OP_Sequence, pF->iOBTab, regAgg+jj);
        jj++;
      }
      if( pF->bOBPayload ){
        regDistinct = regAgg+jj;
        sqlite3ExprCodeExprList(pParse, pList, regDistinct, 0, SQLITE_ECEL_DUP);
        jj += nArg;
      }
      if( pF->bUseSubtype ){
        int kk;
        int regBase = pF->bOBPayload ? regDistinct : regAgg;
        for(kk=0; kk<nArg; kk++, jj++){
          sqlite3VdbeAddOp2(v, OP_GetSubtype, regBase+kk, regAgg+jj);
        }
      }
    }else if( pList ){
      nArg = pList->nExpr;
      regAgg = sqlite3GetTempRange(pParse, nArg);
      regDistinct = regAgg;
      sqlite3ExprCodeExprList(pParse, pList, regAgg, 0, SQLITE_ECEL_DUP);
    }else{

    Expr *pFExpr;            /* Expression encoding the function */
    FuncDef *pFunc;          /* The aggregate function implementation */
    int iDistinct;           /* Ephemeral table used to enforce DISTINCT */
    int iDistAddr;           /* Address of OP_OpenEphemeral */
    int iOBTab;              /* Ephemeral table to implement ORDER BY */
    u8 bOBPayload;           /* iOBTab has payload columns separate from key */
    u8 bOBUnique;            /* Enforce uniqueness on iOBTab keys */
    u8 bUseSubtype;          /* Transfer subtype info through sorter */
  } *aFunc;
  int nFunc;              /* Number of entries in aFunc[] */
  u32 selId;              /* Select to which this AggInfo belongs */
#ifdef SQLITE_DEBUG
  Select *pSelect;        /* SELECT statement that this AggInfo supports */
#endif
};

** Clear the subtype from register P1.
*/
case OP_ClrSubtype: {   /* in1 */
  pIn1 = &aMem[pOp->p1];
  pIn1->flags &= ~MEM_Subtype;
  break;
}

/* Opcode: GetSubtype P1 P2 * * *
** Synopsis:  r[P2] = r[P1].subtype
**
** Extract the subtype value from register P1 and write that subtype
** into register P2.  If P1 has no subtype, then P1 gets a NULL.
*/
case OP_GetSubtype: {   /* in1 out2 */
  pIn1 = &aMem[pOp->p1];
  pOut = &aMem[pOp->p2];
  if( pIn1->flags & MEM_Subtype ){
    sqlite3VdbeMemSetInt64(pOut, pIn1->eSubtype);
  }else{
    sqlite3VdbeMemSetNull(pOut);
  }
  break;
}

/* Opcode: SetSubtype P1 P2 * * *
** Synopsis:  r[P2].subtype = r[P1]
**
** Set the subtype value of register P2 to the integer from register P1.
** If P1 is NULL, clear the subtype from p2.
*/
case OP_SetSubtype: {   /* in1 out2 */
  pIn1 = &aMem[pOp->p1];
  pOut = &aMem[pOp->p2];
  if( pIn1->flags & MEM_Null ){
    pOut->flags &= ~MEM_Subtype;
  }else{
    assert( pIn1->flags & MEM_Int );
    pOut->flags |= MEM_Subtype;
    pOut->eSubtype = (u8)(pIn1->u.i & 0xff);
  }
  break;
}

/* Opcode: FilterAdd P1 * P3 P4 *
** Synopsis: filter(P1) += key(P3@P4)
**
** Compute a hash on the P4 registers starting with r[P3] and
** add that hash to the bloom filter contained in r[P1].
*/

  WITH c(x,y,z) AS (VALUES('a',4,5),('b',3,6),('b',2,7),('c',1,8))
  SELECT group_concat(DISTINCT x ORDER BY y, z) FROM c;
} {c,b,a}
do_execsql_test aggorderby-8.2 {
  WITH c(x,y) AS (VALUES(1,1),(2,2),(3,3),(3,4),(3,5),(3,6))
  SELECT sum(DISTINCT x ORDER BY y) FROM c;
} 6

# Subtype information is transfered through the sorter for aggregates
# that make use of subtype info.
#
do_execsql_test aggorderby-9.0 {
  WITH c(x,y) AS (VALUES
    ('{a:3}', 3),
    ('[1,1]', 1),
    ('[4,4]', 4),
    ('{x:2}', 2))
  SELECT json_group_array(json(x) ORDER BY y) FROM c;
} {{[[1,1],{"x":2},{"a":3},[4,4]]}}
do_execsql_test aggorderby-9.1 {
  WITH c(x,y) AS (VALUES
    ('[4,4]', 4),
    ('{a:3}', 3),
    ('[4,4]', 4),
    ('[1,1]', 1),
    ('[4,4]', 4),
    ('{x:2}', 2))
  SELECT json_group_array(DISTINCT json(x) ORDER BY y) FROM c;
} {{[[1,1],{"x":2},{"a":3},[4,4]]}}
do_execsql_test aggorderby-9.2 {
  WITH c(x,y) AS (VALUES
    ('{a:3}', 3),
    ('[1,1]', 1),
    ('[4,4]', 4),
    ('{x:2}', 2))
  SELECT json_group_array(json(x) ORDER BY json(x)) FROM c;
} {{[[1,1],[4,4],{"a":3},{"x":2}]}}
do_execsql_test aggorderby-9.3 {
  WITH c(x,y) AS (VALUES
    ('[4,4]', 4),
    ('{a:3}', 3),
    ('[4,4]', 4),
    ('[1,1]', 1),
    ('[4,4]', 4),
    ('{x:2}', 2))
  SELECT json_group_array(DISTINCT json(x) ORDER BY json(x)) FROM c;
} {{[[1,1],[4,4],{"a":3},{"x":2}]}}


finish_test