/ Check-in [2c05389e]
Login

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

Overview
Comment:Bug fixes and additional tests for the subquery flattener. (CVS 411)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:2c05389eda391e38894fc6969e29766df82a8fec
User & Date: drh 2002-03-03 02:49:51
Context
2002-03-03
03:03
Suppress superfluous OP_OpenTemps when flattening subqueries. (CVS 412) check-in: 000441c8 user: drh tags: trunk
02:49
Bug fixes and additional tests for the subquery flattener. (CVS 411) check-in: 2c05389e user: drh tags: trunk
2002-03-02
20:41
Pager optimization: do not write or journal free pages. This results in a 2x performance gain for large INSERTs and a 5x performance gain for large DELETEs. (CVS 410) check-in: cf1ebcfb user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
756
757
758
759
760
761
762

763
764




765
766
767
768
769
770
771
772
773
774
775
776
...
795
796
797
798
799
800
801

802
803
804
805
806
807
808
...
904
905
906
907
908
909
910

911
912

913
914
915
916
917
918
919
920
921
922
923
924

925
















926
927
928
929
930
931
932
....
1079
1080
1081
1082
1083
1084
1085







1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
....
1250
1251
1252
1253
1254
1255
1256
1257






1258
1259
1260
1261
1262

1263

1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
**    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.71 2002/03/02 17:04:08 drh Exp $
*/
#include "sqliteInt.h"

/*
** Allocate a new Select structure and return a pointer to that
** structure.
*/
................................................................................
** same column in table number iTo.
*/
static void changeTables(Expr *pExpr, int iFrom, int iTo){
  if( pExpr==0 ) return;
  if( pExpr->op==TK_COLUMN && pExpr->iTable==iFrom ){
    pExpr->iTable = iTo;
  }else{

    changeTables(pExpr->pLeft, iFrom, iTo);
    changeTables(pExpr->pRight, iFrom, iTo);




    if( pExpr->pList ){
      int i;
      for(i=0; i<pExpr->pList->nExpr; i++){
        changeTables(pExpr->pList->a[i].pExpr, iFrom, iTo);
      }
    }
  }
}

/*
** Scan through the expression pExpr.  Replace every reference to
** a column in table number iTable with a copy of the corresponding
................................................................................
    pExpr->op = pNew->op;
    pExpr->pLeft = sqliteExprDup(pNew->pLeft);
    pExpr->pRight = sqliteExprDup(pNew->pRight);
    pExpr->pList = sqliteExprListDup(pNew->pList);
    pExpr->iTable = pNew->iTable;
    pExpr->iColumn = pNew->iColumn;
    pExpr->iAgg = pNew->iAgg;

    if( iSub!=iTable ){
      changeTables(pExpr, iSub, iTable);
    }
  }else{
    static void substExprList(ExprList*,int,ExprList*,int);
    substExpr(pExpr->pLeft, iTable, pEList, iSub);
    substExpr(pExpr->pRight, iTable, pEList, iSub);
................................................................................
  pList = p->pEList;
  for(i=0; i<pList->nExpr; i++){
    if( pList->a[i].zName==0 ){
      Expr *pExpr = pList->a[i].pExpr;
      pList->a[i].zName = sqliteStrNDup(pExpr->span.z, pExpr->span.n);
    }
  }

  substExprList(p->pGroupBy, iParent, pSub->pEList, iSub);
  substExpr(p->pHaving, iParent, pSub->pEList, iSub);

  substExprList(p->pOrderBy, iParent, pSub->pEList, iSub);
  if( pSub->pWhere ){
    pWhere = sqliteExprDup(pSub->pWhere);
    if( iParent!=iSub ){
      changeTables(pWhere, iSub, iParent);
    }
  }else{
    pWhere = 0;
  }
  if( subqueryIsAgg ){
    assert( p->pHaving==0 );
    p->pHaving = pWhere;

    substExpr(p->pHaving, iParent, pSub->pEList, iSub);
















  }else if( p->pWhere==0 ){
    p->pWhere = pWhere;
  }else{
    substExpr(p->pWhere, iParent, pSub->pEList, iSub);
    if( pWhere ){
      p->pWhere = sqliteExpr(TK_AND, p->pWhere, pWhere, 0);
    }
................................................................................
**
** This routine returns the number of errors.  If any errors are
** encountered, then an appropriate error message is left in
** pParse->zErrMsg.
**
** This routine does NOT free the Select structure passed in.  The
** calling function needs to do that.







*/
int sqliteSelect(
  Parse *pParse,         /* The parser context */
  Select *p,             /* The SELECT statement being coded. */
  int eDest,             /* One of: SRT_Callback Mem Set Union Except */
  int iParm,             /* Save result in this memory location, if >=0 */
  Select *pParent,       /* Another SELECT for which this is a sub-query */
  int parentTab,         /* Index in pParent->pSrc of this query */
  int parentAgg          /* True if pParent uses aggregate functions */
){
  int i;
  WhereInfo *pWInfo;
  Vdbe *v;
  int isAgg = 0;         /* True for select lists like "count(*)" */
  ExprList *pEList;      /* List of columns to extract. */
  IdList *pTabList;      /* List of tables to select from */
................................................................................

  /* Generate code for all sub-queries in the FROM clause
  */
  for(i=0; i<pTabList->nId; i++){
    if( pTabList->a[i].pSelect==0 ) continue;
    sqliteVdbeAddOp(v, OP_OpenTemp, base+i, 0);
    sqliteSelect(pParse, pTabList->a[i].pSelect, SRT_Table, base+i,
                 p, i, isAgg);






  }

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

  if( flattenSubquery(pParent, parentTab, parentAgg, isAgg) ){

    return rc;
  }
  pTabList = p->pSrc;
  pWhere = p->pWhere;
  pOrderBy = p->pOrderBy;
  pGroupBy = p->pGroupBy;
  pHaving = p->pHaving;
  isDistinct = p->isDistinct;

  /* Do an analysis of aggregate expressions.
  */
  sqliteAggregateInfoReset(pParse);
  if( isAgg ){
    assert( pParse->nAgg==0 );
    for(i=0; i<pEList->nExpr; i++){







|







 







>


>
>
>
>
|
|
|
|
<







 







>







 







>
|
|
>











|
>

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







 







>
>
>
>
>
>
>








|







 







|
>
>
>
>
>
>





>
|
>


<
<
<
<
<
<







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773

774
775
776
777
778
779
780
...
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
...
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
....
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
....
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304






1305
1306
1307
1308
1309
1310
1311
**    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.72 2002/03/03 02:49:51 drh Exp $
*/
#include "sqliteInt.h"

/*
** Allocate a new Select structure and return a pointer to that
** structure.
*/
................................................................................
** same column in table number iTo.
*/
static void changeTables(Expr *pExpr, int iFrom, int iTo){
  if( pExpr==0 ) return;
  if( pExpr->op==TK_COLUMN && pExpr->iTable==iFrom ){
    pExpr->iTable = iTo;
  }else{
    static void changeTablesInList(ExprList*, int, int);
    changeTables(pExpr->pLeft, iFrom, iTo);
    changeTables(pExpr->pRight, iFrom, iTo);
    changeTablesInList(pExpr->pList, iFrom, iTo);
  }
}
static void changeTablesInList(ExprList *pList, int iFrom, int iTo){
  if( pList ){
    int i;
    for(i=0; i<pList->nExpr; i++){
      changeTables(pList->a[i].pExpr, iFrom, iTo);

    }
  }
}

/*
** Scan through the expression pExpr.  Replace every reference to
** a column in table number iTable with a copy of the corresponding
................................................................................
    pExpr->op = pNew->op;
    pExpr->pLeft = sqliteExprDup(pNew->pLeft);
    pExpr->pRight = sqliteExprDup(pNew->pRight);
    pExpr->pList = sqliteExprListDup(pNew->pList);
    pExpr->iTable = pNew->iTable;
    pExpr->iColumn = pNew->iColumn;
    pExpr->iAgg = pNew->iAgg;
    pExpr->token = pNew->token;
    if( iSub!=iTable ){
      changeTables(pExpr, iSub, iTable);
    }
  }else{
    static void substExprList(ExprList*,int,ExprList*,int);
    substExpr(pExpr->pLeft, iTable, pEList, iSub);
    substExpr(pExpr->pRight, iTable, pEList, iSub);
................................................................................
  pList = p->pEList;
  for(i=0; i<pList->nExpr; i++){
    if( pList->a[i].zName==0 ){
      Expr *pExpr = pList->a[i].pExpr;
      pList->a[i].zName = sqliteStrNDup(pExpr->span.z, pExpr->span.n);
    }
  }
  if( isAgg ){
    substExprList(p->pGroupBy, iParent, pSub->pEList, iSub);
    substExpr(p->pHaving, iParent, pSub->pEList, iSub);
  }
  substExprList(p->pOrderBy, iParent, pSub->pEList, iSub);
  if( pSub->pWhere ){
    pWhere = sqliteExprDup(pSub->pWhere);
    if( iParent!=iSub ){
      changeTables(pWhere, iSub, iParent);
    }
  }else{
    pWhere = 0;
  }
  if( subqueryIsAgg ){
    assert( p->pHaving==0 );
    p->pHaving = p->pWhere;
    p->pWhere = pWhere;
    substExpr(p->pHaving, iParent, pSub->pEList, iSub);
    if( pSub->pHaving ){
      Expr *pHaving = sqliteExprDup(pSub->pHaving);
      if( iParent!=iSub ){
        changeTables(pHaving, iSub, iParent);
      }
      if( p->pHaving ){
        p->pHaving = sqliteExpr(TK_AND, p->pHaving, pHaving, 0);
      }else{
        p->pHaving = pHaving;
      }
    }
    assert( p->pGroupBy==0 );
    p->pGroupBy = sqliteExprListDup(pSub->pGroupBy);
    if( iParent!=iSub ){
      changeTablesInList(p->pGroupBy, iSub, iParent);
    }
  }else if( p->pWhere==0 ){
    p->pWhere = pWhere;
  }else{
    substExpr(p->pWhere, iParent, pSub->pEList, iSub);
    if( pWhere ){
      p->pWhere = sqliteExpr(TK_AND, p->pWhere, pWhere, 0);
    }
................................................................................
**
** This routine returns the number of errors.  If any errors are
** encountered, then an appropriate error message is left in
** pParse->zErrMsg.
**
** This routine does NOT free the Select structure passed in.  The
** calling function needs to do that.
**
** The pParent, parentTab, and *pParentAgg fields are filled in if this
** SELECT is a subquery.  This routine may try to combine this SELECT
** with its parent to form a single flat query.  In so doing, it might
** change the parent query from a non-aggregate to an aggregate query.
** For that reason, the pParentAgg flag is passed as a pointer, so it
** can be changed.
*/
int sqliteSelect(
  Parse *pParse,         /* The parser context */
  Select *p,             /* The SELECT statement being coded. */
  int eDest,             /* One of: SRT_Callback Mem Set Union Except */
  int iParm,             /* Save result in this memory location, if >=0 */
  Select *pParent,       /* Another SELECT for which this is a sub-query */
  int parentTab,         /* Index in pParent->pSrc of this query */
  int *pParentAgg        /* True if pParent uses aggregate functions */
){
  int i;
  WhereInfo *pWInfo;
  Vdbe *v;
  int isAgg = 0;         /* True for select lists like "count(*)" */
  ExprList *pEList;      /* List of columns to extract. */
  IdList *pTabList;      /* List of tables to select from */
................................................................................

  /* Generate code for all sub-queries in the FROM clause
  */
  for(i=0; i<pTabList->nId; i++){
    if( pTabList->a[i].pSelect==0 ) continue;
    sqliteVdbeAddOp(v, OP_OpenTemp, base+i, 0);
    sqliteSelect(pParse, pTabList->a[i].pSelect, SRT_Table, base+i,
                 p, i, &isAgg);
    pTabList = p->pSrc;
    pWhere = p->pWhere;
    pOrderBy = p->pOrderBy;
    pGroupBy = p->pGroupBy;
    pHaving = p->pHaving;
    isDistinct = p->isDistinct;
  }

  /* 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.  
  */
  if( pParent && pParentAgg &&
      flattenSubquery(pParent, parentTab, *pParentAgg, isAgg) ){
    if( isAgg ) *pParentAgg = 1;
    return rc;
  }







  /* Do an analysis of aggregate expressions.
  */
  sqliteAggregateInfoReset(pParse);
  if( isAgg ){
    assert( pParse->nAgg==0 );
    for(i=0; i<pEList->nExpr; i++){

Changes to src/sqliteInt.h.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
**    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.97 2002/03/02 17:04:08 drh Exp $
*/
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
#include <stdio.h>
................................................................................
void sqliteDeleteTable(sqlite*, Table*);
void sqliteInsert(Parse*, Token*, ExprList*, Select*, IdList*, int);
IdList *sqliteIdListAppend(IdList*, Token*);
void sqliteIdListAddAlias(IdList*, Token*);
void sqliteIdListDelete(IdList*);
void sqliteCreateIndex(Parse*, Token*, Token*, IdList*, int, Token*, Token*);
void sqliteDropIndex(Parse*, Token*);
int sqliteSelect(Parse*, Select*, int, int, Select*, int, int);
Select *sqliteSelectNew(ExprList*,IdList*,Expr*,ExprList*,Expr*,ExprList*,
                        int,int,int);
void sqliteSelectDelete(Select*);
void sqliteSelectUnbind(Select*);
Table *sqliteTableNameToTable(Parse*, const char*);
IdList *sqliteTableTokenToIdList(Parse*, Token*);
void sqliteDeleteFrom(Parse*, Token*, Expr*);







|







 







|







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
**    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.98 2002/03/03 02:49:51 drh Exp $
*/
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
#include <stdio.h>
................................................................................
void sqliteDeleteTable(sqlite*, Table*);
void sqliteInsert(Parse*, Token*, ExprList*, Select*, IdList*, int);
IdList *sqliteIdListAppend(IdList*, Token*);
void sqliteIdListAddAlias(IdList*, Token*);
void sqliteIdListDelete(IdList*);
void sqliteCreateIndex(Parse*, Token*, Token*, IdList*, int, Token*, Token*);
void sqliteDropIndex(Parse*, Token*);
int sqliteSelect(Parse*, Select*, int, int, Select*, int, int*);
Select *sqliteSelectNew(ExprList*,IdList*,Expr*,ExprList*,Expr*,ExprList*,
                        int,int,int);
void sqliteSelectDelete(Select*);
void sqliteSelectUnbind(Select*);
Table *sqliteTableNameToTable(Parse*, const char*);
IdList *sqliteTableTokenToIdList(Parse*, Token*);
void sqliteDeleteFrom(Parse*, Token*, Expr*);

Changes to src/vdbe.c.

26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
....
1092
1093
1094
1095
1096
1097
1098

1099
1100
1101
1102
1103
1104
1105
....
1112
1113
1114
1115
1116
1117
1118




1119

1120
1121
1122
1123
1124
1125
1126
** type to the other occurs as necessary.
** 
** Most of the code in this file is taken up by the sqliteVdbeExec()
** function which does the work of interpreting a VDBE program.
** But other routines are also provided to help in building up
** a program instruction by instruction.
**
** $Id: vdbe.c,v 1.129 2002/02/28 03:31:11 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** The following global variable is incremented every time a cursor
** moves, either by the OP_MoveTo or the OP_Next opcode.  The test
................................................................................
  char **pzErrMsg            /* Error msg written here */
){
  int i, rc;
  char *azValue[6];
  char zAddr[20];
  char zP1[20];
  char zP2[20];

  static char *azColumnNames[] = {
     "addr", "opcode", "p1", "p2", "p3", 0
  };

  if( xCallback==0 ) return 0;
  azValue[0] = zAddr;
  azValue[2] = zP1;
................................................................................
      sqliteSetString(pzErrMsg, "interrupted", 0);
      rc = SQLITE_INTERRUPT;
      break;
    }
    sprintf(zAddr,"%d",i);
    sprintf(zP1,"%d", p->aOp[i].p1);
    sprintf(zP2,"%d", p->aOp[i].p2);




    azValue[4] = p->aOp[i].p3type!=P3_POINTER ? p->aOp[i].p3 : "";

    azValue[1] = zOpName[p->aOp[i].opcode];
    if( xCallback(pArg, 5, azValue, azColumnNames) ){
      rc = SQLITE_ABORT;
    }
  }
  return rc;
}







|







 







>







 







>
>
>
>
|
>







26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
....
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
....
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
** type to the other occurs as necessary.
** 
** Most of the code in this file is taken up by the sqliteVdbeExec()
** function which does the work of interpreting a VDBE program.
** But other routines are also provided to help in building up
** a program instruction by instruction.
**
** $Id: vdbe.c,v 1.130 2002/03/03 02:49:51 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** The following global variable is incremented every time a cursor
** moves, either by the OP_MoveTo or the OP_Next opcode.  The test
................................................................................
  char **pzErrMsg            /* Error msg written here */
){
  int i, rc;
  char *azValue[6];
  char zAddr[20];
  char zP1[20];
  char zP2[20];
  char zP3[40];
  static char *azColumnNames[] = {
     "addr", "opcode", "p1", "p2", "p3", 0
  };

  if( xCallback==0 ) return 0;
  azValue[0] = zAddr;
  azValue[2] = zP1;
................................................................................
      sqliteSetString(pzErrMsg, "interrupted", 0);
      rc = SQLITE_INTERRUPT;
      break;
    }
    sprintf(zAddr,"%d",i);
    sprintf(zP1,"%d", p->aOp[i].p1);
    sprintf(zP2,"%d", p->aOp[i].p2);
    if( p->aOp[i].p3type==P3_POINTER ){
      sprintf(zP3, "ptr(%#x)", (int)p->aOp[i].p3);
      azValue[4] = zP3;
    }else{
      azValue[4] = p->aOp[i].p3;
    }
    azValue[1] = zOpName[p->aOp[i].opcode];
    if( xCallback(pArg, 5, azValue, azColumnNames) ){
      rc = SQLITE_ABORT;
    }
  }
  return rc;
}

Changes to test/insert2.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
108
109
110
111
112
113
114
115
116
117
118
119
120

































































121
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the INSERT statement that takes is
# result from a SELECT.
#
# $Id: insert2.test,v 1.7 2002/02/19 13:39:23 drh Exp $

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

# Create some tables with data that we can select against
#
do_test insert2-1.0 {
................................................................................
  execsql {
    DELETE FROM t3;
    INSERT INTO t3(c,a,b) SELECT x, 'hi', y FROM t4;
    SELECT * FROM t3;
  }
} {hi 2 1}

do_test insert2-4.0 {
  set x [execsql {PRAGMA integrity_check}]
  if {$x==""} {set x ok}
  set x
} {ok}


































































finish_test







|







 







|





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

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
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
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the INSERT statement that takes is
# result from a SELECT.
#
# $Id: insert2.test,v 1.8 2002/03/03 02:49:52 drh Exp $

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

# Create some tables with data that we can select against
#
do_test insert2-1.0 {
................................................................................
  execsql {
    DELETE FROM t3;
    INSERT INTO t3(c,a,b) SELECT x, 'hi', y FROM t4;
    SELECT * FROM t3;
  }
} {hi 2 1}

do_test insert2-3.0 {
  set x [execsql {PRAGMA integrity_check}]
  if {$x==""} {set x ok}
  set x
} {ok}

# File table t4 with lots of data
#
do_test insert2-3.1 {
  execsql {
    SELECT * from t4;
  }
} {1 2}
do_test insert2-3.2 {
  execsql {
    BEGIN;
    INSERT INTO t4 VALUES(2,4);
    INSERT INTO t4 VALUES(3,6);
    INSERT INTO t4 VALUES(4,8);
    INSERT INTO t4 VALUES(5,10);
    INSERT INTO t4 VALUES(6,12);
    INSERT INTO t4 VALUES(7,14);
    INSERT INTO t4 VALUES(8,16);
    INSERT INTO t4 VALUES(9,18);
    INSERT INTO t4 VALUES(10,20);
    COMMIT;
    SELECT count(*) FROM t4;
  }
} {10}
do_test insert2-3.3 {
  execsql {
    BEGIN;
    INSERT INTO t4 SELECT x+(SELECT max(x) FROM t4),y FROM t4;
    INSERT INTO t4 SELECT x+(SELECT max(x) FROM t4),y FROM t4;
    INSERT INTO t4 SELECT x+(SELECT max(x) FROM t4),y FROM t4;
    INSERT INTO t4 SELECT x+(SELECT max(x) FROM t4),y FROM t4;
    COMMIT;
    SELECT count(*) FROM t4;
  }
} {160}
do_test insert2-3.4 {
  execsql {
    BEGIN;
    UPDATE t4 SET y='lots of data for the row where x=' || x
                     || ' and y=' || y || ' - even more data to fill space';
    COMMIT;
    SELECT count(*) FROM t4;
  }
} {160}
do_test insert2-3.5 {
  execsql {
    BEGIN;
    INSERT INTO t4 SELECT x+(SELECT max(x)+1 FROM t4),y FROM t4;
    SELECT count(*) from t4;
    ROLLBACK;
  }
} {320}
do_test insert2-3.6 {
  execsql {
    SELECT count(*) FROM t4;
  }
} {160}
do_test insert2-3.7 {
  execsql {
    BEGIN;
    DELETE FROM t4 WHERE x!=123;
    SELECT count(*) FROM t4;
    ROLLBACK;
  }
} {1}

finish_test

Changes to test/select6.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
162
163
164
165
166
167
168



































































































169
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing SELECT statements that contain
# subqueries in their FROM clause.
#
# $Id: select6.test,v 1.2 2002/02/18 13:35:33 drh Exp $

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

do_test select6-1.0 {
  execsql {
    BEGIN;
................................................................................
      (SELECT a.q, a.p, b.r
       FROM (SELECT count(*) as p , b as q FROM t2 GROUP BY q) AS a,
            (SELECT max(a) as r, b as s FROM t2 GROUP BY s) as b
       WHERE a.q=b.s ORDER BY a.q)
    ORDER BY [a.q]
  }
} {1 1 1 2 2 3 3 4 7 4 8 15 5 5 20}



































































































finish_test







|







 







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

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing SELECT statements that contain
# subqueries in their FROM clause.
#
# $Id: select6.test,v 1.3 2002/03/03 02:49:52 drh Exp $

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

do_test select6-1.0 {
  execsql {
    BEGIN;
................................................................................
      (SELECT a.q, a.p, b.r
       FROM (SELECT count(*) as p , b as q FROM t2 GROUP BY q) AS a,
            (SELECT max(a) as r, b as s FROM t2 GROUP BY s) as b
       WHERE a.q=b.s ORDER BY a.q)
    ORDER BY [a.q]
  }
} {1 1 1 2 2 3 3 4 7 4 8 15 5 5 20}

do_test select6-3.3 {
  execsql {
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1)
  }
} {10.5 3.7 14.2}
do_test select6-3.4 {
  execsql {
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1 WHERE y=4)
  }
} {11.5 4 15.5}
do_test select6-3.5 {
  execsql {
    SELECT x,y,x+y FROM (SELECT avg(a) as 'x', avg(b) as 'y' FROM t2 WHERE a=4)
  }
} {4 3 7}
do_test select6-3.6 {
  execsql {
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1)
    WHERE a>10
  }
} {10.5 3.7 14.2}
do_test select6-3.7 {
  execsql {
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1)
    WHERE a<10
  }
} {}
do_test select6-3.8 {
  execsql {
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1 WHERE y=4)
    WHERE a>10
  }
} {11.5 4 15.5}
do_test select6-3.9 {
  execsql {
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1 WHERE y=4)
    WHERE a<10
  }
} {}
do_test select6-3.10 {
  execsql {
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b)
    ORDER BY a
  }
} {1 1 2 2.5 2 4.5 5.5 3 8.5 11.5 4 15.5 18 5 23}
do_test select6-3.11 {
  execsql {
    SELECT a,b,a+b FROM 
       (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b)
    WHERE b<4 ORDER BY a
  }
} {1 1 2 2.5 2 4.5 5.5 3 8.5}
do_test select6-3.12 {
  execsql {
    SELECT a,b,a+b FROM 
       (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b HAVING a>1)
    WHERE b<4 ORDER BY a
  }
} {2.5 2 4.5 5.5 3 8.5}
do_test select6-3.13 {
  execsql {
    SELECT a,b,a+b FROM 
       (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b HAVING a>1)
    ORDER BY a
  }
} {2.5 2 4.5 5.5 3 8.5 11.5 4 15.5 18 5 23}
do_test select6-3.14 {
  execsql {
    SELECT [count(*)],y FROM (SELECT count(*), y FROM t1 GROUP BY y)
    ORDER BY [count(*)]
  }
} {1 1 2 2 4 3 5 5 8 4}
do_test select6-3.15 {
  execsql {
    SELECT [count(*)],y FROM (SELECT count(*), y FROM t1 GROUP BY y)
    ORDER BY y
  }
} {1 1 2 2 4 3 8 4 5 5}

do_test select6-4.1 {
  execsql {
    SELECT a,b,c FROM 
      (SELECT x AS 'a', y AS 'b', x+y AS 'c' FROM t1 WHERE y=4)
    WHERE a<10 ORDER BY a;
  }
} {8 4 12 9 4 13}
do_test select6-4.2 {
  execsql {
    SELECT y FROM (SELECT DISTINCT y FROM t1) WHERE y<5 ORDER BY y
  }
} {1 2 3 4}
do_test select6-4.3 {
  execsql {
    SELECT DISTINCT y FROM (SELECT y FROM t1) WHERE y<5 ORDER BY y
  }
} {1 2 3 4}


finish_test

Changes to test/vacuum.test.

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
#    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 file is testing the VACUUM statement.
#
# $Id: vacuum.test,v 1.6 2001/09/16 00:13:28 drh Exp $

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

if {0} {


# Try to vacuum a non-existant table.
#
do_test vacuum-1.1 {
  set v [catch {execsql {VACUUM dummy1}} msg]
  lappend v $msg
} {1 {no such table or index: dummy1}}

# It is OK to vacuum sqlite_master...
#
do_test vacuum-1.2 {
  set v [catch {execsql {VACUUM sqlite_master}} msg]
  lappend v $msg
} {0 {}}

# Create some tables and indices to test against.
#
execsql {CREATE TABLE test1(a int)}
execsql {CREATE TABLE test2(b int)}
execsql {CREATE INDEX index1 ON test1(a)}
execsql {INSERT INTO test1 VALUES(1)}
execsql {INSERT INTO test1 VALUES(1)}
execsql {INSERT INTO test1 VALUES(2)}
execsql {INSERT INTO test1 VALUES(3)}
execsql {INSERT INTO test2 VALUES(4)}

do_test vacuum-1.3 {
  set b1 [file mtime testdb/test1.tbl]
  set b2 [file mtime testdb/test2.tbl]
  set b3 [file mtime testdb/index1.tbl]
  after 1000
  execsql {VACUUM test1}
  set a1 [file mtime testdb/test1.tbl]
  set a2 [file mtime testdb/test2.tbl]
  set a3 [file mtime testdb/index1.tbl]
  expr {$a1>$b1 && $a2==$b2 && $a3==$b3}
} {1}
if {$::tcl_platform(platform)!="windows"} {
do_test vacuum-1.4 {
  set b1 [file mtime testdb/test1.tbl]
  set b2 [file mtime testdb/test2.tbl]
  set b3 [file mtime testdb/index1.tbl]
  after 1000
  execsql {VACUUM}
  set a1 [file mtime testdb/test1.tbl]
  set a2 [file mtime testdb/test2.tbl]
  set a3 [file mtime testdb/index1.tbl]
  expr {$a1>$b1 && $a2>$b2 && $a3>$b3}
} {1}
} ;# End if( platform!=windows )

finish_test

}







|




|
>

<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
|
<
<
7
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 implements regression tests for SQLite library.  The
# focus of this file is testing the VACUUM statement.
#
# $Id: vacuum.test,v 1.7 2002/03/03 02:49:52 drh Exp $

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

# The vacuum command no longer functions.  There is
# nothing to test.



















































# finish_test


Changes to tool/lemon.c.

3099
3100
3101
3102
3103
3104
3105

3106

3107
3108
3109
3110
3111
3112

3113
3114
3115
3116
3117
3118
3119
        collide[j] = k;
        table[j]->collide = 0;
        if( k<j ) j = k-1;
      }
    }

    /* Print the hash table */

    fprintf(out,"/* State %d */\n",stp->index); lineno++;

    for(j=0; j<tablesize; j++){
      assert( table[j]!=0 );
      fprintf(out,"  {%4d,%4d,%4d}, /* ",
          table[j]->sp->index,
          collide[j]+1,
          compute_action(lemp,table[j]));

      PrintAction(table[j],out,22);
      fprintf(out," */\n"); 
      lineno++;
    }

    /* Update the table count */
    tablecnt += tablesize;







>
|
>


|


|
>







3099
3100
3101
3102
3103
3104
3105
3106
3107
3108
3109
3110
3111
3112
3113
3114
3115
3116
3117
3118
3119
3120
3121
3122
        collide[j] = k;
        table[j]->collide = 0;
        if( k<j ) j = k-1;
      }
    }

    /* Print the hash table */
    if( tablesize>0 ){
      fprintf(out,"/* State %d */\n",stp->index); lineno++;
    }
    for(j=0; j<tablesize; j++){
      assert( table[j]!=0 );
      fprintf(out,"  {%4d,%4d,%4d}, /* %2d: ",
          table[j]->sp->index,
          collide[j]+1,
          compute_action(lemp,table[j]),
          j+1);
      PrintAction(table[j],out,22);
      fprintf(out," */\n"); 
      lineno++;
    }

    /* Update the table count */
    tablecnt += tablesize;