为何触发NonUniqueDiscoveredSqlAliasException异常？SQL问题排查

问题场景 #

现有book与book_version两张表，需筛选满足以下条件的书籍：

• 版本为最新版本，或v.generated_from_another为NULL（表示非稳定版本）
• 指定类型

执行以下原生SQL在MySQL中可正常运行，但触发Hibernate异常：

SELECT b.id, b.book_name, v.id, v.majorVersion, v.minorVersion, v.generated_from_another IS NULL as isNotStable
FROM book b
         INNER JOIN book_version v ON b.id = v.book_id
WHERE b.type = :typeId
  and (v.id in (SELECT max(v.id)
                FROM book b
                         INNER JOIN book_version v ON b.id = v.book_id
                WHERE v.generated_from_another IS NULL
                  AND b.type = :typeId
                GROUP BY b.id) or v.generated_from_another is not null)
ORDER BY b.book_name, v.majorVersion DESC, v.minorVersion DESC;

报错详情 #

Caused by:
org.hibernate.loader.custom.NonUniqueDiscoveredSqlAliasException:
Encountered a duplicated sql alias [id] during auto-discovery of a
native-sql query  at
org.hibernate.loader.custom.CustomLoader.validateAliases(CustomLoader.java:520)
~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]   at
org.hibernate.loader.custom.CustomLoader.autoDiscoverTypes(CustomLoader.java:497)
~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]   at
org.hibernate.loader.Loader.preprocessResultSet(Loader.java:2343)
~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]   at
org.hibernate.loader.Loader.getResultSet(Loader.java:2299)
~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]

问题原因 #

你的SQL查询中同时选取了b.id（书籍ID）和v.id（版本ID），这两个字段在查询结果集中的默认别名都是id。Hibernate在自动映射原生SQL结果时，要求所有列的别名必须唯一，重复的别名会导致它无法正确识别和映射字段，从而抛出NonUniqueDiscoveredSqlAliasException异常。

解决方案 #

给重复的列指定唯一的别名，修改后的SQL如下：

SELECT b.id as book_id, b.book_name, v.id as version_id, v.majorVersion, v.minorVersion, v.generated_from_another IS NULL as isNotStable
FROM book b
         INNER JOIN book_version v ON b.id = v.book_id
WHERE b.type = :typeId
  and (v.id in (SELECT max(v.id)
                FROM book b
                         INNER JOIN book_version v ON b.id = v.book_id
                WHERE v.generated_from_another IS NULL
                  AND b.type = :typeId
                GROUP BY b.id) or v.generated_from_another is not null)
ORDER BY b.book_name, v.majorVersion DESC, v.minorVersion DESC;

内容的提问来源于stack exchange，提问作者Daniela Morais