Bug #73655 ha_innobase::index_flags() reports bogus flags for spatial indexes
Submitted: 20 Aug 2014 12:07 Modified: 21 Mar 2015 16:49
Reporter: Marko Mäkelä Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.7.5 OS:Any
Assigned to: CPU Architecture:Any

[20 Aug 2014 12:07] Marko Mäkelä
Description:
The method ha_innobase::index_flags() is returning bogus-looking flags for spatial (R-tree) indexes. R-tree index entries are not totally ordered. Therefore, some of the returned flags do not make sense.

How to repeat:
Read the code:

ulong
ha_innobase::index_flags(
/*=====================*/
	uint	key,
	uint,
	bool) const
{
	if (table_share->key_info[key].algorithm == HA_KEY_ALG_FULLTEXT) {
		return(0);
	}

	ulong flags = HA_READ_NEXT | HA_READ_PREV | HA_READ_ORDER
		      | HA_READ_RANGE | HA_KEYREAD_ONLY
		      | HA_DO_INDEX_COND_PUSHDOWN;

	if (table_share->key_info[key].flags & HA_SPATIAL) {
		flags |= HA_KEY_SCAN_NOT_ROR;
	}

	return(flags);
}

Suggested fix:
Return only HA_KEY_SCAN_NOT_ROR, or possibly also HA_DO_INDEX_COND_PUSHDOWN for SPATIAL indexes.

Write a test that tries to use ICP on R-tree indexes.
[18 Mar 2015 2:44] Allen Lai
Posted by developer:
 
If we remove the flag HA_READ_RANGE for spatial index, the query plan will be changed.

Here's the example:

CREATE TABLE tab(c1 int NOT NULL PRIMARY KEY,c2 POLYGON NOT NULL,
spatial index idx3 (c2))
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

INSERT INTO tab(c1,c2)
VALUES(1, ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'));

INSERT INTO tab(c1,c2)
VALUES(2, ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'));

SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');

EXPLAIN SELECT c1,ST_Astext(c2) FROM tab WHERE MBRContains(tab.c2, @g1);

----------------------------------------------------------------------------------------
Before remove it:
root@localhost [test] > EXPLAIN SELECT c1,ST_Astext(c2) FROM tab WHERE MBRContains(tab.c2, @g1);
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tab   | NULL       | range | idx3          | idx3 | 34      | NULL |    2 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

After remove it:
root@localhost [test] > EXPLAIN SELECT c1,ST_Astext(c2) FROM tab WHERE MBRContains(tab.c2, @g1);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tab   | NULL       | ALL  | idx3          | NULL | NULL    | NULL |    2 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
[18 Mar 2015 9:10] Norvald Ryeng
Posted by developer:
 
This is an InnoDB issue, so reassigning it to the InnoDB category.
[20 Mar 2015 2:26] Allen Lai
Posted by developer:
 
As dicussed with Novald, we removed HA_READ_PREV and HA_DO_INDEX_COND_PUSHDOWN for spatial index.
[21 Mar 2015 16:49] Daniel Price
Posted by developer:
 
Fixed as of the upcoming 5.7.8, 5.8.0 releases, and here's the changelog entry:

The "ha_innobase::index_flags" function returned invalid flags for
spatial indexes. 

Thank you for the bug report.