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: | |
Category: | MySQL Server | Severity: | S2 (Serious) |
Version: | 4.1 | OS: | Linux (Linux) |
Assigned to: | Alexander Barkov | CPU Architecture: | Any |
[24 Apr 2003 1:18]
[ name withheld ]
[24 Apr 2003 5:44]
Alexander Keremidarski
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
[24 Apr 2003 6:33]
Alexander Barkov
What does this return: SHOW VARIABLES LIKE 'character_set%'; ?
[24 Apr 2003 7:02]
Alexander Barkov
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.