MySQL Bugs: #27300: create view with geometry functions lost columns types

Bug #27300	create view with geometry functions lost columns types
Submitted:	20 Mar 2007 18:20	Modified:	4 Apr 2007 4:13
Reporter:	Albert Rovira	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.0.24, 5.0.37, 5.1-bk, 5.2-falcon	OS:	Linux (Linux, win32)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	functions, types, VIEW

Description:

if you create a view using geometry functions you get longblob types on geometry columns. See below example:

How to repeat:

mysql> create table test (SHAPE geometry);
Query OK, 0 rows affected (0.00 sec)

mysql> desc test;

+------------+---------------+------+-----+---------+-------+
| SHAPE      | geometry      | YES  |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> create view xtest as select GeomFromwkb(asbinary(shape)) from test;
Query OK, 0 rows affected (0.00 sec)

mysql> desc xtest;
+------------------------------+----------+------+-----+---------+-------+
| Field                        | Type     | Null | Key | Default | Extra |
+------------------------------+----------+------+-----+---------+-------+
| GeomFromwkb(asbinary(shape)) | longblob | YES  |     | NULL    |       |
+------------------------------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)

Suggested fix:
Force geometry functions return a geometry type.

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.37, and inform about the results.

The same results on 5.0.37 version.

Also add I'm using MYISAM as default storage engine.

mysql> show create table test;
+-------+-----------------------------------------------------------------------
-----------------------+
| Table | Create Table
                       |
+-------+-----------------------------------------------------------------------
-----------------------+
| tesT  | CREATE TABLE `test` (
  `SHAPE` geometry default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------
-----------------------+

Thank you for the report.

Verified as described.

All versions are affected

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/23136

ChangeSet@1.2422, 2007-03-28 14:35:23+03:00, gkodinov@magare.gmz +4 -0
  Bug #27300:
    Geometry fields have a result type string and a 
    special subclass to cater for the differences
    between them and the base class (just like 
    DATE/TIME).
    When creating temporary tables for results of 
    functions that return results of type GEOMETRY
    we must construct fields of the derived class 
    instead of the base class.
    Fixed by creating a GEOMETRY field (Field_geom) 
    instead of a generic BLOB (Field_blob) in temp 
    tables for the results of GIS functions that 
    have GEOMETRY return type (Item_geometry_func).

Pushed into 5.1.18-beta

Pushed into 5.0.40

Noted in 5.0.40, 5.1.18 changelogs.

In a view, a column that was defined using a GEOMETRY function was
treated as having the LONGBLOB data type rather than the GEOMETRY
type.

Hello. 

My web hosts use 5.0.18. Is there any work around for this problem? It seems to work fine if I select the column directly but I want to use centroid to turn a polygon table into a point view. I've tried explicitly using GeomFromText(AsText(Centroid(polygon))) but this still gives me a longblob data type. 

Cheers. Dan

Dan,

No known workaround exists: you must consider upgrading. 
By a long stretch you could have a second table (with the points) as a real table and then triggers on the original table to replicate the data, but that depends on the nature of your application. 
You may also want to try to avoid the creation of a temp table from the view : check the ALGORITHM option of CREATE VIEW (http://dev.mysql.com/doc/refman/5.0/en/create-view.html).

Pushed into 5.0.44

Pushed into 5.1.20-beta