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

Wrong result (missing row) upon using SPATIAL index on InnoDB table

    XMLWordPrintable

Details

    Description

      --source include/have_innodb.inc
       
      CREATE TABLE t1 (pk INT PRIMARY KEY, g GEOMETRY NOT NULL, SPATIAL(g)) ENGINE=InnoDB;
       
      INSERT INTO t1 VALUES
        (1,ST_GEOMFROMTEXT('LINESTRING(2540 3224, 2137 3092)')),
        (2,ST_GEOMFROMTEXT('LINESTRING(3527 3088, 3839 3026)')),
        (3,ST_GEOMFROMTEXT('LINESTRING(1761 2561, 1851 2597)')),
        (4,ST_GEOMFROMTEXT('LINESTRING(2430 4077, 2392 3900)'));
       
      SELECT pk FROM t1 FORCE KEY (g) WHERE ST_DISJOINT( ST_GEOMFROMTEXT('LINESTRING( 4009 3088, 3786 2897 )'), g );
      SELECT pk FROM t1 WHERE ST_DISJOINT( ST_GEOMFROMTEXT('LINESTRING( 4009 3088, 3786 2897 )'), g );
       
      DROP TABLE t1;
      

      10.3 2d592f75

      SELECT pk FROM t1 FORCE KEY (g) WHERE ST_DISJOINT( ST_GEOMFROMTEXT('LINESTRING( 4009 3088, 3786 2897 )'), g );
      pk
      4
      1
      3
      SELECT pk FROM t1 WHERE ST_DISJOINT( ST_GEOMFROMTEXT('LINESTRING( 4009 3088, 3786 2897 )'), g );
      pk
      1
      2
      3
      4
      

      The second result (without forcing the index) is apparently the correct one.

      Also reproducible on MySQL 5.7. Not reproducible on MySQL 8.0.
      Not reproducible with MyISAM.

      Attachments

        Activity

          People

            holyfoot Alexey Botchkov
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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