Eclipse Community Forums: EclipseLink » valid JPQL creates invalid Derby SQL.

Help

Home

Home » Eclipse Projects » EclipseLink » valid JPQL creates invalid Derby SQL.

Show: Today's Messages :: Show Polls :: Message Navigator

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.

Report message to a moderator

Re: valid JPQL creates invalid Derby SQL. [message #534868 is a reply to message #534058]

Thu, 20 May 2010 14:22

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

Report message to a moderator