Interesting need for casts in IN clauses

[James Massey]

Hi. Sorry if this has been brought up before, but I can't find an mention of it.

In MySQL 5.0.67, there is a need for casting varchars to chars in IN clauses, that seems incorrect.

If you have a table MESSAGE_PARAMETERS of:

+-------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+-------+
| BROADCAST_MESSAGE | bigint(20) | NO | PRI | NULL | |
| VALUE | varchar(512) | YES | | NULL | |
| NAMESPACEKEY | varchar(100) | NO | PRI | NULL | |
| SUBKEY | varchar(100) | NO | PRI | NULL | |
| IDXKEY | int(11) | NO | PRI | NULL | |
+-------------------+--------------+------+-----+---------+-------+

with several rows that have the value 'test' in NAMESPACEKEY, then execute this query:

select 'test' in (select NAMESPACEKEY from MESSAGE_PARAMETERS);

0 is returned. However, if you cast the varchar column to char:

select 'test' in (select CAST(NAMESPACEKEY as CHAR) from MESSAGE_PARAMETERS);

1 is correctly returned.

This cast of varchar to char seems unnecessary to me. Surely varchar is a char? This causes a problem with 'in indices()' in hibernate, but works in postgres.

Can anyone tell me if this is actually a bug, or if I am misunderstanding the typing that goes on?

Thanks