Mailing-List: contact dev-help@openjpa.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@openjpa.apache.org
Message-ID: <1041046966.156301268149647156.JavaMail.jira@brutus.apache.org>
Date: Tue, 9 Mar 2010 15:47:27 +0000 (UTC)
From: "Donald Woods (JIRA)" <jira@apache.org>
To: dev@openjpa.apache.org
Subject: [jira] Updated: (OPENJPA-1494) Criteria query IN predicate
 generates incorrect SQL
In-Reply-To: <192106676.49101265177838941.JavaMail.jira@brutus.apache.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


     [ 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.Photo@22de22de, org.apache.openjpa.persistence.criteria.Photo@23122312, org.apache.openjpa.persistence.criteria.Photo@23462346, org.apache.openjpa.persistence.criteria.Photo@226e226e, org.apache.openjpa.persistence.criteria.Photo@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.