Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8239

Reverse spatial operations OP(const, field) do not get optimized

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.1
    • Fix Version/s: 10.1.6
    • Component/s: Optimizer
    • Labels:
      None
    • Sprint:
      10.1.6-1

      Description

      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.

        Attachments

          Activity

            People

            Assignee:
            bar Alexander Barkov
            Reporter:
            bar Alexander Barkov
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: