MySQL Bugs: #25390: UNIQUE Btree index on spatial column works in strange ways

Bug #25390	UNIQUE Btree index on spatial column works in strange ways
Submitted:	3 Jan 2007 16:39	Modified:	19 Apr 2008 18:29
Reporter:	Anders Karlsson	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: GIS	Severity:	S3 (Non-critical)
Version:	5.0.34-BK, 5.1.15-BK, 5.1.14, 4.1.23-BK	OS:	Linux (Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
As of MySQL 5.0, you can have SPATION data in tables using stoarge engines that doesn't support R-TREE indexes. This is particularily interesting with InnoDB. Although this is not perfect, it is better than no index at all, and if the requirement si to have a unique spacial identifier only, such as a unique point, then a B-Tree way well be more effective. This does not work as expected in neither 4.1 nor 5.1.

In both of those versions (tested are 4.1.8 and 5.1.14), creating an index on a POINT (the only spatial type tested so far) creates an index 21 bytes wide, for some reason. This will complain about values begin non-unique, even though they are. The BTree index created (this is same, independent of MyISAM and InnoDB), the index created is created on the 21 first bytes, although TABLE STATUS reports a table with just 1 spatial index to be 36 bytes wide.

Although the sematics of using B-Tree indexes with GIS datatypes are not well documented, the current is apparently incorrect for all intents and purposes.

How to repeat:
In MySQL 4.1.8:
mysql> CREATE TABLE t1(c1 POINT NOT NULL PRIMARY KEY);
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO t1 VALUES(GeomFromText('POINT(1 1)'));
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES(GeomFromText('POINT(1 2)'));
ERROR 1062 (23000): Duplicate entry '' for key 1

mysql> DROP TABLE t1;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE t1(c1 POINT NOT NULL, PRIMARY KEY(c1(25)));
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO t1 VALUES(GeomFromText('POINT(1 1)'));
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES(GeomFromText('POINT(1 2)'));
Query OK, 1 row affected (0.00 sec)

In MySQL 5.1.14:

mysql> CREATE TABLE t1(c1 POINT NOT NULL PRIMARY KEY);
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO t1 VALUES(GeomFromText('POINT(1 1)'));
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t1 VALUES(GeomFromText('POINT(1 2)'));
ERROR 1062 (23000): Duplicate entry '' for key 'PRIMARY'

mysql> DROP TABLE t1;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE t1(c1 POINT NOT NULL, PRIMARY KEY(c1(25)));
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO t1 VALUES(GeomFromText('POINT(1 1)'));
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO t1 VALUES(GeomFromText('POINT(1 2)'));
ERROR 1062 (23000): Duplicate entry '' for key 'PRIMARY'

Suggested fix:
- Document how this is supposed to work.
- Make sure that B-Tree indexes can be used to ensure uniqeness among spation values, which IS a usufull usage even of B-Tree indexes with spation data.
- Make sure that B-Tree indexes created on SPATIAL data get appropriate width.

Thank you for a bug report. Verified just as described with latest 5.0.34-BK on Linux:

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.34-debug |
+--------------+
1 row in set (0.01 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE t1(c1 POINT NOT NULL PRIMARY KEY);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t1 VALUES(GeomFromText('POINT(1 1)'));
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t1 VALUES(GeomFromText('POINT(1 2)'));
ERROR 1062 (23000): Duplicate entry '' for key 1
mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1(c1 POINT NOT NULL, PRIMARY KEY(c1(25)));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES(GeomFromText('POINT(1 1)'));
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES(GeomFromText('POINT(1 2)'));
ERROR 1062 (23000): Duplicate entry '' for key 1
mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` point NOT NULL,
  PRIMARY KEY  (`c1`(21))
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Verified on latest 4.1.23-BK and 5.1.15-BK also.

I must say i can't repeat this bug in last 5.0 and 5.1 trees.

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/commits/42460

ChangeSet@1.2706, 2008-02-17 23:28:32+04:00, holyfoot@mysql.com +3 -0
  Bug #25390 UNIQUE Btree index on spatial column works in strange ways.
  
  Length for the GIS_POINT keyseg was 12, while the actual length
  of the field is 25. Handled separately as it's the only geometry
  type the length of which is known.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Alexey,

do you got feedback?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".