MySQL Bugs: #26277: User variable returns one type in SELECT @v and other for CREATE as SELECT @v

Bug #26277	User variable returns one type in SELECT @v and other for CREATE as SELECT @v
Submitted:	12 Feb 2007 8:18	Modified:	7 Jun 2007 16:38
Reporter:	Sveta Smirnova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1 bk, 5.0 bk, 5.1 bk	OS:	Any (Linix)
Assigned to:	Kristofer Pettersson	CPU Architecture:	Any
Tags:	bfsm_2007_02_15

Description:
User variable returns one type in SELECT @v and other for CREATE as SELECT @v

All supported versions are affected

How to repeat:
mysql --debug-info

mysql> set @SHARD = 0;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select @SHARD;
Field   1:  `@SHARD`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     20
Max_length: 1
Decimals:   0
Flags:      BINARY

mysql> create table ta as select @SHARD;
mysql> desc ta;
+--------+------------+------+-----+---------+-------+
| Field  | Type       | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| @SHARD | bigint(20) | YES  |     | NULL    |       | 
+--------+------------+------+-----+---------+-------+

Instructions how to fix:

the problem is caused by discrepancy of Item_func_get_user_var::result_type() and field_type() mehtods.

They are implemented as follows:
  /*
    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)
  */
  enum_field_types field_type() const  { return MYSQL_TYPE_VARCHAR; }

enum Item_result Item_func_get_user_var::result_type() const
{
  user_var_entry *entry;
  if (!(entry = (user_var_entry*) hash_search(&current_thd->user_vars,
                                              (byte*) name.str,
                                              name.length)))
    return STRING_RESULT;
  return entry->type;
}

As you can see from the code, result_type may change depending on the actual
type of the variable, whereas field_type never changes.

Reasons for this implementation are:
- user variables have mutating types. A type of a variable can be either
STRING, DECIMAL INT or REAL, and in can change in the course of query exection.
An example is provided by the comment for field_type() method, but
simplistically, with a stored function:

mysql> create function f1() returns int begin if @a=1 then set @b="abc";
  else set @b=1; end if; set @a=1; return 0; end|
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (a int)|
Query OK, 0 rows affected (0.09 sec)

mysql> insert into t1 (a) values (1), (2)|
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0 

mysql> set @a=0| select f1(), @a, @b from t1|
Query OK, 0 rows affected (0.00 sec)
+------+------+------+
| f1() | @a   | @b   |
+------+------+------+
|    0 | 1    | 1    | 
|    0 | 1    | abc  | 
+------+------+------+
2 rows in set (0.00 sec)

- our client/server protocol is designed to first send column types,
and then column data, for every result set record. It does not support
mutating types, since column types are sent only once.
That's why field_type() implementation is hard-coded.

So, the solution that would fix the bug and not break the protocol is
- remember the type of the variable at the first invocation
- use it consistently in both methods (field_type() and result_type()). 
In other words, ensure that field_type() and result_type() do not 
mutate over the course of a single query.
The type can be remembered in fix_fields() and then used in field_type()
and result_type().

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/26510

ChangeSet@1.2508, 2007-05-11 16:52:29+02:00, thek@adventure.(none) +8 -0
  Bug#26277 User variable returns one type in SELECT @v and other for CREATE as SELECT @v
  
  - Adding variable m_cached_result_type to keep the variable type consistent
    during the execution of a bundled set of statements (query).
  - Previously the result type could change if the hash variable entry changed
    between statements. This caused the result set of the query to alternate
    column types in certain cases which is not supported by our client-server 
    protocol.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/26586

ChangeSet@1.2508, 2007-05-14 13:21:37+02:00, thek@adventure.(none) +8 -0
  Bug#26277 User variable returns one type in SELECT @v and other for CREATE as SELECT @v
  
  - Adding variable m_cached_result_type to keep the variable type consistent
    during the execution of a statement.
  - Previously the result type could change if the hash variable entry changed
    between statements. This caused the result set of the query to alternate
    column types in certain cases which is not supported by our client-server 
    protocol.

The customer approved 5.1-only fix.
OK to push as per code review sent by email (the issue with typecodes is resolved, but needs to be clarified in the changeset comment).

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/26969

ChangeSet@1.2508, 2007-05-18 12:44:03+02:00, thek@adventure.(none) +12 -0
  Bug#26277 User variable returns one type in SELECT @v and other for CREATE as SELECT @v
  
  - Adding variable m_cached_result_type to keep the variable type consistent
    during the execution of a statement.
  - Before each result set is returned to the client the description of each
    column is sent as meta data.
    Previously the result type for a column could change if the hash variable
    entry changed between statements. This caused the result set of the query
    to alternate column types in certain cases which is not supported by MySQL
    client-server protocol. Example:
    Previously this sequence:
      SET @a:=1;
      SELECT @a:="text", @a;
    would return "text", "text";
   
    After the change the SELECT returns "text", 0
    The reson for this is that previously the result set from 'SELECT @a;'
    would always be of the type STRING, whereas now the type of the variable
    is taken from the last SET statement. However, 'SELECT @a:="text"' will
    return type of STRING since the right side of the assignment is used.

Bug #26190 "when using user variable in select, the correct type should be reported was marked a dupliate of this bug."

Pushed into 5.1.20-beta

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.1.20 changelog.