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

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

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.1(EOL)
    • 10.1.6
    • Optimizer
    • None
    • 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

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

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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