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:Microsoft Windows (win xp pro/linux)
Assigned to: Sergey Glukhov

[8 May 2006 0:04] Roland Bouman
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
[8 May 2006 13:53] Miguel Solorzano
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>
[29 May 2006 9:25] Bugs System
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
[2 Jun 2006 13:23] Alexander Barkov
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().
[5 Jun 2006 9:07] Bugs System
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
[5 Jun 2006 10:38] Bugs System
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
[5 Jun 2006 10:40] Alexander Barkov
This patch looks ok to push:
http://lists.mysql.com/commits/7265
[6 Jun 2006 7:44] Bugs System
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
[6 Jun 2006 7:47] Sergey Glukhov
Fixed in 5.0.23
[9 Jun 2006 16:22] Paul Dubois
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.