MySQL Bugs: #63906: GeometryType returns a blob instead of a string

Bug #63906	GeometryType returns a blob instead of a string
Submitted:	3 Jan 2012 3:08	Modified:	1 Mar 2012 16:49
Reporter:	Olivier Lefevre	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.1.x	OS:	Windows (XP SP3)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
Subject says it all. The "Query Result" and "Output" tabs show a blue "BLOB" and  an "..." ellipsis (resp.) for the result column. Works fine from, e.g., the mysql command line.

How to repeat:
SELECT GeometryType(GeomFromText('POINT(1 1)')) as type;

Please, check if the same problem happens with a newer version, 5.2.37.

Workbench snapshot

Attachment: workbench_bug1.png (image/png, text), 66.06 KiB.

Yes the problem still exists in 5.2.37

I've got a chance to check on XP today... Please, set "Treat BINARY/VARBINARY as nonbinary character string" in Edit > Preferences > SQL Editor tab, restart Workbench and try again. 

I see "POINT" after that, same as in mysql command line client.

Yes I see POINT now but it still seems wrong that it should be a binary string: the docs for GetGeometryType give no hint of that.

What exact server version do you use (I checked with 5.1.58 on Windows) and what is the output of:

show variables like 'char%';

in your environment?

The server version is 5.1.59 (I can't upgrade to a more recent version because of the customer). Your query returns

character_set_client      utf8
character_set_connection  utf8
character_set_database    utf8
character_set_filesystem  binary
character_set_results     utf8
character_set_server      utf8
character_set_system      utf8
character_sets_dir        C:\Programme\MySQL\MySQL Server 5.1\share\charsets\

Updated Windows version.

You are right about BINARY in 5.1.x. This is what we have in 5.5:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3312 --column-
type-info test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 5.5.19 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT GeometryType(GeomFromText('POINT(1 1)')) as type;
Field   1:  `type`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     60
Max_length: 5
Decimals:   31
Flags:

+-------+
| type  |
+-------+
| POINT |
+-------+
1 row in set (0.09 sec)

mysql> exit
Bye

While in 5.1 the string is really BINARY:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3310 --column-
type-info test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.58-community-log MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT GeometryType(GeomFromText('POINT(1 1)')) as type;
Field   1:  `type`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     20
Max_length: 5
Decimals:   31
Flags:      BINARY

+-------+
| type  |
+-------+
| POINT |
+-------+
1 row in set (0.06 sec)

This is NOT documented at http://dev.mysql.com/doc/refman/5.1/en/geometry-property-functions.html#general-geometry-p..., so we have a valid documentation request here. 

Workbench can do nothing better in 5.1, given the metadata returned from server...

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.