问题现象
执行 merge into xxxx when xxxx insert。
obclient> merge into table_a c using (select * from table_b a) on (1 = 0) when not matched then insert (a,b) values (a.a ,1);
ORA-00904: invalid identifier 'A.A' in 'field list'
obclient> explain extended merge into table_a c using (select * from table_b a) on (1 = 0) when not matched then insert (a,b) values (a.a ,1);
ORA-00904: invalid identifier 'A.A' in 'field list'
obclient> explain merge into table_a c using (select * from table_b a) on (1 = 0) when not matched then insert (a,b) values (a.a ,1);
ORA-00904: invalid identifier 'A.A' in 'field list'
关键诊断信息
触发条件
使用 merge into using insert 的时候，insert 中使用表别名.列的形式。
关键日志信息
[2024-10-17 10:42:09.130895] INFO [SHARE.SCHEMA] get_database_id (ob_schema_getter_guard.cpp:1602) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=4] database not exist(tenant_id=1002, database_name=A)
[2024-10-17 10:42:09.130909] WDIAG [SQL.RESV] get_database_id (ob_schema_checker.cpp:432) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=13][errcode=-5154] database is not exist(tenant_id=1002, database_name=A, ret=-5154)
[2024-10-17 10:42:09.130919] INFO [SHARE.SCHEMA] get_database_id (ob_schema_getter_guard.cpp:1602) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=8] database not exist(tenant_id=1002, database_name=A)
[2024-10-17 10:42:09.130925] WDIAG [SQL.RESV] get_database_id (ob_schema_checker.cpp:432) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=5][errcode=-5154] database is not exist(tenant_id=1002, database_name=A, ret=-5154)
[2024-10-17 10:42:09.130943] WDIAG [SQL.RESV] resolve_column_ref_expr (ob_merge_resolver.cpp:380) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=3][errcode=-5217] resolve single table column item failed(ret=-5217)
[2024-10-17 10:42:09.131012] INFO [PL.CACHE] need_check_schema_version (ob_pl_cache.cpp:465) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=7] need check schema(new_schema_version=1729131237316096, cached_tenant_schema_version=1729131237316096)
[2024-10-17 10:42:09.131028] WDIAG [PL] get_package_type (ob_pl_package_manager.cpp:531) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=7][errcode=-5544] package type not found(package_id=310001, type_name=A, ret=-5544)
[2024-10-17 10:42:09.131034] INFO [PL] search_in_standard_package (ob_pl_stmt.cpp:1405) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=7] get standard package type not exist!(ret=-5544, standard_package_id=310001, name=A)
[2024-10-17 10:42:09.131040] INFO [SHARE.SCHEMA] get_table_id (ob_schema_getter_guard.cpp:1688) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=7] table not exist(tenant_id=1002, tenant_id=1002, database_id=201006, session_id=859790141462616766, table_name=A, is_index=false, schema_version=1729131237316096, schema_mgr_tenant_id=1002)
[2024-10-17 10:42:09.131053] INFO [SHARE.SCHEMA] get_database_id (ob_schema_getter_guard.cpp:1602) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=7] database not exist(tenant_id=1002, database_name=A)
[2024-10-17 10:42:09.131057] WDIAG [PL] resolve_self_element_access (ob_pl_resolver.cpp:13970) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=7][errcode=-5543] undeclared var(access_ident={access_name:"A", access_index:-1, type:-1, params:[]}, ret=-5543)
[2024-10-17 10:42:09.131064] WDIAG resolve_self_element_access (ob_pl_resolver.cpp:13973) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=7][errcode=-5543] PLS-00201: identifier 'A' must be declared
[2024-10-17 10:42:09.131067] WDIAG [PL] resolve_access_ident (ob_pl_resolver.cpp:14310) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=7][errcode=-5543] fail to exec resolve_self_element_access(access_ident, ns, access_idxs, func)(ret=-5543, "access_ident"="access_ident", ::oceanbase::common::check_char_array(access_ident)={access_name:"A", access_index:-1, type:-1, params:[]}, "access_idxs"="access_idxs", ::oceanbase::common::check_char_array(access_idxs)=[])
[2024-10-17 10:42:09.131074] WDIAG [PL] resolve_name (ob_pl_resolver.cpp:13381) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=7][errcode=-5543] failed to resolve access ident(ret=-5543, i=0, q_name.access_idents_=[{access_name:"A", access_index:-1, type:-1, params:[]}, {access_name:"A", access_index:-1, type:-1, params:[]}])
[2024-10-17 10:42:09.131082] WDIAG [PL] resolve_var (ob_pl_resolver.cpp:13118) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=7][errcode=-5543] failed to resolve symbol(q_name={database_name:"", table_name:"A", column:"A", dblink_name:"", is_star:false, ref_expr:{item_type:"T_REF_COLUMN", result_type:{meta:{type:"", collation:"binary", coercibility:"NUMERIC"}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, calc_type:{type:"NULL", collation:"invalid_type", coercibility:"INVALID"}}, expr_info:["IS_COLUMN", "CNT_COLUMN"], rel_id:[], table_id:18446744073709551615, column_id:18446744073709551615, database_name:"", table_name:"", synonym_name:"", synonym_db_name:"", column_name:"", column_flags:0, enum_set_values:[], is_lob_column:false, is_joined_dup_column:false, is_unpivot_mocked_column:false, is_hidden:false, from_alias_table:false, is_rowkey_column:false, is_unique_key_column:false, is_mul_key_column:false, is_strict_json_column:0, srs_id:18446744073709551615, udt_set_id:0}, parents_expr_info:[], parent_aggr_level:-1, access_idents:[{access_name:"A", access_index:-1, type:-1, params:[]}, {access_name:"A", access_index:-1, type:-1, params:[]}], current_resolve_level:-1, is_access_root:true}, ret=-5543)
[2024-10-17 10:42:09.131109] WDIAG [PL] resolve_var (ob_pl_resolver.cpp:11790) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=7][errcode=-5543] failed to resolve var(q_name={database_name:"", table_name:"A", column:"A", dblink_name:"", is_star:false, ref_expr:{item_type:"T_REF_COLUMN", result_type:{meta:{type:"", collation:"binary", coercibility:"NUMERIC"}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, calc_type:{type:"NULL", collation:"invalid_type", coercibility:"INVALID"}}, expr_info:["IS_COLUMN", "CNT_COLUMN"], rel_id:[], table_id:18446744073709551615, column_id:18446744073709551615, database_name:"", table_name:"", synonym_name:"", synonym_db_name:"", column_name:"", column_flags:0, enum_set_values:[], is_lob_column:false, is_joined_dup_column:false, is_unpivot_mocked_column:false, is_hidden:false, from_alias_table:false, is_rowkey_column:false, is_unique_key_column:false, is_mul_key_column:false, is_strict_json_column:0, srs_id:18446744073709551615, udt_set_id:0}, parents_expr_info:[], parent_aggr_level:-1, access_idents:[{access_name:"A", access_index:-1, type:-1, params:[]}, {access_name:"A", access_index:-1, type:-1, params:[]}], current_resolve_level:-1, is_access_root:true}, expr=NULL, ret=-5543)
[2024-10-17 10:42:09.131124] WDIAG [PL] resolve_sequence_object (ob_pl_resolver.cpp:15162) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=7][errcode=-5543] sequence access bad field.(ret=-5543)
[2024-10-17 10:42:09.131127] WDIAG [PL] resolve_qualified_name (ob_pl_resolver.cpp:11740) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=7][errcode=-5543] failed to sequence object(q_name={database_name:"", table_name:"A", column:"A", dblink_name:"", is_star:false, ref_expr:{item_type:"T_REF_COLUMN", result_type:{meta:{type:"", collation:"binary", coercibility:"NUMERIC"}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, calc_type:{type:"NULL", collation:"invalid_type", coercibility:"INVALID"}}, expr_info:["IS_COLUMN", "CNT_COLUMN"], rel_id:[], table_id:18446744073709551615, column_id:18446744073709551615, database_name:"", table_name:"", synonym_name:"", synonym_db_name:"", column_name:"", column_flags:0, enum_set_values:[], is_lob_column:false, is_joined_dup_column:false, is_unpivot_mocked_column:false, is_hidden:false, from_alias_table:false, is_rowkey_column:false, is_unique_key_column:false, is_mul_key_column:false, is_strict_json_column:0, srs_id:18446744073709551615, udt_set_id:0}, parents_expr_info:[], parent_aggr_level:-1, access_idents:[{access_name:"A", access_index:-1, type:-1, params:[]}, {access_name:"A", access_index:-1, type:-1, params:[]}], current_resolve_level:-1, is_access_root:true}, ret=-5543)
[2024-10-17 10:42:09.131140] WDIAG [PL] resolve_sqlcode_or_sqlerrm (ob_pl_resolver.cpp:11290) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=7][errcode=-5543] failed to resolve sqlcode or sqlerrm(ret=-5543, q_name={database_name:"", table_name:"A", column:"A", dblink_name:"", is_star:false, ref_expr:{item_type:"T_REF_COLUMN", result_type:{meta:{type:"", collation:"binary", coercibility:"NUMERIC"}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, calc_type:{type:"NULL", collation:"invalid_type", coercibility:"INVALID"}}, expr_info:["IS_COLUMN", "CNT_COLUMN"], rel_id:[], table_id:18446744073709551615, column_id:18446744073709551615, database_name:"", table_name:"", synonym_name:"", synonym_db_name:"", column_name:"", column_flags:0, enum_set_values:[], is_lob_column:false, is_joined_dup_column:false, is_unpivot_mocked_column:false, is_hidden:false, from_alias_table:false, is_rowkey_column:false, is_unique_key_column:false, is_mul_key_column:false, is_strict_json_column:0, srs_id:18446744073709551615, udt_set_id:0}, parents_expr_info:[], parent_aggr_level:-1, access_idents:[{access_name:"A", access_index:-1, type:-1, params:[]}, {access_name:"A", access_index:-1, type:-1, params:[]}], current_resolve_level:-1, is_access_root:true})
[2024-10-17 10:42:09.131151] WDIAG [PL] resolve_qualified_name (ob_pl_resolver.cpp:11748) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=7][errcode=-5543] failed to resolve sqlcode or sqlerrm(ret=-5543, q_name={database_name:"", table_name:"A", column:"A", dblink_name:"", is_star:false, ref_expr:{item_type:"T_REF_COLUMN", result_type:{meta:{type:"", collation:"binary", coercibility:"NUMERIC"}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, calc_type:{type:"NULL", collation:"invalid_type", coercibility:"INVALID"}}, expr_info:["IS_COLUMN", "CNT_COLUMN"], rel_id:[], table_id:18446744073709551615, column_id:18446744073709551615, database_name:"", table_name:"", synonym_name:"", synonym_db_name:"", column_name:"", column_flags:0, enum_set_values:[], is_lob_column:false, is_joined_dup_column:false, is_unpivot_mocked_column:false, is_hidden:false, from_alias_table:false, is_rowkey_column:false, is_unique_key_column:false, is_mul_key_column:false, is_strict_json_column:0, srs_id:18446744073709551615, udt_set_id:0}, parents_expr_info:[], parent_aggr_level:-1, access_idents:[{access_name:"A", access_index:-1, type:-1, params:[]}, {access_name:"A", access_index:-1, type:-1, params:[]}], current_resolve_level:-1, is_access_root:true})
[2024-10-17 10:42:09.131169] WDIAG [PL] get_package_type (ob_pl_package_manager.cpp:531) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=7][errcode=-5544] package type not found(package_id=310001, type_name=A, ret=-5544)
[2024-10-17 10:42:09.131172] INFO [PL] search_in_standard_package (ob_pl_stmt.cpp:1405) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=7] get standard package type not exist!(ret=-5544, standard_package_id=310001, name=A)
[2024-10-17 10:42:09.131175] INFO [SHARE.SCHEMA] get_table_id (ob_schema_getter_guard.cpp:1688) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=7] table not exist(tenant_id=1002, tenant_id=1002, database_id=201006, session_id=3221568011, table_name=A, is_index=false, schema_version=1729131237316096, schema_mgr_tenant_id=1002)
[2024-10-17 10:42:09.131180] INFO [SHARE.SCHEMA] get_database_id (ob_schema_getter_guard.cpp:1602) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=7] database not exist(tenant_id=1002, database_name=A)
[2024-10-17 10:42:09.131182] WDIAG [PL] resolve_self_element_access (ob_pl_resolver.cpp:13970) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=7][errcode=-5543] undeclared var(access_ident={access_name:"A", access_index:-1, type:-1, params:[]}, ret=-5543)
[2024-10-17 10:42:09.131186] WDIAG resolve_self_element_access (ob_pl_resolver.cpp:13973) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=7][errcode=-5543] PLS-00201: identifier 'A' must be declared
[2024-10-17 10:42:09.131188] WDIAG [PL] resolve_access_ident (ob_pl_resolver.cpp:14310) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=7][errcode=-5543] fail to exec resolve_self_element_access(access_ident, ns, access_idxs, func)(ret=-5543, "access_ident"="access_ident", ::oceanbase::common::check_char_array(access_ident)={access_name:"A", access_index:-1, type:-1, params:[]}, "access_idxs"="access_idxs", ::oceanbase::common::check_char_array(access_idxs)=[])
[2024-10-17 10:42:09.131192] WDIAG [PL] resolve_name (ob_pl_resolver.cpp:13381) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=7][errcode=-5543] failed to resolve access ident(ret=-5543, i=0, q_name.access_idents_=[{access_name:"A", access_index:-1, type:-1, params:[]}, {access_name:"A", access_index:-1, type:2, params:[]}])
[2024-10-17 10:42:09.131196] WDIAG [PL] resolve_udf_without_brackets (ob_pl_resolver.cpp:11599) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=7][errcode=-5543] fail to exec resolve_name(q_name, current_block_->get_namespace(), expr_factory_, &resolve_ctx_.session_info_, access_idxs, unit_ast)(ret=-5543, "access_idxs"="access_idxs", ::oceanbase::common::check_char_array(access_idxs)=[], "q_name"="q_name", ::oceanbase::common::check_char_array(q_name)={database_name:"", table_name:"A", column:"A", dblink_name:"", is_star:false, ref_expr:{item_type:"T_REF_COLUMN", result_type:{meta:{type:"", collation:"binary", coercibility:"NUMERIC"}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, calc_type:{type:"NULL", collation:"invalid_type", coercibility:"INVALID"}}, expr_info:["IS_COLUMN", "CNT_COLUMN"], rel_id:[], table_id:18446744073709551615, column_id:18446744073709551615, database_name:"", table_name:"", synonym_name:"", synonym_db_name:"", column_name:"", column_flags:0, enum_set_values:[], is_lob_column:false, is_joined_dup_column:false, is_unpivot_mocked_column:false, is_hidden:false, from_alias_table:false, is_rowkey_column:false, is_unique_key_column:false, is_mul_key_column:false, is_strict_json_column:0, srs_id:18446744073709551615, udt_set_id:0}, parents_expr_info:[], parent_aggr_level:-1, access_idents:[{access_name:"A", access_index:-1, type:-1, params:[]}, {access_name:"A", access_index:-1, type:2, params:[]}], current_resolve_level:-1, is_access_root:true})
[2024-10-17 10:42:09.131210] WDIAG [PL] resolve_qualified_name (ob_pl_resolver.cpp:11753) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=7][errcode=-5543] failed to resolve udf without bracks(ret=-5543, q_name={database_name:"", table_name:"A", column:"A", dblink_name:"", is_star:false, ref_expr:{item_type:"T_REF_COLUMN", result_type:{meta:{type:"", collation:"binary", coercibility:"NUMERIC"}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, calc_type:{type:"NULL", collation:"invalid_type", coercibility:"INVALID"}}, expr_info:["IS_COLUMN", "CNT_COLUMN"], rel_id:[], table_id:18446744073709551615, column_id:18446744073709551615, database_name:"", table_name:"", synonym_name:"", synonym_db_name:"", column_name:"", column_flags:0, enum_set_values:[], is_lob_column:false, is_joined_dup_column:false, is_unpivot_mocked_column:false, is_hidden:false, from_alias_table:false, is_rowkey_column:false, is_unique_key_column:false, is_mul_key_column:false, is_strict_json_column:0, srs_id:18446744073709551615, udt_set_id:0}, parents_expr_info:[], parent_aggr_level:-1, access_idents:[{access_name:"A", access_index:-1, type:-1, params:[]}, {access_name:"A", access_index:-1, type:2, params:[]}], current_resolve_level:-1, is_access_root:true})
[2024-10-17 10:42:09.131222] WDIAG resolve_qualified_name (ob_pl_resolver.cpp:11762) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=7][errcode=-5543] PLS-00201: identifier 'A' must be declared
[2024-10-17 10:42:09.131233] WDIAG [SQL.RESV] resolve_external_symbol (ob_resolver_utils.cpp:7080) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=8][errcode=-5543] failed to resolve var(q_name={database_name:"", table_name:"A", column:"A", dblink_name:"", is_star:false, ref_expr:{item_type:"T_REF_COLUMN", result_type:{meta:{type:"", collation:"binary", coercibility:"NUMERIC"}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, calc_type:{type:"NULL", collation:"invalid_type", coercibility:"INVALID"}}, expr_info:["IS_COLUMN", "CNT_COLUMN"], rel_id:[], table_id:18446744073709551615, column_id:18446744073709551615, database_name:"", table_name:"", synonym_name:"", synonym_db_name:"", column_name:"", column_flags:0, enum_set_values:[], is_lob_column:false, is_joined_dup_column:false, is_unpivot_mocked_column:false, is_hidden:false, from_alias_table:false, is_rowkey_column:false, is_unique_key_column:false, is_mul_key_column:false, is_strict_json_column:0, srs_id:18446744073709551615, udt_set_id:0}, parents_expr_info:[], parent_aggr_level:-1, access_idents:[{access_name:"A", access_index:-1, type:-1, params:[]}, {access_name:"A", access_index:-1, type:2, params:[]}], current_resolve_level:-1, is_access_root:true}, ret=-5543)
[2024-10-17 10:42:09.131258] WDIAG [SQL.RESV] resolve_external_name (ob_dml_resolver.cpp:11187) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=15][errcode=-5543] failed to resolve var(q_name={database_name:"", table_name:"A", column:"A", dblink_name:"", is_star:false, ref_expr:{item_type:"T_REF_COLUMN", result_type:{meta:{type:"", collation:"binary", coercibility:"NUMERIC"}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, calc_type:{type:"NULL", collation:"invalid_type", coercibility:"INVALID"}}, expr_info:["IS_COLUMN", "CNT_COLUMN"], rel_id:[], table_id:18446744073709551615, column_id:18446744073709551615, database_name:"", table_name:"", synonym_name:"", synonym_db_name:"", column_name:"", column_flags:0, enum_set_values:[], is_lob_column:false, is_joined_dup_column:false, is_unpivot_mocked_column:false, is_hidden:false, from_alias_table:false, is_rowkey_column:false, is_unique_key_column:false, is_mul_key_column:false, is_strict_json_column:0, srs_id:18446744073709551615, udt_set_id:0}, parents_expr_info:[], parent_aggr_level:-1, access_idents:[{access_name:"A", access_index:-1, type:-1, params:[]}, {access_name:"A", access_index:-1, type:2, params:[]}], current_resolve_level:-1, is_access_root:true}, ret=-5543)
[2024-10-17 10:42:09.131276] WDIAG report_user_error_msg (ob_dml_resolver.cpp:2875) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=14][errcode=-5217] ORA-00904: invalid identifier 'A.A' in 'field list'
[2024-10-17 10:42:09.131282] WDIAG [SQL.RESV] resolve_sql_expr (ob_dml_resolver.cpp:1489) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=6][errcode=-5217] resolve columns failed(ret=-5217)
[2024-10-17 10:42:09.131290] WDIAG [SQL.RESV] resolve_insert_values (ob_del_upd_resolver.cpp:3383) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=6][errcode=-5217] resolve sql expr failed(ret=-5217)
[2024-10-17 10:42:09.131297] WDIAG [SQL.RESV] resolve_insert_clause (ob_merge_resolver.cpp:527) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=5][errcode=-5217] fail to resolve values(ret=-5217)
[2024-10-17 10:42:09.131302] WDIAG [SQL.RESV] resolve (ob_merge_resolver.cpp:85) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=5][errcode=-5217] fail to resolve insert clause(ret=-5217)
[2024-10-17 10:42:09.131306] WDIAG [SQL.RESV] stmt_resolver_func (ob_resolver.cpp:168) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=3][errcode=-5217] execute stmt_resolver failed(ret=-5217, parse_tree.type_=3301)
[2024-10-17 10:42:09.131314] WDIAG [SQL] generate_stmt (ob_sql.cpp:2826) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=3][errcode=-5217] failed to resolve(ret=-5217)
[2024-10-17 10:42:09.131320] WDIAG [SQL] generate_physical_plan (ob_sql.cpp:2947) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=5][errcode=-5217] Failed to generate stmt(ret=-5217, result.get_exec_context().need_disconnect()=false)
[2024-10-17 10:42:09.131327] WDIAG [SQL] handle_physical_plan (ob_sql.cpp:4803) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=6][errcode=-5217] Failed to generate plan(ret=-5217, result.get_exec_context().need_disconnect()=false)
[2024-10-17 10:42:09.131334] WDIAG [SQL] handle_text_query (ob_sql.cpp:2531) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=6][errcode=-5217] fail to handle physical plan(ret=-5217)
[2024-10-17 10:42:09.131340] WDIAG [SQL] stmt_query (ob_sql.cpp:222) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=6][errcode=-5217] fail to handle text query(stmt=merge into table_a c using (select * from table_b a) on (1 = 1) when not matched then insert (a,b) values (a.a ,1), ret=-5217)
[2024-10-17 10:42:09.131353] WDIAG [SERVER] after_func (ob_query_retry_ctrl.cpp:947) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=6][errcode=-5217] [RETRY] check if need retry(v={force_local_retry:false, stmt_retry_times:0, local_retry_times:0, err_:-5217, err_:"OB_ERR_BAD_FIELD_ERROR", retry_type:0, client_ret:-5217}, need_retry=false, THIS_WORKER.can_retry()=true, v.ctx_.multi_stmt_item_={is_part_of_multi_stmt:false, seq_num:0, sql:"merge into table_a c using (select * from table_b a) on (1 = 1) when not matched then insert (a,b) values (a.a ,1)", batched_queries:NULL, is_ps_mode:false, ab_cnt:0})
[2024-10-17 10:42:09.131366] WDIAG [SERVER] do_process (obmp_query.cpp:757) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=12][errcode=-5217] run stmt_query failed, check if need retry(ret=-5217, cli_ret=-5217, retry_ctrl_.need_retry()=0, sql=merge into table_a c using (select * from table_b a) on (1 = 1) when not matched then insert (a,b) values (a.a ,1))
[2024-10-17 10:42:09.131376] WDIAG [SERVER] do_process (obmp_query.cpp:879) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=7][errcode=-5217] query failed(ret=-5217, session={this:0x7fda1031c1f8, id:3221568011, deser:false, tenant:"oracle", tenant_id:1002, effective_tenant:"oracle", effective_tenant_id:1002, database:"SYS", user:"SYS", consistency_level:3, session_state:2, autocommit:false, tx:0x7fd9720e62c0}, sql=merge into table_a c using (select * from table_b a) on (1 = 1) when not matched then insert (a,b) values (a.a ,1), retry_ctrl_.need_retry()=0)
[2024-10-17 10:42:09.131393] INFO [SERVER] send_error_packet (obmp_packet_sender.cpp:319) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=11] sending error packet(ob_error=-5217, client error=904, extra_err_info=NULL, lbt()="0x12ba148c 0xa2a0710 0xa24bec9 0x52140c7 0x520ba82 0x52032a8 0x51fe695 0x9f75504 0x12e5d173 0x7fdb43df16ca 0x7fdb43b2bedf")
[2024-10-17 10:42:09.131424] INFO [SERVER] send_error_packet (obmp_packet_sender.cpp:502) [49118][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=7] dump txn free route audit_record(value=0, session->get_sessid()=3221568011, session->get_proxy_sessid()=859790141462616766)
问题原因
使用 merge into xx using xx insert xx 的时候，在 using 子句内部的表的别名不能被之后的 insert 使用。报错 ORA-00904: invalid identifier 'A.A' in 'field list'。
问题的风险及影响
执行 merge into xxxx when xxxx insert 类型的 DML 语句报错。
影响租户
影响 OceanBase 数据库中的 Oracle 租户，对于 SYS 租户和 MySQL 租户无影响。
适用版本
OceanBase 数据库 V3.x、V4.x 版本。
解决方法及规避方式
修改成如下 SQL。
-- 可以将 using 子句的别名放到子句外面，被使用
obclient [SYS]> merge into table_a c using (select * from table_b ) a on (1 = 0) when not matched then insert values (a.a , 1);
Query OK, 1 row affected (7.203 sec)
