You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by Ognjen Blagojevic <og...@etf.bg.ac.yu> on 2008/08/28 16:42:15 UTC
Sub-sub-query not working
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
Re: Sub-sub-query not working
Posted by Ognjen Blagojevic <og...@etf.bg.ac.yu>.
Hi Catalina,
Thank you for your quick reaction!
Regards,
Ognjen
catalina wei wrote:
> 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 <og...@etf.bg.ac.yu>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
...
Re: Sub-sub-query not working
Posted by catalina wei <ca...@gmail.com>.
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 <og...@etf.bg.ac.yu>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;
> }
> }
>
>