MySQL Bugs: #6134: "protocol level" field type from select @variable always returns string

Bug #6134	"protocol level" field type from select @variable always returns string
Submitted:	17 Oct 2004 12:49	Modified:	22 Nov 2005 8:02
Reporter:	Levap Aretnyd	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.2-alpha-debug	OS:	Windows (Windows 2003)
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any

Description:
I'am not sure that this is a bug, but when returning values of user variables from the server with SELECT @variable, client in data packet (client library) always receives type no. 254 (string), but with explicit SELECT CAST(@variable AS ...) the data type byte in packet is correcly changed (to the type of the cast).
But MySQL seems to respect data type of value in user variable in other cases, for example when CREATE TABLE ... SELECT @variable is executed. The column type in created table respects the value in user variable (previous SET @variable=45 creates a bigint field and/or previous SET @variable="45" creates a longtext field).
Why string data type ID is always returned when using SELECT @variable regardless on the contents of the variable?

How to repeat:
Need a client that shows "protocol level" field type:
SET @a = 45;
SELECT @a;
- In returned result the field `@a` has "protocol level" data type "string" (designated byte in data packet has value 254)
with explicit SELECT CAST(@a AS UNSIGNED)
is the data type "Int64" (value 8).

But MySql seems respect correct variable type without explicit cast for example in this case:
SET @a = 45;
CREATE TABLE test1 SELECT @a;
SHOW CREATE TABLE test1;
- type of column @a in created table is bigint
and:
SET @a = "45";
CREATE TABLE test2 SELECT @a;
SHOW CREATE TABLE test2;
- type of column @a in created is longtext

Verified on latest BK source on Linux.

Please retest to determine platform-specificness.

Verified on Window 2003 - mysql 5.0.11.
mysql> SHOW CREATE TABLE test1;
+-------+-----------------------------------------------------------------------
-----------------------+
| Table | Create Table
                       |
+-------+-----------------------------------------------------------------------
-----------------------+
| test1 | CREATE TABLE `test1` (
  `@a` bigint(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------
-----------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE test2;
+-------+-----------------------------------------------------------------------
--------+
| Table | Create Table
        |
+-------+-----------------------------------------------------------------------
--------+
| test2 | CREATE TABLE `test2` (
  `@a` longtext
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------
--------+
1 row in set (0.00 sec)

Thanks for the bug report.
It's not a bug, see Item_func_get_user_var::field_type().

It's intentional behaviour. The comment Ramil is referring to explains:

    We must always return variables as strings to guard against selects of type
    select @t1:=1,@t1,@t:="hello",@t from foo where (@t1:= t2.b)