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)
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)