MySQL Bugs: #33946: Join on Federated tables with Unique index gives error 1430 from storage engine

Bug #33946	Join on Federated tables with Unique index gives error 1430 from storage engine
Submitted:	20 Jan 7:28	Modified:	16 Apr 2:34
Reporter:	Chris Calender
Status:	Closed
Category:	Server: Federated	Severity:	S3 (Non-critical)
Version:	5.0.54	OS:	Any
Assigned to:	Sergey Vojtovich	Target Version:	5.1+
Tags:	federated, join, unique index, 1430, IS NOT NULL
Triage:	D3 (Medium) / R3 (Medium) / E3 (Medium)

Description:
A certain join on 2 federated tables, one of which contains a unique index, leads to the
following error:

ERROR 1030 (HY000): Got error 1430 from storage engine

Note that this does not seem related to a "reserved" word (as in bug #28269).

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 1030 (HY000): Got error 1430 from storage engine

Nothing is in the error log.

How to repeat:
On System A:

CREATE TABLE `CLIENT` (
  `ID_CLIENT` bigint(20),
  `NUM_HIGHDEAL` varchar(8),
  `ID_CONTRAT` bigint(20),
  `ID_BZH` varchar(50),
  `ETAT` tinyint(4),
  `CIVILITE` tinyint(4),
  `CIVILITEFAC` tinyint(4),
  `NOM` varchar(255),
  `PRENOM` varchar(255),
  `BIRTHDAY` date,
  `CYCLE` tinyint(4),
  PRIMARY KEY  (`ID_CLIENT`),
  UNIQUE KEY `NUM_HIGHDEAL_2` (`NUM_HIGHDEAL`),
  KEY `ID_BZH` (`ID_BZH`),
  KEY `CIVILITE` (`CIVILITE`),
  KEY `CIVILITEFAC` (`CIVILITEFAC`),
  KEY `ID_CONTRAT` (`ID_CONTRAT`),
  KEY `ETAT` (`ETAT`),
  KEY `MAX_ABO` (`NOM`,`PRENOM`,`BIRTHDAY`),
  KEY `CYCLE` (`CYCLE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `STOCK_MSISDN` (
  `MSISDN` int(10),
  `NUM_ABO` int(8),
  `ID_ENT` int(11),
  `ID_CLIENT` bigint(20),
  `ADD_OPT_DISE` tinyint(1),
  `RIO` char(12),
  PRIMARY KEY  (`MSISDN`),
  KEY `INDEX_CLIENT` (`ID_CLIENT`),
  KEY `INDEX_ENTITE` (`ID_ENT`),
  KEY `NUM_ABO` (`NUM_ABO`),
  KEY `IDX_ADD_OPT_DISE` (`ADD_OPT_DISE`),
  KEY `RIO` (`RIO`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into client (ID_CLIENT, NUM_HIGHDEAL) values (1, 'aaa'), (2, 'bbb');
INSERT INTO STOCK_MSISDN (MSISDN, ID_CLIENT, RIO) VALUES (1, 1, 1), (2, 2, 2);

On System B:

CREATE TABLE `CLIENT` (
  `ID_CLIENT` bigint(20),
  `NUM_HIGHDEAL` varchar(8),
  `ID_CONTRAT` bigint(20),
  `ID_BZH` varchar(50),
  `ETAT` tinyint(4),
  `CIVILITE` tinyint(4),
  `CIVILITEFAC` tinyint(4),
  `NOM` varchar(255),
  `PRENOM` varchar(255),
  `BIRTHDAY` date,
  `CYCLE` tinyint(4),
  PRIMARY KEY  (`ID_CLIENT`),
  UNIQUE KEY `NUM_HIGHDEAL_2` (`NUM_HIGHDEAL`),
  KEY `ID_BZH` (`ID_BZH`),
  KEY `CIVILITE` (`CIVILITE`),
  KEY `CIVILITEFAC` (`CIVILITEFAC`),
  KEY `ID_CONTRAT` (`ID_CONTRAT`),
  KEY `ETAT` (`ETAT`),
  KEY `MAX_ABO` (`NOM`,`PRENOM`,`BIRTHDAY`),
  KEY `CYCLE` (`CYCLE`)
) ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://root:mysql@localhost:3306/fed/CLIENT';

CREATE TABLE `STOCK_MSISDN` (
  `MSISDN` int(10),
  `NUM_ABO` int(8),
  `ID_ENT` int(11),
  `ID_CLIENT` bigint(20),
  `ADD_OPT_DISE` tinyint(1),
  `RIO` char(12),
  PRIMARY KEY  (`MSISDN`),
  KEY `INDEX_CLIENT` (`ID_CLIENT`),
  KEY `INDEX_ENTITE` (`ID_ENT`),
  KEY `NUM_ABO` (`NUM_ABO`),
  KEY `IDX_ADD_OPT_DISE` (`ADD_OPT_DISE`),
  KEY `RIO` (`RIO`)
) 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 1030 (HY000): Got error 1430 from storage engine

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.

BUG#33947 was marked as duplicate.

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/commits/41863

ChangeSet@1.2570, 2008-02-07 15:57:06+04:00, svoj@mysql.com +3 -0
  BUG#33946 - Join on Federated tables with Unique index gives error 1430
              from storage engine
  
  Federated may crash a server, return wrong result set, return
  "ERROR 1030 (HY000): Got error 1430 from storage engine" message
  when local (engine=federated) table has a key against nullable
  column.
  
  The problem was wrong implementation of function that creates
  WHERE clause for remote query from key.

BUG#33953 was marked as duplicate.

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/commits/42271

ChangeSet@1.2570, 2008-02-14 15:47:29+04:00, svoj@mysql.com +3 -0
  BUG#33946 - Join on Federated tables with Unique index gives error 1430
              from storage engine
  
  Federated may crash a server, return wrong result set, return
  "ERROR 1030 (HY000): Got error 1430 from storage engine" message
  when local (engine=federated) table has a key against nullable
  column.
  
  The problem was wrong implementation of function that creates
  WHERE clause for remote query from key.

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/commits/42277

ChangeSet@1.2571, 2008-02-14 16:27:01+04:00, svoj@mysql.com +3 -0
  BUG#33946 - Join on Federated tables with Unique index gives error 1430
              from storage engine
  
  Federated may crash a server, return wrong result set, return
  "ERROR 1030 (HY000): Got error 1430 from storage engine" message
  when local (engine=federated) table has a key against nullable
  column.
  
  The problem was wrong implementation of function that creates
  WHERE clause for remote query from key.

BUG#34775 was marked as duplicate.

Pushed into 5.1.24-rc

Pushed into 5.0.60

Pushed into 6.0.5-alpha

Pushed into 5.1.23-ndb-6.3.11.

Noted in 5.0.60, 5.1.24, 6.0.5 changelogs.

For a FEDERATED table with an index on a nullable column, accessing
the table could crash a server, return an incorrect result set, or
return ERROR 1030 (HY000): Got error 1430 from storage engine.