title: JPA2: JOIN (Self Join) issue
body: <p>I am trying to fetch records from a table using Join (self join). I am using EclipseLink JPA2 and MySQL. Here is the native query that I using.</p>
  
  <pre><code>    select * from mytable as detail join (select max(timestamp) as maxtimestamp from mytable where id=" + theUser.getId() + " group by hnumber order by maxtimestamp limit " + &lt;offset&gt; + "," + &lt;iTotalRecords&gt; + ") as topconv on detail.timestamp=topconv.maxtimestamp order by detail.timestamp
  </code></pre>
  
  <p>I have following pojo class</p>
  
  <pre><code>    @Entity
      @Table(name="mytable")
      public class MyTable {
  
          @Id
          @GeneratedValue(strategy=GenerationType.SEQUENCE)
          @Column(name="mytableid")
          private long lMyTableId;
  
          @ManyToOne
          @JoinColumn(name="id", referencedColumnName="id")
          private User user; //this object has id, name and so on fields.
  
          @Column(name="timestamp", length=15, nullable=false)
          private String strTimestamp;
  
          @Column(name="hnumber", nullable=true)
          private String hNumber;
  
          //Getters and Setters.
  
      }
  </code></pre>
  
  <p>Here is method to fetch the records</p>
  
  <pre><code>    public List&lt;MyTable&gt; fetchRecords(User theUser, int iTotalRecords, long offset) throws Exception {
          if(null == theUser) {
              throw new Exception("Invalid input.");
          }
  
          EntityManager entityManager = getEntityManager();
          if(false == entityManager.getTransaction().isActive()) {
              entityManager.getTransaction().begin();
          }
  
          try {
              Query query = entityManager.createQuery("select detail from MyTable as detail join (select topconv, max(topconv.timestamp) as maxtimestamp from MyTable where topconv.User= :User group by topconv.hnumber order by topconv.maxtimestamp) as topconv on detail.timestamp=topconv.maxtimestamp order by detail.timestamp");
              query.setParameter("User", theUser.getUser());
              @SuppressWarnings("unchecked")
              List&lt;SMS&gt; resultList = query.getResultList();
              return resultList;
          } catch(Throwable th) {
              throw new Exception(th.getMessage());
          } finally {
              closeEntityManager();
          }
      }
  </code></pre>
  
  <p>To get and close EntityManager</p>
  
  <pre><code>    private EntityManagerFactory _entityManagerFactory = Persistence.createEntityManagerFactory("JPATest");
      protected EntityManager getEntityManager() {
          _entityManager = _entityManagerFactory.createEntityManager();
          return _entityManager;
      }
  
      protected void closeEntityManager() {
          try {
              if(null != _entityManager) {
                  _entityManager.close();
              }
          } catch(Throwable th){}
      }
  </code></pre>
  
  <p>My application supports multi-threading. I getting the following error.</p>
  
  <pre><code>    java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager: 
      Exception Description: Syntax error parsing [select detail from MyTable as detail join (select topconv, max(topconv.timestamp) as maxtimestamp from MyTable where topconv.User= :User group by topconv.hnumber order by topconv.maxtimestamp) as topconv on detail.timestamp=topconv.maxtimestamp order by detail.timestamp]. 
      The join association path is not a valid expression.
      An identification variable must be defined for a JOIN expression.
      The query contains a malformed ending.
  </code></pre>
  
  <p>Looks like JOIN is not supported in JPA2 query, any other alternative please suggest. Any help is appreciated.</p>