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

[29 Jul 2005 12:17] [ name withheld ]
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
[29 Jul 2005 13:14] MySQL Verification Team
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
[5 Aug 2005 9:53] Alexey Botchkov
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'?
[20 Sep 2005 7:19] [ name withheld ]
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.
[22 Sep 2005 16:34] [ name withheld ]
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"
[26 Sep 2005 9:45] Bugs System
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
[15 Oct 2005 17:07] Bugs System
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
[26 Oct 2005 6:28] Alexey Botchkov
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.
[27 Oct 2005 9:02] Jon Stephens
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.