Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2(EOL)
-
CentOS 64bit, MariaDB 10.2.7
-
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
- relates to
-
MDEV-4521 MBRContains, MBRWithin no longer work with geometries of different type
-
- Closed
-
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
0
0
0
0
1
10.2, MyISAM
1
1
1
1
1
10.2, InnoDB
1
1
1
0
1