[sqlite] sqlite bug report
Summary Alter table rename to fails if the database contains a view which selects from a nonexistent table. Seen on sqlite 3.27.2. Script to reproduce it: create view view_1 as select * from table_1; create table table_2 (col_1 text, col_2 text); alter table table_2 rename to table_3; The problem seems to be a side-effect of the change introduced in version 3.25.0 to ALTER TABLE which made it update references to the renamed table in triggers and views. renameTableFunc walks through the schema looking for views and triggers which reference the renamed table. It overwrites the schema entries unconditionally even if it has not changed them. I don't know why you do this unnecessary work, but I assume there is a reason. However it then calls sqlite3SelectPrep for each entry. This is definitely wrong, since sqlite3SelectPrep apparently tries to populate the view and fails in sqlite3LocateTable at line 106955. Views are a bit like the box containing Schrödinger's cat: you don't know what is inside until you look, so sqlite shouldn't look until the user explicitly asks it do so. You could argue that users shouldn't create a view that selects from a nonexistent table, but sqlite currently allows it (and also allows you to drop the table). Banning such views would break a lot of existing scripts: many of mine modify a table in ways which ALTER TABLE can't do by creating a new table, dropping the original one, and renaming the new table as the old one. This paradigm is already broken by the change in the semantics of ALTER TABLE, but I can reinstate the old behaviour with a PRAGMA. However not allowing views on nonexistent tables would break it more thoroughly. Richard Parkins http://www.zen224037.zen.co.uk rparkins999/sqliteman | | | | | | | | | | | rparkins999/sqliteman http://sqliteman.com/. Contribute to rparkins999/sqliteman development by creating an account on GitHub. | | | ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug report for rtree.c
If SQLITE_DEBUG is not defined, rtree.c found at https://www.sqlite.org/src/dir?ci=edb095a9a679c8c7&name=ext/rtree fails to compile. This is because bCorrupt is declared (at line 132) only if SQLITE_DEBUG is defined, but is referenced unconditionally at line 980. Line 980 currently is assert( pRtree->nNodeRef==0 || pRtree->bCorrupt ); and it needs to be replaced by #ifdef SQLITE_DEBUG assert( pRtree->nNodeRef==0 || pRtree->bCorrupt ); #else assert( pRtree->nNodeRef==0); #endif Alternatively it may be better to declare and if appropriate set bCorrupt unconditionally, since this avoids a possible assertion error on releasing the Rtree if it is in fact corrupted and nNodeRef is nonzero. Richard Parkins http://www.zen224037.zen.co.uk https://github.com/rparkins999/sqliteman ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Apparent sqlite bug
An INSERT statement which fails with no explicit conflict clause appears to throw away a pending SAVEPOINT. The following sequence demonstrates this behaviour SAVEPOINT demonstration; CREATE TABLE IF NOT EXISTS "PK" ( "first name" "TEXT", "last name" "TEXT", "address", PRIMARY KEY ( "first name", "last name" ) ) WITHOUT ROWID; INSERT INTO "PK" default values; ROLLBACK TO demonstration; As expected, the insert fails with Error: NOT NULL constraint, but the ROLLBACK statement then also fails with Error: no such savepoint. If INSERT is replaced by INSERT OR ABORT (which is supposed to be the default), the ROLLBACK statement does not fail. Environment:- sqlite 3.9.2 built from the amalgamation, running on UBUNTU Linux 14.04.3 LTS. SQL statements run with sqliteman built from https://github.com/rparkins999/sqliteman.git. I originally saw this problem running the INSERT inside sqliiteman. If you run sqliteman, create the PK table as shown but omitting the WITHOUT ROWID clause, insert a row of all nulls, and then try using sqliteman's Alter Table function (accessible by right click on the table name) and try to change it to a WITHOUT ROWID table, you'll see no such savepoint: ALTER_TABLE Unable to fetch row. This is caused by sqliteman's internal cleanup algorithm trying to roll back after failing to insert the data from the old table into its newly created WITHOUT ROWID table, and not being able to do so because the savepoint has vanished. Changing INSERT in my code to INSERT OR ABORT makes the rollback work properly. Richard Parkins
