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

MBRWithin doesn't work with geo of different type for InnoDB

    XMLWordPrintable

    Details

    • Sprint:
      10.2.11

      Description

      For the InnoDB storage engine specific, MBRWithin doesn't work when using a different geo type as g1 parameter.
      No issues when using a different type as the g2 parameter.
      Using the same geo type works for both g1 and g2.

      I presume this issue was partly fixed in the past by: MDEV-4521

      How to reproduce:

      — Test code —

      CREATE TABLE t1 (
        l LINESTRING NOT NULL,
        SPATIAL KEY(l)
      ) ENGINE = myisam;
       
      INSERT INTO t1 VALUES(GeomFromText('LINESTRING(0 0, 1 1)'));
      INSERT INTO t1 VALUES(GeomFromText('LINESTRING(1 1, 2 2)'));
      INSERT INTO t1 VALUES(GeomFromText('LINESTRING(2 2, 3 3)'));
       
      SELECT COUNT(*) FROM t1 IGNORE INDEX(l) WHERE MBRContains(l, GEOMFROMTEXT('POINT(0 0)'));
      SELECT COUNT(*) FROM t1 IGNORE INDEX(l) WHERE MBRWithin(GEOMFROMTEXT('POINT(0 0)'), l);
      SELECT COUNT(*) FROM t1 FORCE INDEX(l) WHERE MBRContains(l, GEOMFROMTEXT('POINT(0 0)'));
      SELECT COUNT(*) FROM t1 FORCE INDEX(l) WHERE MBRWithin(GEOMFROMTEXT('POINT(0 0)'), l);
      SELECT COUNT(*) FROM t1 FORCE INDEX(l) WHERE MBRWithin(GEOMFROMTEXT('LINESTRING( 0 0, 1 1 )'), l);
      

      — Result with MyISAM —

      COUNT(*)
      1
      COUNT(*)
      1
      COUNT(*)
      1
      COUNT(*)
      1
      COUNT(*)
      1
      

      — Result with InnoDB —

      ALTER TABLE t1 ENGINE = InnoDB;
       
      SELECT COUNT(*) FROM t1 IGNORE INDEX(l) WHERE MBRContains(l, GEOMFROMTEXT('POINT(0 0)'));
      SELECT COUNT(*) FROM t1 IGNORE INDEX(l) WHERE MBRWithin(GEOMFROMTEXT('POINT(0 0)'), l);
      SELECT COUNT(*) FROM t1 FORCE INDEX(l) WHERE MBRContains(l, GEOMFROMTEXT('POINT(0 0)'));
      SELECT COUNT(*) FROM t1 FORCE INDEX(l) WHERE MBRWithin(GEOMFROMTEXT('POINT(0 0)'), l);
      SELECT COUNT(*) FROM t1 FORCE INDEX(l) WHERE MBRWithin(GEOMFROMTEXT('LINESTRING( 0 0, 1 1 )'), l);
      

      — Result —

      COUNT(*)
      1
      COUNT(*)
      1
      COUNT(*)
      1
      COUNT(*)
      0
      COUNT(*)
      1
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              holyfoot Alexey Botchkov
              Reporter:
              gerben Gerben
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: