Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.5
-
Linux x86_64 CentOS 6.4
Description
When there is a SPATIAL INDEX on a GEOMETRY it seems to fulfill a CONTAINS expression. If the spatial index isn't used, the condition works as expected.
create table gisbug(id int not null primary key, g1 geometry not null, spatial index(g1));
|
insert into gisbug values(1, polygonfromtext('POLYGON((0 0, 0 5, 5 5, 5 0, 0 0))'));
|
explain select id from gisbug where contains(g1, pointfromtext('POINT(1 1)'));
|
+------+-------------+--------+-------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------+-------+---------------+------+---------+------+------+-------------+
|
| 1 | SIMPLE | gisbug | range | g1 | g1 | 34 | NULL | 1 | Using where |
|
+------+-------------+--------+-------+---------------+------+---------+------+------+-------------+
|
select id from gisbug where contains(g1, pointfromtext('POINT(1 1)'));
|
Empty set (0.01 sec)
|
alter table gisbug drop index g1;
|
select id from gisbug where contains(g1, pointfromtext('POINT(1 1)'));
|
+----+
|
| id |
|
+----+
|
| 1 |
|
+----+
|
As can be seen, running the same query with and without an index brings different results. Running the query with a "FORCE INDEX(PRIMARY)" hint also brings the correct result, but the SPATIAL INDEX fails.
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
Activity
Could be the same as MDEV-12443, please check both test cases after fixing.
Sorry, this bug is not fixed for me!
I just updated from 10.2.4 to 10.2.6 today.
If there is a SPATIAL INDEX on a GEOMETRY queries now show 0 results.
If I remove the SPATIAL INDEX from the GEOMETRY I get results again (but slower of course).
I tested different databases, recreated GEOMETRY and checked different queries with e.g. polygon instead of LineString -> made no difference. Query shows no results.
This is an example query that does no longer show results:
SELECT * FROM mylocations WHERE MBRContains( LineString(Point(42.00000 + 100 / (111.1 / COS(RADIANS(42.000000))), 12.000000 + 100 / 111.1),Point(42.000000 - 100 / (111.1 / COS(RADIANS(42.000000))), 12.000000 - 100 / 111.1)), latlngpoint ) LIMIT 100;
Whould it be possible to have a look at this again, please?
Is there anything I can do to help you?
Am I doing something wrong?
Still seeing this bug even on 10.2.7, after upgrading from 10.2.4. Dropping and re-creating the SPATIAL index doesn't help.
This query is now broken:
SELECT MAX(liveobs.obs_time) AS max_obs_time, liveobs.station_id FROM liveobs JOIN stations ON (stations.station_id = liveobs.station_id) WHERE liveobs.obs_time >= FROM_UNIXTIME(1502957480) AND Contains(ST_GeomFromText('POLYGON((38.333039 -87.555542, 41.409776 -87.555542, 41.409776 -80.524292, 38.333039 -80.524292, 38.333039 -87.555542))'), stations.coordinates) GROUP BY liveobs.station_id; |
Switching it around to use Within works fine, as reported above:
SELECT MAX(liveobs.obs_time) AS max_obs_time, liveobs.station_id FROM liveobs JOIN stations ON (stations.station_id = liveobs.station_id) WHERE liveobs.obs_time >= FROM_UNIXTIME(1502957480) AND Within(stations.coordinates, ST_GeomFromText('POLYGON((38.333039 -87.555542, 41.409776 -87.555542, 41.409776 -80.524292, 38.333039 -80.524292, 38.333039 -87.555542))')) GROUP BY liveobs.station_id; |
stations.coordinates is a geometry column with a spatial index on just that column.
testcase.tar.gz
Import geoisam.sql and geo.sql then execute the queries in innodb_query.sql and isam_query.sql, dropping the spatial index (ALTER TABLE geo DROP INDEX geo_geo) will make innodb_query.sql return results as expected (and as isam_query.sql).
The bug was fixed then reintroduced by an unlucky merge.
Should be fixed in 10.2.9.
To reproduce, the table should be created with InnoDB (significant in case of MTR). MyISAM does not seem to be affected.