Bug #41627 Illegal mix of collations in LEAST / GREATEST / CASE
Submitted: 19 Dec 2008 8:27 Modified: 13 Apr 2009 19:22
Reporter: Alexander Barkov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Charsets Severity:S3 (Non-critical)
Version:5.0 and higher OS:Any
Assigned to: Sergey Glukhov
Triage: Triaged: D3 (Medium)

[19 Dec 2008 8:27] Alexander Barkov
Description:
MySQL throws "illegal mix of collations" errors in case of N-adic comparison operations too early, when N is more than 2, and the strongest argument
is neither on the first nor on the second place.

How to repeat:
mysql> select least(_latin1'a',_latin2'b',_latin5'c' collate latin5_turkish_ci);
ERROR 1270 (HY000): Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin5_turkish_ci,EXPLICIT) for operation 'least'

mysql> select case _latin1'a' when _latin2'b' then 1 when _latin5'c' collate latin5_turkish_ci then 2 else 3 end;
ERROR 1270 (HY000): Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin5_turkish_ci,EXPLICIT) for operation 'case'

It seems to aggregate the first and the second operands
and immediately through an error, without waiting for the
thirds and higher arguments.

mysql> select least(_latin5'c' collate latin5_turkish_ci, _latin1'a',_latin2'b');
+--------------------------------------------------------------------+
| least(_latin5'c' collate latin5_turkish_ci, _latin1'a',_latin2'b') |
+--------------------------------------------------------------------+
| a                                                                  | 
+--------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select least(_latin1'a', _latin5'c' collate latin5_turkish_ci, _latin2'b');
+---------------------------------------------------------------------+
| least(_latin1'a', _latin5'c' collate latin5_turkish_ci, _latin2'b') |
+---------------------------------------------------------------------+
| a                                                                   | 
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

Possible workarounds: move the strongest argument
to the first or to the second place:

First place:

mysql> select least(_latin5'c' collate latin5_turkish_ci, _latin1'a',_latin2'b');
+--------------------------------------------------------------------+
| least(_latin5'c' collate latin5_turkish_ci, _latin1'a',_latin2'b') |
+--------------------------------------------------------------------+
| a                                                                  | 
+--------------------------------------------------------------------+
1 row in set (0.00 sec)

Second place:

mysql> select least(_latin1'a', _latin5'c' collate latin5_turkish_ci, _latin2'b');
+---------------------------------------------------------------------+
| least(_latin1'a', _latin5'c' collate latin5_turkish_ci, _latin2'b') |
+---------------------------------------------------------------------+
| a                                                                   | 
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
Expected behaviour:

Don't throw an error after checking the first and the second arguments.
Continue with checking the third and higher arguments and if some of
them is stronger according to coercibility rules, then this argument's collation
should be chosen for the operation (the latin5_turkish_ci part in the above
examples).
[19 Dec 2008 8:38] Valeriy Kravchuk
Thank you for a bug report. Verified as described with latest 5.1.31 from bzr, among other versions.
[6 Mar 2009 11:46] 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/commits/68484

2767 Sergey Glukhov	2009-03-06
      Bug#41627 Illegal mix of collations in LEAST / GREATEST / CASE
      Don't throw an error after checking the first and the second arguments.
      Continue with checking the third and higher arguments and if some of
      them is stronger according to coercibility rules,
      then this argument's collation is set as result collation.
     @ mysql-test/r/ctype_collate.result
        test result
     @ mysql-test/t/ctype_collate.test
        test case
     @ sql/item.cc
        Don't throw an error after checking the first and the second arguments.
        Continue with checking the third and higher arguments and if some of
        them is stronger according to coercibility rules,
        then this argument's collation is set as result collation.
[10 Mar 2009 12:26] Alexander Barkov
The patch http://lists.mysql.com/commits/68484 looks ok to push.
[19 Mar 2009 8:20] 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/commits/69700

2777 Sergey Glukhov	2009-03-19
      Bug#41627 Illegal mix of collations in LEAST / GREATEST / CASE
      Don't throw an error after checking the first and the second arguments.
      Continue with checking the third and higher arguments and if some of
      them is stronger according to coercibility rules,
      then this argument's collation is set as result collation.
     @ mysql-test/r/ctype_collate.result
        test result
     @ mysql-test/t/ctype_collate.test
        test case
     @ sql/item.cc
        Don't throw an error after checking the first and the second arguments.
        Continue with checking the third and higher arguments and if some of
        them is stronger according to coercibility rules,
        then this argument's collation is set as result collation.
[27 Mar 2009 14:32] Bugs System
Pushed into 5.0.80 (revid:joro@sun.com-20090327142516-55gumdxj39z6eijj) (version source revid:leonard@mysql.com-20090324072904-7w2lxdxzw8hx1rnm) (merge vers: 5.0.80) (pib:6)
[27 Mar 2009 14:56] Bugs System
Pushed into 5.1.34 (revid:joro@sun.com-20090327143448-wuuuycetc562ty6o) (version source revid:sergey.glukhov@sun.com-20090319092612-yi8naqj8ap7axo61) (merge vers: 5.1.34) (pib:6)
[30 Mar 2009 2:27] Paul Dubois
Noted in 5.0.80, 5.1.34 changelogs.

With more than two arguments, LEAST(), GREATEST(), and CASE could
unnecessarily return "Illegal mix of collations" errors.

Setting report to NDI pending push into 6.0.x.
[13 Apr 2009 9:20] Bugs System
Pushed into 6.0.11-alpha (revid:alik@sun.com-20090413084402-snnrocwzktcl88ny) (version source revid:sergey.glukhov@sun.com-20090319094318-22m70sd15xk0tidr) (merge vers: 6.0.11-alpha) (pib:6)
[13 Apr 2009 19:22] Paul Dubois
Noted in 6.0.11 changelog.
[9 May 2009 16:44] Bugs System
Pushed into 5.1.34-ndb-6.2.18 (revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (version source revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (merge vers: 5.1.34-ndb-6.2.18) (pib:6)
[9 May 2009 17:41] Bugs System
Pushed into 5.1.34-ndb-6.3.25 (revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (version source revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (merge vers: 5.1.34-ndb-6.3.25) (pib:6)
[9 May 2009 18:38] Bugs System
Pushed into 5.1.34-ndb-7.0.6 (revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (version source revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (merge vers: 5.1.34-ndb-7.0.6) (pib:6)