MySQL Bugs: #321: SELECT with where clause doesn't work if collation is utf8

Bug #321	SELECT with where clause doesn't work if collation is utf8
Submitted:	24 Apr 2003 1:18	Modified:	24 Apr 2003 7:02
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1	OS:	Linux (Linux)
Assigned to:	Alexander Barkov	CPU Architecture:	Any

Description:
 The official binaries for 4.1.0-standard-alpha have a problem with utf8 character set. If collation on columns/table is set as utf8 select statements of the sort "select from bla where name = 'bla'" return "Wrong arguments to =".
 If the character set is left intact, the above select statements return correct results.
 Latest sources from BitKeeper have the same problem, whereas the version I've compiled from BitKeeper sources some 4 weeks ago (with --with-extra-charsets=complex --default-charset=utf8) works OK. 

How to repeat:
create table TEST(id int(11), name varchar(50)) type=MyISAM charset=utf8;
insert into TEST values(21, 'bla');
select * from TEST where name = 'bla';

result "Wrong arguments to =".

With 4.1 compiled form BK tree at 23th Apr test case works as expected, but same problem appears in another place:

mysql> create table TEST(id int(11), name varchar(50)) type=MyISAM charset=utf8;
Query OK, 0 rows affected (0.00 sec)

 mysql> show create table `TEST`;
+-------+-----------------------------------------------------------------------------------------------------------------+
| Table | Create Table
                                              |
+-------+-----------------------------------------------------------------------------------------------------------------+
| TEST  | CREATE TABLE `TEST` (
  `id` int(11) default NULL,
  `name` varchar(50) default NULL
) TYPE=MyISAM CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> insert into TEST values(21, 'bla');
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from TEST where name = 'bla';
+------+------+
| id   | name |
+------+------+
|   21 | bla  |
+------+------+

mysql> ALTER TABLE TEST MODIFY name VARCHAR(50) CHARACTER SET latin1;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
 
mysql> select * from TEST where name = 'bla';
ERROR 1210: Wrong arguments to =

Now error occurs if column character set differs from table character set

What does this return:

SHOW VARIABLES LIKE 'character_set%';

?

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

This is not a bug. You can't compare strings in different
character sets. Your default character set is utf8.
The column uses latin1. There are two ways to
do what you want:
1. Specify that the string is in latin1 too:
SELECT * FROM test WHERE name=_latin1'bla';

2. Convert the string from utf8 to latin1:
SELECT * FROM test WHERE name=CONVERT('bla' USING latin1);

I'm closing this bug.