How does MySQL coerce types during joins? Why do these two queries behave differently:

mysql> explain select FOOBAAR_hhs.relationship_group from FOOBAAR_hhs left join FOOBAAR on FOOBAAR_hhs.relationship_group = FOOBAAR.relationship_group where new_hh_id = 15387929;

| id | select_type | table       | type  | possible_keys | key       | key_len | ref   | rows     | Extra       |
+----+-------------+-------------+-------+---------------+-----------+---------+-------+----------+-------------+
|  1 | SIMPLE      | FOOBAAR_hhs | ref   | new_hh_id     | new_hh_id | 9       | const |       15 | Using where |
|  1 | SIMPLE      | FOOBAAR     | index | NULL          | BAARn     | 126     | NULL  | 83236197 | Using index |


mysql> explain select FOOBAAR_hhs.relationship_group from FOOBAAR_hhs left join FOOBAAR on cast(FOOBAAR_hhs.relationship_group as char) = FOOBAAR.relationship_group where new_hh_id = 15387929;

| id | select_type | table       | type | possible_keys | key       | key_len | ref   | rows | Extra       |
+----+-------------+-------------+------+---------------+-----------+---------+-------+------+-------------+
|  1 | SIMPLE      | FOOBAAR_hhs | ref  | new_hh_id     | new_hh_id | 9       | const |   15 | Using where |
|  1 | SIMPLE      | FOOBAAR     | ref  | BAARn         | BAARn     | 22      | func  |    3 | Using index |


?

The relationship_group members of both relations are typed VARCHAR(20) NOT NULL, but it is only when an explicit CAST is invoked that MySQL permits use of the available index?

(This is MySQL version 5.1.52-log)
You need to make absolutely sure the character sets of the tables are identical. Keep in mind that a unicode issues could be at play here.

You could redo the query like this:

select FOOBAAR_hhs.relationship_group from FOOBAAR left join FOOBAAR_hhs
on FOOBAAR.relationship_group = FOOBAAR_hhs.relationship_group where new_hh_id = 15387929;


or

select FOOBAAR_hhs.relationship_group from FOOBAAR left join FOOBAAR_hhs
USING (relationship_group) where new_hh_id = 15387929;


Try the explain for these and see if it changes