/ Check-in [655e75ac]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Add VM code comments on the group-by processing. Extra group-by test case. (CVS 2731)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 655e75ac7d96b05c118226c5b70443da2df319ec
User & Date: drh 2005-09-20 18:13:24
Context
2005-09-22
15:45
Optionally call fdatasync() instead of fsync() only if _POSIX_SYNCHRONIZED_IO is positive, which should only be the case on operating systems that actually support fdatasync(). (CVS 2732) check-in: a9b341dc user: drh tags: trunk
2005-09-20
18:13
Add VM code comments on the group-by processing. Extra group-by test case. (CVS 2731) check-in: 655e75ac user: drh tags: trunk
17:42
Code cleanup and simplification. Three new Mem opcodes added. The sqlite3VdbeJumpHere function added. (CVS 2730) check-in: 2471957f user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains C code routines that are called by the parser
    13     13   ** to handle SELECT statements in SQLite.
    14     14   **
    15         -** $Id: select.c,v 1.275 2005/09/20 17:42:23 drh Exp $
           15  +** $Id: select.c,v 1.276 2005/09/20 18:13:24 drh Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   
    19     19   
    20     20   /*
    21     21   ** Allocate a new Select structure and return a pointer to that
    22     22   ** structure.
................................................................................
  2911   2911         iUseFlag = pParse->nMem++;
  2912   2912         iAbortFlag = pParse->nMem++;
  2913   2913         iAMem = pParse->nMem;
  2914   2914         pParse->nMem += pGroupBy->nExpr;
  2915   2915         iBMem = pParse->nMem;
  2916   2916         pParse->nMem += pGroupBy->nExpr;
  2917   2917         sqlite3VdbeAddOp(v, OP_MemInt, 0, iAbortFlag);
         2918  +      VdbeComment((v, "# clear abort flag"));
  2918   2919         sqlite3VdbeAddOp(v, OP_MemInt, 0, iUseFlag);
  2919         -      sqlite3VdbeAddOp(v, OP_MemNull, iAMem, 0);
         2920  +      VdbeComment((v, "# indicate accumulator empty"));
  2920   2921         sqlite3VdbeAddOp(v, OP_Goto, 0, addrInitializeLoop);
  2921   2922   
  2922   2923         /* Generate a subroutine that outputs a single row of the result
  2923   2924         ** set.  This subroutine first looks at the iUseFlag.  If iUseFlag
  2924   2925         ** is less than or equal to zero, the subroutine is a no-op.  If
  2925   2926         ** the processing calls for the query to abort, this subroutine
  2926   2927         ** increments the iAbortFlag memory location before returning in
  2927   2928         ** order to signal the caller to abort.
  2928   2929         */
  2929   2930         addrSetAbort = sqlite3VdbeCurrentAddr(v);
  2930         -      sqlite3VdbeAddOp(v, OP_MemIncr, iAbortFlag, 0);
         2931  +      sqlite3VdbeAddOp(v, OP_MemInt, 1, iAbortFlag);
         2932  +      VdbeComment((v, "# set abort flag"));
  2931   2933         sqlite3VdbeAddOp(v, OP_Return, 0, 0);
  2932   2934         addrOutputRow = sqlite3VdbeCurrentAddr(v);
  2933   2935         sqlite3VdbeAddOp(v, OP_IfMemPos, iUseFlag, addrOutputRow+2);
         2936  +      VdbeComment((v, "# Groupby result generator entry point"));
  2934   2937         sqlite3VdbeAddOp(v, OP_Return, 0, 0);
  2935   2938         finalizeAggFunctions(pParse, &sAggInfo);
  2936   2939         if( pHaving ){
  2937   2940           sqlite3ExprIfFalse(pParse, pHaving, addrOutputRow+1, 1);
  2938   2941         }
  2939   2942         rc = selectInnerLoop(pParse, p, p->pEList, 0, 0, pOrderBy,
  2940   2943                              distinct, eDest, iParm, 
  2941   2944                              addrOutputRow+1, addrSetAbort, aff);
  2942   2945         if( rc ){
  2943   2946           goto select_end;
  2944   2947         }
  2945   2948         sqlite3VdbeAddOp(v, OP_Return, 0, 0);
         2949  +      VdbeComment((v, "# end groupby result generator"));
  2946   2950   
  2947   2951         /* Generate a subroutine that will reset the group-by accumulator
  2948   2952         */
  2949   2953         addrReset = sqlite3VdbeCurrentAddr(v);
  2950   2954         resetAccumulator(pParse, &sAggInfo);
  2951   2955         sqlite3VdbeAddOp(v, OP_Return, 0, 0);
  2952   2956   
................................................................................
  2986   2990             }
  2987   2991             j++;
  2988   2992           }
  2989   2993           sqlite3VdbeAddOp(v, OP_MakeRecord, j, 0);
  2990   2994           sqlite3VdbeAddOp(v, OP_IdxInsert, sAggInfo.sortingIdx, 0);
  2991   2995           sqlite3WhereEnd(pWInfo);
  2992   2996           sqlite3VdbeAddOp(v, OP_Sort, sAggInfo.sortingIdx, addrEnd);
         2997  +        VdbeComment((v, "# GROUP BY sort"));
  2993   2998           sAggInfo.useSortingIdx = 1;
  2994   2999         }
  2995   3000   
  2996   3001         /* Evaluate the current GROUP BY terms and store in b0, b1, b2...
  2997   3002         ** (b0 is memory location iBMem+0, b1 is iBMem+1, and so forth)
  2998   3003         ** Then compare the current GROUP BY terms against the GROUP BY terms
  2999   3004         ** from the previous row currently stored in a0, a1, a2...
................................................................................
  3031   3036         ** for the next GROUP BY batch.
  3032   3037         */
  3033   3038         sqlite3VdbeResolveLabel(v, addrGroupByChange);
  3034   3039         for(j=0; j<pGroupBy->nExpr; j++){
  3035   3040           sqlite3VdbeAddOp(v, OP_MemMove, iAMem+j, iBMem+j);
  3036   3041         }
  3037   3042         sqlite3VdbeAddOp(v, OP_Gosub, 0, addrOutputRow);
         3043  +      VdbeComment((v, "# output one row"));
  3038   3044         sqlite3VdbeAddOp(v, OP_IfMemPos, iAbortFlag, addrEnd);
         3045  +      VdbeComment((v, "# check abort flag"));
  3039   3046         sqlite3VdbeAddOp(v, OP_Gosub, 0, addrReset);
         3047  +      VdbeComment((v, "# reset accumulator"));
  3040   3048   
  3041   3049         /* Update the aggregate accumulators based on the content of
  3042   3050         ** the current row
  3043   3051         */
  3044   3052         sqlite3VdbeResolveLabel(v, addrProcessRow);
  3045   3053         updateAccumulator(pParse, &sAggInfo);
  3046         -      sqlite3VdbeAddOp(v, OP_MemIncr, iUseFlag, 0);
         3054  +      sqlite3VdbeAddOp(v, OP_MemInt, 1, iUseFlag);
         3055  +      VdbeComment((v, "# indicate data in accumulator"));
  3047   3056   
  3048   3057         /* End of the loop
  3049   3058         */
  3050   3059         if( groupBySort ){
  3051   3060           sqlite3VdbeAddOp(v, OP_Next, sAggInfo.sortingIdx, addrTopOfLoop);
  3052   3061         }else{
  3053   3062           sqlite3WhereEnd(pWInfo);
  3054   3063           uncreateSortingIndex(pParse, addrSortingIdx);
  3055   3064         }
  3056   3065   
  3057   3066         /* Output the final row of result
  3058   3067         */
  3059   3068         sqlite3VdbeAddOp(v, OP_Gosub, 0, addrOutputRow);
         3069  +      VdbeComment((v, "# output final row"));
  3060   3070         
  3061   3071       } /* endif pGroupBy */
  3062   3072       else {
  3063   3073         /* This case runs if the aggregate has no GROUP BY clause.  The
  3064   3074         ** processing is much simpler since there is only a single row
  3065   3075         ** of output.
  3066   3076         */

Changes to test/select5.test.

     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing aggregate functions and the
    13     13   # GROUP BY and HAVING clauses of SELECT statements.
    14     14   #
    15         -# $Id: select5.test,v 1.14 2005/09/20 13:12:00 drh Exp $
           15  +# $Id: select5.test,v 1.15 2005/09/20 18:13:25 drh Exp $
    16     16   
    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
    19     19   
    20     20   # Build some test data
    21     21   #
    22     22   execsql {
................................................................................
   164    164       CREATE TABLE t3(x,y);
   165    165       INSERT INTO t3 VALUES(1,NULL);
   166    166       INSERT INTO t3 VALUES(2,NULL);
   167    167       INSERT INTO t3 VALUES(3,4);
   168    168       SELECT count(x), y FROM t3 GROUP BY y ORDER BY 1
   169    169     }
   170    170   } {1 4 2 {}}
          171  +do_test select5-6.2 {
          172  +  execsql {
          173  +    CREATE TABLE t4(x,y,z);
          174  +    INSERT INTO t4 VALUES(1,2,NULL);
          175  +    INSERT INTO t4 VALUES(2,3,NULL);
          176  +    INSERT INTO t4 VALUES(3,NULL,5);
          177  +    INSERT INTO t4 VALUES(4,NULL,6);
          178  +    INSERT INTO t4 VALUES(4,NULL,6);
          179  +    INSERT INTO t4 VALUES(5,NULL,NULL);
          180  +    INSERT INTO t4 VALUES(5,NULL,NULL);
          181  +    INSERT INTO t4 VALUES(6,7,8);
          182  +    SELECT max(x), count(x), y, z FROM t4 GROUP BY y, z ORDER BY 1
          183  +  }
          184  +} {1 1 2 {} 2 1 3 {} 3 1 {} 5 4 2 {} 6 5 2 {} {} 6 1 7 8}
   171    185     
   172    186   finish_test