MySQL Bugs: #12774: Function UUID() and REPLACE(), can't work together

Bug #12774	Function UUID() and REPLACE(), can't work together
Submitted:	24 Aug 2005 3:00	Modified:	26 Aug 2005 13:01
Reporter:	Poh Chuan Sim	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.13, 5.0.11	OS:	Windows (Windows XP SP2)
Assigned to:		CPU Architecture:	Any

Description:
I try to run the sample statement at below:

  SELECT REPLACE(UUID(), '-', '');

I think it very sample... why can't run?

SERVER show the error message at below:
[localhost] ERROR 1270: Illegal mix of collations (utf8_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'replace'

i try run SELECT UUID();, is no problem.
i also have try use a variable to store the UUID() value, and then use the function REPLACE to replace the varible, also no problem. why can't replace directly...

How to repeat:
SELECT REPLACE(UUID(), '-', '');

Suggested fix:
no suggestion... (pls no suggest me to use varible to solve this problem...)

Thanks for bug report. For quick solution I can suggest:

SELECT REPLACE(CAST(UUID() as char character set utf8), '-', '');

or

SELECT REPLACE(CAST(UUID() as char character set latin1), '-', '');

Could you please write here result of follow query:

 show variables like "char%";

OK, this is my result:

+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | latin1                                                  |
| character_set_connection | latin1                                                  |
| character_set_database   | latin1                                                  |
| character_set_results    | latin1                                                  |
| character_set_server     | latin1                                                  |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 4.1\share\charsets/ |
+--------------------------+---------------------------------------------------------+

The same behaviour was repeated with newer 4.1.13 and 5.0.11 versions.

It is not a bug, really. Just happened so that your character_set_system is utf8 and your character_set_client (used for second argument of replace) is not (latin1). It can be changed globally, for example, with the following mysql.exe startup option:

C:\Documents and Settings\openxs>mysql --default-character-set=utf8 -u root -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 49 to server version: 4.1.13a-nt

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

mysql> SELECT REPLACE(UUID(), '-', '');
+----------------------------------+
| REPLACE(UUID(), '-', '')         |
+----------------------------------+
| 11d1781d67811028ad7d9a0f7b3fc933 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> show variables like "char%";
+--------------------------+----------------------------------------------------
-----+
| Variable_name            | Value
     |
+--------------------------+----------------------------------------------------
-----+
| character_set_client     | utf8
     |
| character_set_connection | utf8
     |
| character_set_database   | cp1251
     |
| character_set_results    | utf8
     |
| character_set_server     | cp1251
     |
| character_set_system     | utf8
     |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 4.1\share\chars
ets/ |
+--------------------------+----------------------------------------------------
-----+
7 rows in set (0.00 sec)

You may post a feature request (convert latin1 to utf8 in such cases) or ask about clear description of the character_set used for the result of UUID(), though.

question is not uuid() and replace() can't work together, if you try concat(),and other string function ;
The unexpect result came out because there are  inexplicit conversion happend when your charset client and charset result is not utf8;
 
1  uuid()output is utf8, no matter what your charset is 
2  when your charset client and charset result is some of high priority than utf8 such as utf8mb4 , then the inexplicit conversion happen,other lower priority charset like latin1 that work fine  
3  inexplicit conversion will ture  uuid() into constant string before sql execute,
so after sql execute finish , the same uuid() return 
 
you can see all these happend by using explain extend + your clause ,and then using show warnings;

how to solve ?
1  turn inexplicit convertion to explicit convertion 
for example : 
set names utf8mb4;
replace( convert(uuid() using utf8mb4), '-','')

or 

set names utf8;
replace( uuid(),_utf8'-',_utf8'')

2  aviod inexplicit convertion
for example : set names utf8

sorry i make a mistake the second example  
set names utf8;
replace( uuid(),_utf8'-',_utf8'')

modify to 
set names utf8mb4;
replace( uuid(),_utf8'-',_utf8'')

explain extend  
modify to 
explain extended