MySQL Bugs: #62861: Wrong data type for user-variables in MySQL 5.1

Bug #62861	Wrong data type for user-variables in MySQL 5.1
Submitted:	21 Oct 2011 11:50	Modified:	21 Oct 2011 13:16
Reporter:	Marco Janssen	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.1.60, 5.5.17	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	5.1, data types, regression, user variables

Description:
According to the documentation of MySQL 5.1, the data type of an user variable should be string, but the real result data type is blob.

Quote from the documentation:
http://dev.mysql.com/doc/refman/5.1/en/user-variables.html
If the value of a user variable is selected in a result set, it is returned to the client as a string.

How to repeat:
Run the following PHP script:
<?php
  $link = mysql_connect( '127.0.0.1', 'user', 'pass' );
  mysql_query('SET @a = "test";', $link);
  $r = mysql_query('SELECT @a;', $link);
  echo mysql_field_type($r,0);
?>

When I execute this on MySQL 5.0 the output is "string".
When I execute this on MySQL 5.1 the output is "blob".

Suggested fix:
Return the data type as described in the documentation (string)

I have seen this too.

I am not using PHP but a C++ GUI-client compiled with the C-API (SQLyog)  This is annoying as SQLyog (and other GUI tools) will open a special interface to view long data (a 'BLOB viewer') instead of displaying them directly in a data GRID.

This is also a regression.  5.0.90 is OK, but 5.1.59 and 5.5.16 are not.

Verified just as described on Mac OS X. This is what we have in 5.0.x:

macbook-pro:5.0 openxs$ bin/mysql -T -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.0.95-debug Source distribution

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> set @a='test';
Query OK, 0 rows affected (0.00 sec)

mysql> select @a;
Field   1:  `@a`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     8192
Max_length: 4
Decimals:   31
Flags:      

+------+
| @a   |
+------+
| test | 
+------+
1 row in set (0.00 sec)

In both 5.1 and 5.5 it is BLOB instead:

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.60 Source distribution

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> set @a='test';
Query OK, 0 rows affected (0.01 sec)

mysql> select @a;
Field   1:  `@a`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       MEDIUM_BLOB
Collation:  latin1_swedish_ci (8)
Length:     16777215
Max_length: 4
Decimals:   31
Flags:      

+------+
| @a   |
+------+
| test |
+------+
1 row in set (0.00 sec)

but I do not see the problem when using numbers:

1)
SET @a = 1;
SELECT @a;

2)
SET @a = 1.1;
SELECT @a;

.. and this 

3)
SET @a = 1.1;
SELECT @a + 7;
returns '8.100000000000000000000000000000'
(probably the addition will convert "1.1" to a double, the result of the addition is also a DOUBLE and finally the DOUBLE is converted to a string - that should be OK as well as far as I can understand)