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

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

              Dates

              Created:
              Updated: