You are right, your example works correctly. I created my database again with the script below and I tried some changes. The error still occurs with System.Data.Sqlite 3.8.5. If I drop the index idx_Pylones_lig_id, the results are good again. So I think there is something wrong with System.Data.Sqlite 3.8.5 when it exists an index on another column than the primary key.
Regards. Eric DAVID ----- CREATE TABLE Lignes (lig_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, lig_nom TEXT NOT NULL); SELECT AddGeometryColumn('Lignes', 'lig_geom', 2154, 'LINESTRING', 'XY'); SELECT CreateSpatialIndex('Lignes', 'lig_geom'); CREATE TABLE Pylones (pyl_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, lig_id INTEGER NOT NULL, pyl_num TEXT NOT NULL, CONSTRAINT FK_Pylones_Lignes FOREIGN KEY (lig_id) REFERENCES Lignes (lig_id)); SELECT AddGeometryColumn('Pylones', 'pyl_geom', 2154, 'POINT', 'XY'); SELECT CreateSpatialIndex('Pylones', 'pyl_geom'); CREATE INDEX idx_Pylones_lig_id ON Pylones (lig_id); INSERT INTO Lignes VALUES (NULL, 'Ligne 1', GeomFromText('LINESTRING(916798.8857 6508700.006676, 917132.628242 6509169.221275, 917228.883041 6509615.499456, 917147.428294 6510600.332165, 917130.811154 6511282.374372, 916891.290563 6511604.151782, 916613.096127 6512076.129893)', 2154)); INSERT INTO Lignes VALUES (NULL, 'Ligne 2', GeomFromText('LINESTRING(916001.23132 6509898.228866, 916928.18227 6510219.970372, 917674.889861 6509913.815551, 918338.55431 6510407.669698)', 2154)); INSERT INTO Pylones VALUES (NULL, 1, '1', GeomFromText('POINT(916798.8857 6508700.006676)', 2154)); INSERT INTO Pylones VALUES (NULL, 1, '2', GeomFromText('POINT(917132.628242 6509169.221275)', 2154)); INSERT INTO Pylones VALUES (NULL, 1, '3', GeomFromText('POINT(917228.883041 6509615.499456)', 2154)); INSERT INTO Pylones VALUES (NULL, 1, '3 bis', GeomFromText('POINT(917147.428294 6510600.332165)', 2154)); INSERT INTO Pylones VALUES (NULL, 1, '5', GeomFromText('POINT(917130.811154 6511282.374372)', 2154)); INSERT INTO Pylones VALUES (NULL, 1, '6', GeomFromText('POINT(916891.290563 6511604.151782)', 2154)); INSERT INTO Pylones VALUES (NULL, 1, '7', GeomFromText('POINT(916613.096127 6512076.129893)', 2154)); INSERT INTO Pylones VALUES (NULL, 2, '1', GeomFromText('POINT(916001.23132 6509898.228866)', 2154)); INSERT INTO Pylones VALUES (NULL, 2, '2', GeomFromText('POINT(916928.18227 6510219.970372)', 2154)); INSERT INTO Pylones VALUES (NULL, 2, '3', GeomFromText('POINT(917674.889861 6509913.815551)', 2154)); INSERT INTO Pylones VALUES (NULL, 2, '4', GeomFromText('POINT(918338.55431 6510407.669698)', 2154)); ----- -----Message d'origine----- De : sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] De la part de Clemens Ladisch Envoyé : lundi 28 juillet 2014 16:32 À : sqlite-users@sqlite.org Objet : Re: [sqlite] System.Data.Sqlite Eric DAVID wrote: > But this one gives correct results only with Spatialite GUI (Sqlite > version > 3.7.3) or QGIS (Sqlite version 3.7.17) but wrong results with > System.Data.Sqlite (version 3.8.5). > > SELECT * FROM Pylones P > JOIN Lignes L ON L.lig_id = P.lig_id > JOIN idx_Pylones_pyl_geom I ON P.pyl_id = I.pkid WHERE L.lig_id = 3; This query works correctly under 3.8.5 with the following database: CREATE TABLE lignes(lig_id INTEGER PRIMARY KEY); CREATE TABLE pylones(pyl_id INTEGER PRIMARY KEY, lig_id); CREATE VIRTUAL TABLE idx_Pylones_pyl_geom USING rtree(pkid, xmin, xmax, ymin, ymax); INSERT INTO lignes VALUES(3); INSERT INTO pylones VALUES(1, 3); INSERT INTO pylones VALUES(2, 3); INSERT INTO idx_Pylones_pyl_geom VALUES(1, 0, 0, 0, 0); INSERT INTO idx_Pylones_pyl_geom VALUES(2, 0, 0, 0, 0); I guess your database might be different, but you haven't told us about it ... Regards, Clemens _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users