SemanticException: Select item at position 1 in select list has no alias when using JPQL subselect

Given Entities:

@Entity
@Data
public class Collection {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;

  private String createdBy;

  @OneToMany(mappedBy = "collection")
  private List<CollectionAccess> collectionAccesses;

}

@Entity
@Data
public class CollectionAccess {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;

  @ManyToOne
  @JoinColumn(
      foreignKey =
          @ForeignKey(
              value = ConstraintMode.PROVIDER_DEFAULT,
              name = "fk_collectionaccess_collection_collections_id"))
  private Collection collection;

  private Integer accessType;
  private LocalDateTime expirationAtUtc;
}

Given database:

select id, created_by from collection;
+----+------------+
| id | created_by |
+----+------------+
| 40 |   ABC123   |
+----+------------+

select * from collection_access;
+----+-------------+----------------------------+---------------+
| id | access_type | expiration_at_utc          | collection_id |
+----+-------------+----------------------------+---------------+
|  2 |           0 | 2011-12-03 03:15:30.000000 |            40 |
|  3 |           1 | 2011-12-03 03:15:30.000000 |            40 |
+----+-------------+----------------------------+---------------+

Writing this query cause an SemanticException:

em.createQuery(
          "SELECT t FROM (SELECT c FROM Collection c "
                  + "LEFT JOIN c.collectionAccesses ca WHERE c.id = ?1 "
                  + "AND (c.createdBy = ?2 OR (ca.accessType = ?3 AND ca.expirationAtUtc <= ?4))) t "
                  + "JOIN FETCH t.collectionAccesses ",
          Collection.class)
      .setParameter(1, id)
      .setParameter(2, "ABC123")
      .setParameter(3, 1)
      .setParameter(4, now)
      getSingleResult();


java.lang.IllegalArgumentException: org.hibernate.query.SemanticException: Select item at position 1 in select list has no alias (aliases are required in CTEs and in subqueries occurring in from clause)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:143) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]

How to correctly write a SELECT query FROM a subselect in JPQL/Hibernate?

You can't use subquery in the FROM clause in JPQL. You have 2 options:

1. use native query
2. try to rewrite the query as to replace the subselect in the FROM clause

However, after analyzing your query, I don't quite understand why you need that subselect. I think it would work just by doing like this:

    em.createQuery(
          "SELECT c FROM Collection c "
                  + "JOIN FETCH c.collectionAccesses ca WHERE c.id = ?1 "
                  + "AND (c.createdBy = ?2 OR c.id = (SELECT c.id FROM Collection c LEFT JOIN c.collectionAccesses ca WHERE ca.accessType = ?3 AND ca.expirationAtUtc <= ?4))",
          Collection.class)
      .setParameter(1, id)
      .setParameter(2, "ABC123")
      .setParameter(3, 1)
      .setParameter(4, now)
      .getSingleResult();

You can't escape 2 select queries in this case. Try executing this in sql console, and you'll see that you get one row:

SELECT * FROM collection c LEFT JOIN collection_access ca ON c.id = ca.collection_id WHERE c.id = 40 AND (c.created_by = 'DUMMY' OR (ca.access_type = 1 OR ca.expiration_at_utc <= now()))

In the case I presented in JPQL, the query will return 2 rows, both having the same collection entity. So either pick my version or the one recommended by Olivier, they are quite the same. Also, please note that my JPQL query will work only if you are sure only one result should be returned, in case there will be more results you could change the sequence:

em.createQuery(
          "SELECT c FROM Collection c "
                  + "JOIN FETCH c.collectionAccesses ca WHERE c.id = ?1 "
                  + "AND (c.createdBy = ?2 OR c.id IN (SELECT c.id FROM Collection c LEFT JOIN c.collectionAccesses ca WHERE ca.accessType = ?3 AND ca.expirationAtUtc <= ?4))",
          Collection.class)
      .setParameter(1, id)
      .setParameter(2, "ABC123")
      .setParameter(3, 1)
      .setParameter(4, now)
      .getResultList();

In this case, getResultList() will return an array of objects, with that you'll avoid NoResultException as well as multiple results by query.