On Thu, Jun 04, 2009 at 02:14:19PM -0700, PFudd scratched on the wall: > Hi.. > > I'm not sure if I've found a bug or not. I've created a table like so: > > CREATE TABLE desc (number primary key, full_line);
Lacking a type, the affinity will be NONE. That means no conversion will be done. > Then I've loaded it with taxonomy numbers from NCBI (where "9606" means > "Homo Sapiens", for instance). I bet when you're loading it, you're loading it as TEXT. Since the column affinity is NONE, no conversion is attempted and the field types become TEXT. > sqlite> select * From desc where number="9606"; Technically that's not valid. Use single quotes. > 9606|Homo sapiens > sqlite> select * From desc where number='9606'; If you're loading as TEXT, this will work. > 9606|Homo sapiens > sqlite> select * From desc where number=9606; > sqlite> select * From desc where number=9606+0; Since the column affinity is NONE, no conversion is attempted. The values are not equal as they are different types. > One workaround is this: > > sqlite> select * From desc where number+0=9606; > 9606|Homo sapiens In this case the operation, not the column handling, forces the conversion. > If I change the table definition to this: > > CREATE TABLE desc (number integer primary key, full_line); > > Then it works as expected: Now the affinity is INTEGER. Both insert/update values as well as comparison values are converted. > Why is it doing this? I thought SQLite automatically converted from > numbers to text and vice-versa as needed. Not always. Read: http://sqlite.org/datatype3.html > Is this a bug? No. As expected. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
