Bug #101489 select federated table with in, has duplicate record
Submitted: 6 Nov 2020 6:10 Modified: 9 Dec 2020 12:55
Reporter: panran Deng Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.7.17 OS:CentOS
Assigned to: MySQL Verification Team CPU Architecture:Any
Tags: federated in

[6 Nov 2020 6:10] panran Deng 
Description:
this sql has duplicate record, event it should have in fact;
SELECT a.id FROM test1 a WHERE	a.tenantid = '8a808eab7304af05017308062d2700d4' AND a.biz_title LIKE '测试流程%' AND a.id IN (SELECT b.biz_Ins_Id FROM	test2 b	 WHERE	b.wf_Task_State ='4' AND b.wf_Task_User = 'PGU131300000000' AND b.wf_Task_Username LIKE '测试');

How to repeat:
1.CREATE TABLE `test1` (
  `ID` varchar(32) NOT NULL COMMENT '业务实例id',
  `BIZ_TITLE` varchar(100) NOT NULL COMMENT '业务标题',
  `TENANTID` varchar(32) DEFAULT NULL COMMENT '租户',
  PRIMARY KEY (`ID`)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8  COMMENT='业务实例' CONNECTION='dhcplat_server_link';

CREATE TABLE `test2` (
  `ID` varchar(32) NOT NULL COMMENT '环节id',
  `BIZ_INS_ID` varchar(32) NOT NULL COMMENT '业务实例id',
  `WF_TASK_STATE` varchar(2) NOT NULL COMMENT '当前环节状态',
  `WF_TASK_USER` varchar(32) DEFAULT NULL COMMENT '办理人id',
  `WF_TASK_USERNAME` varchar(50) DEFAULT NULL COMMENT '办理人名称',
  PRIMARY KEY (`ID`)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 COMMENT='业务过程意见' CONNECTION='dhcplat_server_link';

2.SELECT a.id FROM test1 a WHERE a.tenantid = '8a808eab7304af05017308062d2700d4' AND a.biz_title LIKE '测试流程%' AND a.id IN (SELECT b.biz_Ins_Id FROM	test2 b	 WHERE	b.wf_Task_State ='4' AND b.wf_Task_User = 'PGU131300000000' AND b.wf_Task_Username LIKE '测试');

This query has two records, and the two records are exactly the same.
+---------------------------------+
|               id                |
+---------------------------------+
| dfa77669c9b645b8907f653b49dbde43|
| dfa77669c9b645b8907f653b49dbde43|
+---------------------------------+

3.SELECT a.id FROM test1 a WHERE a.tenantid = '8a808eab7304af05017308062d2700d4' AND a.biz_title LIKE '测试流程%' AND EXISTS (SELECT b.biz_Ins_Id FROM	test2 b	WHERE	b.wf_Task_State ='4' AND b.wf_Task_User = 'PGU131300000000' AND b.wf_Task_Username LIKE '测试'  AND b.biz_Ins_Id=a.id);

This query has one records
+---------------------------------+
|               id                |
+---------------------------------+
| dfa77669c9b645b8907f653b49dbde43|
+---------------------------------+
[9 Nov 2020 12:55] MySQL Verification Team 
Hi,

For start, can you reproduce this with 5.7.32 because 5.7.17 is really too old.

Second, I need full reproducible test case, this means
 - config of native server
 - create tables on native server
 - insert required data on native server
 - config of client server
 - config of "connection" from client to native
 - create tables on client server
 - query to reproduce from client server

Thanks
Bogdan
[10 Dec 2020 1:00] Bugs System 
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".