Bug #20881 Bad recorders returned when Query with federated tables
Submitted: 6 Jul 2006 3:19 Modified: 7 Aug 2006 6:53
Reporter: ming lu Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Federated storage engine Severity:S2 (Serious)
Version:5.1.11 OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[6 Jul 2006 3:19] ming lu
Description:
With Release 5.1.12 beta. Federated Engine not works well.

Bad recorders returned when Query with conditions of two federated tables .

the same test scripts had been test on 5.0.18, and no faults with 5.0.18 .

How to repeat:
--step 1 
--create the source database `sdb`
-- and create 3 tables (a, b, c) and put some recorders into them.

drop database sdb;
create database sdb;
use sdb;
CREATE TABLE `a` (
  `id_a` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id_a`)
);

CREATE TABLE `b` (
  `id_b` int(11) NOT NULL DEFAULT '0',
  `id_a` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id_b`)
);

CREATE TABLE `c` (
  `id_c` int(11) NOT NULL DEFAULT '0',
  `c` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id_c`),
  KEY `tablec_c` (`c`)
) ENGINE=innodb ROW_FORMAT=DYNAMIC ;

insert a values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
insert b values(1,101,1),(2,101,1),(3,101,1),(4,102,2),(5,102,2),(6,102,2),(7,103,3),(8,103,3),(9,103,3);
insert c values(1,1),(2,1),(3,1),(4,2),(5,2),(6,3),(7,3),(8,3);

--step 2 
--create the another database `fdb`
-- and create 2 federated tables for `sdb`.a `sdb`.b
-- create table c same as `sdb`.c and put the same recorders into it.
drop database fdb;
create database fdb;
use fdb;
CREATE TABLE `a` (
  `id_a` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id_a`)
) ENGINE=FEDERATED CONNECTION='mysql://root:1111@127.0.0.1:3306/sdb/a' ;

CREATE TABLE `b` (
  `id_b` int(11) NOT NULL DEFAULT '0',
  `id_a` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id_b`)
) ENGINE=FEDERATED  CONNECTION='mysql://root:1111@127.0.0.1/sdb/b' ;

CREATE TABLE `c` (
  `id_c` int(11) NOT NULL DEFAULT '0',
  `c` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id_c`),
  KEY `tablec_c` (`c`)
) ENGINE=innodb ROW_FORMAT=DYNAMIC ;

insert c values(1,1),(2,1),(3,1),(4,2),(5,2),(6,3),(7,3),(8,3);

--step 3 
-- do a query with following sql on `sdb`
-- the result should be empty, and it is.

use sdb
select a.id_a, b_alias.id_a, c.c, b_alias.c
from a,c,
     (select id_a,c from b group by id_a) b_alias
where a.id_a=b_alias.id_a 
      and
      c.c=b_alias.c
      ;

--Empty set (0.13 sec)
      
--step 4
-- do the same query as step 3 on `fdb`
-- the result should be same as `sdb`, 
-- the real results is:
-- +------+------+---+------+
-- | id_a | id_a | c | c    |
-- +------+------+---+------+
-- | 0    | 101  | 1 | 1    |
-- | 0    | 101  | 1 | 1    |
-- | 0    | 101  | 1 | 1    |
-- | 0    | 102  | 2 | 2    |
-- | 0    | 102  | 2 | 2    |
-- | 0    | 103  | 3 | 3    |
-- | 0    | 103  | 3 | 3    |
-- | 0    | 103  | 3 | 3    |
-- +------+------+---+------+
-- 8 rows in set (0.11 sec)
--

use fdb
select a.id_a, b_alias.id_a, c.c, b_alias.c
from a,c,
     (select id_a,c from b group by id_a) b_alias
where a.id_a=b_alias.id_a 
      and
      c.c=b_alias.c
      ;

Suggested fix:
no suggestion.
[24 Jul 2006 16:01] Valeriy Kravchuk
Thank you for a problem report. I was not able to repeat the behaviour described with 5.1.12-BK on Linux (ChangeSet@1.2264, 2006-07-24 09:38:42+10:00, stewart@willster.(none) +1 -0):

openxs@suse:~/dbs/5.1> bin/mysql -uroot test --socket=/tmp/mysql51.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.1.12-beta-debug

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

mysql> CREATE TABLE `b` (
    ->   `id_b` int(11) NOT NULL DEFAULT '0',
    ->   `id_a` int(11) DEFAULT NULL,
    ->   `c` int(11) DEFAULT NULL,
    ->   PRIMARY KEY (`id_b`)
    -> ) ENGINE=FEDERATED  CONNECTION='mysql://root@127.0.0.1/test/b';
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE `a` (
    ->   `id_a` int(11) NOT NULL DEFAULT '0',
    ->   PRIMARY KEY (`id_a`)
    -> ) ENGINE=FEDERATED CONNECTION='mysql://root@127.0.0.1/test/a';
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE `c` (
    ->   `id_c` int(11) NOT NULL DEFAULT '0',
    ->   `c` int(11) NOT NULL DEFAULT '0',
    ->   PRIMARY KEY (`id_c`),
    ->   KEY `tablec_c` (`c`)
    -> ) ENGINE=innodb ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.03 sec)

mysql> insert c values(1,1),(2,1),(3,1),(4,2),(5,2),(6,3),(7,3),(8,3);
Query OK, 8 rows affected (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> exit
Bye
openxs@suse:~/dbs/5.1> 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 4 to server version: 4.1.22

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

mysql> select a.id_a, b_alias.id_a, c.c, b_alias.c
    -> from a,c,
    ->      (select id_a,c from b group by id_a) b_alias
    -> where a.id_a=b_alias.id_a
    ->       and
    ->       c.c=b_alias.c
    ->       ;
Empty set (0.02 sec)

mysql> exit
Bye
openxs@suse:~/dbs/5.1> bin/mysql -uroot test --socket=/tmp/mysql51.sock
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 3 to server version: 5.1.12-beta-debug

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

mysql> select a.id_a, b_alias.id_a, c.c, b_alias.c from a,c,      (select id_a,
c from b group by id_a) b_alias where a.id_a=b_alias.id_a        and       c.c=
b_alias.c;
Empty set (0.02 sec)

So, results are the same. Please, try to repeat with a newer sources/nightly build, or just wait for 5.1.12 to be released officially.
[7 Aug 2006 6:53] ming lu
Sorry, maybe i make a mistake about the Server version, I think i do the test against to the mysql server 5.1.11 instead of 5.1.12 actually. Do you sure that the problem isn't exist in the version 5.1.11? 
By the way, when will the mysql server 5.1.12 be released ?