[eclipselink-users] JPQL GROUP BY problem

[dzhu <zhu1982@xxxxxxxxx>]

Hi,

I have a Book entity mapped as follows:
...
public class Book {
...
  @ElementCollection
  @CollectionTable(name = "BOOK_TAG")
  @Column(name = "VALUE")
  private Set<String> tags;

  private Integer borrowCount = 0;
...
}

I'm using derby and have configured for auto create tables.

now I execute the JPQL query: SELECT b.tags, COUNT(b.borrowCount) FROM Book
b GROUP BY b.tags

it fails (as an sql exception),

from the debug output, the SQL query that EclipseLink generates is:

SELECT t1.VALUE, COUNT(t0.BORROWCOUNT) FROM BOOK t0, BOOK_TAG t2, BOOK_TAG
t1 WHERE ((t1.Book_ID = t0.ID) AND (t2.Book_ID = t0.ID)) GROUP BY t2.VALUE

and the SQL exception message is:
The SELECT list of a grouped query contains at least one invalid expression.
If a SELECT list has a GROUP BY, the list may only contain valid grouping
expressions and valid aggregate expressions. 

This seems to be a bug, as the expected SQL would be like:

SELECT t1.VALUE, COUNT(t0.BORROWCOUNT) FROM BOOK t0, BOOK_TAG t1 WHERE
t1.BOOK_ID = t0.ID GROUP BY t1.VALUE

the extra join (t2) is not required.


-- 
View this message in context: http://old.nabble.com/JPQL-GROUP-BY-problem-tp29958595p29958595.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.