[MDEV-8239] Reverse spatial operations OP(const, field) do not get optimized - Jira

This script

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a GEOMETRY NOT NULL, SPATIAL KEY(a)) ENGINE=MyISAM;

INSERT INTO t1 VALUES (Point(1,2)),(Point(1,3));

EXPLAIN SELECT * FROM t1 WHERE MBRINTERSECTS(a,Point(1,2));

returns

+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+

| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |

+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+

|    1 | SIMPLE      | t1    | range | a             | a    | 34      | NULL |    1 | Using where |

+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+

which means range optimizer is used. So far so good.

Now if I change the order of the arguments:

EXPLAIN SELECT * FROM t1 WHERE MBRINTERSECTS(Point(1,2),a);

it does not use the index any more:

+------+-------------+-------+------+---------------+------+---------+------+------+-------------+

| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+------+-------------+-------+------+---------------+------+---------+------+------+-------------+

|    1 | SIMPLE      | t1    | ALL  | a             | NULL | NULL    | NULL |    2 | Using where |

+------+-------------+-------+------+---------------+------+---------+------+------+-------------+

The same problem is repeatable with the precise function INTERSECTS().

Note, MBRINTERSECTS() and INTERSECTS() are symmetric operation, so the order of the arguments should not matter.

Transition	Time In Source Status	Execution Times

Alexander Barkov made transition - 2015-06-26 21:57

Open

Closed

30d 1h 31m

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server