HSQLDB数据库中GROUP BY与ORDER BY联用执行结果异常问题

问题产生原因 #

• 字段引用不规范：SQL语句未给关联表指定别名，GROUP BY后字段写了多余的括号且使用全限定名(PUBLIC.DEPARTMENT.NAME)，部分版本HSQLDB解析器会将其识别为表达式而非直接的字段引用，导致分组逻辑异常，也容易出现字段归属识别偏差。
• 别名识别限制：低版本HSQLDB不支持在ORDER BY子句中直接引用SELECT阶段定义的聚合字段别名TOTAL，解析器会优先从原始表中查找TOTAL字段，找不到就会触发报错或者排序逻辑不符合预期。
• 分组逻辑隐患：当前仅按部门名称分组，若存在同名部门，会把不同ID的部门数据合并，不符合业务逻辑。

可行解决方案 #

方案1：改写SQL消除解析歧义（无需升级数据库版本） #

给关联表添加别名，移除GROUP BY字段的多余括号，分组时加入部门主键避免同名部门合并，ORDER BY直接使用聚合函数避免别名识别问题，改写后的SQL如下：

SELECT d.name AS depname, SUM(e.salary) AS TOTAL, MEDIAN(e.salary) AS AVERAGE 
FROM EMPLOYEE e
INNER JOIN DEPARTMENT d ON d.id = e.department
GROUP BY d.id, d.name
ORDER BY SUM(e.salary) DESC

方案2：嵌套子查询使用别名排序 #

如果需要保留ORDER BY直接用别名的写法，可以嵌套一层子查询，外层排序逻辑所有HSQLDB版本都能正常识别：

SELECT depname, TOTAL, AVERAGE
FROM (
    SELECT d.name AS depname, SUM(e.salary) AS TOTAL, MEDIAN(e.salary) AS AVERAGE 
    FROM EMPLOYEE e
    INNER JOIN DEPARTMENT d ON d.id = e.department
    GROUP BY d.id, d.name
) dept_salary_stat
ORDER BY TOTAL DESC

方案3：升级HSQLDB版本 #

如果是版本兼容性导致的别名识别问题，将HSQLDB升级到2.6及以上正式版本，即可原生支持ORDER BY直接引用SELECT阶段定义的聚合字段别名，原来的SQL稍作字段引用规范调整后即可正常执行。

内容的提问来源于stack exchange，提问作者Ekaterina Yurkina