Description:
A certain join on 2 federated tables, one of which contains a unique index, crashes mysql:
Note that this does not seem related to a "reserved" word (as in bug #28269), but it does seem somewhat related to, but much different than, the other issue I opened (#33946).
Here is the query:
SELECT *
FROM CLIENT CL INNER JOIN STOCK_MSISDN SM ON SM.ID_CLIENT = CL.ID_CLIENT
WHERE CL.NUM_HIGHDEAL IS NOT NULL;
ERROR 2013 (HY000): Lost connection to MySQL server during query
Nothing is in the error log.
How to repeat:
On System A:
CREATE TABLE `CLIENT` (
`ID_CLIENT` bigint(20),
`NUM_HIGHDEAL` varchar(8),
PRIMARY KEY (`ID_CLIENT`),
UNIQUE KEY `NUM_HIGHDEAL_2` (`NUM_HIGHDEAL`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `STOCK_MSISDN` (
`MSISDN` int(10),
`ID_CLIENT` bigint(20)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
insert into client (ID_CLIENT, NUM_HIGHDEAL) values (1, 'aaa'), (2, 'bbb');
INSERT INTO STOCK_MSISDN (MSISDN, ID_CLIENT) VALUES (1, 1), (2, 2);
On System B:
CREATE TABLE `CLIENT` (
`ID_CLIENT` bigint(20),
`NUM_HIGHDEAL` varchar(8),
PRIMARY KEY (`ID_CLIENT`),
UNIQUE KEY `NUM_HIGHDEAL_2` (`NUM_HIGHDEAL`)
) ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://root:mysql@localhost:3306/fed/CLIENT';
CREATE TABLE `STOCK_MSISDN` (
`MSISDN` int(10),
`ID_CLIENT` bigint(20)
) ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://root:mysql@localhost:3306/fed/STOCK_MSISDN';
SELECT *
FROM CLIENT CL INNER JOIN STOCK_MSISDN SM ON SM.ID_CLIENT = CL.ID_CLIENT
WHERE CL.NUM_HIGHDEAL IS NOT NULL;
ERROR 2013 (HY000): Lost connection to MySQL server during query
(And nothing is written to the error log)
Suggested fix:
I beleieve it has something to do with the fact the 'NUM_HIGHDEAL' column, and perhaps it's index. Also, the WHERE ... IS NOT NULL is required for the query to reproduce the error.
