Description:
resultset field type of 'astext(f)' where f is a table field of type linestring is blob, but should be text

How to repeat:
(all tables are myisam)

1. execute the attached SQL script to get test data set
2. execute the following query:

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

the type of 'astext(link)' will be 'MYSQL_TYPE_BLOB' but should be 'MYSQL_TYPE_VAR_STRING'.

In 4.1.x this works fine.

test data set - DDL and Inserts

Attachment: linestring_bug.sql (text/plain), 2.34 KiB.

Thank you for a bug report. I've tried to repeat the problem you described on latest 5.0.15-BK build on Linux. 

First, in mysql client I performed the following:

mysql> CREATE TABLE `link` (
    ->   `Toid` bigint(20) unsigned NOT NULL default '0',
    ->   `link` linestring NOT NULL default '',
    ->   `primaryRoad` bigint(20) unsigned NOT NULL default '0',
    ->    PRIMARY KEY  (`Toid`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0,86 sec)

mysql> INSERT INTO `link` (`Toid`,`link`,`primaryRoad`) VALUES
    ->  (1,0x0000000001020000000300000000000000000000000000000000000000000000000
000F03F000000000000F03F00000000000000400000000000000040,1),
    ->  (2,0x0000000001020000000300000000000000000000000000000000000000000000000
000F03F000000000000F03F00000000000000400000000000000040,2);
Query OK, 2 rows affected (0,07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE `road` (
    ->   `Toid` bigint(20) unsigned NOT NULL default '0',
    ->   `roadName` varchar(100) NOT NULL default '',
    ->   PRIMARY KEY  (`Toid`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0,10 sec)

mysql> INSERT INTO `road` (`Toid`,`roadName`) VALUES
    ->  (1,'some road');
Query OK, 1 row affected (0,00 sec)

mysql> SELECT road.roadname, astext(link)
    -> FROM link LEFT JOIN road ON link.primaryroad = road.toid
    -> ORDER BY road.roadname;
+-----------+-------------------------+
| roadname  | astext(link)            |
+-----------+-------------------------+
| NULL      | LINESTRING(0 0,1 1,2 2) |
| some road | LINESTRING(0 0,1 1,2 2) |
+-----------+-------------------------+
2 rows in set (0,03 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.15-rc |
+-----------+
1 row in set (0,00 sec)

Then I created a simple program (I'll attach it as a separate file), compiled and run it. It simply prints the files type for each of two columnsin your select statement. It gave me the following results:

[openxs@Fedora 5.0]$ ./13798
Client info: 5.0.15-rc
Server info: 5.0.15-rc
type=253
length=100
decimals=0
type=253
length=8192
decimals=31

So, both columns are of MYSQL_TYPE_VAR_STRING really, just as you expected. So, how you decided you get the other type for the second column? I can't repeat it.

Simple program to show filed types

Attachment: 13798.c (text/x-csrc), 1.15 KiB.

Valeriy, 
I modified the test-case, so now the error is reproducible. Please see the file attached.

Thank you for the additional test case. You 13798mod1.c gave me the following results after compilation on latest 5.0.16-BK:

[openxs@Fedora 5.0]$ ./13798
Client info: 5.0.16
Server info: 5.0.16
type=253
MYSQL_TYPE_BLOB=252
length=100
decimals=0
type=252
MYSQL_TYPE_BLOB=252
length=8192
decimals=0

So, yes, looks like different C API fucntions returns different data types in this case really. 

I've got a couple of warnings while compiling, but they are not relevant to the issue demonstrated.

The main problem is ASTEXT returning binary data type, which cannot be fixed before 5.1
BLOB/VAR_STRING is relatively minor issue, as two types are essencially identical.

besides 4.1 behaves *exactly* the same, returning MYSQL_TYPE_BLOB and not MYSQL_TYPE_VARSTRING.

But for the first field, 5.0 returns empty database/table/org_table metadata. This should be fixed.

Aparently, Vladimir can observe different metadata in 4.1 and 5.0 on his windows machine.
What are results on linux ?

I've got my results (from the last comment) on Linux, Fedora Core 1.

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

Can't fix this but can offer a workaround...

Add a text field to the table you are trying to get the geometry as text from.

Update the table so the new field holds the result of asText(geomField).

Use the new text field in the select statement instead of 'asText(geomField)'.

For the example given by Vladimir try...

ALTER TABLE `test`.`link` ADD COLUMN `geomAsText` VARCHAR(45) NOT NULL;

Update test set geomAsText = asText(link);

SELECT road.roadname, geomAsText
FROM link LEFT JOIN road ON link.primaryroad = road.toid
ORDER BY road.roadname

You can still use the geometry field in the condition of the select statement if you want but you'll get a preformatted asText in the result not the blob datatype.

Matt