MySQL Bugs: #26190: when using user variable in select, the correct type should be reported

Bug #26190	when using user variable in select, the correct type should be reported
Submitted:	8 Feb 2007 17:17	Modified:	26 May 2007 15:44
Reporter:	Axel Schwenke	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0, 4.1, 5.1	OS:	Linux (Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	bfsm_2007_02_15

Description:
According to the manual: http://dev.mysql.com/doc/refman/5.0/en/user-variables.html
user variables have a corresponding type of either integer, real, string, or NULL. However, if the value of the variable is selected in a result set, it is returned to the client as a string.

This is bad, because some client libraries (i.e. Pythons MySQLdb) propagate the type of the result columns to the application.

How to repeat:
Run mysql with the --debug-info=1 command line option. Then

mysql> set @var=42;
Query OK, 0 rows affected (0,00 sec)

mysql> select @var;
Field   1:  `@var`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     20
Max_length: 2
Decimals:   0
Flags:      BINARY 

+------+
| @var |
+------+
| 42   | 
+------+
1 row in set (0,00 sec)

Suggested fix:
Return the associated type of a user variable in result metadata.

'set @var=0; select @var' returns a string
'set @var=0; create table foo as select @var' creates a table with a bigint column

@var should evaluate to the same type for select and create table as select

This behaviour has changed in 4.1. 4.0 behaves as desired:

$mysql --debug-info=1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.30

mysql> set @var=42;

mysql> select @var;
Name:       '@var'
Table:      ''
Type:       8
<cut>

mysql> set @var=42.5;

mysql> select @var;
Name:       '@var'
Table:      ''
Type:       5
<cut>

mysql> set @var='foo';

mysql> select @var;
Name:       '@var'
Table:      ''
Type:       253
<cut>

Another case, 'select @v=1' returns LONGLONG.

'set @v=1; select @v' -> returns VAR_STRING
'select @v=1;'        -> returns LONGLONG
'set @v=1; create table t1 as select @v'  -> column in table is BIGINT

This is a duplicate of Bug#26277 "User variable returns one type in SELECT @v and other for CREATE as SELECT @v".