/ Check-in [c449e04f]
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:First pass at optimizing max()/min() as described in #2853. Some refinements to come. (CVS 4687)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: c449e04f1870b1ff726c95c0bf1c6c6a22ca588a
User & Date: danielk1977 2008-01-05 17:39:30
Context
2008-01-05
18:44
Fix a memory leak introduced with #4687. (CVS 4688) check-in: 2b98b0fc user: danielk1977 tags: trunk
17:39
First pass at optimizing max()/min() as described in #2853. Some refinements to come. (CVS 4687) check-in: c449e04f user: danielk1977 tags: trunk
16:29
Registerify binary operators. Add register tracing to debugging output. (CVS 4686) check-in: 66396d2f user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/delete.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
**    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
** in order to generate code for DELETE FROM statements.
**
** $Id: delete.c,v 1.150 2008/01/05 05:20:10 drh Exp $
*/
#include "sqliteInt.h"

/*
** Look up every table that is named in pSrc.  If any table is not found,
** add an error message to pParse->zErrMsg and return NULL.  If all tables
** are found, return a pointer to the last table.
................................................................................
  ** the table and pick which records to delete.
  */
  else{
    int iRowid = ++pParse->nMem;    /* Used for storing rowid values. */

    /* Begin the database scan
    */
    pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0);
    if( pWInfo==0 ) goto delete_from_cleanup;

    /* Remember the rowid of every item to be deleted.
    */
    sqlite3VdbeAddOp2(v, IsVirtual(pTab) ? OP_VRowid : OP_Rowid, iCur, iRowid);
    sqlite3VdbeAddOp1(v, OP_FifoWrite, iRowid);
    if( db->flags & SQLITE_CountRows ){







|







 







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
**    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
** in order to generate code for DELETE FROM statements.
**
** $Id: delete.c,v 1.151 2008/01/05 17:39:30 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** Look up every table that is named in pSrc.  If any table is not found,
** add an error message to pParse->zErrMsg and return NULL.  If all tables
** are found, return a pointer to the last table.
................................................................................
  ** the table and pick which records to delete.
  */
  else{
    int iRowid = ++pParse->nMem;    /* Used for storing rowid values. */

    /* Begin the database scan
    */
    pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0);
    if( pWInfo==0 ) goto delete_from_cleanup;

    /* Remember the rowid of every item to be deleted.
    */
    sqlite3VdbeAddOp2(v, IsVirtual(pTab) ? OP_VRowid : OP_Rowid, iCur, iRowid);
    sqlite3VdbeAddOp1(v, OP_FifoWrite, iRowid);
    if( db->flags & SQLITE_CountRows ){

Changes to src/select.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
2620
2621
2622
2623
2624
2625
2626



























2627
2628
2629
2630
2631
2632
2633
....
2641
2642
2643
2644
2645
2646
2647

2648
2649
2650
2651
2652
2653
2654
....
2777
2778
2779
2780
2781
2782
2783

2784
2785
2786
2787
2788
2789
2790
....
3264
3265
3266
3267
3268
3269
3270

3271
3272
3273
3274

3275
3276
3277
3278
3279
3280
3281
....
3341
3342
3343
3344
3345
3346
3347
3348
3349
3350
3351
3352
3353
3354
3355
....
3497
3498
3499
3500
3501
3502
3503
3504
3505
3506
3507
3508
3509
3510
3511
....
3602
3603
3604
3605
3606
3607
3608












3609
3610
3611
3612
3613
3614
3615
3616




3617
3618
3619
3620
3621
3622
3623
3624


3625
3626
3627
3628
3629
3630
3631
**    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.386 2008/01/05 05:20:10 drh Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
  /* Finially, delete what is left of the subquery and return
  ** success.
  */
  sqlite3SelectDelete(pSub);
  return 1;
}
#endif /* SQLITE_OMIT_VIEW */




























/*
** Analyze the SELECT statement passed in as an argument to see if it
** is a simple min() or max() query.  If it is and this query can be
** satisfied using a single seek to the beginning or end of an index,
** then generate the code for this SELECT and return 1.  If this is not a 
** simple min() or max() query, then return 0;
................................................................................
** can be no GROUP BY or HAVING or WHERE clauses.  The result set must
** be the min() or max() of a single column of the table.  The column
** in the min() or max() function must be indexed.
**
** The parameters to this routine are the same as for sqlite3Select().
** See the header comment on that routine for additional information.
*/

static int simpleMinMaxQuery(Parse *pParse, Select *p, SelectDest *pDest){
  Expr *pExpr;
  int iCol;
  Table *pTab;
  Index *pIdx;
  int base;
  Vdbe *v;
................................................................................
  eList.a[0].pExpr = pExpr;
  selectInnerLoop(pParse, p, &eList, 0, 0, 0, -1, pDest, brk, brk, 0);
  sqlite3VdbeResolveLabel(v, brk);
  sqlite3VdbeAddOp2(v, OP_Close, base, 0);
  
  return 1;
}


/*
** This routine resolves any names used in the result set of the
** supplied SELECT statement. If the SELECT statement being resolved
** is a sub-select, then pOuterNC is a pointer to the NameContext 
** of the parent SELECT.
*/
................................................................................
    isDistinct = p->isDistinct;
  }
#endif

  /* Check for the special case of a min() or max() function by itself
  ** in the result set.
  */

  if( simpleMinMaxQuery(pParse, p, pDest) ){
    rc = 0;
    goto select_end;
  }


  /* Check to see if this is a subquery that can be "flattened" into its parent.
  ** If flattening is a possiblity, do so and return immediately.  
  */
#ifndef SQLITE_OMIT_VIEW
  if( pParent && pParentAgg &&
      flattenSubquery(db, pParent, parentTab, *pParentAgg, isAgg) ){
................................................................................
  }

  /* Aggregate and non-aggregate queries are handled differently */
  if( !isAgg && pGroupBy==0 ){
    /* This case is for non-aggregate queries
    ** Begin the database scan
    */
    pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pOrderBy);
    if( pWInfo==0 ) goto select_end;

    /* If sorting index that was created by a prior OP_OpenEphemeral 
    ** instruction ended up not being needed, then change the OP_OpenEphemeral
    ** into an OP_Noop.
    */
    if( addrSortIndex>=0 && pOrderBy==0 ){
................................................................................
      /* Begin a loop that will extract all source rows in GROUP BY order.
      ** This might involve two separate loops with an OP_Sort in between, or
      ** it might be a single loop that uses an index to extract information
      ** in the right order to begin with.
      */
      sqlite3VdbeResolveLabel(v, addrInitializeLoop);
      sqlite3VdbeAddOp2(v, OP_Gosub, 0, addrReset);
      pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pGroupBy);
      if( pWInfo==0 ) goto select_end;
      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;
................................................................................
      /* Output the final row of result
      */
      sqlite3VdbeAddOp2(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.
      */
      resetAccumulator(pParse, &sAggInfo);
      pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0);
      if( pWInfo==0 ) goto select_end;
      updateAccumulator(pParse, &sAggInfo);




      sqlite3WhereEnd(pWInfo);
      finalizeAggFunctions(pParse, &sAggInfo);
      pOrderBy = 0;
      if( pHaving ){
        sqlite3ExprIfFalse(pParse, pHaving, addrEnd, 1);
      }
      selectInnerLoop(pParse, p, p->pEList, 0, 0, 0, -1, 
                      pDest, addrEnd, addrEnd, aff);


    }
    sqlite3VdbeResolveLabel(v, addrEnd);
    
  } /* endif aggregate query */

  /* If there is an ORDER BY clause, then we need to sort the results
  ** and send them to the callback one by one.







|







 







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







 







>







 







>







 







>




>







 







|







 







|







 







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





|


>
>
>
>








>
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
2620
2621
2622
2623
2624
2625
2626
2627
2628
2629
2630
2631
2632
2633
2634
2635
2636
2637
2638
2639
2640
2641
2642
2643
2644
2645
2646
2647
2648
2649
2650
2651
2652
2653
2654
2655
2656
2657
2658
2659
2660
....
2668
2669
2670
2671
2672
2673
2674
2675
2676
2677
2678
2679
2680
2681
2682
....
2805
2806
2807
2808
2809
2810
2811
2812
2813
2814
2815
2816
2817
2818
2819
....
3293
3294
3295
3296
3297
3298
3299
3300
3301
3302
3303
3304
3305
3306
3307
3308
3309
3310
3311
3312
....
3372
3373
3374
3375
3376
3377
3378
3379
3380
3381
3382
3383
3384
3385
3386
....
3528
3529
3530
3531
3532
3533
3534
3535
3536
3537
3538
3539
3540
3541
3542
....
3633
3634
3635
3636
3637
3638
3639
3640
3641
3642
3643
3644
3645
3646
3647
3648
3649
3650
3651
3652
3653
3654
3655
3656
3657
3658
3659
3660
3661
3662
3663
3664
3665
3666
3667
3668
3669
3670
3671
3672
3673
3674
3675
3676
3677
3678
3679
3680
**    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.387 2008/01/05 17:39:30 danielk1977 Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
  /* Finially, delete what is left of the subquery and return
  ** success.
  */
  sqlite3SelectDelete(pSub);
  return 1;
}
#endif /* SQLITE_OMIT_VIEW */

/*
** Analyze the SELECT statement passed as an argument to see if it
** is a min() or max() query. Return ORDERBY_MIN or ORDERBY_MAX if 
** it is, or 0 otherwise. At present, a query is considered to be
** a min()/max() query if:
**
**   1. The result set contains exactly one element, either 
**      min(x) or max(x), where x is a column identifier.
*/
static int minMaxQuery(Parse *pParse, Select *p){
  Expr *pExpr;
  ExprList *pEList = p->pEList;

  if( pEList->nExpr!=1 ) return ORDERBY_NORMAL;
  pExpr = pEList->a[0].pExpr;
  pEList = pExpr->pList;
  if( pExpr->op!=TK_AGG_FUNCTION || pEList==0 || pEList->nExpr!=1 ) return 0;
  if( pEList->a[0].pExpr->op!=TK_AGG_COLUMN ) return ORDERBY_NORMAL;
  if( pExpr->token.n!=3 ) return ORDERBY_NORMAL;
  if( sqlite3StrNICmp((char*)pExpr->token.z,"min",3)==0 ){
    return ORDERBY_MIN;
  }else if( sqlite3StrNICmp((char*)pExpr->token.z,"max",3)==0 ){
    return ORDERBY_MAX;
  }
  return ORDERBY_NORMAL;
}

/*
** Analyze the SELECT statement passed in as an argument to see if it
** is a simple min() or max() query.  If it is and this query can be
** satisfied using a single seek to the beginning or end of an index,
** then generate the code for this SELECT and return 1.  If this is not a 
** simple min() or max() query, then return 0;
................................................................................
** can be no GROUP BY or HAVING or WHERE clauses.  The result set must
** be the min() or max() of a single column of the table.  The column
** in the min() or max() function must be indexed.
**
** The parameters to this routine are the same as for sqlite3Select().
** See the header comment on that routine for additional information.
*/
#if 0
static int simpleMinMaxQuery(Parse *pParse, Select *p, SelectDest *pDest){
  Expr *pExpr;
  int iCol;
  Table *pTab;
  Index *pIdx;
  int base;
  Vdbe *v;
................................................................................
  eList.a[0].pExpr = pExpr;
  selectInnerLoop(pParse, p, &eList, 0, 0, 0, -1, pDest, brk, brk, 0);
  sqlite3VdbeResolveLabel(v, brk);
  sqlite3VdbeAddOp2(v, OP_Close, base, 0);
  
  return 1;
}
#endif 

/*
** This routine resolves any names used in the result set of the
** supplied SELECT statement. If the SELECT statement being resolved
** is a sub-select, then pOuterNC is a pointer to the NameContext 
** of the parent SELECT.
*/
................................................................................
    isDistinct = p->isDistinct;
  }
#endif

  /* Check for the special case of a min() or max() function by itself
  ** in the result set.
  */
#if 0
  if( simpleMinMaxQuery(pParse, p, pDest) ){
    rc = 0;
    goto select_end;
  }
#endif

  /* Check to see if this is a subquery that can be "flattened" into its parent.
  ** If flattening is a possiblity, do so and return immediately.  
  */
#ifndef SQLITE_OMIT_VIEW
  if( pParent && pParentAgg &&
      flattenSubquery(db, pParent, parentTab, *pParentAgg, isAgg) ){
................................................................................
  }

  /* Aggregate and non-aggregate queries are handled differently */
  if( !isAgg && pGroupBy==0 ){
    /* This case is for non-aggregate queries
    ** Begin the database scan
    */
    pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pOrderBy, 0);
    if( pWInfo==0 ) goto select_end;

    /* If sorting index that was created by a prior OP_OpenEphemeral 
    ** instruction ended up not being needed, then change the OP_OpenEphemeral
    ** into an OP_Noop.
    */
    if( addrSortIndex>=0 && pOrderBy==0 ){
................................................................................
      /* Begin a loop that will extract all source rows in GROUP BY order.
      ** This might involve two separate loops with an OP_Sort in between, or
      ** it might be a single loop that uses an index to extract information
      ** in the right order to begin with.
      */
      sqlite3VdbeResolveLabel(v, addrInitializeLoop);
      sqlite3VdbeAddOp2(v, OP_Gosub, 0, addrReset);
      pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pGroupBy, 0);
      if( pWInfo==0 ) goto select_end;
      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;
................................................................................
      /* Output the final row of result
      */
      sqlite3VdbeAddOp2(v, OP_Gosub, 0, addrOutputRow);
      VdbeComment((v, "output final row"));
      
    } /* endif pGroupBy */
    else {
      ExprList *pMinMax = 0;
      u8 flag;

      flag = minMaxQuery(pParse, p);
      if( flag ){
        pMinMax = sqlite3ExprListDup(db, p->pEList->a[0].pExpr->pList);
        if( pMinMax ){
          pMinMax->a[0].sortOrder = ((flag==ORDERBY_MIN)?0:1);
          pMinMax->a[0].pExpr->op = TK_COLUMN;
        }
      }

      /* 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.
      */
      resetAccumulator(pParse, &sAggInfo);
      pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pMinMax, flag);
      if( pWInfo==0 ) goto select_end;
      updateAccumulator(pParse, &sAggInfo);
      if( !pMinMax && flag ){
        sqlite3VdbeAddOp2(v, OP_Goto, 0, pWInfo->iBreak);
        VdbeComment((v, "%s() by index", (flag==ORDERBY_MIN?"min":"max")));
      }
      sqlite3WhereEnd(pWInfo);
      finalizeAggFunctions(pParse, &sAggInfo);
      pOrderBy = 0;
      if( pHaving ){
        sqlite3ExprIfFalse(pParse, pHaving, addrEnd, 1);
      }
      selectInnerLoop(pParse, p, p->pEList, 0, 0, 0, -1, 
                      pDest, addrEnd, addrEnd, aff);

      sqlite3ExprListDelete(pMinMax);
    }
    sqlite3VdbeResolveLabel(v, addrEnd);
    
  } /* endif aggregate query */

  /* If there is an ORDER BY clause, then we need to sort the results
  ** and send them to the callback one by one.

Changes to src/sqliteInt.h.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
....
1225
1226
1227
1228
1229
1230
1231




1232
1233
1234
1235
1236
1237
1238
....
1731
1732
1733
1734
1735
1736
1737
1738
1739
1740
1741
1742
1743
1744
1745
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.638 2008/01/04 19:10:29 danielk1977 Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** The macro unlikely() is a hint that surrounds a boolean
** expression that is usually false.  Macro likely() surrounds
................................................................................
  /* The following field is really not part of the current level.  But
  ** we need a place to cache index information for each table in the
  ** FROM clause and the WhereLevel structure is a convenient place.
  */
  sqlite3_index_info *pIdxInfo;  /* Index info for n-th source table */
};





/*
** The WHERE clause processing routine has two halves.  The
** first part does the start of the WHERE loop and the second
** half does the tail of the WHERE loop.  An instance of
** this structure is returned by the first half and passed
** into the second half to give some continuity.
*/
................................................................................
void sqlite3SelectDelete(Select*);
int sqlite3SelectMask(Parse *, Select *, u32);
Table *sqlite3SrcListLookup(Parse*, SrcList*);
int sqlite3IsReadOnly(Parse*, Table*, int);
void sqlite3OpenTable(Parse*, int iCur, int iDb, Table*, int);
void sqlite3DeleteFrom(Parse*, SrcList*, Expr*);
void sqlite3Update(Parse*, SrcList*, ExprList*, Expr*, int);
WhereInfo *sqlite3WhereBegin(Parse*, SrcList*, Expr*, ExprList**);
void sqlite3WhereEnd(WhereInfo*);
void sqlite3ExprCodeGetColumn(Vdbe*, Table*, int, int, int);
int sqlite3ExprCode(Parse*, Expr*, int);
void sqlite3ExprCodeAndCache(Parse*, Expr*);
int sqlite3ExprCodeExprList(Parse*, ExprList*, int);
void sqlite3ExprIfTrue(Parse*, Expr*, int, int);
void sqlite3ExprIfFalse(Parse*, Expr*, int, int);







|







 







>
>
>
>







 







|







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
....
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
....
1735
1736
1737
1738
1739
1740
1741
1742
1743
1744
1745
1746
1747
1748
1749
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.639 2008/01/05 17:39:30 danielk1977 Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** The macro unlikely() is a hint that surrounds a boolean
** expression that is usually false.  Macro likely() surrounds
................................................................................
  /* The following field is really not part of the current level.  But
  ** we need a place to cache index information for each table in the
  ** FROM clause and the WhereLevel structure is a convenient place.
  */
  sqlite3_index_info *pIdxInfo;  /* Index info for n-th source table */
};

#define ORDERBY_NORMAL 0
#define ORDERBY_MIN    1
#define ORDERBY_MAX    2

/*
** The WHERE clause processing routine has two halves.  The
** first part does the start of the WHERE loop and the second
** half does the tail of the WHERE loop.  An instance of
** this structure is returned by the first half and passed
** into the second half to give some continuity.
*/
................................................................................
void sqlite3SelectDelete(Select*);
int sqlite3SelectMask(Parse *, Select *, u32);
Table *sqlite3SrcListLookup(Parse*, SrcList*);
int sqlite3IsReadOnly(Parse*, Table*, int);
void sqlite3OpenTable(Parse*, int iCur, int iDb, Table*, int);
void sqlite3DeleteFrom(Parse*, SrcList*, Expr*);
void sqlite3Update(Parse*, SrcList*, ExprList*, Expr*, int);
WhereInfo *sqlite3WhereBegin(Parse*, SrcList*, Expr*, ExprList**, u8);
void sqlite3WhereEnd(WhereInfo*);
void sqlite3ExprCodeGetColumn(Vdbe*, Table*, int, int, int);
int sqlite3ExprCode(Parse*, Expr*, int);
void sqlite3ExprCodeAndCache(Parse*, Expr*);
int sqlite3ExprCodeExprList(Parse*, ExprList*, int);
void sqlite3ExprIfTrue(Parse*, Expr*, int, int);
void sqlite3ExprIfFalse(Parse*, Expr*, int, int);

Changes to src/update.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
**    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 UPDATE statements.
**
** $Id: update.c,v 1.160 2008/01/05 05:20:10 drh Exp $
*/
#include "sqliteInt.h"

#ifndef SQLITE_OMIT_VIRTUALTABLE
/* Forward declaration */
static void updateVirtualTable(
  Parse *pParse,       /* The parsing context */
................................................................................
    dest.iParm = iCur;
    sqlite3Select(pParse, pView, &dest, 0, 0, 0, 0);
    sqlite3SelectDelete(pView);
  }

  /* Begin the database scan
  */
  pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0);
  if( pWInfo==0 ) goto update_cleanup;

  /* Remember the rowid of every item to be updated.
  */
  iRowid = ++pParse->nMem;
  sqlite3VdbeAddOp2(v, IsVirtual(pTab) ? OP_VRowid : OP_Rowid, iCur, iRowid);
  sqlite3VdbeAddOp2(v, OP_FifoWrite, iRowid, 0);







|







 







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
**    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 UPDATE statements.
**
** $Id: update.c,v 1.161 2008/01/05 17:39:30 danielk1977 Exp $
*/
#include "sqliteInt.h"

#ifndef SQLITE_OMIT_VIRTUALTABLE
/* Forward declaration */
static void updateVirtualTable(
  Parse *pParse,       /* The parsing context */
................................................................................
    dest.iParm = iCur;
    sqlite3Select(pParse, pView, &dest, 0, 0, 0, 0);
    sqlite3SelectDelete(pView);
  }

  /* Begin the database scan
  */
  pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0);
  if( pWInfo==0 ) goto update_cleanup;

  /* Remember the rowid of every item to be updated.
  */
  iRowid = ++pParse->nMem;
  sqlite3VdbeAddOp2(v, IsVirtual(pTab) ? OP_VRowid : OP_Rowid, iCur, iRowid);
  sqlite3VdbeAddOp2(v, OP_FifoWrite, iRowid, 0);

Changes to src/where.c.

12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
....
1965
1966
1967
1968
1969
1970
1971
1972

1973
1974
1975
1976
1977
1978
1979
....
1980
1981
1982
1983
1984
1985
1986

1987
1988
1989
1990
1991
1992
1993
1994




1995
1996
1997
1998
1999
2000
2001
....
2396
2397
2398
2399
2400
2401
2402

2403
2404
2405
2406
2407
2408
2409
....
2423
2424
2425
2426
2427
2428
2429
















2430
2431
2432
2433
2434
2435
2436
....
2448
2449
2450
2451
2452
2453
2454
2455
2456
2457





2458
2459
2460
2461
2462
2463
2464
2465
2466
2467
2468
2469
2470
2471
2472
2473
2474
2475
....
2485
2486
2487
2488
2489
2490
2491
2492
2493





2494
2495
2496
2497
2498
2499
2500
....
2534
2535
2536
2537
2538
2539
2540

2541
2542
2543
2544
2545
2546
2547
















2548
2549
2550
2551
2552

2553
2554
2555
2556
2557
2558
2559
2560
2561
2562
2563
2564
2565
2566
2567
2568
2569
2570
2571
2572
2573
2574
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is reponsible for
** generating the code that loops through a table looking for applicable
** rows.  Indices are selected and used to speed the search when doing
** so is applicable.  Because this module is responsible for selecting
** indices, you might also think of this module as the "query optimizer".
**
** $Id: where.c,v 1.275 2008/01/05 05:38:21 drh Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8)
................................................................................
** If the where clause loops cannot be arranged to provide the correct
** output order, then the *ppOrderBy is unchanged.
*/
WhereInfo *sqlite3WhereBegin(
  Parse *pParse,        /* The parser context */
  SrcList *pTabList,    /* A list of all tables to be scanned */
  Expr *pWhere,         /* The WHERE clause */
  ExprList **ppOrderBy  /* An ORDER BY clause, or NULL */

){
  int i;                     /* Loop counter */
  WhereInfo *pWInfo;         /* Will become the return value of this function */
  Vdbe *v = pParse->pVdbe;   /* The virtual database engine */
  int brk, cont = 0;         /* Addresses used during code generation */
  Bitmask notReady;          /* Cursors that are not yet positioned */
  WhereTerm *pTerm;          /* A single term in the WHERE clause */
................................................................................
  ExprMaskSet maskSet;       /* The expression mask set */
  WhereClause wc;            /* The WHERE clause is divided into these terms */
  struct SrcList_item *pTabItem;  /* A single entry from pTabList */
  WhereLevel *pLevel;             /* A single level in the pWInfo list */
  int iFrom;                      /* First unused FROM clause element */
  int andFlags;              /* AND-ed combination of all wc.a[].flags */
  sqlite3 *db;               /* Database connection */


  /* The number of tables in the FROM clause is limited by the number of
  ** bits in a Bitmask 
  */
  if( pTabList->nSrc>BMS ){
    sqlite3ErrorMsg(pParse, "at most %d tables in a join", BMS);
    return 0;
  }





  /* Split the WHERE clause into separate subexpressions where each
  ** subexpression is separated by an AND operator.
  */
  initMaskSet(&maskSet);
  whereClauseInit(&wc, pParse, &maskSet);
  whereSplit(&wc, pWhere, TK_AND);
................................................................................
      int nEq = pLevel->nEq;
      int topEq=0;        /* True if top limit uses ==. False is strictly < */
      int btmEq=0;        /* True if btm limit uses ==. False if strictly > */
      int topOp, btmOp;   /* Operators for the top and bottom search bounds */
      int testOp;
      int topLimit = (pLevel->flags & WHERE_TOP_LIMIT)!=0;
      int btmLimit = (pLevel->flags & WHERE_BTM_LIMIT)!=0;


      /* Generate code to evaluate all constraint terms using == or IN
      ** and level the values of those terms on the stack.
      */
      codeAllEqualityTerms(pParse, pLevel, &wc, notReady);

      /* Duplicate the equality term values because they will all be
................................................................................
        topOp = WO_LT|WO_LE;
        btmOp = WO_GT|WO_GE;
      }else{
        topOp = WO_GT|WO_GE;
        btmOp = WO_LT|WO_LE;
        SWAP(int, topLimit, btmLimit);
      }

















      /* Generate the termination key.  This is the key value that
      ** will end the search.  There is no termination key if there
      ** are no equality terms and no "X<..." term.
      **
      ** 2002-Dec-04: On a reverse-order scan, the so-called "termination"
      ** key computed here really ends up being the start key.
................................................................................
        topEq = pTerm->eOperator & (WO_LE|WO_GE);
        disableTerm(pLevel, pTerm);
        testOp = OP_IdxGE;
      }else{
        testOp = nEq>0 ? OP_IdxGE : OP_Noop;
        topEq = 1;
      }
      if( testOp!=OP_Noop ){
        int nCol = nEq + topLimit;
        pLevel->iMem = ++pParse->nMem;





        buildIndexProbe(v, nCol, pIdx);
        if( bRev ){
          int op = topEq ? OP_MoveLe : OP_MoveLt;
          sqlite3VdbeAddOp2(v, op, iIdxCur, nxt);
        }else{
          sqlite3VdbeAddOp2(v, OP_Move, 0, pLevel->iMem);
        }
      }else if( bRev ){
        sqlite3VdbeAddOp2(v, OP_Last, iIdxCur, brk);
      }

      /* Generate the start key.  This is the key that defines the lower
      ** bound on the search.  There is no start key if there are no
      ** equality terms and if there is no "X>..." term.  In
      ** that case, generate a "Rewind" instruction in place of the
      ** start key search.
      **
      ** 2002-Dec-04: In the case of a reverse-order search, the so-called
................................................................................
        sqlite3ExprCode(pParse, pX->pRight, 0);
        sqlite3VdbeAddOp2(v, OP_IsNull, -(nEq+1), nxt);
        btmEq = pTerm->eOperator & (WO_LE|WO_GE);
        disableTerm(pLevel, pTerm);
      }else{
        btmEq = 1;
      }
      if( nEq>0 || btmLimit ){
        int nCol = nEq + btmLimit;





        buildIndexProbe(v, nCol, pIdx);
        if( bRev ){
          pLevel->iMem = ++pParse->nMem;
          sqlite3VdbeAddOp2(v, OP_Move, 0, pLevel->iMem);
          testOp = OP_IdxLT;
        }else{
          int op = btmEq ? OP_MoveGe : OP_MoveGt;
................................................................................
      pLevel->p2 = start;
    }else if( pLevel->flags & WHERE_COLUMN_EQ ){
      /* Case 4:  There is an index and all terms of the WHERE clause that
      **          refer to the index using the "==" or "IN" operators.
      */
      int start;
      int nEq = pLevel->nEq;


      /* Generate code to evaluate all constraint terms using == or IN
      ** and leave the values of those terms on the stack.
      */
      codeAllEqualityTerms(pParse, pLevel, &wc, notReady);
      nxt = pLevel->nxt;

















      /* Generate a single key that will be used to both start and terminate
      ** the search
      */
      buildIndexProbe(v, nEq, pIdx);
      sqlite3VdbeAddOp2(v, OP_Copy, 0, pLevel->iMem);


      /* Generate code (1) to move to the first matching element of the table.
      ** Then generate code (2) that jumps to "nxt" after the cursor is past
      ** the last matching element of the table.  The code (1) is executed
      ** once to initialize the search, the code (2) is executed before each
      ** iteration of the scan to see if the scan has finished. */
      if( bRev ){
        /* Scan in reverse order */
        sqlite3VdbeAddOp2(v, OP_MoveLe, iIdxCur, nxt);
        start = sqlite3VdbeAddOp2(v, OP_SCopy, pLevel->iMem, 0);
        sqlite3VdbeAddOp2(v, OP_IdxLT, iIdxCur, nxt);
        pLevel->op = OP_Prev;
      }else{
        /* Scan in the forward order */
        sqlite3VdbeAddOp2(v, OP_MoveGe, iIdxCur, nxt);
        start = sqlite3VdbeAddOp2(v, OP_SCopy, pLevel->iMem, 0);
        sqlite3VdbeAddOp4(v, OP_IdxGE, iIdxCur, nxt, 0, "+", P4_STATIC);
        pLevel->op = OP_Next;
      }
      if( !omitTable ){
        sqlite3VdbeAddOp2(v, OP_IdxRowid, iIdxCur, 0);
        sqlite3VdbeAddOp2(v, OP_MoveGe, iCur, 0);







|







 







|
>







 







>








>
>
>
>







 







>







 







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







 







|


>
>
>
>
>










|







 







|

>
>
>
>
>







 







>







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
|
|
|
|
>








|





|







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
....
1965
1966
1967
1968
1969
1970
1971
1972
1973
1974
1975
1976
1977
1978
1979
1980
....
1981
1982
1983
1984
1985
1986
1987
1988
1989
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999
2000
2001
2002
2003
2004
2005
2006
2007
....
2402
2403
2404
2405
2406
2407
2408
2409
2410
2411
2412
2413
2414
2415
2416
....
2430
2431
2432
2433
2434
2435
2436
2437
2438
2439
2440
2441
2442
2443
2444
2445
2446
2447
2448
2449
2450
2451
2452
2453
2454
2455
2456
2457
2458
2459
....
2471
2472
2473
2474
2475
2476
2477
2478
2479
2480
2481
2482
2483
2484
2485
2486
2487
2488
2489
2490
2491
2492
2493
2494
2495
2496
2497
2498
2499
2500
2501
2502
2503
....
2513
2514
2515
2516
2517
2518
2519
2520
2521
2522
2523
2524
2525
2526
2527
2528
2529
2530
2531
2532
2533
....
2567
2568
2569
2570
2571
2572
2573
2574
2575
2576
2577
2578
2579
2580
2581
2582
2583
2584
2585
2586
2587
2588
2589
2590
2591
2592
2593
2594
2595
2596
2597
2598
2599
2600
2601
2602
2603
2604
2605
2606
2607
2608
2609
2610
2611
2612
2613
2614
2615
2616
2617
2618
2619
2620
2621
2622
2623
2624
2625
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is reponsible for
** generating the code that loops through a table looking for applicable
** rows.  Indices are selected and used to speed the search when doing
** so is applicable.  Because this module is responsible for selecting
** indices, you might also think of this module as the "query optimizer".
**
** $Id: where.c,v 1.276 2008/01/05 17:39:30 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8)
................................................................................
** If the where clause loops cannot be arranged to provide the correct
** output order, then the *ppOrderBy is unchanged.
*/
WhereInfo *sqlite3WhereBegin(
  Parse *pParse,        /* The parser context */
  SrcList *pTabList,    /* A list of all tables to be scanned */
  Expr *pWhere,         /* The WHERE clause */
  ExprList **ppOrderBy, /* An ORDER BY clause, or NULL */
  u8 obflag             /* One of ORDERBY_MIN, ORDERBY_MAX or ORDERBY_NORMAL */
){
  int i;                     /* Loop counter */
  WhereInfo *pWInfo;         /* Will become the return value of this function */
  Vdbe *v = pParse->pVdbe;   /* The virtual database engine */
  int brk, cont = 0;         /* Addresses used during code generation */
  Bitmask notReady;          /* Cursors that are not yet positioned */
  WhereTerm *pTerm;          /* A single term in the WHERE clause */
................................................................................
  ExprMaskSet maskSet;       /* The expression mask set */
  WhereClause wc;            /* The WHERE clause is divided into these terms */
  struct SrcList_item *pTabItem;  /* A single entry from pTabList */
  WhereLevel *pLevel;             /* A single level in the pWInfo list */
  int iFrom;                      /* First unused FROM clause element */
  int andFlags;              /* AND-ed combination of all wc.a[].flags */
  sqlite3 *db;               /* Database connection */
  ExprList *pOrderBy = 0;

  /* The number of tables in the FROM clause is limited by the number of
  ** bits in a Bitmask 
  */
  if( pTabList->nSrc>BMS ){
    sqlite3ErrorMsg(pParse, "at most %d tables in a join", BMS);
    return 0;
  }

  if( ppOrderBy ){
    pOrderBy = *ppOrderBy;
  }

  /* Split the WHERE clause into separate subexpressions where each
  ** subexpression is separated by an AND operator.
  */
  initMaskSet(&maskSet);
  whereClauseInit(&wc, pParse, &maskSet);
  whereSplit(&wc, pWhere, TK_AND);
................................................................................
      int nEq = pLevel->nEq;
      int topEq=0;        /* True if top limit uses ==. False is strictly < */
      int btmEq=0;        /* True if btm limit uses ==. False if strictly > */
      int topOp, btmOp;   /* Operators for the top and bottom search bounds */
      int testOp;
      int topLimit = (pLevel->flags & WHERE_TOP_LIMIT)!=0;
      int btmLimit = (pLevel->flags & WHERE_BTM_LIMIT)!=0;
      int isMinQuery = 0;      /* If this is an optimized SELECT min(x) ... */

      /* Generate code to evaluate all constraint terms using == or IN
      ** and level the values of those terms on the stack.
      */
      codeAllEqualityTerms(pParse, pLevel, &wc, notReady);

      /* Duplicate the equality term values because they will all be
................................................................................
        topOp = WO_LT|WO_LE;
        btmOp = WO_GT|WO_GE;
      }else{
        topOp = WO_GT|WO_GE;
        btmOp = WO_LT|WO_LE;
        SWAP(int, topLimit, btmLimit);
      }

      /* If this loop satisfies a sort order (pOrderBy) request that 
      ** was passed to this function to implement a "SELECT min(x) ..." 
      ** query, then the caller will only allow the loop to run for
      ** a single iteration. This means that the first row returned
      ** should not have a NULL value stored in 'x'. If column 'x' is
      ** the first one after the nEq equality constraints in the index,
      ** this requires some special handling.
      */
      if( (obflag==ORDERBY_MIN)
       && (pLevel->flags&WHERE_ORDERBY)
       && (pIdx->nColumn>nEq)
       && (pOrderBy->a[0].pExpr->iColumn==pIdx->aiColumn[nEq])
      ){
        isMinQuery = 1;
      }

      /* Generate the termination key.  This is the key value that
      ** will end the search.  There is no termination key if there
      ** are no equality terms and no "X<..." term.
      **
      ** 2002-Dec-04: On a reverse-order scan, the so-called "termination"
      ** key computed here really ends up being the start key.
................................................................................
        topEq = pTerm->eOperator & (WO_LE|WO_GE);
        disableTerm(pLevel, pTerm);
        testOp = OP_IdxGE;
      }else{
        testOp = nEq>0 ? OP_IdxGE : OP_Noop;
        topEq = 1;
      }
      if( testOp!=OP_Noop || (isMinQuery&&bRev) ){
        int nCol = nEq + topLimit;
        pLevel->iMem = ++pParse->nMem;
        if( isMinQuery && !topLimit ){
          nCol++;
          sqlite3VdbeAddOp2(v, OP_Null, 0, 0);
          topEq = 0;
        }
        buildIndexProbe(v, nCol, pIdx);
        if( bRev ){
          int op = topEq ? OP_MoveLe : OP_MoveLt;
          sqlite3VdbeAddOp2(v, op, iIdxCur, nxt);
        }else{
          sqlite3VdbeAddOp2(v, OP_Move, 0, pLevel->iMem);
        }
      }else if( bRev ){
        sqlite3VdbeAddOp2(v, OP_Last, iIdxCur, brk);
      }
   
      /* Generate the start key.  This is the key that defines the lower
      ** bound on the search.  There is no start key if there are no
      ** equality terms and if there is no "X>..." term.  In
      ** that case, generate a "Rewind" instruction in place of the
      ** start key search.
      **
      ** 2002-Dec-04: In the case of a reverse-order search, the so-called
................................................................................
        sqlite3ExprCode(pParse, pX->pRight, 0);
        sqlite3VdbeAddOp2(v, OP_IsNull, -(nEq+1), nxt);
        btmEq = pTerm->eOperator & (WO_LE|WO_GE);
        disableTerm(pLevel, pTerm);
      }else{
        btmEq = 1;
      }
      if( nEq>0 || btmLimit || (isMinQuery&&!bRev) ){
        int nCol = nEq + btmLimit;
        if( isMinQuery && !btmLimit ){
          nCol++;
          sqlite3VdbeAddOp2(v, OP_Null, 0, 0);
          btmEq = 0;
        }
        buildIndexProbe(v, nCol, pIdx);
        if( bRev ){
          pLevel->iMem = ++pParse->nMem;
          sqlite3VdbeAddOp2(v, OP_Move, 0, pLevel->iMem);
          testOp = OP_IdxLT;
        }else{
          int op = btmEq ? OP_MoveGe : OP_MoveGt;
................................................................................
      pLevel->p2 = start;
    }else if( pLevel->flags & WHERE_COLUMN_EQ ){
      /* Case 4:  There is an index and all terms of the WHERE clause that
      **          refer to the index using the "==" or "IN" operators.
      */
      int start;
      int nEq = pLevel->nEq;
      int isMinQuery = 0;      /* If this is an optimized SELECT min(x) ... */

      /* Generate code to evaluate all constraint terms using == or IN
      ** and leave the values of those terms on the stack.
      */
      codeAllEqualityTerms(pParse, pLevel, &wc, notReady);
      nxt = pLevel->nxt;

      if( (obflag==ORDERBY_MIN)
       && (pLevel->flags&WHERE_ORDERBY) 
       && (pIdx->nColumn>nEq)
       && (pOrderBy->a[0].pExpr->iColumn==pIdx->aiColumn[nEq])
      ){
        int h;
        isMinQuery = 1;
        for(h=0; h<nEq; h++){
          sqlite3VdbeAddOp1(v, OP_Copy, 1-nEq);
        }
        buildIndexProbe(v, nEq, pIdx);
        sqlite3VdbeAddOp2(v, OP_Copy, 0, pLevel->iMem);
        sqlite3VdbeAddOp2(v, OP_Pop, 1, 0);
        sqlite3VdbeAddOp2(v, OP_Null, 0, 0);
        buildIndexProbe(v, nEq+1, pIdx);
      }else{
        /* Generate a single key that will be used to both start and 
        ** terminate the search
        */
        buildIndexProbe(v, nEq, pIdx);
        sqlite3VdbeAddOp2(v, OP_Copy, 0, pLevel->iMem);
      }

      /* Generate code (1) to move to the first matching element of the table.
      ** Then generate code (2) that jumps to "nxt" after the cursor is past
      ** the last matching element of the table.  The code (1) is executed
      ** once to initialize the search, the code (2) is executed before each
      ** iteration of the scan to see if the scan has finished. */
      if( bRev ){
        /* Scan in reverse order */
        sqlite3VdbeAddOp2(v, (isMinQuery?OP_MoveLt:OP_MoveLe), iIdxCur, nxt);
        start = sqlite3VdbeAddOp2(v, OP_SCopy, pLevel->iMem, 0);
        sqlite3VdbeAddOp2(v, OP_IdxLT, iIdxCur, nxt);
        pLevel->op = OP_Prev;
      }else{
        /* Scan in the forward order */
        sqlite3VdbeAddOp2(v, (isMinQuery?OP_MoveGt:OP_MoveGe), iIdxCur, nxt);
        start = sqlite3VdbeAddOp2(v, OP_SCopy, pLevel->iMem, 0);
        sqlite3VdbeAddOp4(v, OP_IdxGE, iIdxCur, nxt, 0, "+", P4_STATIC);
        pLevel->op = OP_Next;
      }
      if( !omitTable ){
        sqlite3VdbeAddOp2(v, OP_IdxRowid, iIdxCur, 0);
        sqlite3VdbeAddOp2(v, OP_MoveGe, iCur, 0);

Changes to test/collate4.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is page cache subsystem.
#
# $Id: collate4.test,v 1.8 2005/04/01 10:47:40 drh Exp $

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

db collate TEXT text_collate
proc text_collate {a b} {
  return [string compare $a $b]
................................................................................
  # Test that the index with collation type TEXT is used.
  execsql {
    CREATE INDEX collate4i1 ON collate4t1(a);
  }
  count {
    SELECT min(a) FROM collate4t1;
  }
} {10 2}
do_test collate4-4.4 {
  count {
    SELECT max(a) FROM collate4t1;
  }
} {20 1}
do_test collate4-4.5 {
  # Test that the index with collation type NUMERIC is not used.
  execsql {
    DROP INDEX collate4i1;
    CREATE INDEX collate4i1 ON collate4t1(a COLLATE NUMERIC);
  }
  count {







|







 







|




|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is page cache subsystem.
#
# $Id: collate4.test,v 1.9 2008/01/05 17:39:30 danielk1977 Exp $

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

db collate TEXT text_collate
proc text_collate {a b} {
  return [string compare $a $b]
................................................................................
  # Test that the index with collation type TEXT is used.
  execsql {
    CREATE INDEX collate4i1 ON collate4t1(a);
  }
  count {
    SELECT min(a) FROM collate4t1;
  }
} {10 1}
do_test collate4-4.4 {
  count {
    SELECT max(a) FROM collate4t1;
  }
} {20 0}
do_test collate4-4.5 {
  # Test that the index with collation type NUMERIC is not used.
  execsql {
    DROP INDEX collate4i1;
    CREATE INDEX collate4i1 ON collate4t1(a COLLATE NUMERIC);
  }
  count {

Changes to test/minmax.test.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
..
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing SELECT statements that contain
# aggregate min() and max() functions and which are handled as
# as a special case.
#
# $Id: minmax.test,v 1.19 2006/03/26 01:21:23 drh Exp $

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

do_test minmax-1.0 {
  execsql {
    BEGIN;
................................................................................
do_test minmax-1.5 {
  execsql {CREATE INDEX t1i1 ON t1(x)}
  set sqlite_search_count 0
  execsql {SELECT min(x) FROM t1}
} {1}
do_test minmax-1.6 {
  set sqlite_search_count
} {2}
do_test minmax-1.7 {
  set sqlite_search_count 0
  execsql {SELECT max(x) FROM t1}
} {20}
do_test minmax-1.8 {
  set sqlite_search_count
} {1}
do_test minmax-1.9 {
  set sqlite_search_count 0
  execsql {SELECT max(y) FROM t1}
} {5}
do_test minmax-1.10 {
  set sqlite_search_count
} {19}







|







 







|






|







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
..
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing SELECT statements that contain
# aggregate min() and max() functions and which are handled as
# as a special case.
#
# $Id: minmax.test,v 1.20 2008/01/05 17:39:30 danielk1977 Exp $

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

do_test minmax-1.0 {
  execsql {
    BEGIN;
................................................................................
do_test minmax-1.5 {
  execsql {CREATE INDEX t1i1 ON t1(x)}
  set sqlite_search_count 0
  execsql {SELECT min(x) FROM t1}
} {1}
do_test minmax-1.6 {
  set sqlite_search_count
} {1}
do_test minmax-1.7 {
  set sqlite_search_count 0
  execsql {SELECT max(x) FROM t1}
} {20}
do_test minmax-1.8 {
  set sqlite_search_count
} {0}
do_test minmax-1.9 {
  set sqlite_search_count 0
  execsql {SELECT max(y) FROM t1}
} {5}
do_test minmax-1.10 {
  set sqlite_search_count
} {19}

Changes to test/minmax2.test.

11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
..
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
# This file implements regression tests for SQLite library.  The
# focus of this file is testing SELECT statements that contain
# aggregate min() and max() functions and which are handled as
# as a special case.  This file makes sure that the min/max
# optimization works right in the presence of descending
# indices.  Ticket #2514.
#
# $Id: minmax2.test,v 1.1 2007/07/18 18:17:12 drh Exp $

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

do_test minmax2-1.0 {
  execsql {
    PRAGMA legacy_file_format=0;
................................................................................
do_test minmax2-1.5 {
  execsql {CREATE INDEX t1i1 ON t1(x DESC)}
  set sqlite_search_count 0
  execsql {SELECT min(x) FROM t1}
} {1}
do_test minmax2-1.6 {
  set sqlite_search_count
} {2}
do_test minmax2-1.7 {
  set sqlite_search_count 0
  execsql {SELECT max(x) FROM t1}
} {20}
do_test minmax2-1.8 {
  set sqlite_search_count
} {1}
do_test minmax2-1.9 {
  set sqlite_search_count 0
  execsql {SELECT max(y) FROM t1}
} {5}
do_test minmax2-1.10 {
  set sqlite_search_count
} {19}







|







 







|






|







11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
..
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
# This file implements regression tests for SQLite library.  The
# focus of this file is testing SELECT statements that contain
# aggregate min() and max() functions and which are handled as
# as a special case.  This file makes sure that the min/max
# optimization works right in the presence of descending
# indices.  Ticket #2514.
#
# $Id: minmax2.test,v 1.2 2008/01/05 17:39:30 danielk1977 Exp $

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

do_test minmax2-1.0 {
  execsql {
    PRAGMA legacy_file_format=0;
................................................................................
do_test minmax2-1.5 {
  execsql {CREATE INDEX t1i1 ON t1(x DESC)}
  set sqlite_search_count 0
  execsql {SELECT min(x) FROM t1}
} {1}
do_test minmax2-1.6 {
  set sqlite_search_count
} {1}
do_test minmax2-1.7 {
  set sqlite_search_count 0
  execsql {SELECT max(x) FROM t1}
} {20}
do_test minmax2-1.8 {
  set sqlite_search_count
} {0}
do_test minmax2-1.9 {
  set sqlite_search_count 0
  execsql {SELECT max(y) FROM t1}
} {5}
do_test minmax2-1.10 {
  set sqlite_search_count
} {19}

Added test/minmax3.test.

































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
# 2008 January 5
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# $Id: minmax3.test,v 1.1 2008/01/05 17:39:30 danielk1977 Exp $

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

# Do an SQL statement.  Append the search count to the end of the result.
#
proc count sql {
  set ::sqlite_search_count 0
  return [concat [execsql $sql] $::sqlite_search_count]
}

# This procedure sets the value of the file-format in file 'test.db'
# to $newval. Also, the schema cookie is incremented.
# 
proc set_file_format {newval} {
  set bt [btree_open test.db 10 0]
  btree_begin_transaction $bt
  set meta [btree_get_meta $bt]
  lset meta 2 $newval                    ;# File format
  lset meta 1 [expr [lindex $meta 1]+1]  ;# Schema cookie
  eval "btree_update_meta $bt $meta"
  btree_commit $bt
  btree_close $bt
}

# Create the file as file-format 4 (DESC index support). This is 
# required to exercise a few cases in where.c.
#
execsql { select * from sqlite_master }
set_file_format 4

do_test minmax3-1.0 {
  execsql {
    BEGIN;
    CREATE TABLE t1(x, y, z);
    INSERT INTO t1 VALUES('1', 'I',   'one');
    INSERT INTO t1 VALUES('2', 'IV',  'four');
    INSERT INTO t1 VALUES('2', NULL,  'three');
    INSERT INTO t1 VALUES('2', 'II',  'two');
    INSERT INTO t1 VALUES('2', 'V',   'five');
    INSERT INTO t1 VALUES('3', 'VI',  'six');
    COMMIT;
  }
} {}
do_test minmax3-1.1.1 {
  # Linear scan.
  count { SELECT max(y) FROM t1 WHERE x = '2'; }
} {V 5}
do_test minmax3-1.1.2 {
  # Index optimizes the WHERE x='2' constraint.
  execsql { CREATE INDEX i1 ON t1(x) }
  count   { SELECT max(y) FROM t1 WHERE x = '2'; }
} {V 9}
do_test minmax3-1.1.3 {
  # Index optimizes the WHERE x='2' constraint and the MAX(y).
  execsql { CREATE INDEX i2 ON t1(x,y) }
  count   { SELECT max(y) FROM t1 WHERE x = '2'; }
} {V 1}
do_test minmax3-1.1.4 {
  # Index optimizes the WHERE x='2' constraint and the MAX(y).
  execsql { DROP INDEX i2 ; CREATE INDEX i2 ON t1(x, y DESC) }
  count   { SELECT max(y) FROM t1 WHERE x = '2'; }
} {V 1}
do_test minmax3-1.1.5 {
  count   { SELECT max(y) FROM t1 WHERE x = '2' AND y != 'V'; }
} {IV 2}
do_test minmax3-1.1.6 {
  count   { SELECT max(y) FROM t1 WHERE x = '2' AND y < 'V'; }
} {IV 1}
do_test minmax3-1.1.6 {
  count   { SELECT max(y) FROM t1 WHERE x = '2' AND z != 'five'; }
} {IV 4}

do_test minmax3-1.2.1 {
  # Linear scan of t1.
  execsql { DROP INDEX i1 ; DROP INDEX i2 }
  count { SELECT min(y) FROM t1 WHERE x = '2'; }
} {II 5}
do_test minmax3-1.2.2 {
  # Index i1 optimizes the WHERE x='2' constraint.
  execsql { CREATE INDEX i1 ON t1(x) }
  count   { SELECT min(y) FROM t1 WHERE x = '2'; }
} {II 9}
do_test minmax3-1.2.3 {
  # Index i2 optimizes the WHERE x='2' constraint and the min(y).
  execsql { CREATE INDEX i2 ON t1(x,y) }
  count   { SELECT min(y) FROM t1 WHERE x = '2'; }
} {II 1}
do_test minmax3-1.2.4 {
  # Index optimizes the WHERE x='2' constraint and the MAX(y).
  execsql { DROP INDEX i2 ; CREATE INDEX i2 ON t1(x, y DESC) }
  count   { SELECT min(y) FROM t1 WHERE x = '2'; }
} {II 1}

do_test minmax3-1.3.1 {
  # Linear scan
  execsql { DROP INDEX i1 ; DROP INDEX i2 }
  count   { SELECT min(y) FROM t1; }
} {I 5}
do_test minmax3-1.3.2 {
  # Index i1 optimizes the min(y)
  execsql { CREATE INDEX i1 ON t1(y) }
  count   { SELECT min(y) FROM t1; }
} {I 1}
do_test minmax3-1.3.3 {
  # Index i1 optimizes the min(y)
  execsql { DROP INDEX i1 ; CREATE INDEX i1 ON t1(y DESC) }
  count   { SELECT min(y) FROM t1; }
} {I 1}

do_test minmax3-1.4.1 {
  # Linear scan
  execsql { DROP INDEX i1 }
  count   { SELECT max(y) FROM t1; }
} {VI 5}
do_test minmax3-1.4.2 {
  # Index i1 optimizes the max(y)
  execsql { CREATE INDEX i1 ON t1(y) }
  count   { SELECT max(y) FROM t1; }
} {VI 0}
do_test minmax3-1.4.3 {
  # Index i1 optimizes the max(y)
  execsql { DROP INDEX i1 ; CREATE INDEX i1 ON t1(y DESC) }
  execsql   { SELECT y from t1}
  count   { SELECT max(y) FROM t1; }
} {VI 0}
do_test minmax3-1.4.4 {
  execsql { DROP INDEX i1 }
} {}


finish_test