MySQL Bugs: #17377: Federated Engine returns wrong Data, always the rows with the highest ID

Bug #17377	Federated Engine returns wrong Data, always the rows with the highest ID
Submitted:	14 Feb 2006 14:55	Modified:	8 Mar 2006 16:28
Reporter:	sebastian mork	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.19-BK, 5.0.18-max	OS:	Linux (Linux, Windows XP SP2)
Assigned to:	Magnus Blåudd	CPU Architecture:	Any

Description:
One Table in a mySql-Database that is on a remote server should be accessed by a local running mySql5-Server using the federated storage engine.
So the data is stored on the remote machine and on the 'local' server is the same table created with engine=federated.

Running a query on the local server should receive the data from the remote machine.

What is going wrong:
When sorting the returned query by the field 'fld_name' the result of the query is wrong: every record has the same values. Every time sorting a query result each row returned has the value from the record with the biggest primary ID.

e.g. I want 4 records from tbl_catsde like this:
1, 'Name 0.81864234229423', 0, 0
2, 'Name 0.67525953955507', 0, 0
3, 'Name 0.92037070162629', 0, 0
4, 'Name 0.57607492019988', 0, 0

what I get is this:
56, 'Name 0.95604498028387', 0, 0
56, 'Name 0.95604498028387', 0, 0
56, 'Name 0.95604498028387', 0, 0
56, 'Name 0.95604498028387', 0, 0
All records have the same values (its always the last-id-record, if query returns 1 record, the last is returned one time, if query returns 100 records, it is returned 100times)
(query: SELECT * FROM testlocal.tbl_catsde where fld_delt=0 order by fld_name limit 4)

That occurs only on the local server where the federated engine is used to catch the data from the remote server, the query executed directly on the remote-server returns ok.
(ordering by rand() returns also valid rows from the local machine, but not by name ordered) 

How to repeat:
Here's the way I've created the databases and tables..
--------------------------------------------------------

###on the remote machine where the data is physically stored: (redhat9, mySql 5.0.18 withoud federated engine support)

###Create the database:
CREATE DATABASE `testremote` DEFAULT CHARACTER SET utf8;

###Create the table:
CREATE TABLE `testremote`.`tbl_catsde` (
`fld_cid` bigint(20) NOT NULL auto_increment,
`fld_name` varchar(255) NOT NULL default '',
`fld_parentid` bigint(20) NOT NULL default '0',
`fld_delt` int(1) NOT NULL default '0',
PRIMARY KEY (`fld_cid`),
KEY `fld_parentid` (`fld_parentid`),
KEY `fld_delt` (`fld_delt`),
KEY `fld_cid` (`fld_cid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

### Insert some test-data:
insert into `testremote`.`tbl_catsde`( fld_name )
values
(concat('Name',' ',rand())),
(concat('Name',' ',rand())),
(concat('Name',' ',rand())),
(concat('Name',' ',rand())),
(concat('Name',' ',rand())),
(concat('Name',' ',rand())),
(concat('Name',' ',rand())),
(concat('Name',' ',rand())),
(concat('Name',' ',rand())),
(concat('Name',' ',rand())),
(concat('Name',' ',rand())),
(concat('Name',' ',rand())),
(concat('Name',' ',rand())),
(concat('Name',' ',rand())),
(concat('Name',' ',rand())),
(concat('Name',' ',rand())),
(concat('Name',' ',rand())),
(concat('Name',' ',rand())),
(concat('Name',' ',rand())),
(concat('Name',' ',rand())),
(concat('Name',' ',rand())),
(concat('Name',' ',rand())),
(concat('Name',' ',rand())),
(concat('Name',' ',rand())),
(concat('Name',' ',rand())),
(concat('Name',' ',rand())),
(concat('Name',' ',rand())),
(concat('Name',' ',rand()));

##at the local machine with the federated table: (running xpSp2, mySql 5.0.18-max)

###Create the database:
CREATE DATABASE `testlocal` DEFAULT CHARACTER SET utf8;

###create the table: (CHANGE IP + USER/PASS)
CREATE TABLE `testlocal`.`tbl_catsde` (
`fld_cid` bigint(20) NOT NULL auto_increment,
`fld_name` varchar(255) NOT NULL default '',
`fld_parentid` bigint(20) NOT NULL default '0',
`fld_delt` int(1) NOT NULL default '0',
PRIMARY KEY (`fld_cid`),
KEY `fld_parentid` (`fld_parentid`),
KEY `fld_delt` (`fld_delt`),
KEY `fld_cid` (`fld_cid`)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://user:pass@192.168.0.105:3306/testremote/tbl_catsde';

--------------------------------------------------------

Now, running the following query on the 'local' machine on the federated table should normally show all records (asc.)sorted by fld_name:
###
select * from tbl_catsde where fld_parentid=0 and fld_delt=0 order by fld_name

But: I always (when sorting by name) get 56 Rows, each looking same:
56, 'Name 0.95604498028387', 0, 0
56, 'Name 0.95604498028387', 0, 0
56, 'Name 0.95604498028387', 0, 0
and so on

THe record with the ID 56 is the last record.
When sorting by rand() the result is correct (random sorted, no double entry is shown)

I also tried mySql 3.x + 4.x the remote-machine.

Suggested fix:
?

Verified just as described with 5.0.18-nt as a data source and 5.0.19-BK (ChangeSet@1.2062, 2006-02-20 14:26:32+04:00) on Linux as a server for FEDERATED table (target).

On the source server:

C:\Documents and Settings\openxs>mysql -uroot -p -P3307 test
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 35 to server version: 5.0.18-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE DATABASE `testremote` DEFAULT CHARACTER SET utf8;
Query OK, 1 row affected (0.01 sec)

mysql> CREATE TABLE `testremote`.`tbl_catsde` (
    -> `fld_cid` bigint(20) NOT NULL auto_increment,
    -> `fld_name` varchar(255) NOT NULL default '',
    -> `fld_parentid` bigint(20) NOT NULL default '0',
    -> `fld_delt` int(1) NOT NULL default '0',
    -> PRIMARY KEY (`fld_cid`),
    -> KEY `fld_parentid` (`fld_parentid`),
    -> KEY `fld_delt` (`fld_delt`),
    -> KEY `fld_cid` (`fld_cid`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into `testremote`.`tbl_catsde`( fld_name )
    -> values
    -> (concat('Name',' ',rand())),
    -> (concat('Name',' ',rand())),
    -> (concat('Name',' ',rand())),
    -> (concat('Name',' ',rand())),
    -> (concat('Name',' ',rand())),
    -> (concat('Name',' ',rand())),
    -> (concat('Name',' ',rand())),
    -> (concat('Name',' ',rand())),
    -> (concat('Name',' ',rand())),
    -> (concat('Name',' ',rand())),
    -> (concat('Name',' ',rand())),
    -> (concat('Name',' ',rand())),
    -> (concat('Name',' ',rand())),
    -> (concat('Name',' ',rand())),
    -> (concat('Name',' ',rand())),
    -> (concat('Name',' ',rand())),
    -> (concat('Name',' ',rand())),
    -> (concat('Name',' ',rand())),
    -> (concat('Name',' ',rand())),
    -> (concat('Name',' ',rand())),
    -> (concat('Name',' ',rand())),
    -> (concat('Name',' ',rand())),
    -> (concat('Name',' ',rand())),
    -> (concat('Name',' ',rand())),
    -> (concat('Name',' ',rand())),
    -> (concat('Name',' ',rand())),
    -> (concat('Name',' ',rand())),
    -> (concat('Name',' ',rand()));
Query OK, 28 rows affected (0.04 sec)
Records: 28  Duplicates: 0  Warnings: 0

mysql> select * from testremote.tbl_catsde where fld_parentid=0 and fld_delt=0 order by fld_name;
+---------+-------------------------+--------------+----------+
| fld_cid | fld_name                | fld_parentid | fld_delt |
+---------+-------------------------+--------------+----------+
|      15 | Name 0.0052685653839899 |            0 |        0 |
|       2 | Name 0.012904051703312  |            0 |        0 |
|      27 | Name 0.028746158842692  |            0 |        0 |
|      25 | Name 0.04672655653835   |            0 |        0 |
|      18 | Name 0.11318268544337   |            0 |        0 |
|       6 | Name 0.17967011237486   |            0 |        0 |
|       7 | Name 0.18357742036095   |            0 |        0 |
|      16 | Name 0.19055504462733   |            0 |        0 |
|       4 | Name 0.22609506847905   |            0 |        0 |
|      22 | Name 0.25539817684833   |            0 |        0 |
|       9 | Name 0.34365174671975   |            0 |        0 |
|       8 | Name 0.37887679541379   |            0 |        0 |
|      26 | Name 0.43378515954482   |            0 |        0 |
|      20 | Name 0.43547589838083   |            0 |        0 |
|      23 | Name 0.53989541022097   |            0 |        0 |
|       5 | Name 0.57159105741567   |            0 |        0 |
|      13 | Name 0.5736814956867    |            0 |        0 |
|      10 | Name 0.58162837809103   |            0 |        0 |
|      12 | Name 0.64104830160835   |            0 |        0 |
|       1 | Name 0.73718628355971   |            0 |        0 |
|      19 | Name 0.75500478479546   |            0 |        0 |
|      28 | Name 0.84237534631265   |            0 |        0 |
|       3 | Name 0.85296143857107   |            0 |        0 |
|      11 | Name 0.87718668102956   |            0 |        0 |
|      21 | Name 0.91236516825144   |            0 |        0 |
|      24 | Name 0.93328255129352   |            0 |        0 |
|      17 | Name 0.93696955771835   |            0 |        0 |
|      14 | Name 0.94526260434209   |            0 |        0 |
+---------+-------------------------+--------------+----------+
28 rows in set (0.02 sec)

mysql> grant all on test.* to root@`%`;
Query OK, 0 rows affected (0.00 sec)

On the target:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.19

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `tbl_catsde` ( `fld_cid` bigint(20) NOT NULL auto_increment, `fld_name` varchar(255) NOT NULL default '', `fld_parentid` bigint(20) NOT NULL default '0', `fld_delt` int(1) NOT NULL default '0', PRIMARY KEY (`fld_cid`), KEY `fld_parentid` (`fld_parentid`), KEY `fld_delt` (`fld_delt`), KEY `fld_cid` (`fld_cid`) ) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://root:root@192.168.0.1:3307/testremote/tbl_catsde';
Query OK, 0 rows affected (0.03 sec)

mysql> select * from tbl_catsde where fld_parentid=0 and fld_delt=0 order by fld_name;
+---------+-----------------------+--------------+----------+
| fld_cid | fld_name              | fld_parentid | fld_delt |
+---------+-----------------------+--------------+----------+
|      28 | Name 0.84237534631265 |            0 |        0 |
|      28 | Name 0.84237534631265 |            0 |        0 |
|      28 | Name 0.84237534631265 |            0 |        0 |
|      28 | Name 0.84237534631265 |            0 |        0 |
|      28 | Name 0.84237534631265 |            0 |        0 |
|      28 | Name 0.84237534631265 |            0 |        0 |
|      28 | Name 0.84237534631265 |            0 |        0 |
|      28 | Name 0.84237534631265 |            0 |        0 |
|      28 | Name 0.84237534631265 |            0 |        0 |
|      28 | Name 0.84237534631265 |            0 |        0 |
|      28 | Name 0.84237534631265 |            0 |        0 |
|      28 | Name 0.84237534631265 |            0 |        0 |
|      28 | Name 0.84237534631265 |            0 |        0 |
|      28 | Name 0.84237534631265 |            0 |        0 |
|      28 | Name 0.84237534631265 |            0 |        0 |
|      28 | Name 0.84237534631265 |            0 |        0 |
|      28 | Name 0.84237534631265 |            0 |        0 |
|      28 | Name 0.84237534631265 |            0 |        0 |
|      28 | Name 0.84237534631265 |            0 |        0 |
|      28 | Name 0.84237534631265 |            0 |        0 |
|      28 | Name 0.84237534631265 |            0 |        0 |
|      28 | Name 0.84237534631265 |            0 |        0 |
|      28 | Name 0.84237534631265 |            0 |        0 |
|      28 | Name 0.84237534631265 |            0 |        0 |
|      28 | Name 0.84237534631265 |            0 |        0 |
|      28 | Name 0.84237534631265 |            0 |        0 |
|      28 | Name 0.84237534631265 |            0 |        0 |
|      28 | Name 0.84237534631265 |            0 |        0 |
+---------+-----------------------+--------------+----------+
28 rows in set (0.03 sec)

Works OK without ORDER BY.

Test case for mysql-test-run:

source include/federated.inc;

connection slave;
--disable_warnings
DROP TABLE IF EXISTS federated.bug_17377_table;
--enable_warnings

CREATE TABLE federated.bug_17377_table (
`fld_cid` bigint(20) NOT NULL auto_increment,
`fld_name` varchar(255) NOT NULL default '',
`fld_parentid` bigint(20) NOT NULL default '0',
`fld_delt` int(1) NOT NULL default '0',
PRIMARY KEY (`fld_cid`),
KEY `fld_parentid` (`fld_parentid`),
KEY `fld_delt` (`fld_delt`),
KEY `fld_cid` (`fld_cid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

# Insert some test-data
insert into federated.bug_17377_table( fld_name )
values
("Magnus"), ("Bertil"), ("Timo"), ("Vera"), ("Ronja");

connection master;
--disable_warnings
DROP TABLE IF EXISTS federated.t1;
--enable_warnings

--replace_result $SLAVE_MYPORT SLAVE_PORT
eval CREATE TABLE federated.t1 (
`fld_cid` bigint(20) NOT NULL auto_increment,
`fld_name` varchar(255) NOT NULL default '',
`fld_parentid` bigint(20) NOT NULL default '0',
`fld_delt` int(1) NOT NULL default '0',
PRIMARY KEY (`fld_cid`),
KEY `fld_parentid` (`fld_parentid`),
KEY `fld_delt` (`fld_delt`),
KEY `fld_cid` (`fld_cid`)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8
    CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/bug_17377_table';

select * from federated.t1 where fld_parentid=0 and fld_delt=0
order by fld_name;

#select * from federated.t1 where fld_parentid=0 and fld_delt=0;

DROP TABLE federated.t1;
connection slave;
DROP TABLE federated.bug_17377_table;

source include/federated_cleanup.inc;

rnd_pos is called to retrieve the records in the desired order, but since the "scan_flag" is not set, it will not advance to the correct record in rnd_pos. If the "if (scan_flag)" is commented out, this testcase will work. Will have too look more too see if it's rnd_init that shouold be called with scan=1 or if there need to be something else in the "if (scan_flag)" statement.

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/commits/3228

Pushed a patch to 5.0.20 and 5.1.8 that makes " SELECT" with "ORDER BY" to return the correct rows  - in order.

Noted in 5.0.20, 5.1.8 changelogs.

For <literal>FEDERATED</literal> tables, a 
<literal>SELECT</literal> statement with an <literal>ORDER
BY</literal> clause did not return rows in the proper order.
(Bug #17377)