MySQL Bugs: #44148: A select AsText(geo) from table returns as a result of [BLOB

Bug #44148	A select AsText(geo) from table returns as a result of [BLOB - 19Bytes]
Submitted:	8 Apr 2009 12:38	Modified:	16 May 2009 1:18
Reporter:	Viktor Haffke	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S2 (Serious)
Version:	5.1.33	OS:	Windows (XP SP3)
Assigned to:		CPU Architecture:	Any
Tags:	astext, Spartial

Description:
A select AsText(geo) from table returns as a result of [BLOB - 19Bytes] instead from POINT(x, y). 
The field geo is a datatyp Point.

How to repeat:
select AsText(geo) from table

Thank you for the problem report. Please, send complete test case, with CREATE TABLE, INSERT and SELECT demonstrating the problem.

CREATE TABLE IF NOT EXISTS `table1` (
  `id` int(11) NOT NULL auto_increment,
  `geo` point NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `geo` (`geo`(25))
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

insert into table1 set geo=GeomFromText('POINT(65448 510028)');

SELECT astext( geo ) FROM `table1`

I've got the following results:

valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql --column-type-info -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.34-debug Source distribution

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

mysql> CREATE TABLE IF NOT EXISTS `table1` (
    ->   `id` int(11) NOT NULL auto_increment,
    ->   `geo` point NOT NULL,
    ->   PRIMARY KEY  (`id`),
    ->   KEY `geo` (`geo`(25))
    -> ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into table1 set geo=GeomFromText('POINT(65448 510028)');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT astext( geo ) FROM `table1`;
Field   1:  `astext( geo )`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONG_BLOB
Collation:  binary (63)
Length:     16777216
Max_length: 19
Decimals:   31
Flags:      BINARY 

+---------------------+
| astext( geo )       |
+---------------------+
| POINT(65448 510028) | 
+---------------------+
1 row in set (0.00 sec)

Where is the problem here, and why? Please, explain.

Excuse me, my error information was not quite correct.
The result of "SELECT astext( geo ) FROM `table1`" is reported correctly with the MySql command tool. The result at the MySql Querybrowser (Version 1.2.17) turns out different in the advertisement. Is shown at MySQL5.0 as a result "POINT(65448 510028)" and "BLOB" is shown at Mysql5.1 as a result.

So, you have a problem when working with 5.1.33 in a Query Browser. You do not have the problem with 5.0 in a Query Browser. Please, check if my understanding of the problem is correct (it will be QB bug then).

Yes, this is correct.

Thank you for the feedback. Have you used the QB`s Field Viewer to see the result of the blob?. Thanks in advance.

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".

I have the same Query Browser problem with "AsText()". The command-line tool works correct.

Reinhold

The following workaround displays geometry data as desired:

CAST( AsText( location ), CHAR ) as location

if the column "location" is of the type POINT you get:
POINT(45.23 10.55)

MySQL 5.1.35-community
Query Browser 1.2.17

So long
Reinhold