MySQL join with federated view not working

Question

I want to join a table with a view, where one table L is local, whereas the view F is a FEDERATED view residing on another server:

SELECT * FROM L LEFT JOIN F ON L.id = F.id;

Now the JOIN results in no hits despite the fact that there actually are many matches between the table and view. The ID field is bigint.

Frustrated, I created a TEMPORARY table T and dumped everything from F into it, thus making a local copy of F. Using T instead of F, the JOIN works as expected. But the process of creating T consumes memory and time.

What could be possible reasons for this odd MySQL behaviour?

---

Table definitions:

CREATE TABLE `L` (
  `id` bigint(20) NOT NULL,
  `id2` bigint(20) NOT NULL,
  PRIMARY KEY (`id`,`id2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and (this table is in fact a view on the remote server):

CREATE TABLE `F` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `field1` bigint(20) NOT NULL,
   ...
  `field5` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://userName:pword...';

Answer 1

As it states from definition of what FEDERATED storage-engine is, you must have table structure definition (so, for example .frm files for MyISAM) on both servers. That is because how FEDERATED engine works:

Therefore, you can not use VIEW since it has completely different meaning and structure. So instead you should mirror your table and then you'll be able to use it in your queries.