Re: Wildcard and multiple tables with one of them on federated engine

[Stephan Schulz]

I did more investigation today and found out that it has to be something with the created index (I did not mention this before):
If I remove
---------------------------------------------------
KEY `testkey` (`num`,`sourceid`)
---------------------------------------------------
everything works as expected.
Also if I remove the
-----
%
-----
or
---------------------------------------------------
AND rmt.sourceid = local.sourceid
---------------------------------------------------
everything is fine.

I recreated everything from scratch with test-tables.
Please give me your opinion on this.

Here is my test-setup:

SELECT rmt.num, rmt.sourceid, local.sourceid FROM `rmt`, `local` WHERE rmt.num LIKE 't%' AND rmt.sourceid = local.sourceid;

remote:
__________________________________________________________
CREATE TABLE IF NOT EXISTS `rmt` (
`num` varchar(255) character set utf8 NOT NULL,
`sourceid` varchar(255) character set utf8 NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


INSERT INTO `rmt` (`num`, `sourceid`) VALUES
('ttteeeext', '1');
__________________________________________________________

local:
_________________________________________________________________
CREATE TABLE IF NOT EXISTS `local` (
`sourceid` varchar(255) CHARACTER SET utf8 NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


INSERT INTO `local` (`sourceid`) VALUES
('1');
_________________________________________________________________
local (the federated table):
_________________________________________________________________
CREATE TABLE IF NOT EXISTS `rmt` (
`num` varchar(255) NOT NULL,
`sourceid` varchar(255) NOT NULL,
KEY `testkey` (`num`,`sourceid`)
)
ENGINE=FEDERATED
DEFAULT CHARSET=utf8
COLLATE utf8_general_ci
CONNECTION='mysql://foo:bar@192.168.209.13:3306/foo/rmt';
_________________________________________________________________