Hi,
This problem is resolved in OPENJPA-708, fix checked in under trunk
r690823..
Catalina Wei
On Thu, Aug 28, 2008 at 7:42 AM, Ognjen Blagojevic <[EMAIL PROTECTED]>wrote:
> Hi devs,
>
> Is it allowed to have use a "sub-sub-query" in JPQL? For instance, if I
> want to select publishers with last published magazines, I can use this
> select
>
> SELECT p, m
> FROM Publisher p
> LEFT OUTER JOIN p.magazineCollection m
> WHERE m.datePublished = (SELECT MAX(m3.datePublished)
> FROM Magazine m3
> WHERE m3.idPublisher.id = p.id)
>
> But if I add another select, to make sure that ony one magazine per
> publisher is selected:
>
> SELECT p, m
> FROM Publisher p
> LEFT OUTER JOIN p.magazineCollection m
> WHERE m.id = (SELECT MAX(m2.id)
> FROM Magazine m2
> WHERE m2.idPublisher.id = p.id
> AND m2.datePublished =
> (SELECT MAX(m3.datePublished)
> FROM Magazine m3
> WHERE m3.idPublisher.id = p.id))
>
> OpenJPA 1.2.0 translates this into native (MySQL) query:
>
> SELECT t0.id, t1.id, t1.date_published, t1.id_publisher, t1.name
> FROM Publisher t0
> LEFT OUTER JOIN Magazine t1
> ON t0.id = t1.id_publisher
> WHERE (t1.id = (SELECT MAX(t4.id)
> FROM Magazine t4
> WHERE (t2.id_publisher = t0.id
> AND t2.date_published =
> (SELECT MAX(t3.date_published)
> FROM Magazine t2, Magazine t3
> WHERE (t3.id_publisher = t0.id)))))
>
> and that query throws an exception:
>
> NestedThrowables:
> com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown column
> 't2.id_publisher' in 'where clause'
>
>
> Note, in the first subquery the FROM clause declares t4, but WHERE clause
> references t2, which was daclared in the second subquery, and therefore not
> visible.
>
> Also note, the second subquery selects from two tables "magazine", which is
> unnecessary.
>
> Find the entity beans in the attachment.
>
> Configuration:
> - OpenJPA 1.2.0
> - MySQL 5.0.22
>
> Regards,
> Ognjen
>
> package magazine.model;
>
> import java.io.Serializable;
> import javax.persistence.Column;
> import javax.persistence.Entity;
> import javax.persistence.Id;
> import javax.persistence.GeneratedValue;
> import javax.persistence.GenerationType;
> import javax.persistence.OneToMany;
> import java.util.Set;
>
> @Entity
> public class Publisher implements Serializable {
>
> @Id
> @GeneratedValue(strategy = GenerationType.IDENTITY)
> @Column(name="id")
> private int id;
>
> @Column(name="name")
> private String name;
>
> @OneToMany(mappedBy="idPublisher")
> private Set<Magazine> magazineCollection;
>
>
> private static final long serialVersionUID = 1L;
>
> public int getId() {
> return this.id;
> }
>
> public void setId(int id) {
> this.id = id;
> }
>
> public String getName() {
> return this.name;
> }
>
> public void setName(String name) {
> this.name = name;
> }
>
> public Set<Magazine> getMagazineCollection() {
> return this.magazineCollection;
> }
>
> public void setMagazineCollection(Set<Magazine> magazineCollection) {
> this.magazineCollection = magazineCollection;
> }
>
> @Override
> public String toString() {
> return name;
> }
> }
>
> package magazine.model;
>
> import java.io.Serializable;
> import javax.persistence.Column;
> import javax.persistence.Entity;
> import javax.persistence.Id;
> import javax.persistence.GeneratedValue;
> import javax.persistence.GenerationType;
> import javax.persistence.JoinColumn;
> import javax.persistence.ManyToOne;
> import javax.persistence.FetchType;
> import java.sql.Date;
>
> @Entity
> public class Magazine implements Serializable {
>
> @Id
> @GeneratedValue(strategy = GenerationType.IDENTITY)
> @Column(name="id")
> private int id;
>
> @Column(name="name")
> private String name;
>
> @Column(name="date_published")
> private Date datePublished;
>
> @ManyToOne(fetch=FetchType.LAZY)
> @JoinColumn(name="id_publisher")
> private Publisher idPublisher;
>
>
> private static final long serialVersionUID = 1L;
>
> public int getId() {
> return this.id;
> }
>
> public void setId(int id) {
> this.id = id;
> }
>
> public String getName() {
> return this.name;
> }
>
> public void setName(String name) {
> this.name = name;
> }
>
> public Publisher getIdPublisher() {
> return this.idPublisher;
> }
>
> public void setIdPublisher(Publisher idPublisher) {
> this.idPublisher = idPublisher;
> }
>
> public Date getDatePublished() {
> return datePublished;
> }
>
> public void setDatePublished(Date datePublished) {
> this.datePublished = datePublished;
> }
>
> @Override
> public String toString() {
> return name;
> }
> }
>
>
