unable to search on raw string in sqlite database

[Jelmer Vernooij]

Searching on an object in a SQLite database by a raw string doesn't appear to work, as demonstrated by the attached script.

[Jelmer Vernooij (jelmer)]

The attached patch fixes this particular issue, but it makes some other tests in the storm testsuite fail.

[James Henstridge (jamesh)]

If I edit the test program to insert the test program to insert the row as:

    db.execute("INSERT INTO foo (name) VALUES (?)", (buffer("bla"), ))

Then the assertion doesn't fire. If I poke around in the resulting database, I can't select the row by string comparison:

    sqlite> select * from foo;
    1|bla
    sqlite> select * from foo where name = 'bla';
    sqlite>

I guess sqlite is treating blobs and text strings as distinct here. If you insert the data as blobs, then you can't match it against an equivalent text string and vice versa.

[Jelmer Vernooij (jelmer)]

On Tue, 2011-01-04 at 08:06 +0000, James Henstridge wrote:
> If I edit the test program to insert the test program to insert the row
> as:
>
> db.execute("INSERT INTO foo (name) VALUES (?)", (buffer("bla"), ))
>
> Then the assertion doesn't fire. If I poke around in the resulting
> database, I can't select the row by string comparison:
>
> sqlite> select * from foo;
> 1|bla
> sqlite> select * from foo where name = 'bla';
> sqlite>
>
> I guess sqlite is treating blobs and text strings as distinct here. If
> you insert the data as blobs, then you can't match it against an
> equivalent text string and vice versa.

Yeah, that's what I found later as well. I'm currently using Cast() to
work around this issue, basically:

SELECT * from foo WHERE CAST(name AS BLOB) = CAST('bla' AS BLOB);

The dynamic typing in SQLite is a bit annoying here, and as far as I
know there is no way to disable it. :-/

Cheers,

Jelmer

[James Henstridge (jamesh)]

Is there any reason to leave this bug open then?