SQLite

Check-in [7283f7c29d]
Login

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

Overview
Comment:Optimizations and refinements. Improvements to test coverage. (CVS 2667)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 7283f7c29db4f622380b6a5cb745a4dc0c8e6a25
User & Date: drh 2005-09-08 01:58:43.000
Context
2005-09-08
02:00
Changes to comments only in sqliteInt.h. No changes to code. (CVS 2668) (check-in: cc2a61650e user: drh tags: trunk)
01:58
Optimizations and refinements. Improvements to test coverage. (CVS 2667) (check-in: 7283f7c29d user: drh tags: trunk)
00:13
All regression tests now pass. But I am sure there must still be problems. New tests need to be added. (CVS 2666) (check-in: bcc7d722ce user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/delete.c.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    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.109 2005/07/21 18:23:20 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.







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    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.110 2005/09/08 01:58:43 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.
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
  v = sqlite3GetVdbe(pParse);
  if( v==0 ){
    goto delete_from_cleanup;
  }
  if( pParse->nested==0 ) sqlite3VdbeCountChanges(v);
  sqlite3BeginWriteOperation(pParse, triggers_exist, pTab->iDb);

  /* If we are trying to delete from a view, construct that view into
  ** a temporary table.
  */
  if( isView ){
    Select *pView = sqlite3SelectDup(pTab->pSelect);
    sqlite3Select(pParse, pView, SRT_TempTable, iCur, 0, 0, 0, 0);
    sqlite3SelectDelete(pView);
  }

  /* Initialize the counter of the number of rows deleted, if
  ** we are counting rows.
  */
  if( db->flags & SQLITE_CountRows ){







|
|



|







174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
  v = sqlite3GetVdbe(pParse);
  if( v==0 ){
    goto delete_from_cleanup;
  }
  if( pParse->nested==0 ) sqlite3VdbeCountChanges(v);
  sqlite3BeginWriteOperation(pParse, triggers_exist, pTab->iDb);

  /* If we are trying to delete from a view, realize that view into
  ** a ephemeral table.
  */
  if( isView ){
    Select *pView = sqlite3SelectDup(pTab->pSelect);
    sqlite3Select(pParse, pView, SRT_VirtualTab, iCur, 0, 0, 0, 0);
    sqlite3SelectDelete(pView);
  }

  /* Initialize the counter of the number of rows deleted, if
  ** we are counting rows.
  */
  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
**    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.264 2005/09/08 00:13:28 drh Exp $
*/
#include "sqliteInt.h"


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







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    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.265 2005/09/08 01:58:43 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343

/*
** Insert code into "v" that will push the record on the top of the
** stack into the sorter.
*/
static void pushOntoSorter(Parse *pParse, Vdbe *v, ExprList *pOrderBy){
  sqlite3ExprCodeExprList(pParse, pOrderBy);
  sqlite3VdbeAddOp(v, OP_Sequence, pOrderBy->iTab, 0);
  sqlite3VdbeAddOp(v, OP_Pull, pOrderBy->nExpr + 1, 0);
  sqlite3VdbeAddOp(v, OP_MakeRecord, pOrderBy->nExpr + 2, 0);
  sqlite3VdbeAddOp(v, OP_IdxInsert, pOrderBy->iTab, 0);
}

/*
** Add code to implement the OFFSET and LIMIT
*/
static void codeLimiter(
  Vdbe *v,          /* Generate code into this VM */







|


|







326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343

/*
** Insert code into "v" that will push the record on the top of the
** stack into the sorter.
*/
static void pushOntoSorter(Parse *pParse, Vdbe *v, ExprList *pOrderBy){
  sqlite3ExprCodeExprList(pParse, pOrderBy);
  sqlite3VdbeAddOp(v, OP_Sequence, pOrderBy->iECursor, 0);
  sqlite3VdbeAddOp(v, OP_Pull, pOrderBy->nExpr + 1, 0);
  sqlite3VdbeAddOp(v, OP_MakeRecord, pOrderBy->nExpr + 2, 0);
  sqlite3VdbeAddOp(v, OP_IdxInsert, pOrderBy->iECursor, 0);
}

/*
** Add code to implement the OFFSET and LIMIT
*/
static void codeLimiter(
  Vdbe *v,          /* Generate code into this VM */
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
      break;
    }
#endif

    /* Store the result as data using a unique key.
    */
    case SRT_Table:
    case SRT_TempTable: {
      sqlite3VdbeAddOp(v, OP_MakeRecord, nColumn, 0);
      if( pOrderBy ){
        pushOntoSorter(pParse, v, pOrderBy);
      }else{
        sqlite3VdbeAddOp(v, OP_NewRowid, iParm, 0);
        sqlite3VdbeAddOp(v, OP_Pull, 1, 0);
        sqlite3VdbeAddOp(v, OP_Insert, iParm, 0);







|







461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
      break;
    }
#endif

    /* Store the result as data using a unique key.
    */
    case SRT_Table:
    case SRT_VirtualTab: {
      sqlite3VdbeAddOp(v, OP_MakeRecord, nColumn, 0);
      if( pOrderBy ){
        pushOntoSorter(pParse, v, pOrderBy);
      }else{
        sqlite3VdbeAddOp(v, OP_NewRowid, iParm, 0);
        sqlite3VdbeAddOp(v, OP_Pull, 1, 0);
        sqlite3VdbeAddOp(v, OP_Insert, iParm, 0);
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
#endif /* #ifndef SQLITE_OMIT_SUBQUERY */

    /* Send the data to the callback function or to a subroutine.  In the
    ** case of a subroutine, the subroutine itself is responsible for
    ** popping the data from the stack.
    */
    case SRT_Subroutine:
    case SRT_Callback:
    case SRT_Sorter: {
      if( pOrderBy ){
        sqlite3VdbeAddOp(v, OP_MakeRecord, nColumn, 0);
        pushOntoSorter(pParse, v, pOrderBy);
      }else if( eDest==SRT_Subroutine ){
        sqlite3VdbeAddOp(v, OP_Gosub, 0, iParm);
      }else{
        assert( eDest!=SRT_Sorter );
        sqlite3VdbeAddOp(v, OP_Callback, nColumn, 0);
      }
      break;
    }

#if !defined(SQLITE_OMIT_TRIGGER)
    /* Discard the results.  This is used for SELECT statements inside







|
<






<







524
525
526
527
528
529
530
531

532
533
534
535
536
537

538
539
540
541
542
543
544
#endif /* #ifndef SQLITE_OMIT_SUBQUERY */

    /* Send the data to the callback function or to a subroutine.  In the
    ** case of a subroutine, the subroutine itself is responsible for
    ** popping the data from the stack.
    */
    case SRT_Subroutine:
    case SRT_Callback: {

      if( pOrderBy ){
        sqlite3VdbeAddOp(v, OP_MakeRecord, nColumn, 0);
        pushOntoSorter(pParse, v, pOrderBy);
      }else if( eDest==SRT_Subroutine ){
        sqlite3VdbeAddOp(v, OP_Gosub, 0, iParm);
      }else{

        sqlite3VdbeAddOp(v, OP_Callback, nColumn, 0);
      }
      break;
    }

#if !defined(SQLITE_OMIT_TRIGGER)
    /* Discard the results.  This is used for SELECT statements inside
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
){
  int brk = sqlite3VdbeMakeLabel(v);
  int cont = sqlite3VdbeMakeLabel(v);
  int addr;
  int iTab;
  ExprList *pOrderBy = p->pOrderBy;

  if( eDest==SRT_Sorter ) return;
  iTab = pOrderBy->iTab;
  addr = 1 + sqlite3VdbeAddOp(v, OP_Sort, iTab, brk);
  codeLimiter(v, p, cont, brk, 0);
  sqlite3VdbeAddOp(v, OP_Column, iTab, pOrderBy->nExpr + 1);
  switch( eDest ){
    case SRT_Table:
    case SRT_TempTable: {
      sqlite3VdbeAddOp(v, OP_NewRowid, iParm, 0);
      sqlite3VdbeAddOp(v, OP_Pull, 1, 0);
      sqlite3VdbeAddOp(v, OP_Insert, iParm, 0);
      break;
    }
#ifndef SQLITE_OMIT_SUBQUERY
    case SRT_Set: {







<
|





|







614
615
616
617
618
619
620

621
622
623
624
625
626
627
628
629
630
631
632
633
634
){
  int brk = sqlite3VdbeMakeLabel(v);
  int cont = sqlite3VdbeMakeLabel(v);
  int addr;
  int iTab;
  ExprList *pOrderBy = p->pOrderBy;


  iTab = pOrderBy->iECursor;
  addr = 1 + sqlite3VdbeAddOp(v, OP_Sort, iTab, brk);
  codeLimiter(v, p, cont, brk, 0);
  sqlite3VdbeAddOp(v, OP_Column, iTab, pOrderBy->nExpr + 1);
  switch( eDest ){
    case SRT_Table:
    case SRT_VirtualTab: {
      sqlite3VdbeAddOp(v, OP_NewRowid, iParm, 0);
      sqlite3VdbeAddOp(v, OP_Pull, 1, 0);
      sqlite3VdbeAddOp(v, OP_Insert, iParm, 0);
      break;
    }
#ifndef SQLITE_OMIT_SUBQUERY
    case SRT_Set: {
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356

/*
** Allocate a virtual index to use for sorting.
*/
static void createSortingIndex(Parse *pParse, Select *p, ExprList *pOrderBy){
  if( pOrderBy ){
    int addr;
    assert( pOrderBy->iTab==0 );
    pOrderBy->iTab = pParse->nTab++;
    addr = sqlite3VdbeAddOp(pParse->pVdbe, OP_OpenVirtual,
                            pOrderBy->iTab, pOrderBy->nExpr+1);
    assert( p->addrOpenVirt[2] == -1 );
    p->addrOpenVirt[2] = addr;
  }
}

/*
** The opcode at addr is an OP_OpenVirtual that created a sorting







|
|

|







1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353

/*
** Allocate a virtual index to use for sorting.
*/
static void createSortingIndex(Parse *pParse, Select *p, ExprList *pOrderBy){
  if( pOrderBy ){
    int addr;
    assert( pOrderBy->iECursor==0 );
    pOrderBy->iECursor = pParse->nTab++;
    addr = sqlite3VdbeAddOp(pParse->pVdbe, OP_OpenVirtual,
                            pOrderBy->iECursor, pOrderBy->nExpr+1);
    assert( p->addrOpenVirt[2] == -1 );
    p->addrOpenVirt[2] = addr;
  }
}

/*
** The opcode at addr is an OP_OpenVirtual that created a sorting
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
  if( v==0 ){
    rc = 1;
    goto multi_select_end;
  }

  /* Create the destination temporary table if necessary
  */
  if( eDest==SRT_TempTable ){
    assert( p->pEList );
    assert( nSetP2<sizeof(aSetP2)/sizeof(aSetP2[0]) );
    aSetP2[nSetP2++] = sqlite3VdbeAddOp(v, OP_OpenVirtual, iParm, 0);
    eDest = SRT_Table;
  }

  /* Generate code for the left and right SELECT statements.







|







1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
  if( v==0 ){
    rc = 1;
    goto multi_select_end;
  }

  /* Create the destination temporary table if necessary
  */
  if( eDest==SRT_VirtualTab ){
    assert( p->pEList );
    assert( nSetP2<sizeof(aSetP2)/sizeof(aSetP2[0]) );
    aSetP2[nSetP2++] = sqlite3VdbeAddOp(v, OP_OpenVirtual, iParm, 0);
    eDest = SRT_Table;
  }

  /* Generate code for the left and right SELECT statements.
2178
2179
2180
2181
2182
2183
2184
2185
2186
2187
2188
2189
2190
2191
2192
  ** The column names have already been generated in the calling function.
  */
  v = sqlite3GetVdbe(pParse);
  if( v==0 ) return 0;

  /* If the output is destined for a temporary table, open that table.
  */
  if( eDest==SRT_TempTable ){
    sqlite3VdbeAddOp(v, OP_OpenVirtual, iParm, 1);
  }

  /* Generating code to find the min or the max.  Basically all we have
  ** to do is find the first or the last entry in the chosen index.  If
  ** the min() or max() is on the INTEGER PRIMARY KEY, then find the first
  ** or last entry in the main table.







|







2175
2176
2177
2178
2179
2180
2181
2182
2183
2184
2185
2186
2187
2188
2189
  ** The column names have already been generated in the calling function.
  */
  v = sqlite3GetVdbe(pParse);
  if( v==0 ) return 0;

  /* If the output is destined for a temporary table, open that table.
  */
  if( eDest==SRT_VirtualTab ){
    sqlite3VdbeAddOp(v, OP_OpenVirtual, iParm, 1);
  }

  /* Generating code to find the min or the max.  Basically all we have
  ** to do is find the first or the last entry in the chosen index.  If
  ** the min() or max() is on the INTEGER PRIMARY KEY, then find the first
  ** or last entry in the main table.
2550
2551
2552
2553
2554
2555
2556

2557
2558
2559
2560
2561
2562
2563
  Expr *pWhere;          /* The WHERE clause.  May be NULL */
  ExprList *pOrderBy;    /* The ORDER BY clause.  May be NULL */
  ExprList *pGroupBy;    /* The GROUP BY clause.  May be NULL */
  Expr *pHaving;         /* The HAVING clause.  May be NULL */
  int isDistinct;        /* True if the DISTINCT keyword is present */
  int distinct;          /* Table to use for the distinct set */
  int rc = 1;            /* Value to return from this function */

  AggInfo sAggInfo;      /* Information used by aggregate queries */

  if( sqlite3_malloc_failed || pParse->nErr || p==0 ) return 1;
  if( sqlite3AuthCheck(pParse, SQLITE_SELECT, 0, 0, 0) ) return 1;
  memset(&sAggInfo, 0, sizeof(sAggInfo));

#ifndef SQLITE_OMIT_COMPOUND_SELECT







>







2547
2548
2549
2550
2551
2552
2553
2554
2555
2556
2557
2558
2559
2560
2561
  Expr *pWhere;          /* The WHERE clause.  May be NULL */
  ExprList *pOrderBy;    /* The ORDER BY clause.  May be NULL */
  ExprList *pGroupBy;    /* The GROUP BY clause.  May be NULL */
  Expr *pHaving;         /* The HAVING clause.  May be NULL */
  int isDistinct;        /* True if the DISTINCT keyword is present */
  int distinct;          /* Table to use for the distinct set */
  int rc = 1;            /* Value to return from this function */
  int addrSortIndex;     /* Address of an OP_OpenVirtual instruction */
  AggInfo sAggInfo;      /* Information used by aggregate queries */

  if( sqlite3_malloc_failed || pParse->nErr || p==0 ) return 1;
  if( sqlite3AuthCheck(pParse, SQLITE_SELECT, 0, 0, 0) ) return 1;
  memset(&sAggInfo, 0, sizeof(sAggInfo));

#ifndef SQLITE_OMIT_COMPOUND_SELECT
2642
2643
2644
2645
2646
2647
2648
2649
2650
2651
2652
2653
2654
2655
2656
    if( pItem->zName!=0 ){
      zSavedAuthContext = pParse->zAuthContext;
      pParse->zAuthContext = pItem->zName;
      needRestoreContext = 1;
    }else{
      needRestoreContext = 0;
    }
    sqlite3Select(pParse, pItem->pSelect, SRT_TempTable, 
                 pItem->iCursor, p, i, &isAgg, 0);
    if( needRestoreContext ){
      pParse->zAuthContext = zSavedAuthContext;
    }
    pTabList = p->pSrc;
    pWhere = p->pWhere;
    if( !IgnorableOrderby(eDest) ){







|







2640
2641
2642
2643
2644
2645
2646
2647
2648
2649
2650
2651
2652
2653
2654
    if( pItem->zName!=0 ){
      zSavedAuthContext = pParse->zAuthContext;
      pParse->zAuthContext = pItem->zName;
      needRestoreContext = 1;
    }else{
      needRestoreContext = 0;
    }
    sqlite3Select(pParse, pItem->pSelect, SRT_VirtualTab, 
                 pItem->iCursor, p, i, &isAgg, 0);
    if( needRestoreContext ){
      pParse->zAuthContext = zSavedAuthContext;
    }
    pTabList = p->pSrc;
    pWhere = p->pWhere;
    if( !IgnorableOrderby(eDest) ){
2678
2679
2680
2681
2682
2683
2684
2685






2686
2687
2688
2689
2690
2691
2692
2693
2694
2695
2696
2697
2698
2699
2700

2701
2702

2703
2704
2705
2706
2707
2708
2709
2710
2711
2712
2713
2714
2715
2716
2717
2718
2719
      flattenSubquery(pParse, pParent, parentTab, *pParentAgg, isAgg) ){
    if( isAgg ) *pParentAgg = 1;
    goto select_end;
  }
#endif

  /* If there is an ORDER BY clause, resolve any collation sequences
  ** names that have been explicitly specified.






  */
  if( pOrderBy ){
    struct ExprList_item *pTerm;
    KeyInfo *pKeyInfo;
    int addr;
    for(i=0, pTerm=pOrderBy->a; i<pOrderBy->nExpr; i++, pTerm++){
      if( pTerm->zName ){
        pTerm->pExpr->pColl = sqlite3LocateCollSeq(pParse, pTerm->zName, -1);
      }
    }
    if( pParse->nErr ){
      goto select_end;
    }
    pKeyInfo = keyInfoFromExprList(pParse, pOrderBy);
    pOrderBy->iTab = pParse->nTab++;

    addr = sqlite3VdbeOp3(v, OP_OpenVirtual, pOrderBy->iTab, pOrderBy->nExpr+2, 
                        (char*)pKeyInfo, P3_KEYINFO_HANDOFF);

    p->addrOpenVirt[2] = addr;
  }

  /* Set the limiter.
  */
  computeLimitRegisters(pParse, p);

  /* If the output is destined for a temporary table, open that table.
  */
  if( eDest==SRT_TempTable ){
    sqlite3VdbeAddOp(v, OP_OpenVirtual, iParm, pEList->nExpr);
  }


  /* Initialize the memory cell to NULL for SRT_Mem or 0 for SRT_Exists
  */
  if( eDest==SRT_Mem || eDest==SRT_Exists ){







|
>
>
>
>
>
>




<









|
>
|

>
|








|







2676
2677
2678
2679
2680
2681
2682
2683
2684
2685
2686
2687
2688
2689
2690
2691
2692
2693

2694
2695
2696
2697
2698
2699
2700
2701
2702
2703
2704
2705
2706
2707
2708
2709
2710
2711
2712
2713
2714
2715
2716
2717
2718
2719
2720
2721
2722
2723
2724
      flattenSubquery(pParse, pParent, parentTab, *pParentAgg, isAgg) ){
    if( isAgg ) *pParentAgg = 1;
    goto select_end;
  }
#endif

  /* If there is an ORDER BY clause, resolve any collation sequences
  ** names that have been explicitly specified and create a sorting index.
  **
  ** This sorting index might end up being unused if the data can be 
  ** extracted in pre-sorted order.  If that is the case, then the
  ** OP_OpenVirtual instruction will be changed to an OP_Noop once
  ** we figure out that the sorting index is not needed.  The addrSortIndex
  ** variable is used to facilitate that change.
  */
  if( pOrderBy ){
    struct ExprList_item *pTerm;
    KeyInfo *pKeyInfo;

    for(i=0, pTerm=pOrderBy->a; i<pOrderBy->nExpr; i++, pTerm++){
      if( pTerm->zName ){
        pTerm->pExpr->pColl = sqlite3LocateCollSeq(pParse, pTerm->zName, -1);
      }
    }
    if( pParse->nErr ){
      goto select_end;
    }
    pKeyInfo = keyInfoFromExprList(pParse, pOrderBy);
    pOrderBy->iECursor = pParse->nTab++;
    p->addrOpenVirt[2] = addrSortIndex =
       sqlite3VdbeOp3(v, OP_OpenVirtual, pOrderBy->iECursor, pOrderBy->nExpr+2, 
                        (char*)pKeyInfo, P3_KEYINFO_HANDOFF);
  }else{
    addrSortIndex = -1;
  }

  /* Set the limiter.
  */
  computeLimitRegisters(pParse, p);

  /* If the output is destined for a temporary table, open that table.
  */
  if( eDest==SRT_VirtualTab ){
    sqlite3VdbeAddOp(v, OP_OpenVirtual, iParm, pEList->nExpr);
  }


  /* Initialize the memory cell to NULL for SRT_Mem or 0 for SRT_Exists
  */
  if( eDest==SRT_Mem || eDest==SRT_Exists ){
2736
2737
2738
2739
2740
2741
2742









2743
2744
2745
2746
2747
2748
2749
  /* 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;










    /* Use the standard inner loop
    */
    if( selectInnerLoop(pParse, p, pEList, 0, 0, pOrderBy, distinct, eDest,
                    iParm, pWInfo->iContinue, pWInfo->iBreak, aff) ){
       goto select_end;
    }







>
>
>
>
>
>
>
>
>







2741
2742
2743
2744
2745
2746
2747
2748
2749
2750
2751
2752
2753
2754
2755
2756
2757
2758
2759
2760
2761
2762
2763
  /* 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_OpenVirtual 
    ** instruction ended up not being needed, then change the OP_OpenVirtual
    ** into an OP_Noop.
    */
    if( addrSortIndex>=0 && pOrderBy==0 ){
      uncreateSortingIndex(pParse, addrSortIndex);
      p->addrOpenVirt[2] = -1;
    }

    /* Use the standard inner loop
    */
    if( selectInnerLoop(pParse, p, pEList, 0, 0, pOrderBy, distinct, eDest,
                    iParm, pWInfo->iContinue, pWInfo->iBreak, aff) ){
       goto select_end;
    }
2781
2782
2783
2784
2785
2786
2787

2788
2789
2790
2791
2792
2793
2794
    ** SELECT statement.
    */
    memset(&sNC, 0, sizeof(sNC));
    sNC.pParse = pParse;
    sNC.pSrcList = pTabList;
    sNC.pAggInfo = &sAggInfo;
    sAggInfo.nSortingColumn = pGroupBy ? pGroupBy->nExpr+1 : 0;

    if( sqlite3ExprAnalyzeAggList(&sNC, pEList) ){
      goto select_end;
    }
    if( sqlite3ExprAnalyzeAggList(&sNC, pOrderBy) ){
      goto select_end;
    }
    if( pHaving && sqlite3ExprAnalyzeAggregates(&sNC, pHaving) ){







>







2795
2796
2797
2798
2799
2800
2801
2802
2803
2804
2805
2806
2807
2808
2809
    ** SELECT statement.
    */
    memset(&sNC, 0, sizeof(sNC));
    sNC.pParse = pParse;
    sNC.pSrcList = pTabList;
    sNC.pAggInfo = &sAggInfo;
    sAggInfo.nSortingColumn = pGroupBy ? pGroupBy->nExpr+1 : 0;
    sAggInfo.pGroupBy = pGroupBy;
    if( sqlite3ExprAnalyzeAggList(&sNC, pEList) ){
      goto select_end;
    }
    if( sqlite3ExprAnalyzeAggList(&sNC, pOrderBy) ){
      goto select_end;
    }
    if( pHaving && sqlite3ExprAnalyzeAggregates(&sNC, pHaving) ){
Changes to src/sqliteInt.h.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*
** 2001 September 15
**
** 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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.409 2005/09/07 22:48:16 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** These #defines should enable >2GB file support on Posix if the
** underlying operating system supports it.  If the OS lacks













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*
** 2001 September 15
**
** 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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.410 2005/09/08 01:58:43 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** These #defines should enable >2GB file support on Posix if the
** underlying operating system supports it.  If the OS lacks
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
** list of "ID = expr" items in an UPDATE.  A list of expressions can
** also be used as the argument to a function, in which case the a.zName
** field is not used.
*/
struct ExprList {
  int nExpr;             /* Number of expressions on the list */
  int nAlloc;            /* Number of entries allocated below */
  int iTab;              /* VDBE Cursor associated with this ExprList */
  struct ExprList_item {
    Expr *pExpr;           /* The list of expressions */
    char *zName;           /* Token associated with this expression */
    u8 sortOrder;          /* 1 for DESC or 0 for ASC */
    u8 isAgg;              /* True if this is an aggregate like count(*) */
    u8 done;               /* A flag to indicate when processing is finished */
  } *a;                  /* One entry for each expression */







|







923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
** list of "ID = expr" items in an UPDATE.  A list of expressions can
** also be used as the argument to a function, in which case the a.zName
** field is not used.
*/
struct ExprList {
  int nExpr;             /* Number of expressions on the list */
  int nAlloc;            /* Number of entries allocated below */
  int iECursor;          /* VDBE Cursor associated with this ExprList */
  struct ExprList_item {
    Expr *pExpr;           /* The list of expressions */
    char *zName;           /* Token associated with this expression */
    u8 sortOrder;          /* 1 for DESC or 0 for ASC */
    u8 isAgg;              /* True if this is an aggregate like count(*) */
    u8 done;               /* A flag to indicate when processing is finished */
  } *a;                  /* One entry for each expression */
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
/* The ORDER BY clause is ignored for all of the above */
#define IgnorableOrderby(X) (X<=SRT_Discard)

#define SRT_Callback     4  /* Invoke a callback with each row of result */
#define SRT_Mem          5  /* Store result in a memory cell */
#define SRT_Set          6  /* Store non-null results as keys in an index */
#define SRT_Table        7  /* Store result as data and add automatic rowid */
#define SRT_TempTable    8  /* Store result in a trasient table */
#define SRT_Sorter       9  /* Store results in the sorter NOT USED */
#define SRT_Subroutine  10  /* Call a subroutine to handle results */
#define SRT_Exists      11  /* Put 0 or 1 in a memory cell */

/*
** An SQL parser context.  A copy of this structure is passed through
** the parser and down into all the parser action routine in order to
** carry around information that is global to the entire parse.
**
** The structure is divided into two parts.  When the parser and code







|
<
|
|







1128
1129
1130
1131
1132
1133
1134
1135

1136
1137
1138
1139
1140
1141
1142
1143
1144
/* The ORDER BY clause is ignored for all of the above */
#define IgnorableOrderby(X) (X<=SRT_Discard)

#define SRT_Callback     4  /* Invoke a callback with each row of result */
#define SRT_Mem          5  /* Store result in a memory cell */
#define SRT_Set          6  /* Store non-null results as keys in an index */
#define SRT_Table        7  /* Store result as data and add automatic rowid */
#define SRT_VirtualTab   8  /* Create virtual table and store results there */

#define SRT_Subroutine   9  /* Call a subroutine to handle results */
#define SRT_Exists      10  /* Put 0 or 1 in a memory cell */

/*
** An SQL parser context.  A copy of this structure is passed through
** the parser and down into all the parser action routine in order to
** carry around information that is global to the entire parse.
**
** The structure is divided into two parts.  When the parser and code
Changes to src/update.c.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    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.110 2005/07/21 18:23:20 drh Exp $
*/
#include "sqliteInt.h"

/*
** The most recently coded instruction was an OP_Column to retrieve column
** 'i' of table pTab. This routine sets the P3 parameter of the 
** OP_Column to the default value, if any.







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    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.111 2005/09/08 01:58:43 drh Exp $
*/
#include "sqliteInt.h"

/*
** The most recently coded instruction was an OP_Column to retrieve column
** 'i' of table pTab. This routine sets the P3 parameter of the 
** OP_Column to the default value, if any.
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
  /* Begin generating code.
  */
  v = sqlite3GetVdbe(pParse);
  if( v==0 ) goto update_cleanup;
  if( pParse->nested==0 ) sqlite3VdbeCountChanges(v);
  sqlite3BeginWriteOperation(pParse, 1, pTab->iDb);

  /* If we are trying to update a view, construct that view into
  ** a temporary table.
  */
  if( isView ){
    Select *pView;
    pView = sqlite3SelectDup(pTab->pSelect);
    sqlite3Select(pParse, pView, SRT_TempTable, iCur, 0, 0, 0, 0);
    sqlite3SelectDelete(pView);
  }

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







|
|




|







255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
  /* Begin generating code.
  */
  v = sqlite3GetVdbe(pParse);
  if( v==0 ) goto update_cleanup;
  if( pParse->nested==0 ) sqlite3VdbeCountChanges(v);
  sqlite3BeginWriteOperation(pParse, 1, pTab->iDb);

  /* If we are trying to update a view, realize that view into
  ** a ephemeral table.
  */
  if( isView ){
    Select *pView;
    pView = sqlite3SelectDup(pTab->pSelect);
    sqlite3Select(pParse, pView, SRT_VirtualTab, iCur, 0, 0, 0, 0);
    sqlite3SelectDelete(pView);
  }

  /* Begin the database scan
  */
  pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0);
  if( pWInfo==0 ) goto update_cleanup;
Changes to test/collate3.test.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2001 September 15
#
# 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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is page cache subsystem.
#
# $Id: collate3.test,v 1.10 2005/01/29 08:32:46 danielk1977 Exp $

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

#
# Tests are organised as follows:
#













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2001 September 15
#
# 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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is page cache subsystem.
#
# $Id: collate3.test,v 1.11 2005/09/08 01:58:43 drh Exp $

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

#
# Tests are organised as follows:
#
148
149
150
151
152
153
154

155
156
157
158
159
160
161
  } {1 {no such collation sequence: string_compare}} 
  do_test collate3-2.12 {
    catchsql {
      SELECT c1 FROM collate3t1 UNION ALL SELECT c1 FROM collate3t1;
    }
  } {0 {}}
  do_test collate3-2.13 {

    catchsql {
      SELECT 10 UNION ALL SELECT 20 ORDER BY 1 COLLATE string_compare;
    }
  } {1 {no such collation sequence: string_compare}} 
  do_test collate3-2.14 {
    catchsql {
      SELECT 10 INTERSECT SELECT 20 ORDER BY 1 COLLATE string_compare;







>







148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
  } {1 {no such collation sequence: string_compare}} 
  do_test collate3-2.12 {
    catchsql {
      SELECT c1 FROM collate3t1 UNION ALL SELECT c1 FROM collate3t1;
    }
  } {0 {}}
  do_test collate3-2.13 {
btree_breakpoint
    catchsql {
      SELECT 10 UNION ALL SELECT 20 ORDER BY 1 COLLATE string_compare;
    }
  } {1 {no such collation sequence: string_compare}} 
  do_test collate3-2.14 {
    catchsql {
      SELECT 10 INTERSECT SELECT 20 ORDER BY 1 COLLATE string_compare;
Changes to test/misc4.test.
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc4.test,v 1.18 2005/09/07 22:48:16 drh Exp $

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

# Prepare a statement that will create a temporary table.  Then do
# a rollback.  Then try to execute the prepared statement.
#







|







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc4.test,v 1.19 2005/09/08 01:58:43 drh Exp $

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

# Prepare a statement that will create a temporary table.  Then do
# a rollback.  Then try to execute the prepared statement.
#
96
97
98
99
100
101
102







103
104
105
106
107
108
109
    INSERT INTO Table2 VALUES(1, 'z');
    INSERT INTO Table2 VALUES (1, 'a');
    SELECT ID, Value FROM Table1
       UNION SELECT ID, max(Value) FROM Table2 GROUP BY 1
    ORDER BY 1, 2;
  }
} {1 x 1 z}







} ;# ifcapable compound

# Ticket #1047.  Make sure column types are preserved in subqueries.
#
ifcapable subquery {
  do_test misc4-4.1 {
    execsql {







>
>
>
>
>
>
>







96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
    INSERT INTO Table2 VALUES(1, 'z');
    INSERT INTO Table2 VALUES (1, 'a');
    SELECT ID, Value FROM Table1
       UNION SELECT ID, max(Value) FROM Table2 GROUP BY 1
    ORDER BY 1, 2;
  }
} {1 x 1 z}
do_test misc4-3.2 {
  catchsql { 
    SELECT ID, Value FROM Table1
       UNION SELECT ID, max(Value) FROM Table2 GROUP BY 1, 2
    ORDER BY 1, 2;
  }
} {1 {aggregate functions are not allowed in the GROUP BY clause}}
} ;# ifcapable compound

# Ticket #1047.  Make sure column types are preserved in subqueries.
#
ifcapable subquery {
  do_test misc4-4.1 {
    execsql {