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
Tags: functions, types, VIEW

[20 Mar 2007 18:20] Albert Rovira
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.
[20 Mar 2007 18:31] Valerii Kravchuk
Thank you for a problem report. Please, try to repeat with a newer version, 5.0.37, and inform about the results.
[21 Mar 2007 10:05] Albert Rovira
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 |
+-------+-----------------------------------------------------------------------
-----------------------+
[21 Mar 2007 10:26] Sveta Smirnova
Thank you for the report.

Verified as described.
[21 Mar 2007 10:28] Sveta Smirnova
All versions are affected
[28 Mar 2007 11:35] 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/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).
[31 Mar 2007 8:39] Bugs System
Pushed into 5.1.18-beta
[31 Mar 2007 8:44] Bugs System
Pushed into 5.0.40
[4 Apr 2007 4:13] Paul Dubois
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.
[7 Jun 2007 16:09] Dan Jones
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
[8 Jun 2007 14:32] Georgi Kodinov
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).
[14 Jun 2007 19:01] Bugs System
Pushed into 5.0.44
[14 Jun 2007 19:01] Bugs System
Pushed into 5.1.20-beta