/ 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 Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains C code routines that are called by the parser
    13     13   ** to handle SELECT statements in SQLite.
    14     14   **
    15         -** $Id: select.c,v 1.71 2002/03/02 17:04:08 drh Exp $
           15  +** $Id: select.c,v 1.72 2002/03/03 02:49:51 drh Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   
    19     19   /*
    20     20   ** Allocate a new Select structure and return a pointer to that
    21     21   ** structure.
    22     22   */
................................................................................
   756    756   ** same column in table number iTo.
   757    757   */
   758    758   static void changeTables(Expr *pExpr, int iFrom, int iTo){
   759    759     if( pExpr==0 ) return;
   760    760     if( pExpr->op==TK_COLUMN && pExpr->iTable==iFrom ){
   761    761       pExpr->iTable = iTo;
   762    762     }else{
          763  +    static void changeTablesInList(ExprList*, int, int);
   763    764       changeTables(pExpr->pLeft, iFrom, iTo);
   764    765       changeTables(pExpr->pRight, iFrom, iTo);
   765         -    if( pExpr->pList ){
   766         -      int i;
   767         -      for(i=0; i<pExpr->pList->nExpr; i++){
   768         -        changeTables(pExpr->pList->a[i].pExpr, iFrom, iTo);
   769         -      }
          766  +    changeTablesInList(pExpr->pList, iFrom, iTo);
          767  +  }
          768  +}
          769  +static void changeTablesInList(ExprList *pList, int iFrom, int iTo){
          770  +  if( pList ){
          771  +    int i;
          772  +    for(i=0; i<pList->nExpr; i++){
          773  +      changeTables(pList->a[i].pExpr, iFrom, iTo);
   770    774       }
   771    775     }
   772    776   }
   773    777   
   774    778   /*
   775    779   ** Scan through the expression pExpr.  Replace every reference to
   776    780   ** a column in table number iTable with a copy of the corresponding
................................................................................
   795    799       pExpr->op = pNew->op;
   796    800       pExpr->pLeft = sqliteExprDup(pNew->pLeft);
   797    801       pExpr->pRight = sqliteExprDup(pNew->pRight);
   798    802       pExpr->pList = sqliteExprListDup(pNew->pList);
   799    803       pExpr->iTable = pNew->iTable;
   800    804       pExpr->iColumn = pNew->iColumn;
   801    805       pExpr->iAgg = pNew->iAgg;
          806  +    pExpr->token = pNew->token;
   802    807       if( iSub!=iTable ){
   803    808         changeTables(pExpr, iSub, iTable);
   804    809       }
   805    810     }else{
   806    811       static void substExprList(ExprList*,int,ExprList*,int);
   807    812       substExpr(pExpr->pLeft, iTable, pEList, iSub);
   808    813       substExpr(pExpr->pRight, iTable, pEList, iSub);
................................................................................
   904    909     pList = p->pEList;
   905    910     for(i=0; i<pList->nExpr; i++){
   906    911       if( pList->a[i].zName==0 ){
   907    912         Expr *pExpr = pList->a[i].pExpr;
   908    913         pList->a[i].zName = sqliteStrNDup(pExpr->span.z, pExpr->span.n);
   909    914       }
   910    915     }
   911         -  substExprList(p->pGroupBy, iParent, pSub->pEList, iSub);
   912         -  substExpr(p->pHaving, iParent, pSub->pEList, iSub);
          916  +  if( isAgg ){
          917  +    substExprList(p->pGroupBy, iParent, pSub->pEList, iSub);
          918  +    substExpr(p->pHaving, iParent, pSub->pEList, iSub);
          919  +  }
   913    920     substExprList(p->pOrderBy, iParent, pSub->pEList, iSub);
   914    921     if( pSub->pWhere ){
   915    922       pWhere = sqliteExprDup(pSub->pWhere);
   916    923       if( iParent!=iSub ){
   917    924         changeTables(pWhere, iSub, iParent);
   918    925       }
   919    926     }else{
   920    927       pWhere = 0;
   921    928     }
   922    929     if( subqueryIsAgg ){
   923    930       assert( p->pHaving==0 );
   924         -    p->pHaving = pWhere;
          931  +    p->pHaving = p->pWhere;
          932  +    p->pWhere = pWhere;
   925    933       substExpr(p->pHaving, iParent, pSub->pEList, iSub);
          934  +    if( pSub->pHaving ){
          935  +      Expr *pHaving = sqliteExprDup(pSub->pHaving);
          936  +      if( iParent!=iSub ){
          937  +        changeTables(pHaving, iSub, iParent);
          938  +      }
          939  +      if( p->pHaving ){
          940  +        p->pHaving = sqliteExpr(TK_AND, p->pHaving, pHaving, 0);
          941  +      }else{
          942  +        p->pHaving = pHaving;
          943  +      }
          944  +    }
          945  +    assert( p->pGroupBy==0 );
          946  +    p->pGroupBy = sqliteExprListDup(pSub->pGroupBy);
          947  +    if( iParent!=iSub ){
          948  +      changeTablesInList(p->pGroupBy, iSub, iParent);
          949  +    }
   926    950     }else if( p->pWhere==0 ){
   927    951       p->pWhere = pWhere;
   928    952     }else{
   929    953       substExpr(p->pWhere, iParent, pSub->pEList, iSub);
   930    954       if( pWhere ){
   931    955         p->pWhere = sqliteExpr(TK_AND, p->pWhere, pWhere, 0);
   932    956       }
................................................................................
  1079   1103   **
  1080   1104   ** This routine returns the number of errors.  If any errors are
  1081   1105   ** encountered, then an appropriate error message is left in
  1082   1106   ** pParse->zErrMsg.
  1083   1107   **
  1084   1108   ** This routine does NOT free the Select structure passed in.  The
  1085   1109   ** calling function needs to do that.
         1110  +**
         1111  +** The pParent, parentTab, and *pParentAgg fields are filled in if this
         1112  +** SELECT is a subquery.  This routine may try to combine this SELECT
         1113  +** with its parent to form a single flat query.  In so doing, it might
         1114  +** change the parent query from a non-aggregate to an aggregate query.
         1115  +** For that reason, the pParentAgg flag is passed as a pointer, so it
         1116  +** can be changed.
  1086   1117   */
  1087   1118   int sqliteSelect(
  1088   1119     Parse *pParse,         /* The parser context */
  1089   1120     Select *p,             /* The SELECT statement being coded. */
  1090   1121     int eDest,             /* One of: SRT_Callback Mem Set Union Except */
  1091   1122     int iParm,             /* Save result in this memory location, if >=0 */
  1092   1123     Select *pParent,       /* Another SELECT for which this is a sub-query */
  1093   1124     int parentTab,         /* Index in pParent->pSrc of this query */
  1094         -  int parentAgg          /* True if pParent uses aggregate functions */
         1125  +  int *pParentAgg        /* True if pParent uses aggregate functions */
  1095   1126   ){
  1096   1127     int i;
  1097   1128     WhereInfo *pWInfo;
  1098   1129     Vdbe *v;
  1099   1130     int isAgg = 0;         /* True for select lists like "count(*)" */
  1100   1131     ExprList *pEList;      /* List of columns to extract. */
  1101   1132     IdList *pTabList;      /* List of tables to select from */
................................................................................
  1250   1281   
  1251   1282     /* Generate code for all sub-queries in the FROM clause
  1252   1283     */
  1253   1284     for(i=0; i<pTabList->nId; i++){
  1254   1285       if( pTabList->a[i].pSelect==0 ) continue;
  1255   1286       sqliteVdbeAddOp(v, OP_OpenTemp, base+i, 0);
  1256   1287       sqliteSelect(pParse, pTabList->a[i].pSelect, SRT_Table, base+i,
  1257         -                 p, i, isAgg);
         1288  +                 p, i, &isAgg);
         1289  +    pTabList = p->pSrc;
         1290  +    pWhere = p->pWhere;
         1291  +    pOrderBy = p->pOrderBy;
         1292  +    pGroupBy = p->pGroupBy;
         1293  +    pHaving = p->pHaving;
         1294  +    isDistinct = p->isDistinct;
  1258   1295     }
  1259   1296   
  1260   1297     /* Check to see if this is a subquery that can be "flattened" into its parent.
  1261   1298     ** If flattening is a possiblity, do so and return immediately.  
  1262   1299     */
  1263         -  if( flattenSubquery(pParent, parentTab, parentAgg, isAgg) ){
         1300  +  if( pParent && pParentAgg &&
         1301  +      flattenSubquery(pParent, parentTab, *pParentAgg, isAgg) ){
         1302  +    if( isAgg ) *pParentAgg = 1;
  1264   1303       return rc;
  1265   1304     }
  1266         -  pTabList = p->pSrc;
  1267         -  pWhere = p->pWhere;
  1268         -  pOrderBy = p->pOrderBy;
  1269         -  pGroupBy = p->pGroupBy;
  1270         -  pHaving = p->pHaving;
  1271         -  isDistinct = p->isDistinct;
  1272   1305   
  1273   1306     /* Do an analysis of aggregate expressions.
  1274   1307     */
  1275   1308     sqliteAggregateInfoReset(pParse);
  1276   1309     if( isAgg ){
  1277   1310       assert( pParse->nAgg==0 );
  1278   1311       for(i=0; i<pEList->nExpr; i++){

Changes to src/sqliteInt.h.

     7      7   **    May you do good and not evil.
     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** Internal interface definitions for SQLite.
    13     13   **
    14         -** @(#) $Id: sqliteInt.h,v 1.97 2002/03/02 17:04:08 drh Exp $
           14  +** @(#) $Id: sqliteInt.h,v 1.98 2002/03/03 02:49:51 drh Exp $
    15     15   */
    16     16   #include "sqlite.h"
    17     17   #include "hash.h"
    18     18   #include "vdbe.h"
    19     19   #include "parse.h"
    20     20   #include "btree.h"
    21     21   #include <stdio.h>
................................................................................
   585    585   void sqliteDeleteTable(sqlite*, Table*);
   586    586   void sqliteInsert(Parse*, Token*, ExprList*, Select*, IdList*, int);
   587    587   IdList *sqliteIdListAppend(IdList*, Token*);
   588    588   void sqliteIdListAddAlias(IdList*, Token*);
   589    589   void sqliteIdListDelete(IdList*);
   590    590   void sqliteCreateIndex(Parse*, Token*, Token*, IdList*, int, Token*, Token*);
   591    591   void sqliteDropIndex(Parse*, Token*);
   592         -int sqliteSelect(Parse*, Select*, int, int, Select*, int, int);
          592  +int sqliteSelect(Parse*, Select*, int, int, Select*, int, int*);
   593    593   Select *sqliteSelectNew(ExprList*,IdList*,Expr*,ExprList*,Expr*,ExprList*,
   594    594                           int,int,int);
   595    595   void sqliteSelectDelete(Select*);
   596    596   void sqliteSelectUnbind(Select*);
   597    597   Table *sqliteTableNameToTable(Parse*, const char*);
   598    598   IdList *sqliteTableTokenToIdList(Parse*, Token*);
   599    599   void sqliteDeleteFrom(Parse*, Token*, Expr*);

Changes to src/vdbe.c.

    26     26   ** type to the other occurs as necessary.
    27     27   ** 
    28     28   ** Most of the code in this file is taken up by the sqliteVdbeExec()
    29     29   ** function which does the work of interpreting a VDBE program.
    30     30   ** But other routines are also provided to help in building up
    31     31   ** a program instruction by instruction.
    32     32   **
    33         -** $Id: vdbe.c,v 1.129 2002/02/28 03:31:11 drh Exp $
           33  +** $Id: vdbe.c,v 1.130 2002/03/03 02:49:51 drh Exp $
    34     34   */
    35     35   #include "sqliteInt.h"
    36     36   #include <ctype.h>
    37     37   
    38     38   /*
    39     39   ** The following global variable is incremented every time a cursor
    40     40   ** moves, either by the OP_MoveTo or the OP_Next opcode.  The test
................................................................................
  1092   1092     char **pzErrMsg            /* Error msg written here */
  1093   1093   ){
  1094   1094     int i, rc;
  1095   1095     char *azValue[6];
  1096   1096     char zAddr[20];
  1097   1097     char zP1[20];
  1098   1098     char zP2[20];
         1099  +  char zP3[40];
  1099   1100     static char *azColumnNames[] = {
  1100   1101        "addr", "opcode", "p1", "p2", "p3", 0
  1101   1102     };
  1102   1103   
  1103   1104     if( xCallback==0 ) return 0;
  1104   1105     azValue[0] = zAddr;
  1105   1106     azValue[2] = zP1;
................................................................................
  1112   1113         sqliteSetString(pzErrMsg, "interrupted", 0);
  1113   1114         rc = SQLITE_INTERRUPT;
  1114   1115         break;
  1115   1116       }
  1116   1117       sprintf(zAddr,"%d",i);
  1117   1118       sprintf(zP1,"%d", p->aOp[i].p1);
  1118   1119       sprintf(zP2,"%d", p->aOp[i].p2);
  1119         -    azValue[4] = p->aOp[i].p3type!=P3_POINTER ? p->aOp[i].p3 : "";
         1120  +    if( p->aOp[i].p3type==P3_POINTER ){
         1121  +      sprintf(zP3, "ptr(%#x)", (int)p->aOp[i].p3);
         1122  +      azValue[4] = zP3;
         1123  +    }else{
         1124  +      azValue[4] = p->aOp[i].p3;
         1125  +    }
  1120   1126       azValue[1] = zOpName[p->aOp[i].opcode];
  1121   1127       if( xCallback(pArg, 5, azValue, azColumnNames) ){
  1122   1128         rc = SQLITE_ABORT;
  1123   1129       }
  1124   1130     }
  1125   1131     return rc;
  1126   1132   }

Changes to test/insert2.test.

     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing the INSERT statement that takes is
    13     13   # result from a SELECT.
    14     14   #
    15         -# $Id: insert2.test,v 1.7 2002/02/19 13:39:23 drh Exp $
           15  +# $Id: insert2.test,v 1.8 2002/03/03 02:49:52 drh Exp $
    16     16   
    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
    19     19   
    20     20   # Create some tables with data that we can select against
    21     21   #
    22     22   do_test insert2-1.0 {
................................................................................
   108    108     execsql {
   109    109       DELETE FROM t3;
   110    110       INSERT INTO t3(c,a,b) SELECT x, 'hi', y FROM t4;
   111    111       SELECT * FROM t3;
   112    112     }
   113    113   } {hi 2 1}
   114    114   
   115         -do_test insert2-4.0 {
          115  +do_test insert2-3.0 {
   116    116     set x [execsql {PRAGMA integrity_check}]
   117    117     if {$x==""} {set x ok}
   118    118     set x
   119    119   } {ok}
          120  +
          121  +# File table t4 with lots of data
          122  +#
          123  +do_test insert2-3.1 {
          124  +  execsql {
          125  +    SELECT * from t4;
          126  +  }
          127  +} {1 2}
          128  +do_test insert2-3.2 {
          129  +  execsql {
          130  +    BEGIN;
          131  +    INSERT INTO t4 VALUES(2,4);
          132  +    INSERT INTO t4 VALUES(3,6);
          133  +    INSERT INTO t4 VALUES(4,8);
          134  +    INSERT INTO t4 VALUES(5,10);
          135  +    INSERT INTO t4 VALUES(6,12);
          136  +    INSERT INTO t4 VALUES(7,14);
          137  +    INSERT INTO t4 VALUES(8,16);
          138  +    INSERT INTO t4 VALUES(9,18);
          139  +    INSERT INTO t4 VALUES(10,20);
          140  +    COMMIT;
          141  +    SELECT count(*) FROM t4;
          142  +  }
          143  +} {10}
          144  +do_test insert2-3.3 {
          145  +  execsql {
          146  +    BEGIN;
          147  +    INSERT INTO t4 SELECT x+(SELECT max(x) FROM t4),y FROM t4;
          148  +    INSERT INTO t4 SELECT x+(SELECT max(x) FROM t4),y FROM t4;
          149  +    INSERT INTO t4 SELECT x+(SELECT max(x) FROM t4),y FROM t4;
          150  +    INSERT INTO t4 SELECT x+(SELECT max(x) FROM t4),y FROM t4;
          151  +    COMMIT;
          152  +    SELECT count(*) FROM t4;
          153  +  }
          154  +} {160}
          155  +do_test insert2-3.4 {
          156  +  execsql {
          157  +    BEGIN;
          158  +    UPDATE t4 SET y='lots of data for the row where x=' || x
          159  +                     || ' and y=' || y || ' - even more data to fill space';
          160  +    COMMIT;
          161  +    SELECT count(*) FROM t4;
          162  +  }
          163  +} {160}
          164  +do_test insert2-3.5 {
          165  +  execsql {
          166  +    BEGIN;
          167  +    INSERT INTO t4 SELECT x+(SELECT max(x)+1 FROM t4),y FROM t4;
          168  +    SELECT count(*) from t4;
          169  +    ROLLBACK;
          170  +  }
          171  +} {320}
          172  +do_test insert2-3.6 {
          173  +  execsql {
          174  +    SELECT count(*) FROM t4;
          175  +  }
          176  +} {160}
          177  +do_test insert2-3.7 {
          178  +  execsql {
          179  +    BEGIN;
          180  +    DELETE FROM t4 WHERE x!=123;
          181  +    SELECT count(*) FROM t4;
          182  +    ROLLBACK;
          183  +  }
          184  +} {1}
   120    185   
   121    186   finish_test

Changes to test/select6.test.

     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing SELECT statements that contain
    13     13   # subqueries in their FROM clause.
    14     14   #
    15         -# $Id: select6.test,v 1.2 2002/02/18 13:35:33 drh Exp $
           15  +# $Id: select6.test,v 1.3 2002/03/03 02:49:52 drh Exp $
    16     16   
    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
    19     19   
    20     20   do_test select6-1.0 {
    21     21     execsql {
    22     22       BEGIN;
................................................................................
   162    162         (SELECT a.q, a.p, b.r
   163    163          FROM (SELECT count(*) as p , b as q FROM t2 GROUP BY q) AS a,
   164    164               (SELECT max(a) as r, b as s FROM t2 GROUP BY s) as b
   165    165          WHERE a.q=b.s ORDER BY a.q)
   166    166       ORDER BY [a.q]
   167    167     }
   168    168   } {1 1 1 2 2 3 3 4 7 4 8 15 5 5 20}
          169  +
          170  +do_test select6-3.3 {
          171  +  execsql {
          172  +    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1)
          173  +  }
          174  +} {10.5 3.7 14.2}
          175  +do_test select6-3.4 {
          176  +  execsql {
          177  +    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1 WHERE y=4)
          178  +  }
          179  +} {11.5 4 15.5}
          180  +do_test select6-3.5 {
          181  +  execsql {
          182  +    SELECT x,y,x+y FROM (SELECT avg(a) as 'x', avg(b) as 'y' FROM t2 WHERE a=4)
          183  +  }
          184  +} {4 3 7}
          185  +do_test select6-3.6 {
          186  +  execsql {
          187  +    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1)
          188  +    WHERE a>10
          189  +  }
          190  +} {10.5 3.7 14.2}
          191  +do_test select6-3.7 {
          192  +  execsql {
          193  +    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1)
          194  +    WHERE a<10
          195  +  }
          196  +} {}
          197  +do_test select6-3.8 {
          198  +  execsql {
          199  +    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1 WHERE y=4)
          200  +    WHERE a>10
          201  +  }
          202  +} {11.5 4 15.5}
          203  +do_test select6-3.9 {
          204  +  execsql {
          205  +    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1 WHERE y=4)
          206  +    WHERE a<10
          207  +  }
          208  +} {}
          209  +do_test select6-3.10 {
          210  +  execsql {
          211  +    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b)
          212  +    ORDER BY a
          213  +  }
          214  +} {1 1 2 2.5 2 4.5 5.5 3 8.5 11.5 4 15.5 18 5 23}
          215  +do_test select6-3.11 {
          216  +  execsql {
          217  +    SELECT a,b,a+b FROM 
          218  +       (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b)
          219  +    WHERE b<4 ORDER BY a
          220  +  }
          221  +} {1 1 2 2.5 2 4.5 5.5 3 8.5}
          222  +do_test select6-3.12 {
          223  +  execsql {
          224  +    SELECT a,b,a+b FROM 
          225  +       (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b HAVING a>1)
          226  +    WHERE b<4 ORDER BY a
          227  +  }
          228  +} {2.5 2 4.5 5.5 3 8.5}
          229  +do_test select6-3.13 {
          230  +  execsql {
          231  +    SELECT a,b,a+b FROM 
          232  +       (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b HAVING a>1)
          233  +    ORDER BY a
          234  +  }
          235  +} {2.5 2 4.5 5.5 3 8.5 11.5 4 15.5 18 5 23}
          236  +do_test select6-3.14 {
          237  +  execsql {
          238  +    SELECT [count(*)],y FROM (SELECT count(*), y FROM t1 GROUP BY y)
          239  +    ORDER BY [count(*)]
          240  +  }
          241  +} {1 1 2 2 4 3 5 5 8 4}
          242  +do_test select6-3.15 {
          243  +  execsql {
          244  +    SELECT [count(*)],y FROM (SELECT count(*), y FROM t1 GROUP BY y)
          245  +    ORDER BY y
          246  +  }
          247  +} {1 1 2 2 4 3 8 4 5 5}
          248  +
          249  +do_test select6-4.1 {
          250  +  execsql {
          251  +    SELECT a,b,c FROM 
          252  +      (SELECT x AS 'a', y AS 'b', x+y AS 'c' FROM t1 WHERE y=4)
          253  +    WHERE a<10 ORDER BY a;
          254  +  }
          255  +} {8 4 12 9 4 13}
          256  +do_test select6-4.2 {
          257  +  execsql {
          258  +    SELECT y FROM (SELECT DISTINCT y FROM t1) WHERE y<5 ORDER BY y
          259  +  }
          260  +} {1 2 3 4}
          261  +do_test select6-4.3 {
          262  +  execsql {
          263  +    SELECT DISTINCT y FROM (SELECT y FROM t1) WHERE y<5 ORDER BY y
          264  +  }
          265  +} {1 2 3 4}
          266  +
          267  +
   169    268   finish_test

Changes to test/vacuum.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing the VACUUM statement.
    13     13   #
    14         -# $Id: vacuum.test,v 1.6 2001/09/16 00:13:28 drh Exp $
           14  +# $Id: vacuum.test,v 1.7 2002/03/03 02:49:52 drh Exp $
    15     15   
    16     16   set testdir [file dirname $argv0]
    17     17   source $testdir/tester.tcl
    18     18   
    19         -if {0} {
    20         -
    21         -# Try to vacuum a non-existant table.
    22         -#
    23         -do_test vacuum-1.1 {
    24         -  set v [catch {execsql {VACUUM dummy1}} msg]
    25         -  lappend v $msg
    26         -} {1 {no such table or index: dummy1}}
           19  +# The vacuum command no longer functions.  There is
           20  +# nothing to test.
    27     21   
    28         -# It is OK to vacuum sqlite_master...
    29         -#
    30         -do_test vacuum-1.2 {
    31         -  set v [catch {execsql {VACUUM sqlite_master}} msg]
    32         -  lappend v $msg
    33         -} {0 {}}
    34         -
    35         -# Create some tables and indices to test against.
    36         -#
    37         -execsql {CREATE TABLE test1(a int)}
    38         -execsql {CREATE TABLE test2(b int)}
    39         -execsql {CREATE INDEX index1 ON test1(a)}
    40         -execsql {INSERT INTO test1 VALUES(1)}
    41         -execsql {INSERT INTO test1 VALUES(1)}
    42         -execsql {INSERT INTO test1 VALUES(2)}
    43         -execsql {INSERT INTO test1 VALUES(3)}
    44         -execsql {INSERT INTO test2 VALUES(4)}
    45         -
    46         -do_test vacuum-1.3 {
    47         -  set b1 [file mtime testdb/test1.tbl]
    48         -  set b2 [file mtime testdb/test2.tbl]
    49         -  set b3 [file mtime testdb/index1.tbl]
    50         -  after 1000
    51         -  execsql {VACUUM test1}
    52         -  set a1 [file mtime testdb/test1.tbl]
    53         -  set a2 [file mtime testdb/test2.tbl]
    54         -  set a3 [file mtime testdb/index1.tbl]
    55         -  expr {$a1>$b1 && $a2==$b2 && $a3==$b3}
    56         -} {1}
    57         -if {$::tcl_platform(platform)!="windows"} {
    58         -do_test vacuum-1.4 {
    59         -  set b1 [file mtime testdb/test1.tbl]
    60         -  set b2 [file mtime testdb/test2.tbl]
    61         -  set b3 [file mtime testdb/index1.tbl]
    62         -  after 1000
    63         -  execsql {VACUUM}
    64         -  set a1 [file mtime testdb/test1.tbl]
    65         -  set a2 [file mtime testdb/test2.tbl]
    66         -  set a3 [file mtime testdb/index1.tbl]
    67         -  expr {$a1>$b1 && $a2>$b2 && $a3>$b3}
    68         -} {1}
    69         -} ;# End if( platform!=windows )
    70         -
    71         -finish_test
    72         -
    73         -}
           22  +# finish_test

Changes to tool/lemon.c.

  3099   3099           collide[j] = k;
  3100   3100           table[j]->collide = 0;
  3101   3101           if( k<j ) j = k-1;
  3102   3102         }
  3103   3103       }
  3104   3104   
  3105   3105       /* Print the hash table */
  3106         -    fprintf(out,"/* State %d */\n",stp->index); lineno++;
         3106  +    if( tablesize>0 ){
         3107  +      fprintf(out,"/* State %d */\n",stp->index); lineno++;
         3108  +    }
  3107   3109       for(j=0; j<tablesize; j++){
  3108   3110         assert( table[j]!=0 );
  3109         -      fprintf(out,"  {%4d,%4d,%4d}, /* ",
         3111  +      fprintf(out,"  {%4d,%4d,%4d}, /* %2d: ",
  3110   3112             table[j]->sp->index,
  3111   3113             collide[j]+1,
  3112         -          compute_action(lemp,table[j]));
         3114  +          compute_action(lemp,table[j]),
         3115  +          j+1);
  3113   3116         PrintAction(table[j],out,22);
  3114   3117         fprintf(out," */\n"); 
  3115   3118         lineno++;
  3116   3119       }
  3117   3120   
  3118   3121       /* Update the table count */
  3119   3122       tablecnt += tablesize;