Description:
Get wrong result when two table join sql execute.
The query is as below:
select t1.subTaskId, t2.count1, t2.count2 from t1 STRAIGHT_JOIN t2 ON t2.subTaskId in (1504351734767296512,1504351734767296513,1504351734767296514) and t2.subTaskId = t1.subTaskId where t1.subTaskId in (1504351734767296512,1504351734767296513,1504351734767296514);
the wrong result is:
+---------------------+--------+--------+
| subTaskId | count1 | count2 |
+---------------------+--------+--------+
| 1504351734767296514 | 1 | 1 |
| 1504351734767296513 | 1 | 1 |
| 1504351734767296512 | 1 | 1 |
| 1504351734767296514 | 2 | 2 |
| 1504351734767296513 | 2 | 2 |
| 1504351734767296512 | 2 | 2 |
| 1504351734767296514 | 3 | 3 |
| 1504351734767296513 | 3 | 3 |
| 1504351734767296512 | 3 | 3 |
+---------------------+--------+--------+
change the join order and sql is:
select t1.subTaskId, t2.count1, t2.count2 from t2 STRAIGHT_JOIN t1 ON t2.subTaskId in (1504351734767296512,1504351734767296513,1504351734767296514) and t2.subTaskId = t1.subTaskId where t1.subTaskId in (1504351734767296512,1504351734767296513,1504351734767296514);
the right result is:
+---------------------+--------+--------+
| subTaskId | count1 | count2 |
+---------------------+--------+--------+
| 1504351734767296512 | 1 | 1 |
| 1504351734767296513 | 2 | 2 |
| 1504351734767296514 | 3 | 3 |
+---------------------+--------+--------+
How to repeat:
table definition:
CREATE TABLE `t1` (
`subTaskId` bigint(20) NOT NULL,
PRIMARY KEY (`subTaskId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `t2` (
`rptDate` date NOT NULL DEFAULT '1970-01-01',
`subTaskId` varchar(32) NOT NULL DEFAULT '-1',
`count1` bigint(13) NOT NULL DEFAULT '0',
`count2` bigint(13) NOT NULL DEFAULT '0',
PRIMARY KEY (`rptDate`,`subTaskId`),
KEY `IDX_T_REPORT_TASK_DAY_RPTDATE` (`rptDate`),
KEY `IDX_T_REPORT_TASK_DAY_SUBTASKID` (`subTaskId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
table data:
insert into t1 values (1504351734767296512),(1504351734767296513),(1504351734767296514);
insert into t2 values ('2022-03-17',1504351734767296512,1,1),('2022-03-17',1504351734767296513,2,2),('2022-03-17',1504351734767296514,3,3);
Description: Get wrong result when two table join sql execute. The query is as below: select t1.subTaskId, t2.count1, t2.count2 from t1 STRAIGHT_JOIN t2 ON t2.subTaskId in (1504351734767296512,1504351734767296513,1504351734767296514) and t2.subTaskId = t1.subTaskId where t1.subTaskId in (1504351734767296512,1504351734767296513,1504351734767296514); the wrong result is: +---------------------+--------+--------+ | subTaskId | count1 | count2 | +---------------------+--------+--------+ | 1504351734767296514 | 1 | 1 | | 1504351734767296513 | 1 | 1 | | 1504351734767296512 | 1 | 1 | | 1504351734767296514 | 2 | 2 | | 1504351734767296513 | 2 | 2 | | 1504351734767296512 | 2 | 2 | | 1504351734767296514 | 3 | 3 | | 1504351734767296513 | 3 | 3 | | 1504351734767296512 | 3 | 3 | +---------------------+--------+--------+ change the join order and sql is: select t1.subTaskId, t2.count1, t2.count2 from t2 STRAIGHT_JOIN t1 ON t2.subTaskId in (1504351734767296512,1504351734767296513,1504351734767296514) and t2.subTaskId = t1.subTaskId where t1.subTaskId in (1504351734767296512,1504351734767296513,1504351734767296514); the right result is: +---------------------+--------+--------+ | subTaskId | count1 | count2 | +---------------------+--------+--------+ | 1504351734767296512 | 1 | 1 | | 1504351734767296513 | 2 | 2 | | 1504351734767296514 | 3 | 3 | +---------------------+--------+--------+ How to repeat: table definition: CREATE TABLE `t1` ( `subTaskId` bigint(20) NOT NULL, PRIMARY KEY (`subTaskId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `t2` ( `rptDate` date NOT NULL DEFAULT '1970-01-01', `subTaskId` varchar(32) NOT NULL DEFAULT '-1', `count1` bigint(13) NOT NULL DEFAULT '0', `count2` bigint(13) NOT NULL DEFAULT '0', PRIMARY KEY (`rptDate`,`subTaskId`), KEY `IDX_T_REPORT_TASK_DAY_RPTDATE` (`rptDate`), KEY `IDX_T_REPORT_TASK_DAY_SUBTASKID` (`subTaskId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; table data: insert into t1 values (1504351734767296512),(1504351734767296513),(1504351734767296514); insert into t2 values ('2022-03-17',1504351734767296512,1,1),('2022-03-17',1504351734767296513,2,2),('2022-03-17',1504351734767296514,3,3);