MySQL Bugs: #12267: Can't use POINT for Primary Key

Bug #12267	Can't use POINT for Primary Key
Submitted:	29 Jul 2005 12:17	Modified:	27 Oct 2005 9:02
Reporter:	[ name withheld ] (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.7/BK Source, 5.0.12-nt	OS:	Any (ALL)
Assigned to:	Alexey Botchkov	CPU Architecture:	Any

Description:
Inserting different values in a column of type POINT defined as PK leads to error "duplicate entry" even if the two points inserted are different.

How to repeat:
Create table:

USE test;

DROP TABLE IF EXISTS `test`.`address`;

CREATE TABLE address (
  address CHAR(80) NOT NULL,
  address_loc POINT NOT NULL,
  PRIMARY KEY(address_loc),
  SPATIAL KEY(address_loc)
) ENGINE=MyISAM;

Insert first row:
INSERT INTO address VALUES('Foobar street 12', GeomFromText('POINT(2671 2500)'));
Insert second row:
INSERT INTO address VALUES('Foobar street 56', GeomFromText('POINT(2971 2520)'));
Error:
Duplicate entry '' for key 1

Thank you for the bug report.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.11-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE address (
    ->   address CHAR(80) NOT NULL,
    ->   address_loc POINT NOT NULL,
    ->   PRIMARY KEY(address_loc),
    ->   SPATIAL KEY(address_loc)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO address VALUES('Foobar street 12', GeomFromText('POINT(2671
    '> 2500)'));
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO address VALUES('Foobar street 56', GeomFromText('POINT(2971
    '> 2520)'));
ERROR 1062 (23000): Duplicate entry '' for key 1

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

The problem is with the PRIMARY KEY on the address_loc field.
As GEOMETRY field is actually a BLOB, default index-part size is 12,
which is less that the size of POINT's representation.
You can make this index work properly declaring PRIMARY KEY address_loc(25).

BTW didn't you plan to create the PRIMARY KEY on the 'address' field, not the 'address_loc'?

Could you please explain why this bug is constantly changing status?
It has been marked as "not a bug", as "analyzing", now "open", and it has not been assigned to a developer.

Does the fact that this bug report has been assigned to a developer mean that I should have got an error like "BLOB/TEXT column '...' used in key specification without a key length"

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30324

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/31135

Now with this change:
you can't create an index over GEOMETRY family type field unless you specify the length of the keypart (as for BLOB fields).
Only exception is the POINT type - as the length of the POINT object is internally known.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented feature change for 5.0.16, added note to GIS Manual chapter.