Bug #66666 user variables are identified as BLOB
Submitted: 3 Sep 2012 11:11 Modified: 3 Sep 2012 16:49
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.1.63, 5.5.23, 5.5.28, 5.7.0 OS:Any
Assigned to: CPU Architecture:Any

[3 Sep 2012 11:11] Peter Laursen
Description:
In 5.1 and 5.5 'mysql` the metadata of a @user_variable returned identify the variable as BLOB (BLOB with an encoding = TEXT, I think).  In 5.0 it was identified as a VAR_STRING. 

How to repeat:
C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -p --column-type-info
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.23 MySQL Community Server (GPL)

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> SELECT NOW() INTO @a; SELECT @a AS 'TEST';
Query OK, 1 row affected (0.00 sec)

Field   1:  `TEST`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       MEDIUM_BLOB
Collation:  utf8_general_ci (33)
Length:     50331645
Max_length: 19
Decimals:   31
Flags:

+---------------------+
| TEST                |
+---------------------+
| 2012-09-03 12:43:28 |
+---------------------+
1 row in set (0.00 sec)

mysql>

..

C:\Program Files\MySQL\MySQL Server 5.5\bin>cd \program files\mysql\mysql server
 5.0\bin

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -p --debug-info
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.96-community-nt MySQL Community Edition (GPL)

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> SELECT NOW() INTO @a; SELECT @a AS 'TEST';
Query OK, 1 row affected (0.00 sec)

Field   1:  `TEST`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     8192
Max_length: 19
Decimals:   31
Flags:      BINARY

+---------------------+
| TEST                |
+---------------------+
| 2012-09-03 13:01:23 |
+---------------------+
1 row in set (0.00 sec)

mysql>

Suggested fix:
VAR_STRING is preferable IMHO - at least as long as the string returned is not too long. Scripts, application (GUI tools in particular) may invoke special code and interfaces to handle BLOBs/TEXTs.

But why 5.0 returns "BINARY" flag I do not understand! Anyway this is history now.

(I am setting the client category.  If this is a server issue then please change)
[3 Sep 2012 11:40] Peter Laursen
Changing category to 'Data Types'.  This is obviously not a client issue. The client version does not matter.  The server versions does.
[3 Sep 2012 16:49] Sveta Smirnova
Thank you for the report.

Verified as described. Workaround: use CAST.

I see following problems with this:

1. MEDIUM_BLOB is larger than VAR_STRING and not necessary in this case
2. Type of NOW() is DATETIME. Don't know why it should be converted to something else.