Bug #32613 String constant in subquery fails if CHARSET is utf8 (not latin1)
Submitted: 22 Nov 2007 10:41 Modified: 22 Nov 2007 10:53
Reporter: Berto van de Kraats Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.0.45 MySQL Community Server OS:Linux (2.6.16.46-0.12-smp)
Assigned to: CPU Architecture:Any
Tags: IN, string constant, subquery, utf8

[22 Nov 2007 10:41] Berto van de Kraats 
Description:
The IN predicate is incorrectly evaluated if the left operand is a string constant and the right operand is a subquery and the character set is utf8 (or any character set other than latin1). Error seems reproducible on all storage engines.

How to repeat:
Run the following script:

DROP TABLE IF EXISTS x;
CREATE TABLE x ( 
        str CHAR(6)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO x VALUES ('BOB');
SELECT COUNT(*) FROM x;
SELECT COUNT(*) FROM x WHERE str IN ( SELECT str FROM x );
SELECT COUNT(*) FROM x WHERE 'BOB' IN ( SELECT str FROM x );

All three SELECT statements should return the value 1, but the third statement returns 0, which demonstrates that the comparison "'BOB' IN ( SELECT str ...)" is incorrectly evaluated. If the CHARSET is changed to latin1, then the correct results are returned.
[22 Nov 2007 10:53] Sveta Smirnova 
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

You should use correct client character set settings to get expected result. See also http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html
[22 Nov 2007 11:25] MySQL Verification Team 
c:\dev\5.0>bin\mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.52-nt Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS x;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE x (
    ->         str CHAR(6)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.28 sec)

mysql> INSERT INTO x VALUES ('BOB');
Query OK, 1 row affected (0.06 sec)

mysql> SELECT COUNT(*) FROM x;
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM x WHERE str IN ( SELECT str FROM x );
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM x WHERE 'BOB' IN ( SELECT str FROM x );
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql>