MySQL Bugs: #64391: Select from federated table with order by clause returns no rows

Bug #64391	Select from federated table with order by clause returns no rows
Submitted:	21 Feb 2012 7:42	Modified:	28 Feb 2012 20:02
Reporter:	Desmond Coertzen	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Federated storage engine	Severity:	S2 (Serious)
Version:	5.1.59, 5.5.23, 5.6.5, 5.0.96	OS:	Linux (open suse 11.2)
Assigned to:		CPU Architecture:	Any

Description:
A select from a federated table returns a record set.
When running the same select with a order by clause on the pk, no records are returned.

How to repeat:
step1: create the federated table
---------------------------------

CREATE TABLE `fed_otb_pay_StaffLoanPayments` (
  `LKey` int(11) NOT NULL,
  `StaffKey` int(11) NOT NULL DEFAULT '0',
  `LoanKey` int(11) NOT NULL DEFAULT '0',
  `PayAmount` double(8,2) DEFAULT NULL,
  `DateOfPayment` char(11) DEFAULT NULL,
  `DateT` char(10) DEFAULT NULL,
  `Reason` char(51) NOT NULL DEFAULT '0',
  `bbrk_payment_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`LKey`),
  UNIQUE KEY `unq_pslp_bbrkpaymentid` (`bbrk_payment_id`),
  KEY `LoanKey` (`LoanKey`),
  KEY `StaffKey` (`StaffKey`)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://user@host/otb/pay_StaffLoanPayments;

step 2: execute the query
-------------------------

select * from fed_otb_pay_StaffLoanPayments where LoanKey = 84915 and bbrk_payment_id is null;
+--------+----------+---------+-----------+---------------+------------+--------+-----------------+
| LKey   | StaffKey | LoanKey | PayAmount | DateOfPayment | DateT      | Reason | bbrk_payment_id |
+--------+----------+---------+-----------+---------------+------------+--------+-----------------+
| 459984 |   101042 |   84915 |     45.00 | 08/01/2012    | 12/01/2012 | 0      |            NULL |
| 460115 |   101042 |   84915 |     45.00 | 15/01/2012    | 19/01/2012 | 0      |            NULL |
| 460220 |   101042 |   84915 |     45.00 | 22/01/2012    | 26/01/2012 | 0      |            NULL |
| 460464 |   101042 |   84915 |     45.00 | 05/02/2012    | 09/02/2012 | 0      |            NULL |
| 460579 |   101042 |   84915 |     45.00 | 12/02/2012    | 16/02/2012 | 0      |            NULL |
+--------+----------+---------+-----------+---------------+------------+--------+-----------------+
5 rows in set (6.07 sec)

step3: execute the same query with order by on pk
-------------------------------------------------

select * from fed_otb_pay_StaffLoanPayments where LoanKey = 84915 and bbrk_payment_id is null order by LKey;
Empty set (0.08 sec)

I wonder how original table (that federated points to) is defined, and will query with order by return correct result against it directly.

The federated table is a replica of the real table. Quick test on the real table reveals the that the record does not disappear when ordering by the pk. If it did, it would have been a serious bug that would have been reported by many other. By the way, both db's runs ver 5.1.59

Thank you for the report.

Verified as described.

Test case for MTR:

source federated.inc;

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

CREATE TABLE federated.`fed_otb_pay_StaffLoanPayments` (
  `LKey` int(11) NOT NULL,
  `StaffKey` int(11) NOT NULL DEFAULT '0',
  `LoanKey` int(11) NOT NULL DEFAULT '0',
  `PayAmount` double(8,2) DEFAULT NULL,
  `DateOfPayment` char(11) DEFAULT NULL,
  `DateT` char(10) DEFAULT NULL,
  `Reason` char(51) NOT NULL DEFAULT '0',
  `bbrk_payment_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`LKey`),
  UNIQUE KEY `unq_pslp_bbrkpaymentid` (`bbrk_payment_id`),
  KEY `LoanKey` (`LoanKey`),
  KEY `StaffKey` (`StaffKey`)
) DEFAULT CHARSET=utf8;

insert into federated.fed_otb_pay_StaffLoanPayments values (459984, 101042, 84915, 45.00, 08/01/2012, 12/01/2012, 0, NULL ), ( 460115, 101042, 84915, 45.00, 15/01/2012, 19/01/2012, 0, NULL ), ( 460220, 101042, 84915, 45.00, 22/01/2012, 26/01/2012, 0, NULL ), ( 460464, 101042, 84915, 45.00, 05/02/2012, 09/02/2012, 0, NULL ), ( 460579, 101042, 84915, 45.00, 12/02/2012, 16/02/2012, 0, NULL);

connection master;
--disable_warnings
DROP TABLE IF EXISTS fed_otb_pay_StaffLoanPayments;
--enable_warnings

--replace_result $SLAVE_MYPORT SLAVE_PORT
eval CREATE TABLE `fed_otb_pay_StaffLoanPayments` (
  `LKey` int(11) NOT NULL,
  `StaffKey` int(11) NOT NULL DEFAULT '0',
  `LoanKey` int(11) NOT NULL DEFAULT '0',
  `PayAmount` double(8,2) DEFAULT NULL,
  `DateOfPayment` char(11) DEFAULT NULL,
  `DateT` char(10) DEFAULT NULL,
  `Reason` char(51) NOT NULL DEFAULT '0',
  `bbrk_payment_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`LKey`),
  UNIQUE KEY `unq_pslp_bbrkpaymentid` (`bbrk_payment_id`),
  KEY `LoanKey` (`LoanKey`),
  KEY `StaffKey` (`StaffKey`)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8

    CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/fed_otb_pay_StaffLoanPayments';
	
select * from fed_otb_pay_StaffLoanPayments where LoanKey = 84915 and bbrk_payment_id is
null;

select * from fed_otb_pay_StaffLoanPayments where LoanKey = 84915 and bbrk_payment_id is
null  order by LKey;

5.7.6:

--------
drop table if exists `t1`,`t2`;
create table `t2`(`a` int,`b` int,`c` int,unique(`b`),key(`a`)) engine=innodb;
create table `t1`(`a` int,`b` int,`c` int,unique(`b`),key(`a`)) engine=federated connection='mysql://root@127.0.0.1:3306/test/t2';
insert into `t2`(`a`,`b`) values (1,1),(20,2),(30,3),(null,4);
select * from `t1` where `a` = 1 or `a` is null order by `c`;
select * from `t1` where `a` = 1 or `a` is null;
--------

mysql> select * from `t1` where `a` = 1 or `a` is null order by `c`;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    1 | NULL |
+------+------+------+
1 row in set (0.10 sec)

mysql> select * from `t1` where `a` = 1 or `a` is null;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    1 | NULL |
| NULL |    4 | NULL |
+------+------+------+
2 rows in set (0.05 sec)