MySQL Bugs: #42303: MySQL subquery IN problem

Bug #42303	MySQL subquery IN problem
Submitted:	23 Jan 2009 13:04	Modified:	25 Feb 2009 16:41
Reporter:	Kei Tsoi	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.67	OS:	Linux (Ubuntu 8.10 (x86_64))
Assigned to:		CPU Architecture:	Any

Description:
The following code:

DROP DATABASE IF EXISTS `test`;
CREATE DATABASE `test`;
USE test;
CREATE TABLE `pair` (
  `PREFERENCE_ID` VARCHAR(50) CHARACTER SET LATIN1 NOT NULL,
  `IDEA_GROUP_ID` VARCHAR(255) CHARACTER SET LATIN1 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `pair` (`PREFERENCE_ID`, `IDEA_GROUP_ID`)
  VALUES ("A", "B");
SELECT "A" IN (SELECT `PREFERENCE_ID` FROM `pair`);

Returns 0 on my machine, when it should return 1. I cannot replicate this problem on any other machine. mysql --version prints:

mysql  Ver 14.12 Distrib 5.0.67, for debian-linux-gnu (x86_64) using readline 5.2

Which is the standard version for my release of Ubuntu. Nothing in my my.cnf file should change this behavior.

How to repeat:
Not repeatable outside current machine

default my.cnf

Attachment: my.cnf (application/octet-stream, text), 4.21 KiB.

Thank you for a problem report. Are you sure server version is 5.0.67? Please, send also the results of 

select version();

and 

select @@sql_mode\G

statements. Just copy and paste, like this:

mysql> CREATE TABLE `pair` (
    ->   `PREFERENCE_ID` VARCHAR(50) CHARACTER SET LATIN1 NOT NULL,
    ->   `IDEA_GROUP_ID` VARCHAR(255) CHARACTER SET LATIN1 NOT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.19 sec)

mysql> INSERT INTO `pair` (`PREFERENCE_ID`, `IDEA_GROUP_ID`)
    ->   VALUES ("A", "B");
Query OK, 1 row affected (0.03 sec)

mysql> SELECT "A" IN (SELECT `PREFERENCE_ID` FROM `pair`);
+---------------------------------------------+
| "A" IN (SELECT `PREFERENCE_ID` FROM `pair`) |
+---------------------------------------------+
|                                           1 |
+---------------------------------------------+
1 row in set (0.08 sec)

mysql> select version();
+------------------------------------------+
| version()                                |
+------------------------------------------+
| 5.1.30-enterprise-gpl-advanced-debug-log |
+------------------------------------------+
1 row in set (0.02 sec)

mysql> select @@sql_mode\G
*************************** 1. row ***************************
@@sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.05 sec)

The results are below. It's also worth noting that this was "fixed" by removing the "default-character-set = utf8" line in my configuration, so that now it works in the case below, but fails when the table's fields are created with "CHARACTER SET utf8" instead of "CHARACTER SET LATIN1".

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

mysql> select version();
+-----------------+
| version()       |
+-----------------+
| 5.0.67-0ubuntu6 |
+-----------------+
1 row in set (0.01 sec)

Thank you for the feedback.

Please indicate if you use Ubuntu binaries and if yes please download ours generic Linux from http://dev.mysql.com/downloads, install them somewhere at problem machine and compare results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".