Hi,
I submitted this some time ago, see
http://www.sqlite.org/cvstrac/tktview?tn=3437,39.
I've just been messing about with this again and have found out what was
happening. The following script demonstrates the problem and resolution:
[begin bug2.sql]
/*
this command line will demonstrate the problem:
del bug.db or rm bug.db, to taste
sqlite3 bug.db < bug2.sql
*/
BEGIN TRANSACTION;
CREATE TABLE races (ID, Name, Date, Distance, Climb, Comment);
INSERT INTO "races" VALUES('20','totley
moor','2009-05-19',NULL,NULL,NULL);
CREATE TABLE times (RaceID, RaceNo, RaceTime, Name, Team, Cat,
RacePosition);
INSERT INTO "times" VALUES(20,'804','01:08:51','CLARKE,
Helen','','FV40','233');
COMMIT;
SELECT "looking for totley moor 09 results";
SELECT RaceTime, RaceNo, Name, Team, Cat, RacePosition FROM times
WHERE RaceId = (SELECT ID FROM Races WHERE Name = 'totley moor' AND Date
LIKE "2009%");
SELECT "";
/* the sub queries are ok */
SELECT "race id for totley moor 09";
SELECT ID FROM Races WHERE Name = 'totley moor' AND Date LIKE "2009%";
SELECT "";
/* the data is actually there */
SELECT "totley moor 09 data";
SELECT * FROM times WHERE RaceId = 20;
/* now do it so it works */
SELECT "now doing it properly";
SELECT "============================================";
SELECT "";
DROP TABLE races;
DROP TABLE times;
BEGIN TRANSACTION;
CREATE TABLE races (ID, Name, Date, Distance, Climb, Comment);
/* note 20, as opposed to '20' in line below */
INSERT INTO "races" VALUES(20,'totley
moor','2009-05-19',NULL,NULL,NULL);
CREATE TABLE times (RaceID, RaceNo, RaceTime, Name, Team, Cat,
RacePosition);
INSERT INTO "times" VALUES(20,'804','01:08:51','CLARKE,
Helen','','FV40','233');
COMMIT;
SELECT "looking for totley moor 09 results";
SELECT RaceTime, RaceNo, Name, Team, Cat, RacePosition FROM times
WHERE RaceId = (SELECT ID FROM Races WHERE Name = 'totley moor' AND Date
LIKE "2009%");
SELECT "";
/* the sub queries are ok */
SELECT "race id for totley moor 09";
SELECT ID FROM Races WHERE Name = 'totley moor' AND Date LIKE "2009%";
SELECT "";
/* the data is actually there */
SELECT "totley moor 09 data";
SELECT * FROM times WHERE RaceId = 20;
[end bug2.sql]
My feeling is that this behaviour is arguably correct, but confusing. I
had the idea that sqlite isn't strongly typed?
I'm not exactly sure how I created this mix up, I use my program,
sqlite3 and the sqlite database browser to manipulate the DB, so there's
plenty of scope for error :-)
BTW, I enjoy using sqlite, does just what I need.
Cheers
NickJ
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
