Bug #10848 Joins not working in federated storage engine
Submitted: 25 May 2005 0:11 Modified: 1 Jun 2005 4:05
Reporter: Patrick Galbraith Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.0a OS:Any (all)
Assigned to: Bugs System CPU Architecture:Any

[25 May 2005 0:11] Patrick Galbraith
Description:
simple inner joins do not work (return result set where it should) with the federated storage engine. If you specify a right or left join, they will work.

How to repeat:
DROP TABLE IF EXISTS federated.t1;
CREATE TABLE federated.t1 (
    `id` int(20) NOT NULL auto_increment,
    `country_id` int(20) NOT NULL DEFAULT 0,
    `name` varchar(32),
    `other` varchar(20),
    PRIMARY KEY  (`id`),
    key (country_id));

connection master;
DROP TABLE IF EXISTS federated.countries;
CREATE TABLE federated.countries (
    `id` int(20) NOT NULL auto_increment,
    `country` varchar(32),
    PRIMARY KEY (id));
INSERT INTO federated.countries (country) VALUES ('India');
INSERT INTO federated.countries (country) VALUES ('Germany');
INSERT INTO federated.countries (country) VALUES ('Italy');
INSERT INTO federated.countries (country) VALUES ('Finland');
INSERT INTO federated.countries (country) VALUES ('Ukraine');

DROP TABLE IF EXISTS federated.t1;
--replace_result $SLAVE_MYPORT SLAVE_PORT
eval CREATE TABLE federated.t1 (
    `id` int(20) NOT NULL auto_increment,
    `country_id` int(20) NOT NULL DEFAULT 0,
    `name` varchar(32),
    `other` varchar(20),
    PRIMARY KEY  (`id`),
    KEY (country_id) )
  ENGINE="FEDERATED" DEFAULT CHARSET=latin1
  COMMENT='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1';

INSERT INTO federated.t1 (name, country_id, other) VALUES ('Kumar', 1, 11111);
INSERT INTO federated.t1 (name, country_id, other) VALUES ('Lenz', 2, 22222);
INSERT INTO federated.t1 (name, country_id, other) VALUES ('Marizio', 3, 33333);
INSERT INTO federated.t1 (name, country_id, other) VALUES ('Monty', 4, 33333);
INSERT INTO federated.t1 (name, country_id, other) VALUES ('Sanja', 5, 33333);

#inner join
SELECT federated.t1.name AS name, federated.t1.country_id AS country_id,
federated.t1.other AS other, federated.countries.country AS country
FROM federated.t1, federated.countries WHERE
federated.t1.country_id = federated.countries.id;

SELECT federated.t1.name AS name, federated.t1.country_id AS country_id,
federated.t1.other AS other, federated.countries.country AS country
FROM federated.t1 INNER JOIN federated.countries ON
federated.t1.country_id = federated.countries.id;

Both of these queries should return results, but they don't if table->status is not set to 0 in index_read_idx

Suggested fix:
table->status was simply not being set to 0 in index_read_idx

/* very important - joins will not work without this! */
  table->status=0;

results:

SELECT federated.t1.name AS name, federated.t1.country_id AS country_id,
federated.t1.other AS other, federated.countries.country AS country
FROM federated.t1, federated.countries WHERE
federated.t1.country_id = federated.countries.id;
name    country_id      other   country
Kumar   1       11111   India
Lenz    2       22222   Germany
Marizio 3       33333   Italy
Monty   4       33333   Finland
Sanja   5       33333   Ukraine
SELECT federated.t1.name AS name, federated.t1.country_id AS country_id,
federated.t1.other AS other, federated.countries.country AS country
FROM federated.t1 INNER JOIN federated.countries ON
federated.t1.country_id = federated.countries.id;
name    country_id      other   country
Kumar   1       11111   India
Lenz    2       22222   Germany
Marizio 3       33333   Italy
Monty   4       33333   Finland
Sanja   5       33333   Ukraine
[25 May 2005 0:16] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/25244
[1 Jun 2005 4:05] Paul Dubois
Noted in 5.0.7 changelog.