/ Check-in [ba89da60]
Login
Overview
Comment:Fix a problem with the way temporary tables mask permanent tables. (CVS 345)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:ba89da603135e5f63f6f97ba3adad02682375c07
User & Date: drh 2002-01-10 14:31:49
Context
2002-01-14
02:56
Fix a bug that could cause UPDATE to fail for a table that contains both an INTEGER PRIMARY KEY and an index. (CVS 346) check-in: 96cd07a8 user: drh tags: trunk
2002-01-10
14:31
Fix a problem with the way temporary tables mask permanent tables. (CVS 345) check-in: ba89da60 user: drh tags: trunk
2002-01-09
13:45
Version 2.2.1 (CVS 452) check-in: 61c38f3b user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to VERSION.

1
2.2.1
|
1
2.2.2

Changes to src/build.c.

21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
...
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
...
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
...
950
951
952
953
954
955
956

957
958
959
960
961
962
963
**     COPY
**     VACUUM
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.63 2002/01/09 13:30:41 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called after a single SQL statement has been
** parsed and we want to execute the VDBE code to implement 
................................................................................
** been committed.  If this index was never committed, then just
** delete it.
**
** Indices on the pending drop queue are deleted when a COMMIT is
** executed.  If a ROLLBACK occurs, the indices are moved back into
** the main index hash table.
*/
void sqlitePendingDropIndex(sqlite *db, Index *p){
  if( !p->isCommit ){
    sqliteUnlinkAndDeleteIndex(db, p);
  }else{
    Index *pOld;
    pOld = sqliteHashInsert(&db->idxHash, p->zName, strlen(p->zName)+1, 0);
    if( pOld!=0 && pOld!=p ){
      sqliteHashInsert(&db->idxHash, pOld->zName, strlen(pOld->zName)+1, pOld);
................................................................................
** delete it.  Do the same for all its indices.
**
** Table on the drop queue are not actually deleted until a COMMIT
** statement is executed.  If a ROLLBACK occurs instead of a COMMIT,
** then the tables on the drop queue are moved back into the main
** hash table.
*/
void sqlitePendingDropTable(sqlite *db, Table *pTbl){
  if( !pTbl->isCommit ){
    sqliteUnlinkAndDeleteTable(db, pTbl);
  }else{
    Table *pOld;
    Index *pIndex, *pNext;
    pOld = sqliteHashInsert(&db->tblHash, pTbl->zName, strlen(pTbl->zName)+1,0);
    assert( pOld==pTbl );
................................................................................
    int n;
    Index *pLoop;
    for(pLoop=pTab->pIndex, n=1; pLoop; pLoop=pLoop->pNext, n++){}
    sprintf(zBuf,"%d)",n);
    zName = 0;
    sqliteSetString(&zName, "(", pTab->zName, " autoindex ", zBuf, 0);
    if( zName==0 ) goto exit_create_index;

  }

  /* If pList==0, it means this routine was called to make a primary
  ** key out of the last column added to the table under construction.
  ** So create a fake list to simulate this.
  */
  if( pList==0 ){







|







 







|







 







|







 







>







21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
...
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
...
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
...
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
**     COPY
**     VACUUM
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.64 2002/01/10 14:31:49 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called after a single SQL statement has been
** parsed and we want to execute the VDBE code to implement 
................................................................................
** been committed.  If this index was never committed, then just
** delete it.
**
** Indices on the pending drop queue are deleted when a COMMIT is
** executed.  If a ROLLBACK occurs, the indices are moved back into
** the main index hash table.
*/
static void sqlitePendingDropIndex(sqlite *db, Index *p){
  if( !p->isCommit ){
    sqliteUnlinkAndDeleteIndex(db, p);
  }else{
    Index *pOld;
    pOld = sqliteHashInsert(&db->idxHash, p->zName, strlen(p->zName)+1, 0);
    if( pOld!=0 && pOld!=p ){
      sqliteHashInsert(&db->idxHash, pOld->zName, strlen(pOld->zName)+1, pOld);
................................................................................
** delete it.  Do the same for all its indices.
**
** Table on the drop queue are not actually deleted until a COMMIT
** statement is executed.  If a ROLLBACK occurs instead of a COMMIT,
** then the tables on the drop queue are moved back into the main
** hash table.
*/
static void sqlitePendingDropTable(sqlite *db, Table *pTbl){
  if( !pTbl->isCommit ){
    sqliteUnlinkAndDeleteTable(db, pTbl);
  }else{
    Table *pOld;
    Index *pIndex, *pNext;
    pOld = sqliteHashInsert(&db->tblHash, pTbl->zName, strlen(pTbl->zName)+1,0);
    assert( pOld==pTbl );
................................................................................
    int n;
    Index *pLoop;
    for(pLoop=pTab->pIndex, n=1; pLoop; pLoop=pLoop->pNext, n++){}
    sprintf(zBuf,"%d)",n);
    zName = 0;
    sqliteSetString(&zName, "(", pTab->zName, " autoindex ", zBuf, 0);
    if( zName==0 ) goto exit_create_index;
    hideName = sqliteFindIndex(db, zName)!=0;
  }

  /* If pList==0, it means this routine was called to make a primary
  ** key out of the last column added to the table under construction.
  ** So create a fake list to simulate this.
  */
  if( pList==0 ){

Changes to src/main.c.

10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
..
69
70
71
72
73
74
75
76





77
78
79
80
81
82
83
**
*************************************************************************
** Main file for the SQLite library.  The routines in this file
** implement the programmer interface to the library.  Routines in
** other files are for internal use by SQLite and should not be
** accessed by users of the library.
**
** $Id: main.c,v 1.54 2002/01/09 03:20:00 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"

/*
** This is the callback routine for the code that initializes the
** database.  See sqliteInit() below for additional information.
................................................................................
        ** was created to be the PRIMARY KEY or to fulfill a UNIQUE
        ** constraint for a CREATE TABLE.  The index should have already
        ** been created when we processed the CREATE TABLE.  All we have
        ** to do here is record the root page number for that index.
        */
        Index *pIndex = sqliteFindIndex(db, argv[1]);
        if( pIndex==0 || pIndex->tnum!=0 ){
          nErr++;





        }else{
          pIndex->tnum = atoi(argv[2]);
        }
      }
      break;
    }
    default: {







|







 







|
>
>
>
>
>







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
..
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
**
*************************************************************************
** Main file for the SQLite library.  The routines in this file
** implement the programmer interface to the library.  Routines in
** other files are for internal use by SQLite and should not be
** accessed by users of the library.
**
** $Id: main.c,v 1.55 2002/01/10 14:31:49 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"

/*
** This is the callback routine for the code that initializes the
** database.  See sqliteInit() below for additional information.
................................................................................
        ** was created to be the PRIMARY KEY or to fulfill a UNIQUE
        ** constraint for a CREATE TABLE.  The index should have already
        ** been created when we processed the CREATE TABLE.  All we have
        ** to do here is record the root page number for that index.
        */
        Index *pIndex = sqliteFindIndex(db, argv[1]);
        if( pIndex==0 || pIndex->tnum!=0 ){
          /* This can occur if there exists an index on a TEMP table which
          ** has the same name as another index on a permanent index.  Since
          ** the permanent table is hidden by the TEMP table, we can also
          ** safely ignore the index on the permanent table.
          */
          /* Do Nothing */;
        }else{
          pIndex->tnum = atoi(argv[2]);
        }
      }
      break;
    }
    default: {

Changes to test/temptable.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
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
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
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for temporary tables and indices.
#
# $Id: temptable.test,v 1.3 2001/10/09 12:39:24 drh Exp $

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

# Create an alternative connection to the database
#
do_test temptable-1.0 {
................................................................................

# Check for correct name collision processing. A name collision can
# occur when process A creates a temporary table T then process B
# creates a permanent table also named T.  The temp table in process A
# hides the existance of the permanent table.
#
do_test temptable-4.1 {
  db2 eval {
    CREATE TEMP TABLE t2(x,y);
    INSERT INTO t2 VALUES(10,20);
    SELECT * FROM t2;
  }
} {10 20}
do_test temptable-4.2 {
  execsql {
    CREATE TABLE t2(x,y,z);
    INSERT INTO t2 VALUES(9,8,7);
    SELECT * FROM t2;
  }
} {9 8 7}
do_test temptable-4.3 {

  set r [catch {db2 eval {SELECT * FROM t2}} msg]
  lappend r $msg

} {1 {database schema has changed}}
do_test temptable-4.4 {

  set r [catch {db2 eval {SELECT * FROM t2}} msg]
  lappend r $msg

} {0 {10 20}}
do_test temptable-4.5 {

  db2 eval {DROP TABLE t2}
  set r [catch {db2 eval {SELECT * FROM t2}} msg]
  lappend r $msg

} {1 {no such table: t2}}
do_test temptable-4.6 {
  db2 close
  sqlite db2 ./test.db

  set r [catch {db2 eval {SELECT * FROM t2}} msg]
  lappend r $msg

} {0 {9 8 7}}
























































# Now create a temporary table in db2 and a permanent index in db.  The
# temporary table in db2 should mask the name of the permanent index,
# but the permanent index should still be accessible and should still
# be updated when its correspnding table changes.
#
do_test temptable-5.1 {

  db2 eval {CREATE TEMP TABLE mask(a,b,c)}

  execsql {
    CREATE INDEX mask ON t2(x);
    SELECT * FROM t2;
  }
} {9 8 7}
do_test temptable-5.2 {

  set r [catch {db2 eval {SELECT * FROM t2}} msg]
  lappend r $msg

} {1 {database schema has changed}}
do_test temptable-5.3 {

  set r [catch {db2 eval {SELECT * FROM t2}} msg]
  lappend r $msg

} {0 {9 8 7}}
do_test temptable-5.4 {

  execsql {SELECT y FROM t2 WHERE x=9}

} {8}
do_test temptable-5.5 {

  db2 eval {SELECT y FROM t2 WHERE x=9}

} {8}
do_test temptable-5.6 {
  db2 eval {

    INSERT INTO t2 VALUES(1,2,3);
    SELECT y FROM t2 WHERE x=1;
  }

} {2}
do_test temptable-5.7 {

  db2 eval {SELECT y FROM t2 WHERE x=9}

} {8}
do_test temptable-5.8 {
  execsql {
    SELECT y FROM t2 WHERE x=1;
  }
} {2}
do_test temptable-5.9 {

  execsql {SELECT y FROM t2 WHERE x=9}

} {8}

db2 close

finish_test







|







 







|



|









>
|
<
>


>
|
<
>


>
|
|
<
>




>
|
<
>

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



|


>
|
>




|

>
|
<
>


>
|
<
>
|

>
|
>
|

>
|
>
|

<
>
|

<
>


>
|
>
|






>
|
>
|




8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
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
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
269
270
271
272
273
274
275
276
277

278
279
280

281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for temporary tables and indices.
#
# $Id: temptable.test,v 1.4 2002/01/10 14:31:49 drh Exp $

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

# Create an alternative connection to the database
#
do_test temptable-1.0 {
................................................................................

# Check for correct name collision processing. A name collision can
# occur when process A creates a temporary table T then process B
# creates a permanent table also named T.  The temp table in process A
# hides the existance of the permanent table.
#
do_test temptable-4.1 {
  execsql {
    CREATE TEMP TABLE t2(x,y);
    INSERT INTO t2 VALUES(10,20);
    SELECT * FROM t2;
  } db2
} {10 20}
do_test temptable-4.2 {
  execsql {
    CREATE TABLE t2(x,y,z);
    INSERT INTO t2 VALUES(9,8,7);
    SELECT * FROM t2;
  }
} {9 8 7}
do_test temptable-4.3 {
  catchsql {
    SELECT * FROM t2;

  } db2
} {1 {database schema has changed}}
do_test temptable-4.4 {
  catchsql {
    SELECT * FROM t2;

  } db2
} {0 {10 20}}
do_test temptable-4.5 {
  catchsql {
    DROP TABLE t2;
    SELECT * FROM t2;

  } db2
} {1 {no such table: t2}}
do_test temptable-4.6 {
  db2 close
  sqlite db2 ./test.db
  catchsql {
    SELECT * FROM t2;

  } db2
} {0 {9 8 7}}
do_test temptable-4.7 {
  catchsql {
    DROP TABLE t2;
    SELECT * FROM t2;
  }
} {1 {no such table: t2}}
do_test temptable-4.8 {
  db2 close
  sqlite db2 ./test.db
  execsql {
    CREATE TEMP TABLE t2(x unique,y);
    INSERT INTO t2 VALUES(1,2);
    SELECT * FROM t2;
  } db2
} {1 2}
do_test temptable-4.9 {
  execsql {
    CREATE TABLE t2(x unique, y);
    INSERT INTO t2 VALUES(3,4);
    SELECT * FROM t2;
  }
} {3 4}
do_test temptable-4.10 {
  catchsql {
    SELECT * FROM t2;
  } db2
} {1 {database schema has changed}}
do_test temptable-4.11 {
  execsql {
    SELECT * FROM t2;
  } db2
} {1 2}
do_test temptable-4.12 {
  execsql {
    SELECT * FROM t2;
  }
} {3 4}
do_test temptable-4.13 {
  catchsql {
    DROP TABLE t2;
    SELECT * FROM t2;
  } db2
} {1 {no such table: t2}}
do_test temptable-4.14 {
  execsql {
    SELECT * FROM t2;
  }
} {3 4}
do_test temptable-4.15 {
  db2 close
  sqlite db2 ./test.db
  execsql {
    SELECT * FROM t2;
  } db2
} {3 4}

# Now create a temporary table in db2 and a permanent index in db.  The
# temporary table in db2 should mask the name of the permanent index,
# but the permanent index should still be accessible and should still
# be updated when its corresponding table changes.
#
do_test temptable-5.1 {
  execsql {
    CREATE TEMP TABLE mask(a,b,c)
  } db2
  execsql {
    CREATE INDEX mask ON t2(x);
    SELECT * FROM t2;
  }
} {3 4}
do_test temptable-5.2 {
  catchsql {
    SELECT * FROM t2;

  } db2
} {1 {database schema has changed}}
do_test temptable-5.3 {
  catchsql {
    SELECT * FROM t2;

  } db2
} {0 {3 4}}
do_test temptable-5.4 {
  execsql {
    SELECT y FROM t2 WHERE x=3
  }
} {4}
do_test temptable-5.5 {
  execsql {
    SELECT y FROM t2 WHERE x=3
  } db2
} {4}
do_test temptable-5.6 {

  execsql {
    INSERT INTO t2 VALUES(1,2);
    SELECT y FROM t2 WHERE x=1;

  } db2
} {2}
do_test temptable-5.7 {
  execsql {
    SELECT y FROM t2 WHERE x=3
  } db2
} {4}
do_test temptable-5.8 {
  execsql {
    SELECT y FROM t2 WHERE x=1;
  }
} {2}
do_test temptable-5.9 {
  execsql {
    SELECT y FROM t2 WHERE x=3
  }
} {4}

db2 close

finish_test

Changes to test/trans.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
642
643
644
645
646
647
648
649






650
651
652
653
654
655
656
657
658
659
660
661
#    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 database locks.
#
# $Id: trans.test,v 1.9 2002/01/09 13:30:42 drh Exp $


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


# Create several tables to work with.
................................................................................
  }
} {1 -2 -3 4 -5 -6}
do_test trans-6.36 {
  execsql {
    SELECT * FROM t1 WHERE c<1;
  }
} {4 -5 -6 1 -2 -3}
do_test trans-6.36 {






  execsql {
    ROLLBACK;
    SELECT * FROM t1 WHERE b<1;
  }
} {4 -5 -6 1 -2 -3}
do_test trans-6.38 {
  execsql {
    SELECT * FROM t1 WHERE c<1;
  }
} {1 -2 -3 4 -5 -6}

finish_test







|







 







|
>
>
>
>
>
>





|






7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
#    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 database locks.
#
# $Id: trans.test,v 1.10 2002/01/10 14:31:49 drh Exp $


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


# Create several tables to work with.
................................................................................
  }
} {1 -2 -3 4 -5 -6}
do_test trans-6.36 {
  execsql {
    SELECT * FROM t1 WHERE c<1;
  }
} {4 -5 -6 1 -2 -3}
do_test trans-6.37 {
  execsql {
    DROP INDEX i1;
    SELECT * FROM t1 WHERE c<1;
  }
} {1 -2 -3 4 -5 -6}
do_test trans-6.38 {
  execsql {
    ROLLBACK;
    SELECT * FROM t1 WHERE b<1;
  }
} {4 -5 -6 1 -2 -3}
do_test trans-6.39 {
  execsql {
    SELECT * FROM t1 WHERE c<1;
  }
} {1 -2 -3 4 -5 -6}

finish_test

Changes to www/changes.tcl.

12
13
14
15
16
17
18





19
20
21
22
23
24
25
}


proc chng {date desc} {
  puts "<DT><B>$date</B></DT>"
  puts "<DD><P><UL>$desc</UL></P></DD>"
}






chng {2002 Jan 9 (2.2.1)} {
<li>Bug fix: An attempt to delete a single row of a table with a WHERE
    clause of "ROWID=x" when no such rowid exists was causing an error.</li>
<li>Bug fix: Passing in a NULL as the 3rd parameter to <b>sqlite_open()</b>
    would sometimes cause a coredump.</li>
<li>Bug fix: DROP TABLE followed by a CREATE TABLE with the same name all







>
>
>
>
>







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
}


proc chng {date desc} {
  puts "<DT><B>$date</B></DT>"
  puts "<DD><P><UL>$desc</UL></P></DD>"
}

chng {2002 Jan 10 (2.2.2)} {
<li>Bug fix: An assertion was failing when a temporary table with an index
    had the same name as a permanent table created by a separate process.</li>
}

chng {2002 Jan 9 (2.2.1)} {
<li>Bug fix: An attempt to delete a single row of a table with a WHERE
    clause of "ROWID=x" when no such rowid exists was causing an error.</li>
<li>Bug fix: Passing in a NULL as the 3rd parameter to <b>sqlite_open()</b>
    would sometimes cause a coredump.</li>
<li>Bug fix: DROP TABLE followed by a CREATE TABLE with the same name all