SQLite4
Check-in [7aace3e09f]
Not logged in

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

Overview
Comment:Get some more aggregate queries working.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 7aace3e09f9a492a56c958445508457e310f8197
User & Date: dan 2012-04-21 17:33:12
Context
2012-04-21
18:55
Fixes to the text of the key encoding definition in key_encoding.txt. check-in: ee5b8b8d11 user: drh tags: trunk
17:33
Get some more aggregate queries working. check-in: 7aace3e09f user: dan tags: trunk
13:56
Get rid of the page-cache and scratch memory allocators. check-in: 9346a42e0d user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c.

2292
2293
2294
2295
2296
2297
2298
2299
2300
2301
2302
2303
2304
2305
2306
2307
      AggInfo *pAggInfo = pExpr->pAggInfo;
      struct AggInfo_col *pCol = &pAggInfo->aCol[pExpr->iAgg];
      if( !pAggInfo->directMode ){
        assert( pCol->iMem>0 );
        inReg = pCol->iMem;
        break;
      }else if( pAggInfo->useSortingIdx ){
        sqlite4VdbeAddOp3(v, OP_Column, pAggInfo->sortingIdxPTab,
                              pCol->iSorterColumn, target);
        break;
      }
      /* Otherwise, fall thru into the TK_COLUMN case */
    }
    case TK_COLUMN: {
      if( pExpr->iTable<0 ){
        /* This only happens when coding check constraints */







|
|







2292
2293
2294
2295
2296
2297
2298
2299
2300
2301
2302
2303
2304
2305
2306
2307
      AggInfo *pAggInfo = pExpr->pAggInfo;
      struct AggInfo_col *pCol = &pAggInfo->aCol[pExpr->iAgg];
      if( !pAggInfo->directMode ){
        assert( pCol->iMem>0 );
        inReg = pCol->iMem;
        break;
      }else if( pAggInfo->useSortingIdx ){
        sqlite4VdbeAddOp3(v, OP_Column, pAggInfo->sortingIdx,
                              pExpr->iAgg, target);
        break;
      }
      /* Otherwise, fall thru into the TK_COLUMN case */
    }
    case TK_COLUMN: {
      if( pExpr->iTable<0 ){
        /* This only happens when coding check constraints */

Changes to src/select.c.

759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
....
3763
3764
3765
3766
3767
3768
3769


3770
3771
3772
3773
3774
3775
3776
3777
3778
3779
3780
3781
3782
....
4176
4177
4178
4179
4180
4181
4182
















4183
4184
4185
4186
4187
4188
4189
4190
4191
4192
4193
4194
4195
4196
4197
4198
4199
4200
4201
4202
4203
4204
4205
4206
4207
4208
4209
4210
4211
4212
4213
4214
4215
4216
4217
4218
4219
4220
4221
4222
4223
4224
4225
4226
4227
4228
4229
4230
4231
4232
4233
4234
4235
4236
4237
4238
4239
4240
4241
4242
4243
4244
4245

4246
4247
4248
4249
4250
4251
4252
4253
4254
4255
4256
4257
4258
4259
4260
4261
4262
4263
4264
4265
4266
4267
4268
4269
4270
4271
4272
4273

4274

4275
4276
4277
4278
4279
4280
4281
  */
  if( pOrderBy==0 && p->iLimit ){
    sqlite4VdbeAddOp3(v, OP_IfZero, p->iLimit, iBreak, -1);
  }
}

/*
** Given an expression list, generate a KeyInfo structure that records
** the collating sequence for each expression in that expression list.
**
** If the ExprList is an ORDER BY or GROUP BY clause then the resulting
** KeyInfo structure is appropriate for initializing a virtual index to
** implement that clause.  If the ExprList is the result set of a SELECT
** then the KeyInfo structure is appropriate for initializing a virtual
** index to implement a DISTINCT test.
**
** Space to hold the KeyInfo structure is obtain from malloc.  The calling
** function is responsible for seeing that this structure is eventually
** freed.  Add the KeyInfo structure to the P4 field of an opcode using
** P4_KEYINFO_HANDOFF is the usual way of dealing with this.
*/
static KeyInfo *keyInfoFromExprList(
  Parse *pParse, 
  ExprList *pList,
  int bOrderBy
){
  sqlite4 *db = pParse->db;       /* Database handle */
  int nField;                     /* Number of fields in keys */
  KeyInfo *pInfo;                 /* Object to return */
  int nByte;                      /* Bytes of space to allocate */

  assert( bOrderBy==0 || bOrderBy==1 );
................................................................................
  sqlite4 *db;           /* The database connection */

#ifndef SQLITE_OMIT_EXPLAIN
  int iRestoreSelectId = pParse->iSelectId;
  pParse->iSelectId = pParse->iNextSelectId++;
#endif



  db = pParse->db;
  if( p==0 || db->mallocFailed || pParse->nErr ){
    return 1;
  }
  if( sqlite4AuthCheck(pParse, SQLITE_SELECT, 0, 0, 0) ) return 1;
  memset(&sAggInfo, 0, sizeof(sAggInfo));

  if( IgnorableOrderby(pDest) ){
    assert(pDest->eDest==SRT_Exists || pDest->eDest==SRT_Union || 
           pDest->eDest==SRT_Except || pDest->eDest==SRT_Discard);
    /* If ORDER BY makes no difference in the output then neither does
    ** DISTINCT so it can be removed too. */
    sqlite4ExprListDelete(db, p->pOrderBy);
................................................................................
      if( pGroupBy==0 ){
        /* The optimizer is able to deliver rows in group by order so
        ** we do not have to sort.  The OP_OpenEphemeral table will be
        ** cancelled later because we still need to use the pKeyInfo
        */
        pGroupBy = p->pGroupBy;
        groupBySort = 0;
















      }else{
        /* Rows are coming out in undetermined order.  We have to push
        ** each row into a sorting index, terminate the first loop,
        ** then loop over the sorting index in order to get the output
        ** in sorted order
        */
        int regBase;
        int regRecord;
        int nCol;
        int nGroupBy;

        explainTempTable(pParse, 
            isDistinct && !(p->selFlags&SF_Distinct)?"DISTINCT":"GROUP BY");

        groupBySort = 1;
        nGroupBy = pGroupBy->nExpr;
        nCol = nGroupBy + 1;
        j = nGroupBy+1;
        for(i=0; i<sAggInfo.nColumn; i++){
          if( sAggInfo.aCol[i].iSorterColumn>=j ){
            nCol++;
            j++;
          }
        }
        regBase = sqlite4GetTempRange(pParse, nCol);
        sqlite4ExprCacheClear(pParse);
        sqlite4ExprCodeExprList(pParse, pGroupBy, regBase, 0);
        sqlite4VdbeAddOp2(v, OP_Sequence, sAggInfo.sortingIdx,regBase+nGroupBy);
        j = nGroupBy+1;
        for(i=0; i<sAggInfo.nColumn; i++){
          struct AggInfo_col *pCol = &sAggInfo.aCol[i];
          if( pCol->iSorterColumn>=j ){
            int r1 = j + regBase;
            int r2;

            r2 = sqlite4ExprCodeGetColumn(pParse, 
                               pCol->pTab, pCol->iColumn, pCol->iTable, r1);
            if( r1!=r2 ){
              sqlite4VdbeAddOp2(v, OP_SCopy, r2, r1);
            }
            j++;
          }
        }
        regRecord = sqlite4GetTempReg(pParse);
        sqlite4VdbeAddOp3(v, OP_MakeRecord, regBase, nCol, regRecord);
        sqlite4VdbeAddOp2(v, OP_SorterInsert, sAggInfo.sortingIdx, regRecord);
        sqlite4ReleaseTempReg(pParse, regRecord);
        sqlite4ReleaseTempRange(pParse, regBase, nCol);
        sqlite4WhereEnd(pWInfo);
        sAggInfo.sortingIdxPTab = sortPTab = pParse->nTab++;
        sortOut = sqlite4GetTempReg(pParse);
        sqlite4VdbeAddOp3(v, OP_OpenPseudo, sortPTab, sortOut, nCol);
        sqlite4VdbeAddOp2(v, OP_SorterSort, sAggInfo.sortingIdx, addrEnd);
        VdbeComment((v, "GROUP BY sort"));
        sAggInfo.useSortingIdx = 1;
        sqlite4ExprCacheClear(pParse);
      }

      /* 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...
      */

      addrTopOfLoop = sqlite4VdbeCurrentAddr(v);
      sqlite4ExprCacheClear(pParse);
      if( groupBySort ){
        sqlite4VdbeAddOp2(v, OP_SorterData, sAggInfo.sortingIdx, sortOut);
      }
      for(j=0; j<pGroupBy->nExpr; j++){
        if( groupBySort ){
          sqlite4VdbeAddOp3(v, OP_Column, sortPTab, j, iBMem+j);
          if( j==0 ) sqlite4VdbeChangeP5(v, OPFLAG_CLEARCACHE);
        }else{
          sAggInfo.directMode = 1;
          sqlite4ExprCode(pParse, pGroupBy->a[j].pExpr, iBMem+j);
        }
      }
      sqlite4VdbeAddOp4(v, OP_Compare, iAMem, iBMem, pGroupBy->nExpr,
                          (char*)pKeyInfo, P4_KEYINFO);
      j1 = sqlite4VdbeCurrentAddr(v);
      sqlite4VdbeAddOp3(v, OP_Jump, j1+1, 0, j1+1);

      /* Generate code that runs whenever the GROUP BY changes.
      ** Changes in the GROUP BY are detected by the previous code
      ** block.  If there were no changes, this block is skipped.
      **
      ** This code copies current group by terms in b0,b1,b2,...
      ** over to a0,a1,a2.  It then calls the output subroutine
      ** and resets the aggregate accumulator registers in preparation
      ** for the next GROUP BY batch.
      */

      sqlite4ExprCodeMove(pParse, iBMem, iAMem, pGroupBy->nExpr);

      sqlite4VdbeAddOp2(v, OP_Gosub, regOutputRow, addrOutputRow);
      VdbeComment((v, "output one row"));
      sqlite4VdbeAddOp2(v, OP_IfPos, iAbortFlag, addrEnd);
      VdbeComment((v, "check abort flag"));
      sqlite4VdbeAddOp2(v, OP_Gosub, regReset, addrReset);
      VdbeComment((v, "reset accumulator"));








|
|



|











|







 







>
>





<







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>




|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
<




|
<
<
<
<
<
<
>
|
<
<
<

<
<
<
<
<
<
<
|
<
<
<
<
<
<









>
|
>







759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
....
3763
3764
3765
3766
3767
3768
3769
3770
3771
3772
3773
3774
3775
3776

3777
3778
3779
3780
3781
3782
3783
....
4177
4178
4179
4180
4181
4182
4183
4184
4185
4186
4187
4188
4189
4190
4191
4192
4193
4194
4195
4196
4197
4198
4199
4200
4201
4202
4203
4204
4205
4206
4207
4208
4209
4210
4211
4212
4213
4214
4215
4216
4217
4218
4219
4220
4221
4222
4223
4224
4225
4226
4227
4228
4229
4230
4231
4232
4233
4234
4235
4236
4237
4238
4239
4240
4241
4242
4243
4244
4245
4246
4247
4248
4249
4250

4251
4252
4253
4254
4255






4256
4257



4258







4259






4260
4261
4262
4263
4264
4265
4266
4267
4268
4269
4270
4271
4272
4273
4274
4275
4276
4277
4278
  */
  if( pOrderBy==0 && p->iLimit ){
    sqlite4VdbeAddOp3(v, OP_IfZero, p->iLimit, iBreak, -1);
  }
}

/*
** Given an expression list, generate a KeyInfo structure that can be
** used to encode the results of the expressions into an index key.
**
** If the ExprList is an ORDER BY or GROUP BY clause then the resulting
** KeyInfo structure is appropriate for initializing a virtual index to
** implement that clause. If the ExprList is the result set of a SELECT
** then the KeyInfo structure is appropriate for initializing a virtual
** index to implement a DISTINCT test.
**
** Space to hold the KeyInfo structure is obtain from malloc.  The calling
** function is responsible for seeing that this structure is eventually
** freed.  Add the KeyInfo structure to the P4 field of an opcode using
** P4_KEYINFO_HANDOFF is the usual way of dealing with this.
*/
static KeyInfo *keyInfoFromExprList(
  Parse *pParse, 
  ExprList *pList,
  int bOrderBy                    /* True for ORDER BY */
){
  sqlite4 *db = pParse->db;       /* Database handle */
  int nField;                     /* Number of fields in keys */
  KeyInfo *pInfo;                 /* Object to return */
  int nByte;                      /* Bytes of space to allocate */

  assert( bOrderBy==0 || bOrderBy==1 );
................................................................................
  sqlite4 *db;           /* The database connection */

#ifndef SQLITE_OMIT_EXPLAIN
  int iRestoreSelectId = pParse->iSelectId;
  pParse->iSelectId = pParse->iNextSelectId++;
#endif

  memset(&sAggInfo, 0, sizeof(sAggInfo));

  db = pParse->db;
  if( p==0 || db->mallocFailed || pParse->nErr ){
    return 1;
  }
  if( sqlite4AuthCheck(pParse, SQLITE_SELECT, 0, 0, 0) ) return 1;


  if( IgnorableOrderby(pDest) ){
    assert(pDest->eDest==SRT_Exists || pDest->eDest==SRT_Union || 
           pDest->eDest==SRT_Except || pDest->eDest==SRT_Discard);
    /* If ORDER BY makes no difference in the output then neither does
    ** DISTINCT so it can be removed too. */
    sqlite4ExprListDelete(db, p->pOrderBy);
................................................................................
      if( pGroupBy==0 ){
        /* The optimizer is able to deliver rows in group by order so
        ** we do not have to sort.  The OP_OpenEphemeral table will be
        ** cancelled later because we still need to use the pKeyInfo
        */
        pGroupBy = p->pGroupBy;
        groupBySort = 0;

        /* 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...
        */
        sAggInfo.directMode = 1;
        addrTopOfLoop = sqlite4VdbeCurrentAddr(v);
        sqlite4ExprCacheClear(pParse);
        for(j=0; j<pGroupBy->nExpr; j++){
          sqlite4ExprCode(pParse, pGroupBy->a[j].pExpr, iBMem+j);
        }
        sqlite4VdbeAddOp4(v, OP_Compare, iAMem, iBMem, pGroupBy->nExpr,
            (char*)pKeyInfo, P4_KEYINFO);
        j1 = sqlite4VdbeCurrentAddr(v);
        sqlite4VdbeAddOp3(v, OP_Jump, j1+1, 0, j1+1);
      }else{
        /* Rows are coming out in undetermined order.  We have to push
        ** each row into a sorting index, terminate the first loop,
        ** then loop over the sorting index in order to get the output
        ** in sorted order */
        int regBase;
        int nCol = sAggInfo.nColumn;
        int nGroup = pGroupBy->nExpr;
        int regKey = ++pParse->nMem;
        int regRecord = ++pParse->nMem;

        groupBySort = 1;

        explainTempTable(pParse, 
            isDistinct && !(p->selFlags&SF_Distinct)?"DISTINCT":"GROUP BY");

        /* Encode the key for the sorting index. The key consists of each
        ** of the expressions in the GROUP BY list followed by a sequence
        ** number (to ensure each key is unique - the point of this is just
        ** to sort the rows, not to eliminate duplicates).  */
        sqlite4ExprCacheClear(pParse);
        regBase = sqlite4GetTempRange(pParse, nGroup);
        sqlite4ExprCodeExprList(pParse, pGroupBy, regBase, 0);
        sqlite4VdbeAddOp3(v, OP_MakeIdxKey, sAggInfo.sortingIdx,regBase,regKey);
        sqlite4VdbeChangeP5(v, 1);
        sqlite4ReleaseTempRange(pParse, regBase, nGroup);

        /* Encode the record for the sorting index. The record contains all
        ** required column values from the elements of the FROM clause.  */
        regBase = sqlite4GetTempRange(pParse, nCol);
        for(i=0; i<nCol; i++){
          struct AggInfo_col *pCol = &sAggInfo.aCol[i];
          int regDest = i + regBase;
          int regValue = sqlite4ExprCodeGetColumn(
              pParse, pCol->pTab, pCol->iColumn, pCol->iTable, regDest
          );
          if( regDest!=regValue ){
            sqlite4VdbeAddOp2(v, OP_SCopy, regValue, regDest);
          }
        }
        sqlite4VdbeAddOp3(v, OP_MakeRecord, regBase, nCol, regRecord);
        sqlite4ReleaseTempRange(pParse, regBase, nCol);

        /* Insert the key/value into the sorting index and end the loop
        ** generated by where.c code.  */
        sqlite4VdbeAddOp3(
            v, OP_SorterInsert, sAggInfo.sortingIdx, regRecord, regKey
        );
        sqlite4WhereEnd(pWInfo);

        sqlite4VdbeAddOp2(v, OP_Null, 0, regKey);

        sqlite4VdbeAddOp2(v, OP_SorterSort, sAggInfo.sortingIdx, addrEnd);
        VdbeComment((v, "GROUP BY sort"));
        sAggInfo.useSortingIdx = 1;
        sqlite4ExprCacheClear(pParse);







        j1 = sqlite4VdbeAddOp3(v, OP_GrpCompare, sAggInfo.sortingIdx, 0,regKey);
        addrTopOfLoop = j1;



      }














      /* Generate code that runs whenever the GROUP BY changes.
      ** Changes in the GROUP BY are detected by the previous code
      ** block.  If there were no changes, this block is skipped.
      **
      ** This code copies current group by terms in b0,b1,b2,...
      ** over to a0,a1,a2.  It then calls the output subroutine
      ** and resets the aggregate accumulator registers in preparation
      ** for the next GROUP BY batch.
      */
      if( groupBySort==0 ){
        sqlite4ExprCodeMove(pParse, iBMem, iAMem, pGroupBy->nExpr);
      }
      sqlite4VdbeAddOp2(v, OP_Gosub, regOutputRow, addrOutputRow);
      VdbeComment((v, "output one row"));
      sqlite4VdbeAddOp2(v, OP_IfPos, iAbortFlag, addrEnd);
      VdbeComment((v, "check abort flag"));
      sqlite4VdbeAddOp2(v, OP_Gosub, regReset, addrReset);
      VdbeComment((v, "reset accumulator"));

Changes to src/sqliteInt.h.

1546
1547
1548
1549
1550
1551
1552

1553

1554
1555
1556
1557
1558
1559
1560
*/
struct AggInfo {
  u8 directMode;          /* Direct rendering mode means take data directly
                          ** from source tables rather than from accumulators */
  u8 useSortingIdx;       /* In direct mode, reference the sorting index rather
                          ** than the source table */
  int sortingIdx;         /* Cursor number of the sorting index */

  int sortingIdxPTab;     /* Cursor number of pseudo-table */

  ExprList *pGroupBy;     /* The group by clause */
  int nSortingColumn;     /* Number of columns in the sorting index */
  struct AggInfo_col {    /* For each column used in source tables */
    Table *pTab;             /* Source table */
    int iTable;              /* Cursor number of the source table */
    int iColumn;             /* Column number within the source table */
    int iSorterColumn;       /* Column number in the sorting index */







>

>







1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
*/
struct AggInfo {
  u8 directMode;          /* Direct rendering mode means take data directly
                          ** from source tables rather than from accumulators */
  u8 useSortingIdx;       /* In direct mode, reference the sorting index rather
                          ** than the source table */
  int sortingIdx;         /* Cursor number of the sorting index */
#if 0
  int sortingIdxPTab;     /* Cursor number of pseudo-table */
#endif
  ExprList *pGroupBy;     /* The group by clause */
  int nSortingColumn;     /* Number of columns in the sorting index */
  struct AggInfo_col {    /* For each column used in source tables */
    Table *pTab;             /* Source table */
    int iTable;              /* Cursor number of the source table */
    int iColumn;             /* Column number within the source table */
    int iSorterColumn;       /* Column number in the sorting index */

Changes to src/vdbe.c.

2169
2170
2171
2172
2173
2174
2175
2176
2177
2178
2179
2180
2181
2182
2183
2184
2185
2186

2187
2188
2189


2190
2191
2192
2193
2194
2195
2196
2197



2198
2199
2200
2201
2202
2203















2204
2205
2206
2207
2208
2209
2210
2211
2212
2213
2214
2215
2216
2217
2218



2219
2220
2221
2222
2223
2224
2225
2226
....
2712
2713
2714
2715
2716
2717
2718


2719
2720
2721
2722
2723
2724
2725
....
3397
3398
3399
3400
3401
3402
3403





































3404
3405
3406
3407
3408
3409
3410
    applyAffinity(pIn1, *(zAffinity++), encoding);
    REGISTER_TRACE(pIn1-aMem, pIn1);
  }

  break;
}

/* Opcode: MakeIdxKey P1 P2 P3 P4 *
**
** P1 is an open cursor. P2 is the first register in a contiguous array
** of N registers containing values to encode into a database key. Normally,
** N is equal to the number of columns indexed by P1, plus the number of 
** trailing primary key columns (if any). 
**
** Or, if P4 is a non-zero integer, then it contains the value for N.
**
** This instruction encodes the N values into a database key and writes
** the result to register P3.

**
** No affinity transformations are applied to the input values before 
** they are encoded. 


*/
case OP_MakeIdxKey: {
  VdbeCursor *pC;
  KeyInfo *pKeyInfo;
  Mem *pData0;                    /* First in array of input registers */
  u8 *aRec;                       /* The constructed database key */
  int nRec;                       /* Size of aRec[] in bytes */
  int nField;                     /* Number of fields in encoded record */



  
  pC = p->apCsr[pOp->p1];
  pKeyInfo = pC->pKeyInfo;
  pData0 = &aMem[pOp->p2];
  pOut = &aMem[pOp->p3];
  aRec = 0;
















  memAboutToChange(p, pOut);

  nField = pKeyInfo->nField;
  if( pOp->p4type==P4_INT32 && pOp->p4.i ){
    nField = pOp->p4.i;
    assert( nField<=pKeyInfo->nField );
  }
  rc = sqlite4VdbeEncodeKey(
    db, pData0, nField, pC->iRoot, pKeyInfo, &aRec, &nRec, 0
  );

  if( rc ){
    sqlite4DbFree(db, aRec);
  }else{



    rc = sqlite4VdbeMemSetStr(pOut, aRec, nRec, 0, SQLITE_DYNAMIC);
    REGISTER_TRACE(pOp->p3, pOut);
    UPDATE_MAX_BLOBSIZE(pOut);
  }

  break;
}

................................................................................
  );
  if( rc==SQLITE_OK ) rc = sqlite4KVStoreOpenCursor(pCx->pTmpKV, &pCx->pKVCur);
  if( rc==SQLITE_OK ) rc = sqlite4KVStoreBegin(pCx->pTmpKV, 2);

  pCx->pKeyInfo = pOp->p4.pKeyInfo;
  if( pCx->pKeyInfo ) pCx->pKeyInfo->enc = ENC(p->db);
  pCx->isIndex = !pCx->isTable;


  break;
}

/* Opcode: OpenSorter P1 P2 * P4 *
**
** This opcode works like OP_OpenEphemeral except that it opens
** a transient index that is specifically designed to sort large
................................................................................
  assert( pC->iRoot>0 );
  assert( isSorter(pC) );
  pIn3 = &aMem[pOp->p3];
  rc = sqlite4VdbeSorterCompare(pC, pIn3, &res);
  if( res ){
    pc = pOp->p2-1;
  }





































  break;
};

/* Opcode: SorterData P1 P2 * * *
**
** Write into register P2 the current sorter data for sorter cursor P1.
*/







|









|
>

<
|
>
>








>
>
>






>
>
>
>
>
>
>
>
>
>
>
>
>
>
>









|





>
>
>
|







 







>
>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







2169
2170
2171
2172
2173
2174
2175
2176
2177
2178
2179
2180
2181
2182
2183
2184
2185
2186
2187
2188

2189
2190
2191
2192
2193
2194
2195
2196
2197
2198
2199
2200
2201
2202
2203
2204
2205
2206
2207
2208
2209
2210
2211
2212
2213
2214
2215
2216
2217
2218
2219
2220
2221
2222
2223
2224
2225
2226
2227
2228
2229
2230
2231
2232
2233
2234
2235
2236
2237
2238
2239
2240
2241
2242
2243
2244
2245
2246
2247
2248
2249
....
2735
2736
2737
2738
2739
2740
2741
2742
2743
2744
2745
2746
2747
2748
2749
2750
....
3422
3423
3424
3425
3426
3427
3428
3429
3430
3431
3432
3433
3434
3435
3436
3437
3438
3439
3440
3441
3442
3443
3444
3445
3446
3447
3448
3449
3450
3451
3452
3453
3454
3455
3456
3457
3458
3459
3460
3461
3462
3463
3464
3465
3466
3467
3468
3469
3470
3471
3472
    applyAffinity(pIn1, *(zAffinity++), encoding);
    REGISTER_TRACE(pIn1-aMem, pIn1);
  }

  break;
}

/* Opcode: MakeIdxKey P1 P2 P3 P4 P5
**
** P1 is an open cursor. P2 is the first register in a contiguous array
** of N registers containing values to encode into a database key. Normally,
** N is equal to the number of columns indexed by P1, plus the number of 
** trailing primary key columns (if any). 
**
** Or, if P4 is a non-zero integer, then it contains the value for N.
**
** This instruction encodes the N values into a database key and writes
** the result to register P3. No affinity transformations are applied to 
** the input values before they are encoded. 
**

** If P5 is non-zero, then a sequence number (unique within the cursor)
** is appended to the record. The sole purpose of this is to ensure that
** the key blob is unique within the cursors table.
*/
case OP_MakeIdxKey: {
  VdbeCursor *pC;
  KeyInfo *pKeyInfo;
  Mem *pData0;                    /* First in array of input registers */
  u8 *aRec;                       /* The constructed database key */
  int nRec;                       /* Size of aRec[] in bytes */
  int nField;                     /* Number of fields in encoded record */
  u8 aSeq[10];                    /* Encoded sequence number */
  int nSeq;                       /* Size of sequence number in bytes */
  u64 iSeq;                       /* Sequence number, if any */
  
  pC = p->apCsr[pOp->p1];
  pKeyInfo = pC->pKeyInfo;
  pData0 = &aMem[pOp->p2];
  pOut = &aMem[pOp->p3];
  aRec = 0;

  /* If pOp->p5 is non-zero, encode the sequence number blob to append to
  ** the end of the key. Variable nSeq is set to the number of bytes in
  ** the encoded key.
  */
  nSeq = 0;
  if( pOp->p5 ){
    iSeq = pC->seqCount++;
    do {
      nSeq++;
      aSeq[sizeof(aSeq)-nSeq] = (u8)(iSeq & 0x007F);
      iSeq = iSeq >> 7;
    }while( iSeq );
    aSeq[sizeof(aSeq)-nSeq] |= 0x80;
  }

  memAboutToChange(p, pOut);

  nField = pKeyInfo->nField;
  if( pOp->p4type==P4_INT32 && pOp->p4.i ){
    nField = pOp->p4.i;
    assert( nField<=pKeyInfo->nField );
  }
  rc = sqlite4VdbeEncodeKey(
    db, pData0, nField, pC->iRoot, pKeyInfo, &aRec, &nRec, nSeq
  );

  if( rc ){
    sqlite4DbFree(db, aRec);
  }else{
    if( nSeq ){
      memcpy(&aRec[nRec], &aSeq[sizeof(aSeq)-nSeq], nSeq);
    }
    rc = sqlite4VdbeMemSetStr(pOut, aRec, nRec+nSeq, 0, SQLITE_DYNAMIC);
    REGISTER_TRACE(pOp->p3, pOut);
    UPDATE_MAX_BLOBSIZE(pOut);
  }

  break;
}

................................................................................
  );
  if( rc==SQLITE_OK ) rc = sqlite4KVStoreOpenCursor(pCx->pTmpKV, &pCx->pKVCur);
  if( rc==SQLITE_OK ) rc = sqlite4KVStoreBegin(pCx->pTmpKV, 2);

  pCx->pKeyInfo = pOp->p4.pKeyInfo;
  if( pCx->pKeyInfo ) pCx->pKeyInfo->enc = ENC(p->db);
  pCx->isIndex = !pCx->isTable;

  pCx->isOrdered = 1;
  break;
}

/* Opcode: OpenSorter P1 P2 * P4 *
**
** This opcode works like OP_OpenEphemeral except that it opens
** a transient index that is specifically designed to sort large
................................................................................
  assert( pC->iRoot>0 );
  assert( isSorter(pC) );
  pIn3 = &aMem[pOp->p3];
  rc = sqlite4VdbeSorterCompare(pC, pIn3, &res);
  if( res ){
    pc = pOp->p2-1;
  }
  break;
};

/* Opcode: GrpCompare P1 P2 P3
**
** P1 is a cursor used to sort records. Its keys consist of the fields being
** sorted on encoded as an ordinary database key, followed by a sequence 
** number encoded as defined by the comments surrounding OP_MakeIdxKey. 
** Register P3 either contains NULL, or such a key truncated so as to 
** remove the sequence number.
**
** This opcode compares the current key of P1, less the sequence number, 
** with the contents of register P3. If they are identical, jump to 
** instruction P2. Otherwise, replace the contents of P3 with the current
** key of P1 (minus the sequence number) and fall through to the next
** instruction.
*/
case OP_GrpCompare: {
  VdbeCursor *pC;                 /* Cursor P1 */
  KVByteArray *aKey;              /* Key from cursor P1 */
  KVSize nKey;                    /* Size of aKey[] in bytes */

  pC = p->apCsr[pOp->p1];
  rc = sqlite4KVCursorKey(pC->pKVCur, &aKey, &nKey);
  if( rc==SQLITE_OK ){
    for(nKey--; (aKey[nKey] & 0x80)==0; nKey--);
  }

  pIn3 = &aMem[pOp->p3];
  if( (pIn3->flags & MEM_Blob) 
   && pIn3->n==nKey && 0==memcmp(pIn3->z, aKey, nKey) 
  ){
    pc = pOp->p2-1;
  }else{
    sqlite4VdbeMemSetStr(pIn3, (const char*)aKey, nKey, 0, SQLITE_TRANSIENT);
  }

  break;
};

/* Opcode: SorterData P1 P2 * * *
**
** Write into register P2 the current sorter data for sorter cursor P1.
*/

Changes to src/vdbecodec.c.

638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
...
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
  sqlite4 *db,                 /* The database connection */
  Mem *aIn,                    /* Values to be encoded */
  int nIn,                     /* Number of entries in aIn[] */
  int iTabno,                  /* The table this key applies to */
  KeyInfo *pKeyInfo,           /* Collating sequence and sort-order info */
  u8 **paOut,                  /* Write the resulting key here */
  int *pnOut,                  /* Number of bytes in the key */
  int bIncr                    /* See above */
){
  int i;
  int rc = SQLITE_OK;
  KeyEncoder x;
  u8 *so;
  int iShort;
  CollSeq **aColl;
................................................................................
  iShort = pKeyInfo->nField - pKeyInfo->nPK;
  aColl = pKeyInfo->aColl;
  so = pKeyInfo->aSortOrder;
  for(i=0; i<nIn && rc==SQLITE_OK; i++){
    rc = encodeOneKeyValue(&x, aIn+i, so ? so[i] : SQLITE_SO_ASC, aColl[i]);
  }

  if( rc==SQLITE_OK && bIncr ){ rc = enlargeEncoderAllocation(&x, 1); }
  if( rc ){
    sqlite4DbFree(db, x.aOut);
  }else{
    *paOut = x.aOut;
    *pnOut = x.nOut;
  }
  return rc;







|







 







|







638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
...
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
  sqlite4 *db,                 /* The database connection */
  Mem *aIn,                    /* Values to be encoded */
  int nIn,                     /* Number of entries in aIn[] */
  int iTabno,                  /* The table this key applies to */
  KeyInfo *pKeyInfo,           /* Collating sequence and sort-order info */
  u8 **paOut,                  /* Write the resulting key here */
  int *pnOut,                  /* Number of bytes in the key */
  int nExtra                   /* See above */
){
  int i;
  int rc = SQLITE_OK;
  KeyEncoder x;
  u8 *so;
  int iShort;
  CollSeq **aColl;
................................................................................
  iShort = pKeyInfo->nField - pKeyInfo->nPK;
  aColl = pKeyInfo->aColl;
  so = pKeyInfo->aSortOrder;
  for(i=0; i<nIn && rc==SQLITE_OK; i++){
    rc = encodeOneKeyValue(&x, aIn+i, so ? so[i] : SQLITE_SO_ASC, aColl[i]);
  }

  if( rc==SQLITE_OK && nExtra ){ rc = enlargeEncoderAllocation(&x, nExtra); }
  if( rc ){
    sqlite4DbFree(db, x.aOut);
  }else{
    *paOut = x.aOut;
    *pnOut = x.nOut;
  }
  return rc;

Changes to src/where.c.

617
618
619
620
621
622
623
624
625
626
627
628
629
630





631
632
633
634
635
636
637
          }
  
          /* Figure out the collation sequence used by expression pX. Store
          ** this in pColl. Also the collation sequence used by the index.
          ** Store this one in zColl.  */
          assert(pX->pLeft);
          pColl = sqlite4BinaryCompareCollSeq(pParse, pX->pLeft, pX->pRight);
          assert( pParse->nErr || (pColl && pColl->enc==pIdx->pSchema->enc)  );
          for(j=0; pIdx->aiColumn[j]!=iColumn && j<pIdx->nColumn; j++);
          if( j>=pIdx->nColumn ){
            zColl = pTab->aCol[iColumn].zColl;
          }else{
            zColl = pIdx->azColl[j];
          }






          /* If the collation sequence used by the index is not the same as
          ** that used by the expression, then this term is not a match.  */
          if( pColl!=sqlite4FindCollSeq(db, ENC(db), zColl, 0) ) continue;
        }
        return pTerm;
      }







<






>
>
>
>
>







617
618
619
620
621
622
623

624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
          }
  
          /* Figure out the collation sequence used by expression pX. Store
          ** this in pColl. Also the collation sequence used by the index.
          ** Store this one in zColl.  */
          assert(pX->pLeft);
          pColl = sqlite4BinaryCompareCollSeq(pParse, pX->pLeft, pX->pRight);

          for(j=0; pIdx->aiColumn[j]!=iColumn && j<pIdx->nColumn; j++);
          if( j>=pIdx->nColumn ){
            zColl = pTab->aCol[iColumn].zColl;
          }else{
            zColl = pIdx->azColl[j];
          }

          assert( pParse->nErr 
               || pIdx->pSchema==0 
               || pColl->enc==pIdx->pSchema->enc 
          );

          /* If the collation sequence used by the index is not the same as
          ** that used by the expression, then this term is not a match.  */
          if( pColl!=sqlite4FindCollSeq(db, ENC(db), zColl, 0) ) continue;
        }
        return pTerm;
      }

Changes to test/permutations.test.

130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
#   full
#
lappend ::testsuitelist xxx

test_suite "src4" -prefix "" -description {
} -files {
  simple.test fkey1.test conflict.test trigger2.test select1.test
  where.test
}

test_suite "veryquick" -prefix "" -description {
  "Very" quick test suite. Runs in less than 5 minutes on a workstation. 
  This test suite is the same as the "quick" tests, except that some files
  that test malloc and IO errors are omitted.
} -files [







|







130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
#   full
#
lappend ::testsuitelist xxx

test_suite "src4" -prefix "" -description {
} -files {
  simple.test fkey1.test conflict.test trigger2.test select1.test
  where.test select3.test select5.test select7.test select8.test
}

test_suite "veryquick" -prefix "" -description {
  "Very" quick test suite. Runs in less than 5 minutes on a workstation. 
  This test suite is the same as the "quick" tests, except that some files
  that test malloc and IO errors are omitted.
} -files [

Changes to test/select4.test.

457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
# Test distinctness of NULL in other ways.
#
do_test select4-6.7 {
  execsql {
    SELECT NULL EXCEPT SELECT NULL
  }
} {}


# Make sure column names are correct when a compound select appears as
# an expression in the WHERE clause.
#
do_test select4-7.1 {
  execsql {
    CREATE TABLE t2 AS SELECT log AS 'x', count(*) AS 'y' FROM t1 GROUP BY log;







<







457
458
459
460
461
462
463

464
465
466
467
468
469
470
# Test distinctness of NULL in other ways.
#
do_test select4-6.7 {
  execsql {
    SELECT NULL EXCEPT SELECT NULL
  }
} {}


# Make sure column names are correct when a compound select appears as
# an expression in the WHERE clause.
#
do_test select4-7.1 {
  execsql {
    CREATE TABLE t2 AS SELECT log AS 'x', count(*) AS 'y' FROM t1 GROUP BY log;

Changes to test/select5.test.

203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
  execsql {
    CREATE TABLE t8a(a,b);
    CREATE TABLE t8b(x);
    INSERT INTO t8a VALUES('one', 1);
    INSERT INTO t8a VALUES('one', 2);
    INSERT INTO t8a VALUES('two', 3);
    INSERT INTO t8a VALUES('one', NULL);
    INSERT INTO t8b(rowid,x) VALUES(1,111);
    INSERT INTO t8b(rowid,x) VALUES(2,222);
    INSERT INTO t8b(rowid,x) VALUES(3,333);
    SELECT a, count(b) FROM t8a, t8b WHERE b=t8b.rowid GROUP BY a ORDER BY a;
  }
} {one 2 two 1}
do_test select5-8.2 {
  execsql {
    SELECT a, count(b) FROM t8a, t8b WHERE b=+t8b.rowid GROUP BY a ORDER BY a;
  }







|
|
|







203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
  execsql {
    CREATE TABLE t8a(a,b);
    CREATE TABLE t8b(x);
    INSERT INTO t8a VALUES('one', 1);
    INSERT INTO t8a VALUES('one', 2);
    INSERT INTO t8a VALUES('two', 3);
    INSERT INTO t8a VALUES('one', NULL);
    INSERT INTO t8b(x) VALUES(111);
    INSERT INTO t8b(x) VALUES(222);
    INSERT INTO t8b(x) VALUES(333);
    SELECT a, count(b) FROM t8a, t8b WHERE b=t8b.rowid GROUP BY a ORDER BY a;
  }
} {one 2 two 1}
do_test select5-8.2 {
  execsql {
    SELECT a, count(b) FROM t8a, t8b WHERE b=+t8b.rowid GROUP BY a ORDER BY a;
  }

Changes to test/select7.test.

37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

# Nested views do not handle * properly.  Ticket #826.
#
ifcapable view {
do_test select7-2.1 {
  execsql {
    CREATE TABLE x(id integer primary key, a TEXT NULL);
    INSERT INTO x (a) VALUES ('first');
    CREATE TABLE tempx(id integer primary key, a TEXT NULL);
    INSERT INTO tempx (a) VALUES ('t-first');
    CREATE VIEW tv1 AS SELECT x.id, tx.id FROM x JOIN tempx tx ON tx.id=x.id;
    CREATE VIEW tv1b AS SELECT x.id, tx.id FROM x JOIN tempx tx on tx.id=x.id;
    CREATE VIEW tv2 AS SELECT * FROM tv1 UNION SELECT * FROM tv1b;
    SELECT * FROM tv2;
  }
} {1 1}
} ;# ifcapable view







|

|







37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

# Nested views do not handle * properly.  Ticket #826.
#
ifcapable view {
do_test select7-2.1 {
  execsql {
    CREATE TABLE x(id integer primary key, a TEXT NULL);
    INSERT INTO x (id, a) VALUES (1, 'first');
    CREATE TABLE tempx(id integer primary key, a TEXT NULL);
    INSERT INTO tempx (id, a) VALUES (1, 't-first');
    CREATE VIEW tv1 AS SELECT x.id, tx.id FROM x JOIN tempx tx ON tx.id=x.id;
    CREATE VIEW tv1b AS SELECT x.id, tx.id FROM x JOIN tempx tx on tx.id=x.id;
    CREATE VIEW tv2 AS SELECT * FROM tv1 UNION SELECT * FROM tv1b;
    SELECT * FROM tv2;
  }
} {1 1}
} ;# ifcapable view

Changes to test/simple.test.

11
12
13
14
15
16
17


18
19
20
21
22
23
24
...
783
784
785
786
787
788
789

















790
# The tests in this file were used while developing the SQLite 4 code. 
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix simple

#set sqlite_where_trace 1



do_execsql_test 1.0 { 
  PRAGMA table_info = sqlite_master
} {
    0 type text        0 {} 0 
    1 name text        0 {} 0 
    2 tbl_name text    0 {} 0 
................................................................................
    1 "SELECT * FROM t1 WHERE a = 7"        {7 seven}
    2 "SELECT * FROM t1 WHERE b = 'seven'"  {7 seven}
  } {
    do_execsql_test 42.$t.$u $sql $res
  }
}


















finish_test







>
>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
...
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
# The tests in this file were used while developing the SQLite 4 code. 
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix simple

#set sqlite_where_trace 1
#
if 1 {

do_execsql_test 1.0 { 
  PRAGMA table_info = sqlite_master
} {
    0 type text        0 {} 0 
    1 name text        0 {} 0 
    2 tbl_name text    0 {} 0 
................................................................................
    1 "SELECT * FROM t1 WHERE a = 7"        {7 seven}
    2 "SELECT * FROM t1 WHERE b = 'seven'"  {7 seven}
  } {
    do_execsql_test 42.$t.$u $sql $res
  }
}

}

#-------------------------------------------------------------------------
reset_db

do_execsql_test 43.1 {
  CREATE TABLE t1(a, b);
  INSERT INTO t1 VALUES('a', 1);
  INSERT INTO t1 VALUES('b', 4);
  INSERT INTO t1 VALUES('a', 2);
  INSERT INTO t1 VALUES('b', 5);
}

do_execsql_test 43.2 {
  SELECT a, sum(b) FROM t1 GROUP BY a;
} {a 3 b 9}

finish_test