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

[8 Feb 2007 17:17] Axel Schwenke
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.
[8 Feb 2007 19:46] Mark Callaghan
'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
[9 Feb 2007 9:53] Axel Schwenke
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>
[12 Feb 2007 20:57] Mark Callaghan
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
[26 May 2007 15:44] Konstantin Osipov
This is a duplicate of Bug#26277 "User variable returns one type in SELECT @v and other for CREATE as SELECT @v".