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

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

Details

    • 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

            Thanks for the report and the test case. I'm confirming as there are clearly different results which shouldn't be happening anyway.

            I'll leave it to holyfoot to find out which results (if any) are correct. MySQL 5.7, for example, returns the same results for InnoDB / MyISAM, but they are different from both sets of results on 10.2:

            MySQL 5.7, InnoDB and MyISAM

            SELECT COUNT(*) FROM t1 IGNORE INDEX(l) WHERE MBRContains(l, ST_GEOMFROMTEXT('POINT(0 0)'));
            COUNT(*)
            0
            SELECT COUNT(*) FROM t1 IGNORE INDEX(l) WHERE MBRWithin(ST_GEOMFROMTEXT('POINT(0 0)'), l);
            COUNT(*)
            0
            SELECT COUNT(*) FROM t1 FORCE INDEX(l) WHERE MBRContains(l, ST_GEOMFROMTEXT('POINT(0 0)'));
            COUNT(*)
            0
            SELECT COUNT(*) FROM t1 FORCE INDEX(l) WHERE MBRWithin(ST_GEOMFROMTEXT('POINT(0 0)'), l);
            COUNT(*)
            0
            SELECT COUNT(*) FROM t1 FORCE INDEX(l) WHERE MBRWithin(ST_GEOMFROMTEXT('LINESTRING( 0 0, 1 1 )'), l);
            COUNT(*)
            1
            

            10.2, MyISAM

            SELECT COUNT(*) FROM t1 IGNORE INDEX(l) WHERE MBRContains(l, ST_GEOMFROMTEXT('POINT(0 0)'));
            COUNT(*)
            1
            SELECT COUNT(*) FROM t1 IGNORE INDEX(l) WHERE MBRWithin(ST_GEOMFROMTEXT('POINT(0 0)'), l);
            COUNT(*)
            1
            SELECT COUNT(*) FROM t1 FORCE INDEX(l) WHERE MBRContains(l, ST_GEOMFROMTEXT('POINT(0 0)'));
            COUNT(*)
            1
            SELECT COUNT(*) FROM t1 FORCE INDEX(l) WHERE MBRWithin(ST_GEOMFROMTEXT('POINT(0 0)'), l);
            COUNT(*)
            1
            SELECT COUNT(*) FROM t1 FORCE INDEX(l) WHERE MBRWithin(ST_GEOMFROMTEXT('LINESTRING( 0 0, 1 1 )'), l);
            COUNT(*)
            1
            

            10.2, InnoDB

            SELECT COUNT(*) FROM t1 IGNORE INDEX(l) WHERE MBRContains(l, ST_GEOMFROMTEXT('POINT(0 0)'));
            COUNT(*)
            1
            SELECT COUNT(*) FROM t1 IGNORE INDEX(l) WHERE MBRWithin(ST_GEOMFROMTEXT('POINT(0 0)'), l);
            COUNT(*)
            1
            SELECT COUNT(*) FROM t1 FORCE INDEX(l) WHERE MBRContains(l, ST_GEOMFROMTEXT('POINT(0 0)'));
            COUNT(*)
            1
            SELECT COUNT(*) FROM t1 FORCE INDEX(l) WHERE MBRWithin(ST_GEOMFROMTEXT('POINT(0 0)'), l);
            COUNT(*)
            0
            SELECT COUNT(*) FROM t1 FORCE INDEX(l) WHERE MBRWithin(ST_GEOMFROMTEXT('LINESTRING( 0 0, 1 1 )'), l);
            COUNT(*)
            1
            

            elenst Elena Stepanova added a comment - Thanks for the report and the test case. I'm confirming as there are clearly different results which shouldn't be happening anyway. I'll leave it to holyfoot to find out which results (if any) are correct. MySQL 5.7, for example, returns the same results for InnoDB / MyISAM, but they are different from both sets of results on 10.2: MySQL 5.7, InnoDB and MyISAM SELECT COUNT (*) FROM t1 IGNORE INDEX (l) WHERE MBRContains(l, ST_GEOMFROMTEXT( 'POINT(0 0)' )); COUNT (*) 0 SELECT COUNT (*) FROM t1 IGNORE INDEX (l) WHERE MBRWithin(ST_GEOMFROMTEXT( 'POINT(0 0)' ), l); COUNT (*) 0 SELECT COUNT (*) FROM t1 FORCE INDEX (l) WHERE MBRContains(l, ST_GEOMFROMTEXT( 'POINT(0 0)' )); COUNT (*) 0 SELECT COUNT (*) FROM t1 FORCE INDEX (l) WHERE MBRWithin(ST_GEOMFROMTEXT( 'POINT(0 0)' ), l); COUNT (*) 0 SELECT COUNT (*) FROM t1 FORCE INDEX (l) WHERE MBRWithin(ST_GEOMFROMTEXT( 'LINESTRING( 0 0, 1 1 )' ), l); COUNT (*) 1 10.2, MyISAM SELECT COUNT (*) FROM t1 IGNORE INDEX (l) WHERE MBRContains(l, ST_GEOMFROMTEXT( 'POINT(0 0)' )); COUNT (*) 1 SELECT COUNT (*) FROM t1 IGNORE INDEX (l) WHERE MBRWithin(ST_GEOMFROMTEXT( 'POINT(0 0)' ), l); COUNT (*) 1 SELECT COUNT (*) FROM t1 FORCE INDEX (l) WHERE MBRContains(l, ST_GEOMFROMTEXT( 'POINT(0 0)' )); COUNT (*) 1 SELECT COUNT (*) FROM t1 FORCE INDEX (l) WHERE MBRWithin(ST_GEOMFROMTEXT( 'POINT(0 0)' ), l); COUNT (*) 1 SELECT COUNT (*) FROM t1 FORCE INDEX (l) WHERE MBRWithin(ST_GEOMFROMTEXT( 'LINESTRING( 0 0, 1 1 )' ), l); COUNT (*) 1 10.2, InnoDB SELECT COUNT (*) FROM t1 IGNORE INDEX (l) WHERE MBRContains(l, ST_GEOMFROMTEXT( 'POINT(0 0)' )); COUNT (*) 1 SELECT COUNT (*) FROM t1 IGNORE INDEX (l) WHERE MBRWithin(ST_GEOMFROMTEXT( 'POINT(0 0)' ), l); COUNT (*) 1 SELECT COUNT (*) FROM t1 FORCE INDEX (l) WHERE MBRContains(l, ST_GEOMFROMTEXT( 'POINT(0 0)' )); COUNT (*) 1 SELECT COUNT (*) FROM t1 FORCE INDEX (l) WHERE MBRWithin(ST_GEOMFROMTEXT( 'POINT(0 0)' ), l); COUNT (*) 0 SELECT COUNT (*) FROM t1 FORCE INDEX (l) WHERE MBRWithin(ST_GEOMFROMTEXT( 'LINESTRING( 0 0, 1 1 )' ), l); COUNT (*) 1

            There was a bug about the MBRWithin. Fixed already.

            holyfoot Alexey Botchkov added a comment - There was a bug about the MBRWithin. Fixed already.

            People

              holyfoot Alexey Botchkov
              gerben Gerben
              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.