System.Data.SQLite

p GunID integer prim autoinvrement GunManufactor text, GunModel text, Division text, Factor text,

Table "Shooters"

ShooterInternalID integer prim autoincrement, DataBirth integer, LastName text, FirstName text,

Table "ShooterGun"

ShooterID integer prim not null, GunID integer prim not null,

The main quary im havin problems with are:

"Select Shooters.FirstName, Shooters.LastName, ShooterGun.ShooterID, GunsAndAmmo.Division, GunsAndAmmo.Factor, GunsAndAmmo.GunManufactor, GunsAndAmmo.GunModel from Shooters, ShooterGun, GunsAndAmmo"

This shoudl return about 24 results currently but are only returning 3. If I remove "ShooterGun.ShooterID" from the query then i get the correct number of results but no. The same happens if I use "Shooters.ShooterInternalID" insted of "ShooterGun.ShooterID".

This is not a problem when using a different client tool.

anonymous added on 2011-10-26 10:33:53 UTC:
Use:
SQLiteConnection cnn = new SQLiteConnection(dbConnection);
cnn.Open();
SQLiteCommand mycommand = new SQLiteCommand(cnn);
mycommand.CommandText = sql;
SQLiteDataReader reader = mycommand.ExecuteReader();
dt.Load(reader);
reader.Close();
cnn.Close();

anonymous added on 2011-10-26 12:35:07 UTC:
Problem seems to exist in 1.0.76.0 also

anonymous added on 2011-10-26 12:46:02 UTC:
values in tabels:
"GunsAndAmmo"
2, CZ, SP-01 Shaddow, Open
3, CZ, 75, Production

Table "Shooters"
1, 19800506, Aanonsen, Eirik
2, , Westol, Stein

Table "ShooterGun"
1, 3
1, 2
2, 2
2, 3

anonymous added on 2011-10-26 12:47:12 UTC:
Forgot one entry ( for making it 24 as the expected result)
3, , Eiriksa, Aasuhds

mistachkin added on 2011-10-26 14:02:31 UTC:

Have you tried using the latest System.Data.SQLite?  Version 1.0.66.0 was
released in April 2010 and quite a few things have been changed and/or fixed
since then.

Also, is the following SQL what you are doing?

CREATE TABLE GunsAndAmmo (
  GunID INTEGER PRIMARY KEY AUTOINCREMENT,
  GunManufactor TEXT,
  GunModel TEXT,
  Division TEXT,
  Factor TEXT
);

CREATE TABLE Shooters (
  ShooterInternalID INTEGER PRIMARY KEY AUTOINCREMENT,
  DataBirth INTEGER,
  LastName TEXT,
  FirstName TEXT
);

CREATE TABLE ShooterGun (
  ShooterID INTEGER NOT NULL,
  GunID INTEGER NOT NULL
);

SELECT Shooters.FirstName, Shooters.LastName,
       ShooterGun.ShooterID, GunsAndAmmo.Division,
       GunsAndAmmo.Factor, GunsAndAmmo.GunManufactor,
       GunsAndAmmo.GunModel
FROM Shooters, ShooterGun, GunsAndAmmo;


INSERT INTO GunsAndAmmo (GunID, GunManufactor, GunModel, Division, Factor)
VALUES(2, 'CZ', 'SP-01', 'Shaddow', 'Open');

INSERT INTO GunsAndAmmo (GunID, GunManufactor, GunModel, Division, Factor)
VALUES(3, 'CZ', 75, NULL, 'Production');

INSERT INTO Shooters (ShooterInternalID, DataBirth, LastName, FirstName)
VALUES(1, 19800506, 'Aanonsen', 'Eirik');

INSERT INTO Shooters (ShooterInternalID, DataBirth, LastName, FirstName)
VALUES(2, NULL, 'Westol', 'Stein');

INSERT INTO Shooters (ShooterInternalID, DataBirth, LastName, FirstName)
VALUES(3, NULL, 'Eiriksa', 'Aasuhds');

INSERT INTO ShooterGun (ShooterID, GunID) VALUES(1, 3);
INSERT INTO ShooterGun (ShooterID, GunID) VALUES(1, 2);
INSERT INTO ShooterGun (ShooterID, GunID) VALUES(2, 2);
INSERT INTO ShooterGun (ShooterID, GunID) VALUES(2, 3);

mistachkin added on 2011-10-26 14:37:49 UTC:
I'm not able to reproduce this issue with either version of System.Data.SQLite. If you can provide more specific steps to get this issue to reproduce with the latest version of System.Data.SQLite, please re-open this ticket.

anonymous added on 2011-10-28 08:56:56 UTC:
CREATE TABLE GunsAndAmmo ( GunID INTEGER PRIMARY KEY AUTOINCREMENT, GunManufactor TEXT, GunModel TEXT, Division TEXT, Factor TEXT );

CREATE TABLE Shooters ( ShooterInternalID INTEGER PRIMARY KEY AUTOINCREMENT, DataBirth INTEGER, LastName TEXT, FirstName TEXT );

CREATE TABLE ShooterGun ( ShooterID INTEGER NOT NULL, GunID INTEGER NOT NULL, PRIMARY KEY (ShooterID, GunID ));

INSERT INTO GunsAndAmmo (GunID, GunManufactor, GunModel, Division, Factor) VALUES(2, 'CZ', 'SP-01', 'Shaddow', 'Open');
INSERT INTO GunsAndAmmo (GunID, GunManufactor, GunModel, Division, Factor) VALUES(3, 'CZ', 75, NULL, 'Production');

INSERT INTO Shooters (ShooterInternalID, DataBirth, LastName, FirstName) VALUES(1, 19800506, 'Aanonsen', 'Eirik');
INSERT INTO Shooters (ShooterInternalID, DataBirth, LastName, FirstName) VALUES(2, NULL, 'Westol', 'Stein');
INSERT INTO Shooters (ShooterInternalID, DataBirth, LastName, FirstName) VALUES(3, NULL, 'Eiriksa', 'Aasuhds');

INSERT INTO ShooterGun (ShooterID, GunID) VALUES(1, 3);
INSERT INTO ShooterGun (ShooterID, GunID) VALUES(1, 2);
INSERT INTO ShooterGun (ShooterID, GunID) VALUES(2, 2);
INSERT INTO ShooterGun (ShooterID, GunID) VALUES(2, 3);

SELECT Shooters.FirstName, Shooters.LastName, ShooterGun.ShooterID, GunsAndAmmo.Division, GunsAndAmmo.Factor, GunsAndAmmo.GunManufactor, GunsAndAmmo.GunModel
FROM Shooters, ShooterGun, GunsAndAmmo;

With this the problem exist. Gets only 2 results back. Also under 1.0.76.0 version. The differance is the ShooterGun table where I defined the two collums as primary keys.

Thank you for looking on this problem and please point out if I am wrong about something.

anonymous added on 2011-10-28 09:34:47 UTC:
IF I create the ShooterGun table with
UNIQUE (ShooterID, GunID )
insted of
PRIMARY KEY (ShooterID, GunID )
, it works as expected. Is this some design feture? I'm a bit puzzeled. Thanx for all and any feedback

mistachkin added on 2011-10-28 14:33:49 UTC:
I've been able to reproduce the results you are seeing. It appears that the built-in System.Data.DataTable class in the .NET Framework gets confused by the schema of your database. I'm still investigating this issue; however, I'm not sure how much can be done since the results returned by the System.Data.SQLite components are indeed correct (i.e. they return 24 rows).

mistachkin added on 2011-11-12 06:47:27 UTC:
May be related to this code fragment.

mistachkin added on 2011-11-13 06:00:47 UTC:
Fixed by check-in [da3e0bfb53]. The root cause of this issue was treating a column as 'unique' in multi-table data reader results just because it was unique in the underlying database table. For types of joins that can include multiple rows with the same value in that particular column (e.g. a cross join), this can cause serious problems.

mistachkin added on 2011-11-13 06:25:30 UTC:
Now checked into trunk [8fe2a401d6]. If possible, please test this fix in your environment and report back. If you need a custom binary compiled to facilitate this testing, just ask.