MySQL Forums
Forum List  »  GIS

can't find point when column has index
Posted by: andy w
Date: September 04, 2007 02:54AM

I have a very complex scenario but I managed to drill this down to a simple situation:

DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t3;
CREATE TABLE t1 (p POINT);
CREATE TABLE t2 (p POINT, INDEX(p));
CREATE TABLE t3 (p POINT, INDEX(p)) ENGINE=MyISAM;

INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(1 2)'));
INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)'));
INSERT INTO t3 VALUES (POINTFROMTEXT('POINT(1 2)'));

SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)');

+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)


SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');

+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)

SELECT COUNT(*) FROM t3 WHERE p=POINTFROMTEXT('POINT(1 2)');

+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)


That is, when using InnoDB tables, MySQL cannot find a point if an index is defined in that column (table t2). However, if an index is not defined (table t1) or the engine is MyISAM (table t3) then the point is found.
Is this a bug or is it documented somewhere?

Options: ReplyQuote


Subject
Views
Written By
Posted
can't find point when column has index
5585
September 04, 2007 02:54AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.