--- title: "OceanBase 数据库 Oracle 模式下 SQL 涉及 GROUP BY 函数列 GROUP BY 函数列，报错 ORA-00979: 'SYS.A.NUM2' not a GROUP BY expression-OceanBase数据库使用指南" description: "了解OceanBase数据库在实际应用中关于OceanBase 数据库 Oracle 模式下 SQL 涉及 GROUP BY 函数列 GROUP BY 函数列，报错 ORA-00979: 'SYS.A.NUM2' not a GROUP BY expression相关的常见问题和使用技巧，帮助您快速解决OceanBase 数据库 Oracle 模式下 SQL 涉及 GROUP BY 函数列 GROUP BY 函数列，报错 ORA-00979: 'SYS.A.NUM2' not a GROUP BY expression的难题。" --- 切换语言 - 简体中文 - English 划线反馈 # 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`，示例如下。 ```shell 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() 函数的列。 ```shell #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() 函数，使用用别名规避。 ```shell 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) ``` Previous [NLJ 条件下压计划执行结果错误](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000422798) Next [OceanBase 数据库 V4.x 版本中字符集为 gbk 的 Oracle 模式租户下匿名块含 dbms_sql.parse 函数，执行时报错 ob_convert failed(ret=-4258 的原因和解决方法](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000001888933) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线