MySQL Bugs: #19599: duplication of information_schema column value in a CONCAT expr with user var

Bug #19599	duplication of information_schema column value in a CONCAT expr with user var
Submitted:	8 May 2006 0:04	Modified:	9 Jun 2006 16:22
Reporter:	Roland Bouman	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.1.9/5.0BK/5.1BK	OS:	Windows (win xp pro/linux)
Assigned to:	Sergei Glukhov	CPU Architecture:	Any

Description:
A specially constructed CONCAT expression on an information schema column projects data correctly only for the first record. After that, the column value for the first record is duplicated and concatenated 'inline' for all subsequent records. 

The expression selects the correct results for columns that reside inside the information_schema itself; the error does occur for all other databases.

The concat expression has the form:

CONCAT(@uservar,information_schema_column)

where the user variable @uservar has been initialized with a non-empty string prior to this query.

See how to repeat:

How to repeat:
set @a:= '.';

select concat(@a, table_name)
,        @a
,        table_name
from   information_schema.tables
where table_schema = 'sakila'
limit 2;

+-----------------------+------+------------+
| concat(@a,table_name) | @a   | table_name |
+-----------------------+------+------------+
| .actor                | .    | actor      |
| .actoractor_info      | .    | actor_info |
+-----------------------+------+------------+

Note the second row: the concat expression has repeated the 'actor' string in front of the value for that record, 'actor_info'.

the user variable appearing at the first position if the concat definitely has something to do with it, because ommiting it yields a result that's just as expected, and this:

concat(table_name,@a,table_name)

also works according to expectations.

That the column is an information schema column also has something to do with it, because you cannot get this result for a 'normal' column.

Suggested fix:
LOL! don't duplicate the value

Thank you for the bug report.

miguel@hegel:~/dbs/5.0> bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.22-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database sakila;
Query OK, 1 row affected (0.03 sec)

mysql> use sakila
Database changed
mysql> create table actor (actor_id int);
Query OK, 0 rows affected (0.02 sec)

mysql> create table actor_info (actor_id int, name char(12));
Query OK, 0 rows affected (0.03 sec)

mysql> set @a:= '.';
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> select concat(@a, table_name)
    -> ,        @a
    -> ,        table_name
    -> from   information_schema.tables
    -> where table_schema = 'sakila'
    -> limit 2;
+------------------------+------+------------+
| concat(@a, table_name) | @a   | table_name |
+------------------------+------+------------+
| .actor                 | .    | actor      | 
| .actoractor_info       | .    | actor_info | 
+------------------------+------+------------+
2 rows in set (0.01 sec)

mysql>

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/6954

It is not correct to fix Item_func_concat::val_str().
Item_func_get_user_var::val_str() should be fixed instead.

The String value returned from Item_func_get_user_var::val_str()
should be marked using String::mark_as_const(),
which will prevent CONCAT from reusing it as a buffer
for concatenation result.

Another way is to fix user_var_entry::val_str() to do mark_as_const().

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/7261

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/7265

This patch looks ok to push:
http://lists.mysql.com/commits/7265

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/7307

Fixed in 5.0.23

Noted in 5.0.23 changelog.

Using CONCAT(@user_var, col_name), where col_name is a column in an
INFORMATION_SCHEMA table, could cause erroneous duplication of data
in the query result.