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
