| 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 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: http://bugs.mysql.com/bug.php?id=28875
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.