ARRAY类型作为where条件在聚合场景有个小bug

数据库技术爱好者 · 2026 年5 月 20 日 11:18

【使用环境】测试环境
【 OB or 其他组件】OB MySQL模式
【使用版本】4.4.2版本
【问题描述】ARRAY类型在联合查询时，作为查询条件，会报类型不匹配问题

【有概率报错】SELECT agent.host_type, COUNT(*) AS count FROM ctrl_agent agent LEFT JOIN ctrl_agent_group group on agent.group_id = group.id WHERE ARRAY_CONTAINS(group.path_ids, ‘00000000-0000-4000-a000-000000000002’) GROUP BY agent.host_type;
【一定不报错】SELECT agent.host_type, COUNT(*) AS count FROM ctrl_agent agent LEFT JOIN ctrl_agent_group group on agent.group_id = group.id AND ARRAY_CONTAINS(group.path_ids, ‘00000000-0000-4000-a000-000000000002’) GROUP BY agent.host_type;
【一定不报错】SELECT agent.host_type, COUNT(*) AS count FROM ctrl_agent agent LEFT JOIN ctrl_agent_group group on agent.group_id = group.id WHERE ARRAY_CONTAINS(agent.path_ids, ‘00000000-0000-4000-a000-000000000002’) GROUP BY agent.host_type;

【初步分析】
SQL生成的查询计划为如下所示时，6号算子对path_ids做了聚合操作，而path_ids是一个ARRAY类型，故报错

mysql> explain  select  agent.group_id as data, count(1)   from ctrl_agent as agent left join ctrl_agent_group agent_group on agent.group_id = agent_group.id where array_contains(agent_group.path_ids, '00000000-0000-4000-a000-00000000000a') gro
up by data;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                                                     |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ==============================================================                                                                                                                                 |
| |ID|OPERATOR               |NAME       |EST.ROWS|EST.TIME(us)|                                                                                                                                 |
| --------------------------------------------------------------                                                                                                                                 |
| |0 |HASH GROUP BY          |           |1       |63          |                                                                                                                                 |
| |1 |└─HASH OUTER JOIN      |           |1       |63          |                                                                                                                                 |
| |2 |  ├─SUBPLAN SCAN       |VIEW1      |1       |59          |                                                                                                                                 |
| |3 |  │ └─HASH GROUP BY    |           |1       |59          |                                                                                                                                 |
| |4 |  │   └─TABLE FULL SCAN|agent      |469     |12          |                                                                                                                                 |
| |5 |  └─SUBPLAN SCAN       |VIEW2      |2       |3           |                                                                                                                                 |
| |6 |    └─HASH GROUP BY    |           |2       |3           |                                                                                                                                 |
| |7 |      └─TABLE FULL SCAN|agent_group|2       |3           |                                                                                                                                 |
| ==============================================================                                                                                                                                 |
| Outputs & filters:                                                                                                                                                                             |
| -------------------------------------                                                                                                                                                          |
|   0 - output([VIEW1.agent.group_id], [T_FUN_COUNT_SUM(VIEW1.T_FUN_COUNT(*) * CASE WHEN VIEW2.T_FUN_COUNT(*) IS NOT NULL THEN VIEW2.T_FUN_COUNT(*) ELSE 1                                       |
|       END)]), filter(nil), rowset=256                                                                                                                                                          |
|       group([VIEW1.agent.group_id]), agg_func([T_FUN_COUNT_SUM(VIEW1.T_FUN_COUNT(*) * CASE WHEN VIEW2.T_FUN_COUNT(*) IS NOT NULL THEN VIEW2.T_FUN_COUNT(*)                                     |
|       ELSE 1 END)])                                                                                                                                                                            |
|   1 - output([VIEW1.agent.group_id], [VIEW1.T_FUN_COUNT(*)], [VIEW2.T_FUN_COUNT(*)]), filter([array_contains(VIEW2.agent_group.path_ids, '00000000-0000-4000-a000-00000000000a')]), rowset=256 |
|       equal_conds([VIEW1.agent.group_id = VIEW2.agent_group.id]), other_conds(nil)                                                                                                             |
|   2 - output([VIEW1.agent.group_id], [VIEW1.T_FUN_COUNT(*)]), filter(nil), rowset=256                                                                                                          |
|       access([VIEW1.agent.group_id], [VIEW1.T_FUN_COUNT(*)])                                                                                                                                   |
|   3 - output([agent.group_id], [T_FUN_COUNT(*)]), filter(nil), rowset=256                                                                                                                      |
|       group([agent.group_id]), agg_func([T_FUN_COUNT(*)])                                                                                                                                      |
|   4 - output([agent.group_id]), filter(nil), rowset=256                                                                                                                                        |
|       access([agent.group_id]), partitions(p0)                                                                                                                                                 |
|       is_index_back=false, is_global_index=false,                                                                                                                                              |
|       range_key([agent.id], [agent.tenant]), range(MIN,MIN ; MAX,MAX)always true                                                                                                               |
|   5 - output([VIEW2.agent_group.path_ids], [VIEW2.agent_group.id], [VIEW2.T_FUN_COUNT(*)]), filter(nil), rowset=256                                                                            |
|       access([VIEW2.agent_group.path_ids], [VIEW2.agent_group.id], [VIEW2.T_FUN_COUNT(*)])                                                                                                     |
|   6 - output([agent_group.path_ids], [agent_group.id], [T_FUN_COUNT(*)]), filter(nil), rowset=256                                                                                              |
|       group([agent_group.path_ids], [agent_group.id]), agg_func([T_FUN_COUNT(*)])                                                                                                              |
|   7 - output([agent_group.id], [agent_group.path_ids]), filter(nil), rowset=256                                                                                                                |
|       access([agent_group.id], [agent_group.path_ids]), partitions(p0)                                                                                                                         |
|       is_index_back=false, is_global_index=false,                                                                                                                                              |
|       range_key([agent_group.id], [agent_group.tenant]), range(MIN,MIN ; MAX,MAX)always true

【复现路径】问题出现前后相关操作

创建两张表：t1.group_id关联到t2.id，t2有一个类型为ARRAY(VARCHAR(32))的字段
往t1插入1000条数据，t2插入两条数据，t1.group_id随机关联到t2.id
执行问题中类似的SQL，即可复现（构造出一样的查询计划）

独善其身 · 2026 年5 月 20 日 11:21

这个分析的很细致了

耶一耶 · 2026 年5 月 20 日 11:22

学习

辞霜 · 2026 年5 月 20 日 15:33

使用的是oracle模式么

数据库技术爱好者 · 2026 年5 月 20 日 15:46

MySQL模式

数据库技术爱好者 · 2026 年5 月 20 日 15:56

补充一下：

版本是4.4.2
错误日志: ERROR 5083 (22000): Invalid data type for the operation

辞霜 · 2026 年5 月 20 日 16:02

t2类型为ARRAY(VARCHAR(32))的字段表的建表sql麻烦提供一份

数据库技术爱好者 · 2026 年5 月 20 日 16:03

上午本来要提交附件的，没提交成功，我再试试

数据库技术爱好者 · 2026 年5 月 20 日 16:39

data.7z (19.7 KB)

AntTech_7N26K0 · 2026 年5 月 20 日 17:09

学习

辞霜 · 2026 年5 月 20 日 17:16

你这是商业版么，开源版创建会报错5001问题。show create table ctrl_agent 看下

数据库技术爱好者 · 2026 年5 月 20 日 17:18

不是，是社区版本

mysql> show create table ctrl_agent;
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table


                                                                                                                                                                                     |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ctrl_agent | CREATE TABLE `ctrl_agent` (
  `id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `agent_id` varbinary(64) NOT NULL,
  `host_type` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `group_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `path_ids` ARRAY(VARCHAR(64)) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uidx_ctrl_agent_agent_id` (`agent_id`) BLOCK_SIZE 16384 LOCAL,
  KEY `idx_ctrl_agent_group_id` (`group_id`) BLOCK_SIZE 16384 LOCAL,
  KEY `idx_ctrl_agent_name` (`name`) BLOCK_SIZE 16384 LOCAL
) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+


mysql> show create table ctrl_agent_group;
+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------+
| Table            | Create Table

                    |
+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------+
| ctrl_agent_group | CREATE TABLE `ctrl_agent_group` (
  `id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `path_ids` ARRAY(VARCHAR(64)) NOT NULL,
  PRIMARY KEY (`id`)
) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------+

辞霜 · 2026 年5 月 20 日 17:41

这边反馈给研发看下