MySQL Bugs: #9925: Federated query processor (step 2 or step 3) not using logic in WHERE statement

Bug #9925	Federated query processor (step 2 or step 3) not using logic in WHERE statement
Submitted:	15 Apr 2005 10:45	Modified:	18 Jul 2005 19:50
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.3-beta	OS:	Linux (linux)
Assigned to:	Bugs System	CPU Architecture:	Any

Description:
Federated tables aren't passing the proper sql query to the remote database with WHERE statements that have more than one conditional.  

How to repeat:
From a properly setup master table:
CREATE TABLE `client` (
  `location_id` int(11) NOT NULL default '0',
  `client_id` int(11) NOT NULL default '0',
...more rows here...
 sPRIMARY KEY  (`location_id`,`client_id`),
) engine=Innodb;

select count(*) from client;
+----------+
| count(*) |
+----------+
|    22269 |
+----------+
1 row in set (0.12 sec)

mysql> select count(*) from client where location_id=63 && client_id=88888;;
+----------+
| count(*) |
+----------+
|        1    |
+----------+
1 row in set (0.00 sec)

With a properly setup federated table:

CREATE TABLE `client` (
  `location_id` int(11) NOT NULL default '0',
  `client_id` int(11) NOT NULL default '0',
...more rows here...
 PRIMARY KEY  (`location_id`,`client_id`),
)  ) ENGINE=FEDERATED
  COMMENT="mysql://u:pw@server:3306/mmg/client";

mysql> select count(*) from client;
+----------+
| count(*) |
+----------+
|    22269 |
+----------+
1 row in set (0.87 sec)

mysql> select count(*) from client where location_id=63 && client_id=88888;
+----------+
| count(*) |
+----------+
|        0    |
+----------+
1 row in set (2.06 sec)

This same incorrect result happens if it is changed to 'select * from ...".  Main InnoDB table returned 1 record while federated table returned 0 records

--Query log on the master server shows: 
050415  6:42:25     133 Query       SELECT * FROM `client` WHERE `location_id` = 63

 

Suggested fix:
pass full where clause to remote server

Thanks for your bug report.  Verified w/5.0.4 and 30,000 records

There's something definitely here to fix, in that even though I try to create a WHERE clause based off of a key, even if that key is a multi-column key, the create_where_from_key isn't constructing enough of a WHERE clause containing _both_ columns, only the column of the first part of the key. This is testing with the same exact tables as described in this bug report with 100000 records.

Please see worklog #2653, this will fix this issue once complete 
https://intranet.mysql.com/worklog/Server-Sprint/index.pl?tid=2653

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/25896

Changeset 1.1936 includes fixes that make indexes work correctly. I've also including notes in comments about oddities in range->flag that I'd like to discuss with someone. The only type of index I can find problems with is the blob type, when using a limitted number of chars of a blob for an index.

Still working with Timour and Anthony on perfecting the index/range code. Antony has a patch that I am currently testing, as well, I'm working on some changes that Timour suggested. It may turn out that Antony's solution may be the course to take.

fix pushed as of 17.07.05, reviewed by JimW and Brian