Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-9966

Remote indexing error with the FEDERATED(X) and CONNECT engines

    XMLWordPrintable

    Details

      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.

       

        Attachments

          Activity

            People

            Assignee:
            bertrandop Olivier Bertrand
            Reporter:
            bertrandop Olivier Bertrand
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: