[MDEV-9966] Remote indexing error with the FEDERATED(X) and CONNECT engines Created: 2016-04-21  Updated: 2016-04-24  Resolved: 2016-04-21

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Connect, Storage Engine - Federated
Affects Version/s: 10.1.12, 5.5, 10.0, 10.1
Fix Version/s: 10.0.25, 10.1.14

Type: Bug Priority: Major
Reporter: Olivier Bertrand Assignee: Olivier Bertrand
Resolution: Fixed Votes: 0
Labels: None

Attachments: Microsoft Word smp.csv    

 Description    

CREATE TABLE t1 (
 
  matricule int(11) NOT NULL,
 
  nom varchar(16) NOT NULL,
 
  prenom varchar(20) NOT NULL,
 
  PRIMARY KEY (matricule),
 
  KEY NP (nom,prenom)
 
) ENGINE=MyISAM;

T1 is filled with 4545 rows containing various id's, names and surnames.
When issuing the query:

select * from t1 where nom between 'HELEN' and 'HEROS' and prenom = 'PHILIPPE';

The result is:

matricule nom prenom
9096 HELENA PHILIPPE
7626 HENIN PHILIPPE
403 HERMITTE PHILIPPE

Now accessing the same table via FEDERATED:

CREATE TABLE tfed 
ENGINE=FEDERATED connection='mysql://root@host_name/test/t1';

0 lines are retrieved from the query:

select * from tfed where nom between 'HELEN' and 'HEROS' and prenom = 'PHILIPPE';

The bug occurs in create_where_from_key that construct the query:

SELECT `matricule`, `nom`, `prenom` FROM `myemp2` WHERE  ( (`nom` >= 'HELEN')  AND  (`prenom` >= 'PHILIPPE') ) AND ( (`nom` >= 'HEROS')  AND  (`prenom` <= 'PHILIPPE') );

The above query is wrong but should anyway return all the 'PHILIPPE' for names above 'HEROS'.
Using the CONNECT engine the constructed query is made in MakeKeyWhere. It is different but also wrong:

SELECT matricule, nom, prenom FROM dbemp WHERE (nom >= 'HELEN' AND prenom >= 'PHILIPPE') AND (nom >= 'HEROS' AND prenom <= 'PHILIPPE')

Also logically returning no rows.
Because FEDERATED and CONNECT use a different construction program, I can take care of the problem in CONNECT but not in FEDERATED.

 



 Comments   
Comment by Olivier Bertrand [ 2016-04-21 ]

I have attached the smp.csv file that can be used to populate the t1 table.

Comment by Elena Stepanova [ 2016-04-21 ]

Actually, Federated seems to be fine, FederatedX has the problem.

bertrandop, do you prefer to have the FederatedX part extracted into a separate issue, or do you want to fix the Connect part first, and then reassign it to whoever will take care of the Federated part.

Comment by Olivier Bertrand [ 2016-04-21 ]

Yes, a separate issue would be fine. As a matter of fact, the way CONNECT handle this was inspired by what FEDERATED did and should have worked, except for a trivial error that I could fix. Therefore the CONNECT bug is fixed but not the FEDERATEDX one.

Comment by Olivier Bertrand [ 2016-04-21 ]

Finally, don't make them as separate issues. Looking in the FEDERATEDX code, I found the same error and I have also fixed it.

As a matter of fact, I am wondering if this error were also initially in the FEDERATED code that we both copied and that it was later fixed in FEDERATED but not in FEDERATEDX?

Generated at Thu Feb 08 07:38:40 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.