| Bug #5357 | pb with mbrcontains and real numbers | ||
|---|---|---|---|
| Submitted: | 1 Sep 2004 23:34 | Modified: | 13 Apr 2005 22:38 |
| Reporter: | [ name withheld ] | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.1.7 | OS: | Windows (windows 2000) |
| Assigned to: | CPU Architecture: | Any | |
[2 Sep 2004 14:14]
MySQL Verification Team
Thank you for the report!
I tested this on the latest BK 5.0 tree and failed to repeat it.
mysql> select astext(geometrie) from gestionmap where
-> MBRContains(GeomFromText('Polygon((-180.1 -90.1,-180.1 90.1,180.1
'> 90.1,180.1 -90.1,-180.1 -90.1))'),geometrie);
+----------------------------------------------------------------------------------------------------------------+
| astext(geometrie) |
+----------------------------------------------------------------------------------------------------------------+
| POLYGON((29.751307 31.332317,30.10916 31.332317,30.10916 31.077066,29.751307 31.077066,29.751307 31.332317)) |
| POLYGON((29.751255 31.32349,30.092671 31.32349,30.092671 31.093151,29.751255 31.093151,29.751255 31.32349)) |
| POLYGON((29.751369 31.323861,30.067349 31.323861,30.067349 31.090876,29.751369 31.090876,29.751369 31.323861)) |
| POLYGON((29.751305 31.350013,30.109187 31.350013,30.109187 31.077006,29.751305 31.077006,29.751305 31.350013)) |
+----------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)
[2 Sep 2004 20:27]
[ name withheld ]
Hi and thx to answer me. it's very strange that you failed to repeat the pb :-( Do you use a spatial index in the geometrie field ? In my database i use it.
[2 Sep 2004 22:49]
MySQL Verification Team
I created a spatial index and tested again - the same result:
mysql> alter table gestionmap add spatial index(geometrie);
Query OK, 5 rows affected (0.08 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select astext(geometrie) from gestionmap where
-> MBRContains(GeomFromText('Polygon((-180.1 -90.1,-180.1 90.1,180.1
'> 90.1,180.1 -90.1,-180.1 -90.1))'),geometrie);
+----------------------------------------------------------------------------------------------------------------+
| astext(geometrie) |
+----------------------------------------------------------------------------------------------------------------+
| POLYGON((29.751307 31.332317,30.10916 31.332317,30.10916 31.077066,29.751307 31.077066,29.751307 31.332317)) |
| POLYGON((29.751255 31.32349,30.092671 31.32349,30.092671 31.093151,29.751255 31.093151,29.751255 31.32349)) |
| POLYGON((29.751369 31.323861,30.067349 31.323861,30.067349 31.090876,29.751369 31.090876,29.751369 31.323861)) |
| POLYGON((29.751305 31.350013,30.109187 31.350013,30.109187 31.077006,29.751305 31.077006,29.751305 31.350013)) |
+----------------------------------------------------------------------------------------------------------------+
4 rows in set (0.02 sec)
[2 Sep 2004 22:55]
[ name withheld ]
Argh, May be it's due to a problem in my database and not with the command. What is your opinion about that ? tomorrow I'll try to rebuild my database and try the command again. Thx again
[3 Sep 2004 19:30]
[ name withheld ]
i found the problem. It was in my database. I dindn't undestand exactly the problem but i solved it :-) Thx for your help and sorry for my poor english.
[7 Nov 2004 12:35]
Mark Overmeer
I have exactly the same problem as described here, so I would really like to hear the solution.
In my situation,
SELECT name FROM scenes
WHERE MBRContains(GeomFromText(AsText(area)),GeomFromText('POINT(23.9 12.3)'));
does produce results, where
SELECT name FROM scenes
WHERE MBRContains(area, GeomFromText('POINT(23.9 12.3)'));
does not.
However, on the other hand
SELECT name FROM scenes
WHERE MBRContains(area, GeomFromText('POINT(24 12)'));
works as expected. So, apparently, it looks like fractions are not handled correctly. I do have a geometry index on area. The database was build from fresh, with over 15,000 polygons. It runs on Linux/mysql 4.1.7/2.6.8.1 kernel.
One of the areas found (if it works) is:
POLYGON((23.236475268869 11.990001299805,23.914341636288 11.890306643452,24.000686376015 12.452951198271,23.321502205667 12.55310422086,23.236475268869 11.990001299805))
[10 Nov 2004 3:50]
[ name withheld ]
Good to see GIS in MySQL. Thanks guys.
MYSQL 4.17 - Solaris 2.9 & AIX
It's a bug with the index and floating point numbers. If you get rid of the index it works fine (and slow of cause), if you round the numbers to 1 dec place it also works.
select astext(coordBBox) from MapSet where mapName ='k5523';
+---------------------------------------------------------------------------------------------+
| astext(coordBBox) |
+---------------------------------------------------------------------------------------------+
| POLYGON((146.499999 -44,146.499999 -41.75,148.583334 -41.75,148.583334 -44,146.499999 -44)) |
+---------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select count(*) from Feature where MBRContains((select coordBBox from MapSet where mapName ='k5523'), featureCoordinates);
+----------+
| count(*) |
+----------+
| 24038 |
+----------+
1 row in set (0.24 sec)
mysql> ALTER TABLE Feature ADD INDEX BBox(featureCoordinates); Query OK, 69693 rows affected (3.32 sec)
Records: 69693 Duplicates: 0 Warnings: 0
mysql> select count(*) from Feature where MBRContains((select coordBBox from MapSet where mapName ='k5523'), featureCoordinates);
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql> ALTER TABLE Feature DROP INDEX BBox; Query OK, 69693 rows affected (2.98 sec)
Records: 69693 Duplicates: 0 Warnings: 0
mysql> select count(*) from Feature where MBRContains((select coordBBox from MapSet where mapName ='k5523'), featureCoordinates);
+----------+
| count(*) |
+----------+
| 24038 |
+----------+
1 row in set (0.24 sec)
select count(*) from Feature where MBRContains(geomfromtext('POLYGON((146.5 -44,146.5 -41.75,148.5 -41.75,148.5 -44,146.5 -44))'), featureCoordinates);
+----------+
| count(*) |
+----------+
| 24032 |
+----------+
1 row in set (0.38 sec)
I notice that there has been some trouble before with something similar see:
http://lists.mysql.com/internals/16964:
``This bug is the result of weird error happening with mi_float8get and double
arythmetic. I described that in 'Bug that looks potentially dangerous' email''
Hope this helps someone fix the bug.
Cheers,
Dave Penton
www.cs.mu.oz.au/~djpenton/
[10 Nov 2004 11:43]
Mark Overmeer
I hope someone will change this status from "Can't repeat" into something more serious. It seems to go wrong only if the first coordinate in the polygon is a fraction. At least, in my case. In my application, that value is xmin (always), the first point is xmin,ymin (always) mark@overmeer.net
[10 Nov 2004 23:04]
[ name withheld ]
Hi,
Here's a short example that can be repeated to show the bug. Hopefully this can be added to the test suite etc.
CREATE TABLE Feature (
featureID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
featureTypeID INTEGER UNSIGNED NOT NULL,
featureCoordinates GEOMETRY NULL,
PRIMARY KEY(featureID),
INDEX BBox(featureCoordinates)
);
INSERT INTO Feature VALUES (NULL, (SELECT featureTypeID FROM FeatureType WHERE ShortDescription="Contours_Polygon"), GeomFromText("POLYGON((143.82441 -39.67771,143.82557 -39.6774,143.82587 -39.67669,143.82516 -39.67423,143.82684 -39.67229,143.82967 -39.67122,143.83595 -39.67099,143.83677 -39.67078,143.83741 -39.67009,143.83725 -39.66838,143.8364 -39.66769,143.83244 -39.66756,143.83107 -39.66639,143.82812 -39.66556,143.82708 -39.6655,143.82489 -39.66614,143.82417 -39.668,143.82104 -39.66927,143.81986 -39.67031,143.81987 -39.67085,143.81991 -39.67256,143.81866 -39.67342,143.81874 -39.67539,143.81941 -39.67613,143.82227 -39.6774,143.82441 -39.67771))"));
INSERT INTO Feature VALUES (NULL, (SELECT featureTypeID FROM FeatureType WHERE ShortDescription="Contours_Polygon"), GeomFromText("POLYGON((147.5413 -39.59318,147.54158 -39.59288,147.5415 -39.59244,147.54077 -39.59185,147.54054 -39.59169,147.54025 -39.5916,147.54015 -39.59187,147.54042 -39.59238,147.54056 -39.59291,147.5413 -39.59318))"));
INSERT INTO Feature VALUES (NULL, (SELECT featureTypeID FROM FeatureType WHERE ShortDescription="Contours_Polygon"), GeomFromText("POLYGON((146.33763 -41.75,146.338 -41.75,146.33864 -41.75,146.33763 -41.75))"));
INSERT INTO Feature VALUES (NULL, (SELECT featureTypeID FROM FeatureType WHERE ShortDescription="Contours_Polygon"), GeomFromText("POLYGON((148.07627 -41.75,148.078 -41.75,148.07974 -41.75,148.07627 -41.75))"));
SELECT count(*) FROM Feature WHERE MBRContains(geomfromtext('POLYGON((146.499999 -44,146.499999 -41.75,148.583334 -41.75,148.583334 -44,146.499999 -44))'), featureCoordinates);
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
ALTER TABLE Feature DROP INDEX BBox;
SELECT count(*) FROM Feature WHERE MBRContains(geomfromtext('POLYGON((146.499999 -44,146.499999 -41.75,148.583334 -41.75,148.583334 -44,146.499999 -44))'), featureCoordinates);
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
You appear to be right on the <x,y> MBR float length. It works for a limited number of dec places on the x coordinates but doesn't seem to cause a problem on the y coordinates.
Not sure if this bug has been forgotten. I might send an email to the bugs list to ask them to change the status.
Thanks,
Dave.
[10 Nov 2004 23:28]
Jim Winstead
Reopened so the bugs team will re-verify with the added info.
[21 Nov 2004 23:43]
[ name withheld ]
Hi, Is there anything more we can do to help get this bug fixed? It would be hard to create a simpler test case; I think. Can we verify that it doesn't work on any other machines/versions I wonder. Still thinking. Regards, Dave.
[22 Nov 2004 12:03]
Mark Overmeer
I reported (above) the same problem running Linux (SuSE 9.0 upgraded with 2.6.8 kernel), so it seems OS independent. mark@overmeer.net
[11 Dec 2004 1:00]
[ name withheld ]
Hi, I think I have worked out what is going on. ALTER TABLE Feature ADD INDEX BBox(featureCoordinates); ALTER TABLE Feature ADD SPATIAL INDEX BBox(featureCoordinates); These do different things. The former causes the bug described. This is probably due to a limit in the size of the keys (or something). The second works as expected for the test. I still need to confirm that it solves the problem for my larger set. Maybe it isn't a useful to have ADD INDEX on a spatial column. It should be ungrammatic replaced with the ADD SPATIAL INDEX syntax. Would have been nice to hear from the mysql staff; but anyway. I hope this helps someone. Cheers, Dave www.cs.mu.oz.au/~djpenton/
[12 Dec 2004 23:11]
[ name withheld ]
Hi Again,
Curiouser and curiouser. Although this solves the problem for the example provided it doesn't generalise. I have included another test. Now it works when run in a fresh database on a SOLARIS 9 version. But it doesn't work on the AIX version that has 4 GB of geodata in a separate table. I don't think it is a platform problem (though I can't rule that out). Perhaps there is some residual information somewhere.
DROP TABLE MapSet;
CREATE TABLE MapSet (
mapID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
mapTypeID INTEGER UNSIGNED NOT NULL,
coordBBox POLYGON NOT NULL,
coordXMin DOUBLE NULL,
coordYMin DOUBLE NULL,
coordXMax DOUBLE NULL,
coordYMax DOUBLE NULL,
mapName VARCHAR(255) NULL,
PRIMARY KEY(mapID, mapTypeID)
);
\. ~/aiatsis/workspace/djpenton/src/database/MapSetGA.sql
SELECT * FROM MapSet WHERE MBREqual(coordBBox, GEOMFROMTEXT('LINESTRING(143.75 -41.75, 146.5 -39.53333)'));
ALTER TABLE MapSet ADD SPATIAL INDEX GAIndex2(coordBBox);
SELECT * FROM MapSet WHERE MBREqual(coordBBox, GEOMFROMTEXT('LINESTRING(143.75 -41.75, 146.5 -39.53333)'));
Dave.
http://www.cs.mu.oz.au/~djpenton/
[12 Dec 2004 23:14]
[ name withheld ]
Sorry I thought I could add this as a file.
MapSetGA.sql:
INSERT INTO MapSet VALUES(NULL, 1, GeomFromText('POLYGON((143.750000 -41.750000, 143.750000 -39.533330,146.500000 -39.533330, 146.500000 -41.750000, 143.750000 -41.750000))'), 143.750000, -41.750000, 146.500000, -39.533330,'k5520');
INSERT INTO MapSet VALUES(NULL, 1, GeomFromText('POLYGON((146.499999 -41.750000, 146.499999 -39.533333,148.583334 -39.533333, 148.583334 -41.750000, 146.499999 -41.750000))'), 146.499999, -41.750000, 148.583334, -39.533333,'k5521');
INSERT INTO MapSet VALUES(NULL, 1, GeomFromText('POLYGON((143.749999 -44.000000, 143.749999 -41.750000,146.500001 -41.750000, 146.500001 -44.000000, 143.749999 -44.000000))'), 143.749999, -44.000000, 146.500001, -41.750000,'k5522');
INSERT INTO MapSet VALUES(NULL, 1, GeomFromText('POLYGON((146.499999 -44.000000, 146.499999 -41.750000,148.583334 -41.750000, 148.583334 -44.000000, 146.499999 -44.000000))'), 146.499999, -44.000000, 148.583334, -41.750000,'k5523');
Cheers,
Dave.
[21 Dec 2004 5:20]
[ name withheld ]
Hi,
I forgot to show the output:
+-------+-----------+---------------------------------------------------------------------------------------------------+-----------+-----------+-----------+-----------+---------+
| mapID | mapTypeID | coordBBox | coordXMin | coordYMin | coordXMax | coordYMax | mapName |
+-------+-----------+---------------------------------------------------------------------------------------------------+-----------+-----------+-----------+-----------+---------+
| 1 | 1 | | 143.75 | -41.75 | 146.5 | -39.53333 | k5520 |
+-------+-----------+---------------------------------------------------------------------------------------------------+-----------+-----------+-----------+-----------+---------+
1 row in set (0.00 sec)
mysql>
mysql> ALTER TABLE MapSet ADD SPATIAL INDEX GAIndex2(coordBBox);
Query OK, 4 rows affected (0.11 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT * FROM MapSet WHERE MBREqual(coordBBox, GEOMFROMTEXT('LINESTRING(143.75
'> -41.75, 146.5 -39.53333)'));
Empty set (0.01 sec)
Still not entirely sure what's going on.
Cheers,
Dave
[22 Dec 2004 0:14]
[ name withheld ]
Hi, Just writing to confirm that the problem still exists in mysql 4.1.8 on AIX. Cheers, Dave.
[4 Jan 2005 13:11]
MySQL Verification Team
Can't repeat it neither on Linux nor on Windows.
[14 Feb 2005 22:26]
[ name withheld ]
Hi, I have dropped the database and created a new database (which takes about a day...). I can't repeat the bug when using the SPATIAL keyword. So thanks guys for the forum. Cheers, Dave.
[13 Apr 2005 22:38]
Jorge del Conde
I was unable to reproduce this bug using 4.1.11 from bk under Win2k, WinXP & Linux. Dave, can you please confirm if you can still reproduce this bug or not ? Thanks!
Description: not sure that is a bug, but it's very strange i've got a pb with the mbrcontains command and the real numbers (not with the integer numbers). I send this command : select astext(geometrie) from gestionmap where MBRContains(GeomFromText('Polygon((-180 -90.1,-180 90.1,180.1 90.1,180.1 -90.1,-180 -90.1))'),geometrie); it works and i receive this recordset : POLYGON((29.751307 31.332317,30.10916 31.332317,30.10916 31.077066,29.751307 31.077066,29.751307 31.332317)) POLYGON((29.751255 31.32349,30.092671 31.32349,30.092671 31.093151,29.751255 31.093151,29.751255 31.32349)) POLYGON((29.751369 31.323861,30.067349 31.323861,30.067349 31.090876,29.751369 31.090876,29.751369 31.323861)) POLYGON((29.751305 31.350013,30.109187 31.350013,30.109187 31.077006,29.751305 31.077006,29.751305 31.350013)) but if i send this command : select astext(geometrie) from gestionmap where MBRContains(GeomFromText('Polygon((-180.1 -90.1,-180.1 90.1,180.1 90.1,180.1 -90.1,-180.1 -90.1))'),geometrie); i receive a empty recordset, i just changed the number -180 into -180.1 (to enlarge the selection). if i use only integer numbers i don't have any problems (but i need real numbers of course ...) How to repeat: create a database with a geometry field in a table and store some geometries and execute the select like mine