Bug #1572 MySQL 4.1.0 does binary comparision on text columns when using IN

• Submitted: 16 Oct 2003 8:16
• Modified: 22 Oct 2003 13:41
• Reporter: [ name withheld ]
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1.0
• OS: Linux (Linux)
• CPU Architecture: Any

[16 Oct 2003 8:16] [ name withheld ]

Description:
MySQL 4.1.0 seems to be doing a binary comparison when comparing a text column using IN (.. 
WHERE colname IN ('foo', 'bar')).

MySQL reverts to non-binary comparision if column name is preceded with BINARY keyword (.. 
WHERE BINARY colname IN ('foo', 'bar'))

How to repeat:
mysql> select count(id) from screenshots where user = 'batmanppc';
+-----------+
| count(id) |
+-----------+
|        77 |
+-----------+
1 row in set (0.00 sec)

mysql> select count(id) from screenshots where user in ('batmanppc');
+-----------+
| count(id) |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

mysql> select count(id) from screenshots where lower(user) in ('batmanppc');
+-----------+
| count(id) |
+-----------+
|        77 |
+-----------+
1 row in set (0.02 sec)

mysql> select count(id) from screenshots where binary user = 'batmanppc';
+-----------+
| count(id) |
+-----------+
|         0 |
+-----------+
1 row in set (0.36 sec)

mysql> select count(id) from screenshots where binary user in ('batmanppc');
+-----------+
| count(id) |
+-----------+
|        77 |
+-----------+
1 row in set (0.02 sec)

[22 Oct 2003 13:41] Dean Ellis

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html