Left join in subquery producing strange results

[Graham Tilson]

This is a cut down version of my query:

SELECT
(SELECT
AVG(rr.offered)
FROM
`rfp_rates` rr
LEFT JOIN `lookups`.`currency_rates` exc
ON exc.currency_code = 'does not exist'
WHERE rr.duplicateid = 0
AND rr.rfpid = r.`rfpid`
AND rr.companycode = i.`companycode`) AS avg_room_rate
FROM
rfp_replies r
INNER JOIN data_core i
ON i.companycode = r.companycode
INNER JOIN rfp_requests p
ON r.rfpid = p.uniqueid
WHERE (
p.`rfptype` = 'A'
AND r.created BETWEEN '2012-01-08'
AND '2012-01-08 23:59:59'
)
AND i.companycode = 10000
GROUP BY i.companycode

It produces one row with a null value for avg_room_rate

There are three strange things here:

1) If I either remove the left join to `lookups`.`currency_rates` or change 'does not exist' to a value that does exist, the query returns the value for avg_room_rate that I expect.

2) if I remove the group by clause, the query still produces one row, but returns the value for avg_room_rate that I expect.

3) If I change the line:

AND rr.companycode = i.`companycode`) AS avg_room_rate

to

AND rr.companycode = r.`companycode`) AS avg_room_rate

the query returns the value for avg_room_rate that I expect.

Can anyone shed any light on this? I'm using mysql 5.5.2

Thanks