[MDEV-4854] ConnectSE: table_type=mysql does not send the WHERE part -> full table scan Created: 2013-08-08  Updated: 2013-08-09  Resolved: 2013-08-08

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.3
Fix Version/s: 10.0.5

Type: Bug Priority: Major
Reporter: erkan yanar Assignee: Olivier Bertrand
Resolution: Not a Bug Votes: 0
Labels: connect-engine
Environment:

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

 Comments   
Comment by Olivier Bertrand [ 2013-08-08 ]

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.

Comment by Olivier Bertrand [ 2013-08-08 ]

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

Comment by erkan yanar [ 2013-08-09 ]

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

Generated at Thu Feb 08 06:59:42 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.