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
• Status: Closed
• Category: MySQL Server: Charsets
• Severity: S3 (Non-critical)
• Version: 5.0 and higher
• OS: Any
• Assigned to: Sergei Glukhov
• CPU Architecture: Any

[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:[e-mail]-20090327142516-55gumdxj39z6eijj) (version source revid:[e-mail]-20090324072904-7w2lxdxzw8hx1rnm) (merge vers: 5.0.80) (pib:6)

[27 Mar 2009 14:56] Bugs System

Pushed into 5.1.34 (revid:[e-mail]-20090327143448-wuuuycetc562ty6o) (version source revid:[e-mail]-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:[e-mail]-20090413084402-snnrocwzktcl88ny) (version source revid:[e-mail]-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:[e-mail]-20090508185236-p9b3as7qyauybefl) (version source revid:[e-mail]-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:[e-mail]-20090509063138-1u3q3v09wnn2txyt) (version source revid:[e-mail]-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:[e-mail]-20090509154927-im9a7g846c6u1hzc) (version source revid:[e-mail]-20090509154927-im9a7g846c6u1hzc) (merge vers: 5.1.34-ndb-7.0.6) (pib:6)