Hello.
It looks like I found a bug in sqlite3_step. Here is the sample
code.
#include <windows.h>
#include <stdio.h>
#include <assert.h>
#include "sqlite3.h"
#ifdef _DEBUG
#define verify(f) assert(f)
#else
#define verify(f) ((void)(f))
#endif
char const * const fname = "bug.db3";
int cb(void*, int, char**, char**)
{
// If here, then got kav=-1 for uid=2
printf("ok");
return 0;
}
int main()
{
sqlite3* db = 0;
char* err;
DeleteFileA(fname);
verify(sqlite3_open(fname, &db) == SQLITE_OK);
verify(sqlite3_exec(db, "drop table if exists mails;", 0, 0, &err) ==
SQLITE_OK);
verify(sqlite3_exec(db, "drop table if exists plugins;", 0, 0, &err) ==
SQLITE_OK);
verify(sqlite3_exec(db
, "create table if not exists mails ("
"uid integer not null primary key unique"
", kav integer not null default 0"
");"
, 0, 0, &err) == SQLITE_OK);
verify(sqlite3_exec(db
, "create table plugins ("
"uid integer not null"
", plugin varchar(32) not null"
", result varchar(32) not null"
", primary key (uid, plugin));"
, 0, 0, &err) == SQLITE_OK);
verify(sqlite3_exec(db, "insert into mails (uid) values (1);", 0, 0,
&err) == SQLITE_OK);
verify(sqlite3_exec(db, "insert into mails (uid) values (2);", 0, 0,
&err) == SQLITE_OK);
verify(sqlite3_exec(db, "insert into plugins (uid, plugin, result)
values (1, 'kav', 'clean');", 0, 0, &err) == SQLITE_OK);
verify(sqlite3_exec(db, "insert into plugins (uid, plugin, result)
values (2, 'kav', 'infected');", 0, 0, &err) == SQLITE_OK);
sqlite3_stmt* stmt = 0;
#if 0
// Work's correctly
verify(sqlite3_prepare_v2(db, "create trigger updater update of result
on plugins"
" begin"
" update mails set kav=case old.result when 'infected' then -1
else 0 end where uid=old.uid;"
" end;"
, -1, &stmt, 0) == SQLITE_OK);
// In this case I get kav=-1 for uid=2 in mails table
#else
int idx = 0;
// case 1: exception
//verify(sqlite3_prepare_v2(db, "create trigger updater update of
result on plugins"
// " begin"
// " update mails set kav=case old.result when 'infected' then ?
else 0 end where uid=old.uid;"
// " end;"
// , -1, &stmt, 0) == SQLITE_OK);
//verify(sqlite3_bind_int(stmt, ++idx, -1) == SQLITE_OK);
// case 2: exception
//verify(sqlite3_prepare_v2(db, "create trigger updater update of
result on plugins"
// " begin"
// " update mails set kav=case old.result when 'infected' then -1
else ? end where uid=old.uid;"
// " end;"
// , -1, &stmt, 0) == SQLITE_OK);
//verify(sqlite3_bind_int(stmt, ++idx, 0) == SQLITE_OK);
// case 3: nothing happens
//verify(sqlite3_prepare_v2(db, "create trigger updater update of
result on plugins"
// " begin"
// " update mails set kav=case old.result when ? then -1 else 0
end where uid=old.uid;"
// " end;"
// , -1, &stmt, 0) == SQLITE_OK);
//verify(sqlite3_bind_text(stmt, ++idx, "infected", -1, SQLITE_STATIC)
== SQLITE_OK);
// case 4: nothing happens
verify(sqlite3_prepare_v2(db, "create trigger updater update of result
on plugins"
" begin"
" update mails set kav=case old.result when ? then ? else 0 end
where uid=old.uid;"
" end;"
, -1, &stmt, 0) == SQLITE_OK);
verify(sqlite3_bind_text(stmt, ++idx, "infected", -1, SQLITE_STATIC) ==
SQLITE_OK);
verify(sqlite3_bind_int(stmt, ++idx, -1) == SQLITE_OK);
// In this case I get kav=0 for uid=2 in mails table
#endif
verify(sqlite3_step(stmt) == SQLITE_DONE);
verify(sqlite3_finalize(stmt) == SQLITE_OK);
verify(sqlite3_exec(db, "update plugins set result=result;", 0, 0,
&err) == SQLITE_OK);
verify(sqlite3_exec(db, "select kav from mails where kav=-1;", cb, 0,
&err) == SQLITE_OK);
verify(sqlite3_close(db) == SQLITE_OK);
return 0;
}
--
Best regards,
Unsupported mailto:[email protected]
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
