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
Triage: Needs Triage: D2 (Serious)

[21 Feb 2012 7:42] Desmond Coertzen
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)
[27 Feb 2012 16:38] Valeriy Kravchuk
I wonder how original table (that federated points to) is defined, and will query with order by return correct result against it directly.
[27 Feb 2012 17:35] Desmond Coertzen
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
[28 Feb 2012 20:02] Sveta Smirnova
Thank you for the report.

Verified as described.
[28 Feb 2012 21:25] Sveta Smirnova
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;
[26 Sep 2014 18:30] Shane Bester
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)