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

        1. smp.csv
          101 kB
          Olivier Bertrand

        Activity

          People

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

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.