Bug #37601 Cast Is Not Done On Row Comparison

• Submitted: 24 Jun 2008 6:59
• Modified: 18 Mar 2009 14:32
• Reporter: Mikiya Okuno
• Status: Closed
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• Version: 5.0
• OS: Any
• Assigned to: Sergei Glukhov
• CPU Architecture: Any

[24 Jun 2008 6:59] Mikiya Okuno

Description:
When row comparison is done using parentheses like (a,b) = (c,d) and there are string data type columns with different character set, the statement causes the error like below:

ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='

I'm not certain this is a bug or an expected behavior by design.

How to repeat:
mysql> create table t_utf8 (a varchar(200), b int unsigned not null primary key auto_increment) default character set 'utf8';
Query OK, 0 rows affected (0.28 sec)

mysql> create table t_latin1 (c varchar(200), d int unsigned not null primary key auto_increment) default character set 'latin1';
Query OK, 0 rows affected (0.09 sec)

mysql> insert into t_utf8 (a) values('abc');
Query OK, 1 row affected (0.06 sec)

mysql> insert into t_latin1 (c) values('abc');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_utf8,t_latin1 where (a,b) = (c,d);
ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='

Suggested fix:
An appropriate cast should be done?

[24 Jun 2008 11:31] Peter Laursen

This is related: http://bugs.mysql.com/bug.php?id=28768

.. what I think is even more silly.  You can JOIN columns with different charset but not different collations (unless it has been changed since then).

[19 Dec 2008 8:10] Alexander Barkov

"Bug#29326 comparisons using row constructors throw collation errno"
has been marked as duplicate for this one.

[23 Jan 2009 15:14] Alexander Barkov

"Bug#41859 Two element row constructor causes Illegal mix of collations"
was marked as duplicate for this one.

[17 Feb 2009 12:39] 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/66639

2733 Sergey Glukhov	2009-02-17
      Bug#37601 Cast Is Not Done On Row Comparison
      In case of ROW item each compared pair does not
      check if argumet collations can be aggregated and
      thus appropiriate item conversion does not happen.
      The fix is to add the check and convertion for ROW
      pairs.

[19 Feb 2009 12:38] Alexander Barkov

http://lists.mysql.com/commits/66639 is OK to push.

[19 Feb 2009 13:21] 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/66906

2753 Sergey Glukhov	2009-02-19
      Bug#37601 Cast Is Not Done On Row Comparison
      In case of ROW item each compared pair does not
      check if argumet collations can be aggregated and
      thus appropiriate item conversion does not happen.
      The fix is to add the check and convertion for ROW
      pairs.
     @ mysql-test/r/row.result
        test result
     @ mysql-test/t/row.test
        test case
     @ sql/item.cc
        added agg_item_set_converter() function which was a part of
        agg_item_charsets() func. The only difference is that
        agg_item_set_converter() checks and converts items 
        using already known collation.
     @ sql/item.h
        added agg_item_set_converter() function
     @ sql/item_cmpfunc.cc
        In case of ROW item each compared pair does not
        check if argumet collations can be aggregated and
        thus appropiriate item conversion does not happen.
        The fix is to add the check and convertion for ROW
        pairs.

[9 Mar 2009 14:13] Bugs System

Pushed into 5.0.79 (revid:[e-mail]-20090309135922-a0di9ebkxoj4d4wv) (version source revid:[e-mail]-20090219132044-cc0xj52duw5j2aa3) (merge vers: 5.0.79) (pib:6)

[13 Mar 2009 16:58] Paul DuBois

Noted in 5.0.79 changelog.

Comparisons between row constructors, such as (a, b) = (c, d)
resulted in unnecessary Illegal mix of collations errors for string
columns.

Setting report to NDI pending push into 5.1.x/6.0.x.

[13 Mar 2009 19:08] Bugs System

Pushed into 5.1.33 (revid:[e-mail]-20090313111355-7bsi1hgkvrg8pdds) (version source revid:[e-mail]-20090219135900-6xqnviesu6q557cm) (merge vers: 5.1.33) (pib:6)

[13 Mar 2009 20:32] Paul DuBois

Noted in 5.1.33 changelog.

Setting report to NDI pending push into 6.0.x.

[18 Mar 2009 13:19] Bugs System

Pushed into 6.0.11-alpha (revid:[e-mail]-20090318122208-1b5kvg6zeb4hxwp9) (version source revid:[e-mail]-20090219144828-iejdzprcmwnzqnfg) (merge vers: 6.0.10-alpha) (pib:6)

[18 Mar 2009 14:32] Paul DuBois

Noted in 6.0.11 changelog.

[9 May 2009 16:47] Bugs System

Pushed into 5.1.34-ndb-6.2.18 (revid:[e-mail]-20090508185236-p9b3as7qyauybefl) (version source revid:[e-mail]-20090508100057-30ote4xggi4nq14v) (merge vers: 5.1.33-ndb-6.2.18) (pib:6)

[9 May 2009 17:44] Bugs System

Pushed into 5.1.34-ndb-6.3.25 (revid:[e-mail]-20090509063138-1u3q3v09wnn2txyt) (version source revid:[e-mail]-20090508175813-s6yele2z3oh6o99z) (merge vers: 5.1.33-ndb-6.3.25) (pib:6)

[9 May 2009 18:41] Bugs System

Pushed into 5.1.34-ndb-7.0.6 (revid:[e-mail]-20090509154927-im9a7g846c6u1hzc) (version source revid:[e-mail]-20090509073226-09bljakh9eppogec) (merge vers: 5.1.33-ndb-7.0.6) (pib:6)