MySQL Bugs: #76384: Spatial index not used when checking return values explicitly

Bug #76384	Spatial index not used when checking return values explicitly
Submitted:	19 Mar 2015 8:41	Modified:	20 Mar 2015 13:39
Reporter:	Johannes Staffans	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: GIS	Severity:	S2 (Serious)
Version:	5.6.19, 5.6.25, 5.7.8	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	INDEX, spatial, spatial index

Description:
It seems that the spatial index is not used when checking return values from spatial functions explicitly. Compare:

mysql> explain select count(*) from Region where contains(`geometry`, point(13.0, 52.0)) = 1 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: Region
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 7469
Extra: Using where

mysql> explain select count(*) from Region where contains(`geometry`, point(13.0, 52.0)) \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: Region
type: range
possible_keys: geometry
key: geometry
key_len: 34
ref: NULL
rows: 1
Extra: Using where

How to repeat:
Run a query against a spatial index and check its return value explicitly, e.g. "select * from Region where contains(`geometry`, point(13.0, 52.0)) = 1"

Suggested fix:
Use spatial index also when checking return value explicitly, e.g. contains(..) = 1

Hello Johannes Staffans,

Thank you for the report.
Observed similar behavior with 5.6.25 and 5.7.8 builds.

Thanks,
Umesh

// 5.7.8

root@localhost [(none)] > create database test;
Query OK, 1 row affected (0.00 sec)

root@localhost [(none)] > use test;
Database changed
root@localhost [test] > drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@localhost [test] > create table t1(a geometry not null, b geometry not null,
    -> spatial index(a),spatial index(b),key(a(25)),key(b(25)),
    -> unique key(a(25),b(25)),key(b(25),a(25)))engine=myisam;
Query OK, 0 rows affected (0.01 sec)

root@localhost [test] > drop procedure if exists p1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@localhost [test] >
root@localhost [test] > delimiter $
root@localhost [test] >
root@localhost [test] > create procedure p1()
    -> begin
    ->  declare iii bigint unsigned default 0;
    ->  declare continue handler for sqlexception begin end;
    ->  repeat
    ->   insert into t1 set a=point(floor(rand()*iii),floor(rand()*iii)), b=point(floor(rand()*iii),floor(rand()*iii));
    ->   set iii:=iii+1;
    ->   until iii>=5000 end repeat;
    -> end $
Query OK, 0 rows affected (0.00 sec)

root@localhost [test] >
root@localhost [test] > delimiter ;
root@localhost [test] >
root@localhost [test] > call p1();
Query OK, 1 row affected (0.35 sec)

root@localhost [test] > explain select count(*) from t1 where MBRcontains(`a`, point(505, 2648))=1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4999
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

root@localhost [test] > explain select count(*) from t1 where MBRcontains(`a`, point(505, 2648))\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: range
possible_keys: a_3,a,a_2
          key: a
      key_len: 34
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

root@localhost [test] > explain extended select count(*) from t1 where MBRcontains(`a`, point(505, 2648))=1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4999
     filtered: 100.00
        Extra: Using where
1 row in set, 2 warnings (0.00 sec)

root@localhost [test] > show warnings;
+---------+------+---------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                         |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future release.                                                               |
| Note    | 1003 | /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (mbrcontains(`test`.`t1`.`a`,<cache>(point(505,2648))) = 1) |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

root@localhost [test] > explain extended select count(*) from t1 where MBRcontains(`a`, point(505, 2648))\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: range
possible_keys: a_3,a,a_2
          key: a
      key_len: 34
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 2 warnings (0.00 sec)

root@localhost [test] > show warnings;
+---------+------+---------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                   |
+---------+------+---------------------------------------------------------------------------------------------------------------------------+
| Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future release.                                                         |
| Note    | 1003 | /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where mbrcontains(`test`.`t1`.`a`,<cache>(point(505,2648))) |
+---------+------+---------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)