---
title: SPJ where 子查询的 where 条件中包含两参数的 substr 函数，做条件单调性判断时触发 core-OceanBase数据库使用指南
description: 了解OceanBase数据库在实际应用中关于 SPJ where 子查询的 where 条件中包含两参数的 substr 函数，做条件单调性判断时触发 core相关的常见问题和使用技巧，帮助您快速解决 SPJ where 子查询的 where 条件中包含两参数的 substr 函数，做条件单调性判断时触发 core的难题。
image: https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*OSPzQ6GUQF4AAAAAQHAAAAgAeiGDAQ/original
---
切换语言

- 中文站 - 简体中文
- International - English
- 日本站 - 日本語

划线反馈

# SPJ where 子查询的 where 条件中包含两参数的 substr 函数，做条件单调性判断时触发 core

更新时间：2026-05-14 07:41

适用版本： V4.2.x 内容类型：Troubleshoot  

## 问题现象

SPJ 形式的 where 子查询中，包含两参数的 substr 函数时，会触发 core。

例如下述的 SELECT 语句。

```shell
create table t1(c1 int, c2 varchar(100));
create table t2(c1 int, c2 varchar(100));
select * from t1 where c1 > (select c1 from t2 where substr(c2, 1) = t1.c2);

```

## 关键诊断信息

### 触发条件

**充分不必要条件：** SPJ 形式的 where 子查询中，包含两参数的 substr 函数。

### 事前巡检

检查是否存在上述形式的 SQL 语句。

### 事后诊断

通过 core 栈可以快速定位到 `get_expr_monotonicity_recursively` 函数的问题，这个 core 栈可以取出实际上发生问题的 SQL 语句。与 `get_expr_monotonicity_recursively` 函数中的 switch case 语句对比，容易发现是 substr 函数引发的问题。

core 栈信息如下。

```shell
safe_backtrace at ??:?
oceanbase::common::coredump_cb(int, int, void*, void*) at ??:?
?? ??:0
oceanbase::sql::ObOptimizerUtil::get_expr_monotonicity_recursively(oceanbase::sql::ObRawExpr const*, oceanbase::sql::ObColumnRefRawExpr const*, oceanbase::sql::ObExecContext&, oceanbase::sql::Monotonicity&, bool&, oceanbase::common::Ob2DArray<oceanbase::common::ObObjParam, 2079744, oceanbase::common::ObWrapperAllocator, false, oceanbase::common::ObSEArray<oceanbase::common::ObObjParam*, 1l, oceanbase::common::ObWrapperAllocator, false> > const&, oceanbase::sql::ObPCConstParamInfo&) at 2_cxx.cxx:?
oceanbase::sql::ObOptimizerUtil::is_expr_const_for_monotonicity(oceanbase::sql::ObRawExpr const*, oceanbase::common::ObIArray<oceanbase::sql::ObRawExpr*> const&, oceanbase::sql::ObExecContext*, bool&) at ??:?
oceanbase::sql::ObWhereSubQueryPullup::check_subquery_validity(oceanbase::sql::ObDMLStmt&, oceanbase::sql::ObRawExpr*, oceanbase::sql::ObQueryRefRawExpr*, bool, oceanbase::common::ObIArray<oceanbase::sql::ObWhereSubQueryPullup::SingleSetParam>&) at 1_cxx.cxx:?
oceanbase::sql::ObWhereSubQueryPullup::get_single_set_subquery(oceanbase::sql::ObDMLStmt&, oceanbase::sql::ObRawExpr*, oceanbase::sql::ObRawExpr*, bool, oceanbase::common::ObIArray<oceanbase::sql::ObWhereSubQueryPullup::SingleSetParam>&) at 1_cxx.cxx:?
oceanbase::sql::ObWhereSubQueryPullup::transform_single_set_query(oceanbase::sql::ObDMLStmt*, oceanbase::common::ObIArray<oceanbase::sql::ObSelectStmt*>&, bool&) at 1_cxx.cxx:?
oceanbase::sql::ObWhereSubQueryPullup::transform_one_stmt(oceanbase::common::ObIArray<oceanbase::sql::ObParentDMLStmt>&, oceanbase::sql::ObDMLStmt*&, bool&) at 1_cxx.cxx:?
oceanbase::sql::ObTransformRule::transform_self(oceanbase::common::ObIArray<oceanbase::sql::ObParentDMLStmt>&, long, oceanbase::sql::ObDMLStmt*&) at ??:?
oceanbase::sql::ObTransformRule::transform_post_order(oceanbase::common::ObIArray<oceanbase::sql::ObParentDMLStmt>&, long, oceanbase::sql::ObDMLStmt*&) at 0_cxx.cxx:?
oceanbase::sql::ObTransformRule::transform_stmt_recursively(oceanbase::common::ObIArray<oceanbase::sql::ObParentDMLStmt>&, long, oceanbase::sql::ObDMLStmt*&) at ??:?
oceanbase::sql::ObTransformRule::transform(oceanbase::sql::ObDMLStmt*&, unsigned long&) at ??:?
oceanbase::sql::ObTransformerImpl::transform_rule_set_in_one_iteration(oceanbase::sql::ObDMLStmt*&, unsigned long, bool&) at 1_cxx.cxx:?
oceanbase::sql::ObTransformerImpl::transform_rule_set(oceanbase::sql::ObDMLStmt*&, unsigned long, long, bool&) at ??:?
oceanbase::sql::ObTransformerImpl::transform(oceanbase::sql::ObDMLStmt*&) at ??:?
oceanbase::sql::ObSql::transform_stmt(oceanbase::sql::ObSqlSchemaGuard*, oceanbase::common::ObOptStatManager*, oceanbase::common::ObAddr*, oceanbase::sql::ObPhysicalPlan*, oceanbase::sql::ObExecContext&, oceanbase::sql::ObDMLStmt*&, bool) at ??:?
oceanbase::sql::ObSql::generate_plan(ParseResult&, oceanbase::sql::ObPlanCacheCtx*, oceanbase::sql::ObSqlCtx&, oceanbase::sql::ObResultSet&, oceanbase::sql::PlanCacheMode, oceanbase::sql::ObStmt*, oceanbase::share::schema::ObStmtNeedPrivs&, oceanbase::share::schema::ObStmtOraNeedPrivs&) at 0_cxx.cxx:?
oceanbase::sql::ObSql::generate_physical_plan(ParseResult&, oceanbase::sql::ObPlanCacheCtx*, oceanbase::sql::ObSqlCtx&, oceanbase::sql::ObResultSet&, bool, oceanbase::sql::PlanCacheMode, ParseResult*) at 0_cxx.cxx:?
oceanbase::sql::ObSql::handle_physical_plan(oceanbase::common::ObString const&, oceanbase::sql::ObSqlCtx&, oceanbase::sql::ObResultSet&, oceanbase::sql::ObPlanCacheCtx&, int) at 0_cxx.cxx:?
oceanbase::sql::ObSql::handle_pl_execute(oceanbase::common::ObString const&, oceanbase::sql::ObSQLSessionInfo&, oceanbase::common::Ob2DArray<oceanbase::common::ObObjParam, 2079744, oceanbase::common::ObWrapperAllocator, false, oceanbase::common::ObSEArray<oceanbase::common::ObObjParam*, 1l, oceanbase::common::ObWrapperAllocator, false> >&, oceanbase::sql::ObResultSet&, oceanbase::sql::ObSqlCtx&, bool, bool) at ??:?
oceanbase::sql::ObSPIService::inner_open(oceanbase::pl::ObPLExecCtx*, oceanbase::common::ObString const&, oceanbase::common::ObString const&, long, oceanbase::common::Ob2DArray<oceanbase::common::ObObjParam, 2079744, oceanbase::common::ObWrapperAllocator, false, oceanbase::common::ObSEArray<oceanbase::common::ObObjParam*, 1l, oceanbase::common::ObWrapperAllocator, false> >&, oceanbase::sql::ObSPIResultSet&, oceanbase::sql::ObSPIOutParams&, bool) at ??:?
oceanbase::sql::ObSPIService::inner_open(oceanbase::pl::ObPLExecCtx*, oceanbase::common::ObIAllocator&, oceanbase::common::ObString const&, oceanbase::common::ObString const&, long, void*, long, oceanbase::sql::ObSqlExpression const**, long, oceanbase::sql::ObSPIResultSet&, oceanbase::sql::ObSPIOutParams&, bool, bool, bool) at 0_cxx.cxx:?
oceanbase::sql::ObSPIService::unstreaming_cursor_open(oceanbase::pl::ObPLExecCtx*, oceanbase::pl::ObPLCursorInfo&, oceanbase::sql::ObSQLSessionInfo&, oceanbase::common::ObString const&, oceanbase::common::ObString const&, long, void*, long, bool, bool, bool, bool, long) at 0_cxx.cxx:?
oceanbase::sql::ObSPIService::spi_cursor_open(oceanbase::pl::ObPLExecCtx*, char const*, char const*, long, bool, bool, oceanbase::sql::ObSqlExpression const**, long, unsigned long, unsigned long, long, long const*, oceanbase::sql::ObSqlExpression const**, long, bool) at 0_cxx.cxx:?
oceanbase::sql::ObSPIService::spi_dynamic_open(oceanbase::pl::ObPLExecCtx*, long, long const*, long, unsigned long, unsigned long, long) at ??:?
?? ??:0

```

## 问题原因

SPJ where 子查询在尝试子查询提升改写时，会通过 `get_expr_monotonicity_recursively` 函数去判断子查询的 where 条件的单调性。

这个函数在处理 substr 函数时，没有对 substr 的第三个参数对应的指针做空值校验，就直接使用了这个指针。因此当 substr 只有两个参数的时候，会触发 core。

## 问题的风险及影响

影响程度为中等。子查询必须是主查询的 where 条件中的一个 SPJ 子查询，并且子查询自己的 where 条件中包含只有两个参数的 substr 函数，才会触发该问题。

## 影响租户

影响 OceanBase 数据库中的 SYS 租户和 Oracle 租户以及 MySQL 租户。

## 影响版本

OceanBase 数据库企业版 V4.2.5 GA（oceanbase-4.2.5.0-100000082024102022）及之后版本。

## 解决方法

- 升级至问题已修复版本。目前已修复的版本包括 OceanBase 数据库企业版 V4.2.5 BP1 Hotfix2（oceanbase-4.2.5.1-101020052025011421）、V4.2.5 BP2（oceanbase-4.2.5.2-102000122025011711）。
 - 重启 OBServer。

## 规避方式

使用添加 Hint 提示 `/*+ NO_REWRITE */` 或者 `/*+NO_UNNEST */`。

上一篇

[如何在行列混存的场景下，指定 Hint 走上预期的计划](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000005412746)

下一篇

[数据库执行计划频繁跳变问题排查与解决](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000005438096) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线