Bug #8291 Illegal collation mix with USER() function

• Submitted: 3 Feb 2005 15:47
• Modified: 11 Mar 2005 19:23
• Reporter: Alexander Barkov
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Assigned to: Alexander Barkov
• CPU Architecture: Any

[3 Feb 2005 15:47] Alexander Barkov

Description:
Illegal mix of collations error when mixing a latin1 column
with the function USER().

How to repeat:
mysql> create table users(lastlogin datetime, user varchar(10) character set latin1);
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE users SET lastlogin=curdate() WHERE user=substring_index(user(),         "@", 1);
ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='
mysql>

[21 Feb 2005 12:07] Alexander Barkov

An easier example 

mysql> create table t1 (user char(10));
Query OK, 0 rows affected (0.01 sec)
mysql> select * from t1 where user=user();
ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

[4 Mar 2005 10:24] 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/22651

[4 Mar 2005 10:33] Alexander Barkov

This bug was fixed in 4.1.11.
A new level of coercibility was introduced: SYSCONST,
between COERRIBLE and IMPLICIT.

The levels of predecence now look as follows:
0 - explicit
1 - none
2 - implicit
3 - sysconst
4 - coercible
So, coercivility() results were changed for coercible, it's now 4, not 3.

Thus:
SELECT col1 = USER() FROM t1; - is done according to col1 collation.
SELECT 'string' = USER(); - is done according to USER() collation (which is utf8_general_ci)

[11 Mar 2005 19:23] Paul DuBois

Noted in 4.1.11, 5.0.3 changelogs.