Saturday, 7 April 2007

SQLite gotcha

SQLite is great. No, really, I do mean it. If you ever need to work on structured data and editing it from the outside is a must, then SQLite is worth checking out and might be a superior choice to XML and will be a superior choice to your own proprietary format.

I cannot speak about performance - there are some outdated claims that SQLite is faster than postgres and mysql for the most common operations; but those benchmarks don't feature a single join, and that's where database implementation gets interesting and hard. I just cannot believe that SQLite stands up to any full scale DBMS.

I spent a few hours chasing down a bug in my own code after realising that I've been utterly stupid and put my parameters in the wrong order into my parameter list.

To boil it down, this is what happens in SQLite if I compare an integer field with a string in a WHERE part of a statement:

sqlite> CREATE TABLE my_favourite_numbers (number integer);
sqlite> SELECT * FROM my_favourite_numbers WHERE number = "foo";

You get no result. No error is thrown, like in Postgres:

# CREATE TABLE my_favourite_numbers (number integer);
# SELECT * FROM my_favourite_numbers WHERE number = 'bla';
ERROR: invalid input syntax for integer: "bla"

I know now what to look out for in the future.

1 comment:

Hrishikesh said...

That "gotcha" is not just with SQLite - MySQL will happily do such stuff, like comaring INTs with 'strings', inserting 'some random date' into a DATE or DATETIME field (will result in the field becoming 0000-00-00), and will generate warnings (not errors) if you try to INSERT or UPDATE a non-null column with NULL values (will insert '' for strings).

Even worse is to write the tests - due to this stupid behaviour, the tests have to be

foo WHERE Field IS NULL or Field = '' (or DateField IS NOT NULL and DateField != '0000-00-00')


Header Image

Header Image
Bitwiese Header Image