基于湖库一体架构,统一管理结构化、半结构化与非结构化等多模态数据,一个系统承载事务处理、实时分析与 AI 工作负载。
SQL 语句过滤条件里存在非同一字段判断条件超过 64 个报错 -4002
更新时间:2026-05-28 02:06
适用版本
OceanBase 数据库所有版本。
问题现象
SQL 语句的过滤条件里,非同一个字段的判断条件超过 64 个时报错 -4002 Invalid argument。比如执行如下 SQL 就会报错。
select * from t20220608 where
aid='1' and bid='1' and cid='1' and did='1' and eid='1' and fid='1' and gid='1' and hid='1' and iid='1' and jid='1' and
kid='1' and lid='1' and mid='1' and nid='1' and oid='1' and pid='1' and qid='1' and rid='1' and sid='1' and tid='1' and
aaid='1' and abid='1' and acid='1' and adid='1' and aeid='1' and afid='1' and agid='1' and ahid='1' and aiid='1' and ajid='1' and
akid='1' and alid='1' and amid='1' and anid='1' and aoid='1' and apid='1' and aqid='1' and arid='1' and asid='1' and atid='1' and
baid='1' and bbid='1' and bcid='1' and bdid='1' and beid='1' and bfid='1' and bgid='1' and bhid='1' and biid='1' and bjid='1' and
bkid='1' and blid='1' and bmid='1' and bnid='1' and boid='1' and bpid='1' and bqid='1' and brid='1' and bsid='1' and btid='1' and
caid='1' and cbid='1' and ccid='1' or cdid='1' or ceid like '1' ;
类似这种非同一字段的条件数超 64 时就会报错 4002 Invalid argument。
对应日志如下:
[2022-06-08 16:54:26.039260] WARN [SQL] execute_get_plan (ob_sql.cpp:3041) [58443][0][YB42ACC83C1A-0005DFBA1498F8F8-0-0] [lt=17] [dc=0] fail to get plan retry(ret=-5138)
[2022-06-08 16:54:26.044530] WARN [SQL.OPT] add_filter_column_ids (ob_skyline_prunning.cpp:79) [58443][0][YB42ACC83C1A-0005DFBA1498F8F8-0-0] [lt=21] [dc=0] too many columns(ret=-4002, filter_column_ids.count()=65)
[2022-06-08 16:54:26.044551] WARN [SQL.OPT] add_index_back_dim (ob_skyline_prunning.cpp:481) [58443][0][YB42ACC83C1A-0005DFBA1498F8F8-0-0] [lt=19] [dc=0] failed to add restrcit_ids(ret=-4002)
[2022-06-08 16:54:26.044557] WARN [SQL.JO] cal_dimension_info (ob_join_order.cpp:1611) [58443][0][YB42ACC83C1A-0005DFBA1498F8F8-0-0] [lt=4] [dc=0] add index back dim failed(is_index_back=false, ret=-4002)
[2022-06-08 16:54:26.044569] WARN [SQL.JO] prunning_index (ob_join_order.cpp:1675) [58443][0][YB42ACC83C1A-0005DFBA1498F8F8-0-0] [lt=6] [dc=0] Failed to cal dimension info(ret=-4002, index_id=[1103909674337105], i=0)
[2022-06-08 16:54:26.044578] WARN [SQL.JO] add_table (ob_join_order.cpp:1877) [58443][0][YB42ACC83C1A-0005DFBA1498F8F8-0-0] [lt=6] [dc=0] failed to pruning_index(table_id=1103909674337105, ref_table_id=1103909674337105, ret=-4002)
[2022-06-08 16:54:26.044586] WARN [SQL.JO] generate_access_paths (ob_join_order.cpp:4378) [58443][0][YB42ACC83C1A-0005DFBA1498F8F8-0-0] [lt=5] [dc=0] failed to add table to join order(single)(ret=-4002)
[2022-06-08 16:54:26.044591] WARN [SQL.JO] generate_normal_access_path (ob_join_order.cpp:4353) [58443][0][YB42ACC83C1A-0005DFBA1498F8F8-0-0] [lt=4] [dc=0] failed to generate access paths(ret=-4002)
[2022-06-08 16:54:26.044595] WARN [SQL.JO] generate_base_paths (ob_join_order.cpp:4327) [58443][0][YB42ACC83C1A-0005DFBA1498F8F8-0-0] [lt=3] [dc=0] failed to generate normal access path(ret=-4002)
[2022-06-08 16:54:26.044602] WARN [SQL.OPT] generate_plan_tree (ob_log_plan.cpp:7203) [58443][0][YB42ACC83C1A-0005DFBA1498F8F8-0-0] [lt=3] [dc=0] failed to generate the access path for the single-table query(ret=-4002, get_stmt()->get_sql_stmt()=select * from t20220608 where
[2022-06-08 16:54:26.044616] WARN [SQL.OPT] generate_plan_for_plain_select (ob_select_log_plan.cpp:1330) [58443][0][YB42ACC83C1A-0005DFBA1498F8F8-0-0] [lt=13] [dc=0] failed to generate plan tree for plain select(ret=-4002)
[2022-06-08 16:54:26.044621] WARN [SQL.OPT] generate_raw_plan (ob_select_log_plan.cpp:1311) [58443][0][YB42ACC83C1A-0005DFBA1498F8F8-0-0] [lt=4] [dc=0] failed to generate plan for plin select.(ret=-4002)
[2022-06-08 16:54:26.044624] WARN [SQL.OPT] generate_plan (ob_select_log_plan.cpp:1539) [58443][0][YB42ACC83C1A-0005DFBA1498F8F8-0-0] [lt=2] [dc=0] fail to generate raw plan(ret=-4002)
[2022-06-08 16:54:26.044627] WARN [SQL.OPT] optimize (ob_optimizer.cpp:52) [58443][0][YB42ACC83C1A-0005DFBA1498F8F8-0-0] [lt=2] [dc=0] failed to perform optimization(ret=-4002)
[2022-06-08 16:54:26.044631] WARN [SQL] optimize_stmt (ob_sql.cpp:2557) [58443][0][YB42ACC83C1A-0005DFBA1498F8F8-0-0] [lt=3] [dc=0] Failed to optimize logical plan(ret=-4002)
[2022-06-08 16:54:26.044635] WARN [SQL] generate_physical_plan (ob_sql.cpp:2143) [58443][0][YB42ACC83C1A-0005DFBA1498F8F8-0-0] [lt=3] [dc=0] Failed to optimizer stmt(ret=-4002)
[2022-06-08 16:54:26.044690] WARN [SQL] handle_physical_plan (ob_sql.cpp:4067) [58443][0][YB42ACC83C1A-0005DFBA1498F8F8-0-0] [lt=3] [dc=0] Failed to generate plan(ret=-4002, result.get_exec_context().need_disconnect()=false)
[2022-06-08 16:54:26.044701] WARN [SQL] handle_text_query (ob_sql.cpp:1655) [58443][0][YB42ACC83C1A-0005DFBA1498F8F8-0-0] [lt=10] [dc=0] fail to handle physical plan(ret=-4002)
[2022-06-08 16:54:26.044706] WARN [SQL] stmt_query (ob_sql.cpp:197) [58443][0][YB42ACC83C1A-0005DFBA1498F8F8-0-0] [lt=3] [dc=0] fail to handle text query(stmt=select * from t20220608 where
[2022-06-08 16:54:26.044717] WARN [SERVER] test_and_save_retry_state (ob_query_retry_ctrl.cpp:527) [58443][0][YB42ACC83C1A-0005DFBA1498F8F8-0-0] [lt=4] [dc=0] do not need retry(client_ret=-4002, err=-4002, expected_stmt=true, THIS_WORKER.get_timeout_ts()=1654678526039103, retry_type_=0, result.get_stmt_type()=1, result.get_exec_context().need_change_timeout_ret()=true, session->get_retry_info().get_last_query_retry_err()=0)
[2022-06-08 16:54:26.044734] INFO [SERVER] ob_query_retry_ctrl.cpp:554 [58443][0][YB42ACC83C1A-0005DFBA1498F8F8-0-0] [lt=9] [dc=0] check if need retry(client_ret=-4002, err=-4002, retry_type_=0, retry_times=1, multi_stmt_item={is_part_of_multi_stmt:true, seq_num:0, sql:"select * from t20220608 where
[2022-06-08 16:54:26.044757] WARN [SERVER] do_process (obmp_query.cpp:705) [58443][0][YB42ACC83C1A-0005DFBA1498F8F8-0-0] [lt=18] [dc=0] run stmt_query failed, check if need retry(ret=-4002, cli_ret=-4002, retry_ctrl_.need_retry()=0, sql=select * from t20220608 where
[2022-06-08 16:54:26.044777] WARN [SERVER] do_process (obmp_query.cpp:828) [58443][0][YB42ACC83C1A-0005DFBA1498F8F8-0-0] [lt=12] [dc=0] query failed(ret=-4002, retry_ctrl_.need_retry()=0)
[2022-06-08 16:54:26.044785] INFO [SERVER] obmp_packet_sender.cpp:220 [58443][0][YB42ACC83C1A-0005DFBA1498F8F8-0-0] [lt=3] [dc=0] sending error packet(err=-4002, extra_err_info=0x7fe37515c740)
问题原因
- OceanBase 数据库产品限制这样的判断条件上限为 64 个(OB_USER_MAX_ROWKEY_COLUMN_NUMBER )。
- select 1,2 列也是如此,update 和 delete 都是同一现象。
解决方法
建议应用侧修改 SQL 代码避开。