NonUniqueDiscoveredSqlAliasException被错误抛出

Question

我有一个有点大的查询，当我在上运行它时，它返回正确的结果：

SELECT a.id, a.name, 'ANALYSIS' AS type, NULL, NULL, NULL FROM Analysis a WHERE a.category_neoId IS NULL 

UNION 

SELECT bp.neoId, bp.name, 'BUSINESS_PERFORMANCE' AS type, sc.defaultPermission, sp.flags, sp.securityEntity_neoId FROM BPQuery bp LEFT JOIN SecurityController sc ON bp.userSecurityController_neoId = sc.neoId LEFT JOIN SecurityPermission sp ON bp.userSecurityController_neoId = sp.controller_neoId WHERE bp.category_neoId IS NULL 

UNION 

SELECT bpc.neoId, bpc.name, 'BUSINESS_PERFORMANCE_CONSOLIDATED' AS type, NULL, NULL, NULL FROM BPConsolidatedQuery bpc WHERE bpc.category_neoId IS NULL 

UNION 

SELECT ip.neoId, ip.name, 'INDICATORS_PANEL' AS type, sc.defaultPermission, sp.flags, sp.securityEntity_neoId FROM NeoDashBoard ip LEFT JOIN SecurityController sc ON ip.userSecurityController_neoId = sc.neoId LEFT JOIN SecurityPermission sp ON ip.userSecurityController_neoId = sp.controller_neoId WHERE ip.category_neoId IS NULL 

UNION 

SELECT r.neoId, r.name, 'REPORT' AS type, sc.defaultPermission, sp.flags, sp.securityEntity_neoId FROM NeoReport r LEFT JOIN SecurityController sc ON r.userSecurityController_neoId = sc.neoId LEFT JOIN SecurityPermission sp ON r.userSecurityController_neoId = sp.controller_neoId WHERE r.category_neoId IS NULL

但是，当我在entityManager.createNativeQuery(thisHugeQuery).getResultList()上运行它时，它会引发以下异常：

org.hibernate.loader.custom.NonUniqueDiscoveredSqlAliasException: Encountered a duplicated sql alias [] during auto-discovery of a native-sql query

我真的找不到任何真正的查询副本。SecurityPermission和SecurityController是惟一的重复别名(sc和sp)，但据我所知，由于我没有从中选择，只连接和引用相同的表，Hibernate正确识别它不应该是一个问题。

Answer 1

这是因为您没有最后3列的列名。为这三列中的每一列提供一个列别名，这应该有效。

顺便说一句，除非尝试只返回不同的行，否则可以考虑使用UNION。

Answer 2

您可以尝试给出第一个查询的最后三列NULL别名。否则，这些列都不是名称。

SELECT a.id, 
       a.NAME, 
       'ANALYSIS' AS type, 
       NULL as 'defaultpermission', 
       NULL as 'flags', 
       NULL as 'securityentity_neoid'
FROM   analysis a 
WHERE  a.category_neoid IS NULL 
UNION 
SELECT bp.neoid, 
       bp.NAME, 
       'BUSINESS_PERFORMANCE' AS type, 
       sc.defaultpermission, 
       sp.flags, 
       sp.securityentity_neoid 
FROM   bpquery bp 
       LEFT JOIN securitycontroller sc 
              ON bp.usersecuritycontroller_neoid = sc.neoid 
       LEFT JOIN securitypermission sp 
              ON bp.usersecuritycontroller_neoid = sp.controller_neoid 
WHERE  bp.category_neoid IS NULL 
UNION 
SELECT bpc.neoid, 
       bpc.NAME, 
       'BUSINESS_PERFORMANCE_CONSOLIDATED' AS type, 
       NULL, 
       NULL, 
       NULL 
FROM   bpconsolidatedquery bpc 
WHERE  bpc.category_neoid IS NULL 
UNION 
SELECT ip.neoid, 
       ip.NAME, 
       'INDICATORS_PANEL' AS type, 
       sc.defaultpermission, 
       sp.flags, 
       sp.securityentity_neoid 
FROM   neodashboard ip 
       LEFT JOIN securitycontroller sc 
              ON ip.usersecuritycontroller_neoid = sc.neoid 
       LEFT JOIN securitypermission sp 
              ON ip.usersecuritycontroller_neoid = sp.controller_neoid 
WHERE  ip.category_neoid IS NULL 
UNION 
SELECT r.neoid, 
       r.NAME, 
       'REPORT' AS type, 
       sc.defaultpermission, 
       sp.flags, 
       sp.securityentity_neoid 
FROM   neoreport r 
       LEFT JOIN securitycontroller sc 
              ON r.usersecuritycontroller_neoid = sc.neoid 
       LEFT JOIN securitypermission sp 
              ON r.usersecuritycontroller_neoid = sp.controller_neoid 
WHERE  r.category_neoid IS NULL