Hello,
I just found a problem which is truly weird in SQLite 3.0.8 on Mac OS X 10.3.5.
When I create an 'address' table, I pass this string to SQLite:
1) CREATE TABLE address(ROWID INTEGER PRIMARY KEY,First QLString,Last QLString,ZIP QLString,Country QLString);
2) CREATE TABLE address_mem_2(ROWID INTEGER PRIMARY KEY,First QLString,Last QLString,ZIP QLString,Country QLString);
To double-check, I type this:
3) SELECT * FROM sqlite_master;
And SQLite returns a series of data, from which you can see:
4) CREATE TABLE address(Last QLString,ROWID INTEGER PRIMARY KEY,First QLString,ZIP QLString,Country QLString);
Clearly not what I passed in statement #1. You also see this table called 'address_mem_2', which is fine:
5) CREATE TABLE address_mem_2(ROWID INTEGER PRIMARY KEY,First QLString,Last QLString,ZIP QLString,Country QLString);
This time it's exactly what I expected. Since the two are really different, the following statement fails:
6) INSERT INTO address_mem_2 SELECT * FROM address;
The error returned by SQLite is: datatype mismatch.
What is truly confusing is that I'm sending statement #1 and somehow, #4 is actually written to disk. To add more confusion, the creation of 'address_mem_2' (just a schema copy of address) goes without a flaw. I assume that SQLite copies on a column-by column basis, which obviously makes statement #6 fail.
Why is that? Is this a bug in SQLite or am I missing something?
-- Tito
