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

SPATIAL index fails to work with CONTAINS

    XMLWordPrintable

Details

    Description

      When there is a SPATIAL INDEX on a GEOMETRY it seems to fulfill a CONTAINS expression. If the spatial index isn't used, the condition works as expected.

      create table gisbug(id int not null primary key, g1 geometry not null, spatial index(g1));
      insert into gisbug values(1, polygonfromtext('POLYGON((0 0, 0 5, 5 5, 5 0, 0 0))'));
      explain select id from gisbug where contains(g1, pointfromtext('POINT(1 1)'));
      +------+-------------+--------+-------+---------------+------+---------+------+------+-------------+
      | id   | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
      +------+-------------+--------+-------+---------------+------+---------+------+------+-------------+
      |    1 | SIMPLE      | gisbug | range | g1            | g1   | 34      | NULL |    1 | Using where |
      +------+-------------+--------+-------+---------------+------+---------+------+------+-------------+
      select id from gisbug where contains(g1, pointfromtext('POINT(1 1)'));
      Empty set (0.01 sec)
      alter table gisbug drop index g1;
      select id from gisbug where contains(g1, pointfromtext('POINT(1 1)'));
      +----+
      | id |
      +----+
      |  1 |
      +----+
      

      As can be seen, running the same query with and without an index brings different results. Running the query with a "FORCE INDEX(PRIMARY)" hint also brings the correct result, but the SPATIAL INDEX fails.

      Attachments

        Issue Links

          Activity

            People

              holyfoot Alexey Botchkov
              karlsson Anders Karlsson
              Votes:
              0 Vote for this issue
              Watchers:
              7 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.