Join doesn't work

[MBulli]

Hi,

given the following Tables:

CREATE TABLE TestA
(
    ID INTEGER PRIMARY KEY,
    Txt TEXT
);

CREATE TABLE TestB
(
    ID INTEGER PRIMARY KEY,
    Aref INTEGER,
    Txt TEXT,

    FOREIGN KEY(Aref) REFERENCES TestA(ID)
);

INSERT INTO TestA
VALUES (1, 'This is Test A!');

INSERT INTO TestB
VALUES (1, 1, 'This is TestB!');

This line fails:

db["TestB"].Find(db["TestB"]["TestA"]["ID"] == db["TestB"]["Aref"])

The generated SQL statement:

select [TestB].* from [TestB]  JOIN [TestA] ON ([TestA].[Aref] = [TestB].[ID]) where [TestA].[ID] = [TestB].[Aref]

As far as I can tell the tables in the ON part are inverted. I tested it with: Simple.Data.Core/Ado v0.12.2.2 and Simple.Data.Sqlite v0.12.2.4.

I also checked the behavior of the sqlserver which is doing it right.

[NotMyself]

Thank you for reporting. I will take a look this weekend. If you have a chance could you submit a failing test? Would make it easier for me to track down.

[MBulli]

[Test]
public void JoinOnSuppliersProducts()
{
    var db = Database.OpenFile(DatabasePath);
    var data = db["Suppliers"].Find(db["Suppliers"]["Products"]["SupplierID"] == db["Supplieres"]["SupplierID"]).ToList();
    var item = data.First();
    Assert.Pass();
}

This test is design for the northwind.db and should generate a query like that:

SELECT *
FROM Suppliers JOIN Products
ON (Suppliers.SupplierID = Products.SupplierID)

There is also another syntax for joins but I can't figure out how it works ...