(BUG) sqlite cannot search for text, if inserted via sqlite3_bind_blob

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

(BUG) sqlite cannot search for text, if inserted via sqlite3_bind_blob

sqlite-11
In my endless obsession with premature optimization, I've been using
sqlite3_bind_blob, whenever I know the length of what I'm inserting, even if
it's text or whatnot. It exhibits some very strange properties though, which I
can't imagine is anything other than a bug. Here's my test case:

-----------------------------------------------------------------------mimesucks-----------------

#include <sqlite3.h>
#include <stdio.h>
#include <stdlib.h> // NULL
#include <assert.h>

#define LITLEN(lit) lit, sizeof(lit)-1

int main(int argc, char *argv[])
{
    sqlite3* db;
    sqlite3_open(":memory:",&db);
    sqlite3_exec(db,"CREATE TABLE foo (id INTEGER PRIMARY KEY, bar
TEXT)",NULL,NULL,NULL);
    sqlite3_stmt *ins,*sel;
    sqlite3_prepare(db,LITLEN("INSERT INTO foo (bar) VALUES (?)"),&ins,NULL);
    sqlite3_prepare(db,LITLEN("SELECT id FROM foo WHERE bar = ?"),&sel,NULL);

    puts("This is fine.");
    sqlite3_bind_text(ins,1,"test",4,NULL);
    sqlite3_step(ins);
    sqlite3_reset(ins);
    sqlite3_bind_text(sel,1,"test",4,NULL);
    assert(SQLITE_ROW == sqlite3_step(sel));
    printf("Got ID %d\n",sqlite3_column_int(sel,0));

    sqlite3_stmt* clear;
    sqlite3_prepare(db,LITLEN("DELETE FROM foo"),&clear,NULL);
    sqlite3_step(clear);
    sqlite3_reset(clear);
   
    puts("This is NOT fine.");
    sqlite3_bind_blob(ins,1,"test",4,NULL);
    sqlite3_step(ins);
    sqlite3_reset(ins);
    sqlite3_bind_blob(sel,1,"test",4,NULL);
    if(SQLITE_ROW != sqlite3_step(sel)) {
        printf("no results? %s\n",sqlite3_errmsg(db));
    } else {
        printf("Got ID %d\n",sqlite3_column_int(sel,0));
    }

    sqlite3_step(clear);
    sqlite3_reset(clear);

    puts("This is NOT fine.");
    sqlite3_bind_blob(ins,1,"test",4,NULL);
    sqlite3_step(ins);
    sqlite3_reset(ins);
    sqlite3_bind_text(sel,1,"test",4,NULL);
    if(SQLITE_ROW != sqlite3_step(sel)) {
        printf("no results? %s\n",sqlite3_errmsg(db));
    } else {
        printf("Got ID %d\n",sqlite3_column_int(sel,0));
    }
   
    sqlite3_step(clear);
    sqlite3_reset(clear);

    puts("This is fine though?");
    sqlite3_bind_text(ins,1,"test",4,NULL);
    sqlite3_step(ins);
    sqlite3_reset(ins);
    sqlite3_bind_blob(sel,1,"test",4,NULL);
    if(SQLITE_ROW != sqlite3_step(sel)) {
        printf("no results? %s\n",sqlite3_errmsg(db));
    } else {
        printf("Got ID %d\n",sqlite3_column_int(sel,0));
    }
   
    return 0;
}


-----------------------------------------------------------------------mimesucks-----------------

When I insert anything via sqlite3_bind_blob, sqlite then loses the record, uh,
forever. Searching for the exact same text I just inserted, I cannot get any
results, if sqlite3_bind_blob is used. Even if I use sqlite3_bind_blob on both
the search text and the insert text, sqlite still comes up with no results.
Interestingly, if I use sqlite_bind_text on insert, then sqlite_bind_blob on
select, the database DOES find a result. Only when I use sqlite3_bind_blob, on
an insert statement, does the resulting field become entirely unsearchable. If I
get the row by some other criteria, the data inserted with sqlite3_bind_blob and
sqlite3_bind_text are byte-equivalent, and when I look at them using
sqlite3_column_blob/bytes, neither one has an embedded null terminator.

I'm not... familiar with sqlite's innards, but my best guess is that there must
be a (hidden) null terminator included on disk for stuff inserted with
sqlite3_bind_text, that isn't reported, but when sqlite3_bind_blob is used, that
extra byte for the null doesn't go on disk. When sqlite encounters an "a = b"
expression, it assumes that null byte exists, and adds the null terminator to
the criteria, which then fails to find the row, since the actual data was
inserted with sqlite3_bind_blob and lacks a null terminator.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...