首批通过分布式安全可靠测评，为关键业务系统打造
划线反馈
OceanBase 数据库 Oracle 模式下 SQL 涉及 GROUP BY 函数列 GROUP BY 函数列，报错 ORA-00979: 'SYS.A.NUM2' not a GROUP BY expression
更新时间：2026-06-03 03:01
分享
适用版本：V4.2.x内容类型：Troubleshoot
问题现象
SQL 执行报错 ORA-00979: 'SYS.A.NUM2' not a GROUP BY expression，示例如下。
obclient> select a.num1,decode(a.num2,0,decode(a.num3,(-1),2,0),a.num2) cc from test a group by num1 ,decode(a.num2,0,decode(a.num3,(-1),2,0),a.num2) order by decode(a.num2,0,decode(a.num3,(-1),2,0),a.num2);
ORA-00979: 'SYS.A.NUM2' not a GROUP BY expression
关键诊断信息
触发条件
ORDER BY 子句中引用了存在于 GROUP BY 子句中的列（OceanBase 数据库没识别比较复杂的 decode() 函数）。
日志信息
关键日志报错 child visit failed(ret=-5218, i=0, type=596, name="T_FUN_SYS_ORA_DECODE")，没识别这个嵌套的 decode() 函数的列。
#grep 'xxxxx-xxxxx-xxxxx-xxxxx' observer.log.20241228*
observer.log.20241228204741911:[2024-12-28 20:45:40.488987] INFO [SQL.JO] compute_table_location (ob_join_order.cpp:277) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=10] succeed to calculate base table sharding info(table_id=18446744073709551614, ref_table_id=114, is_global_index=false)
observer.log.20241228204741911:[2024-12-28 20:45:40.490978] INFO [SHARE.SCHEMA] retrieve_table_schema (ob_schema_retrieve_utils.ipp:306) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=13] retrieve table schema succeed(*table_schema={simple_table_schema:{tenant_id:1002, database_id:201006, tablegroup_id:18446744073709551615, table_id:507235, association_table_id:18446744073709551615, in_offline_ddl_white_list:false, table_name:"TEST", session_id:0, index_type:0, table_type:3, table_mode:{table_mode_flag:0, pk_mode:2, table_state_flag:0, view_created_method_flag:0, table_organization_mode:1, auto_increment_mode:0, rowid_mode:0, view_column_filled_flag:0}, tablespace_id:18446744073709551615, data_table_id:0, name_casemode:-1, schema_version:1735389934573584, part_level:0, part_option:{part_func_type:0, part_func_expr:"", part_num:1, auto_part:false, auto_part_size:0}, sub_part_option:{part_func_type:0, part_func_expr:"", part_num:0, auto_part:false, auto_part_size:0}, partition_num:0, def_subpartition_num:0, partition_array:null, def_subpartition_array:null, hidden_partition_array:null, index_status:1, duplicate_scope:0, encryption:"", encrypt_key:"", master_key_id:18446744073709551615, sub_part_template_flags:0, get_tablet_id():{id:203248}, max_dependency_version:-1, object_status:1, is_force_view:false, truncate_version:-1}, max_used_column_id:18, sess_active_time:0, rowkey_column_num:1, index_column_num:0, rowkey_split_pos:0, block_size:16384, is_use_bloomfilter:false, progressive_merge_num:0, tablet_size:134217728, pctfree:0, load_type:0, index_using_type:0, def_type:1, charset_type:2, collation_type:46, index_status:1, partition_status:0, code_version:1, comment:"", pk_comment:"", create_host:"", tablegroup_name:"", compressor_type:6, row_store_type:1, store_format:15, expire_info:"", view_schema:{view_definition:"", check_option:"none", is_updatable:"false", is_materialized:"false", character_set_client:0, collation_connection:"invalid_type"}, autoinc_column_id:0, auto_increment:1, read_only:false, simple_index_infos:[], mv_tid_array:null, base_table_ids:[], rowkey_info:{columns:null, capacity:0}, partition_key_info:{columns:null, capacity:0}, column_cnt:0, table_dop:1, constraints:null, column_array:null, aux_vp_tid_array:[], define_user_id:200003, aux_lob_meta_tid:18446744073709551615, aux_lob_piece_tid:18446744073709551615, name_generated_type:0, lob_inrow_threshold:4096}, is_deleted=false)
observer.log.20241228204741911:[2024-12-28 20:45:40.493271] INFO [SQL.JO] compute_table_location (ob_join_order.cpp:277) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=51] succeed to calculate base table sharding info(table_id=18446744073709551614, ref_table_id=115, is_global_index=false)
observer.log.20241228204741911:[2024-12-28 20:45:40.496412] INFO [SQL.JO] compute_table_location (ob_join_order.cpp:277) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=16] succeed to calculate base table sharding info(table_id=18446744073709551614, ref_table_id=149, is_global_index=false)
observer.log.20241228204741911:[2024-12-28 20:45:40.497578] INFO [STORAGE] check_snapshot_readable_with_cache (ob_tablet.cpp:6081) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=14] refresh ddl data cache(ret=0, tablet_meta_.ls_id_={id:1}, tablet_id={id:149}, ddl_data_cache_={redefined:false, schema_version:0, snapshot_version:0})
observer.log.20241228204741911:[2024-12-28 20:45:40.499280] INFO [SQL.JO] compute_table_location (ob_join_order.cpp:277) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=13] succeed to calculate base table sharding info(table_id=18446744073709551614, ref_table_id=167, is_global_index=false)
observer.log.20241228204741911:[2024-12-28 20:45:40.501406] INFO [SQL.JO] compute_table_location (ob_join_order.cpp:277) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=13] succeed to calculate base table sharding info(table_id=18446744073709551614, ref_table_id=207, is_global_index=false)
observer.log.20241228204741911:[2024-12-28 20:45:40.503374] INFO [SQL.JO] compute_table_location (ob_join_order.cpp:277) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=14] succeed to calculate base table sharding info(table_id=18446744073709551614, ref_table_id=255, is_global_index=false)
observer.log.20241228204741911:[2024-12-28 20:45:40.504030] INFO [STORAGE] check_snapshot_readable_with_cache (ob_tablet.cpp:6081) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=14] refresh ddl data cache(ret=0, tablet_meta_.ls_id_={id:1}, tablet_id={id:255}, ddl_data_cache_={redefined:false, schema_version:0, snapshot_version:0})
observer.log.20241228204741911:[2024-12-28 20:45:40.504104] INFO [SHARE.SCHEMA] get_table_schema (ob_schema_service_sql_impl.cpp:5422) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=12] get not core table schema succeed(schema_status={tenant_id:1002, snapshot_timestamp:-1, readable_schema_version:-1}, table_id=507235, table_schema->get_table_name_str()=TEST)
observer.log.20241228204741911:[2024-12-28 20:45:40.504179] INFO [SHARE.SCHEMA] get_database_id (ob_schema_getter_guard.cpp:1597) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=12] database not exist(tenant_id=1002, database_name=NUM1)
observer.log.20241228204741911:[2024-12-28 20:45:40.504189] WDIAG [SQL.RESV] get_database_id (ob_schema_checker.cpp:432) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=9][errcode=-5154] database is not exist(tenant_id=1002, database_name=NUM1, ret=-5154)
observer.log.20241228204741911:[2024-12-28 20:45:40.504223] WDIAG [SQL.RESV] check_first_node_name (ob_dml_resolver.cpp:895) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=7][errcode=0] node_name input null(ret=0)
observer.log.20241228204741911:[2024-12-28 20:45:40.504235] INFO [SHARE.SCHEMA] get_database_id (ob_schema_getter_guard.cpp:1597) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=10] database not exist(tenant_id=1002, database_name=A)
observer.log.20241228204741911:[2024-12-28 20:45:40.504242] WDIAG [SQL.RESV] get_database_id (ob_schema_checker.cpp:432) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=6][errcode=-5154] database is not exist(tenant_id=1002, database_name=A, ret=-5154)
observer.log.20241228204741911:[2024-12-28 20:45:40.504251] INFO [SHARE.SCHEMA] get_database_id (ob_schema_getter_guard.cpp:1597) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=6] database not exist(tenant_id=1002, database_name=NUM2)
observer.log.20241228204741911:[2024-12-28 20:45:40.504256] WDIAG [SQL.RESV] get_database_id (ob_schema_checker.cpp:432) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=5][errcode=-5154] database is not exist(tenant_id=1002, database_name=NUM2, ret=-5154)
observer.log.20241228204741911:[2024-12-28 20:45:40.504260] WDIAG [SQL.RESV] check_first_node_name (ob_dml_resolver.cpp:895) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=3][errcode=0] node_name input null(ret=0)
observer.log.20241228204741911:[2024-12-28 20:45:40.504266] INFO [SHARE.SCHEMA] get_database_id (ob_schema_getter_guard.cpp:1597) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=5] database not exist(tenant_id=1002, database_name=A)
observer.log.20241228204741911:[2024-12-28 20:45:40.504269] WDIAG [SQL.RESV] get_database_id (ob_schema_checker.cpp:432) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=2][errcode=-5154] database is not exist(tenant_id=1002, database_name=A, ret=-5154)
observer.log.20241228204741911:[2024-12-28 20:45:40.504274] INFO [SHARE.SCHEMA] get_database_id (ob_schema_getter_guard.cpp:1597) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=4] database not exist(tenant_id=1002, database_name=NUM3)
observer.log.20241228204741911:[2024-12-28 20:45:40.504277] WDIAG [SQL.RESV] get_database_id (ob_schema_checker.cpp:432) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=3][errcode=-5154] database is not exist(tenant_id=1002, database_name=NUM3, ret=-5154)
observer.log.20241228204741911:[2024-12-28 20:45:40.504282] INFO [SHARE.SCHEMA] get_database_id (ob_schema_getter_guard.cpp:1597) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=4] database not exist(tenant_id=1002, database_name=A)
observer.log.20241228204741911:[2024-12-28 20:45:40.504285] WDIAG [SQL.RESV] get_database_id (ob_schema_checker.cpp:432) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=2][errcode=-5154] database is not exist(tenant_id=1002, database_name=A, ret=-5154)
observer.log.20241228204741911:[2024-12-28 20:45:40.504290] INFO [SHARE.SCHEMA] get_database_id (ob_schema_getter_guard.cpp:1597) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=4] database not exist(tenant_id=1002, database_name=NUM2)
observer.log.20241228204741911:[2024-12-28 20:45:40.504293] WDIAG [SQL.RESV] get_database_id (ob_schema_checker.cpp:432) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=3][errcode=-5154] database is not exist(tenant_id=1002, database_name=NUM2, ret=-5154)
observer.log.20241228204741911:[2024-12-28 20:45:40.504371] INFO [SHARE.SCHEMA] get_database_id (ob_schema_getter_guard.cpp:1597) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=5] database not exist(tenant_id=1002, database_name=A)
observer.log.20241228204741911:[2024-12-28 20:45:40.504378] WDIAG [SQL.RESV] get_database_id (ob_schema_checker.cpp:432) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=6][errcode=-5154] database is not exist(tenant_id=1002, database_name=A, ret=-5154)
observer.log.20241228204741911:[2024-12-28 20:45:40.504382] INFO [SHARE.SCHEMA] get_database_id (ob_schema_getter_guard.cpp:1597) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=3] database not exist(tenant_id=1002, database_name=NUM1)
observer.log.20241228204741911:[2024-12-28 20:45:40.504387] WDIAG [SQL.RESV] get_database_id (ob_schema_checker.cpp:432) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=4][errcode=-5154] database is not exist(tenant_id=1002, database_name=NUM1, ret=-5154)
observer.log.20241228204741911:[2024-12-28 20:45:40.504402] WDIAG [SQL.RESV] check_first_node_name (ob_dml_resolver.cpp:895) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=3][errcode=0] node_name input null(ret=0)
observer.log.20241228204741911:[2024-12-28 20:45:40.504408] INFO [SHARE.SCHEMA] get_database_id (ob_schema_getter_guard.cpp:1597) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=5] database not exist(tenant_id=1002, database_name=A)
observer.log.20241228204741911:[2024-12-28 20:45:40.504411] WDIAG [SQL.RESV] get_database_id (ob_schema_checker.cpp:432) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=3][errcode=-5154] database is not exist(tenant_id=1002, database_name=A, ret=-5154)
observer.log.20241228204741911:[2024-12-28 20:45:40.504416] INFO [SHARE.SCHEMA] get_database_id (ob_schema_getter_guard.cpp:1597) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=4] database not exist(tenant_id=1002, database_name=NUM2)
observer.log.20241228204741911:[2024-12-28 20:45:40.504419] WDIAG [SQL.RESV] get_database_id (ob_schema_checker.cpp:432) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=3][errcode=-5154] database is not exist(tenant_id=1002, database_name=NUM2, ret=-5154)
observer.log.20241228204741911:[2024-12-28 20:45:40.504424] WDIAG [SQL.RESV] check_first_node_name (ob_dml_resolver.cpp:895) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=5][errcode=0] node_name input null(ret=0)
observer.log.20241228204741911:[2024-12-28 20:45:40.504427] INFO [SHARE.SCHEMA] get_database_id (ob_schema_getter_guard.cpp:1597) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=2] database not exist(tenant_id=1002, database_name=A)
observer.log.20241228204741911:[2024-12-28 20:45:40.504431] WDIAG [SQL.RESV] get_database_id (ob_schema_checker.cpp:432) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=4][errcode=-5154] database is not exist(tenant_id=1002, database_name=A, ret=-5154)
observer.log.20241228204741911:[2024-12-28 20:45:40.504435] INFO [SHARE.SCHEMA] get_database_id (ob_schema_getter_guard.cpp:1597) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=3] database not exist(tenant_id=1002, database_name=NUM3)
observer.log.20241228204741911:[2024-12-28 20:45:40.504439] WDIAG [SQL.RESV] get_database_id (ob_schema_checker.cpp:432) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=4][errcode=-5154] database is not exist(tenant_id=1002, database_name=NUM3, ret=-5154)
observer.log.20241228204741911:[2024-12-28 20:45:40.504442] INFO [SHARE.SCHEMA] get_database_id (ob_schema_getter_guard.cpp:1597) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=3] database not exist(tenant_id=1002, database_name=A)
observer.log.20241228204741911:[2024-12-28 20:45:40.504447] WDIAG [SQL.RESV] get_database_id (ob_schema_checker.cpp:432) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=4][errcode=-5154] database is not exist(tenant_id=1002, database_name=A, ret=-5154)
observer.log.20241228204741911:[2024-12-28 20:45:40.504450] INFO [SHARE.SCHEMA] get_database_id (ob_schema_getter_guard.cpp:1597) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=3] database not exist(tenant_id=1002, database_name=NUM2)
observer.log.20241228204741911:[2024-12-28 20:45:40.504455] WDIAG [SQL.RESV] get_database_id (ob_schema_checker.cpp:432) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=5][errcode=-5154] database is not exist(tenant_id=1002, database_name=NUM2, ret=-5154)
observer.log.20241228204741911:[2024-12-28 20:45:40.504507] WDIAG [SQL.RESV] check_first_node_name (ob_dml_resolver.cpp:895) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=2][errcode=0] node_name input null(ret=0)
observer.log.20241228204741911:[2024-12-28 20:45:40.504514] INFO [SHARE.SCHEMA] get_database_id (ob_schema_getter_guard.cpp:1597) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=6] database not exist(tenant_id=1002, database_name=A)
observer.log.20241228204741911:[2024-12-28 20:45:40.504517] WDIAG [SQL.RESV] get_database_id (ob_schema_checker.cpp:432) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=3][errcode=-5154] database is not exist(tenant_id=1002, database_name=A, ret=-5154)
observer.log.20241228204741911:[2024-12-28 20:45:40.504523] INFO [SHARE.SCHEMA] get_database_id (ob_schema_getter_guard.cpp:1597) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=5] database not exist(tenant_id=1002, database_name=NUM2)
observer.log.20241228204741911:[2024-12-28 20:45:40.504526] WDIAG [SQL.RESV] get_database_id (ob_schema_checker.cpp:432) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=2][errcode=-5154] database is not exist(tenant_id=1002, database_name=NUM2, ret=-5154)
observer.log.20241228204741911:[2024-12-28 20:45:40.504528] WDIAG [SQL.RESV] check_first_node_name (ob_dml_resolver.cpp:895) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=2][errcode=0] node_name input null(ret=0)
observer.log.20241228204741911:[2024-12-28 20:45:40.504534] INFO [SHARE.SCHEMA] get_database_id (ob_schema_getter_guard.cpp:1597) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=5] database not exist(tenant_id=1002, database_name=A)
observer.log.20241228204741911:[2024-12-28 20:45:40.504536] WDIAG [SQL.RESV] get_database_id (ob_schema_checker.cpp:432) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=2][errcode=-5154] database is not exist(tenant_id=1002, database_name=A, ret=-5154)
observer.log.20241228204741911:[2024-12-28 20:45:40.504542] INFO [SHARE.SCHEMA] get_database_id (ob_schema_getter_guard.cpp:1597) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=5] database not exist(tenant_id=1002, database_name=NUM3)
observer.log.20241228204741911:[2024-12-28 20:45:40.504545] WDIAG [SQL.RESV] get_database_id (ob_schema_checker.cpp:432) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=2][errcode=-5154] database is not exist(tenant_id=1002, database_name=NUM3, ret=-5154)
observer.log.20241228204741911:[2024-12-28 20:45:40.504550] INFO [SHARE.SCHEMA] get_database_id (ob_schema_getter_guard.cpp:1597) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=4] database not exist(tenant_id=1002, database_name=A)
observer.log.20241228204741911:[2024-12-28 20:45:40.504553] WDIAG [SQL.RESV] get_database_id (ob_schema_checker.cpp:432) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=2][errcode=-5154] database is not exist(tenant_id=1002, database_name=A, ret=-5154)
observer.log.20241228204741911:[2024-12-28 20:45:40.504558] INFO [SHARE.SCHEMA] get_database_id (ob_schema_getter_guard.cpp:1597) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=4] database not exist(tenant_id=1002, database_name=NUM2)
observer.log.20241228204741911:[2024-12-28 20:45:40.504560] WDIAG [SQL.RESV] get_database_id (ob_schema_checker.cpp:432) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=2][errcode=-5154] database is not exist(tenant_id=1002, database_name=NUM2, ret=-5154)
observer.log.20241228204741911:[2024-12-28 20:45:40.504662] WDIAG visit (ob_group_by_checker.cpp:850) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=5][errcode=-5218] ORA-00979: 'SYS.A.NUM2' not a GROUP BY expression
observer.log.20241228204741911:[2024-12-28 20:45:40.504671] WDIAG [SQL.RESV] preorder_accept (ob_raw_expr.cpp:471) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=8][errcode=-5218] visit failed(ret=-5218, type=89, name="T_REF_COLUMN")
observer.log.20241228204741911:[2024-12-28 20:45:40.504690] WDIAG [SQL.RESV] preorder_accept (ob_raw_expr.cpp:481) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=15][errcode=-5218] child visit failed(ret=-5218, i=0, type=596, name="T_FUN_SYS_ORA_DECODE")
observer.log.20241228204741911:[2024-12-28 20:45:40.504697] WDIAG [SQL.RESV] check_select_stmt (ob_group_by_checker.cpp:773) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=7][errcode=-5218] fail to check group by(i=0, ret=-5218)
observer.log.20241228204741911:[2024-12-28 20:45:40.504704] WDIAG [SQL.RESV] check_group_by (ob_group_by_checker.cpp:283) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=6][errcode=-5218] failed to check group by(ret=-5218)
observer.log.20241228204741911:[2024-12-28 20:45:40.504713] WDIAG [SQL.RESV] check_group_by (ob_select_resolver.cpp:876) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=5][errcode=-5218] failed to check group by in oracle mode
observer.log.20241228204741911:[2024-12-28 20:45:40.504719] WDIAG [SQL.RESV] resolve_normal_query (ob_select_resolver.cpp:1158) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=5][errcode=-5218] fail to exec check_group_by()(ret=-5218)
observer.log.20241228204741911:[2024-12-28 20:45:40.504722] WDIAG [SQL.RESV] resolve (ob_select_resolver.cpp:1275) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=3][errcode=-5218] resolve normal query failed(ret=-5218)
observer.log.20241228204741911:[2024-12-28 20:45:40.504729] WDIAG [SQL.RESV] select_stmt_resolver_func (ob_resolver.cpp:183) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=6][errcode=-5218] execute stmt_resolver failed(ret=-5218, parse_tree.type_=3299)
observer.log.20241228204741911:[2024-12-28 20:45:40.504743] WDIAG [SQL] generate_stmt (ob_sql.cpp:2856) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=7][errcode=-5218] failed to resolve(ret=-5218)
observer.log.20241228204741911:[2024-12-28 20:45:40.504750] WDIAG [SQL] generate_physical_plan (ob_sql.cpp:2977) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=6][errcode=-5218] Failed to generate stmt(ret=-5218, result.get_exec_context().need_disconnect()=false)
observer.log.20241228204741911:[2024-12-28 20:45:40.504757] WDIAG [SQL] handle_physical_plan (ob_sql.cpp:4826) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=6][errcode=-5218] Failed to generate plan(ret=-5218, result.get_exec_context().need_disconnect()=false)
observer.log.20241228204741911:[2024-12-28 20:45:40.504763] WDIAG [SQL] handle_text_query (ob_sql.cpp:2561) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=6][errcode=-5218] fail to handle physical plan(ret=-5218)
observer.log.20241228204741911:[2024-12-28 20:45:40.504770] WDIAG [SQL] stmt_query (ob_sql.cpp:222) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=6][errcode=-5218] fail to handle text query(stmt=select a.num1,decode(a.num2,0,decode(a.num3,(-1),2,0),a.num2) cc from test a group by num1 ,decode(a.num2,0,decode(a.num3,(-1),2,0),a.num2) order by decode(a.num2,0,decode(a.num3,(-1),2,0),a.num2), ret=-5218)
observer.log.20241228204741911:[2024-12-28 20:45:40.504779] WDIAG [SERVER] after_func (ob_query_retry_ctrl.cpp:947) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=6][errcode=-5218] [RETRY] check if need retry(v={force_local_retry:false, stmt_retry_times:0, local_retry_times:0, err_:-5218, err_:"OB_ERR_WRONG_FIELD_WITH_GROUP", retry_type:0, client_ret:-5218}, need_retry=false, THIS_WORKER.can_retry()=true, v.ctx_.multi_stmt_item_={is_part_of_multi_stmt:false, seq_num:0, sql:"select a.num1,decode(a.num2,0,decode(a.num3,(-1),2,0),a.num2) cc from test a group by num1 ,decode(a.num2,0,decode(a.num3,(-1),2,0),a.num2) order by decode(a.num2,0,decode(a.num3,(-1),2,0),a.num2)", batched_queries:NULL, is_ps_mode:false, ab_cnt:0})
observer.log.20241228204741911:[2024-12-28 20:45:40.504807] WDIAG [SERVER] do_process (obmp_query.cpp:757) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=27][errcode=-5218] run stmt_query failed, check if need retry(ret=-5218, cli_ret=-5218, retry_ctrl_.need_retry()=0, sql=select a.num1,decode(a.num2,0,decode(a.num3,(-1),2,0),a.num2) cc from test a group by num1 ,decode(a.num2,0,decode(a.num3,(-1),2,0),a.num2) order by decode(a.num2,0,decode(a.num3,(-1),2,0),a.num2))
observer.log.20241228204741911:[2024-12-28 20:45:40.504818] WDIAG [SERVER] do_process (obmp_query.cpp:880) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=6][errcode=-5218] query failed(ret=-5218, session={this:0x7f1c99b3a1f8, id:3221690598, deser:false, tenant:"jcz_oracle", tenant_id:1002, effective_tenant:"jcz_oracle", effective_tenant_id:1002, database:"SYS", user:"SYS", consistency_level:3, session_state:2, autocommit:false, tx:null}, sql=select a.num1,decode(a.num2,0,decode(a.num3,(-1),2,0),a.num2) cc from test a group by num1 ,decode(a.num2,0,decode(a.num3,(-1),2,0),a.num2) order by decode(a.num2,0,decode(a.num3,(-1),2,0),a.num2), retry_ctrl_.need_retry()=0)
observer.log.20241228204741911:[2024-12-28 20:45:40.504835] INFO [SERVER] send_error_packet (obmp_packet_sender.cpp:319) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=12] sending error packet(ob_error=-5218, client error=979, extra_err_info=NULL, lbt()="0x136a22fc 0xab97ae0 0xab47184 0x54e395b 0x54d7822 0x54ca65a 0x54b40b4 0xa852b54 0x1395d020 0x7f2ada33a6ca 0x7f2ada074edf")
observer.log.20241228204741911:[2024-12-28 20:45:40.504871] INFO [SERVER] send_error_packet (obmp_packet_sender.cpp:502) [29655][T1002_L0_G0][T1002][xxxxx-xxxxx-xxxxx-xxxxx] [lt=8] dump txn free route audit_record(value=5, session->get_sessid()=3221690598, session->get_proxy_sessid()=859790141462613531)
问题原因
错误是因为在 SQL 语句中，ORDER BY 子句中引用了不存在于 GROUP BY 子句中的列（如果只有单列即 A.NUM2）。在使用 GROUP BY 子句进行聚合查询时，ORDER BY 子句中引用的列必须是 GROUP BY 子句中出现的列或者聚合函数的参数。因此，需要修改 ORDER BY 子句中的列为 GROUP BY 子句中已经存在的列或者聚合函数的参数。
实际情况 ORDER BY 中排序的列就是 GROUP BY 子句中出现的列的，OceanBase 数据库没识别这个嵌套的 decode() 的函数，先用别名规避。
问题的风险及影响
执行 SQL 报错。
影响租户
影响 OceanBase 数据库中的 SYS 租户和 Oracle 租户，对于 MySQL 租户无影响。
适用版本
OceanBase 数据库 V4.2.x 版本。
解决方法及规避方式
ORDER BY 中排序的列就是 GROUP BY 子句中出现的列的，OceanBase 数据库没识别这个嵌套的 decode() 函数，使用用别名规避。
obclient> select a.num1,decode(a.num2,0,decode(a.num3,(-1),2,0),a.num2) cc from test a group by num1 ,decode(a.num2,0,decode(a.num3,(-1),2,0),a.num2) order by 2;
Empty set (0.01 sec)
obclient> select a.num1,decode(a.num2,0,decode(a.num3,(-1),2,0),a.num2) cc from test a group by num1 ,decode(a.num2,0,decode(a.num3,(-1),2,0),a.num2) order by cc;
Empty set (0.00 sec)
