| Bug #65348 | Within() yields no results when spatial index is used | ||
|---|---|---|---|
| Submitted: | 17 May 2012 9:23 | Modified: | 20 Jul 2012 3:09 |
| Reporter: | Arno Hollosi | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: GIS | Severity: | S2 (Serious) |
| Version: | 5.6.5-m8-log, 5.6.6-m9 | OS: | Linux (debian5.0/i686) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | INDEX, no result, regression, within | ||
[17 May 2012 10:36]
Arno Hollosi
Continuing, even more bizarre:
Add a second polygon:
INSERT INTO b VALUES (3, MultiPolygonFromText('MULTIPOLYGON(((13.326 52.513, 13.328 52.513, 13.328 52.511,13.326 52.511,13.326 52.513)))'));
SELECT p2.id, b.id from b, p2 where ST_Within(location,boundary);
+----------+-----+
| id | id |
+----------+-----+
| 26708183 | 171 |
| 26708184 | 171 |
| 26708183 | 3 |
+----------+-----+
Note that id=171 matches now!
DELETE from b where id=3;
SELECT p2.id, b.id from b, p2 where ST_Within(location,boundary);
Empty set (0.00 sec)
Note that id=171 no longer matches!?
BUT: using primary key of p2:
SELECT p2.id, b.id from b, p2 where ST_Within(location,boundary) and p2.id=26708183;
+----------+-----+
| id | id |
+----------+-----+
| 26708183 | 171 |
+----------+-----+
But not the following:
SELECT p2.id, b.id from b, p2 where ST_Within(location,boundary) and p2.id>26708182;
Empty set (0.00 sec)
[17 May 2012 11:10]
Valeriy Kravchuk
Thank you for the bug report. Verified with current mysql-trunk also: ... mysql> select version(); +----------------+ | version() | +----------------+ | 5.6.6-m9-debug | +----------------+ 1 row in set (0.00 sec) mysql> SELECT p2.id from b, p2 where ST_Contains(boundary,location); +----------+ | id | +----------+ | 26708183 | | 26708184 | +----------+ 2 rows in set (0.00 sec) mysql> SELECT p2.id from b, p2 ignore index (idx_loc) where ST_Within(location,boundary); +----------+ | id | +----------+ | 26708183 | | 26708184 | +----------+ 2 rows in set (0.00 sec) mysql> SELECT p2.id from b, p2 where ST_Within(location,boundary); Empty set (0.00 sec) mysql> SELECT p2.id from b, p2 where Within(location,boundary); Empty set (0.00 sec) mysql> SELECT p2.id from b, p2 where MBRWithin(location,boundary); Empty set (0.00 sec) Moreover, last 2 functions produce correct results in 5.5.23 (ST_Within() etc do not exist in 5.5), so this is a regression of a kind in 5.6.x.
[20 Jul 2012 3:09]
Paul DuBois
Noted in 5.6.7, 5.7.0 changelogs. The ST_Contains() and Within() functions yielded an incorrect result when used on a column with a SPATIAL index.
Description: When using a spatial index, Within(), ST_Within() and MBRWithin() yield no result. How to repeat: DROP TABLE IF EXISTS `b`; CREATE TABLE `b` ( `id` int(11) NOT NULL, `boundary` multipolygon NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM; DROP TABLE IF EXISTS `p2`; CREATE TABLE `p2` ( `id` int(11) NOT NULL, `location` point NOT NULL, PRIMARY KEY (`id`), SPATIAL KEY `idx_loc` (`location`) ) ENGINE=MyISAM; INSERT INTO p2 VALUES (26708183, POINT(13.3270117,52.5124749)); INSERT INTO p2 VALUES (26708184, POINT(13.3226096,52.5079019)); INSERT into b VALUES (171, MultiPolygonFromText(' MULTIPOLYGON(((13.070748 52.408936,13.068374 52.420998,13.070532 52.428623,13.075481 52.43481,13.0907345 52.443157,13.089313 52.45343,13.09216 52.47368,13.104742 52.492462,13.1154785 52.497215,13.109683 52.49858,13.103365 52.502777,13.099066 52.509026,13.097403 52.51643,13.100491 52.53659,13.1148205 52.568245,13.109085 52.576797,13.108081 52.584286,13.109614 52.593445,13.116302 52.602966,13.123285 52.606483,13.138388 52.609097,13.145608 52.614475,13.155889 52.617718,13.1658325 52.618744,13.182897 52.614346,13.18569 52.6193,13.194577 52.625916,13.201423 52.63386,13.205094 52.64091,13.210747 52.645664,13.217738 52.64804,13.243684 52.64815,13.249684 52.656364,13.256883 52.660015,13.262632 52.660614,13.263677 52.66677,13.267617 52.673836,13.277855 52.680153,13.309485 52.67951,13.316618 52.677235,13.324385 52.67145,13.328682 52.66494,13.330164 52.657284,13.3284025 52.64927,13.329187 52.64569,13.338237 52.643417,13.355217 52.643562,13.3612995 52.64515,13.374932 52.65506,13.383145 52.664135,13.390004 52.667027,13.402259 52.66782,13.411329 52.663277,13.422596 52.660774,13.431268 52.666687,13.437446 52.6776,13.4496355 52.686417,13.46101 52.68898,13.469022 52.693935,13.480502 52.695454,13.488375 52.69343,13.503117 52.683617,13.507208 52.676712,13.508371 52.669342,13.515296 52.666584,13.526819 52.665394,13.536545 52.65941,13.541152 52.652924,13.542868 52.645153,13.540402 52.632023,13.533958 52.618164,13.527566 52.61232,13.555709 52.60593,13.570173 52.5988,13.577169 52.59363,13.590234 52.589085,13.599221 52.580296,13.604298 52.56678,13.644517 52.560963,13.650742 52.55701,13.655557 52.549866,13.664704 52.54832,13.671438 52.54367,13.67824 52.528965,13.676565 52.517384,13.668695 52.50872,13.648799 52.49876,13.665353 52.49402,13.671573 52.493706,13.689925 52.485786,13.695476 52.48784,13.705219 52.487785,13.725564 52.47985,13.735713 52.470592,13.763427 52.465042,13.77305 52.457222,13.77981 52.444263,13.780818 52.439453,13.77984 52.4311,13.774757 52.42278,13.768704 52.418175,13.758038 52.416225,13.756981 52.414753,13.758204 52.406563,13.756725 52.400337,13.747164 52.387085,13.737072 52.380947,13.720454 52.37839,13.72009 52.37288,13.7163 52.36416,13.703908 52.35087,13.695917 52.347458,13.683476 52.347794,13.673603 52.33781,13.667071 52.32696,13.662137 52.322483,13.654895 52.319107,13.647396 52.318096,13.633682 52.321964,13.622624 52.33086,13.616529 52.34354,13.61704 52.356037,13.604869 52.35346,13.597273 52.355385,13.59101 52.360023,13.586451 52.368633,13.583081 52.371067,13.564872 52.368145,13.530968 52.36943,13.523733 52.37273,13.515762 52.38109,13.477099 52.376144,13.466335 52.381226,13.456617 52.39893,13.445443 52.39537,13.447435 52.38669,13.445886 52.37798,13.435117 52.362125,13.428164 52.357517,13.419936 52.356148,13.387805 52.357853,13.38017 52.359623,13.373801 52.364193,13.370883 52.3684,13.361427 52.370613,13.355109 52.37563,13.352544 52.379696,13.337022 52.387917,13.312676 52.379307,13.30217 52.381935,13.292031 52.390907,13.283148 52.386612,13.273764 52.38439,13.256673 52.386253,13.248557 52.385017,13.237344 52.389236,13.232129 52.395374,13.230263 52.400356,13.226176 52.400658,13.200915 52.395576,13.19126 52.392155,13.186018 52.38179,13.1762495 52.37585,13.160772 52.373966,13.149127 52.375072,13.139198 52.368183,13.127825 52.367466,13.114347 52.37403,13.107824 52.384052,13.09793 52.389538,13.091567 52.39024,13.080054 52.39465,13.074522 52.399868,13.070748 52.408936)))')); SELECT p2.id from b, p2 where ST_Contains(boundary,location); +----------+ | id | +----------+ | 26708183 | | 26708184 | +----------+ SELECT p2.id from b, p2 ignore index (idx_loc) where ST_Within(location,boundary); +----------+ | id | +----------+ | 26708183 | | 26708184 | +----------+ BUT: SELECT p2.id from b, p2 where ST_Within(location,boundary); Empty set (0.01 sec) SELECT p2.id from b, p2 where Within(location,boundary); Empty set (0.00 sec) SELECT p2.id from b, p2 where MBRWithin(location,boundary); Empty set (0.00 sec)