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 ;-)
