Hello,
On Mon, Dec 7, 2015 at 5:05 PM, Patrick Donnelly <batrick at batbytes.com>
wrote:
> Update on this:
>
> On Mon, May 5, 2014 at 4:53 PM, Patrick Donnelly <batrick at batbytes.com>
> wrote:
>> Hi,
>>
>> I have an INSERT that looks like
>>
>> INSERT INTO T
>> SELECT ...
>>
>> which I'm running numerous times a second that generally does nothing
>> because the SELECT returns no rows. Unfortunately, I've found that
>> SQLite still does numerous disk writes anyway in this situation.
>>
>> Is my only option to eliminate the INSERT by using a SELECT first to
>> check if there are no rows? Something like:
>>
>> CREATE TEMPORARY VIEW V AS
>> SELECT ...
>> SELECT COUNT(*) FROM V;
>> /* If > 0 */
>> INSERT INTO T SELECT * FROM V;
>>
>> ?
>
> I've been able to reproduce it with this minimal example:
>
> CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT);
> .print ---------------------------------
> INSERT INTO t1
> SELECT 0
> WHERE 1 = 2;
> select changes();
>
> $ ./sqlite3 -vfstrace foo.db < test.sql
> ...
> ---------------------------------
> trace.xLock(foo.db,SHARED) -> SQLITE_OK
> trace.xAccess("/home/batrick/scm/cctools/chirp/src/sqlite-amalgamation-3090200/foo.db-journal",0)
> -> SQLITE_OK, out=0
> trace.xFileSize(foo.db) -> SQLITE_OK, size=3072
> trace.xRead(foo.db,n=16,ofst=24) -> SQLITE_OK
> trace.xFileSize(foo.db) -> SQLITE_OK, size=3072
> trace.xAccess("/home/batrick/scm/cctools/chirp/src/sqlite-amalgamation-3090200/foo.db-wal",0)
> -> SQLITE_OK, out=0
> trace.xFileSize(foo.db) -> SQLITE_OK, size=3072
> trace.xLock(foo.db,RESERVED) -> SQLITE_OK
> trace.xFileControl(foo.db,20) -> SQLITE_OK
> trace.xOpen(foo.db-journal,flags=0x806) -> SQLITE_OK
> trace.xDeviceCharacteristics(foo.db) -> 0x00001000
> trace.xWrite(foo.db-journal,n=512,ofst=0) -> SQLITE_OK
> trace.xWrite(foo.db-journal,n=4,ofst=512) -> SQLITE_OK
> trace.xWrite(foo.db-journal,n=1024,ofst=516) -> SQLITE_OK
> trace.xWrite(foo.db-journal,n=4,ofst=1540) -> SQLITE_OK
> trace.xLock(foo.db,EXCLUSIVE) -> SQLITE_OK
> trace.xWrite(foo.db-journal,n=4,ofst=1544) -> SQLITE_OK
> trace.xWrite(foo.db-journal,n=1024,ofst=1548) -> SQLITE_OK
> trace.xWrite(foo.db-journal,n=4,ofst=2572) -> SQLITE_OK
> trace.xDeviceCharacteristics(foo.db) -> 0x00001000
> trace.xRead(foo.db-journal,n=8,ofst=3072) -> SQLITE_IOERR_SHORT_READ
> trace.xSync(foo.db-journal,FULL) -> 0
> trace.xWrite(foo.db-journal,n=12,ofst=0) -> SQLITE_OK
> trace.xSync(foo.db-journal,FULL) -> 0
> trace.xWrite(foo.db,n=1024,ofst=0) -> SQLITE_OK
> trace.xWrite(foo.db,n=1024,ofst=2048) -> SQLITE_OK
> trace.xFileControl(foo.db,21) -> 12
> trace.xSync(foo.db,FULL) -> 0
> trace.xClose(foo.db-journal) -> SQLITE_OK
> trace.xDelete("/home/batrick/scm/cctools/chirp/src/sqlite-amalgamation-3090200/foo.db-journal",0)
> -> SQLITE_OK
> trace.xFileControl(foo.db,22) -> 12
> trace.xUnlock(foo.db,SHARED) -> SQLITE_OK
> trace.xDeviceCharacteristics(foo.db) -> 0x00001000
> trace.xUnlock(foo.db,NONE) -> SQLITE_OK
> 0
> trace.xDeviceCharacteristics(foo.db) -> 0x00001000
> trace.xUnlock(foo.db,NONE) -> SQLITE_OK
> trace.xClose(foo.db) -> SQLITE_OK
>
> No rows were inserted but there are several writes. This behavior
> seems to be caused by AUTOINCREMENT?
Now that there is a trivial test-case, I was hoping to find this on
the bugs page [1] but nothing yet. Is this not a bug?
[1] http://www.sqlite.org/src/rptview?rn=1
--
Patrick Donnelly
