[MDEV-13348] MBRWithin doesn't work with geo of different type for InnoDB Created: 2017-07-19  Updated: 2017-11-11  Resolved: 2017-11-11

Status: Closed
Project: MariaDB Server
Component/s: GIS, Storage Engine - InnoDB
Affects Version/s: 10.2
Fix Version/s: 10.2.6

Type: Bug Priority: Major
Reporter: Gerben Assignee: Alexey Botchkov
Resolution: Fixed Votes: 0
Labels: geometry, innodb
Environment:

CentOS 64bit, MariaDB 10.2.7

Issue Links:
Relates
relates to MDEV-4521 MBRContains, MBRWithin no longer work... Closed
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



 Comments   
Comment by Elena Stepanova [ 2017-07-20 ]

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

Comment by Alexey Botchkov [ 2017-11-11 ]

There was a bug about the MBRWithin. Fixed already.

Generated at Thu Feb 08 08:04:54 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.