| 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: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.7.5 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[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.
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.