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

ConnectSE: table_type=mysql does not send the WHERE part -> full table scan

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 10.0.3
    • 10.0.5
    • None
    • Ubuntu/Precise/LXContainer

    Description

      ConnectSE does not send the WHERE part of an statement in opposite to the docs (https://kb.askmonty.org/en/connect-table-types-mysql-table-type-accessing-mysqlmariadb-tables/) to the remote server.
      Given:

      CREATE TABLE `federatedconnect` (
      		 
        `id` int(11) NOT NULL,
      		 
        `id2` int(11) DEFAULT NULL
      		 
      ) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='mysql' `TABNAME`='aha' `DBNAME`='test' `OPTION_LIST`='user=me,host=10.0.3.191'

      Regarding to the docs select * from federatedconnect where id=1 should be sent as: SELECT id,id2 FROM aha WHERE id=1 to the remote server.
      Using ngrep I see only: SELECT `id`, `id2` FROM `aha` So all rows are send instead of only one.
      The table aha has about 1000 rows. So even another 'proof'.

      MariaDB [test]> show status like 'handler_read_rnd_next'; select * from federate_classic where id=1;  show status like 'handler_read_rnd_next';
      		 
      +-----------------------+-------+
      		 
      | Variable_name         | Value |
      		 
      +-----------------------+-------+
      		 
      | Handler_read_rnd_next | 7347  |
      		 
      +-----------------------+-------+
      		 
      1 row in set (0.00 sec)
      		 
       
      		 
      +------+------+
      		 
      | id   | id2  |
      		 
      +------+------+
      		 
      |    1 |    4 |
      		 
      +------+------+
      		 
      1 row in set (0.00 sec)
      		 
       
      		 
      +-----------------------+-------+
      		 
      | Variable_name         | Value |
      		 
      +-----------------------+-------+
      		 
      | Handler_read_rnd_next | 8356  |
      		 
      +-----------------------+-------+
      		 
      1 row in set (0.00 sec)
      		 
       

      So we see all rows are send.

      Regads
      Erkan

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          bertrandop Olivier Bertrand added a comment -

          To get the where clause, CONNECT must be called from MariaDB to do so. This depends on the condition
          --engine_condition_pushdown=on
          Be sure it is ON because it is sometimes OFF by default.

          bertrandop Olivier Bertrand added a comment - To get the where clause, CONNECT must be called from MariaDB to do so. This depends on the condition --engine_condition_pushdown=on Be sure it is ON because it is sometimes OFF by default.
          bertrandop Olivier Bertrand added a comment -

          mysqld must be started with:
          --engine_condition_pushdown=on
          If it is OFF by default, CONNECT cannot get the where clause.

          bertrandop Olivier Bertrand added a comment - mysqld must be started with: --engine_condition_pushdown=on If it is OFF by default, CONNECT cannot get the where clause.
          erkules erkan yanar added a comment -

          Great!
          thx
          erkan

          #v+
          MariaDB [test]> show status like 'handler_read_rnd_next'; select * from federatedconnect where id=1; show status like 'handler_read_rnd_next';
          ----------------------------+

          Variable_name Value

          ----------------------------+

          Handler_read_rnd_next 74

          ----------------------------+
          1 row in set (0.00 sec)

          --------+

          id id2

          --------+

          1 4

          --------+
          1 row in set (0.00 sec)

          ----------------------------+

          Variable_name Value

          ----------------------------+

          Handler_read_rnd_next 76

          ----------------------------+
          1 row in set (0.00 sec)
          #v-

          Thx

          erkules erkan yanar added a comment - Great! thx erkan #v+ MariaDB [test] > show status like 'handler_read_rnd_next'; select * from federatedconnect where id=1; show status like 'handler_read_rnd_next'; ---------------------- ------+ Variable_name Value ---------------------- ------+ Handler_read_rnd_next 74 ---------------------- ------+ 1 row in set (0.00 sec) --- -----+ id id2 --- -----+ 1 4 --- -----+ 1 row in set (0.00 sec) ---------------------- ------+ Variable_name Value ---------------------- ------+ Handler_read_rnd_next 76 ---------------------- ------+ 1 row in set (0.00 sec) #v- Thx

          People

            bertrandop Olivier Bertrand
            erkules erkan yanar
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.