Oracle 19c内层查询正常，嵌套count(*)时报ORA-00979原因咨询

问题场景 #

在Oracle 19c中，包含ROLLUP、HAVING子句的内层查询单独执行完全正常，但将其嵌套进select count(*) from (...)结构后，立即触发ORA-00979: not a GROUP BY expression错误，需求是统计内层查询返回的行数。

测试查询如下：

-- QUERY
select count(*) from (
    SELECT
      CASE WHEN GROUPING_ID(t3.accessory_name, D.name, NVL(D.computer_price, 9999) || NVL(t3.accessory_price, 9999)) != 0 THEN COUNT(*) END "Records",
      t3.accessory_name "accessory name",
      D.name "device name",
      SUM(D.computer_price) "computer price",
      SUM(D.laptop_price) "laptop price",
      SUM(t3.accessory_price) "accessory price",
      CASE WHEN GROUPING_ID(t3.accessory_name, D.name, NVL(D.computer_price, 9999) || NVL(t3.accessory_price, 9999)) = 0 THEN ANY_VALUE(t3.accessory_info) END "accessory info"
    FROM (
      SELECT
        computer_id id,
        computer_name name,
        computer_info info,
        computer_price computer_price,
        null laptop_price
      FROM computer
      UNION ALL
      SELECT
        laptop_id id,
        laptop_name name,
        JSON_VALUE(laptop_info, '$.model') info,
        null computer_price,
        laptop_price laptop_price
      FROM laptop
    ) D
    JOIN accessory t3 on t3.accessory_id = D.id  
    GROUP BY
      ROLLUP (t3.accessory_name, D.name, NVL(D.computer_price, 9999) || NVL(t3.accessory_price, 9999))
    HAVING
      (GROUPING_ID(t3.accessory_name, D.name, NVL(D.computer_price, 9999) || NVL(t3.accessory_price, 9999))=3)
    ORDER BY NVL(t3.accessory_name, 0), NVL(D.name, 0), NVL(D.computer_price, 9999) || NVL(t3.accessory_price, 9999) DESC
);

以下修改可使嵌套查询正常运行：

• 移除HAVING子句中的拼接表达式NVL(D.computer_price, 9999) || NVL(t3.accessory_price, 9999)
• 直接移除HAVING子句
• 将UNION ALL中laptop子查询的JSON_VALUE(laptop_info, '$.model') info改为null info
• 移除UNION ALL其中一个分支

测试表及数据脚本：

-- CREATE TEST TABLES
create table computer(computer_id NUMBER, computer_name varchar2(50), computer_info varchar2(50), computer_price NUMBER);
create table laptop(laptop_id NUMBER, laptop_name varchar2(50), laptop_info varchar2(50), laptop_price NUMBER);
create table accessory(accessory_id NUMBER, accessory_name varchar2(50), accessory_info varchar2(50), accessory_price NUMBER);

-- INSERT TEST DATA
insert into computer (computer_id, computer_name, computer_info, computer_price) values (1, 'computer 1', 'some info about computer 1', 10);
insert into computer (computer_id, computer_name, computer_info, computer_price) values (2, 'computer 2', 'some info about computer 2', 20);
insert into computer (computer_id, computer_name, computer_info, computer_price) values (3, 'computer 3', 'some info about computer 3', 30);

insert into laptop (laptop_id, laptop_name, laptop_info, laptop_price) values (1, 'laptop 1', '{''model'': ''model 1''}', 15);
insert into laptop (laptop_id, laptop_name, laptop_info, laptop_price) values (2, 'laptop 2', '{''model'': ''model 2''}', 25);
insert into laptop (laptop_id, laptop_name, laptop_info, laptop_price) values (3, 'laptop 3', '{''model'': ''model 3''}', 35);

insert into accessory (accessory_id, accessory_name, accessory_info, accessory_price) values (1, 'accessory 1', 'accessory 1 info', 1);
insert into accessory (accessory_id, accessory_name, accessory_info, accessory_price) values (2, 'accessory 2', 'accessory 2 info', 2);
insert into accessory (accessory_id, accessory_name, accessory_info, accessory_price) values (3, 'accessory 3', 'accessory 3 info', 3);

错误原因解析 #

核心是Oracle优化器在处理嵌套查询时的视图合并逻辑导致的分组表达式误判：

1. 单独执行内层查询时：Oracle能正确识别ROLLUP中的分组表达式，HAVING子句中的GROUPING_ID参数与GROUP BY的ROLLUP参数完全匹配，优化器可以明确所有SELECT列要么是分组列，要么是聚合函数，因此不会触发ORA-00979。
2. 嵌套进count(*)时：优化器会尝试对查询进行重写（比如将外层count(*)与内层查询合并），此时：
   • ROLLUP中的拼接表达式NVL(D.computer_price, 9999) || NVL(t3.accessory_price, 9999)属于复杂计算列，优化器在合并视图时可能无法正确关联它与GROUP BY的分组逻辑；
   • UNION ALL的两个分支中，info列的来源不同：一个是直接的varchar2列，另一个是JSON_VALUE函数返回的字符串，虽然类型兼容，但优化器在处理分组时可能误判该列未被正确聚合或分组，进而牵连到整个GROUP BY的合法性检查。

你的几个有效修改本质都是降低了优化器解析的复杂度：

• 移除HAVING中的拼接表达式：消除了优化器对分组表达式的关联歧义；
• 移除HAVING：直接去掉了触发误判的条件；
• 将JSON_VALUE改为null：让UNION ALL分支的info列结构完全一致，避免类型解析冲突；
• 移除一个UNION ALL分支：消除了分支间的表达式差异，让优化器能正确处理分组逻辑。

临时解决办法 #

如果需要保留原查询结构，可以给内层查询添加/*+ NO_MERGE */提示，阻止优化器合并视图，强制其先执行内层查询再统计行数：

select count(*) from (
    -- 内层查询内容保持不变
    SELECT
      CASE WHEN GROUPING_ID(t3.accessory_name, D.name, NVL(D.computer_price, 9999) || NVL(t3.accessory_price, 9999)) != 0 THEN COUNT(*) END "Records",
      t3.accessory_name "accessory name",
      D.name "device name",
      SUM(D.computer_price) "computer price",
      SUM(D.laptop_price) "laptop price",
      SUM(t3.accessory_price) "accessory price",
      CASE WHEN GROUPING_ID(t3.accessory_name, D.name, NVL(D.computer_price, 9999) || NVL(t3.accessory_price, 9999)) = 0 THEN ANY_VALUE(t3.accessory_info) END "accessory info"
    FROM (
      SELECT
        computer_id id,
        computer_name name,
        computer_info info,
        computer_price computer_price,
        null laptop_price
      FROM computer
      UNION ALL
      SELECT
        laptop_id id,
        laptop_name name,
        JSON_VALUE(laptop_info, '$.model') info,
        null computer_price,
        laptop_price laptop_price
      FROM laptop
    ) D
    JOIN accessory t3 on t3.accessory_id = D.id  
    GROUP BY
      ROLLUP (t3.accessory_name, D.name, NVL(D.computer_price, 9999) || NVL(t3.accessory_price, 9999))
    HAVING
      (GROUPING_ID(t3.accessory_name, D.name, NVL(D.computer_price, 9999) || NVL(t3.accessory_price, 9999))=3)
    ORDER BY NVL(t3.accessory_name, 0), NVL(D.name, 0), NVL(D.computer_price, 9999) || NVL(t3.accessory_price, 9999) DESC
) /*+ NO_MERGE */; -- 添加该提示阻止视图合并

内容的提问来源于stack exchange，提问作者Oleksandr Onoshko