基于湖库一体架构,统一管理结构化、半结构化与非结构化等多模态数据,一个系统承载事务处理、实时分析与 AI 工作负载。
SPJ where 子查询的 where 条件中包含两参数的 substr 函数,做条件单调性判断时触发 core
更新时间:2026-05-14 07:41
问题现象
SPJ 形式的 where 子查询中,包含两参数的 substr 函数时,会触发 core。
例如下述的 SELECT 语句。
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 栈信息如下。
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 */。