问题描述
在 Oracle 数据库迁移至 OceanBase 4.2.1 Oracle 租户的过程中，不少研发同学会遇到 SQL 兼容性问题。最近技术部门反馈，一条SQL在Oracle中查询正常，OceanBase 中查询报错ORA-01722: invalid number，表字段是VARCHAR2类型，使用regexp_like过滤数据后，再通过to_number转换成数字类型。
今天就带大家深度拆解这个问题的根源，以及对应的解决方案。
报错的SQL语句如下:
select a.orgunitguid companyid,a.orgname companyname from agentarea a where a.ORGLEVEL = 2 and regexp_like(a.orgunitguid,'^[0-9]+$') and (to_number(a.ORGUNITGUID) BETWEEN 3 and 614 or to_number(a.ORGUNITGUID) BETWEEN 769 and 1624 or to_number(a.ORGUNITGUID) BETWEEN 1748 and 1926 or to_number(a.ORGUNITGUID) BETWEEN 2850 and 3619 or to_number(a.ORGUNITGUID) BETWEEN 3680 and 5615);
环境信息
源库Oracle版本：Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
目标库：OBServer版本号 Server version: OceanBase 4.2.1.11 (r111020032025051316-76704a9622f7e60a1a0b3f962ee2b36a731c644a) (Built May 13 2025 16:41:50)
租户模式：Oracle
问题排查
1. OB库执行报错如下
查看表DDL
obclient(OMS_USER@ecs_t01)[OMS_USER]> show create table agentarea\G
*************************** 1. row ***************************
TABLE: AGENTAREA
CREATE TABLE: CREATE TABLE "AGENTAREA" (
"ORGUNITGUID" VARCHAR2(38) NOT NULL ENABLE,
"PARENTGUID" VARCHAR2(38),
"ORGNAME" VARCHAR2(80) NOT NULL ENABLE,
"ORGLEVEL" NUMBER,
"PATH" VARCHAR2(32767),
"ORGAREA" VARCHAR2(32767)
) COMPRESS FOR ARCHIVE REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
1 row in set (0.005 sec)
obclient(OMS_USER@ecs_t01)[ECS]> select a.orgunitguid companyid,a.orgname companyname from agentarea a
-> where a.ORGLEVEL = 2 and regexp_like(a.orgunitguid,'^[0-9]+$')
-> and
-> (to_number(a.ORGUNITGUID) BETWEEN 3 and 614 or
-> to_number(a.ORGUNITGUID) BETWEEN 769 and 1624 );
ORA-01722: invalid number
[10.79.189.78:2882] [2025-10-22 19:36:14.632670] [YB420A4FBD4E-00063AB339BDB385-0-0]
observer 根据trace_id过滤的关键日志：fail to calc function to_number(ret=-5114, ori_str=system)
observer.log.20251022193708351:[2025-10-22 19:36:14.565436] WDIAG [SQL.ENG] eval_trim (ob_expr_trim.cpp:781) [958194][T1002_L0_G0][T1002][YB420A4FBD4E-00063AB339BDB385-0-0] [lt=6][errcode=0] trim_type is ltrim
observer.log.20251022193708351:[2025-10-22 19:36:14.632199] WDIAG [SQL.ENG] calc_tonumber_expr (ob_expr_to_number.cpp:129) [958194][T1002_L0_G0][T1002][YB420A4FBD4E-00063AB339BDB385-0-0] [lt=1][errcode=-5114] fail to calc function to_number(ret=-5114, ori_str=system)
observer.log.20251022193708351:[2025-10-22 19:36:14.632223] WDIAG [SQL] get_comparator_operands (ob_expr_operator.h:1184) [958194][T1002_L0_G0][T1002][YB420A4FBD4E-00063AB339BDB385-0-0] [lt=23][errcode=-5114] left eval failed(ret=-5114)
observer.log.20251022193708351:[2025-10-22 19:36:14.632230] WDIAG [SQL.ENG] def_relational_eval_func (ob_expr_cmp_func.cpp:57) [958194][T1002_L0_G0][T1002][YB420A4FBD4E-00063AB339BDB385-0-0] [lt=7][errcode=-5114] failed to eval args(ret=-5114)
observer.log.20251022193708351:[2025-10-22 19:36:14.632243] WDIAG [SQL.ENG] calc_and_exprN (ob_expr_and.cpp:81) [958194][T1002_L0_G0][T1002][YB420A4FBD4E-00063AB339BDB385-0-0] [lt=13][errcode=-5114] eval arg 0 failed(ret=-5114)
observer.log.20251022193708351:[2025-10-22 19:36:14.632250] WDIAG [SQL.ENG] calc_or_exprN (ob_expr_or.cpp:125) [958194][T1002_L0_G0][T1002][YB420A4FBD4E-00063AB339BDB385-0-0] [lt=7][errcode=-5114] eval arg 0 failed(ret=-5114)
observer.log.20251022193708351:[2025-10-22 19:36:14.632263] WDIAG [SQL.ENG] filter_row (ob_operator.cpp:1300) [958194][T1002_L0_G0][T1002][YB420A4FBD4E-00063AB339BDB385-0-0] [lt=12][errcode=-5114] expr evaluate failed(ret=-5114, eval_ctx={batch_idx:0, batch_size:1, max_batch_size:0, frames_:0x7f8c4396eb20}, expr=0x7fbca6d1c260)
observer.log.20251022193708351:[2025-10-22 19:36:14.632281] WDIAG [SQL.ENG] get_next_row (ob_operator.cpp:1097) [958194][T1002_L0_G0][T1002][YB420A4FBD4E-00063AB339BDB385-0-0] [lt=18][errcode=-5114] filter row failed(ret=-5114, type=32, op="PHY_SUBPLAN_SCAN")
observer.log.20251022193708351:[2025-10-22 19:36:14.632291] WDIAG [SQL.EXE] get_next_row (ob_execute_result.cpp:147) [958194][T1002_L0_G0][T1002][YB420A4FBD4E-00063AB339BDB385-0-0] [lt=8][errcode=-5114] get next row from operator failed(ret=-5114)
observer.log.20251022193708351:[2025-10-22 19:36:14.632296] WDIAG [SQL] inner_get_next_row (ob_result_set.cpp:411) [958194][T1002_L0_G0][T1002][YB420A4FBD4E-00063AB339BDB385-0-0] [lt=5][errcode=-5114] get next row from exec result failed(ret=-5114)
observer.log.20251022193708351:[2025-10-22 19:36:14.632310] WDIAG [SERVER] response_query_result (ob_query_driver.cpp:306) [958194][T1002_L0_G0][T1002][YB420A4FBD4E-00063AB339BDB385-0-0] [lt=13][errcode=-5114] fail to iterate and response(ret=-5114, row_num=8, can_retry=false)
observer.log.20251022193708351:[2025-10-22 19:36:14.632317] INFO [SERVER] response_query_result (ob_query_driver.cpp:316) [958194][T1002_L0_G0][T1002][YB420A4FBD4E-00063AB339BDB385-0-0] [lt=6] The query has already returned partial results to the client and cannot be retried(ret=-5114, ret="OB_INVALID_NUMERIC")
observer.log.20251022193708351:[2025-10-22 19:36:14.632324] WDIAG [SERVER] response_result (ob_sync_plan_driver.cpp:104) [958194][T1002_L0_G0][T1002][YB420A4FBD4E-00063AB339BDB385-0-0] [lt=6][errcode=-5114] response query result fail(ret=-5114)
observer.log.20251022193708351:[2025-10-22 19:36:14.632467] INFO [SQL.EXE] end_stmt (ob_sql_trans_control.cpp:1170) [958194][T1002_L0_G0][T1002][YB420A4FBD4E-00063AB339BDB385-0-0] [lt=5] end stmt(ret=0, plain_select=true, stmt_type=1, savepoint=0, tx_desc={this:0x7fc8ae250260, tx_id:{txid:0}, state:1, addr:"10.79.189.78:2882", tenant_id:1002, session_id:3221712900, assoc_session_id:3221712900, xid:NULL, xa_mode:"", xa_start_addr:"0.0.0.0:0", access_mode:0, tx_consistency_type:0, isolation:1, snapshot_version:{val:18446744073709551615, v:3}, snapshot_scn:0, active_scn:0, op_sn:1, alloc_ts:1761132400954310, active_ts:-1, commit_ts:-1, finish_ts:-1, timeout_us:-1, lock_timeout_us:-1, expire_ts:9223372036854775807, coord_id:{id:-1}, parts:[], exec_info_reap_ts:0, commit_version:{val:18446744073709551615, v:3}, commit_times:0, commit_cb:null, cluster_id:-1, cluster_version:0, flags_.SHADOW:false, flags_.INTERRUPTED:false, flags_.BLOCK:false, flags_.REPLICA:false, can_elr:true, cflict_txs:[], abort_cause:0, commit_expire_ts:0, commit_task_.is_registered():false, modified_tables:[], ref:1}, trans_result={incomplete:false, parts:[], touched_ls_list:[], cflict_txs:[]}, rollback=true, need_rollback=true, session={this:0x7fbb72ac01f8, id:3221712900, deser:false, tenant:"ecs_t01", tenant_id:1002, effective_tenant:"ecs_t01", effective_tenant_id:1002, database:"ECS", user:"OMS_USER", consistency_level:3, session_state:2, autocommit:false, tx:0x7fc8ae250260}, exec_ctx.get_errcode()=-5114)
observer.log.20251022193708351:[2025-10-22 19:36:14.632579] WDIAG [SERVER] response_result (ob_sync_plan_driver.cpp:126) [958194][T1002_L0_G0][T1002][YB420A4FBD4E-00063AB339BDB385-0-0] [lt=62][errcode=-5114] close result set fail(cret=-5114)
observer.log.20251022193708351:[2025-10-22 19:36:14.632652] INFO [SERVER] send_error_packet (obmp_packet_sender.cpp:319) [958194][T1002_L0_G0][T1002][YB420A4FBD4E-00063AB339BDB385-0-0] [lt=9] sending error packet(ob_error=-5114, client error=1722, extra_err_info=NULL, lbt()="0x139d7265 0xace6c60 0xac931c2 0x55bf817 0x557db30 0x55763a6 0x55692da 0x5553d42 0xa99bf74 0x13c9570f 0x7fcf7b94ef1b 0x7fcf7b8861c0")
observer.log.20251022193708351:[2025-10-22 19:36:14.632719] INFO [SERVER] send_error_packet (obmp_packet_sender.cpp:502) [958194][T1002_L0_G0][T1002][YB420A4FBD4E-00063AB339BDB385-0-0] [lt=12] dump txn free route audit_record(value=1048581, session->get_sessid()=3221712900, session->get_proxy_sessid()=743020606433511071)
observer.log.20251022193708351:[2025-10-22 19:36:14.632731] WDIAG [SERVER] do_process (obmp_query.cpp:808) [958194][T1002_L0_G0][T1002][YB420A4FBD4E-00063AB339BDB385-0-0] [lt=9][errcode=-5114] execute query fail(ret=-5114, timeout_timestamp=1761136574492994)
2. 将where 条件中的to_number数字转换函数条件去掉SQL执行正常
根据报错日志的值查询表中的记录ori_str=system
obclient(OMS_USER@ecs_t01)[ECS]> select a.orgunitguid companyid,a.orgname companyname from agentarea a where a.ORGUNITGUID='system';
+-----------+-----------------------------------------------------+
| COMPANYID | COMPANYNAME |
+-----------+-----------------------------------------------------+
| system | 测试 |
+-----------+-----------------------------------------------------+
1 row in set (0.187 sec)
-- to_number数字转换函数条件去掉SQL执行正常
select a.orgunitguid companyid,a.orgname companyname from agentarea a where a.ORGLEVEL = 2 and regexp_like(a.orgunitguid,'^[0-9]+$') ;
192 rows in set (0.537 sec)
3. explain extended查看SQL的OB执行计划
obclient(OMS_USER@ecs_t01)[OMS_USER]> explain extended select a.orgunitguid companyid,a.orgname companyname from agentarea a where a.ORGLEVEL = 2 and regexp_like(a.orgunitguid,'^[0-9]+$') and (to_number(a.ORGUNITGUID) BETWEEN 3 and 614 or to_number(a.ORGUNITGUID) BETWEEN 769 and 1624 or to_number(a.ORGUNITGUID) BETWEEN 1748 and 1926 or to_number(a.ORGUNITGUID) BETWEEN 2850 and 3619 or to_number(a.ORGUNITGUID) BETWEEN 3680 and 5615);
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| =============================================== |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| |
| ----------------------------------------------- |
| |0 |TABLE FULL SCAN|A |390 |453 | |
| =============================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([A.ORGUNITGUID(0x7ed1ec026bd0)], [A.ORGNAME(0x7ed1ec048330)]), filter([A.ORGLEVEL(0x7ed1ec025570) = 2(0x7ed1ec024e20)], [(T_OP_OR, TO_NUMBER(cast(A.ORGUNITGUID(0x7ed1ec026bd0), |
| VARCHAR2(1048576 ))(0x7ed1ec03b810))(0x7ed1ec0297d0) >= 3(0x7ed1ec02ba10) AND TO_NUMBER(cast(A.ORGUNITGUID(0x7ed1ec026bd0), VARCHAR2(1048576 ))(0x7ed1ec03b810))(0x7ed1ec0297d0) |
| = 769(0x7ed1ec02f190) |
| AND TO_NUMBER(cast(A.ORGUNITGUID(0x7ed1ec026bd0), VARCHAR2(1048576 ))(0x7ed1ec03b810))(0x7ed1ec0297d0) = 1748(0x7ed1ec0328b0) AND TO_NUMBER(cast(A.ORGUNITGUID(0x7ed1ec026bd0), VARCHAR2(1048576 ))(0x7ed1ec03b810))(0x7ed1ec0297d0) |
| = 2850(0x7ed1ec036090) |
| AND TO_NUMBER(cast(A.ORGUNITGUID(0x7ed1ec026bd0), VARCHAR2(1048576 ))(0x7ed1ec03b810))(0x7ed1ec0297d0) = 3680(0x7ed1ec0397b0) AND TO_NUMBER(cast(A.ORGUNITGUID(0x7ed1ec026bd0), VARCHAR2(1048576 ))(0x7ed1ec03b810))(0x7ed1ec0297d0) |
| =3 AND
TO_NUMBER("A"."ORGUNITGUID")=769
AND TO_NUMBER("A"."ORGUNITGUID")
查看Oracle 的执行计划，Oracle是根据谓词条件REGEXP_LIKE ("A"."ORGUNITGUID",'^[0-9]+$')先过滤，OB执行的顺序是to_number过滤在前。导致查询到的字符无法转换为数字类型。
规避方案
针对这个问题，最直接有效的方式是通过改写SQL的方式，主要内部改写可能会还原这个SQL，所以增加一个no_rewrite的hint，具体方案如下：
select /*+no_rewrite*/ b.orgunitguid companyid,b.orgname companyname from (select a.orgunitguid,a.orgname from agentarea a where a.ORGLEVEL = 2 and regexp_like(a.orgunitguid,'^[0-9]+$')) b where (to_number(b.ORGUNITGUID) BETWEEN 3 and 614 or to_number(b.ORGUNITGUID) BETWEEN 769 and 1624 or to_number(b.ORGUNITGUID) BETWEEN 1748 and 1926 or to_number(b.ORGUNITGUID) BETWEEN 2850 and 3619 or to_number(b.ORGUNITGUID) BETWEEN 3680 and 5615);
如果你的团队在 Oracle 迁移 OceanBase 过程中遇到类似兼容性问题，欢迎在评论区留言交流，下次见！
