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
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