问题现象
遇到这个问题可能会 core 掉。
可以从计划上判断是否存是这个问题导致，比如下面这个计划中，2 号 hash join 中有一个 join key 左侧是 cast(length(t1.c2), DECIMAL(20, 0))，但是这个表达式没有分配在 3 号 join filter 中。
-- 一个触发 core 的例子
create table t1(c1 bigint, c2 varchar(2000));
create table t2(c1 bigint, c2 varchar(2000));
create table t3(c1 bigint, c2 varchar(2000));
insert into t1 select random(123) , repeat('0123456789', 120) from table(generator(300));
insert into t1 select random(123) , "123" from table(generator(500));
insert into t2 select random(123) , repeat('0123456789', 100) from table(generator(80));
insert into t2 select random(123) , "123" from table(generator(100));
insert into t3 select random(123) , repeat('0123456789', 100) from table(generator(80));
insert into t3 select random(123) , "123" from table(generator(100));
explain basic select /*+
BEGIN_OUTLINE_DATA
LEADING(@"SEL$1" ("test"."t1"@"SEL$1" ("test"."t2"@"SEL$1" "test"."t3"@"SEL$1")))
USE_HASH(@"SEL$1" ("test"."t3"@"SEL$1" "test"."t2"@"SEL$1"))
PQ_DISTRIBUTE(@"SEL$1" ("test"."t3"@"SEL$1" "test"."t2"@"SEL$1") BC2HOST NONE)
PX_JOIN_FILTER(@"SEL$1" "test"."t2"@"SEL$1" "test"."t1"@"SEL$1")
NO_PX_JOIN_FILTER(@"SEL$1" "test"."t3"@"SEL$1" "test"."t1"@"SEL$1")
PARALLEL(@"SEL$1" "test"."t1"@"SEL$1" 2)
FULL(@"SEL$1" "test"."t1"@"SEL$1")
USE_HASH(@"SEL$1" "test"."t3"@"SEL$1")
PQ_DISTRIBUTE(@"SEL$1" "test"."t3"@"SEL$1" BC2HOST NONE)
PX_JOIN_FILTER(@"SEL$1" "test"."t3"@"SEL$1" "test"."t2"@"SEL$1")
PARALLEL(@"SEL$1" "test"."t2"@"SEL$1" 2)
FULL(@"SEL$1" "test"."t2"@"SEL$1")
PARALLEL(@"SEL$1" "test"."t3"@"SEL$1" 2)
FULL(@"SEL$1" "test"."t3"@"SEL$1")
PARALLEL(2)
OPTIMIZER_FEATURES_ENABLE('4.3.2.0')
END_OUTLINE_DATA
*/
* from t1, t2, t3 where length(t1.c2)=t3.c2 and t1.c2 = t2.c2 and t2.c1=t3.c1;
Query Plan
======================================================
|ID|OPERATOR |NAME |
------------------------------------------------------
|0 |PX COORDINATOR | |
|1 |└─EXCHANGE OUT DISTR |:EX10002|
|2 | └─SHARED HASH JOIN | |
|3 | ├─join filter CREATE |:RF0001 |
|4 | │ └─EXCHANGE IN DISTR | |
|5 | │ └─EXCHANGE OUT DISTR (BC2HOST) |:EX10000|
|6 | │ └─PX BLOCK ITERATOR | |
|7 | │ └─TABLE FULL SCAN |t1 |
|8 | └─SHARED HASH JOIN | |
|9 | ├─join filter CREATE |:RF0000 |
|10| │ └─EXCHANGE IN DISTR | |
|11| │ └─EXCHANGE OUT DISTR (BC2HOST)|:EX10001|
|12| │ └─join filter USE |:RF0001 |
|13| │ └─PX BLOCK ITERATOR | |
|14| │ └─TABLE FULL SCAN |t2 |
|15| └─join filter USE |:RF0000 |
|16| └─PX BLOCK ITERATOR | |
|17| └─TABLE FULL SCAN |t3 |
======================================================
Outputs & filters:
-------------------------------------
0 - output([INTERNAL_FUNCTION(t1.c1, t1.c2, t2.c1, t2.c2, t3.c1, t3.c2)]), filter(nil), rowset=256
1 - output([INTERNAL_FUNCTION(t1.c1, t1.c2, t2.c1, t2.c2, t3.c1, t3.c2)]), filter(nil), rowset=256
dop=2
2 - output([t1.c2], [t3.c2], [t2.c2], [t1.c1], [t2.c1], [t3.c1]), filter(nil), rowset=256
equal_conds([cast(length(t1.c2), DECIMAL(20, 0)) = cast(t3.c2, DECIMAL(-1, -1))], [t1.c2 = t2.c2]), other_conds(nil)
3 - output([t1.c2], [t1.c1]), filter(nil), rowset=256
RF_TYPE(in, range, bloom), RF_EXPR[t1.c2]
4 - output([t1.c2], [t1.c1]), filter(nil), rowset=256
5 - output([t1.c2], [t1.c1]), filter(nil), rowset=256
dop=2
6 - output([t1.c2], [t1.c1]), filter(nil), rowset=256
7 - output([t1.c2], [t1.c1]), filter(nil), rowset=256
access([t1.c2], [t1.c1]), partitions(p0)
is_index_back=false, is_global_index=false,
range_key([t1.__pk_increment]), range(MIN ; MAX)always true
8 - output([t3.c2], [t2.c2], [t2.c1], [t3.c1]), filter(nil), rowset=256
equal_conds([t2.c1 = t3.c1]), other_conds(nil)
9 - output([t2.c2], [t2.c1]), filter(nil), rowset=256
RF_TYPE(in, range, bloom), RF_EXPR[t2.c1]
10 - output([t2.c2], [t2.c1]), filter(nil), rowset=256
11 - output([t2.c2], [t2.c1]), filter(nil), rowset=256
dop=2
12 - output([t2.c2], [t2.c1]), filter(nil), rowset=256
13 - output([t2.c2], [t2.c1]), filter(nil), rowset=256
14 - output([t2.c2], [t2.c1]), filter([RF_IN_FILTER(t2.c2)], [RF_RANGE_FILTER(t2.c2)], [RF_BLOOM_FILTER(t2.c2)]), rowset=256
access([t2.c2], [t2.c1]), partitions(p0)
is_index_back=false, is_global_index=false, filter_before_indexback[false,false,false],
range_key([t2.__pk_increment]), range(MIN ; MAX)always true
15 - output([t3.c2], [t3.c1]), filter(nil), rowset=256
16 - output([t3.c2], [t3.c1]), filter(nil), rowset=256
17 - output([t3.c2], [t3.c1]), filter([RF_IN_FILTER(t3.c1)], [RF_RANGE_FILTER(t3.c1)], [RF_BLOOM_FILTER(t3.c1)]), rowset=256
access([t3.c2], [t3.c1]), partitions(p0)
is_index_back=false, is_global_index=false, filter_before_indexback[false,false,false],
range_key([t3.__pk_increment]), range(MIN ; MAX)always true
关键诊断信息
触发条件
必要不充分条件：
hash join 的左侧条件中需要有一个 join 等值条件的左侧是一个需要计算表达式，而非投影列。
需要满足 hash join 的左侧输出的每一行数据占据的空间较大，满足每一行行长的均值大于 256 个字节。
事前巡检
无。
事后诊断
通过 core 栈可以快速定位到是 join filter 算子做后构建的函数 join_filter_create_do_material 中计算表达式出现了问题，访问了非法内存，分析下来是表达式没有清理计算标记导致。
oceanbase::common::coredump_cb(int, int, void*, void*) at ??:?
?? ??:0
oceanbase::sql::ObExprTimestamp::calc_timestamp1(oceanbase::sql::ObExpr const&, oceanbase::sql::ObEvalCtx&, oceanbase::common::ObDatum&) at ??:?
oceanbase::sql::expr_default_eval_batch_func(oceanbase::sql::ObExpr const&, oceanbase::sql::ObEvalCtx&, oceanbase::sql::ObBitVectorImpl const&, long) at ??:?
oceanbase::sql::ObExpr::eval_vector(oceanbase::sql::ObEvalCtx&, oceanbase::sql::ObBitVectorImpl const&, oceanbase::sql::EvalBound const&) const at ??:?
oceanbase::sql::ObJoinFilterOp::join_filter_create_do_material(long) at 0_cxx.cxx:?
oceanbase::sql::ObJoinFilterOp::inner_get_next_batch(long) at 0_cxx.cxx:?
oceanbase::sql::ObOperator::get_next_batch(long, oceanbase::sql::ObBatchRows const*&) at ??:?
oceanbase::sql::ObHashJoinVecOp::inner_get_next_batch(long) at 0_cxx.cxx:?
oceanbase::sql::ObOperator::get_next_batch(long, oceanbase::sql::ObBatchRows const*&) at ??:?
oceanbase::sql::ObPxTransmitOp::next_vector(long) at 1_cxx.cxx:?
oceanbase::sql::ObPxDistTransmitOp::inner_get_next_batch(long) at ??:?
oceanbase::sql::ObOperator::get_next_batch(long, oceanbase::sql::ObBatchRows const*&) at ??:?
oceanbase::sql::ObPxTransmitOp::fetch_first_row() at 1_cxx.cxx:?
oceanbase::sql::ObPxTransmitOp::inner_open() at ??:?
oceanbase::sql::ObPxDistTransmitOp::inner_open() at ??:?
oceanbase::sql::ObOperator::open() at ??:?
oceanbase::sql::ObPxTaskProcess::process() at 0_cxx.cxx:?
oceanbase::sql::PxWorkerFunctor::operator()(bool) at 0_cxx.cxx:?
oceanbase::omt::ObPxPool::run1() at 0_cxx.cxx:?
oceanbase::omt::ObPxPool::run(long) at 0_cxx.cxx:?
oceanbase::lib::Thread::__th_start(void*) at 0_cxx.cxx:?
?? ??:0
?? ??:0
问题原因
在 OceanBase 数据库 V4.3.3 及之后版本，在 hash join 场景生成 join filter 时，会使用 Bloom Filter 后构建的优化，使用真实的行数来分配 Bloom Filter 的大小，这个功能需要在 join filter 算子内提前计算 hash join 的所有相关表达式，但是在表达式分配的时候，没有将这些表达式挪到 join filter 内部，导致出现了内存问题从而 core 掉。
快速判断方式：
观察计划中 hash join 算子内关于是否有左表的表达式没有出现在 join filter 算子内。比如上面这个计划中，2 号 hash join 中有一个 join key 左侧是
cast(length(t1.c2), DECIMAL(20, 0))，但是这个表达式没有分配在 3 号 join filter 中。堆栈中包含有
join_filter_create_do_material和eval_vector相关信息。xxxx oceanbase::sql::ObExpr::eval_vector(oceanbase::sql::ObEvalCtx&, oceanbase::sql::ObBitVectorImpl const&, oceanbase::sql::EvalBound const&) const at ??:? oceanbase::sql::ObJoinFilterOp::join_filter_create_do_material xxxx
问题的风险及影响
中等。查询必须是满足 hash join 的等值条件中有一个计算表达式，且平均行长必须要够长。
影响租户
影响 OceanBase 数据库中的 SYS 租户和 Oracle 租户以及 MySQL 租户。
影响版本
OceanBase 数据库企业版 V4.3.3 GA（oceanbase-4.3.3.0-100000362024093001）及之后版本、V4.3.5 GA（oceanbase-4.3.5.0-100000122024123020）及之后版本。
解决方法
升级至问题已修复版本。目前已修复的版本包括 OceanBase 数据库企业版 V4.3.3 BP1 Hotfix6（oceanbase-4.3.3.1-101060012025012322）、V4.3.3 BP1 Hotfix7（oceanbase-4.3.3.1-101070012025021915）、V4.3.5 GA Hotfix3（oceanbase-4.3.5.0-100030012025020717）、V4.3.5 GA Hotfix4（oceanbase-4.3.5.0-100040012025021921）、V4.3.5 GA Hotfix5（oceanbase-4.3.5.0-100050012025022422）、V4.3.5 GA Hotfix6（oceanbase-4.3.5.0-100060022025031922）、V4.3.5 GA Hotfix7（oceanbase-4.3.5.0-100070012025040810）、V4.3.5 GA Hotfix8（oceanbase-4.3.5.0-100080012025052715）、V4.3.5 GA Hotfix9（oceanbase-4.3.5.0-100090012025063017）、V4.3.5 GA Hotfix10（ooceanbase-4.3.5.0-100100032025071516）、V4.3.5 GA Hotfix11（oceanbase-4.3.5.0-100110012025072810）、V4.3.5 GA Hotfix12（oceanbase-4.3.5.0-100120012026011215）、V4.3.5 BP1（oceanbase-4.3.5.1-101000292025030623）。
系统租户下，将集群配置项开关
_preset_runtime_bloom_filter_size设置成 true。alter system set _preset_runtime_bloom_filter_size = true;
规避方式
系统租户下，将集群配置项开关 _preset_runtime_bloom_filter_size 设置成 true。
alter system set _preset_runtime_bloom_filter_size = true;
