Bug #27534 Illegal mix of collations (utf8) and (latin1) for nested functions and table col
Submitted: 29 Mar 2007 18:58 Modified: 22 Jun 2007 5:52
Reporter: Jamie Maher Email Updates:
Status: Duplicate Impact on me:
Category:MySQL Server: Charsets Severity:S2 (Serious)
Version:5.0.37-community-nt, 5.0.36-enterprise OS:Linux (Linux, Windows 2000 pro)
Assigned to: Assigned Account CPU Architecture:Any
Tags: bfsm_2007_06_21, Collations, Illegal, mix, Mysql 5, query browser, regression

[29 Mar 2007 18:58] Jamie Maher
When executing a simple nested query:

"select IF(a = 'bar',IF(b='bar','test1','test2'),a) from t1;"

 the server reports that there was:

"Illegal mix of collations (utf8_general_ci,COERCIBLE) and  latin1_swedish_ci,IMPLICIT) for operation 'if'"

Yet if I run:

"select IF(a = 'bar',IF(b='bar','test1','test2'),'firstIF-false') from t1;"

note: the second option for the first IF() is now just a client supplied value (utf8) then it completes successfully.

How to repeat:
Using MySQL Query Browser (default char sets utf8):

- Check client character and collation sets:

show variables where variable_name like 'char%';

'character_set_client', 'utf8'
'character_set_connection', 'utf8'
'character_set_database', 'latin1'
'character_set_filesystem', 'binary'
'character_set_results', 'utf8'
'character_set_server', 'latin1'
'character_set_system', 'utf8'
'character_sets_dir', 'C:\MySQL\share\charsets\'

- Create the table and populate it:

create table t1 (a char(3), b varchar(10));
insert into t1 values ('bar','foo');

- Attempt to run the offending query:
select IF(a = 'bar',IF(b='bar','test1','test2'),a) from t1;

Error: Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'if'

- Attempt to run a similar query:
select IF(a = 'bar',IF(b='bar','test1','test2'),'firstIF-false') from t1;

1 row fetched.

Using MySQL command line client (default char sets latin1) :

- Check the variables:

mysql> show variables where variable_name like 'char%';
| Variable_name            | Value                    |
| character_set_client     | latin1                   |
| character_set_connection | latin1                   |
| character_set_database   | latin1                   |
| character_set_filesystem | binary                   |
| character_set_results    | latin1                   |
| character_set_server     | latin1                   |
| character_set_system     | utf8                     |
| character_sets_dir       | C:\MySQL\share\charsets\ |
8 rows in set (0.00 sec)

- Run the offending query in mysql command line:

mysql> select IF(a = 'bar',IF(b='bar','test1','test2'),a) from t1;
| IF(a = 'bar',IF(b='bar','test1','test2'),a) |
| test2                                       |
1 row in set (0.00 sec)

- Second one which worked previously in query browser as well:

mysql> select IF(a = 'bar',IF(b='bar','test1','test2'),'firstIF-false') from t1;

| IF(a = 'bar',IF(b='bar','test1','test2'),'firstIF-false') |
| test2                                                     |
1 row in set (0.01 sec)

Suggested fix:
Fix the server so that the nested IF(IF()) statements work with column comparisons and client supplied strings.
[29 Mar 2007 20:23] Jamie Maher
Perhaps better examples would be:

fine: "select IF(a = 'bar',IF('foo'='foo','truefoo','test1'),a) from t1;"
fine: "select IF(a = 'bar',IF(b='foo',b,'test1'),a) from t1;"
fine: "select IF(a = 'bar','truefoo',a) from t1;"
fine: "select IF(b = 'foo','truefoo','test1') from t1;"

error: "select IF(a = 'bar',IF(b='foo','truefoo','test1'),a) from t1;"
[29 Mar 2007 20:31] Jamie Maher
An old instance of MySQL 4.0.23 that I have executes the query successfully:

"select IF(a = 'bar',IF(b='foo','truefoo','test1'),a) from t1;"

results: 'truefoo'
[30 Mar 2007 9:00] Sveta Smirnova
Thank you for the report.

Verified as described. Only community version is affected.
[6 Jun 2007 20:18] Jamie Maher
Is there a target version yet that this will be fixed in?
[8 Jun 2007 8:18] Axel Schwenke
This now also affects a customer: replication slave (5.0.42) behaves different than master (5.0.28) and thus replication breaks.

Simplified testcase:

\C latin1
drop table if exists t1, t2;
create table t1 (c1 date);
create table t2 (c2 char(7));
insert into t1 values ('2007-01-02'), ('2007-02-03');
insert into t2 values ('2007/01');
select * from t1, t2 where date_format(t1.c1, '%Y/%m') <= t2.c2;
\C utf8
select * from t1, t2 where date_format(t1.c1, '%Y/%m') <= t2.c2;

The last SELECT fails for 5.0.36 and later but works in previous versions (tested with 5.0.28 and 5.0.34). This is a REGRESSION bug!
[8 Jun 2007 8:29] Axel Schwenke
I just noticed that the charset of the result of DATE_FORMAT() changed in 5.0.36 to be @character_set_connection (before: BINARY) and thus the comparison fails.

But IMHO the coercibility of the DATE_FORMAT() result should be 4 (like a literal) and thus the comparison should be done using charset and collation from t2.c2.
[22 Jun 2007 5:51] Alexander Barkov
This is a duplicate report with the bug#28875: