Hello.
It seems to me I've found a bug in the Sqlite.
I have database with two tables. TestTable is ordinary table, TestTableRTree is
virtual rtree table. Each table has two records. Also database has after update
trigger for table TestTable, this trigger updates corresponding record in the
table TestTableRTree.
The problem appears if I have a select statement and update statement which
changes the current record of the select statement. After execution update
statement sqlite3_step returns SQLITE_ROW instead of SQLITE_DONE for the
select statement. RowID of the current row of the select statement does not
change after this sqlite3_step.
Pseudocode:
1. Query1.CreateAndExecute( "SELECT ... FROM TestTable INNER JOIN
TestTableRTree ON ... WHERE (...)") // This query should return one
and only record
2. Query2.Create( "UPDATE TestTable SET (....) WHERE (...)
// Trying to update currect record of the Query1, this cause
execution trigger that changes record in the TestTableRTree
3. Query2.Execute();
4. Query1.MoveNext(); // This call is finished with code SQLITE_ROW, not
SQLITE_DONE. It is error
The bug is not reproduced if:
1. Query1 has no inner join, in other words if Query1 is query to
TestTable only
or
2. There is no after update trigger in the table TestTable
or
3. There is no update execution on stage 3
C++ code( SQLITE_ENABLE_RTREE should be defined! ):
int Test(bool bPerformUpdate)
{
const char szFileName[]="c:/test.db3";
DeleteFile( szFileName );
sqlite3* db=0;
int nValue=sqlite3_open( szFileName, &db );
if ( SQLITE_OK != nValue || !db )
{
std::cout<<"sqlite3_open returned "<<nValue<<std::endl;
return -1;
}
const char szCreatedDBSQL[]=
"DROP TABLE IF EXISTS TestTable;\
DROP TABLE IF EXISTS TestTable_rtreeindex;\
CREATE TABLE TestTable(x_1 INTEGER NOT NULL DEFAULT 0 , x_8
BLOB NULL , x_65536 REAL NULL DEFAULT 0 , x_65537 TEXT NULL , x_65538
TEXT NULL , x_65539 TEXT NULL , x_65540 TEXT NULL , x_65541 INTEGER
PRIMARY KEY NOT NULL DEFAULT 1 );\
CREATE UNIQUE INDEX TestTable_x_65541_index ON TestTable
(x_65541);\
CREATE TRIGGER TestTable_on_update_trigger AFTER UPDATE ON
TestTable BEGIN UPDATE TestTable_rtreeindex SET x_2 = 0., x_3 = 0., x_4 = 0.,
x_5 = 0. WHERE ID=old.OID; END;\
CREATE VIRTUAL TABLE TestTable_rtreeindex USING rtree ( ID INTEGER
PRIMARY KEY , x_2 REAL, x_3 REAL, x_5 REAL, x_4 REAL);\
INSERT INTO TestTable (x_1, x_8, x_65536, x_65537, x_65538,
x_65539, x_65540, x_65541 ) VALUES ( 7, 'SomeBlob', 64619678.0, NULL, 4,
NULL, 'SomeText', 3);\
INSERT INTO TestTable (x_1, x_8, x_65536, x_65537, x_65538,
x_65539, x_65540, x_65541 ) VALUES ( 07, 'SomeAnotherBlob', -398444.0,
'SomeAnotherText', 6, NULL, NULL, 4 );\
INSERT INTO TestTable_rtreeindex (ID, x_2, x_3, x_5, x_4 ) VALUES
( 4, 4., 4., 4., 4. );\
INSERT INTO TestTable_rtreeindex (ID, x_2, x_3, x_5, x_4 ) VALUES
( 3, 3., 3., 3., 3. );\
";
char* szErrorMsg=0;
int nRes=sqlite3_exec( db, szCreatedDBSQL, 0, 0, &szErrorMsg );
if ( SQLITE_OK != nRes )
{
std::cout<<"exec_sql failed. sqlite3_exec returned "<<nRes<<".
Error is "<<szErrorMsg<<std::endl;
sqlite3_free(szErrorMsg);
return false;
}
sqlite3_stmt* select_stmt=0;
if ( SQLITE_OK != sqlite3_prepare_v2( db,"SELECT
L.x_65541,L.x_1,L.x_8,L.x_65536,LI.x_2,LI.x_4,LI.x_3,LI.x_5, L.OID FROM
TestTable L, TestTable_rtreeindex LI WHERE L.OID=LI.ID AND (L.x_65536=?)" ,
-1, &select_stmt, 0 ) )
{
std::cout<<"sqlite3_prepare_v2 failed"<<std::endl;
return -2;
}
if ( SQLITE_OK != sqlite3_bind_int( select_stmt, 1, -398444 ) )
{
std::cout<<"sqlite3_bind_int failed"<<std::endl;
return -3;
}
int nResult=0;
nResult=sqlite3_step( select_stmt );
if ( SQLITE_ROW != nResult )
{
std::cout<<"sqlite3_step failed. Error is "<<nResult<<std::endl;
return -4;
}
sqlite3_stmt* update_stmt=0;
if ( SQLITE_OK != sqlite3_prepare_v2( db, "UPDATE TestTable SET x_65541
= ?, x_1= ?, x_8 = ?, x_65536 = ? WHERE OID = ?" , -1, &update_stmt, 0 ) )
{
std::cout<<"sqlite3_prepare_v2 failed"<<std::endl;
return -109;
}
if ( SQLITE_OK != sqlite3_bind_int( update_stmt, 1, 4 ) )
{
std::cout<<"sqlite3_bind_int failed"<<std::endl;
return -110;
}
unsigned char uc=7;
if ( SQLITE_OK != sqlite3_bind_int( update_stmt, 2, uc ) )
{
std::cout<<"sqlite3_bind_int failed"<<std::endl;
return -112;
}
std::vector<char> x;
int nBytes=sqlite3_column_bytes( select_stmt, 2 );
if ( nBytes<0 )
{
std::cout<<"sqlite3_column_bytes returned "<<nBytes<<std::endl;
return -113;
}
if ( nBytes!=0 )
{
x.resize( nBytes );
const void * pX=sqlite3_column_blob( select_stmt, 2 );
memcpy( &*x.begin(), pX, nBytes );
}
if ( SQLITE_OK != sqlite3_bind_blob( update_stmt, 3, &*x.begin(),
x.size(), 0 ) )
{
std::cout<<"sqlite3_bind_int failed"<<std::endl;
return -113;
}
if ( SQLITE_OK != sqlite3_bind_int( update_stmt, 4, -398444 ) )
{
std::cout<<"sqlite3_bind_int failed"<<std::endl;
return -114;
}
if ( SQLITE_OK != sqlite3_bind_int( update_stmt, 5, 4 ) )
{
std::cout<<"sqlite3_bind_int failed"<<std::endl;
return -115;
}
if ( bPerformUpdate )
{
if ( SQLITE_DONE != sqlite3_step( update_stmt ) )
{
std::cout<<"update.execute failed"<<std::endl;
return -116;
}
}
nResult=sqlite3_step( select_stmt );
if ( SQLITE_ROW == nResult )
{
std::cout<<"ERROR: move_next2 successed"<<std::endl;
return -117;
}
else if ( SQLITE_DONE != nResult )
{
std::cout<<"move_next2 returned "<<nResult<<std::endl;
return -118;
}
std::cout<<"OK"<<std::endl;
if ( update_stmt )
{
sqlite3_finalize( update_stmt );
update_stmt = 0;
}
if ( select_stmt )
{
sqlite3_finalize( select_stmt );
select_stmt = 0;
}
if ( db )
{
sqlite3_close(db);
db=0;
}
return 0;
}
int _tmain(int argc, _TCHAR* argv[])
{
Test( false );
Test( true );
}
If we call function Test with argument equal to false then this function
perform no update and everything is correct. If we call function Test with
argument equal to true then we can see reproduced bug. The bug was reproduced
on Windows XP + MS Visual C++ 2010. Sqlite version is 3.7.6.1.
Best wishes, Osipov Andrey.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
