Bug #13608 Select astext() returns blob datatype instead of string
Submitted: 29 Sep 2005 13:53 Modified: 17 Oct 2005 7:38
Reporter: Matthew Ashworth Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Query Browser Severity:S3 (Non-critical)
Version:1.1.14 OS:Windows (xp sp2)
Assigned to: Assigned Account CPU Architecture:Any

[29 Sep 2005 13:53] Matthew Ashworth
Description:
using :
 jdk1.5.0_04
 Jconnector (3.1.10)
 Query browser (1.1.14)
 [command line] mySQL (Ver 14.7 Distrib 4.1.14, for Win32 (ia32))

If you select a geometry field using the ASTEXT() function and also have an ORDER BY clause on a joined table then the results for the ASTEXT() field are returned as BLOB datatype.

How to repeat:
2 tables 'link' and 'road'...

mysql> describe link;
+-----------------+--------------+------+-----+------------+-------+
| Field           | Type         | Null | Key | Default    | Extra |
+-----------------+--------------+------+-----+------------+-------+
| Toid            | bigint(20)   |      | PRI | 0          |       |
| primaryRoad     | bigint(20)   |      |     | 0          |       |
| descriptiveTerm | varchar(100) |      |     |            |       |
| natureOfRoad    | varchar(100) |      |     |            |       |
| startNode       | bigint(20)   |      |     | 0          |       |
| endNode         | bigint(20)   |      |     | 0          |       |
| length          | float        |      |     | 0          |       |
| version         | tinyint(4)   |      |     | 0          |       |
| versionDate     | date         |      |     | 0000-00-00 |       |
| changeDate      | date         |      |     | 0000-00-00 |       |
| reasonForChange | varchar(100) |      |     |            |       |
| link            | linestring   |      | MUL |            |       |
| SearchMBR       | polygon      |      | MUL |            |       |
| Dula_ID         | int(11)      | YES  |     | NULL       |       |
| District_ID     | int(11)      | YES  |     | NULL       |       |
+-----------------+--------------+------+-----+------------+-------+
15 rows in set (0.00 sec)

mysql> describe road;
+-----------------+---------------------+------+-----+------------+-------+
| Field           | Type                | Null | Key | Default    | Extra |
+-----------------+---------------------+------+-----+------------+-------+
| Toid            | bigint(20) unsigned |      | PRI | 0          |       |
| roadName        | varchar(100)        |      |     |            |       |
| length          | float               |      |     | 0          |       |
| version         | tinyint(4)          |      |     | 0          |       |
| versionDate     | date                |      |     | 0000-00-00 |       |
| changeDate      | date                |      |     | 0000-00-00 |       |
| reasonForChange | varchar(100)        |      |     |            |       |
| road            | polygon             |      | MUL |            |       |
+-----------------+---------------------+------+-----+------------+-------+
8 rows in set (0.00 sec)

The following query exibits the bug described above

SELECT road.roadname, astext(link)
FROM link LEFT JOIN road ON link.primaryroad = road.toid
ORDER BY road.roadname

replacing 'ORDER BY r' with any field from the 'road' table has the same effect.

replacing 'ORDER BY r' with a field from 'link' works fine - astext() returns the geometry in WKT format.
 

Suggested fix:
Dont 'ORDER BY' a joined field if you need the geometry as text ;-)
[29 Sep 2005 14:42] Jorge del Conde
Thanks for your bug report.  I was able to reproduce this problem using 1.1.15
[17 Oct 2005 7:38] Vladimir Kolesnikov
Hi Matthew,

Thanks for the bug report. Actually this is a server side bug (http://bugs.mysql.com/?id=13798), which will _not_ be until MySQL Server 5.1.
[8 Sep 2006 7:45] Bastian Voigt
please please please fix it asap ....
it is a real showstopper for me :-((((
[13 Mar 2014 13:34] Omer Barnir
This bug is not scheduled to be fixed at this time.