[sqlite] Incorrect documentation for PRAGMA table_info (pk column)
The documentation for PRAGMA table_info says: The 'pk' column in the result set is zero for columns that are not part of the primary key, and is the index of the column in the primary key for columns that are part of the primary key. But in reality, pk = 1 for all the primary key columns: SQLite version 3.7.15.2 2013-01-09 11:53:05 Enter .help for instructions Enter SQL statements terminated with a ; sqlite .mode column sqlite .header on sqlite CREATE TABLE foo (a INT, b INT, PRIMARY KEY(a,b)); sqlite PRAGMA table_info(foo); cid nametypenotnull dflt_value pk -- -- -- -- -- -- 0 a INT 0 1 1 b INT 0 1 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [BUG] sqlite3_exec BEGIN; ROLLBACK corrupts statement already running
Consider the following operations (full test program attached): stmt - prepare conn SELECT * FROM foo Row - step stmt exec conn BEGIN; ROLLBACK Row - step stmt Namely, we prepare a statement with sqlite3_prepare_v2, call sqlite3_step (giving us SQLITE_ROW). While the statement is busy, we jump in and do this: rc = sqlite3_exec(conn, BEGIN; ROLLBACK, NULL, NULL, NULL); On SQLite 3.6.22, this sqlite3_exec call returns SQLITE_BUSY, and the subsequent sqlite3_step returns SQLITE_ROW. On SQLite 3.7.13, this sqlite3_exec call returns SQLITE_OK, but the subsequent sqlite3_step returns SQLITE_ABORT. The latter result looks bogus to me. #define SQLITE_ABORT4 /* Callback routine requested an abort */ We're not doing anything with a callback routine. And according to the documentation for ROLLBACK: The ROLLBACK will fail with an error code SQLITE_BUSY if there are any pending queries. Does this include queries started before the transaction begin? Should it? When the sequence above is performed, I would expect one of two results (preferably the first): * The exec BEGIN; ROLLBACK has no effect on the prepared statement that has already started. * The ROLLBACK fails with SQLITE_BUSY (the 3.6.22 behavior). The 3.7.13 behavior definitely looks wrong. Is this a bug, or is it undefined behavior to BEGIN or ROLLBACK while a prepared statement is running on the same connection? Thanks, -Joey ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [BUG] sqlite3_exec BEGIN; ROLLBACK corrupts statement already running
On Fri, Aug 24, 2012 at 12:45 AM, Pavel Ivanov paiva...@gmail.com wrote: This is a documented change. See http://www.sqlite.org/releaselog/3_7_11.html: Pending statements no longer block ROLLBACK. Instead, the pending statement will return SQLITE_ABORT upon next access after the ROLLBACK. There was even some explanation of reasons for that somewhere on the list. Thanks for the quick response. This looks like the thread you are referring to: http://osdir.com/ml/sqlite-users/2012-04/msg00638.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Assertion failure when sqlite3_bind_blob is passed a negative length
With SQLite 3.7.13 (the current version), if you pass a negative length to sqlite3_bind_blob, you get an assertion failure: sqlite3.c:59949: sqlite3VdbeMemSetStr: Assertion `enc!=0' failed. Here is the relevant source code: ... if( nByte0 ){ assert( enc!=0 ); if( enc==SQLITE_UTF8 ){ for(nByte=0; nByte=iLimit z[nByte]; nByte++){} }else{ for(nByte=0; nByte=iLimit (z[nByte] | z[nByte+1]); nByte+=2){} } flags |= MEM_Term; } ... If assertions are not turned on, it takes the second branch, treating the string as UTF-16 (output of attached test program): sqlite3_bind_blob returned 0 column type: BLOB column text: foo^@garbage^@ column bytes: 12 This is not a bug, strictly speaking. It doesn't really make sense for a BLOB to be NUL-terminated. But the documentation does not make it clear that this is undefined behavior [1]: In those routines that have a fourth argument, its value is the number of bytes in the parameter. To be clear: the value is the number of bytes in the value, not the number of characters. If the fourth parameter is negative, the length of the string is the number of bytes up to the first zero terminator. ... [1]: http://www.sqlite.org/c3ref/bind_blob.html Giving a negative length to sqlite3_bind_blob might be an easy trap to fall into, since sqlite3_bind_text *does* let you give a negative length, which basically means strlen(value). I think the documentation should mention that a negative length argument is only allowed for text and text16. Thanks, -Joey ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users