MySQL Bugs: #104336: LEFT Join giving duplicate records for matching records from right table

Bug #104336	LEFT Join giving duplicate records for matching records from right table
Submitted:	16 Jul 2021 12:38	Modified:	17 Jul 2021 18:20
Reporter:	Prasant Kumar	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S1 (Critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Below query showing duplicate records for matching condition:
select *
	from REF_MEDIA_OUTLET RMO 
	left  JOIN 
	(
	select MEDIA_OUTLET_ID from SEC_DIVISION_NETWORK where DIVISION_ID in 
	(select DIVISION_ID from SEC_DIVISION_NETWORK where MEDIA_OUTLET_ID= 96 AND ACTIVE_YN='Y' and DIVISION_ID not in 
	(select DIVISION_ID from SEC_DIVISION where INCLUDE_IN_CLUSTER='N' AND ACTIVE_YN='Y'))
	AND ACTIVE_YN='Y' ) 
	T on T.MEDIA_OUTLET_ID=RMO.MEDIA_OUTLET_ID ;

In above query, right portion of the sub-query returning two records(96 & 166) and for those two records, the complete query is returning duplicate records, output is:

left.Media_outlet_id       right.media_outlet_id
96                            96
96                            96
166                           166
166                           166 

How to repeat:
CREATE TABLE `REF_OUTLET_LEFT` (
  `MEDIA_OUTLET_ID` int NOT NULL,
  `NAME` varchar(100) NOT NULL,
  `SHORT_NAME` varchar(20) DEFAULT NULL,
  `NET_ID` int NOT NULL,
  `MEDIA_OUTLET_TYPE_ID` int NOT NULL,
  `CABLE_NETWORK_ID` int DEFAULT NULL,
  `External_ID1` int DEFAULT NULL,
  `External_ID2` int DEFAULT NULL,
  `External_ID3` int DEFAULT NULL,
  `EXTERNAL_SHORT_NAME` varchar(50) DEFAULT NULL,
  `RATED_NETWORK_YN` char(1) DEFAULT NULL,
  `PRIMARY_CHANNEL_INT` int DEFAULT NULL,
  `IS_STREAMING_YN` char(1) DEFAULT NULL,
  PRIMARY KEY (`MEDIA_OUTLET_ID`),
  KEY `FK_REF_MEDIA_OUTLET_REF_NETWORK_1` (`NET_ID`),
  KEY `FK_REF_MEDIA_OUTLET_REF_MEDIA_OUTLET_TYPE_1` (`MEDIA_OUTLET_TYPE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `SEC_DIVISION_SUB_QUERY` (
  `DIVISION_ID` int NOT NULL AUTO_INCREMENT,
  `DIVISION_NAME` varchar(45) DEFAULT NULL,
  `ACTIVE_YN` char(1) DEFAULT 'Y',
  `INCLUDE_IN_CLUSTER` char(1) DEFAULT 'Y',
  `CREATE_DT` datetime DEFAULT NULL,
  `CREATE_USER` int DEFAULT NULL,
  `UPDATE_DT` datetime DEFAULT NULL,
  `UPDATE_USER` int DEFAULT NULL,
  `DISPLAY_SEQ` int NOT NULL,
  PRIMARY KEY (`DIVISION_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

CREATE TABLE `SEC_DIVISION_NET_SUB_QUERY` (
  `COMPONENT_ID` int NOT NULL AUTO_INCREMENT,
  `DIVISION_ID` int DEFAULT NULL,
  `MEDIA_OUTLET_ID` int DEFAULT NULL,
  `ACTIVE_YN` char(1) DEFAULT 'Y',
  `CREATE_DT` datetime DEFAULT NULL,
  `CREATE_USER` int DEFAULT NULL,
  `UPDATE_DT` datetime DEFAULT NULL,
  `UPDATE_USER` int DEFAULT NULL,
  PRIMARY KEY (`COMPONENT_ID`),
  KEY `SDNC_FK_DIVISION_ID_idx_1` (`DIVISION_ID`),
  KEY `SDNC_FK_MEDIA_OUTLET_ID_idx_1` (`MEDIA_OUTLET_ID`),
  CONSTRAINT `SDNC_FK_DIVISION_ID_idx_1` FOREIGN KEY (`DIVISION_ID`) REFERENCES `SEC_DIVISION` (`DIVISION_ID`) ON UPDATE CASCADE,
  CONSTRAINT `SDNC_FK_MEDIA_OUTLET_ID_idx_1` FOREIGN KEY (`MEDIA_OUTLET_ID`) REFERENCES `REF_MEDIA_OUTLET` (`MEDIA_OUTLET_ID`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=48 DEFAULT CHARSET=latin1;

INSERT INTO `REF_OUTLET_LEFT` (`MEDIA_OUTLET_ID`, `NAME`, `SHORT_NAME`, `NET_ID`, `MEDIA_OUTLET_TYPE_ID`, `CABLE_NETWORK_ID`, `RATED_NETWORK_YN`, `IS_STREAMING_YN`) VALUES ('1', 'MTV', 'MTV', '1', '1', '6198', 'Y', 'N');
INSERT INTO `REF_OUTLET_LEFT` (`MEDIA_OUTLET_ID`, `NAME`, `SHORT_NAME`, `NET_ID`, `MEDIA_OUTLET_TYPE_ID`, `CABLE_NETWORK_ID`, `RATED_NETWORK_YN`, `IS_STREAMING_YN`) VALUES ('2', 'MTV2', 'MTV2', '2', '1', '8466', 'Y', 'N');
INSERT INTO `REF_OUTLET_LEFT` (`MEDIA_OUTLET_ID`, `NAME`, `SHORT_NAME`, `NET_ID`, `MEDIA_OUTLET_TYPE_ID`, `CABLE_NETWORK_ID`, `RATED_NETWORK_YN`, `IS_STREAMING_YN`) VALUES ('3', 'VH1', 'VH1', '3', '1', '6546', 'Y', 'N');
INSERT INTO `REF_OUTLET_LEFT` (`MEDIA_OUTLET_ID`, `NAME`, `SHORT_NAME`, `NET_ID`, `MEDIA_OUTLET_TYPE_ID`, `CABLE_NETWORK_ID`, `RATED_NETWORK_YN`, `IS_STREAMING_YN`) VALUES ('11', 'Nickelodeon', 'NICK', '11', '1', '6212', 'Y', 'N');
INSERT INTO `REF_OUTLET_LEFT` (`MEDIA_OUTLET_ID`, `NAME`, `SHORT_NAME`, `NET_ID`, `MEDIA_OUTLET_TYPE_ID`, `CABLE_NETWORK_ID`, `RATED_NETWORK_YN`, `IS_STREAMING_YN`) VALUES ('12', 'TV Land', 'TVL', '12', '1', '7838', 'Y', 'N');
INSERT INTO `REF_OUTLET_LEFT` (`MEDIA_OUTLET_ID`, `NAME`, `SHORT_NAME`, `NET_ID`, `MEDIA_OUTLET_TYPE_ID`, `CABLE_NETWORK_ID`, `RATED_NETWORK_YN`, `IS_STREAMING_YN`) VALUES ('13', 'Nick JR', 'NICK JR', '13', '1', '2181', 'Y', 'N');
INSERT INTO `REF_OUTLET_LEFT` (`MEDIA_OUTLET_ID`, `NAME`, `SHORT_NAME`, `NET_ID`, `MEDIA_OUTLET_TYPE_ID`, `CABLE_NETWORK_ID`, `RATED_NETWORK_YN`, `IS_STREAMING_YN`) VALUES ('15', 'MTV Classic', 'MTVC', '15', '1', '8512', 'Y', 'N');
INSERT INTO `REF_OUTLET_LEFT` (`MEDIA_OUTLET_ID`, `NAME`, `SHORT_NAME`, `NET_ID`, `MEDIA_OUTLET_TYPE_ID`, `CABLE_NETWORK_ID`, `RATED_NETWORK_YN`, `IS_STREAMING_YN`) VALUES ('20', 'Paramount', 'PAR', '20', '1', '6221', 'Y', 'N');
INSERT INTO `REF_OUTLET_LEFT` (`MEDIA_OUTLET_ID`, `NAME`, `SHORT_NAME`, `NET_ID`, `MEDIA_OUTLET_TYPE_ID`, `CABLE_NETWORK_ID`, `RATED_NETWORK_YN`, `IS_STREAMING_YN`) VALUES ('21', 'Country Music Television', 'CMT', '21', '1', '6647', 'Y', 'N');
INSERT INTO `REF_OUTLET_LEFT` (`MEDIA_OUTLET_ID`, `NAME`, `SHORT_NAME`, `NET_ID`, `MEDIA_OUTLET_TYPE_ID`, `CABLE_NETWORK_ID`, `RATED_NETWORK_YN`, `IS_STREAMING_YN`) VALUES ('37', 'Nicktoons', 'NKTN', '37', '1', '9963', 'Y', 'N');
INSERT INTO `REF_OUTLET_LEFT` (`MEDIA_OUTLET_ID`, `NAME`, `SHORT_NAME`, `NET_ID`, `MEDIA_OUTLET_TYPE_ID`, `CABLE_NETWORK_ID`, `RATED_NETWORK_YN`, `IS_STREAMING_YN`) VALUES ('41', 'NICK at NITE', 'NAN', '41', '1', '9043', 'Y', 'N');
INSERT INTO `REF_OUTLET_LEFT` (`MEDIA_OUTLET_ID`, `NAME`, `SHORT_NAME`, `NET_ID`, `MEDIA_OUTLET_TYPE_ID`, `CABLE_NETWORK_ID`, `RATED_NETWORK_YN`, `IS_STREAMING_YN`) VALUES ('46', 'Comedy Central', 'CMDY', '46', '1', '7133', 'Y', 'N');
INSERT INTO `REF_OUTLET_LEFT` (`MEDIA_OUTLET_ID`, `NAME`, `SHORT_NAME`, `NET_ID`, `MEDIA_OUTLET_TYPE_ID`, `CABLE_NETWORK_ID`, `RATED_NETWORK_YN`, `IS_STREAMING_YN`) VALUES ('53', 'TeenNick', 'TNNK', '53', '1', '8331', 'Y', 'N');
INSERT INTO `REF_OUTLET_LEFT` (`MEDIA_OUTLET_ID`, `NAME`, `SHORT_NAME`, `NET_ID`, `MEDIA_OUTLET_TYPE_ID`, `CABLE_NETWORK_ID`, `RATED_NETWORK_YN`, `IS_STREAMING_YN`) VALUES ('78', 'LOGO', 'LOGO', '78', '1', '4443', 'Y', 'N');
INSERT INTO `REF_OUTLET_LEFT` (`MEDIA_OUTLET_ID`, `NAME`, `SHORT_NAME`, `NET_ID`, `MEDIA_OUTLET_TYPE_ID`, `CABLE_NETWORK_ID`, `RATED_NETWORK_YN`, `IS_STREAMING_YN`) VALUES ('96', 'BET', 'BET', '96', '1', '6200', 'Y', 'N');
INSERT INTO `REF_OUTLET_LEFT` (`MEDIA_OUTLET_ID`, `NAME`, `SHORT_NAME`, `NET_ID`, `MEDIA_OUTLET_TYPE_ID`, `CABLE_NETWORK_ID`, `RATED_NETWORK_YN`, `IS_STREAMING_YN`) VALUES ('166', 'BET Her', 'BHER', '166', '1', '6577', 'Y', 'N');
INSERT INTO `REF_OUTLET_LEFT` (`MEDIA_OUTLET_ID`, `NAME`, `SHORT_NAME`, `NET_ID`, `MEDIA_OUTLET_TYPE_ID`, `CABLE_NETWORK_ID`, `RATED_NETWORK_YN`, `IS_STREAMING_YN`) VALUES ('997', 'CBS', 'CBS', '997', '1', '9', 'Y', 'N');
INSERT INTO `REF_OUTLET_LEFT` (`MEDIA_OUTLET_ID`, `NAME`, `SHORT_NAME`, `NET_ID`, `MEDIA_OUTLET_TYPE_ID`, `CABLE_NETWORK_ID`, `RATED_NETWORK_YN`, `IS_STREAMING_YN`) VALUES ('998', 'SMITHSONIAN', 'SMTH', '998', '1', '6626', 'Y', 'N');
INSERT INTO `REF_OUTLET_LEFT` (`MEDIA_OUTLET_ID`, `NAME`, `SHORT_NAME`, `NET_ID`, `MEDIA_OUTLET_TYPE_ID`, `CABLE_NETWORK_ID`, `RATED_NETWORK_YN`, `IS_STREAMING_YN`) VALUES ('999', 'POP', 'POP', '999', '1', '6477', 'Y', 'N');

INSERT INTO `SEC_DIVISION_SUB_QUERY`  VALUES ('1', 'BET Nets', 'Y', 'Y', '2018-03-10 03:05:26', '1', '2019-04-05 05:45:29', '1', '100');
INSERT INTO `SEC_DIVISION_SUB_QUERY`  VALUES ('2', 'Comedy Central', 'N', 'Y', '2018-03-10 03:05:26', '1', '2019-04-05 05:45:28', '1', '300');
INSERT INTO `SEC_DIVISION_SUB_QUERY`  VALUES ('3', 'Corporate (All)', 'Y', 'N', '2018-03-10 03:05:26', '1', '2019-04-05 05:45:29', '1', '400');
INSERT INTO `SEC_DIVISION_SUB_QUERY`  VALUES ('4', 'MTV/VH1', 'N', 'Y', '2018-03-10 03:05:26', '1', '2019-04-05 05:45:29', '1', '700');
INSERT INTO `SEC_DIVISION_SUB_QUERY`  VALUES ('5', 'Kids Nets', 'Y', 'Y', '2018-03-10 03:05:26', '1', '2019-04-05 05:45:30', '1', '600');
INSERT INTO `SEC_DIVISION_SUB_QUERY`  VALUES ('6', 'TV Land/PAR/CMT', 'N', 'Y', '2018-03-10 03:05:26', '1', '2019-04-05 05:45:29', '1', '900');
INSERT INTO `SEC_DIVISION_SUB_QUERY`  VALUES ('7', 'MTV Entertainment Group', 'Y', 'Y', '2019-04-05 05:45:30', '1', '2020-05-18 08:08:40', '1', '500');
INSERT INTO `SEC_DIVISION_SUB_QUERY`  VALUES ('8', 'Music Nets', 'N', 'Y', '2019-04-05 05:45:30', '1', '2019-04-05 05:45:30', '1', '800');
INSERT INTO `SEC_DIVISION_SUB_QUERY`  VALUES ('9', 'CBS Broadcast Network', 'Y', 'Y', '2021-03-16 06:58:43', '1', '2021-03-16 06:58:43', '1', '200');

INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('1', '1', '96', 'Y', '2018-03-10 03:05:26', '1', '2018-03-10 03:05:26', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('2', '1', '166', 'Y', '2018-03-10 03:05:26', '1', '2018-03-10 03:05:26', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('3', '2', '46', 'N', '2018-03-10 03:05:26', '1', '2019-04-05 05:45:30', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('4', '3', '1', 'Y', '2018-03-10 03:05:26', '1', '2018-03-10 03:05:26', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('5', '3', '2', 'Y', '2018-03-10 03:05:26', '1', '2018-03-10 03:05:26', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('6', '3', '3', 'Y', '2018-03-10 03:05:26', '1', '2018-03-10 03:05:26', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('7', '3', '11', 'Y', '2018-03-10 03:05:26', '1', '2018-03-10 03:05:26', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('8', '3', '12', 'Y', '2018-03-10 03:05:26', '1', '2018-03-10 03:05:26', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('9', '3', '13', 'Y', '2018-03-10 03:05:26', '1', '2018-03-10 03:05:26', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('10', '3', '15', 'Y', '2018-03-10 03:05:26', '1', '2018-03-10 03:05:26', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('11', '3', '20', 'Y', '2018-03-10 03:05:26', '1', '2018-03-10 03:05:26', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('12', '3', '21', 'Y', '2018-03-10 03:05:26', '1', '2018-03-10 03:05:26', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('13', '3', '37', 'Y', '2018-03-10 03:05:26', '1', '2018-03-10 03:05:26', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('14', '3', '41', 'Y', '2018-03-10 03:05:26', '1', '2018-03-10 03:05:26', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('15', '3', '46', 'Y', '2018-03-10 03:05:26', '1', '2018-03-10 03:05:26', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('16', '3', '53', 'Y', '2018-03-10 03:05:26', '1', '2018-03-10 03:05:26', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('17', '3', '78', 'Y', '2018-03-10 03:05:26', '1', '2018-03-10 03:05:26', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('18', '3', '96', 'Y', '2018-03-10 03:05:26', '1', '2018-03-10 03:05:26', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('19', '3', '166', 'Y', '2018-03-10 03:05:26', '1', '2018-03-10 03:05:26', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('20', '4', '1', 'N', '2018-03-10 03:05:26', '1', '2019-04-05 05:45:30', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('21', '4', '2', 'N', '2018-03-10 03:05:26', '1', '2019-04-05 05:45:31', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('22', '4', '3', 'N', '2018-03-10 03:05:26', '1', '2019-04-05 05:45:31', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('23', '4', '15', 'N', '2018-03-10 03:05:26', '1', '2019-04-05 05:45:31', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('24', '4', '78', 'N', '2018-03-10 03:05:26', '1', '2019-04-05 05:45:31', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('25', '5', '11', 'Y', '2018-03-10 03:05:26', '1', '2018-03-10 03:05:26', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('26', '5', '13', 'Y', '2018-03-10 03:05:26', '1', '2018-03-10 03:05:26', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('27', '5', '41', 'Y', '2018-03-10 03:05:26', '1', '2018-03-10 03:05:26', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('28', '5', '37', 'Y', '2018-03-10 03:05:26', '1', '2018-03-10 03:05:26', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('29', '5', '53', 'Y', '2018-03-10 03:05:26', '1', '2018-03-10 03:05:26', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('30', '6', '12', 'N', '2018-03-10 03:05:26', '1', '2019-04-05 05:45:32', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('31', '6', '20', 'N', '2018-03-10 03:05:26', '1', '2019-04-05 05:45:32', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('32', '6', '21', 'N', '2018-03-10 03:05:26', '1', '2019-04-05 05:45:32', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('33', '7', '46', 'Y', '2019-04-05 05:45:32', '1', '2020-05-18 08:08:41', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('34', '7', '12', 'Y', '2019-04-05 05:45:32', '1', '2020-05-18 08:08:41', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('35', '7', '20', 'Y', '2019-04-05 05:45:32', '1', '2020-05-18 08:08:41', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('36', '7', '21', 'Y', '2019-04-05 05:45:32', '1', '2020-05-18 08:08:41', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('37', '7', '78', 'Y', '2019-04-05 05:45:32', '1', '2020-05-18 08:08:41', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('38', '7', '1', 'Y', '2019-04-05 05:45:32', '1', '2020-05-18 08:08:41', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('39', '7', '2', 'Y', '2019-04-05 05:45:32', '1', '2020-05-18 08:08:41', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('40', '7', '15', 'Y', '2019-04-05 05:45:32', '1', '2020-05-18 08:08:41', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('41', '7', '3', 'Y', '2019-04-05 05:45:32', '1', '2020-05-18 08:08:41', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('42', '7', '998', 'Y', '2020-09-20 05:21:16', '1', '2020-09-20 05:21:16', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('43', '7', '999', 'Y', '2020-09-20 05:21:17', '1', '2020-09-20 05:21:17', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('44', '3', '998', 'Y', '2020-09-20 05:21:16', '1', '2020-09-20 05:21:16', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('45', '3', '999', 'Y', '2020-09-20 05:21:17', '1', '2020-09-20 05:21:17', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('46', '9', '997', 'Y', '2021-03-16 06:58:43', '1', '2021-03-16 06:58:43', '1');
INSERT INTO `SEC_DIVISION_NET_SUB_QUERY`  VALUES ('47', '3', '997', 'Y', '2021-03-16 06:58:44', '1', '2021-03-16 06:58:44', '1');

select *
	from REF_OUTLET_LEFT RMO 
	left  JOIN 
	(
	select MEDIA_OUTLET_ID from SEC_DIVISION_NET_SUB_QUERY where DIVISION_ID in 
	(select DIVISION_ID from SEC_DIVISION_NET_SUB_QUERY where MEDIA_OUTLET_ID= 96 AND ACTIVE_YN='Y' and DIVISION_ID not in 
	(select DIVISION_ID from SEC_DIVISION_SUB_QUERY where INCLUDE_IN_CLUSTER='N' AND ACTIVE_YN='Y'))
	AND ACTIVE_YN='Y' ) 
	T on T.MEDIA_OUTLET_ID=RMO.MEDIA_OUTLET_ID ;

Suggested fix:
The query is working fine when adding DISTINCT keyword in sub-query of right side. This is also working fine by adding GROUP BY clause at the end of the sub-query block. Please look into it.

Thank you for the bug report. Please check with latest release version 8.0.25, if the issue continue, provide a correct test case which is failing with the below error:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.26 Source distribution BUILT: 2021-MAY-26

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 8.0 > CREATE DATABASE JJ;
Query OK, 1 row affected (0.01 sec)

mysql 8.0 > USE JJ
Database changed
mysql 8.0 > CREATE TABLE `REF_OUTLET_LEFT` (
    ->   `MEDIA_OUTLET_ID` int NOT NULL,
    ->   `NAME` varchar(100) NOT NULL,
    ->   `SHORT_NAME` varchar(20) DEFAULT NULL,
    ->   `NET_ID` int NOT NULL,
    ->   `MEDIA_OUTLET_TYPE_ID` int NOT NULL,
    ->   `CABLE_NETWORK_ID` int DEFAULT NULL,
    ->   `External_ID1` int DEFAULT NULL,
    ->   `External_ID2` int DEFAULT NULL,
    ->   `External_ID3` int DEFAULT NULL,
    ->   `EXTERNAL_SHORT_NAME` varchar(50) DEFAULT NULL,
    ->   `RATED_NETWORK_YN` char(1) DEFAULT NULL,
    ->   `PRIMARY_CHANNEL_INT` int DEFAULT NULL,
    ->   `IS_STREAMING_YN` char(1) DEFAULT NULL,
    ->   PRIMARY KEY (`MEDIA_OUTLET_ID`),
    ->   KEY `FK_REF_MEDIA_OUTLET_REF_NETWORK_1` (`NET_ID`),
    ->   KEY `FK_REF_MEDIA_OUTLET_REF_MEDIA_OUTLET_TYPE_1` (`MEDIA_OUTLET_TYPE_ID`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.04 sec)

mysql 8.0 > CREATE TABLE `SEC_DIVISION_SUB_QUERY` (
    ->   `DIVISION_ID` int NOT NULL AUTO_INCREMENT,
    ->   `DIVISION_NAME` varchar(45) DEFAULT NULL,
    ->   `ACTIVE_YN` char(1) DEFAULT 'Y',
    ->   `INCLUDE_IN_CLUSTER` char(1) DEFAULT 'Y',
    ->   `CREATE_DT` datetime DEFAULT NULL,
    ->   `CREATE_USER` int DEFAULT NULL,
    ->   `UPDATE_DT` datetime DEFAULT NULL,
    ->   `UPDATE_USER` int DEFAULT NULL,
    ->   `DISPLAY_SEQ` int NOT NULL,
    ->   PRIMARY KEY (`DIVISION_ID`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.03 sec)

mysql 8.0 > CREATE TABLE `SEC_DIVISION_NET_SUB_QUERY` (
    ->   `COMPONENT_ID` int NOT NULL AUTO_INCREMENT,
    ->   `DIVISION_ID` int DEFAULT NULL,
    ->   `MEDIA_OUTLET_ID` int DEFAULT NULL,
    ->   `ACTIVE_YN` char(1) DEFAULT 'Y',
    ->   `CREATE_DT` datetime DEFAULT NULL,
    ->   `CREATE_USER` int DEFAULT NULL,
    ->   `UPDATE_DT` datetime DEFAULT NULL,
    ->   `UPDATE_USER` int DEFAULT NULL,
    ->   PRIMARY KEY (`COMPONENT_ID`),
    ->   KEY `SDNC_FK_DIVISION_ID_idx_1` (`DIVISION_ID`),
    ->   KEY `SDNC_FK_MEDIA_OUTLET_ID_idx_1` (`MEDIA_OUTLET_ID`),
    ->   CONSTRAINT `SDNC_FK_DIVISION_ID_idx_1` FOREIGN KEY (`DIVISION_ID`) REFERENCES `SEC_DIVISION` (`DIVISION_ID`) ON UPDATE CASCADE,
    ->   CONSTRAINT `SDNC_FK_MEDIA_OUTLET_ID_idx_1` FOREIGN KEY (`MEDIA_OUTLET_ID`) REFERENCES `REF_MEDIA_OUTLET` (`MEDIA_OUTLET_ID`) ON UPDATE CASCADE
    -> ) ENGINE=InnoDB AUTO_INCREMENT=48 DEFAULT CHARSET=latin1;
ERROR 1824 (HY000): Failed to open the referenced table 'sec_division'
mysql 8.0 >

script to re-create the issue

Attachment: bug_script.sql (application/octet-stream, text), 13.83 KiB.

We have tested this bug with 8.0.25 version also and the issue still persists. Attaching the correct script file to re-generate the issue:

Thank you for the feedback.