Here is my query,

  @Query(
      nativeQuery = true,
      value =
          """
WITH RankedOrders AS (
        SELECT *,
        ROW_NUMBER() OVER (PARTITION BY order_status ORDER BY create_date_time DESC) AS rn
        FROM {h-schema}orders
        WHERE customer_code = :customerCode AND order_status not in :orderStatusNotIn
        )
    SELECT o.*, oi.*
        FROM RankedOrders o
        LEFT JOIN {h-schema}order_item oi ON o.id = oi.order_id
    WHERE rn <= :count
""")
  List<OrderEntity> getLatestRankedOrdersByCustomerCodeAndStatusNotIn(
      @Param("count") int count,
      @Param("customerCode") String customerCode,
      List<OrderStatus> orderStatusNotIn);

Both order and orderItems contains the column id which make the id column ambiguous as a result jpa is throwing this exception. This query works well with the previous version of hibernate. After updating the hibernate version to 6.4.1 final I am facing this issue.

I have change the query like this, which doesn’t solve my problem. Instead of Select * I have Select with oo.* as I have show this type of solution in a reddit blog.

  @Query(
      nativeQuery = true,
      value =
          """
WITH RankedOrders AS (
        SELECT oo.*,
        ROW_NUMBER() OVER (PARTITION BY order_status ORDER BY create_date_time DESC) AS rn
        FROM {h-schema}orders oo
        WHERE customer_code = :customerCode AND order_status not in :orderStatusNotIn
        )
    SELECT o.*, oi.*
        FROM RankedOrders o
        LEFT JOIN {h-schema}order_item oi ON o.id = oi.order_id
    WHERE rn <= :count
""")
  List<OrderEntity> getLatestRankedOrdersByCustomerCodeAndStatusNotIn(
      @Param("count") int count,
      @Param("customerCode") String customerCode,
      List<OrderStatus> orderStatusNotIn);

Is there any solution without changing the id alias name?