title: Multi-level subquery with JPA CriteriaBuilder
body: <p>I have the following JPA entities</p>
  
  <pre><code>@Entity
  @Table(name="application_user")
  public class ApplicationUser {
      @Id
      @Column(name="user_id")
      private String userid;
  
      @Column(name="last_write_time")
      private Instant lastWrite;
  
      //other fields omitted
  }
  
  @Entity
  @Table(name="demographic")
  public class Demographic {
      @Id
      @Column(name="user_id")
      private String userid;
  
      //primary key is a foreign key link
      @OneToOne
      @PrimaryKeyJoinColumn(name="user_id", referencedColumnName="user_id")
      private ApplicationUser user;
  
      //other fields omitted
  }
  </code></pre>
  
  <p>My goal is to retrieve all of the Demographics that contains users where the last write time is the max value in the column. I pretty much want to write the following SQL using the JPA CriteriaBUilder</p>
  
  <pre><code>select * from demographic where
    userid in (
      select userid from application_user where
        last_write in (
          select max(last_write) from application_user
        )
    )
  </code></pre>
  
  <p>I tried writing the following CriteriaBuilder Code to accomplish this goal and it compiles successfully. Note I am using the generated Metamodel classes.</p>
  
  <pre><code>CriteriaBuilder cb = em.getCriteriaBuilder();
  CriteriaQuery&lt;Demographic&gt; c = cb.createQuery(Demographic.class);
  Root&lt;Demographic&gt; root = c.from(Demographic.class);
  root.fetch(Demographic_.user, JoinType.INNER);
  
  Subquery&lt;Instant&gt; sqLatestUsers = c.subquery(Instant.class);
  Root&lt;ApplicationUser&gt; subRootLatestUsers = sqLatestUsers.from(ApplicationUser.class);
  sqLatestUsers.select(cb.greatest(subRootLatestUsers.&lt;Instant&gt;get(ApplicationUser_.LAST_WRITE)));
  Predicate predicateLatestUsers = subRootLatestUsers.get(ApplicationUser_.LAST_WRITE).in(sqLatestUsers);
  
  Subquery&lt;ApplicationUser&gt; sq = c.subquery(ApplicationUser.class);
  Root&lt;Demographic&gt; subRoot = sq.from(Demographic.class);
  sq.select(subRoot.&lt;ApplicationUser&gt;get(Demographic_.USER)).where(predicateLatestUsers);
  Predicate containsUsers = subRoot.get(Demographic_.USER).in(sq);
  
  c.select(root).where(containsUsers);
  </code></pre>
  
  <p>The code compiles and successfully deploys in Wildfly 14, but when I execute the code, the get the following error (with white space to improve readability):</p>
  
  <pre><code>Invalid path: 'generatedAlias2.user' :  Invalid path: 'generatedAlias2.user'
  ...
  Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: Invalid path: 'generatedAlias2.user' [
  select generatedAlias0 from com.company.model.Demographic as generatedAlias0 
    inner join fetch generatedAlias0.user as generatedAlias1 
      where generatedAlias2.user in (
        select generatedAlias2.user from com.company.model.Demographic as generatedAlias2 where generatedAlias3.lastWrite in (
          select max(generatedAlias3.lastWrite) from com.company.model.StarfishUser as generatedAlias3
        )
      )
  ]
  </code></pre>
  
  <p>Is chaining subqueries (nested subqueries) allowed by the JPA spec? Did I find something that is syntactically correctly but not actually allowed?</p>