[
https://issues.apache.org/jira/browse/OPENJPA-1494?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Donald Woods updated OPENJPA-1494:
----------------------------------
Affects Version/s: (was: 2.0.0)
2.0.0-beta
Fix Version/s: 2.0.0-beta2
> Criteria query IN predicate generates incorrect SQL
> ----------------------------------------------------
>
> Key: OPENJPA-1494
> URL: https://issues.apache.org/jira/browse/OPENJPA-1494
> Project: OpenJPA
> Issue Type: Bug
> Components: query
> Affects Versions: 2.0.0-beta
> Reporter: Catalina Wei
> Assignee: Fay Wang
> Fix For: 2.0.0-beta2
>
>
> Junit regression uncovered following problems:
> 1.a testcase error in TestTypesafeCriteria.testValues5() : this test is
> currently annotated @AllowFailure.
> the expected sql string is incorrect.
> String sql = "SELECT t0.name, t2.id, t2.label FROM CR_ITEM t0 "
> + "INNER JOIN CR_ITEM_photos t1 ON t0.id = t1.ITEM_ID "
> + "INNER JOIN CR_PHT t2 ON t1.VALUE_ID = t2.id WHERE "
> + "(0 = (SELECT COUNT(*) FROM CR_ITEM_photos t3 WHERE "
> + "(t3.VALUE_ID = ? OR t3.VALUE_ID = ? OR t3.VALUE_ID = ? OR
> t3.VALUE_ID = ? OR t3.VALUE_ID = ?) "
> + "AND (t0.id = t3.ITEM_ID) AND t0.id = t3.ITEM_ID) "
> + "AND 0 < (SELECT COUNT(*) FROM CR_ITEM_photos WHERE
> CR_ITEM_photos.ITEM_ID = t0.id))";
> in the last SELECT COUNT(*), the FROM table does not have table alias
> assigned.
> 2. NOT IN expression is transformed into a QueryExpression which resulting
> SQL subselects with SELECT COUNT(*) -- there could be a problem in how query
> expression tree is built in Criteria Query for IN-Expression.
>
> 3: ((CriteriaQueryImpl)q).toCQL() produces the following JPQL string which
> has syntax error.
> JPQL=SELECT i.name, i.photos FROM Item i INNER JOIN i.photos ? WHERE
> i.photos IN ([org.apache.openjpa.persistence.criteria.ph...@22de22de,
> org.apache.openjpa.persistence.criteria.ph...@23122312,
> org.apache.openjpa.persistence.criteria.ph...@23462346,
> org.apache.openjpa.persistence.criteria.ph...@226e226e,
> org.apache.openjpa.persistence.criteria.ph...@22aa22aa])
> if toCQL() produced the following JPQL, then semantically they would be
> equavilence:
> SELECT i.name, p FROM Item i INNER JOIN i.photos p where p NOT IN ?1
> but JPQL BNF does not allow Object-value 'p' in [NOT] IN conditional
> expression.
>
> 4. There is no JPQL equivalence query for the criteria query in
> testValues5().
> a closer JPQL string could be:
> SELECT i.name, p FROM Item i INNER JOIN i.photos p where p.id NOT IN ?1
> where ?1 is a collection-valued-parameter that contains a list of
> Photo IDs.
> The above JPQL generates following SQL:
> 8500 test TRACE [main] openjpa.Query - Executing query: [SELECT
> i.name, p FROM Item i INNER JOIN i.photos p WHERE p.id not IN ?1] with
> parameters: {1=[0, 0, 0, 0, 0]}
> 8750 test TRACE [main] openjpa.jdbc.SQL - <t 108529272, conn 1628201228>
> executing prepstmnt 1532713819 SELECT t0.name, t2.id, t2.label FROM CR_ITEM
> t0 INNER JOIN CR_ITEM_photos t1 ON t0.id = t1.ITEM_ID INNER JOIN CR_PHT t2 ON
> t1.VALUE_ID = t2.id WHERE (NOT (t1.VALUE_ID IN (?, ?, ?, ?, ?)))
> [params=(int) 0, (int) 0, (int) 0, (int) 0, (int) 0]
> as shown in the above, a NOT IN JPQL generated a NOT IN SQL.
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
