valid JPQL creates invalid Derby SQL. [message #534058] |
Mon, 17 May 2010 20:10 |
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 14:22 |
|
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
|
|
|
|
Powered by
FUDForum. Page generated in 0.03135 seconds