Bug #27534 Illegal mix of collations (utf8) and (latin1) for nested functions and table col

Description:
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%';

Results:
'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;

Results:
'test2'
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.

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;"

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'

Thank you for the report.

Verified as described. Only community version is affected.

Is there a target version yet that this will be fixed in?

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!

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.

This is a duplicate report with the bug#28875:

http://bugs.mysql.com/bug.php?id=28875