升级Hibernate 6后递归原生SQL查询出现NonUniqueDiscoveredSqlAliasException

将基于spring-boot-starter-parent的Spring Boot应用从2.6.1版本升级至3.4.1，同步把Hibernate从5.x版本升级到6.6.4.Final，搭配PostgreSQL数据库后，某个测试用例抛出异常：

org.hibernate.loader.NonUniqueDiscoveredSqlAliasException: Encountered a duplicated sql alias [id_number] during auto-discovery of a native-sql query
    at org.hibernate.query.results.ResultSetMappingImpl.resolve(ResultSetMappingImpl.java:289)
    at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.resolveJdbcValuesSource(JdbcSelectExecutorStandardImpl.java:340)
    ...

该异常关联的原生递归SQL查询代码如下：

@Override
public List<ParentNodeChieldTreeDTOInterface> findAllSubNodesRecursively(NodeInterface node) {
    try {
        Session session = getSessionFactory().openSession();
        session.beginTransaction();

        String sql_query = 
                        " WITH RECURSIVE tree AS "
                      + "("
                      + " SELECT id, id_number, node_type_id, cast (array[] as BIGINT[]) AS ancestors"
                      + " FROM node" 
                                 
                      + " UNION ALL"
                                 
                      + " SELECT node.id, node.id_number, node.node_type_id, tree.ancestors || node.parent_id_number"
                      + " FROM node, tree"
                      + " WHERE node.parent_id_number = tree.id_number"
                      + ") "
                      + "SELECT DISTINCT ON (id_number) tree.id_number, * FROM tree WHERE (:Argument1 = ANY(tree.ancestors) )"
                      + "  ORDER BY tree.id_number" 
                      ;
        
        @SuppressWarnings("unchecked")
        Query<Object[]> query = session.createNativeQuery(sql_query)
                .addScalar("id", byte[].class)
                .addScalar("id_number", Long.class)
                .addScalar("node_type_id", Integer.class)
                .addScalar("ancestors",  LongArrayType.INSTANCE)
                ;
        
        Long nodeIdHex = node.getIdNumber();
        query.setParameter("Argument1", nodeIdHex, Long.class);
        
        List<Object[]> objectCollection = (List<Object[]>) query.list();
        
        List<ParentNodeChieldTreeDTOInterface> parentNodeChieldTreeDTOList = new ArrayList<ParentNodeChieldTreeDTOInterface>();
        if (objectCollection.iterator().hasNext()) {
            for (Object[] objects : objectCollection) {
                byte[] nodeUUIDArray = (byte[]) objects[0];
                long nodeIdNumber = (long) objects[1];
                int nodeTypeIdNumber = (int) objects[2];
                Object parentNodes =  objects[3];
                long[] parents =  (long[]) parentNodes;
                
                ParentNodeChieldTreeDTOInterface dtoObject = new ParentNodeChieldTreeDTO(
                        nodeUUIDArray, 
                        nodeIdNumber, 
                        nodeTypeIdNumber, 
                        parents
                        );
                
                if(logger.isDebugEnabled()) {
                    ByteBuffer buffer = ByteBuffer.wrap(nodeUUIDArray);
                    UUID nodeUUID = ByteBufferOperations.byteBufferToUUID(buffer);
                    
                    logger.debug("Cheild Node UUID       : " + nodeUUID.toString() );
                    logger.debug("Parent Nodes           : " + parents);
                    logger.debug("Cheild Node            : " + nodeUUIDArray);
                }
                parentNodeChieldTreeDTOList.add(dtoObject);
            }
        }
        session.getTransaction().commit();
        session.close();
        return parentNodeChieldTreeDTOList;
    } catch (Exception e) {
        logger.error("Exception", e);
        throw e;
    }
}

这段代码在Hibernate 5环境下运行正常。异常核心原因是：最终查询语句SELECT DISTINCT ON (id_number) tree.id_number, * FROM tree返回的结果集中，tree.id_number和*展开后的tree.id_number重复，PostgreSQL会生成两个同名列；而Hibernate 6对原生SQL查询的重复别名校验比Hibernate 5严格，因此触发异常。

PostgreSQL中node表的核心结构如下：

# \d node;
                                             Table "public.node"
      Column      |           Type           | Collation | Nullable |                 Default                 
------------------+--------------------------+-----------+----------+-----------------------------------------
...
 node_type_id     | integer                  |           | not null | 
 id_number        | bigint                   |           | not null | nextval('node_id_number_seq'::regclass)
 parent_id_number | bigint                   |           |          | 
 id               | bytea                    |           | not null | 
 parent_id        | bytea                    |           |          | 
... 

方案1：修改SQL查询语句，消除重复列（推荐） #

直接修正SQL的重复列问题，这是最彻底的解决方式：

方式1.1：移除重复的tree.id_number #

因为DISTINCT ON (id_number)已经基于id_number去重，而*已经包含该列，所以可以直接去掉前面的tree.id_number：

WITH RECURSIVE tree AS (
  SELECT id, id_number, node_type_id, cast (array[] as BIGINT[]) AS ancestors
  FROM node 
  UNION ALL
  SELECT node.id, node.id_number, node.node_type_id, tree.ancestors || node.parent_id_number
  FROM node, tree
  WHERE node.parent_id_number = tree.id_number
) 
SELECT DISTINCT ON (id_number) * FROM tree WHERE (:Argument1 = ANY(tree.ancestors) )
ORDER BY tree.id_number

方式1.2：明确列出所有需要的列 #

如果需要严格控制返回列，避免*带来的隐性问题，可以直接列出所有需要的字段：

WITH RECURSIVE tree AS (
  SELECT id, id_number, node_type_id, cast (array[] as BIGINT[]) AS ancestors
  FROM node 
  UNION ALL
  SELECT node.id, node.id_number, node.node_type_id, tree.ancestors || node.parent_id_number
  FROM node, tree
  WHERE node.parent_id_number = tree.id_number
) 
SELECT DISTINCT ON (id_number) id, id_number, node_type_id, ancestors FROM tree WHERE (:Argument1 = ANY(tree.ancestors) )
ORDER BY id_number

方案2：使用显式结果集映射 #

如果无法修改SQL，可以通过Hibernate的结果集映射为重复列指定不同别名，不过这种方式相对繁琐：

1. 定义结果集映射：

@SqlResultSetMapping(
    name = "TreeResultMapping",
    columns = {
        @ColumnResult(name = "id", type = byte[].class),
        @ColumnResult(name = "id_number_1", type = Long.class),
        @ColumnResult(name = "id_number_2", type = Long.class),
        @ColumnResult(name = "node_type_id", type = Integer.class),
        @ColumnResult(name = "ancestors", type = Long[].class)
    }
)

2. 修改SQL为重复列指定别名：

WITH RECURSIVE tree AS (
  SELECT id, id_number, node_type_id, cast (array[] as BIGINT[]) AS ancestors
  FROM node 
  UNION ALL
  SELECT node.id, node.id_number, node.node_type_id, tree.ancestors || node.parent_id_number
  FROM node, tree
  WHERE node.parent_id_number = tree.id_number
) 
SELECT DISTINCT ON (id_number) tree.id_number AS id_number_1, tree.* FROM tree WHERE (:Argument1 = ANY(tree.ancestors) )
ORDER BY tree.id_number

3. 创建查询时引用该映射：

Query<Object[]> query = session.createNativeQuery(sql_query, "TreeResultMapping");

方案3：关闭Hibernate 6的重复别名校验（不推荐） #

通过配置参数关闭重复别名的校验，仅作为临时绕过方案，可能引发后续隐性问题：
在application.properties中添加：

spring.jpa.properties.hibernate.query.native_query.allow_duplicate_scalar_aliases=true

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