SQLite

**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.276 2005/09/20 18:13:24 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.

      iUseFlag = pParse->nMem++;
      iAbortFlag = pParse->nMem++;
      iAMem = pParse->nMem;
      pParse->nMem += pGroupBy->nExpr;
      iBMem = pParse->nMem;
      pParse->nMem += pGroupBy->nExpr;
      sqlite3VdbeAddOp(v, OP_MemInt, 0, iAbortFlag);
      VdbeComment((v, "# clear abort flag"));
      sqlite3VdbeAddOp(v, OP_MemInt, 0, iUseFlag);

      VdbeComment((v, "# indicate accumulator empty"));
      sqlite3VdbeAddOp(v, OP_Goto, 0, addrInitializeLoop);

      /* Generate a subroutine that outputs a single row of the result
      ** set.  This subroutine first looks at the iUseFlag.  If iUseFlag
      ** is less than or equal to zero, the subroutine is a no-op.  If
      ** the processing calls for the query to abort, this subroutine
      ** increments the iAbortFlag memory location before returning in
      ** order to signal the caller to abort.
      */
      addrSetAbort = sqlite3VdbeCurrentAddr(v);
      sqlite3VdbeAddOp(v, OP_MemInt, 1, iAbortFlag);
      VdbeComment((v, "# set abort flag"));
      sqlite3VdbeAddOp(v, OP_Return, 0, 0);
      addrOutputRow = sqlite3VdbeCurrentAddr(v);
      sqlite3VdbeAddOp(v, OP_IfMemPos, iUseFlag, addrOutputRow+2);
      VdbeComment((v, "# Groupby result generator entry point"));
      sqlite3VdbeAddOp(v, OP_Return, 0, 0);
      finalizeAggFunctions(pParse, &sAggInfo);
      if( pHaving ){
        sqlite3ExprIfFalse(pParse, pHaving, addrOutputRow+1, 1);
      }
      rc = selectInnerLoop(pParse, p, p->pEList, 0, 0, pOrderBy,
                           distinct, eDest, iParm, 
                           addrOutputRow+1, addrSetAbort, aff);
      if( rc ){
        goto select_end;
      }
      sqlite3VdbeAddOp(v, OP_Return, 0, 0);
      VdbeComment((v, "# end groupby result generator"));

      /* Generate a subroutine that will reset the group-by accumulator
      */
      addrReset = sqlite3VdbeCurrentAddr(v);
      resetAccumulator(pParse, &sAggInfo);
      sqlite3VdbeAddOp(v, OP_Return, 0, 0);

          }
          j++;
        }
        sqlite3VdbeAddOp(v, OP_MakeRecord, j, 0);
        sqlite3VdbeAddOp(v, OP_IdxInsert, sAggInfo.sortingIdx, 0);
        sqlite3WhereEnd(pWInfo);
        sqlite3VdbeAddOp(v, OP_Sort, sAggInfo.sortingIdx, addrEnd);
        VdbeComment((v, "# GROUP BY sort"));
        sAggInfo.useSortingIdx = 1;
      }

      /* Evaluate the current GROUP BY terms and store in b0, b1, b2...
      ** (b0 is memory location iBMem+0, b1 is iBMem+1, and so forth)
      ** Then compare the current GROUP BY terms against the GROUP BY terms
      ** from the previous row currently stored in a0, a1, a2...

      ** for the next GROUP BY batch.
      */
      sqlite3VdbeResolveLabel(v, addrGroupByChange);
      for(j=0; j<pGroupBy->nExpr; j++){
        sqlite3VdbeAddOp(v, OP_MemMove, iAMem+j, iBMem+j);
      }
      sqlite3VdbeAddOp(v, OP_Gosub, 0, addrOutputRow);
      VdbeComment((v, "# output one row"));
      sqlite3VdbeAddOp(v, OP_IfMemPos, iAbortFlag, addrEnd);
      VdbeComment((v, "# check abort flag"));
      sqlite3VdbeAddOp(v, OP_Gosub, 0, addrReset);
      VdbeComment((v, "# reset accumulator"));

      /* Update the aggregate accumulators based on the content of
      ** the current row
      */
      sqlite3VdbeResolveLabel(v, addrProcessRow);
      updateAccumulator(pParse, &sAggInfo);
      sqlite3VdbeAddOp(v, OP_MemInt, 1, iUseFlag);
      VdbeComment((v, "# indicate data in accumulator"));

      /* End of the loop
      */
      if( groupBySort ){
        sqlite3VdbeAddOp(v, OP_Next, sAggInfo.sortingIdx, addrTopOfLoop);
      }else{
        sqlite3WhereEnd(pWInfo);
        uncreateSortingIndex(pParse, addrSortingIdx);
      }

      /* Output the final row of result
      */
      sqlite3VdbeAddOp(v, OP_Gosub, 0, addrOutputRow);
      VdbeComment((v, "# output final row"));
      
    } /* endif pGroupBy */
    else {
      /* This case runs if the aggregate has no GROUP BY clause.  The
      ** processing is much simpler since there is only a single row
      ** of output.
      */

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing aggregate functions and the
# GROUP BY and HAVING clauses of SELECT statements.
#
# $Id: select5.test,v 1.15 2005/09/20 18:13:25 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Build some test data
#
execsql {

    CREATE TABLE t3(x,y);
    INSERT INTO t3 VALUES(1,NULL);
    INSERT INTO t3 VALUES(2,NULL);
    INSERT INTO t3 VALUES(3,4);
    SELECT count(x), y FROM t3 GROUP BY y ORDER BY 1
  }
} {1 4 2 {}}
do_test select5-6.2 {
  execsql {
    CREATE TABLE t4(x,y,z);
    INSERT INTO t4 VALUES(1,2,NULL);
    INSERT INTO t4 VALUES(2,3,NULL);
    INSERT INTO t4 VALUES(3,NULL,5);
    INSERT INTO t4 VALUES(4,NULL,6);
    INSERT INTO t4 VALUES(4,NULL,6);
    INSERT INTO t4 VALUES(5,NULL,NULL);
    INSERT INTO t4 VALUES(5,NULL,NULL);
    INSERT INTO t4 VALUES(6,7,8);
    SELECT max(x), count(x), y, z FROM t4 GROUP BY y, z ORDER BY 1
  }
} {1 1 2 {} 2 1 3 {} 3 1 {} 5 4 2 {} 6 5 2 {} {} 6 1 7 8}
  
finish_test