MySQL Bugs: #64460: Federated returns multiple records, for same record, for every "or" operator

Bug #64460	Federated returns multiple records, for same record, for every "or" operator
Submitted:	27 Feb 2012 5:06	Modified:	26 May 2012 17:14
Reporter:	Desmond Coertzen	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Federated storage engine	Severity:	S1 (Critical)
Version:	5.1.59	OS:	Linux (open suse 11.2)
Assigned to:		CPU Architecture:	Any

Description:
"or" operator in where-clause or join-clause returns two rows for the same record that matches the two conditions, separated with "or", in the where-clause. Only one record exists. The record is listed twice in the dataset, with matching primary key.

How to repeat:
Step 1: create federated table
------------------------------

fed_jhb_pay_StaffLoans	CREATE TABLE `fed_jhb_pay_StaffLoans` (
  `LKey` int(11) NOT NULL,
  `StaffKey` int(11) NOT NULL DEFAULT '0',
  `LoanAmount` double(8,2) DEFAULT NULL,
  `DateOfLoan` char(11) DEFAULT NULL,
  `Description` char(128) DEFAULT NULL,
  `Repayment` double(8,2) DEFAULT NULL,
  `BeneficiaryKey` int(11) NOT NULL DEFAULT '0',
  `LoanPaid` int(2) NOT NULL DEFAULT '0',
  `Type` char(20) DEFAULT NULL,
  `MicroKey` int(12) DEFAULT '0',
  `Interest` double(11,2) DEFAULT '0.00',
  `IniFee` double(11,2) DEFAULT '0.00',
  `ServiceFee` double(11,2) DEFAULT '0.00',
  `Monthly` int(1) DEFAULT '0',
  `ActivationDate` char(11) DEFAULT NULL,
  `SpecRepay` double(8,2) DEFAULT NULL,
  `SpecPreiod` int(2) DEFAULT NULL,
  `Dormant` int(1) DEFAULT '0',
  `Insurance` double(8,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`LKey`),
  KEY `StaffKey` (`StaffKey`),
  KEY `BeneficiaryKey` (`BeneficiaryKey`),
  KEY `LoanPaid` (`LoanPaid`),
  KEY `MicroKey` (`MicroKey`),
  KEY `indx_psl_type` (`Type`)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://user@host/JhbGlobal/pay_StaffLoans';

Step 2: Run query directly on remote database
---------------------------------------------

mysql> select * from pay_StaffLoans where MicroKey = 143775 or LKey = 157617;
+--------+----------+------------+------------+------------------+-----------+----------------+----------+------------------+----------+----------+--------+------------+---------+----------------+-----------+------------+---------+-----------+
| LKey   | StaffKey | LoanAmount | DateOfLoan | Description      | Repayment | BeneficiaryKey | LoanPaid | Type             | MicroKey | Interest | IniFee | ServiceFee | Monthly | ActivationDate | SpecRepay | SpecPreiod | Dormant | Insurance |
+--------+----------+------------+------------+------------------+-----------+----------------+----------+------------------+----------+----------+--------+------------+---------+----------------+-----------+------------+---------+-----------+
| 157617 |   114633 |     220.00 | 24/02/2012 | Babereki Airtime |    220.00 |              0 |        0 | Babereki Airtime |   143775 |     0.00 |   0.00 |       0.00 |       1 | 25/01/2012     |      0.00 |          0 |       0 |      0.00 |
+--------+----------+------------+------------+------------------+-----------+----------------+----------+------------------+----------+----------+--------+------------+---------+----------------+-----------+------------+---------+-----------+
1 row in set (0.00 sec)

Step 3: Run same query on local database using federated table
--------------------------------------------------------------

mysql> select * from fed_jhb_pay_StaffLoans where MicroKey = 143775 or LKey = 157617;
+--------+----------+------------+------------+------------------+-----------+----------------+----------+------------------+----------+----------+--------+------------+---------+----------------+-----------+------------+---------+-----------+
| LKey   | StaffKey | LoanAmount | DateOfLoan | Description      | Repayment | BeneficiaryKey | LoanPaid | Type             | MicroKey | Interest | IniFee | ServiceFee | Monthly | ActivationDate | SpecRepay | SpecPreiod | Dormant | Insurance |
+--------+----------+------------+------------+------------------+-----------+----------------+----------+------------------+----------+----------+--------+------------+---------+----------------+-----------+------------+---------+-----------+
| 157617 |   114633 |     220.00 | 24/02/2012 | Babereki Airtime |    220.00 |              0 |        0 | Babereki Airtime |   143775 |     0.00 |   0.00 |       0.00 |       1 | 25/01/2012     |      0.00 |          0 |       0 |      0.00 |
| 157617 |   114633 |     220.00 | 24/02/2012 | Babereki Airtime |    220.00 |              0 |        0 | Babereki Airtime |   143775 |     0.00 |   0.00 |       0.00 |       1 | 25/01/2012     |      0.00 |          0 |       0 |      0.00 |
+--------+----------+------------+------------+------------------+-----------+----------------+----------+------------------+----------+----------+--------+------------+---------+----------------+-----------+------------+---------+-----------+
2 rows in set (2.41 sec)

It would be nice to check if the same problem still happens with a newer version, 5.1.61. If you can not check this, please, send a dump of data for the table that demonstrates the problem described.

The two fixes to federated mentioned in release notes for 5.1.60 does not sound like it will fix this problem. I did not see anything regarding federated in the release notes for 5.1.61.

I will do a test anyway.

Performed two tests:

Upgraded test system from 5.1.59 to 5.1.61

Test 1:
-------
Federated table on test server 5.1.61 to real table on live server with 5.1.59
Problem still persists

Test 2:
-------
Federated table on test server 5.1.61 to real table on another live server with 5.1.61
Problem still persists

NOTE:

On another test (5.1.61 -> 5.1.61) where a duplicate record really existed between LKey and MicroKey, only the real records were returned, not a set of duplicates:

mysql> select * from fed_dbn_pay_StaffLoans where MicroKey = 27 or LKey = 68690;
+-------+----------+------------+------------+-------------+-----------+----------------+----------+-------------+----------+----------+--------+------------+---------+----------------+-----------+------------+---------+-----------+
| LKey  | StaffKey | LoanAmount | DateOfLoan | Description | Repayment | BeneficiaryKey | LoanPaid | Type        | MicroKey | Interest | IniFee | ServiceFee | Monthly | ActivationDate | SpecRepay | SpecPreiod | Dormant | Insurance |
+-------+----------+------------+------------+-------------+-----------+----------------+----------+-------------+----------+----------+--------+------------+---------+----------------+-----------+------------+---------+-----------+
| 27887 |    68690 |    1466.86 | 17/07/2007 | BAB LN      |    122.24 |              0 |        1 | Baberki Lns |       27 |   124.86 | 171.00 |     171.00 |       0 | NULL           |      NULL |       NULL |       0 |      0.00 |
| 68690 |   138585 |      81.00 | 15/05/2011 | OVERPAYMENT |     81.00 |              0 |        1 | Other       |        0 |     0.00 |   0.00 |       0.00 |       0 | 15/05/2011     |      NULL |       NULL |       0 |      0.00 |
+-------+----------+------------+------------+-------------+-----------+----------------+----------+-------------+----------+----------+--------+------------+---------+----------------+-----------+------------+---------+-----------+
2 rows in set (4 min 14.17 sec)

Looks like the bug only surfaces in specific conditions

That was my initial idea that the reason is related to some optimizer bug on original server that can be fixed in 5.1.61. That's why I asked for CREATE TABLE for the original (not federated) table. I need to know is it InnoDB table, for example.

I tried remote server 5.1.61 as well. No change.

The federated table is a replica of the real table. The real table is using the MyISAM engine

I believe I gave enough information to reproduce the bug. Can someone try to reproduce? I'm trying to avoid giving a dump of the data so that I do not have to scrub it of private information

Please, send the output of EXPLAIN for the query that gives duplicate records and for the case when results are correct.

*** explain for incorrect results:

mysql> explain select * from fed_jhb_pay_StaffLoans where MicroKey = 143775 or LKey = 157617;
+----+-------------+------------------------+-------------+------------------+------------------+---------+------+------+--------------------------------------------+
| id | select_type | table                  | type        | possible_keys    | key              | key_len | ref  | rows | Extra                                      |
+----+-------------+------------------------+-------------+------------------+------------------+---------+------+------+--------------------------------------------+
|  1 | SIMPLE      | fed_jhb_pay_StaffLoans | index_merge | PRIMARY,MicroKey | MicroKey,PRIMARY | 5,4     | NULL |    3 | Using union(MicroKey,PRIMARY); Using where |
+----+-------------+------------------------+-------------+------------------+------------------+---------+------+------+--------------------------------------------+
1 row in set (0.00 sec)

*** explain for correct results:

explain select * from fed_dbn_pay_StaffLoans where MicroKey = 27 or LKey = 68690;
+----+-------------+-------------------------+-------------+------------------+------------------+---------+------+------+--------------------------------------------+
| id | select_type | table                   | type        | possible_keys    | key              | key_len | ref  | rows | Extra                                      |
+----+-------------+-------------------------+-------------+------------------+------------------+---------+------+------+--------------------------------------------+
|  1 | SIMPLE      | fed_dbnr_pay_StaffLoans | index_merge | PRIMARY,MicroKey | MicroKey,PRIMARY | 5,4     | NULL |    3 | Using union(MicroKey,PRIMARY); Using where |
+----+-------------+-------------------------+-------------+------------------+------------------+---------+------+------+--------------------------------------------+
1 row in set (0.00 sec)

Thank you for the feedback.

I can not repeat described behavior with generic data. Could you please provide [partial] dump of original table, so we can repeat it?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".