Community
Participate
Working Groups
Build Identifier: EclipseLink-4002 The following 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) parses OK but the sql it produces for Apache Derby (10.5.3.0) is incorrect. 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 white space. posted to... http://www.eclipse.org/forums/index.php?t=msg&goto=534058&#msg_534058 and James responded with... 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. So the bug is the parse query should have failed with a descriptive error message or the sql generation should have succeeded. Reproducible: Always Steps to Reproduce: see description.
Setting target and priority. See the following page for details of the meanings of these fields: http://wiki.eclipse.org/EclipseLink/Development/Bugs/Guidelines
Issue also described in bug 301741, but keeping this one open as 301741 is more general.
Fixed.
Created attachment 207097 [details] add support for objects with in, as well as other jpql fixes
SVN trunk pending commit: Bug#259867 - JPQL issues http://wiki.eclipse.org/EclipseLink/DesignDocs/312146 Fixes several JPQL isssues, as well as adding tests for several JPQL bugs that have not been fixed yet. Fixes bugs, - 346729, 331124, 320541, 327848, 328378, 245652, 259867, 243384, 314025 Changes: - Added Expression.getFunction/getFunctionWithArguments that take a List, deprecated Vector API. - Moved getLeafDescriptr/Mapping to Expression to avoid duplicated code. - Added options in platform to require order by in select, and support multiple fields in distinct (Informix requires first, and MySQL supports second). - Added support to FunctionExpression for normalizing object expression with IN. - Added support to RelationExpression for normalizing object expression with IN. - Added support to RelationExpression for normalizing object expression with equals and subselects. - Fixed count of object expressions to work with complex ids, avoided duplicate codeof prepareObjectAttributeCount. - Removed incorrect parameter validation for direct collection in ParameterExpression as does not take into account convertible types. - Added support for extracting ids from collection parameters in QueryKeyExpression. - Detected duplicate aggregate fields in order by in SQLSelectStatement. - Some cleanup of @Overrides and Vector usage in expressions, platform. - Use correct descriptor clone for aggregate collections in JPQL parsing TypeHelperImpl. - Fixed writeDeleteFromTargetTableUsingTempTableSql in MySQLPlatform to have correct method signature. - Set query in QueryException for failed prepares. - Added tests for known JPQL issues including ones fixed and not fixed (not fixed tests are commented out currently).
Checked into trunk (2.4)
The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink
