Bug #77000 Geometric operation returns error instead of result
Submitted: 11 May 2015 12:31 Modified: 11 Jun 2015 16:47
Reporter: Norvald Ryeng Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: GIS Severity:S3 (Non-critical)
Version:5.7.8 OS:Any
Assigned to: CPU Architecture:Any

[11 May 2015 12:31] Norvald Ryeng
Description:
Geometry operations that should return results instead return errors because values are stored in fields of the wrong type by the optimizer.

In get_mm_leaf(), opt_range_cc:7301, key_part->image_type is Field::itRAW instead of Field::itMBR because of the unique index.

How to repeat:
CREATE TABLE t1 (
  p POINT NOT NULL,
  UNIQUE KEY (p(25))
);

INSERT INTO t1 VALUES (ST_GEOMFROMTEXT('POINT(1 1)'));

SELECT ST_ASTEXT(p) FROM t1
WHERE MBRCOVEREDBY
(
  p,
  ST_GEOMFROMTEXT('POLYGON((1 1, 1 2, 2 2, 2 1, 1 1))')
);

DROP TABLE t1;

Suggested fix:
Temporarily convert the field to Field::GEOM_GEOMETRY.
[11 May 2015 12:33] Norvald Ryeng
Posted by developer:
 
Type checking of geometry fields was introduced by the fix for bug#19593342.
[11 Jun 2015 16:47] Paul DuBois
Noted in 5.7.8, 5.8.0 changelogs.

Queries on a geometry column returned an error instead of a result if
there existed a UNIQUE index on the column.