Hi,
I've been using Sqlite on and off for a while now, with great results. Awesome
piece of software :) Yesterday I ran into an odd problem which has me stumped.
Perhaps I'm overlooking something ridiculously simple? My original
implementation
is quite a bit more complicated, so I've simplified it just enough to be
reproducable
(I hope ...)
When I run:
rm ./testme.db ; sqlite3 ./testme.db < input.sql
The first sql batch sequence below returns nothing (unexpected). The second
sql
sequence returns the value that I expected. The only difference between the two
sequences is one uses a TEXT value of "md5" and the other uses a TEXT value of
"hu5"
------- this fails (no result)
----------------------------------------------------
CREATE TABLE f_main (id INTEGER PRIMARY KEY,pathid INTEGER,name TEXT);
INSERT INTO f_main VALUES(4,5,"my_filename");
CREATE TABLE f_path (pathid INTEGER PRIMARY KEY,name TEXT);
INSERT INTO f_path VALUES(5,"md5");
CREATE TABLE f_stats (id INTEGER PRIMARY KEY,size INTEGER,md5 TEXT);
INSERT INTO f_stats VALUES(4,973,"16f5a3c8edc1668d7318f6113b810009");
SELECT s.id,s.size,s.md5
FROM f_main m,f_stats s,f_path p
WHERE m.name="my_filename" AND p.name="md5" AND m.pathid=p.pathid AND m.id=s.id;
------ but this succeeds: 4|973|16f5a3c8edc1668d7318f6113b810009
-------------------
CREATE TABLE f_main (id INTEGER PRIMARY KEY,pathid INTEGER,name TEXT);
INSERT INTO f_main VALUES(4,5,"my_filename");
CREATE TABLE f_path (pathid INTEGER PRIMARY KEY,name TEXT);
INSERT INTO f_path VALUES(5,"hu5");
CREATE TABLE f_stats (id INTEGER PRIMARY KEY,size INTEGER,md5 TEXT);
INSERT INTO f_stats VALUES(4,973,"16f5a3c8edc1668d7318f6113b810009");
SELECT s.id,s.size,s.md5
FROM f_main m,f_stats s,f_path p
WHERE m.name="my_filename" AND p.name="hu5" AND m.pathid=p.pathid AND m.id=s.id;
----------------------------------------------------------------------------
I get the same results on 3 different Linux machines, across 3 different Sqlite
versions. 2 versions were Ubuntu-based and the third (3.7.4) I compiled from
source obtained from the sqlite.org website.
Linux outpost3 2.6.24-23-xen #1 SMP Mon Jan 26 03:09:12 UTC 2009 x86_64
GNU/Linux
a virtual server (xen based) running Ubuntu 8
/dev/sda1 on / type ext3 (rw,relatime,errors=remount-ro)
sqlite3 3.4.2
sqlite3 3.7.4
Linux castle 2.6.32-23-generic #37-Ubuntu SMP Fri Jun 11 07:54:58 UTC 2010 i686
GNU/Linux
a desktop machine running Ubuntu 10
/dev/sda1 on / type reiserfs (rw,relatime,notail)
sqlite3 3.6.22
sqlite3 3.7.4
Linux guardian 2.4.28 #1 Fri Dec 10 18:55:16 CST 2004 i686 unknown unknown
GNU/Linux
an older server running slackware 10
/dev/hda2 on / type reiserfs (rw)
sqlite3 3.7.4
So, am I missing something? Or is this a bug of some sort?
Thanks for any help,
Ed Nolan
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
