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

ST_Contains doesn't work as expected on InnoDB engine

    XMLWordPrintable

Details

    Description

      When a table with geometry field and spatial index is converted from MyISAM to InnoDB GIS function doesn't work as expected anymore:

      • No records are being returned even the statement is true (the geometry falls within the given borders of another)
      • When adding additional '=1' check - rows are returned but in that case no index is used (possible).

      A small test case:

      DROP TABLE IF EXISTS `objects`;
       
      CREATE TABLE `objects` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `bounds` geometry NOT NULL,
        PRIMARY KEY (`id`),
        SPATIAL KEY `bounds` (`bounds`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
       
      INSERT INTO `objects` VALUES (1,0x000000000103000000010000001100000051653FC69612494000000000C0EA0840B101B67B2FE2484000000000C0220D40FDA9371F45D248400000000020481040B5270BD172D048400000000060E811409A033715FBCA484000000000E05812407C1B8214E8AB48400000000060D713404D0C25C71E9348400000000000CC1540259FDC1CBDAD48400000000070B21940B0226899F92949400000000060ED1B40D7B36C6FAF7849400000000080FB1A406582C73D5EC6494000000000205E18408FE35055B8E7494000000000F0CE13405631E16F75E449400000000080600F401A8FF47F8EC1494000000000402602407BFFEE6A2A4249400000000040340340EEA681117C25494000000000C0D1034051653FC69612494000000000C0EA0840);
       
      -- No rows are returned
      SELECT id FROM  objects WHERE ST_Contains(bounds, GeomFromText('POINT(50.83216 3.59565)'));
      EXPLAIN SELECT id FROM  objects WHERE ST_Contains(bounds, GeomFromText('POINT(50.83216 3.59565)'));
       
      -- A row is returned but no index is being used
      SELECT id FROM  objects WHERE ST_Contains(bounds, GeomFromText('POINT(50.83216 3.59565)')) = 1;
      EXPLAIN SELECT id FROM  objects WHERE ST_Contains(bounds, GeomFromText('POINT(50.83216 3.59565)')) = 1;
       
       
      -- ST_Within works fine and returns correct result and uses index
      SELECT id FROM  objects WHERE ST_Within(GeomFromText('POINT(50.83216 3.59565)'),bounds);
       
       
      -- MyISAM - everything works as expected
      ALTER TABLE `objects` ENGINE=MyISAM;
       
      SELECT id FROM  objects WHERE ST_Contains(bounds, GeomFromText('POINT(50.83216 3.59565)'));
      EXPLAIN SELECT id FROM  objects WHERE ST_Contains(bounds, GeomFromText('POINT(50.83216 3.59565)'));
       
      SELECT id FROM  objects WHERE ST_Contains(bounds, GeomFromText('POINT(50.83216 3.59565)')) = 1;
      EXPLAIN SELECT id FROM  objects WHERE ST_Contains(bounds, GeomFromText('POINT(50.83216 3.59565)')) = 1;
      

      Also the oppposite funcion ST_Within works as expected.

      Attachments

        Issue Links

          Activity

            People

              holyfoot Alexey Botchkov
              Roze Reinis Rozitis
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.