Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » valid JPQL creates invalid Derby SQL.
valid JPQL creates invalid Derby SQL. [message #534058] Mon, 17 May 2010 16:10 Go to next message
Philip Wilkinson is currently offline Philip Wilkinson
Messages: 18
Registered: July 2009
Junior Member
Hi All,

I have a jpql query...

SELECT COUNT(x) AS XY FROM ISSUE x, NumericField y JOIN x.fields jf WHERE jf.issueTypeField.issueTypeFieldId = 4 AND y.numValue = 20 AND y IN (SELECT zz FROM ISSUE z, NumericField zz JOIN z.fields jfz WHERE jfz.issueTypeField.issueTypeFieldId = 5 AND zz.numValue = 30)


which parses ok from EntityManager.createQuery() to get a Query object.

When I call query.getSingleResult();

I get an exception...

Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.0.2.v20100323-r6872): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: Syntax error: Encountered "IN" at line 1, column 142.
Error Code: -1
Call: SELECT COUNT(t0.ISSUEID) FROM FIELD t3, FIELD t2, ISSUETYPEFIELD t1, ISSUE t0 WHERE ((((t1.ISSUETYPEFIELDID = ?) AND (t3.NUMVALUE = ?)) AND  IN (SELECT DISTINCT t4.FIELDID, t4.DCOL, t4.ISSUEID, t4.ISSUETYPEFIELDID, t4.NUMVALUE FROM ISSUE t7, FIELD t6, ISSUETYPEFIELD t5, FIELD t4 WHERE ((((t5.ISSUETYPEFIELDID = ?) AND (t4.NUMVALUE = ?)) AND (t4.DCOL = ?)) AND (((t6.ISSUEID = t7.ISSUEID) AND (t5.ISSUETYPEFIELDID = t6.ISSUETYPEFIELDID)) AND (t4.DCOL = ?))))) AND (((t2.ISSUEID = t0.ISSUEID) AND (t1.ISSUETYPEFIELDID = t2.ISSUETYPEFIELDID)) AND (t3.DCOL = ?)))
	bind => [4, 20, 5, 30, 1, 1, 1]


The problem being "?)) AND IN (" -- AND and IN next to each other with some extra whitespace where something should be (t3) maybe.

Is this a bug?

I'm using the latest eclipse link
javax.persistence_2.0.0.v201002051058.jar
and the target database is Apache Derby. (10.5.3.0)

Cheers,
Phil.
Re: valid JPQL creates invalid Derby SQL. [message #534868 is a reply to message #534058] Thu, 20 May 2010 10:22 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

Please log a bug for this issue.

The JPQL is technically not compliant as field must be used to compare with a sub-select, not an alias (i.e. becomes complex if the object has a composite pk). But it should probably work, at least for singleton pk, or a least a proper error message.

Your JPQL should compare the id of the NumericField, not the alias.


James : Wiki : Book : Blog : Twitter
Re: valid JPQL creates invalid Derby SQL. [message #535376 is a reply to message #534868] Sat, 22 May 2010 17:52 Go to previous message
Philip Wilkinson is currently offline Philip Wilkinson
Messages: 18
Registered: July 2009
Junior Member
Thanks James.

I have logged this issue.
https://bugs.eclipse.org/bugs/show_bug.cgi?id=314025
Previous Topic:Help with mapping a inherited relationship as a @OneToOne
Next Topic:Problem with installing EclipseLink 2.0.2 on Glassfish 3.0
Goto Forum:
  


Current Time: Thu Aug 21 02:23:03 EDT 2014

Powered by FUDForum. Page generated in 0.04119 seconds