[MDEV-12462] SPATIAL index fails to work with CONTAINS Created: 2017-04-06  Updated: 2020-08-25  Resolved: 2017-05-02

Status: Closed
Project: MariaDB Server
Component/s: GIS, Storage Engine - InnoDB
Affects Version/s: 10.2.5
Fix Version/s: 10.2.6

Type: Bug Priority: Major
Reporter: Anders Karlsson Assignee: Alexey Botchkov
Resolution: Fixed Votes: 0
Labels: 10.2-ga
Environment:

Linux x86_64 CentOS 6.4

Attachments: File testcase.tar.gz    
Issue Links:
Relates
relates to MDEV-12443 ST_Contains doesn't work as expected ... Closed
relates to MDEV-13853 Spatial index does not work properly ... Closed
relates to MDEV-12443 ST_Contains doesn't work as expected ... Closed

 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.

 Comments   
Comment by Elena Stepanova [ 2017-04-20 ]

To reproduce, the table should be created with InnoDB (significant in case of MTR). MyISAM does not seem to be affected.

Comment by Elena Stepanova [ 2017-04-20 ]

Could be the same as MDEV-12443, please check both test cases after fixing.

Comment by Alexey Botchkov [ 2017-05-02 ]

http://lists.askmonty.org/pipermail/commits/2017-May/011071.html

Comment by Robert Sonnberger [ 2017-05-23 ]

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?

Comment by Wesley Haines [ 2017-08-17 ]

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.

Comment by Claudio Nanni [ 2017-09-20 ]

Still present on 10.2.7, dropping the index the results come back.

Comment by Claudio Nanni [ 2017-09-20 ]

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

Comment by Alexey Botchkov [ 2017-09-20 ]

The bug was fixed then reintroduced by an unlucky merge.
Should be fixed in 10.2.9.

Generated at Thu Feb 08 07:57:54 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.