Bug #114807 Subquery returns more than 1 row
Submitted: 29 Apr 6:28 Modified: 29 Apr 8:16
Reporter: lei yue Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.33 OS:Any
Assigned to: CPU Architecture:Any

[29 Apr 6:28] lei yue 
Description:
It can also be reproduced in the new version 8.0, mainly due to the error reported by the IndexScanIterator using the secondary index. When using the EQRefIterator operator of the primary key, the ExecuteIteratorQuery operator will jump out when matching 1 row. However, when using the secondary index, it does not. Yes, causing an error

How to repeat:
```
CREATE TABLE `auth_org` (
  `ID` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '组织ID',
  `CODE` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '机构代号',
  `SHORT_NAME` varchar(128) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '机构简称',
  `SORT_CODE` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '排序代码',
  `PARENT_ID` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '上级机构',
  `LEVEL` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '机构级别',
  `ORG_TYPE` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '机构类型(BRANCH:供应商，DEPARTMENT：科室，GROUP：业务组)',
  `LEADER` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '负责人',
  `REMARK` varchar(512) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '机构说明',
  `REVISION` int DEFAULT NULL COMMENT '乐观锁',
  `CREATED_TIME` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`ID`) USING BTREE,
  KEY `IDX_PARENT_ID` (`PARENT_ID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin COMMENT='组织机构';

INSERT INTO `auth_org` VALUES ('12489691','wldh1','',NULL,'223282370',NULL,'','','',NULL,'2024-03-15 10:18:49'),('12681858','qwfw2','',NULL,'536464021',NULL,'','','',NULL,'2024-01-26 14:11:49'),('129099158','xcx1','',NULL,'491365791',NULL,'','','',NULL,'2024-03-15 10:19:07'),('188167169','1fw','',NULL,'577943021',NULL,'','','',NULL,'2022-07-27 15:33:14'),('195954376','mhzx','',NULL,'zj36745499',NULL,'','','',NULL,'2022-10-26 17:35:33'),('2','1000','',NULL,'1','1','','','',NULL,'2021-11-01 13:41:46'),('203133781','slb','',NULL,'zj279330589',NULL,'','','',NULL,'2022-10-26 17:40:13'),('218181686','yx2','',NULL,'zj36745499',NULL,'','','',NULL,'2023-09-18 17:40:47'),('223282370','测试111','',NULL,'zj279330589',NULL,'','','',NULL,'2024-03-15 10:17:57'),('26572478','dianhe11','',NULL,'343611817',NULL,'','','',NULL,'2022-09-29 09:42:39'),('273804521','流程新增1','',NULL,'zj36745499',NULL,'','','',NULL,'2023-09-12 09:49:17'),('27922531','shualzx','',NULL,'zj36745499',NULL,'','','',NULL,'2022-10-26 17:36:01'),('303131738','XCV','',NULL,'zj279330589',NULL,'','','',NULL,'2022-07-08 21:33:46'),('318003110','CE2','',NULL,'zj36745499',NULL,'','','',NULL,'2023-09-18 16:15:12'),('319158051','mh1','',NULL,'959021492',NULL,'','','',NULL,'2022-10-26 17:42:42'),('338550149','流程校验3','',NULL,'zj36745499',NULL,'','','',NULL,'2023-09-14 16:20:02'),('343611817','dh','',NULL,'zj36745499',NULL,'','','',NULL,'2022-09-29 09:41:01'),('348580437','zjb','',NULL,'zj279330589',NULL,'','','',NULL,'2021-10-13 15:15:34'),('353522631','dianhe','',NULL,'zj279330589',NULL,'','','',NULL,'2022-09-29 09:41:32'),('378816960','在这种','',NULL,'348580437',NULL,'','','',NULL,'2021-11-02 15:12:28'),('38087263','333','',NULL,'896905619',NULL,'','','',NULL,'2023-09-11 11:48:07'),('410910337','111','',NULL,'472361927',NULL,'','','',NULL,'2023-09-11 11:49:16'),('41223400','yxhcyz','',NULL,'415173936',NULL,'','','',NULL,'2023-08-25 11:43:42'),('415173936','yxhc','',NULL,'zj36745499',NULL,'','','',NULL,'2023-08-25 11:43:03'),('442454829','rgsp1','',NULL,'98242513',NULL,'','','',NULL,'2022-11-25 16:03:30'),('472361927','kfzx','',NULL,'zj36745499',NULL,'','','',NULL,'2021-10-13 15:18:00'),('491365791','测试233','',NULL,'zj279330589',NULL,'','','',NULL,'2024-03-15 10:18:16'),('503334932','fwzcs','',NULL,'zj36745499',NULL,'','','',NULL,'2021-10-13 15:18:36'),('506765909','yckf','',NULL,'zj279330589',NULL,'','','',NULL,'2021-12-22 14:55:34'),('516857565','zj1z','',NULL,'348580437',NULL,'','','',NULL,'2021-10-13 15:16:36'),('524438698','kfb','',NULL,'zj279330589',NULL,'','','',NULL,'2021-10-13 15:17:42'),('524694029','dianhe01','',NULL,'353522631',NULL,'','','',NULL,'2022-09-29 09:42:23'),('525219477','mh1z','',NULL,'195954376',NULL,'','','',NULL,'2022-10-26 17:36:47'),('536464021','123qwfw','',NULL,'zj36745499',NULL,'','','',NULL,'2024-01-26 14:10:43'),('557217075','YX','',NULL,'791609211',NULL,'','','',NULL,'2023-09-18 16:11:35'),('576635169','hcerzu','',NULL,'415173936',NULL,'','','',NULL,'2023-08-25 11:44:01'),('577943021','fwwh','',NULL,'zj279330589',NULL,'','','',NULL,'2022-07-27 15:32:20'),('678422695','客服部一组','',NULL,'524438698',NULL,'','','',NULL,'2021-12-02 10:28:08'),('695585856','测试','',NULL,'zj279330589',NULL,'','','',NULL,'2021-12-09 16:29:43'),('699927870','要新增1','',NULL,'zj36745499',NULL,'','','',NULL,'2023-09-27 14:37:36'),('723259886','shual','',NULL,'203133781',NULL,'','','',NULL,'2022-10-26 17:42:59'),('765450854','流程校验2','',NULL,'zj36745499',NULL,'','','',NULL,'2023-09-12 11:31:37'),('787985535','222','',NULL,'2',NULL,'','','',NULL,'2021-11-16 13:06:52'),('791609211','123','',NULL,'zj36745499',NULL,'','','',NULL,'2023-09-11 11:45:02'),('812830052','cuishou','',NULL,'zj279330589',NULL,'','','',NULL,'2022-06-17 17:06:04'),('828319604','cui1','',NULL,'880283565',NULL,'','','',NULL,'2022-06-17 17:09:52'),('842930241','qwfu1','',NULL,'536464021',NULL,'','','',NULL,'2024-01-26 14:11:30'),('863014084','20220310','',NULL,'503334932',NULL,'','','',NULL,'2022-03-10 10:28:19'),('878114027','发电机房价','',NULL,'472361927',NULL,'','','',NULL,'2021-10-14 18:55:50'),('880283565','cuishouz','',NULL,'zj36745499',NULL,'','','',NULL,'2022-06-17 17:06:26'),('896905619','a\'s\'k','',NULL,'472361927',NULL,'','','',NULL,'2021-10-14 18:30:57'),('933639245','sl1z','',NULL,'27922531',NULL,'','','',NULL,'2022-10-26 17:37:05'),('938377300','cuis1','',NULL,'812830052',NULL,'','','',NULL,'2022-06-17 17:09:18'),('959021492','mh','',NULL,'zj279330589',NULL,'','','',NULL,'2022-10-26 17:39:58'),('98242513','rgsp','',NULL,'zj36745499',NULL,'','','',NULL,'2022-11-25 16:02:43'),('zj279330589','xiaojin','',NULL,'2','2','','','',NULL,'2021-05-31 14:58:42'),('zj36745499','zhitong','',NULL,'2','2','','','',NULL,'2021-05-31 14:59:06');
```

### force index secondary

```
txsql> SELECT @r as _id, (SELECT @r := PARENT_ID FROM AUTH_ORG force index(IDX_PARENT_ID) WHERE ID= _id) AS PARENT_ID, @l :=@l + 1 as lvl FROM (select @r := '516857565',@l :=0) vars,AUTH_ORG h WHERE PARENT_ID is not null order by lvl desc;
ERROR 1242 (21000): Subquery returns more than 1 row
```

### force index primary
```
txsql> SELECT @r as _id, (SELECT @r := PARENT_ID FROM AUTH_ORG force index(PRIMARY) WHERE ID= _id) AS PARENT_ID, @l :=@l + 1 as lvl FROM (select @r := '516857565',@l :=0) vars,AUTH_ORG h WHERE PARENT_ID is not null order by lvl desc;
+-------------+-------------+------+
| _id         | PARENT_ID   | lvl  |
+-------------+-------------+------+
| 1           | NULL        |   57 |
| 1           | NULL        |   56 |
| 1           | NULL        |   55 |
| 1           | NULL        |   54 |
| 1           | NULL        |   53 |
| 1           | NULL        |   52 |
| 1           | NULL        |   51 |
| 1           | NULL        |   50 |
| 1           | NULL        |   49 |
| 1           | NULL        |   48 |
| 1           | NULL        |   47 |
| 1           | NULL        |   46 |
| 1           | NULL        |   45 |
| 1           | NULL        |   44 |
| 1           | NULL        |   43 |
| 1           | NULL        |   42 |
| 1           | NULL        |   41 |
| 1           | NULL        |   40 |
| 1           | NULL        |   39 |
| 1           | NULL        |   38 |
| 1           | NULL        |   37 |
| 1           | NULL        |   36 |
| 1           | NULL        |   35 |
| 1           | NULL        |   34 |
| 1           | NULL        |   33 |
| 1           | NULL        |   32 |
| 1           | NULL        |   31 |
| 1           | NULL        |   30 |
| 1           | NULL        |   29 |
| 1           | NULL        |   28 |
| 1           | NULL        |   27 |
| 1           | NULL        |   26 |
| 1           | NULL        |   25 |
| 1           | NULL        |   24 |
| 1           | NULL        |   23 |
| 1           | NULL        |   22 |
| 1           | NULL        |   21 |
| 1           | NULL        |   20 |
| 1           | NULL        |   19 |
| 1           | NULL        |   18 |
| 1           | NULL        |   17 |
| 1           | NULL        |   16 |
| 1           | NULL        |   15 |
| 1           | NULL        |   14 |
| 1           | NULL        |   13 |
| 1           | NULL        |   12 |
| 1           | NULL        |   11 |
| 1           | NULL        |   10 |
| 1           | NULL        |    9 |
| 1           | NULL        |    8 |
| 1           | NULL        |    7 |
| 1           | NULL        |    6 |
| 1           | NULL        |    5 |
| 2           | 1           |    4 |
| zj279330589 | 2           |    3 |
| 348580437   | zj279330589 |    2 |
| 516857565   | 348580437   |    1 |
+-------------+-------------+------+
57 rows in set, 4 warnings (0.01 sec)
```
[29 Apr 8:16] MySQL Verification Team 
Hello lei yue,

Thank you for the report and feedback.

regards,
Umesh
[30 Apr 3:06] tianfeng li 
```
(SELECT @r := PARENT_ID FROM AUTH_ORG force index(PRIMARY) WHERE ID= _id)
```

In dependant query execution, the correlated parameter `_id` in filter condition `ID= _id` should be constant.
However, each row produced by this subselect has a side affect that changing the value of `@r` by `@r := PARENT_ID`. At the meantime, `_id` refers to the outer query block `@r`, which is same as the `@r` inside this subselect.

As a result, correlated parameters had been changed in one single round of subselect execution by itself!!

There may be a solution that we always create a Item_cache for user_var when it is referred to by other query blocks. OR consider this issue as NOT a bug AT ALL..