System.Data.Sqlite

classic Classic list List threaded Threaded
17 messages Options
Reply | Threaded
Open this post in threaded view
|

System.Data.Sqlite

Eric DAVID
Hi,

 

I have a problem using System.Data.Sqlite in a C# application. I have 2
tables, each table with a geometry field and a spatial index, so the
following request give me the results I want using Spatialite GUI or QGIS :

 

SELECT * FROM Pylones P

JOIN Lignes L ON L.lig_id = P.lig_id AND L.lig_id IN (3,4)

JOIN idx_Pylones_pyl_geom I ON P.pyl_id = I.pkid AND I.xmax >= 910202.5 AND
I.ymax >= 6506392.5 AND I.xmin <= 924587.5 AND I.ymin <= 6513427.5

 

But, when I try to use the same request with System.Data.Sqlite, I only
obtain one record (the first one). The only way I found to obtain the
correct results is to remove joins like this :

 

SELECT pyl_id, lig_id, pyl_num, pyl_geom FROM Pylones

WHERE lig_id IN (3,4)

AND pyl_id IN (SELECT pkid FROM idx_Pylones_pyl_geom WHERE xmax >= 910202.5
AND ymax >= 6506392.5 AND xmin <= 924587.5 AND ymin <= 6513427.5)

 

So, I would like to know if there is a bug in System.Data.SQLite or if I
don't understand something using this assembly.

 

Eric DAVID

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: System.Data.Sqlite

Simon Slavin-3

On 28 Jul 2014, at 1:01pm, Eric DAVID <[hidden email]> wrote:

> I have a problem using System.Data.Sqlite in a C# application. I have 2
> tables, each table with a geometry field and a spatial index, so the
> following request give me the results I want using Spatialite GUI or QGIS :
>
>
>
> SELECT * FROM Pylones P
>
> JOIN Lignes L ON L.lig_id = P.lig_id AND L.lig_id IN (3,4)
>
> JOIN idx_Pylones_pyl_geom I ON P.pyl_id = I.pkid AND I.xmax >= 910202.5 AND
> I.ymax >= 6506392.5 AND I.xmin <= 924587.5 AND I.ymin <= 6513427.5
>
>
>
> But, when I try to use the same request with System.Data.Sqlite, I only
> obtain one record (the first one).

Can you simplify this ?  Perhaps get rid of one of the JOINed tables, or remove one of the clauses until it is a clear demonstration of an error ?

Also, can you check to see which versions of SQLite each of your tools is using ?  The output from "SELECT sqlite_version()" would be fine.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: System.Data.Sqlite

Eric DAVID
Well, I tried some different requests to simplify. This one is OK in all
cases :

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;

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;

All is right when I join only 2 tables :

SELECT * FROM Pylones P
JOIN Lignes L ON L.lig_id = P.lig_id
WHERE L.lig_id = 3;

SELECT lig_id, lig_nom, lig_geom FROM Lignes L
JOIN idx_Lignes_lig_geom I ON (L.lig_id = I.pkid AND I.xmax >= 910202.5 AND
I.ymax >= 6506392.5 AND I.xmin <= 924587.5 AND I.ymin <= 6513427.5);

-----Message d'origine-----
De : [hidden email]
[mailto:[hidden email]] De la part de Simon Slavin
Envoyé : lundi 28 juillet 2014 15:43
À : General Discussion of SQLite Database
Objet : Re: [sqlite] System.Data.Sqlite


On 28 Jul 2014, at 1:01pm, Eric DAVID <[hidden email]> wrote:

> I have a problem using System.Data.Sqlite in a C# application. I have
> 2 tables, each table with a geometry field and a spatial index, so the
> following request give me the results I want using Spatialite GUI or QGIS
:

>
>
>
> SELECT * FROM Pylones P
>
> JOIN Lignes L ON L.lig_id = P.lig_id AND L.lig_id IN (3,4)
>
> JOIN idx_Pylones_pyl_geom I ON P.pyl_id = I.pkid AND I.xmax >=
> 910202.5 AND I.ymax >= 6506392.5 AND I.xmin <= 924587.5 AND I.ymin <=
> 6513427.5
>
>
>
> But, when I try to use the same request with System.Data.Sqlite, I
> only obtain one record (the first one).

Can you simplify this ?  Perhaps get rid of one of the JOINed tables, or
remove one of the clauses until it is a clear demonstration of an error ?

Also, can you check to see which versions of SQLite each of your tools is
using ?  The output from "SELECT sqlite_version()" would be fine.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: System.Data.Sqlite

Clemens Ladisch
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: System.Data.Sqlite

Eric DAVID
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 : [hidden email]
[mailto:[hidden email]] De la part de Clemens Ladisch
Envoyé : lundi 28 juillet 2014 16:32
À : [hidden email]
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: System.Data.Sqlite

Simon Slavin-3

On 28 Jul 2014, at 5:51pm, Eric DAVID <[hidden email]> wrote:

> 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.

Thank you for your script for spotting the error.  We should be able to investigate this.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: System.Data.Sqlite

Joe Mistachkin-3
In reply to this post by Eric DAVID

Eric DAVID wrote:
>
> 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;
>

Is the above query the one that has the issue?  What is the output you
expect from it?
 
--
Joe Mistachkin

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: System.Data.Sqlite

Eric DAVID
Yes, it is this one. With the new script I gave in my last mail it has
changed to

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 = 1;

And the expected result is :

1 1 1 BLOB sz=60 GEOMETRY 1 Ligne 1 BLOB sz=160
GEOMETRY 1 916798.875000 916798.875000 6508700.000000
6508700.000000
2 1 2 BLOB sz=60 GEOMETRY 1 Ligne 1 BLOB sz=160
GEOMETRY 2 917132.625000 917132.625000 6509169.000000
6509169.000000
3 1 3 BLOB sz=60 GEOMETRY 1 Ligne 1 BLOB sz=160
GEOMETRY 3 917228.875000 917228.875000 6509615.500000
6509615.500000
4 1 3 bis BLOB sz=60 GEOMETRY 1 Ligne 1 BLOB sz=160
GEOMETRY 4 917147.437500 917147.437500 6510600.500000
6510600.500000
5 1 5 BLOB sz=60 GEOMETRY 1 Ligne 1 BLOB sz=160
GEOMETRY 5 917130.812500 917130.812500 6511282.500000
6511282.500000
6 1 6 BLOB sz=60 GEOMETRY 1 Ligne 1 BLOB sz=160
GEOMETRY 6 916891.312500 916891.312500 6511604.000000
6511604.000000
7 1 7 BLOB sz=60 GEOMETRY 1 Ligne 1 BLOB sz=160
GEOMETRY 7 916613.125000 916613.125000 6512076.000000
6512076.000000

With System.Data.Sqlite 3.8.5 and an index on Pylones.lig_id the result is
only :

1 1 1 BLOB sz=60 GEOMETRY 1 Ligne 1 BLOB sz=160
GEOMETRY 1 916798.875000 916798.875000 6508700.000000
6508700.000000

-----Message d'origine-----
De : [hidden email]
[mailto:[hidden email]] De la part de Joe Mistachkin
Envoyé : lundi 28 juillet 2014 23:11
À : 'General Discussion of SQLite Database'
Objet : Re: [sqlite] System.Data.Sqlite


Eric DAVID wrote:
>
> 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;
>

Is the above query the one that has the issue?  What is the output you
expect from it?
 
--
Joe Mistachkin

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: System.Data.Sqlite

Joe Mistachkin-3

Eric DAVID wrote:
>
> Yes, it is this one. With the new script I gave in my last mail it has
> changed to
>
> 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 = 1;
>

So far, I'm unable to reproduce your results here.

Could you please provide the list of all SQL statements used to get the
database into the state where the issue is seen, including any INSERT
statements used to populate it with the test data.

--
Joe Mistachkin

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: System.Data.Sqlite

Simon Slavin-3

> On 28 Jul 2014, at 10:58pm, Joe Mistachkin <[hidden email]> wrote:
>
> Eric DAVID wrote:
>
>> Yes, it is this one. With the new script I gave in my last mail it has
>> changed to
>>
>> 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 = 1;
>
> So far, I'm unable to reproduce your results here.
>
> Could you please provide the list of all SQL statements used to get the
> database into the state where the issue is seen, including any INSERT
> statements used to populate it with the test data.

I'm beginning to wonder whether there's a problem with the handling of the floating point numbers used for this data.  A number with ten significant digits like these:

917130.8125 917147.4375

could run up against a slop or fiddle factor when assessing equality or inequality.  Solving the problem could involve testing in the same programming language that the original user used.

Original Poster: if you take the list of commands you showed us in your earlier post and enter them straight into the SQLite shell tool (to create a new database) do you get the same results as you get when you execute them in your own program ?

Simon.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: System.Data.Sqlite

Eric DAVID
In reply to this post by Joe Mistachkin-3
Here is the list of all sql statements in the file Base.sql, included INSERT
Statements.
I think the issue comes from the index idx_Pylones_lig_id.

Eric DAVID

-----Message d'origine-----
De : [hidden email]
[mailto:[hidden email]] De la part de Joe Mistachkin
Envoyé : lundi 28 juillet 2014 23:59
À : 'General Discussion of SQLite Database'
Objet : Re: [sqlite] System.Data.Sqlite


Eric DAVID wrote:
>
> Yes, it is this one. With the new script I gave in my last mail it has
> changed to
>
> 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 = 1;
>

So far, I'm unable to reproduce your results here.

Could you please provide the list of all SQL statements used to get the
database into the state where the issue is seen, including any INSERT
statements used to populate it with the test data.

--
Joe Mistachkin

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: System.Data.Sqlite

Eric DAVID
Well, It seems to be impossible to join files to mails. So, here are the
statements :

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 : [hidden email]
[mailto:[hidden email]] De la part de Eric DAVID
Envoyé : mardi 29 juillet 2014 01:40
À : 'General Discussion of SQLite Database'
Objet : Re: [sqlite] System.Data.Sqlite

Here is the list of all sql statements in the file Base.sql, included INSERT
Statements.
I think the issue comes from the index idx_Pylones_lig_id.

Eric DAVID

-----Message d'origine-----
De : [hidden email]
[mailto:[hidden email]] De la part de Joe Mistachkin
Envoyé : lundi 28 juillet 2014 23:59 À : 'General Discussion of SQLite
Database'
Objet : Re: [sqlite] System.Data.Sqlite


Eric DAVID wrote:
>
> Yes, it is this one. With the new script I gave in my last mail it has
> changed to
>
> 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 = 1;
>

So far, I'm unable to reproduce your results here.

Could you please provide the list of all SQL statements used to get the
database into the state where the issue is seen, including any INSERT
statements used to populate it with the test data.

--
Joe Mistachkin

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: System.Data.Sqlite

Joe Mistachkin-3
In reply to this post by Eric DAVID

Eric DAVID wrote:
>
> Here is the list of all sql statements in the file Base.sql, included
> INSERT Statements.
>

Unfortunately, the mailing list strips most attachments.  Can you include
the information within the message itself?

--
Joe Mistachkin

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: System.Data.Sqlite

Simon Slavin-3
In reply to this post by Eric DAVID

On 29 Jul 2014, at 12:43am, Eric DAVID <[hidden email]> wrote:

> Well, It seems to be impossible to join files to mails. So, here are the
> statements :

Unfortunate I'm not willing to download SpatiaLite just to test this.  But I'm still suspecting that the problem is in your high-precision numbers.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: System.Data.Sqlite

Clemens Ladisch
In reply to this post by Eric DAVID
Eric DAVID wrote:
> Well, It seems to be impossible to join files to mails. So, here are the
> statements :
>
> CREATE TABLE Lignes (lig_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, lig_nom TEXT NOT NULL);
> SELECT AddGeometryColumn('Lignes', 'lig_geom', 2154, 'LINESTRING', 'XY');
> ...

Simplified for plain ol' SQLite as follows:

CREATE TABLE L (Lid INTEGER PRIMARY KEY);
INSERT INTO L VALUES(1);
CREATE TABLE P (Pid INTEGER PRIMARY KEY, Lid INTEGER);
INSERT INTO P VALUES(1,1);
INSERT INTO P VALUES(2,1);
CREATE VIRTUAL TABLE R USING rtree(Pid, xmin, xmax);
INSERT INTO R VALUES(1,0,0);
INSERT INTO R VALUES(2,0,0);
CREATE INDEX idx_P_Lid ON P (Lid);

-- fails, result should be two rows:
SELECT * FROM P JOIN L USING (Lid) JOIN R USING (Pid) WHERE L.Lid = 1;


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: System.Data.Sqlite

Eric DAVID
Yes, this is exactly the same issue I encounter.
There are 2 rows in the result with Spatialite GUI, but only one with
System.Data.Sqlite.

-----Message d'origine-----
De : [hidden email]
[mailto:[hidden email]] De la part de Clemens Ladisch
Envoyé : mardi 29 juillet 2014 10:49
À : [hidden email]
Objet : Re: [sqlite] System.Data.Sqlite

Eric DAVID wrote:
> Well, It seems to be impossible to join files to mails. So, here are
> the statements :
>
> CREATE TABLE Lignes (lig_id INTEGER PRIMARY KEY AUTOINCREMENT NOT
> NULL, lig_nom TEXT NOT NULL); SELECT AddGeometryColumn('Lignes',
> 'lig_geom', 2154, 'LINESTRING', 'XY'); ...

Simplified for plain ol' SQLite as follows:

CREATE TABLE L (Lid INTEGER PRIMARY KEY); INSERT INTO L VALUES(1); CREATE
TABLE P (Pid INTEGER PRIMARY KEY, Lid INTEGER); INSERT INTO P VALUES(1,1);
INSERT INTO P VALUES(2,1); CREATE VIRTUAL TABLE R USING rtree(Pid, xmin,
xmax); INSERT INTO R VALUES(1,0,0); INSERT INTO R VALUES(2,0,0); CREATE
INDEX idx_P_Lid ON P (Lid);

-- fails, result should be two rows:
SELECT * FROM P JOIN L USING (Lid) JOIN R USING (Pid) WHERE L.Lid = 1;


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: System.Data.Sqlite

Richard Hipp-3
In reply to this post by Clemens Ladisch
On Tue, Jul 29, 2014 at 4:48 AM, Clemens Ladisch <[hidden email]> wrote:

>
> Simplified for plain ol' SQLite as follows:
>

Thank you.  That is exactly what we needed.

The bug has now been characterized, studied, and fixed.  See [
http://www.sqlite.org/src/info/d2889096e7bdeac6] for the ticket and [
http://www.sqlite.org/src/info/8cc41b0bf365] for a patch that fixes the
problem.  The problem is fixed on trunk and an amalgamation build of the
latest trunk is available on the download page at [
http://www.sqlite.org/download.html]



>
> CREATE TABLE L (Lid INTEGER PRIMARY KEY);
> INSERT INTO L VALUES(1);
> CREATE TABLE P (Pid INTEGER PRIMARY KEY, Lid INTEGER);
> INSERT INTO P VALUES(1,1);
> INSERT INTO P VALUES(2,1);
> CREATE VIRTUAL TABLE R USING rtree(Pid, xmin, xmax);
> INSERT INTO R VALUES(1,0,0);
> INSERT INTO R VALUES(2,0,0);
> CREATE INDEX idx_P_Lid ON P (Lid);
>
> -- fails, result should be two rows:
> SELECT * FROM P JOIN L USING (Lid) JOIN R USING (Pid) WHERE L.Lid = 1;
>
>
> Regards,
> Clemens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users