Bug #12351 CONCAT with USER()/DATEBASE() etc, a constant and a column gets strange results
Submitted: 3 Aug 2005 16:09 Modified: 16 Aug 2005 4:05
Reporter: Anders Karlsson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.10/BK source OS:Linux (Linux)
Assigned to: Alexander Barkov CPU Architecture:Any

[3 Aug 2005 16:09] Anders Karlsson
Description:
A CONCAT of the result of a function such as USER/DATABASE, a literal string and a column value had the literal "duplicating" for each row. The result depnds a bit on which function (USER/DATABASE etc) is used. This only seems to happen with latin1 character sets. A table with UTF8 seems not to be affected by this.
The problem seems to come from the fact that there is a mix of latin1 and utf8 strings, a workaround is to convert USER() to latin1, or the literal and the column data to utf8.

How to repeat:
mysql> create table mytab(c1 char(5)) character set=latin1;;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into mytab values('row 1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into mytab values('row 2');
Query OK, 1 row affected (0.00 sec)

mysql> insert into mytab values('row 3');
Query OK, 1 row affected (0.00 sec)

mysql> select concat(user(), '--', c1) from mytab;
+-------------------------------+
| concat(user(), '--', c1)      |
+-------------------------------+
| karlsson@localhost--row 1     |
| karlsson@localhost----row 2   |
| karlsson@localhost------row 3 |
+-------------------------------+
3 rows in set (0.00 sec)

mysql> select concat(database(), '--', c1) from mytab;
+------------------------------+
| concat(database(), '--', c1) |
+------------------------------+
| test--row 1                  |
| test----row 2                |
| test----row 2--row 3         |
+------------------------------+
3 rows in set (0.00 sec)

-- Workarounds below.
mysql> select concat(user(), CONVERT('--' USING utf8), CONVERT(c1 USING utf8)) from mytab;
+------------------------------------------------------------------+
| concat(user(), CONVERT('--' USING utf8), CONVERT(c1 USING utf8)) |
+------------------------------------------------------------------+
| karlsson@localhost--row 1                                        |
| karlsson@localhost--row 2                                        |
| karlsson@localhost--row 3                                        |
+------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> select concat(CONVERT(user() USING latin1), '--', c1) from mytab;
+------------------------------------------------+
| concat(CONVERT(user() USING latin1), '--', c1) |
+------------------------------------------------+
| karlsson@localhost--row 1                      |
| karlsson@localhost--row 2                      |
| karlsson@localhost--row 3                      |
+------------------------------------------------+
3 rows in set (0.00 sec)
[8 Aug 2005 14:15] 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/internals/27996
[12 Aug 2005 12:26] Alexander Barkov
Reviewed by Serg.
Pushed into and 4.1.14 and 5.0.12.
[16 Aug 2005 4:05] Mike Hillyer
Documented in 5.0.12 and 4.1.14 changelogs:

<listitem><para>
 Concatenating </literal>USER()</literal>/<literal>DATEBASE()</literal> with a column produces invalid results. (Bug #12351)
</para></listitem>